Compliance invariants enforced in the database, not the application

The problem

In regulated record-keeping, some failures cannot be repaired after the fact. Evidence dated after the event it authorizes is not cured by entering it later. An edited ledger row no longer shows what the source reported. A result that cannot be reproduced from its stored inputs is an assertion, not evidence.

Most systems keep such rules in application code, where they hold only if every code path, batch job and database user behaves.

Here they are properties of the database, tested against the wrong input.

What was built

A PostgreSQL schema in six forward-only migrations, a seeded ruleset, a pgTAP suite, and two Node.js evaluators that run limit tests against stated thresholds using only the stored record.

The invariants, each a trigger, constraint or grant:

• Evidence precedes the event. A record cannot be inserted unless the evidence that authorizes it exists, dated on or before the event, and matches what the record claims. The gate reruns on any update to a column it reads and compares dates in UTC.
• Append-only where history matters. Fifteen tables (pre-event evidence, the ledger, stored evaluations, rulesets, findings, classification events and the audit log) refuse UPDATE, DELETE and TRUNCATE from every role, the table owner included. A correction is a new row.
• Classification moves only by event. A classification with regulatory consequences is never written directly. Inserting an event row moves it; clearing it requires a separate compliance role, a reason and evidence.
• Thresholds are versioned rules. Each threshold the schema reads is a ruleset row with its citation; each ruleset carries a hash of its definition. A missing parameter raises an error or shows as a visible null, never a silent pass. A changed threshold is a new version, never an edit.

Design decisions

Reproducible by construction. Each stored result records the ruleset it ran under and the SHA-256 of a canonical JSON document of everything it read: evaluator version, stated assumptions, parameters, record fields and every transaction considered. A verify command recomputes every current result, writes nothing, and exits non-zero if any fails to reproduce or a date that was run has records with no result.

Corrections without rewriting. A late transaction can change a past answer. The input hash is part of each result’s unique key, so the same inputs give one row and new inputs a new row beside the old one; a view names the current row. When the corrected answer is “no result”, a withdrawal finding names the superseded row. Nothing is deleted.

Refuse rather than guess. Where the ledger cannot support an answer, the evaluator writes a finding with a reason code and no measurement, and each run reports how much of the population it could not evaluate. Where a statutory date rule has two defensible readings, both are run and a result is stored only when they agree.

A warning is not a failure. A threshold that requires a governance approval is a warning that needs evidence of that approval, not a blocked transaction.

The connection is the boundary. The evaluator logs in as its own role and refuses to start if that role is a superuser, the schema owner, or a member of anything beyond the application role. Switching roles from the owner’s session is not a boundary, because it can switch back.

No model in the loop. Determinations are local and deterministic; the evaluators handle money as integer cents and the schema as fixed-point decimals, never floating point.

Identifiers by reference. By convention, sensitive identifiers are columns that hold a vault reference rather than the number. Nothing in the schema checks this yet.

Evidence and limits

• 240 pgTAP checks and 77 evaluator tests (10 of them against the database) pass on a throwaway local database. Failure paths are tested, among them evidence dated after the event, an update to a frozen field and a direct write to a classification.
• A two-session race against a cumulative limit was run once by hand. The second session waited and was refused.
• Migrations 0002-0006 and the evaluator code went through several rounds of file-level adversarial review by a second AI agent from a different vendor; the base schema was outside its scope. Every round required changes. Most were made; the rest are recorded as open debt, including the migration-only owner role listed below. The final fixes have not been re-reviewed.

Limits, stated plainly:

• It has never run against a live database or real data, and has no feed ingestion yet.
• The audit table is append-only and has hash-chain columns, but nothing yet computes or verifies the chain.
• Retention by data band is decided but not implemented. The vault behind the identifier references is not built.
• Against the table owner, the triggers take a deliberate act to remove; they are not a wall. A separate owner role used only for migrations is not built.
• The evaluators cover two limit tests and refuse any record whose ledger holds the kinds of history they do not yet model.

Where it applies

The method fits any regulated record where the question is what was true, and known, as of a date.

• Preparing an authorization package or an audit. Least privilege, separation of duties, protection of audit records and input validation are easiest to evidence as database properties with repeatable tests. The test output is the artifact, and a reviewer can rerun it.
• Payer and government-contractor data. Eligibility, enrollment and claims records face the same pressures: late corrections, thresholds that change by plan year, and results that must reproduce years later.
• Fractional security leadership. The useful questions: which rules are enforced below the application, who can bypass them, how a past result is reproduced, and what happens to stored results when a threshold changes.

Independent work by Wade Blackwell. Each write-up is written from scratch for this site, without client, employer or personal data. Names, hostnames, addresses and configuration detail are left out on purpose.