Skip to Content
Meubles Morinière

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.
cash_chain_hashpackages/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:invariants checks 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.

Why an edit shows in the chain of paymentsThe chain intact : H41 = SHA-256(H40 | C41), H42 = SHA-256(H41 | C42), H43 = SHA-256(H42 | C43). Each link is checked against the hash the previous one carries. The amount of link 42 is edited : H41 = SHA-256(H40 | C41), H42 ≠ SHA-256(H41 | C42′), H43 = SHA-256(H42 | C43). Its hash no longer matches its content. The next morning’s check reports link 42. The hash of link 42 is recomputed to hide it : H41 = SHA-256(H40 | C41), H42′ = SHA-256(H41 | C42′), H43 ≠ SHA-256(H42′ | C43). The break moves to the next link. Everything up to the last link would have to be rewritten, then the closures.The chain intactlink 41contentC41hashH41link 42contentC42hashH42link 43contentC43hashH43daily closurecopies H43H42 = SHA-256(H41 | C42)Each link is checked against the hash the previous one carries.The amount of link 42 is editedlink 41contentC41hashH41link 42contentC42′hashH42link 43contentC43hashH43daily closurecopies H43H42 ≠ SHA-256(H41 | C42′)Its hash no longer matches its content. The next morning’s check reports link 42.The hash of link 42 is recomputed to hide itlink 41contentC41hashH41link 42contentC42′hashH42′link 43contentC43hashH43daily closurecopies H43H43 ≠ SHA-256(H42′ | C43)The break moves to the next link. Everything up to the last link would have to be rewritten,then the closures.
The numbers are an example. The computation is the database trigger’s (cash_chain_hash, Payment_chain_insert) and the verdict is readCashChainBreaks’s, all checked at every build of this documentation. Refunds have their own chain, built the same way.

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).

On this page