1 Ledger-First Data Modeling

One decision in your ERP's data model outranks every other one: inventory and financial state are derived from an append-only log of events, never stored as mutable truth. This chapter builds that architecture concretely: an inventory transaction ledger, a rebuildable balance cache, double-entry money, versioned documents, and state machines enforced in both TypeScript and Postgres. It also explains why every serious system of record (banks, Stripe, Uber's payments platform, TigerBeetle) converges on the same shape.

What you need to know first

That paragraph is written in the dialect of people who have already built one of these systems. Let's translate it, from zero.

An ERP (Enterprise Resource Planning system) is the software a company runs its operations on: what products exist, what's in the warehouse, who ordered what, who owes us money. Ours serves an apparel brand. Three acronyms recur:

• a SKU (Stock Keeping Unit, said "skew") is one exact sellable thing, such as "core tee, black, medium";
• ATS (available-to-sell) is how many units a salesperson may promise today;
• a PO (Purchase Order) is a request for goods. You send them to factories, and retailers send them to you.

A database is a program that remembers things

A database is a program whose entire job is to store data on disk and answer questions about it. You do not open it the way you open a file. It runs continuously as a service on a server, a computer whose job is answering other computers' requests.

Ours is Postgres (PostgreSQL), a free, superb database. Supabase is a company that will run one for you. When this chapter says "Postgres does X," the database itself enforces X. Your code can have bugs, but a rule living inside the database applies to every program that ever connects, forever.

Tables, rows, columns

A database organizes data into tables. A table is a grid, like one sheet in a spreadsheet: named columns (the vertical strips: color, size, quantity) and rows (the horizontal lines, one per thing recorded). One row is one record: one SKU, one shipment, one order.

Unlike a spreadsheet, every column has a declared type the database enforces: a column declared integer refuses to hold the word "banana." The overall shape of your tables (names, columns, types, rules) is the schema, and it is the decision you are least likely to undo later.

SQL: the language you talk to the database in

SQL (say "sequel", for Structured Query Language) is how you ask a database questions and tell it to store things. Four verbs appear constantly:

• CREATE TABLE defines a new table: name, columns, types, and the rules rows must obey. Commands in this category are called DDL, Data Definition Language. They define the shape of the container. Other commands fill it.
• INSERT adds a new row. "Here is a new fact; write it down."
• UPDATE changes an existing row in place. The old values are overwritten and gone. This chapter is largely an argument about when you may do it.
• SELECT reads. "Give me these columns, from this table, for the rows matching this condition." It never changes anything.

Also: DELETE removes rows outright, and SUM(...) is an aggregate function, adding a column up across matched rows.

Keys: how rows point at other rows

A primary key is the column (or columns) uniquely identifying a row, its permanent name badge. No two rows may share one, it never changes, and the database uses it to find that row instantly.

A foreign key is a column holding another table's primary key, creating a link. If a shipment row stores a SKU's key in sku_id, that's a foreign key: this shipment is of that SKU. Declaring it (references skus(id)) makes the database enforce the link. It rejects a shipment pointing at a SKU that doesn't exist, and refuses to delete a SKU that shipments still reference. Broken pointers become impossible.

A UUID (Universally Unique Identifier) is a long random-looking value any machine can generate on its own, with effectively zero chance of collision. The alternative is a counter handing out 1, 2, 3, …: ordered, but only the database can issue one.

Transactions: all or nothing

A transaction is a group of changes that either all happen or none do. Think of a shopping trip where everything in the cart gets bought or you walk out with nothing, never a state where you paid for the shoes but not the socks. You open a transaction, run several statements, then commit (make it permanent) or roll back (discard it). If the power fails mid-way, Postgres rolls back for you.

Real operations touch several tables: shipping an order writes a stock movement, updates a running total, flips a status. If the server dies after step one, you get zero of the three, not one. The properties that make this trustworthy abbreviate to ACID (Atomicity, Consistency, Isolation, Durability):

• all-or-nothing,
• rules always hold,
• concurrent transactions don't see each other's half-finished work,
• committed data survives a crash.

Append-only, and what a ledger is

Mutable means changeable. Immutable means it can never be modified once created. Append-only is the discipline of a table where INSERT is allowed but UPDATE and DELETE are forbidden: add lines at the bottom, never revise or erase old ones. Wrong entry? You don't fix it. You add a line that cancels it out, and both stay visible forever.

A ledger is exactly that: a checkbook register you never erase. Each line is one event (date, amount, why, who), and the balance is written nowhere. You get it by adding the lines up. That is the idea the rest of this chapter builds on. Last piece of plumbing: a migration is a saved SQL file making one schema change, run in order so every copy of your database (laptop, test, production) ends up identical.

1.1 The number you can't trust

Here is the schema almost everyone writes first for an apparel ERP:

create table skus (
  id       uuid primary key,
  style_id uuid not null,
  color    text not null,
  size     text not null,
  on_hand  integer not null default 0   -- the trap
);

Line by line:

