Engineering choicesTechnical case studies4 min read
Payments that cannot be edited
French law wants point-of-sale software to make any edit visible. So the database chains each payment to the previous one, and here’s how.
The problem
The ERP records what private customers pay in the store: cash, cheque, card, bank transfer. Whatever the payment method, the French General Tax Code treats that as a point-of-sale system (article 286, I, 3° bis). Its records have to be unalterable and secured. If you change or delete something, it has to leave a trace, for example with chaining. And periodic closures have to freeze their state (BOI-TVA-DECLA-30-10-30).
The database already refused to modify or delete a payment: triggers reject UPDATE, DELETE and TRUNCATE. Except a trigger can be disabled, and disabling it leaves no trace in the data. So an edit had to show after the fact, even when it’s made with full privileges.
The constraints
- The proof has to work against the owner of the database too, not only against the application’s code. Whoever can disable a trigger can also rewrite a row.
- Money gets written through several paths: in-store sales, later payments on a sale, the Stripe webhook, refunds made in the store or announced by Stripe. If each path had to apply the guarantee itself, the next one would forget it.
- A refund changes status after it’s created, when Stripe confirms or rejects it. Its amounts don’t move anymore, though. So the proof should only cover what no longer changes.
- Two payments can arrive at the same time. A sequence number can’t be taken twice, and you can’t skip one.
- Periodic closures have to freeze the state of the till at their date.
The alternatives
- Keep only the triggers. They block the ordinary edit, but when you disable them, it leaves no trace.
- Use third-party point-of-sale software. Payments would then live outside the ERP, away from the orders and invoices they settle.
- Compute the chain in the application. Every write path would have to do the computation. A script or a migration that forgets it would write a wrong link, with no error.
- Anchor the chain outside the database, with a timestamp or a signature from an external key. That also protects against a complete rewrite, but it means one more service and one more secret.
- Compute the chain in the database, at insertion, whichever path writes.
The decision
The database computes the chain, in a trigger, when each payment and each refund is created. It gets recomputed every morning, and after every migration applied to prod.
The implementation
When a payment or a refund is created, it gets two values that the database computes itself (migration 20261025000100_encaissements_chaines):
- A sequence number with no gaps (
chainSequence). - A SHA-256 hash (
chainHash) of its content (identifier, order, method, amount, dates in UTC to the millisecond), chained to the hash of the previous link.
packages/db/prisma/migrations/20261025000100_encaissements_chaines/migration.sql · lines 32–37-- L'empreinte d'un maillon : celle du précédent, puis le contenu du maillon. Les dates sont
-- écrites en UTC, au millième, pour ne pas dépendre du fuseau de la session.
CREATE FUNCTION "cash_chain_hash"(previous text, payload text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
SELECT encode(sha256(convert_to(coalesce(previous, '') || '|' || payload, 'UTF8')), 'hex')
$$;The computation is in a trigger, so outside the application. The columns have a default value, the code has nothing to supply, and the trigger always replaces that value. The hash only covers what no longer changes. A refund’s status can still move, but not its amounts, so its link stays valid. So that two simultaneous payments don’t take the same number, an advisory lock per table makes insertions go one after the other. At the volume of one store, this lock costs nothing.
Daily, monthly and yearly closures pick up the last hash of the chain. You can’t modify them either.
Nobody has to remember to verify it.
- Every morning, the daily check recomputes both chains. If a link is broken, the step fails and the alert goes out (
cash-closure.service.ts). pnpm db:invariantschecks that the database has its guards and that the chain is intact. The script that applies migrations to prod runs it again after each one.
If you touch a row, the chain breaks from that row on. Its hash no longer matches its content. And if you recompute the hash to hide the edit, it’s the next link’s hash that no longer matches.
The results
During the migration, the existing payments were chained in the order they’d been recorded. Since then, if someone modifies a row, the daily check reports it the next morning, even with the owner’s privileges and the triggers disabled.
The limits
- Chaining makes an edit visible, but not impossible. If someone rewrites the whole chain from the edited link on, then the closures that pick up its hash, they get a consistent chain. In that case, a copy of the database kept elsewhere is what you compare against.
- Only money collected and money returned are chained. Invoices are protected another way: database guards, gapless numbering and a PDF archived with its hash (Invoicing).