Append-only is a database privilege, not a coding convention
A function that appends an activity row to an opportunity’s history was written to accept a client-supplied identifier, so that a phone which created the row offline could replay it later without producing a duplicate. The first draft inserted the row, then assigned the client’s identifier onto it, then flushed.
That is an INSERT followed by an UPDATE. The table it wrote to had UPDATE revoked from the application’s database role. The write would have failed outright, on the first real request, for a reason that has nothing to do with the identifier and everything to do with what kind of table it was.
It was caught before it ran, and only because the revoke was already in the migration. The fix was one line moved: set the identifier before adding the row to the session, which is what the attendance check-in code had been doing for weeks. Same table category, same constraint, and the second implementation had quietly rediscovered it.
What “append-only” usually means
In most codebases append-only is a statement of intent. There is a table of status changes, or a ledger of stock movements, or an audit trail, and everyone knows you are not supposed to update it. Nothing stops you. The ORM will happily issue an UPDATE. A migration will happily issue a DELETE. A developer fixing bad data at three in the morning will do both.
The property you actually want is not “we agree not to modify this”. It is “this cannot be modified”. Those are different systems with different failure rates, and the difference costs one line in a migration:
REVOKE UPDATE, DELETE ON activity_trail FROM app_role;
The application connects as a role that can INSERT and SELECT on that table and nothing else. The owner role, which runs migrations, retains full rights — so a deliberate, reviewed, audited repair is still possible. What is no longer possible is an accidental one, and accidental is the category that actually happens.
Every history table in the system was built this way: order status changes, dispatch status changes, demo status changes, enquiry notes, visit check-ins, stock movements, import rows, the activity trail above. None of them has a soft-delete column either, because a soft-delete column on an append-only table is a contradiction wearing a disguise — it is an UPDATE that hides a row, and hiding a row from the history is the exact thing the history exists to prevent.
The check that makes it real
A revoke in a migration is a claim. Claims about privileges are worth exactly as much as claims about anything else, which is to say they need a test.
Each of those modules ships a smoke check that queries the database’s own privilege catalogue for the table and asserts the application role holds INSERT and SELECT and nothing more. Not a check that an UPDATE raises an error in one code path — a check on the actual granted set, which cannot be satisfied by a lucky code path or defeated by an unlucky one.
This matters more than it sounds, because migrations get edited. Autogenerated migration diffs in this project routinely contained changes nobody asked for, and stripping them was a per-migration chore. A grant silently reinstated by a tool is invisible in review and completely visible to that one query.
Where the guarantee did not reach
The same work exposed the limit of the approach, and it is worth stating because it is the part that gets assumed away.
The automatic audit trail was implemented as a hook on the session flush: every object being written that carries a tenant column gets an audit row describing the change. That is a neat mechanism and it covers essentially every business table, because every business table carries a tenant column by rule.
Every business table except one. The tenant record itself has no tenant column — it is the tenant. So changes to a tenant’s name, default currency, timezone and locale were not captured by the automatic trail at all. The settings row beside it was captured, because that has a tenant column, which made the gap harder to see: the audit history looked populated.
The assumption was that “every entity is audited”. The mechanism actually implemented was “every entity with a tenant column is audited”, and those two sentences describe the same set right up until they do not. Nobody wrote the wrong rule. The rule was written once, in code, as a test for an attribute, and the exception was structural rather than accidental.
There is a general shape here. A hook that discovers what to do by inspecting the object will always have a blind spot exactly where the object is unusual, and the unusual object is normally the important one. The tenant row is the most security-relevant row in a multi-tenant system.
Sensitive events do not flush
The other place the automatic mechanism does not reach is anything that is not a row change.
A CSV export changes no business data. A role’s permission set being rewritten changes rows, but if you implement the rewrite as a bulk delete plus a bulk insert through the database layer rather than through the object layer, the flush hook never fires. A refresh token being replayed after rotation changes nothing at all — it is a request that should never have happened, and it is the single most interesting thing the trail could record.
So those are written explicitly, by a call in the service that performs the action, through a separate connection that commits independently of the request’s transaction. That last detail is the point. If the audit write lives in the request’s transaction and the request rolls back, the evidence rolls back with it. An audit row for an action that was attempted and rejected is worth more than one for an action that succeeded, and it is precisely the one a shared transaction destroys.
Rules
Make append-only a privilege, not a policy. Revoke UPDATE and DELETE from the role the application connects as, keep them on the role that runs migrations, and assert the granted set in a test rather than trusting the migration ran.
Do not put a soft-delete column on an append-only table. If it is history, it does not get retracted. If it does get retracted, it is not history.
Write down which entity your automatic audit hook cannot see. Any mechanism that decides by inspecting the object has an exception, and the exception is worth finding on purpose rather than during an incident.
Audit sensitive events on a connection that survives a rollback. The failed attempt is the record you will want.
The honest limit: none of this prevents someone with the owner credentials from rewriting history. It was never meant to. It prevents the ordinary path — an ORM, a helpful refactor, a data fix under pressure — from doing it by accident, and that is the path every case in this project actually took.