> ## Documentation Index > Fetch the complete documentation index at: https://schemabrain.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # ADR 0001 — Audit row + PII taxonomy > The audit row schema and the two-layer PII taxonomy (Sensitivity + PII category) shipped at v0.4. # ADR 0001 — Audit row schema and PII taxonomy * **Status:** Accepted * **Date:** 2026-05-15 * **Charter version referenced:** v1.0.1 ## Context SchemaBrain today describes databases — it never executes queries against them. Every MCP tool shipped through v0.5 returns derived metadata (column descriptions, join paths, suggested example queries) and the audit trail is implicit in the MCP transport logs. That shape is about to change. The v2 line adds `execute` and `validate_query` — tools that compile a `MetricDefinition` into a parameterised SQL statement and run it against the source database. The moment that lands, three new properties become load-bearing: 1. **Decisions need a record.** "This query was refused because it touched `pii:contact` and the caller's role is `analyst_no_pii`" is only useful if the record survives, is queryable, and can be produced for audit review months later. 2. **Records need to be tamper-evident.** An audit trail that can be silently rewritten is worse than no audit trail — it implies guarantees that don't hold. 3. **Records need privacy boundaries that match real regulation.** "PII" as a single flag conflates GDPR special categories with PCI DSS payment data with HIPAA PHI. Regulators audit on the categorical distinctions; the schema needs to express them. The right time to lock this shape is **now**, before tool #5 (`get_example_queries`) ships as the first charter-v1.0.1-compliant tool. Tool #5's description, recovery contract, and event stream all need to reference the final audit row and the final PII taxonomy. Once locked, the v1 implementation lands the table, and v2 widens the writers; no retrofit required. The fingerprint primitive used by audit rows has a separate visibility trajectory (see Privacy-by-construction below) — this ADR documents what the fingerprint **excludes**, not how it's computed. ## Decision ### 1. The `mcp_audit` table — 14-field DDL Every MCP tool invocation that touches the source database, returns a refusal, or completes a charter-defined status writes exactly one row to `mcp_audit`. The table lives in the same SQLite store as the rest of SchemaBrain's state. ```sql theme={null} CREATE TABLE mcp_audit ( id INTEGER PRIMARY KEY, occurred_at TIMESTAMP NOT NULL, source_connection_id TEXT NOT NULL, caller_id TEXT, tool_name TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('success','empty','partial','degraded','error','refused')), refusal_reason TEXT CHECK (refusal_reason IS NULL OR refusal_reason IN ( 'pii_blocked', 'allowlist_violation', 'fragment_unsafe', 'cost_cap_exceeded', 'ambiguous_resolution', 'schema_drift' )), cost_class TEXT NOT NULL CHECK (cost_class IN ('small','medium','large','refused')), pii_categories TEXT NOT NULL DEFAULT '', ast_shape_hash BLOB, rule_id TEXT, fingerprint BLOB NOT NULL, fingerprint_version TEXT NOT NULL, chain_hash BLOB NOT NULL ); CREATE INDEX idx_mcp_audit_occurred_at ON mcp_audit (occurred_at); CREATE INDEX idx_mcp_audit_fingerprint ON mcp_audit (fingerprint); ``` Per-field semantics: | # | Field | Purpose | | -- | ---------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------- | | 1 | `id` | Surrogate row id; monotonic | | 2 | `occurred_at` | UTC timestamp of the event | | 3 | `source_connection_id` | Which source DB the row applies to | | 4 | `caller_id` | Agent identity / role at call time | | 5 | `tool_name` | MCP tool name (e.g. `execute`, `validate_query`) | | 6 | `status` | Charter envelope status enum | | 7 | `refusal_reason` | One of six refusal kinds; NULL unless `status='refused'` | | 8 | `cost_class` | Bucketed cost (`small`/`medium`/`large`/`refused`) | | 9 | `pii_categories` | Sorted comma-separated `frozenset[PIICategory]`; `''` when none | | 10 | `ast_shape_hash` | 32-byte SHA-256 identifying the structural pattern of a refused query — suitable for grouping similar refusals in audit review; NULL for non-refusal rows | | 11 | `rule_id` | Structural rule identifier (`builtin:…`, `deployment:…`, `fleet:…`) | | 12 | `fingerprint` | 32-byte SHA-256; the composition is documented in Privacy-by-construction below | | 13 | `fingerprint_version` | `"fp-v1"` etc; supports formula migration | | 14 | `chain_hash` | `sha256(prev_row.chain_hash ‖ canonical(this_row[1..13]))`; genesis row uses 32 zero bytes | ### 2. Append-only invariant The audit table is append-only by three independent mechanisms. Any single one being weak is acceptable; all three failing simultaneously is what we design against. **Mechanism A — SQL triggers reject UPDATE and DELETE.** ```sql theme={null} CREATE TRIGGER mcp_audit_no_update BEFORE UPDATE ON mcp_audit BEGIN SELECT RAISE(ABORT, 'mcp_audit is append-only'); END; CREATE TRIGGER mcp_audit_no_delete BEFORE DELETE ON mcp_audit BEGIN SELECT RAISE(ABORT, 'mcp_audit is append-only'); END; ``` **Mechanism B — Role separation at the SQLite-attach layer.** Schema Brain attaches the audit table through a write-only connection used exclusively by the audit writer; read paths use a separate connection that holds a read-only file lock. Both connections ship with the audit implementation; the writer connection has no exposed code path for UPDATE or DELETE construction. **Mechanism C — Hash chain (`chain_hash` column).** Each row's `chain_hash` is computed as `sha256(prev_row.chain_hash ‖ canonical_serialisation(this_row, fields 1–13))`. The genesis row uses 32 zero bytes for the previous hash. A `schemabrain audit verify` CLI command (deferred to a later release) walks rows in order, recomputing each chain hash and reporting the first mismatch. The chain is **detection**, not prevention — an attacker with write access to the file can rewrite the chain coherently. The defense is that any external archive (file-system snapshot, S3 backup, off-host copy) that captured a prior `chain_hash` can be compared against the current row to detect tampering between the snapshot and now. ### 3. PII taxonomy — 4 sensitivities, 12 categories ```python theme={null} # schemabrain/pii/categories.py Sensitivity = Literal["public", "internal", "confidential", "pii"] PIICategory = Literal[ "contact", # email, phone, address, name "financial", # account balances, salary, transaction amounts "payment_card", # PAN, CVV (PCI DSS scope; distinct from financial) "health", # diagnoses, medications, treatments (HIPAA PHI) "genetic", # DNA, genomic data "biometric", # face, fingerprint, voice, iris "behavioral", # browsing, purchase history, app usage "online_identifier", # IP, cookies, device IDs, advertising IDs "credential", # passwords, tokens, secrets, API keys "government_id", # SSN, passport, driver's license, tax ID "location", # GPS, precise geolocation "demographic_protected", # race, religion, sexual orientation, political views, # trade-union membership (GDPR Art. 9 special categories) ] ``` The two layers are intentional: * **Sensitivity (Layer 1)** drives role-based access decisions. "Role `analyst` may read `internal` and `confidential`; never `pii`." One axis, four values, ordered `public < internal < confidential < pii`. * **Categories (Layer 2)** drive refusal patterns. "Role `customer_support` may read `pii:contact` but not `pii:financial` or `pii:government_id`." Set-valued (`frozenset[PIICategory]`), composable across joins. Why 12 categories rather than a flat enum, and rather than the 3-category short-list (`public | confidential | pii`) some implementations use: | Category | Anchored in | | -------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------ | | `contact`, `financial`, `location`, `behavioral`, `online_identifier`, `government_id` | GDPR Arts. 4 + 9; CCPA/CPRA §1798.140(o) personal information categories | | `health` | HIPAA Safe Harbor 18 identifiers; GDPR Art. 9 special categories | | `genetic` | GDPR Art. 4(13); HIPAA | | `biometric` | GDPR Art. 4(14); CCPA | | `demographic_protected` | GDPR Art. 9 special categories (race, religion, sexual orientation, political views, trade-union membership) | | `payment_card` | PCI DSS scope (separated from `financial` to reduce scope-creep on non-PCI financial data) | | `credential` | ISO 27018 + general security practice; passwords / API keys / tokens / secrets | The list is closed at 12 today but additive — extending it is a minor charter bump. The current set covers GDPR, CCPA/CPRA, HIPAA, PCI DSS, and ISO 27018 categorical distinctions without conflating regulator-distinguished categories. A Pydantic validator enforces the cross-layer invariant: ```python theme={null} # Sketch — exact form lands with the implementation @model_validator(mode="after") def _consistency(self) -> EntityColumn: if self.sensitivity != "pii" and self.pii_categories: raise ValueError("non-pii sensitivity cannot carry categories") if self.sensitivity == "pii" and not self.pii_categories: raise ValueError("pii sensitivity requires at least one category") return self ``` ### 4. Propagation across joins and views Tool authors will need a deterministic rule for "what PII categories does this joined result carry?" The rule is: * **Sensitivity propagates by MAX** under the ordering `public < internal < confidential < pii`. A view that pulls one column from a `public` table and one column from a `pii` table is `pii`. A view combining `internal` and `confidential` is `confidential`. * **Categories propagate by UNION.** A view selecting `users.email` (`pii_categories={"contact"}`) and `transactions.amount` (`pii_categories={"financial"}`) has effective categories `{"contact", "financial"}`. Propagation is computed at compile time inside `schemabrain/compiler/` — the audit writer reads the compiler's already- resolved category set rather than recomputing. This keeps the compiler's grain check and the audit writer's PII bookkeeping aligned by construction. ### 5. Privacy-by-construction The fingerprint primitive (column 12, `fingerprint`) is the load-bearing audit field for any future fleet-level aggregation. Its visibility posture is explicit and asymmetric: this ADR documents what the fingerprint **excludes**; the exact composition is not public until the hosted fleet-aggregation product ships. What the fingerprint excludes, and how the design enforces each exclusion: | Excluded | Why it's excluded | How the design enforces it | | ------------------------------------------------------- | ---------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------- | | Row content (any column value from any source database) | Privacy lawsuit; trivial PII leakage | The fingerprint input is a frozen dataclass with no value-typed field; the type system forbids construction with row content | | Column names from the source database | Schema names leak business context (`billing_invoice_line_items` reveals industry, ERP system) | Source-database identifier strings never reach the fingerprint input; the input has no identifier-string field | | Literal SQL strings | Even normalised SQL can leak schema vocabulary | Raw SQL strings never reach the fingerprint input; the input has no string-typed SQL field | | Caller / user / agent identity | The pattern matters across deployments; identity is per-tenant audit, not fleet signal | The fingerprint input has no caller field | | Database / source identity | Tenant identity is per-row context (`source_connection_id`), not per-pattern | The fingerprint input has no source identifier | | Timestamps | Time-correlation across tenants is a side channel | The fingerprint input has no time field; `occurred_at` lives in the audit row alongside, not inside, the fingerprint | The single-paragraph guarantee — quotable verbatim: > Fingerprints contain no row content, no column values, no identifying > schema information. We publish what the fingerprint excludes rather > than how it's computed — the same posture Cloudflare uses for WAF > signatures and Snyk uses for vulnerability patterns. The exact > composition will be published when our hosted fleet-aggregation > product ships. The CI lint that enforces the fingerprint-input field count is part of the audit-implementation deliverable; the test asserts the field count at the dataclass definition and fails on any addition. Adding a fingerprint field is therefore a deliberate, reviewed action — not an accidental drift. ## Implementation sequence This ADR is design-only. The implementation deliverables, in order: 1. **PII taxonomy module first.** `schemabrain/pii/categories.py` lands with the `Sensitivity` and `PIICategory` Literal types and the propagation helpers. First consumers are the PII classifier and the entity blueprint — both of which need the taxonomy before the audit table needs it. 2. **Audit module next.** `schemabrain/audit/` lands with the `mcp_audit` DDL, the writer, the chain-hash computation, and the fingerprint primitive. Tool authors begin emitting audit events through a single `audit_write` helper. 3. **Chain verification CLI.** `schemabrain audit verify` walks the chain and reports the first mismatch. Deferred to a later release once real audit volume justifies it; the chain hash makes verification mathematically possible from day one of the audit module. 4. **v2 widens the writers.** `execute` and `validate_query` are the first tools to populate the `refusal_reason`, `ast_shape_hash`, and `rule_id` fields for refusal paths. Non-refusal v2 rows use `refusal_reason IS NULL` and `ast_shape_hash IS NULL`. The ADR is locked **now** so tool #5 (`get_example_queries`) can reference final field names, final refusal reasons, and final PII category names in its description and recovery contract. ## Consequences **What becomes possible:** * Charter-v1.0.1 tools can name `refusal_reason` values in their recovery contracts (e.g. `Recovery(suggested_tool="describe_table")` paired with `refusal_reason="pii_blocked"`) and the values won't drift between tools. * Enterprise legal review has a quotable privacy guarantee and a closed schema to validate against, separately from any code-level audit. * The fleet-aggregation product can ship without renegotiating the audit shape — the schema is forward-compatible by construction through `fingerprint_version`. **What changes for tool authors:** * Every new MCP tool must define which `refusal_reason` values its refusal paths produce (subset of the six listed) and document them in the tool's docstring. * Tools that touch source-database data compute their effective `pii_categories` through the compiler's propagation helper rather than hand-rolling set-union logic at the tool surface. * Tools must not log row content into any other surface (stderr, MCP transport, exception messages) — the audit row is the only sanctioned per-call record, and its column constraints already forbid value fields. **What remains deferred:** * The exact write path for the audit row (single helper vs decorator) is an implementation choice at audit-module landing time. * The `schemabrain audit verify` CLI shape is deferred to a later release; the chain hash makes verification mathematically possible from day one of the audit module. * Cross-deployment fingerprint aggregation is the hosted product (v3+); the local audit table never aggregates beyond a single deployment. ## References * [SchemaBrain MCP Charter v1.0.1](../agent-ux-charter.md) — status enum, refusal kinds, recovery contracts. * GDPR Articles 4 (definitions) and 9 (special categories). * CCPA / CPRA §1798.140(o) personal information categories. * HIPAA Safe Harbor — 18 identifiers, 45 CFR §164.514(b)(2). * PCI DSS scope guidance (payment card data isolation). * ISO 27018:2019 (PII in public clouds). * Cloudflare WAF signature posture (public exclusion list, private composition) — the analogous visibility model.