Schema design for money and documents

"why not just use a decimal column?"

Design review, hour two. The web lead had my ERD printed out and covered in red pen, the same thing circled on every table carrying an amount: bigint.

The system was an internal ERP for a construction and property development group in South Asia — delivery orders, purchase orders, receipts, bank accounts, loans, employees. I owned the data model, the web team owned the implementation, and that split is its own objection at the end of this post.

His question was reasonable. Postgres numeric is exact, it doesn't drift like a float, so why store 1,250.75 as 125075?

Because Postgres isn't the only thing touching the number.

numeric is exact inside the database and stops being exact the moment it leaves. The Node driver returns it as a string, which is correct, and then somebody writes parseFloat because the chart library wants a number. I found exactly that in a reporting aggregation during the first month of review. It had sat there for weeks — the error hides past the fifteenth digit until one day it doesn't.

The second reason is better. Integer minor units force the rounding decision to happen where the rounding happens. Split 1,000.00 across three cost centres. With numeric you write amount / 3, get a repeating tail, and end up with a rounding rule buried in whichever layer formats it. With minor units you get 33,333 and 33,333 and 33,334, and somebody has to say where the extra paisa goes.

A currency table carries minor_unit per code, so JPY and NPR rows share a column:

create table ledger_entry (
  id           bigint generated always as identity primary key,
  document_id  bigint  not null references document (id),
  account_id   bigint  not null references account (id),
  currency     char(3) not null references currency (code),
  amount_minor bigint  not null,
  created_at   timestamptz not null default now(),

  constraint ledger_entry_amount_nonzero check (amount_minor <> 0)
);

Quantities stayed numeric(18,3) — cement comes in fractional tonnes and there's no minor unit for a tonne. Money is integer, quantity is decimal, and I'll defend both in one review.

"why can't i edit a posted receipt?"

Because a posted document is a claim about something that already happened. Editing it doesn't correct the past, it corrects the record of the past, and those are different operations with different audiences. By the time a receipt is posted, its entries have hit account balances, someone has reconciled a bank statement against them, and a printed copy is in a file. Change the amount in place and the balance moves under everyone who already looked, with nothing saying it did.

Documents have three states and a self-reference:

create type document_status as enum ('draft', 'posted', 'reversed');

create table document (
  id           bigint generated always as identity primary key,
  book_id      smallint not null references document_book (id),
  fiscal_year  smallint not null,
  serial_no    integer,
  doc_no       text,
  status       document_status not null default 'draft',
  posted_at    timestamptz,
  reversal_of  bigint references document (id),

  constraint document_posted_has_number
    check (status = 'draft' or (serial_no is not null and doc_no is not null)),
  constraint document_posted_has_time
    check ((status = 'draft') = (posted_at is null))
);

create unique index document_single_reversal_uq
  on document (reversal_of) where reversal_of is not null;

A correction is a new document reversing the old one, sign-flipped, with its own number and timestamp. The original stays as it was. Anyone reading the account sees both, in order, and can tell what happened and when someone noticed.

Enforcement is a trigger, not a convention, since I couldn't review their code:

create or replace function document_forbid_posted_edit() returns trigger
language plpgsql as $$
begin
  -- the only legal change to a non-draft document is being marked reversed
  if old.status = 'draft'
     or (old.status = 'posted' and new.status = 'reversed'
         and to_jsonb(new) - 'status' = to_jsonb(old) - 'status') then
    return new;
  end if;

  raise exception 'document % is %; post a reversal instead of editing it', old.id, old.status
    using errcode = 'restrict_violation';
end;
$$;

create trigger document_immutable_when_posted
  before update on document
  for each row execute function document_forbid_posted_edit();

Ledger entries got the blunter treatment: revoke update, delete on ledger_entry from app_rw. Insert and select, nothing else. No ORM misconfiguration and no 2am hotfix takes a line out of the ledger. If someone truly needs to, they need a DBA.

"why is the document number not the primary key?"

Three reasons, in order of pain saved.

A draft doesn't have a number yet. Numbers are assigned at posting, so if doc_no were the key, half the table couldn't exist.

Numbers get reformatted. Six months in, they wanted a branch prefix on the serial. Against a text column that's a display change. Against a primary key it's a rewrite of every foreign key in the system.

And the serial resets each fiscal year, per book, so the natural key is really (book_id, fiscal_year, serial_no) — three columns propagating into every child table.

So id is the key, and two partial unique indexes carry the human contract: (book_id, fiscal_year, serial_no) and doc_no, both where ... is not null. Surrogate key for the machines, human key for the humans. The mistake people make is picking one.

"then why is the numbering so complicated?"

Because they wanted gapless numbering per book — purchase orders in one, receipts in another — and Postgres sequences are not gapless.

A sequence burns its value on rollback, by design — that's what makes it non-blocking. If a posting fails validation after nextval, you've got a hole. For most systems a hole is fine. Here a missing receipt number is a question somebody answers to an auditor, and "the database rolled back" doesn't end that conversation.

So the counter is a row in document_counter, keyed (book_id, fiscal_year), and posting locks it:

begin;

update document_counter
   set next_serial = next_serial + 1
 where book_id = $1 and fiscal_year = $2
returning next_serial - 1 as serial_no;

-- insert document, lines, ledger entries, all in this transaction

commit;

The update ... returning takes a row lock every other posting in that book waits behind until commit. That's the point, and it bounds throughput per book by how long the transaction takes.

Which is where this went wrong.