• create table skus ( makes a new table called skus. Everything in the parentheses is a column definition.
• id uuid primary key gives every row its own unique identifier, stored as a UUID. Marking it primary key tells Postgres to reject duplicates and to build a fast lookup path to any row by its id.
• style_id uuid not null records which parent style this SKU belongs to ("Core Tee"). not null means the column may never be left empty, so an insert without it fails.
• color text not null and size text not null are free text, required. Together with the style, these are what make this SKU a specific sellable thing.
• on_hand integer not null default 0 is a whole number holding how many units we supposedly have. default 0 means new rows start at zero if nobody says otherwise. The comment marks it as the trap, and the rest of this chapter explains why.

Two dashes (--) start a comment: a note for humans that the database ignores.

The update that overwrites the old number

And here is the line of code that will eventually cost you a customer:

update skus set on_hand = on_hand - 48 where id = $1;

In English: find the one row in skus whose id matches the value supplied, take whatever number is currently in its on_hand column, subtract 48, and write the result back into that same column. The $1 is a placeholder: the application supplies the actual identifier separately, which is both safer and faster than pasting it into the text of the query. The critical word is back: the new number lands on top of the old one, in the same cell.

It looks innocent. It carries three separate disasters, each waiting on its own schedule.

Disaster one: a number you cannot explain

Disaster one: lost information. The moment that UPDATE commits, the previous value is gone. When your customer asks "why does the system say we have 417 units of the black medium core tee when the warehouse just counted 177?", you have a single number and no way to explain it.

Was it a mis-keyed receipt? A shipment recorded twice? A return that never got entered? You cannot answer, because you stored the answer and threw away the question. In accounting terms, you stored a balance without a ledger.

Modern Treasury's "Accounting for Developers" states the correct posture directly: "While mutating a balance directly is more efficient and simpler to implement, it's more accurate to store immutable transactions and always compute balances from those transactions."

Disaster two: drift with no self-audit

Disaster two: silent drift. Every bug in every code path that touches on_hand permanently corrupts the number, and nothing detects it: a retried webhook that decrements twice, a crash between decrementing stock and creating the shipment record, an admin "quick fix" in the Supabase dashboard. (A webhook is an automatic notification another service sends you when something happens on its end. Senders retry when unsure you got it, so the same message can arrive twice.)

Mutable state has no self-audit: since the column holds only its current value, there is nothing to compare it against. Drift accumulates until a physical count reveals it, and then you have no idea which of ten thousand mutations went wrong.

Disaster three: row locks and races

Disaster three: contention and races. UPDATE ... SET on_hand = on_hand - 48 takes a row lock on the SKU's balance row. While one transaction is changing that row, Postgres makes everyone else who wants to change it wait in line, because letting two writers edit the same cell simultaneously would corrupt it.

Two wholesale orders allocating the same hot SKU serialize on that lock. Under load, a popular style during market week (the few weeks each season when buyers gather at trade shows and write most of their orders) becomes a hot row that every writer queues behind.

Worse, the read-check-write variant ("SELECT on_hand, check it's enough, then UPDATE") is a textbook race condition, a bug where the outcome depends on the accidental interleaving of two things happening at once. Two order-entry sessions both read 50, both decide 48 is fine, both commit, and you have promised 96 units out of a stock of 50. Neither session did anything wrong on its own. The damage happens in the gap between reading and writing.

1.2 The inventory ledger

The fix is to stop storing the balance as truth and start storing what actually happened. Every physical event that changes stock becomes one immutable row in an append-only table: a receipt from the factory, a shipment against an order, a warehouse transfer, a damage write-off, a cycle-count correction. (A cycle count is a rolling spot-check in which staff physically count part of the warehouse and compare it with the system.) The balance is a SUM.

create type inventory_txn_type as enum (
  'receipt',          -- goods in from a PO / production run
  'shipment',         -- goods out against a wholesale order
  'adjustment',       -- damage, shrinkage, samples, gifting
  'transfer_out',     -- leaving one location...
  'transfer_in',      -- ...arriving at another
  'count_correction'  -- reconciling to a physical count
);

create table inventory_transactions (
  id             bigint generated always as identity primary key,
  tenant_id      uuid    not null references tenants(id),
  sku_id         uuid    not null references skus(id),
  location_id    uuid    not null references locations(id),
  txn_type       inventory_txn_type not null,
  qty_delta      integer not null check (qty_delta <> 0),
  reason_code    text    references adjustment_reasons(code),
  ref_type       text,             -- 'purchase_order' | 'order' ...
  ref_id         uuid,             -- the document that caused this event
  transfer_group uuid,             -- pairs the two legs of a transfer
  actor_id       uuid    not null references users(id),
  occurred_at    timestamptz not null default now(),  -- when it happened
  recorded_at    timestamptz not null default now(),  -- when we learned

  -- the type dictates the sign; nonsense combinations are unrepresentable
  constraint sign_matches_type check (
    (txn_type = 'receipt'      and qty_delta > 0) or
    (txn_type = 'shipment'     and qty_delta < 0) or
    (txn_type = 'transfer_out' and qty_delta < 0) or
    (txn_type = 'transfer_in'  and qty_delta > 0) or
    (txn_type in ('adjustment', 'count_correction'))
  ),
  -- adjustments must say why
  constraint adjustment_needs_reason check (
    txn_type not in ('adjustment', 'count_correction')
    or reason_code is not null
  ),
  -- transfers must travel in pairs
  constraint transfer_needs_group check (
    (txn_type in ('transfer_out', 'transfer_in'))
      = (transfer_group is not null)
  )
);

create index idx_inv_txn_balance
  on inventory_transactions (tenant_id, sku_id, location_id, occurred_at);

Enums, columns, and one index

That is the biggest block in the chapter, so take it in three parts.

The first statement creates an enum, short for enumerated type: a custom column type whose value must be one of a fixed list you write down in advance. Here the list is the six kinds of stock movement that can exist. Any column declared inventory_txn_type will accept 'receipt' but reject 'reciept', 'RECEIPT', or 'misc'. A typo becomes an error at write time instead of a category of ghost rows nobody notices for a year.

The second statement creates the ledger table itself. Column by column:

• id bigint generated always as identity primary key is the row's unique name badge, but here it's a big whole number that Postgres issues in ascending order (1, 2, 3, …) rather than a random UUID. generated always means the application is not permitted to supply it. Ascending IDs are useful later: a bigger id means "written after."
• tenant_id uuid not null references tenants(id) records which customer's data this is. Our ERP is multi-tenant: many separate brands share one database, and every row must state whose it is. references tenants(id) is the foreign key, so the value must match a real row in tenants.
• sku_id and location_id are foreign keys to what moved and where (which warehouse or store).
• txn_type inventory_txn_type not null names which of the six kinds of movement this is, required on every row.
• qty_delta integer not null check (qty_delta <> 0) is the change in units, signed: +240 for goods arriving, -48 for goods leaving. "Delta" just means "change in." A constraint is a rule the database refuses to break, and a check constraint is one written as a true/false test on the row. <> means "not equal to," so this one bans zero, because a movement of zero units records nothing and almost always means a bug upstream.
• reason_code text references adjustment_reasons(code) gives the why, for the movements that need a why ("damaged", "sample", "shrinkage", the trade's word for stock that disappears without a sale, through theft, breakage, or miscounting), drawn from a controlled list rather than free text.
• ref_type and ref_id record which business document caused this: the kind of document, and its identifier. Together they point at the purchase order, the wholesale order, or the count sheet.
• transfer_group uuid is a shared identifier that ties together the two halves of a warehouse-to-warehouse transfer.
• actor_id uuid not null references users(id) names which human (or service account) caused this. Required, always.
• occurred_at and recorded_at are two timestamptz columns, meaning "timestamp with time zone": a moment in time stored unambiguously no matter where the user sits. default now() fills in the current time if nobody supplies one. Section 1.6 is entirely about why there are two.

Then three named check constraints, each preventing a specific class of nonsense:

• sign_matches_type refuses a receipt with a negative quantity or a shipment with a positive one;
• adjustment_needs_reason refuses an adjustment with an empty reason_code (is not null = "has a value");
• transfer_needs_group uses an equals sign between two true/false expressions to say "these must agree": a transfer leg must have a group, and a non-transfer must not.

The third statement creates an index. An index is a secondary lookup structure the database maintains alongside the table, like the index at the back of a book. Without it, answering "all rows for this SKU at this location" means reading every row in the table. With it, Postgres jumps straight to the matching ones. The column order matters: this index is built to serve exactly the balance query we're about to write.

What the constraints guarantee

Read the constraints carefully, because they carry most of the design's weight. Every row answers the auditor's questions by construction:

• what moved (sku_id, qty_delta),
• where (location_id),
• why (txn_type, reason_code),
• because of which document (ref_type/ref_id: the PO, the wholesale order, the count sheet),
• who (actor_id),
• and when (occurred_at).

The sign-per-type check means a bug cannot record a positive shipment. The reason-code check means nobody can shave 200 units off inventory without leaving a "why" behind.

Notice also the transfer design. A transfer is two rows, an out-leg and an in-leg sharing a transfer_group, so units are never in limbo and total stock across locations is conserved.

This is double-entry thinking applied to physical goods: the bookkeeping rule that nothing is ever created or destroyed, only moved, so every event is recorded twice, once where value left and once where it arrived. It's the same invariant TigerBeetle, a database built specifically for financial transactions, enforces for money. (An invariant is a statement that must be true before and after every operation, forever.) Every movement of value is from somewhere, to somewhere.

Enforcing append-only in the database

Now make it actually append-only. In Postgres you enforce immutability with privileges and triggers:

-- Supabase: application roles can insert and read, never rewrite
revoke update, delete on inventory_transactions from authenticated;

alter table inventory_transactions enable row level security;
create policy tenant_isolation on inventory_transactions
  using (
    tenant_id = ((select auth.jwt()) -> 'app_metadata'
                 ->> 'tenant_id')::uuid
  );

-- belt and braces: even privileged code paths hit a wall
create function forbid_ledger_mutation() returns trigger
language plpgsql as $$
begin
  raise exception
    'inventory_transactions is append-only (attempted %)', tg_op;
end $$;

create trigger inventory_txn_immutable
  before update or delete on inventory_transactions
  for each row execute function forbid_ledger_mutation();

Three defenses, from outermost to innermost:

• revoke update, delete ... from authenticated works on Postgres roles (named identities with permissions) and privileges (what each role is allowed to do). authenticated is Supabase's role for any logged-in user of your app. This line takes away that role's permission to modify or remove ledger rows at all. Inserting and reading still work.
• enable row level security plus create policy. Row level security (RLS) makes the database filter which rows a given user can even see or touch, automatically, on every query. The policy's using (...) clause is the test: the row's tenant_id must equal the tenant named in the caller's JWT (JSON Web Token, the signed, tamper-evident credential a logged-in user's browser sends with each request). Three details in that one line. -> digs into a nested object inside the token and ->> pulls a text field out of it, and ::uuid converts that text to a UUID so the comparison is like-for-like. The tenant id is read from app_metadata because Supabase exposes two claim bags through auth.jwt() and only that one is out of the user's reach. user_metadata can be edited by the signed-in user, so authorization data must never live there. (You put tenant_id into the token yourself, with a custom access token hook, when the user signs in.) Wrapping the call as (select auth.jwt()) lets Postgres evaluate it once per statement instead of once per row, which Supabase's own benchmarks show is a large speed-up on big tables. Since the policy names no command, it applies to all of them, and because it declares no separate with check clause, Postgres applies the same test to rows being inserted. Net effect: brand A physically cannot read or write brand B's inventory, even if your application code forgets to filter.
• The function plus trigger. A function here is a small program stored inside the database (plpgsql is Postgres's built-in language for them, and the $$ pairs just mark where the body starts and ends). A trigger tells Postgres to run that function automatically whenever a certain event happens on a table. This one fires before any update or delete on the ledger and immediately raise exceptions, throwing an error that aborts the whole transaction. tg_op is a built-in variable holding the operation name, so the error message says which was attempted. Because the trigger lives in the database, it applies to every connection: your app, a migration script, a person typing in a dashboard at midnight.

A superuser (the administrator account with unlimited rights) can still drop the trigger, so what you have is tamper-resistance: enough to stop every ordinary accident and most deliberate edits, and short of a cryptographic proof. No application bug, no ORM surprise (an Object-Relational Mapper is a library that lets you work with rows as if they were ordinary objects in your programming language, writing the SQL for you, which is convenient and occasionally too clever), and no well-meaning dashboard edit can rewrite history.

Mistakes are corrected the way accountants have corrected them since the Italian merchant houses of the late 1200s: with a new, compensating entry (adjustment with reason entry_error, referencing the bad row), so the mistake and its correction are both permanently visible.

Balances become queries

Two examples: the balance right now, and the balance as it stood on a date in the past.

-- current on-hand for one SKU at one warehouse
select coalesce(sum(qty_delta), 0) as on_hand
from inventory_transactions
where tenant_id = $1 and sku_id = $2 and location_id = $3;

-- on-hand as it stood the moment March 1 began
select coalesce(sum(qty_delta), 0) as on_hand_march_1
from inventory_transactions
where tenant_id = $1 and sku_id = $2 and location_id = $3
  and occurred_at < '2026-03-01';

The first query says: look in inventory_transactions; keep only the rows where the tenant, SKU, and location match the three supplied values; add up the qty_delta column across those rows; call the result on_hand. coalesce(x, 0) means "use x, unless it's empty, in which case use 0," which is necessary because summing zero rows returns null (the database's word for "no value"), and a brand-new SKU should read as 0 rather than as nothing.

The second query is identical with one more filter: only count movements that happened before March 1 began. Same table, same arithmetic, different slice of history.

Look again at that second query. Point-in-time state ("what did we have on hand when we confirmed the Nordstrom order?") is a WHERE clause. In the mutable-column world it is unanswerable.

1.3 The balance cache: fast, but never authoritative

"Balance is a SUM" worries people, and the worry is fair: summing millions of rows on every ATS check would be slow. So you materialize it. To materialize a computed value means to calculate it once and store the answer, so later readers get it instantly instead of recomputing.

The stored copy is a cache: a fast duplicate of something you could always work out again from the original. Everything in this section rests on that last clause. This materialization is a cache of the ledger, maintained transactionally and rebuildable from scratch, never an independent source of truth.

create table inventory_balances (
  tenant_id   uuid not null,
  sku_id      uuid not null,
  location_id uuid not null,
  on_hand     integer not null default 0,
  as_of_txn   bigint,                     -- last ledger row folded in
  updated_at  timestamptz not null default now(),
  primary key (tenant_id, sku_id, location_id)
);

create function apply_txn_to_balance() returns trigger
language plpgsql as $$
begin
  insert into inventory_balances as b
    (tenant_id, sku_id, location_id, on_hand, as_of_txn)
  values
    (new.tenant_id, new.sku_id, new.location_id, new.qty_delta, new.id)
  on conflict (tenant_id, sku_id, location_id) do update
    set on_hand    = b.on_hand + excluded.on_hand,
        as_of_txn  = excluded.as_of_txn,
        updated_at = now();
  return null;
end $$;

create trigger maintain_balance
  after insert on inventory_transactions
  for each row execute function apply_txn_to_balance();

-- the cache is disposable; this function is the proof
create function rebuild_balances(p_tenant uuid) returns void
language sql as $$
  delete from inventory_balances where tenant_id = p_tenant;
  insert into inventory_balances
    (tenant_id, sku_id, location_id, on_hand, as_of_txn)
  select tenant_id, sku_id, location_id, sum(qty_delta), max(id)
  from inventory_transactions
  where tenant_id = p_tenant
  group by tenant_id, sku_id, location_id;
$$;

The table, the trigger, and the rebuild

Three pieces again.

The table holds one row per combination of tenant, SKU, and location, storing the running total in on_hand, the id of the last ledger row folded into it (as_of_txn), and when it last changed. Its primary key is composite (three columns together), which is what guarantees there can only ever be one balance row per SKU per location. That uniqueness is what the next piece depends on.

The function and its trigger keep the cache current. The trigger fires after insert on the ledger, once per new row. Inside the function, new is a variable holding the row that was just inserted.

The function then performs an upsert, an insert that turns into an update when the row already exists. Read it as: try to insert a fresh balance row using this movement's quantity, but on conflict with the existing primary key, instead do update. That means adding the incoming quantity to the stored total (excluded is Postgres's name for the row you tried to insert), recording the highest ledger id folded in so far, and stamping the time. return null is the correct return for an after trigger, and its value is ignored.

The rebuild function takes one argument, a tenant's id (p_tenant), and returns nothing (void). It deletes that tenant's entire cache, then recomputes it in a single pass: read all their ledger rows, group by tenant/SKU/location (which collapses the rows into one group per combination), and for each group insert the sum of the quantities plus the max (highest) row id.

This function is idempotent: running it twice in a row leaves the database in exactly the same state as running it once, because it always rebuilds from the source rather than adjusting what's there. Its existence is the proof that the cache is disposable. If you can regenerate it on demand, it can never be the thing you're afraid of losing.

Consistency, contention, and when to worry

Because the trigger runs inside the inserting transaction, the cache is always consistent with committed ledger rows, with no eventual-consistency window to reason about. (Eventual consistency is the arrangement where a copy of the data catches up shortly after the original changes. It is convenient at scale, but it means readers can briefly see stale numbers, and you have to design for that everywhere.)

Yes, the upsert reintroduces a per-SKU hot row. The difference from section 1.1 is that correctness no longer depends on it. If contention on a viral style ever becomes a real bottleneck, you can change how the cache is maintained by batching the updates, or by keeping a few running counters per SKU and doing the final arithmetic at read time.

That second approach is how Modern Treasury's ledger works: it stores five aggregate fields per account and computes the posted, pending, and available balances only when someone asks for them. Either way, the source of truth is untouched. Until you have that problem, don't build for it: a trigger-maintained cache in Postgres comfortably handles an apparel brand doing thousands of movements a day.

A nightly reconciliation query

The cache also gives you a free integrity monitor. Run this nightly and page yourself if it returns rows:

-- any row here means a bug wrote to the cache directly. The ledger wins.
select b.tenant_id, b.sku_id, b.location_id,
       b.on_hand as cached, coalesce(l.actual, 0) as actual
from inventory_balances b
left join (
  select tenant_id, sku_id, location_id, sum(qty_delta) as actual
  from inventory_transactions
  group by tenant_id, sku_id, location_id
) l using (tenant_id, sku_id, location_id)
where b.on_hand is distinct from coalesce(l.actual, 0);

This is a reconciliation query: it computes the truth the slow way and compares it against the cache. A join combines two sets of rows by matching them on shared columns. Here the right-hand side is a subquery (a query in parentheses used as if it were a table) that sums the whole ledger, grouped per tenant/SKU/location, aliased as l.

left join means "keep every row from the left side (b, the cache) even if the right side has no match," which is exactly the case you most want to catch: a cached balance for a SKU with no ledger rows behind it at all. using (...) is shorthand for matching on identically named columns.

The final where keeps only rows where the two numbers disagree. is distinct from is a comparison that treats null sensibly (plain <> against a null returns null, not true, and the row would slip through). An empty result means the cache and the ledger agree everywhere. Any row at all means a bug wrote to the cache directly, and the ledger is the copy you trust: rebuild the cache from it and go find the bug.

ATS is a view, not a column

The number your sales team actually cares about, ATS, available-to-sell, is a view over the balance cache and the open documents. No column anywhere stores it. A view is a saved query that you can then read from as though it were a table. It stores no data of its own, and it re-runs every time you select from it, so it can never go stale.

On-hand comes from the ledger; allocations (units already promised to confirmed orders) come from confirmed order lines; inbound comes from open POs. Derived all the way down:

create view sku_ats as
select
  b.tenant_id, b.sku_id, b.location_id,
  b.on_hand,
  coalesce(a.qty, 0)  as allocated,
  coalesce(p.qty, 0)  as on_order,
  b.on_hand - coalesce(a.qty, 0) as ats   -- conservative: sell what exists
from inventory_balances b
left join lateral (
  select sum(ol.qty) as qty
  from order_lines ol
  join order_revisions r on r.id = ol.revision_id
  join orders o on o.id = r.order_id
              and o.current_revision = r.revision_no
  where o.tenant_id = b.tenant_id and ol.sku_id = b.sku_id
    and o.status in ('confirmed', 'allocated') and o.voided_at is null
) a on true
left join lateral (
  select sum(pl.qty_open) as qty
  from purchase_order_lines pl
  join purchase_orders po on po.id = pl.po_id
  where po.tenant_id = b.tenant_id and pl.sku_id = b.sku_id
    and po.status = 'open'
) p on true;

The outer query starts from every row of the balance cache and attaches two computed numbers to it. Each is a left join lateral. Lateral is the keyword that lets a subquery refer to columns from the row it's being attached to (notice b.sku_id appearing inside), so each subquery runs "for this SKU, at this location." on true just means there's no extra matching condition, because the lateral subquery already did the matching.

• Subquery a (allocated) adds up the quantities on order lines that belong to the current revision of orders in status confirmed or allocated that have not been voided. The chain of joins walks lines → revision → order, and the condition o.current_revision = r.revision_no is what keeps superseded revisions out of the total. Section 1.7 explains why orders have revisions at all.
• Subquery p (on order) adds up the still-outstanding quantities on purchase order lines belonging to POs that are still open: goods you've asked the factory for but haven't received.
• The ats column is deliberately conservative: on-hand minus allocated, ignoring inbound. Inbound is shown separately as on_order so a human can decide whether to sell against it, but the system will not promise units that are still on a boat.
• One simplification to spot before you copy this: subquery a matches on tenant and SKU only, because orders in this schema are never tied to a warehouse. With a single fulfilling location that is correct. The day you hold the same SKU in two locations, the same allocation gets subtracted from both rows and ATS reads low, so add a location_id to the order line and join on it too. Chapter 2 picks this up when allocation becomes its own document.

Because this is a view, every read recomputes from the current cache and current orders. There is no ATS number sitting anywhere that could drift.

1.4 What you just bought: event sourcing without the ceremony

What we built is, structurally, event sourcing: the architectural pattern where you store the sequence of things that happened as the permanent record, and treat current state as a report generated from it. (The alternative, storing only current state and overwriting it, is called CRUD: Create, Read, Update, Delete.) In the pattern's own vocabulary, the ledger is the system of record, and everything computed from it (the balance cache, the ATS view) is a projection, meaning any view of the log built by replaying it.

But notice what we did not build: no event store product, no CQRS bus (Command Query Responsibility Segregation: a design where writes and reads go through entirely separate models and infrastructure), no projection rebuild framework, no eventual consistency between write and read models. It's Postgres tables, triggers, and SUM(), which means every tool you already have (SQL, Supabase RLS, pg_dump for backups, your ORM's read path) still works.

Martin Fowler's write-up of the pattern, and the twenty years of write-ups that followed it, agree on the trade: the event log wins you a complete audit trail, queries about past states, and the ability to replay what happened. The cost is extra machinery and a way of thinking your team has to learn. The ledger pattern takes the winnings and skips most of the bill. Concretely:

Audit trails and big-O notation

Two bits of vocabulary in that table. An audit trail is a permanent record of who changed what, when, and why: the thing an accountant, a lawyer, or an angry customer will eventually ask you for.

The last row uses big-O notation, engineers' shorthand for how work grows with data size. O(1) means constant time: reading one cached row costs about the same whether you have a hundred rows or a hundred million. (Strictly, finding it through an index is O(log n), which grows so slowly that everyone treats it as constant.) O(n) means the work grows in proportion to the number of rows.

The point of the cache is that the everyday read stays O(1) and the expensive O(n) pass only happens when you deliberately rebuild or audit.

Debugging a wrong number

The debuggability point deserves one concrete illustration, because it changes what support work feels like. "Why does the system say 417 when the count says 177?" stops being archaeology and becomes a query:

select id, occurred_at, txn_type, qty_delta, reason_code,
       ref_type, ref_id, actor_id
from inventory_transactions
where sku_id = $1 and location_id = $2
order by occurred_at, id;
-- scan the list: 240 + 240 - 48 - 12 - 3 = 417, but the receipt for
-- 240 was entered twice on June 3 by user X against PO-1187. True
-- on-hand is 177. Post one compensating adjustment of -240. Done.

This one pulls the full life story of a single SKU at a single location: every movement, in the order it happened. order by occurred_at, id sorts by real-world time, breaking ties with the insertion id so the sequence is fully deterministic.

What comes back is a readable narrative (received 240, received 240 again, shipped 48, shipped 12, adjusted −3 for damage), and the duplicate is visible to the naked eye. Add those up and you get 417, which is what the screen says. Drop the repeated receipt and you get 177, which is what the warehouse counted.

Note the fix in the comment: you don't delete the duplicate receipt. You post one new adjustment row of −240 that cancels it, and both rows remain in the history forever.

1.5 Money is a ledger too

Everything above applies with more force to money, because money adds an invariant that inventory lacks: it is conserved between accounts. When a retailer pays an invoice, your cash goes up exactly as much as your receivable goes down.

Double-entry bookkeeping is just that conservation law made mechanical: every transaction is recorded as two or more entries that sum to zero. Florentine merchants were keeping full double-entry books by 1300, and Luca Pacioli printed the first detailed description of the method in his 1494 mathematics textbook. It has survived seven centuries because it makes a whole class of bookkeeping error structurally impossible.

The two directions have names: a debit increases what you own or are owed, a credit increases what you owe or have earned, and every entry pairs them so the totals cancel.

TigerBeetle argues debit/credit is "the schema for OLTP" (Online Transaction Processing, the category of system that records lots of small business events in real time, as opposed to systems built for after-the-fact analysis). Two entities (accounts, transfers), one invariant, any domain. You don't need TigerBeetle at apparel-brand scale, but you should steal the schema:

create table ledger_accounts (
  id        uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  code      text not null,          -- '1100' A/R, '4000' revenue...
  name      text not null,
  kind      text not null
    check (kind in ('asset','liability','equity','revenue',
                    'expense','contra_revenue')),
  unique (tenant_id, code)
);

create table journal_entries (
  id          bigint generated always as identity primary key,
  tenant_id   uuid not null references tenants(id),
  description text not null,
  ref_type    text,                 -- 'invoice' | 'payment' | ...
  ref_id      uuid,
  occurred_at timestamptz not null,
  recorded_at timestamptz not null default now()
);

create table journal_lines (
  id           bigint generated always as identity primary key,
  entry_id     bigint not null references journal_entries(id),
  account_id   uuid   not null references ledger_accounts(id),
  amount_minor bigint not null check (amount_minor <> 0)
  -- signed integer cents: positive = debit, negative = credit.
  -- never floats, never numeric dollars in application math.
);

-- the double-entry invariant, enforced by the database at commit time
create function assert_entry_balances() returns trigger
language plpgsql as $$
declare
  v_entry bigint := coalesce(new.entry_id, old.entry_id);
  v_total bigint;
begin
  select coalesce(sum(amount_minor), 0) into v_total
  from journal_lines where entry_id = v_entry;
  if v_total <> 0 then
    raise exception
      'journal entry % does not balance (off by % minor units)',
      v_entry, v_total;
  end if;
  return null;
end $$;

create constraint trigger journal_entry_balanced
  after insert or update or delete on journal_lines
  deferrable initially deferred
  for each row execute function assert_entry_balances();

Accounts, entries, and lines

Four pieces here, and they're worth reading slowly because this is the same shape as the inventory ledger with one extra rule bolted on.

• ledger_accounts is your chart of accounts, the named buckets money can sit in. gen_random_uuid() is a Postgres built-in that generates a fresh random (version 4) UUID, so the database fills in the key for you. code is the accountant-facing number ("1100" for accounts receivable), name the human label, and kind is restricted by a check constraint to the five standard account types (asset, liability, equity, revenue, expense) plus contra_revenue, a bucket for things that reduce revenue, such as discounts, allowances, and returns. unique (tenant_id, code) means each brand may use each account code exactly once, a unique constraint being a rule that no two rows may repeat a combination of values.
• journal_entries is one financial event: "we invoiced Nordstrom." It carries a description, an optional pointer to the business document that caused it (ref_type/ref_id), and the same two timestamps as the inventory ledger.
• journal_lines is where the money actually is. Each line belongs to one entry (entry_id) and touches one account (account_id). amount_minor is the amount in minor units (whole cents rather than dollars), stored as a signed big integer, where positive means debit and negative means credit. There is a hard reason for that. Decimal fractions like 0.1 cannot be represented exactly in binary floating-point, so money math done in floats drifts by fractions of a cent and eventually fails to balance. Integers of cents are exact. The check constraint bans zero-amount lines.
• The balance check. The function looks up which entry was touched (coalesce(new.entry_id, old.entry_id) handles inserts, updates, and deletes in one expression, since new is empty on a delete and old is empty on an insert), sums that entry's lines into the variable v_total, and raises an exception unless the total is exactly zero. Debits and credits must cancel.

Why the balance check is deferred

The DEFERRABLE INITIALLY DEFERRED constraint trigger is the key Postgres trick. "Deferred" means the check is postponed to the end of the transaction instead of running after each individual statement. Without it the rule would be unusable: the instant you insert the first line of a two-line entry, the entry is unbalanced, and a non-deferred check would reject it.

Deferring lets you insert an entry's lines one at a time inside a transaction, and the sums-to-zero check runs at COMMIT, once for each line you touched, which costs almost nothing at these sizes. (Postgres requires constraint triggers to be AFTER triggers declared FOR EACH ROW, which is why this one is written that way.) An unbalanced entry cannot exist in your database, no matter which service wrote it.

One invoice, one short payment

The business documents remain their own tables: invoices, payments, and credit memos. (A credit memo is a document that reduces what a customer owes: the paperwork version of "we agree you shouldn't pay for that.") They follow the ledger discipline: they are immutable once issued, they move through explicit statuses (section 1.7), and posting one means writing a balanced journal entry that references it.

The lifecycle of one wholesale invoice runs like this, including the part that makes apparel finance painful, the retailer deduction. Two bits of trade shorthand appear in the comments: a replen is a replenishment order, a repeat order of a style the retailer already carries, and SS26 is the Spring/Summer 2026 season. A routing guide is the retailer's rulebook for how goods must be labeled, packed, booked, and delivered.

-- 1) Invoice INV-2107 issued to Nordstrom for the SS26 replen: $8,420.00
insert into journal_lines (entry_id, account_id, amount_minor) values
  (1042, :accounts_receivable, +842000),   -- debit  A/R
  (1042, :revenue_wholesale,   -842000);   -- credit revenue

