Appendix CWhat does the hash chain protect against?
Chapter 03 of the main article says that every change keeps the previous version, chained to the one before it by a cryptographic hash. This appendix opens the mechanism: what is hashed, how it is chained, what happens when two people write at once, who could rewrite it, and what it costs.
Status, 2 November 2026
This appendix is a snapshot of work in progress. Done: the mechanism checked against its migrations and tried on a disposable database. Open: a fork when one transaction updates a row several times (OPEN-110), no verifier (OPEN-111), no protection against an administrator (OPEN-112), and the improvements ranked at the end. A later version will report what was done about them. Numbers in the form OPEN-nnn are entries in the project's register of open items, part of its quality system (chapter 06).
How does it work?
Eleven tables of the device hold data: the patient record and its addresses, the configuration the device receives from the configuration tool, the action catalogue and the deployed restrictions. Each has an archive twin with the same columns and four more: its own identifier, the identifier of the row it came from, the time it was archived, and a hash. A trigger runs before every update. It takes the row as it was, computes a hash over that content and the previous hash for the same row, writes both into the archive, and lets the update proceed, all in the same statement. If the update fails, the archive entry disappears with it.
The trigger, for the patient table
PERFORM pg_advisory_xact_lock((8::bigint << 48) | OLD.id);
SELECT content_hash INTO previous_hash FROM patient_archive
WHERE source_id = OLD.id ORDER BY archived_at DESC LIMIT 1;
INSERT INTO patient_archive (source_id, name, gender, photo, marital_status, version, active,
content_hash)
VALUES (OLD.id, OLD.name, OLD.gender, OLD.photo, OLD.marital_status, OLD.version, OLD.active,
encode(digest(COALESCE(previous_hash, '') || '|' || to_jsonb(OLD)::text, 'sha256'), 'hex'));
RETURN NEW;What is hashed
The whole outgoing row, serialised by the database as JSON, after the previous hash for that row and a separator, through SHA-256. The serialisation is deterministic, which lets anyone recompute a hash from the archived columns later. The comment in the code says the JSON keeps the columns in the table's order; PostgreSQL in fact orders the keys of this kind of JSON by length, then alphabetically. The result is just as deterministic, but a verifier written from the comment would fail.
How it is chained
One chain per row, starting from an empty previous hash: each version of a patient's record points to the version before it. Changing any archived version changes its hash, and every later hash for that row stops matching.
When the table changes shape
When a column was added to the action catalogue and later dropped, the archive kept it, so the history stayed complete, and the migrations say how the hashed JSON differs before and after each change. A verifier will have to rebuild each version with the columns of its era.
Who can write where
Three database roles are involved. The application's own role can insert, update and read the data tables, and only read the archives. The configuration tool's credential can insert and update the configuration tables and the deployed restrictions, and cannot read patients at all. Neither is granted delete on anything. The archive is written by the trigger, which runs with the rights of a third role that owns the tables. Each of these was confirmed by trying it: a delete or an archive update by either writer role is refused by the database.
What happens when two people write at once?
Two sessions updating the same row are serialised twice: by the row lock every update takes, and by a lock taken in the trigger, keyed on the table and the row. Tried with two sessions, the second waited for the first to commit, archived the version the first had written, and the chain verified end to end. The application also rejects a save made against a version that has since changed, so the second person would be told, and nothing would be overwritten in silence.
The key of that lock has one slip. Each table has a number in it, and the patient table and the action catalogue both use 8, although the patient table's migration notes 8 as the next free number. An update to patient 42 and one to catalogue entry 42 therefore wait for each other. The chain is unaffected; it is a small cost, and a convention kept in a comment that nothing checked.
The defect: several updates in one transaction
The trigger finds the previous hash by taking the most recent archive entry for the row, ordered by its timestamp. That timestamp is the start of the transaction, so every entry written in one transaction carries the same one, and their order is undefined. Tried with four updates to one patient in a single transaction: the second entry chained correctly, and the third and fourth both chained to the first. The history then forks, and an entry on one branch could be altered or removed without breaking the hashes on the other.
Nothing in today's code is known to update a row three times in one transaction; a clerk's save updates a record once. The only test of the chain makes two updates, each in its own transaction, and reads them back in the same timestamp order, so it could not see this. The fix is one line per trigger, ordering by the archive entry's own identifier, which only grows. It is recorded as OPEN-110, with a test that makes several updates in one transaction.
Who checks the chain, and who could rewrite it?
Nothing checks it
The access-control module has a function that verifies its own audit chain; the device has none, and the only code that recomputes the device's hashes is that one test. A change to the history would show to anyone who recomputed the chain, but nothing does. A verifier, run on a schedule and at every deployment, is the missing piece. It is recorded as OPEN-111.
The owner of the tables could rewrite it
The role that owns the tables, and any database superuser, can update the archive and recompute every hash after it. The chain protects against the roles the application uses. Against an administrator, it needs a key the database does not hold, and the latest hash of each chain exported on a schedule to a store the database administrator cannot change. That is a design decision still to be taken, with the hospital's IT, and it is recorded as OPEN-112, alongside OPEN-91 for the same gap in the access-control module's audit log.
What does it cost?
Measured on a laptop, in a container: updating 10,000 patient rows took about 1.45 seconds with the trigger and about 0.08 seconds on an identical table without it, about 0.14 milliseconds more per row. A single update took 0.45 milliseconds, 0.29 of them in the trigger. For a clerk saving a record, that is invisible. For a bulk load of hundreds of thousands of rows, it would be minutes, and worth planning for.
What did I learn?
What Claude and BMAD brought shows in the mechanism itself. It was specified once, in the architecture, and Claude applied it to eleven tables in the same way, with the details a reviewer looks for: the trigger running with the owner's rights and a pinned search path, no delete granted to anyone, history kept when columns change. And the review that found its weaknesses took one session, on a disposable database.
About the method, each finding points at how the work was done:
| Finding | What it says about the method |
|---|---|
| The history the article first called tamper-evident is evident only against the application's own roles | The architecture's decision lists what the chain prevents, lost history and races among them, and names no one it protects against, so nobody checked the administrator |
| The control was built without its verifier | The architecture foresaw "a future verification job", and nothing scheduled it: a control and its check were not specified together |
| The fork within one transaction went unseen | The only test exercised one update per transaction, and read the chain in the order that hides the fork |
| Two tables share a lock number that a comment declared free | A convention lived in a comment, with nothing to check it |
What would I change?
The corrections are recorded as OPEN-110 to OPEN-112. The improvements to the method below would change how every later control is built. Each is a proposal, ranked by the benefit I expect, with the measure a later check would read.
| Rank | Improvement | Why at this rank | Measure |
|---|---|---|---|
| 1 | Every security control in the architecture states who it protects against, and who it does not | It would have shown the administrator gap at design time, for every control | Controls with a stated threat, against controls listed |
| 2 | A control is specified with its check, and both ship together | Until something recomputes the chain, a change to the history goes unseen | Controls with a running check |
| 3 | Tests of a control include the case built to break it: several writes in one transaction, two sessions, an administrator | The fork and the anchor would both have shown in tests | Breaking cases per control |
| 4 | Numbers and names that must stay unique are allocated in one place, and the build checks them | Cheap, and it removes a class of slip | Duplicates found by the build |
Where does this leave the project?
The project is at a gate, and the mechanism with it. It does what chapter 03 says for the cases the product meets today: every update keeps the previous version in the same statement, the writer roles cannot delete or alter history, and two people writing at once are serialised. Against an administrator it protects nothing yet, which is why the article calls it hash-chained. Ahead are the three open items and these four proposals, and none has a date yet. A later version of this appendix will say what was done.