Skip to main content

ADR-009: No DELETE on financial rows — reversals only

  • Status: Accepted
  • Date: 2026-05-23
  • Deciders: CTO / Founding Engineering / Ledger team

Context​

In a regulated financial system, the historical record must be reconstructible at any later point — for disputes, audits, regulator inquiries, and internal investigations. A DELETE destroys that record.

Soft-delete (deletedAt column) is no better for financial rows: it removes the row from queries and breaks reconciliation, while still letting the data resurface through joins or projections in unpredictable ways.

Decision​

  • No DELETE FROM statement, ever, in migrations or app code, targeting any financial table:
    • journal_entry, transaction, wallet_transaction, payment, disbursement,
    • payroll, payroll_entry, settlement_batch, settlement_record,
    • any audit table.
  • "Cancelling" a transaction is posting a reversal — a new transaction whose entries are the original's mirror, plus a link to the original via reverses_transaction_id.
  • Status changes (e.g. mark a Payroll as CANCELLED) are allowed via UPDATE for non-financial state machines; the financial entries underneath them are still immutable.
  • Non-financial entities (e.g. Department, Role) may use soft-delete via deletedAt if there's a real business need. They are not what this ADR governs.

Alternatives considered​

  • Soft-delete with deletedAt. Rejected for financial rows — discussed above.
  • Allow DELETE during a "draft" status. Rejected — drift between "draft" and "posted" semantics is its own bug surface; better to require explicit reversal even during drafts.
  • Allow admin-only DELETE for "test data." Rejected. Test data lives in test databases.

Consequences​

Positive

  • The audit record is complete by construction.
  • Reconciliation works.
  • A regulator's "show me everything that happened to account X" query is one WHERE clause.

Negative

  • Tables grow monotonically. Negligible at expected scale; partitioning is the answer if it ever isn't.
  • Test fixtures sometimes want to "clear" data — they target test schemas only, not the migrations under audit.

Follow-ups​

  • scripts/ci/adr-guards.sh (wired into .github/workflows/ci.yml as the adr-guards job) fails the build on any prisma.<financialModel>.delete*() call in apps/packages/services production code. (Note: this superseded the originally-planned tooling/prisma-lint/, which was never built.)
  • Ledger service rejects any internal SQL that contains DELETE against journal tables (codified inside the Go service).
  • A future migration adds a Postgres REVOKE DELETE from the application role on financial tables, as a defense-in-depth.