-- 2) Nordstrom pays 60 days later — but short-pays $600, citing a
--    routing-guide compliance chargeback. You do NOT edit the invoice.
insert into journal_lines (entry_id, account_id, amount_minor) values
  (1057, :cash,                +782000),   -- what actually hit the bank
  (1057, :deduction_expense,   + 60000),   -- the chargeback, on the record
  (1057, :accounts_receivable, -842000);   -- receivable cleared in full

Both statements insert several rows at once: one values clause, several parenthesized rows separated by commas. The names starting with a colon (:accounts_receivable) are placeholders for account ids the application supplies. Remember the amounts are in cents: 842000 is $8,420.00.

• Entry 1042, the invoice. Two lines. +842000 to accounts receivable (a debit: Nordstrom now owes us $8,420) and -842000 to wholesale revenue (a credit: we earned $8,420). They sum to zero, so the deferred trigger is satisfied at commit.
• Entry 1057, the payment. Three lines. +782000 to cash, the $7,820 that actually landed in the bank. +60000 to a deduction expense account, the $600 the retailer withheld, recorded as a real cost of doing business. -842000 against accounts receivable, clearing the full original amount, because the invoice is now settled one way or another. Sum: 782000 + 60000 − 842000 = 0. Balanced. (Some brands book retailer deductions to a contra-revenue account instead of an expense account, which changes the reported top line but not the arithmetic. Ask your accountant which they want before you pick.)