The first implementation called the notification service inline, inside that transaction, so posting a receipt notified the site engineer. I built that notification service myself, in Node and Express, and it is not fast — it fans out to email and SMS and renders a template. Posting p95 went from about 40ms to 1.9 seconds. During a week when our email provider throttled us, one project's receipts book effectively stopped. Storekeepers staring at spinners because a mail queue was backed up.

The fix was an outbox_event row written in the same transaction — document id, event type, jsonb payload, nullable published_at — drained by a worker outside it. The rule since: nothing that can block on a network sits inside a transaction holding a counter lock.

Afterwards, postings ran at about 40 per second per book on staging, against a real peak under three. There was never a throughput problem. There was a "we put an HTTP call in a critical section" problem, and those look identical on a dashboard until you read the code.

"what happens when two people receive the same delivery?"

Two different failures hide in that question.

The first is over-receipt: two receipts against one delivery order line, each valid alone, summing to more than was ordered. Do the arithmetic in SQL, so the constraint sees the real new value:

create table delivery_order_line (
  id                 bigint generated always as identity primary key,
  delivery_order_id  bigint not null references delivery_order (id),
  item_id            bigint not null references item (id),
  ordered_qty        numeric(18,3) not null check (ordered_qty > 0),
  -- frozen at approval; includes the category's over-delivery tolerance
  max_receivable_qty numeric(18,3) not null,
  received_qty       numeric(18,3) not null default 0,

  constraint do_line_within_tolerance check (received_qty <= max_receivable_qty)
);

update delivery_order_line
   set received_qty = received_qty + $2
 where id = $1;

Read-modify-write in Node loses the second update. received_qty + $2 doesn't — the update takes the row lock itself and the check constraint sees the post-increment value. Of two concurrent receipts, the one that breaks tolerance fails loudly instead of quietly over-receiving.

max_receivable_qty is frozen at approval rather than computed live, because over-delivery tolerance for bulk aggregate is real and the percentage on an item category changes. A DO approved last year gets judged by last year's rule.

The second failure is the one no constraint caught. A storekeeper on a bad mobile connection tapped submit four times. Four receipts, each within tolerance, all legitimate as far as the schema knew. Nothing said "these are the same event."

That's an idempotency key, and it lives in the database because the client is the only thing that knows the four taps were one intent. A request_idempotency table, key as primary key, nullable document_id, written inside the posting transaction. The key is generated on the device when the form opens, not when it submits. That distinction is the whole trick.

I'd love to say I designed it in from the start. It shipped the week after the four receipts.

"why are you denormalising the reporting tables?"

The ledger is normalised and it's the source of truth. Nothing below changes that.

But the delivery order list screen joined nine tables to render one row — supplier, project, site, item summary, receipt status, approval chain, currency, creator, document — and took 2.8 seconds for 50 rows. That screen is the first thing a site manager opens in the morning.

So there's a flat document_index for list screens, and balance snapshots for reports:

create table account_balance_snapshot (
  account_id          bigint not null references account (id),
  period              date   not null,
  closing_minor       bigint not null,
  built_from_entry_id bigint not null,

  primary key (account_id, period)
);

Two rules govern every denormalised table there. It has to be rebuildable by one deterministic job from the normalised tables alone. If you can't write that job, you haven't denormalised — you've forked your data, and one copy will be wrong.

And it has to carry a watermark. built_from_entry_id is the highest ledger entry in the snapshot. A nightly check re-sums the ledger to that entry, asserts it equals closing_minor, and pages if it doesn't. Without the watermark you can't ask the question — you're comparing against a moving target, and drift is always explainable.

The list screen went from 2.8s to 240ms. The check has fired twice, both times on a backfill inserting entries below the watermark, and both times at 4am from a job rather than 11am from a user.

Where I stop: anything read once a quarter stays normalised and takes its four seconds.

"you're not writing this code — why do you get to decide?"

It came at the end of a long review. Fair question.

I didn't write a line of the application. The web team built it in a stack I don't work in and can't meaningfully review. I owned the architecture and the data model, and sat in reviews while they implemented against it. That's the whole of my involvement, and I'd rather say so than let anyone assume otherwise.

My answer then is my answer now: the schema outlives the code. That application will be rewritten, and whatever replaces it points at the same tables. Constraints and triggers survive a team change, a framework change, and an ORM that thinks it knows better. I couldn't review their service layer. I could review the DDL, and Postgres applies it whether or not anyone remembers why.

The cost of that landed on them. Somebody spent days mapping Postgres error codes into messages a storekeeper can act on, because restrict_violation means nothing to anyone. That work existed because of my decision, and I hadn't estimated it.

Loans are where I got it wrong outright. My original design mutated installment rows in place when a loan was restructured, so postings made against the old schedule stopped tying out to anything. Two people spent a week rebuilding six months of history from ledger entries and printed statements. The fix was obvious once we'd paid for it. Version the schedule, never edit it:

create table loan_schedule_version (
  id             bigint generated always as identity primary key,
  loan_id        bigint not null references loan (id),
  version_no     smallint not null,
  effective_from date   not null,

  unique (loan_id, version_no)
);

Installments hang off a version, and every posting carries the schedule_version_id it was made against. Same immutability argument as documents. I'd written that rule down for receipts and failed to apply it one table over, which is how I usually get things wrong: the principle is written down, and I don't notice the new case is the same case.

The bigger correction, if I started that engagement again: I'd write a reference implementation of the posting transaction myself, in any language, as an executable spec. Not a sequence diagram and two pages of prose. The two nastiest bugs of the first quarter were both the web team implementing my design exactly as written, where what I'd written was ambiguous.