Part 8 ended by naming what came next: lineage, the catalog, the audit trail. Every record traceable to the file it came from.
That sentence is the bug.
The Promise That Was the Bug
Tying every record to its source file was a good decision when I made it. Part 4 built the ingest path so that a canonical record and its lineage row commit in the same transaction, on the same connection, or neither commits. The docstring still says why: lineage that can roll back separately from the thing it describes is lineage nobody can trust.
The mechanism for that was a foreign key.
raw_file_id UUID NOT NULL REFERENCES raw_files(id)It appears three times — on lineage, on gate_results, on resolution_proposals. Three tables, one column, and NOT NULL on all of them.
Read it as a guarantee and it says every audit row can be traced to the file that caused it. Read it as a constraint and it says something else entirely, which took me four parts and an afternoon with a schema diff to see:
Any write that did not come from a file cannot be recorded at all.
Not recorded badly. Not recorded with a placeholder. The row cannot be inserted. The database refuses it, correctly, because the column says a file is mandatory and there is no file.
Everything the platform does on its own has a file. Everything a person does does not.
Four Writes the Database Refused
I found these by walking the endpoints and asking what row each one should write. Three had been live in the platform for weeks. The fourth I caught on the way in — and it's the one that sent me looking at the other three.
A steward merges two members by hand. This one had no endpoint yet. I sat down to build it, asked what it should write, and the schema answered before I got there. The merge itself would land fine — identity_links never had a raw_file_id, which is the one place the schema got this right. The lineage row explaining why the merge happened could not be written at all, because the steward's judgement did not arrive in an 834.
That is a constraint catching a problem at design time, which is the system working. It also meant the schema had been saying something about the three paths that already existed, and I had not been listening.
Somebody reverses an automatic merge. Part 6 built POST /identity/links/{id}/unlink as a precondition for letting anything merge unattended, and Part 8 leaned on it hard. It sets unlinked_at and it stores the reason in a free-text notes column. Who reversed it is nowhere.
A human approves a proposal. Live since Part 6. resolution_proposals.gate_decision gets set to approved. One field to say it happened, and no field for which human.
A rejected file is run again after a rule change. The second pass writes a second set of rows identical in shape to the first, distinguishable only by timestamp. Nothing says it was a reprocess or what changed in between.
Assembled in one place, these say something I didn't enjoy writing down. Part 8 shipped a scorer that can merge two people's medical coverage without a human in the loop. The same system could not record a person's decision to undo it.
The auto-merge is defensible only because the record of who did what survives. Half of that record did not exist to survive.
The Change Set Was Always There, and It Was Anonymous
The fix came from reading someone else's schema. I spent a week with EHRbase and the openEHR clinical knowledge manager, neither of which we can adopt — one is a clinical repository and we hold enrollment, and it's built on a language this platform ruled out in Part 1. Both are excellent design sources anyway.
openEHR has an object called a CONTRIBUTION: a named change set. Everything committed together points at one contribution, and that contribution carries one audit record. A clinician saving a form that touches four things produces one contribution, not four unrelated rows.
We already had that object. Ours was raw_file_id, and it had three problems: it was anonymous, it was spelled as a file, and it was mandatory.
So the change set becomes a real table, and the file demotes to one kind of change set among five:
| Kind | What originated it | Needs a file? |
|---|---|---|
file_ingest | An 834 or an HL7 message arrived and was processed | Yes |
manual | A steward merged, unlinked, approved or rejected | No |
reprocess | A rejected file run again after a rule change | Yes |
backfill | A system correction across existing rows | No |
activation | A rule set or weight set was made live | No |
The vocabulary is closed and checked in the database. Five kinds cover every write path in the codebase today, and a sixth costs a migration rather than a string somebody invents at a call site — the same discipline Part 6 put on the gate's predicate vocabulary, for the same reason.
The NOT NULL survives where it was actually true. A file_ingest contribution without a file is still refused:
CHECK (kind NOT IN ('file_ingest', 'reprocess') OR raw_file_id IS NOT NULL)The old guarantee kept exactly where it holds, and dropped exactly where it was doing harm.
Lifting a Fact Instead of Copying It
Four tables were spelling the same facts four different ways, which I hadn't noticed until I lined them up:
| Table | Who did it | When | Under which version |
|---|---|---|---|
lineage | implied by transformation | created_at | — |
gate_results | implied by gate_name | evaluated_at | ruleset_version |
identity_links | actor | linked_at | weight_version |
resolution_proposals | model + prompt_version | created_at | — |
Three tables have no column for who. The obvious repair is to add actor to all three and move on. It takes an afternoon.
It would also be the fourth spelling of the same fact, stored in a fourth place that can disagree with the other three. Six months later somebody writes a query joining two of them and gets two different answers about the same event, and there is no principled way to say which is right.
The alternative is to lift rather than copy. The contribution carries who, when, what kind, under which version — once, in one row. The child tables keep the one thing that genuinely differs between them: what changed, and the evidence for it.
This is the whole design, and everything else in the part follows from it. An audit query stops being a union of four incompatible shapes and becomes a join to a common parent.
identity_links.actor stays where it is. It predates all of this and it carries a convention from Part 8 — actor='scorer:v3' — that a reader two years from now is meant to recognise on sight.
The Column That Had to Go
Once the child tables point at a contribution, raw_file_id on those tables is a duplicate of something the parent already knows. There were two options and the smaller diff was the wrong one.
Making it nullable keeps every existing query working. It also creates a column that has to agree with its parent forever, with nothing enforcing that it does. The first disagreement gets discovered by an auditor rather than by a test, and by then nobody can say which value was right.
So it goes. Three tables lose the column and three indexes go with it, replaced by a join through contributions.
That costs something, and this series has claimed a normalisation was free before, so this time I measured it. One million gate_results rows across five thousand files, in a transaction rolled back afterward:
| Execution | Buffers | |
|---|---|---|
Before — direct index on raw_file_id | 0.028 ms | 8 |
After — join through contributions | 0.046 ms | 15 |
Roughly twice the buffer reads and twenty microseconds. Cheap rather than free, and the number goes in the docs instead of the adjective.
Zero
The migration had to give every existing audit row a contribution. Rows with a raw_file_id map cleanly — one synthesised file_ingest contribution per file, carrying that file's original received_at. Rows that couldn't be traced were to land on a single backfill contribution whose description says plainly that these predate contribution tracking and their origin is unrecoverable.
One honest row, I reasoned, beats a column full of nulls that every future query has to special-case.
I expected that number to be the headline. How many audit rows in this platform cannot be explained — that felt like the measurement the whole part existed to produce.
It came back zero. Nine files, twenty-seven lineage rows, a hundred and two gate results, every one mapping to its file on the first pass.
For about a minute I read that as the migration going well.
Every row the platform had came from a file.
Every row the platform had came from a file because a row that did not come from a file could not be written.
The zero isn't evidence of a clean history. It's the constraint reporting itself. A database that refuses a class of writes will never accumulate rows of that class, and no migration can count the writes that never happened — the steward merges with no explanation attached, the reversals with no name on them. They aren't unrecoverable rows. They were never rows.
The hole was never in the rows that exist.
I've had measurements come back empty in this series before and both times it was a problem. Part 6's calibration harness never ran. Part 8's weight estimator found zero true-match pairs against a floor of five hundred and refused to publish. Those were instruments with nothing to measure, and they're why Part 11 exists.
This one is different in a way worth being precise about. The instrument works and the reading is correct. The zero is the finding, and it states the problem more exactly than any number I could have hoped for.
A Steward on Thursday Is Not an Ingest on Tuesday
Building this turned up an attribution I'd been living with without noticing.
When a steward runs the gate over a batch of pending proposals, the resulting gate_results rows were filed under the files whose proposals were being judged. There was even a function for it — _persist_by_file — which existed for exactly one reason: gate_results.raw_file_id was NOT NULL and something had to go in the column.
So a disposition run on Thursday was recorded as though it were part of an ingest that happened on Tuesday. Nobody would ever have filed a bug for it — a small lie of convenience that the schema demanded and I supplied.
With contributions it has somewhere true to go. A disposition run is its own manual contribution, naming who asked for it and which rule set was live when they did. Every link it writes still carries actor='gate:v7', so the person who ordered the run and the thing that made each decision stay separately readable — which is the distinction that matters when somebody asks who approved a merge.
POST /gates/proposals/evaluate now takes a required actor, and its body is no longer optional.
This is the shape of finding I've come to expect from this series. The constraint didn't produce an error anybody saw. It produced a small untruth in the data, every time, for three parts, and it looked exactly like a normal row.
The Test That Could Only Pass
The catalog is the read side, and the design rule I took from the CKM is that a catalog's value lives in reverse dependencies. Their most-used view answers "which templates use this archetype". Ours answers four questions that were each a join nobody could make without hand-writing SQL: which rules a version contained and what they decided, which records a weight set wrote and how many survive, which prompt version produced the proposals a steward keeps rejecting, and which fields any rule actually branches on.
That last one has a second customer. Part 2 proposed the branch test — if code branches on a value it earns a typed column, and pass-through data lives in JSONB — and Part 3 put variable payer fields in a JSONB bag on the strength of it. Six parts later the catalog could finally run the test against real rules.
It reported that no rule branches on a JSONB-backed field. The bet holds, clean.
Except three rules do reach into coverage.payer_extensions, and the catalog couldn't see them. A predicate is compiled from an expression, and the compiler records the paths the expression names. payer_extensions_valid(coverage, payer_id, guide_version) names coverage and nothing deeper — the reaching-in happens inside the function. So a catalog built from referenced paths alone reports a clean result and is simply wrong.
A test that can only report success proves nothing, the same way a purity check that cannot fail proves nothing about the modules it passes. Part 8 made that argument about its own import-graph test; this is the same lesson arriving from the other direction.
The fix is five lines — predicates now declare what they read beyond their arguments. And with the instrument able to see, the answer got more interesting than either possible verdict:
Three active rules reach into the JSONB bag, and all three go through whole-bag validators — checking the entire set of extensions against a payer's companion-guide schema, or flagging keys the guide doesn't define. That's conformance checking, not branching. No individual key is being read to decide anything, so no key has earned a typed column.
The Part 3 bet holds. It holds for a more specific reason than "nothing touches the bag", and that reason is the thing worth carrying into the next payer.
What Ships, and What It Takes on Trust
Four audit tables now share one parent. The four writes that could not be recorded are each executable and each asserted end to end — the manual merge that writes a lineage row, the reversal that names who reversed it, the approval that names its human, the reprocess distinguishable from the first pass by more than a clock reading. One record's whole history assembles into a single ordered timeline across all four tables. Seven endpoints, and two commands for the questions I actually ask at a terminal.
One instrument was added and no new metrics namespace, which Part 8 closed deliberately at three. Contribution kind became a label on the counters that already existed, which turned out to be more useful anyway — it answers "how much of last month was reprocessing" without inventing anything.
And there's a limitation a reader will reach before the end of this paragraph, so I'd rather state it than have it found.
The actor is trusted input. There is no auth layer on this platform. The actor arrives on the request — a field on the merge, a required parameter on the disposition run — and the server takes the caller's word for it. Every contribution is tamper-evident about the system: what ran, under which rule set, in what order, against which file. About the person, it records a claim.
The trail can prove what the platform did and when. It takes the caller's word for who asked.
For an internal service with one operator that's an acceptable state to ship in, and an unacceptable state to ship in quietly. Authentication makes the claim verifiable and it doesn't change one line of this schema — the contribution already has the column, waiting for a value somebody can't fake.
Next is the pipeline: the whole thing end to end under an orchestrator, with lineage and contributions carrying through it rather than bolted on afterward. The part after that is the one where all this machinery finally meets enough real volume to say whether any of it was calibrated.
For now the platform can explain itself. It can say what happened to a record, under which rules, at whose request — including when the request came from a person, which for eight parts it could not say at all.