Look at what the second entry preserves. The invoice still says $8,420, because you did invoice $8,420. The payment says $7,820, because that's what arrived. The $600 gap sits in its own account as a first-class, queryable fact, instead of vanishing inside an edited invoice.

At year end, "how much did retailer deductions cost us, by retailer, by reason?" is a GROUP BY, the SQL clause that buckets rows by a shared value and computes a total per bucket. That is exactly the report a wholesale apparel brand's CFO asks for, and exactly the report a system that edits invoices in place can never produce.

If you later dispute the chargeback and win the money back, you post another entry to reverse it. Documents are facts. The ledger relates them. Nothing is ever rewritten.

1.6 Two kinds of time

You may have noticed inventory_transactions carries two timestamps. Those two columns are the minimum viable answer to a subtle problem the temporal-database literature calls bitemporality: tracking two independent timelines for the same fact, so you can ask both "what was true?" and "what did we know?" The two columns carry one timeline each:

• Valid time (occurred_at, which Fowler calls "actual time"): when the event happened in the real world. The truck arrived Friday.
• Transaction time (recorded_at, or "record time"): when your database learned about it. The receiving clerk entered it Monday morning.

When the two clocks disagree

Most of the time these are minutes apart and nobody cares. They start to matter the moment data arrives late or gets corrected after the fact, which in a warehouse-driven business happens most weeks. Suppose on Monday the clerk backdates Friday's receipt of 300 units.

"On-hand as of Saturday" now has two correct answers: what was physically true (includes the 300, so filter on occurred_at) and what the system believed (excludes them, so filter on recorded_at too). The first answers operations questions. The second answers accountability questions like "why did we refuse that order Saturday?" or an auditor's "reproduce the report you generated on March 31":

-- what did the system BELIEVE on-hand was, at the close of March 1?
select coalesce(sum(qty_delta), 0) as believed_on_hand
from inventory_transactions
where tenant_id = $1 and sku_id = $2 and location_id = $3
  and occurred_at < '2026-03-02'    -- effective by then...
  and recorded_at < '2026-03-02';   -- ...and known by then

Same sum as before, now filtered on both timelines at once. The first date condition keeps movements that had happened in the real world by the end of March 1. The second keeps only those the database had actually been told about by then.

Drop the second line and you get today's best understanding of March 1. Keep it and you get a faithful replay of what the screen showed on March 1, which is what an auditor means by "reproduce that report." Nothing was archived or snapshotted to make this work. Those two columns do all of it.

How much bitemporality to buy

Because ledger rows are immutable, you get this bitemporal query capability almost for free: append-only + two timestamps is a bitemporal event log. Do not extend full bitemporality to your entity tables (bitemporal SKUs, bitemporal price lists with four timestamp columns and interval-splitting update logic).

Martin Fowler, who wrote the standard reference on the technique, is blunt about it: "If we can avoid using bitemporal history, then that's usually preferable as it does complicate a system quite significantly." His own worked example is a payroll correction (a February raise that payroll only hears about in March, then has to correct again in April), and even there he spends most of the article warning about the complexity.

The table below is a decision aid: find the kind of data you're modeling in the right-hand column, and it tells you how much temporal machinery to spend on it. One term in it needs a definition first: a linesheet is the seasonal document listing every style you're offering with its wholesale price, the thing a buyer orders from.

1.7 Documents that get "edited": version chains

Wholesale orders are the classic objection: "the buyer revises quantities three times before shipping, so surely those rows must be mutable?" No. An order revision is a business event, and buyers will dispute what they ordered ("we cut the crewnecks from 120 to 60 in April!").

The pattern: an immutable header carrying identity and status, plus a chain of immutable revision snapshots. Lines belong to revisions. "Editing" appends a revision and advances a pointer. Deleting is a voided_at timestamp, never DELETE. That is a soft delete: you mark a row as no longer active instead of removing it, so history survives and queries simply learn to skip it.

create table orders (
  id               uuid primary key default gen_random_uuid(),
  tenant_id        uuid not null references tenants(id),
  order_no         text not null,
  retailer_id      uuid not null references retailers(id),
  status           order_status not null default 'draft',
  current_revision integer not null default 0,
  voided_at        timestamptz,          -- soft delete: null = live
  created_at       timestamptz not null default now(),
  unique (tenant_id, order_no)
);

create table order_revisions (
  id          uuid primary key default gen_random_uuid(),
  order_id    uuid not null references orders(id),
  revision_no integer not null,
  created_by  uuid not null references users(id),
  created_at  timestamptz not null default now(),
  note        text,
  unique (order_id, revision_no)
);

create table order_lines (
  id               uuid primary key default gen_random_uuid(),
  revision_id      uuid not null references order_revisions(id),
  sku_id           uuid not null references skus(id),
  qty              integer not null check (qty > 0),
  unit_price_minor bigint  not null check (unit_price_minor >= 0)
);

Orders, revisions, and lines

Three tables forming a chain: an order has many revisions, and each revision has many lines.

• orders is the stable identity of the order. It exists once and is never replaced. order_no is the human-facing number ("SO-1043"), unique per tenant. status uses the enum defined in the next section. current_revision is the pointer: an integer naming which revision is in force right now. voided_at is the soft delete, where null means live and a timestamp means canceled-but-remembered. Notice it has no not null, because empty is the normal state.
• order_revisions is one snapshot of what was agreed, numbered sequentially per order (unique (order_id, revision_no) makes two "revision 3"s impossible), stamped with who made it, when, and an optional note explaining why.
• order_lines holds the actual items, but note the foreign key: a line points at a revision, and never at the order directly. That single choice is what makes the whole pattern work. Lines are never edited. A new revision gets a whole new set of lines. The check constraints require a positive quantity and a non-negative price (free goods are allowed, but negative prices are not).

The revise-order write path

And the write path, as a Turborepo service function your Next.js server actions call (using postgres.js, though the same shape works through a Supabase RPC). Some quick orientation on those names:

• Next.js is the web framework this book builds on;
• a server action is a function that runs on the server but is called from browser code as if it were local;
• Turborepo is the tool that lets one repository (a monorepo) hold several packages, such as a web app and a shared domain package, that import each other;
• postgres.js is a small library for sending SQL to Postgres from TypeScript;
• and an RPC (Remote Procedure Call) is Supabase's way of exposing a database function as a callable endpoint.

Note the FOR UPDATE: revising is one of the few places we serialize, because two concurrent revisions of the same order must not both become "revision 3":

// packages/domain/src/orders/revise-order.ts
import type { Sql } from 'postgres';

const REVISABLE = ['draft', 'submitted', 'confirmed'] as const;

export async function reviseOrder(
  sql: Sql,
  orderId: string,
  actorId: string,
  patch: { note: string; lines: OrderLineInput[] },
) {
  return sql.begin(async (tx) => {
    const [order] = await tx`
      select id, status, current_revision
      from orders where id = ${orderId}
      for update`;                       // serialize revisions per order

    if (!REVISABLE.includes(order.status)) {
      throw new DomainError(
        `cannot revise an order in status "${order.status}"`);
    }

    const [rev] = await tx`
      insert into order_revisions (order_id, revision_no, created_by, note)
      values (${orderId}, ${order.current_revision + 1},
              ${actorId}, ${patch.note})
      returning id, revision_no`;

    for (const line of patch.lines) {
      await tx`
        insert into order_lines
          (revision_id, sku_id, qty, unit_price_minor)
        values (${rev.id}, ${line.skuId}, ${line.qty},
                ${line.unitPriceMinor})`;
    }

    await tx`
      update orders set current_revision = ${rev.revision_no}
      where id = ${orderId}`;

    return rev;
  });
}

This is TypeScript: JavaScript plus a type system, where you annotate what kind of value each thing holds and a compiler checks your work before the code ever runs. Reading it top to bottom:

• What goes in. The function takes four arguments: sql, the database connection (its type, Sql, is imported from the postgres.js library and tells the compiler what methods exist on it); orderId and actorId, both strings; and patch, an object containing a note and an array of new lines. REVISABLE is the list of statuses an order may be revised in. as const tells TypeScript to treat those three strings as exact fixed values rather than as generic text, so a typo elsewhere becomes a compile error.
• async and await. Talking to a database takes time, so these calls are asynchronous: await means "pause here until the answer comes back, without blocking the rest of the server." An async function is one allowed to contain await.
• sql.begin(async (tx) => { ... }) opens a transaction and hands you tx, a handle you must use for every statement inside it. If the callback finishes normally the transaction commits. If anything throws, everything rolls back automatically. That is the all-or-nothing guarantee from the prerequisites, in code.
• The locked read. The first query fetches the order with for update appended, which locks that row for the rest of the transaction. Any other session trying to revise the same order waits here until we commit, which is precisely what stops two people from both claiming revision 3. The backtick strings are tagged templates: values written as ${orderId} are sent to Postgres as safely bound parameters, never pasted into the query text, so this syntax is immune to SQL injection, the attack where text a user typed gets treated as part of your query instead of as a plain value. const [order] = await ... destructures the first row out of the returned array.
• The status guard. If the order's status isn't in REVISABLE, throw a DomainError, a custom error type for "the business rules say no," as distinct from a crash. Throwing here rolls back the transaction, so nothing partial is left behind.
• The three writes. Insert one new revision numbered current_revision + 1 (returning id, revision_no asks Postgres to hand back the row it just created, saving a second round trip); loop over the incoming lines and insert each against that new revision id; then move the order's current_revision pointer forward.
• What comes out. The new revision's id and number. Nothing was updated except the pointer. The previous revision and all its lines sit untouched in the database forever.

Every historical revision remains queryable forever: what the buyer originally committed to, what changed in each revision, who changed it, and when. When the retailer disputes a fill-rate chargeback, you print the revision chain. Note the ATS view in section 1.3 already accounted for this: it joins through current_revision, so superseded revisions never double-count allocations.

1.8 Explicit state machines, enforced twice

The last pillar: documents move through enumerated states via enumerated transitions, and everything else is rejected. A state machine is exactly that idea: a finite list of states a thing can be in, plus a finite list of the moves allowed between them. An order is a state machine: it can be a draft, submitted, confirmed, shipped, and so on, and "shipped" can never go back to "draft."

We enforce it twice: in TypeScript for good error messages, and in Postgres because the database is the last line of defense against every future code path (the admin panel, the EDI integration, the data-fix script at 2 a.m.). EDI is Electronic Data Interchange, the decades-old file format big retailers use to send you orders automatically.

A Postgres CHECK constraint can restrict which values a status column may hold, but it cannot see the previous row state, so it can't validate transitions. That requires a trigger comparing OLD to NEW, the trigger variables holding the row before and after the change. Keep the legal transitions in a table, so the machine is data anyone can read:

create type order_status as enum
  ('draft','submitted','confirmed','allocated',
   'shipped','invoiced','closed','cancelled');

create table order_status_transitions (
  from_status order_status not null,
  to_status   order_status not null,
  primary key (from_status, to_status)
);

insert into order_status_transitions values
  ('draft','submitted'),
  ('submitted','confirmed'), ('submitted','cancelled'),
  ('confirmed','allocated'), ('confirmed','cancelled'),
  ('allocated','shipped'),
  ('shipped','invoiced'),
  ('invoiced','closed');
  -- note what is absent: shipped -> cancelled does not exist.
  -- physical goods left the building; only a return flow undoes that.

create function enforce_order_status() returns trigger
language plpgsql as $$
begin
  if old.status is distinct from new.status and not exists (
    select 1 from order_status_transitions
    where from_status = old.status and to_status = new.status
  ) then
    raise exception 'illegal order status transition: % -> %',
      old.status, new.status;
  end if;
  return new;
end $$;

create trigger order_status_guard
  before update of status on orders
  for each row execute function enforce_order_status();

Reading the four statements

Four statements, building the machine out of ordinary data:

• The enum fixes the eight states an order may be in. Any other value is rejected by the database.
• The transitions table is the map of legal moves. Its primary key is the pair (from_status, to_status), so each move can be listed exactly once. Being a table rather than code means you can read the rules with a SELECT, and a product manager can look at them.
• The insert populates the map with eight allowed moves. Read them as arrows: draft → submitted, submitted → confirmed or cancelled, and so on. What matters most is what is missing: there is no shipped → cancelled row, so once goods leave the building that move is simply not expressible. Absence is the enforcement.
• The function and trigger check every attempted change. The trigger fires before update of status on orders, before the change lands, and only when the status column is targeted. The function asks: did the status actually change (is distinct from, which handles nulls correctly), and if so, does a matching row exist in the transitions table? select 1 ... not exists is the standard idiom for asking "is there any such row?" The 1 is a throwaway value since only existence matters. No matching row means raise exception, which aborts the transaction. return new at the end means "allow the row through unchanged."

The same machine in TypeScript

Mirror it in a shared Turborepo package so the UI can compute "which buttons to show" and services can fail fast with typed errors:

// packages/domain/src/orders/status.ts
export const ORDER_STATUSES = [
  'draft', 'submitted', 'confirmed', 'allocated',
  'shipped', 'invoiced', 'closed', 'cancelled',
] as const;

export type OrderStatus = (typeof ORDER_STATUSES)[number];

export const LEGAL_TRANSITIONS = {
  draft:     ['submitted'],
  submitted: ['confirmed', 'cancelled'],
  confirmed: ['allocated', 'cancelled'],
  allocated: ['shipped'],
  shipped:   ['invoiced'],
  invoiced:  ['closed'],
  closed:    [],
  cancelled: [],
} as const satisfies Record<OrderStatus, readonly OrderStatus[]>;

export function canTransition(
  from: OrderStatus,
  to: OrderStatus,
): boolean {
  return (LEGAL_TRANSITIONS[from] as readonly OrderStatus[]).includes(to);
}

// Two sources of truth WILL drift. This integration test forbids it:
// SELECT * FROM order_status_transitions, diff against LEGAL_TRANSITIONS,
// fail CI on any mismatch. Cheap insurance, runs in seconds.

The same machine, restated so the browser and the server can reason about it without a database round trip. export makes each item importable by other files in the monorepo.

• ORDER_STATUSES is the list of states. as const freezes it: TypeScript now knows this array holds exactly those eight literal strings, in that order, and can never be added to.
• export type OrderStatus = (typeof ORDER_STATUSES)[number] is the clever line. A TypeScript type is a compile-time description of what values are allowed somewhere, and this one is derived from the array rather than written twice. typeof ORDER_STATUSES asks for the array's type, and indexing it by [number] means "the type of any element," which yields a union type (a type meaning "one of this set"), namely 'draft' | 'submitted' | ... | 'cancelled'. Now any variable typed OrderStatus can only ever hold one of those eight strings, and adding a state to the array automatically extends the type. One list, two jobs.
• LEGAL_TRANSITIONS is the same map as the database table, expressed as an object from each state to the array of states it may move to. closed and cancelled map to empty arrays: terminal states, no exits. The trailing satisfies Record<OrderStatus, readonly OrderStatus[]> is a compile-time assertion: Record<K, V> describes an object with keys of type K and values of type V, so this says "every status must appear as a key, and every value must be a list of valid statuses." Forget to add an entry when you add a state and the build fails. satisfies (rather than a plain type annotation) checks the shape while preserving the exact literal contents, so canTransition still gets precise information.
• canTransition(from, to) takes two statuses and returns true or false: look up the source state's allowed list and ask whether the destination is in it. This is what a React component (one reusable piece of the on-screen interface) calls to decide whether to render the "Confirm" button.
• The closing comment names the real risk: two copies of the same rules will eventually disagree. The fix is an automated test that reads the database table, compares it to LEGAL_TRANSITIONS, and fails CI (continuous integration, the service that runs your tests automatically on every change) if they differ. Duplication is fine when a machine is guarding it.

Why the strictness pays

Every illegal transition you have ever seen in a production ERP was written by someone with a good reason that day. An order that jumps from draft to shipped skips allocation, so no shipment ledger rows were written, and inventory is now wrong. An invoice "canceled" after payment strands a journal entry against a dead document.

State machines are how the document layer keeps its promises to the ledger layer: each transition is exactly the place where ledger side effects happen (confirm ⇒ allocation appears in ATS; ship ⇒ shipment rows post; invoice ⇒ journal entry posts).

Enumerate the transitions and you have enumerated every place inventory and money can change. That property, you can list all the doors, is the whole game in ERP engineering, and it's why status columns must never be writable as ordinary data.

Field notes & further reading

• Modern Treasury — Accounting for Developers, Part I: The Fundamentals (and the later parts): the canonical "store immutable transactions, derive balances" argument, with schemas. Source of the quotation in section 1.1.
• Modern Treasury — How to Scale a Ledger, Part II: Mapping Financial Events: how a production ledger keeps five aggregate fields per account and computes posted, pending, and available balances only when asked.
• TigerBeetle — Debit/Credit: The Schema for OLTP: the strongest articulation of accounts + transfers + immutability as a universal transaction-processing schema.
• Stripe — Ledger: Stripe's system for tracking and validating money movement (2024): "At its core, Ledger is an immutable log of events." The same architecture at a different scale.
• Uber — How LedgerStore Supports Trillions of Indexes: Uber's immutable storage layer for financial events, and what immutability buys you when the volume gets absurd.
• Martin Fowler — Bitemporal History: actual time vs. record time (his names for valid and transaction time), the retroactive-payroll example, and the case for restraint.
• Martin Fowler — Event Sourcing: the original write-up of "store the events, derive the state," including what it costs you.
• PostgreSQL Documentation — Constraints: "PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked" (and why a trigger is the right tool when you need that).
• PostgreSQL Documentation — CREATE TRIGGER: constraint triggers, DEFERRABLE INITIALLY DEFERRED, and the rule that constraint triggers must be AFTER ROW triggers.
• Supabase — Row Level Security: auth.jwt(), why authorization claims belong in app_metadata, and the measured speed-up from wrapping function calls in (select …).