9 Testing and Operational Discipline

The append-only ledger, the outbox table that hands work to background jobs, tenant isolation, the available-to-sell (ATS) cache: everything you have built so far is only as good as your ability to prove it still works tomorrow. This chapter teaches the discipline that money-adjacent software demands: property-based tests that attack your domain core with thousands of random operation sequences, invariant checks that run in production and page a human when the books do not balance, golden tests that freeze the shape of an invoice, rehearsed backups, structured logs that reconstruct the life of order #4471, and a job queue boring enough to trust.

Two words appear on almost every page, so here they are up front. A tenant is one customer company using your ERP: one apparel brand, with its own orders, stock, and staff. Available-to-sell (ATS) is the quantity of a product you can still promise to a buyer: physical stock on hand minus the stock already promised to other orders.

What you need to know first

This section assumes nothing. If you already know what a test is, skim it and move on.

A test is a small program that runs your real code with known inputs and complains if the output is wrong. That is the whole idea. If you have a function ats(onHand, allocated) that should return 7 when given 10 and 3, a test is a few lines that call ats(10, 3), look at the answer, and shout if it is not 7.

The shouting is done by an assertion, a statement of the form "I claim this is true. Blow up if it is not." Tests live in files next to your code, usually named something.test.ts. They are ordinary code, and they run every time you ask.

You run tests with a test runner. A test runner is a program that finds all your test files, executes them, and prints a summary: 412 passed, 1 failed, here is the failure.

In a Next.js + Turborepo project (a Turborepo is one code repository holding several related applications and shared libraries) the usual runner is Vitest, and you run it by typing pnpm test in a terminal. Green means every claim held. Red means one did not, and the runner tells you which file, which line, what it expected, and what it got.

You will run tests hundreds of times a day. They should finish in seconds, which is why the fastest and most valuable tests never touch a database.

Production, staging, and the CI robot

"Production" means the real system your real customers are really using right now. Your laptop is not production. Production is the deployed application, the actual Supabase database holding actual purchase orders from actual boutiques, the thing that loses real money when it is wrong. Staging is a second, complete, running copy of that system with fake data in it, used for rehearsal. The rule that matters: changes go to staging first, then to production. The rule people forget: staging is also where you rehearse recovery, alongside features.

Continuous integration (CI) is a robot that runs your tests for you. Every time you push code, a service such as GitHub Actions checks out your branch on a fresh machine, installs everything, runs the whole test suite, and reports pass or fail on the pull request. It catches the tests you forgot to run locally. Whenever this chapter says "in CI", it means "on that robot, on every push, before anyone can merge".

Background jobs, queues, and workers

A background job is work that happens without anyone waiting for it. When a warehouse clerk clicks "Ship", the web page must respond in a few hundred milliseconds. But shipping also means:

• recalculate available-to-sell,
• generate an invoice PDF,
• push a fulfillment record to the third-party logistics provider,
• email the buyer.

If you did all of that before responding, the clerk would stare at a spinner for eight seconds, and the whole click would fail if the email provider hiccuped. So instead you write a short note saying "generate invoice for shipment SH-9001", drop it into a queue (a list of pending work, stored in a table), and respond immediately.

A separate long-running program called a worker reads notes off the queue and does the work. A scheduled job (or cron job, named after the ancient Unix scheduler cron) is the same thing on a timer: "run this every night at 02:15."

Structured logs

A log is a line your program writes down describing something that just happened. Nobody reads logs for fun. You read them at 11pm when a customer says an order vanished. Old-fashioned logs are sentences: Shipped order 4471, 3 lines. Structured logging means writing that same information as machine-readable data, a JSON object like {"msg":"order.shipped","orderId":"SO-4471","tenantId":"acme","lines":3}, so you can later ask your logging tool "show me everything where orderId is SO-4471" and get a precise answer instead of grepping English prose. Structured logs are the difference between searching and guessing.

Paging a human

"Paging" an engineer means deliberately interrupting a human being. The word comes from pagers, the beepers doctors used to carry. Today it means an on-call tool such as PagerDuty ringing your phone at 3am, overriding silent mode, until you acknowledge it. (If you inherit a system wired to Atlassian's Opsgenie, note that Atlassian is retiring it: existing customers can keep using it until 5 April 2027, after which it shuts down and users move to Jira Service Management.)

Paging burns human attention and goodwill, so you page only for things that are both true and urgent. A ledger that no longer matches its cached balance is both. A slow page load is neither.

Backups, restores, RPO and RTO

A backup is a copy of your data stored somewhere else. A restore is the act of turning that copy back into a working database. These are not the same skill, and the second is the only one that matters. A backup you have never restored is a rumor.

Restoring involves finding the backup, provisioning a machine or project to put it on, loading potentially hundreds of gigabytes, pointing an application at it, and verifying the data is correct and current. That takes real time, minutes to hours, and you want to have measured it on a calm afternoon long before an outage forces the question.

Two terms come with it. RPO (Recovery Point Objective) is how much data you are willing to lose, measured in time: "RPO of 2 minutes" means the last 2 minutes of writes can be gone forever. RTO (Recovery Time Objective) is how long you are willing to be offline while restoring. Both are business decisions with engineering price tags, and both should be written down before you need them.

Invariants: the sentences that must always be true

An invariant is a statement about your system that must hold at every observable moment, no matter what happened before. "Usually" fails the bar. "Once the nightly job has run" fails the bar. It has to be true always, including halfway through a busy afternoon. In an apparel ERP the important ones are short enough to write on an index card:

• I1. Ledger ties to cache. For every stock-keeping unit (SKU) in every tenant, the sum of quantities in the append-only inventory_ledger equals the on_hand figure in the ATS cache you built in chapter 8.
• I2. An allocation never promises stock you do not have. When the system accepts an allocation, the total promised for that SKU stays within what is available at that moment. Your code must refuse to promise 40 units of TEE-BLK-M when 30 exist. (A stock write-down afterwards can still leave you promising more than you hold. That is an oversell, it is legitimate, and the rule is that it must be visible rather than hidden.)
• I3. No negative on-hand. Physical inventory cannot be less than zero. Negative available-to-sell is legitimate (that is the oversell above). Negative physical stock means you have recorded shipping shirts that were never in the building, which is a bug.
• I4. Every invoice traces to shipments. Every invoice line references a real shipment line, the quantities are consistent, and no shipment line is ever invoiced twice.
• I5. Tenant containment. No row ever references a row belonging to a different customer company. An order line in tenant A cannot point at a SKU in tenant B.

Write these down in a real file in your repository (docs/invariants.md), because you are about to encode each one three times: as a property-based test, as a production check, and as an on-call runbook entry. When someone proposes a feature that would violate one, you now have something concrete to point at.

From example tests to property tests

Start with the kind of test you would write naturally. Here is a classic example-based test: you pick the inputs, you state the expected output.

// packages/domain/test/allocate.example.test.ts
import { describe, it, expect } from 'vitest';
import { emptyState, apply } from '../src/core';

describe('allocation', () => {
  it('reduces available-to-sell by the allocated quantity', () => {
    let s = emptyState('acme');

    s = apply(s, { kind: 'receipt',  sku: 'TEE-BLK-M', qty: 10 }).state;
    s = apply(s, { kind: 'allocate', sku: 'TEE-BLK-M', qty: 3, orderId: 'SO-1001' }).state;

    expect(s.onHand['TEE-BLK-M']).toBe(10);
    expect(s.allocated['TEE-BLK-M']).toBe(3);
    expect(ats(s, 'TEE-BLK-M')).toBe(7);
  });

  it('refuses to allocate more than is available', () => {
    let s = emptyState('acme');
    s = apply(s, { kind: 'receipt', sku: 'TEE-BLK-M', qty: 10 }).state;

    const result = apply(s, { kind: 'allocate', sku: 'TEE-BLK-M', qty: 11, orderId: 'SO-1001' });

    expect(result.ok).toBe(false);
    expect(result.error).toBe('INSUFFICIENT_ATS');
  });
});

The import line pulls three helpers out of Vitest:

• describe groups related tests under a heading,
• it declares one test with a human-readable name,
• and expect creates assertions.

emptyState('acme') builds a fresh in-memory world belonging to tenant "acme". Each apply(...) call feeds one event into the domain core and returns a result. We take .state, the new world after that event.

The first test receives 10 units, allocates 3, and asserts three facts: on-hand still 10, allocated 3, available-to-sell 7. The second test tries to allocate 11 out of 10. Instead of reading .state we inspect the whole result and assert that ok is false and the error is INSUFFICIENT_ATS. Notice we assert on a code, not an English message, so rewording the message later does not break the test.

These are good tests. They are also limited by your imagination. You tested 10 and 3. You did not test a receipt of 10, an allocation of 3, a negative adjustment of −8, a partial shipment of 2, a return of 1, and a cancellation in that exact order, because you never thought of that order. Nobody does. That sequence is where the bug is.

What property-based testing actually is

Property-based testing inverts the exercise. Instead of naming inputs and expected outputs, you name a property, a rule that must hold for all inputs, and let the library invent the inputs. Hundreds of them, per run, forever, including ugly ones you would never type.

The library is fast-check (version 4.9.0 as of July 2026). Four terms carry the whole idea:

• An arbitrary is a recipe for producing random values of some type. fc.integer({ min: 1, max: 50 }) is an arbitrary for quantities.
• The generator is the half of the arbitrary that actually produces a value when asked. fast-check's own documentation is precise about this: "An arbitrary in fast-check is not just a random value generator. It is a generator paired with a shrinker."
• Shrinking is the other half, and it is the feature that makes property testing usable by humans. When a property fails on a sixty-event sequence, fast-check does not hand you sixty events. It repeatedly simplifies the failing input, dropping events and pulling numbers toward zero, while checking that it still fails, until it finds the smallest input that still breaks. You get a short counterexample instead of a wall of noise. The docs put it bluntly: "Debugging a bad input that is hundreds of lines long can be nightmarish so fast-check works to drop unnecessary noise from any inputs which cause failures."
• A counterexample is that minimal failing input. It is your bug report, written for you by a machine.

One more term, because it is the thing beginners fear: determinism. A deterministic program produces the same output every time it is given the same input. Random tests sound like the opposite, and flaky tests are awful. fast-check solves it with a seed. A seed is a starting number for the random generator, and the same seed always produces the same sequence of "random" values. The default number of runs (numRuns) is 100 and the default seed is Date.now(), so each run explores fresh territory. When a run fails, fast-check prints the seed it used so you can pin it and reproduce the failure exactly.

The domain core: pure functions you can hammer

Property testing only pays off if your business rules live in pure functions: functions that take values in, return values out, and touch nothing else. No database, no clock, no network, no random numbers. A pure function can be called ten thousand times in two seconds. A function that opens a Postgres connection cannot.

This is also why you will barely use mocks here. A mock is a fake stand-in for a real dependency, such as a pretend payment client that records what you asked it to do instead of charging a card. Mocks are necessary at the edges of a system but are a smell in the middle of one: every mock is a place where your test can agree with itself while disagreeing with reality. A pure domain core needs none.

// packages/domain/src/core.ts
export type Sku = string;
export type OrderId = string;

export type Event =
  | { kind: 'receipt';    sku: Sku; qty: number }                                   // qty > 0
  | { kind: 'adjustment'; sku: Sku; qty: number }                                   // may be negative
  | { kind: 'allocate';   sku: Sku; qty: number; orderId: OrderId }
  | { kind: 'release';    sku: Sku; qty: number; orderId: OrderId }
  | { kind: 'ship';       sku: Sku; qty: number; orderId: OrderId; shipmentId: string }
  | { kind: 'invoice';    shipmentId: string; invoiceId: string }
  | { kind: 'return';     sku: Sku; qty: number; orderId: OrderId };

export type State = {
  tenantId: string;
  ledger: { sku: Sku; qty: number; reason: Event['kind'] }[];   // append-only, chapter 1
  onHand: Record<Sku, number>;                                   // cached fold of the ledger
  allocated: Record<Sku, number>;
  shipped: { shipmentId: string; orderId: OrderId; sku: Sku; qty: number }[];
  invoices: { invoiceId: string; shipmentId: string }[];
};

export type Applied =
  | { ok: true;  state: State }
  | { ok: false; state: State; error: string };

const at = (m: Record<Sku, number>, k: Sku) => m[k] ?? 0;
export const ats = (s: State, sku: Sku) => at(s.onHand, sku) - at(s.allocated, sku);

export function emptyState(tenantId: string): State {
  return { tenantId, ledger: [], onHand: {}, allocated: {}, shipped: [], invoices: [] };
}

export function apply(s: State, e: Event): Applied {
  // Post one ledger row AND update the cached balance in a single move,
  // so the two can never drift apart by accident.
  const post = (sku: Sku, qty: number): State => ({
    ...s,
    ledger: [...s.ledger, { sku, qty, reason: e.kind }],
    onHand: { ...s.onHand, [sku]: at(s.onHand, sku) + qty },
  });

  switch (e.kind) {
    case 'receipt':
      if (e.qty <= 0) return { ok: false, state: s, error: 'NON_POSITIVE_RECEIPT' };
      return { ok: true, state: post(e.sku, e.qty) };

    case 'adjustment':
      if (at(s.onHand, e.sku) + e.qty < 0) return { ok: false, state: s, error: 'WOULD_GO_NEGATIVE' };
      return { ok: true, state: post(e.sku, e.qty) };

    case 'allocate':
      if (e.qty <= 0)            return { ok: false, state: s, error: 'NON_POSITIVE_QTY' };
      if (e.qty > ats(s, e.sku)) return { ok: false, state: s, error: 'INSUFFICIENT_ATS' };
      return { ok: true, state: { ...s,
        allocated: { ...s.allocated, [e.sku]: at(s.allocated, e.sku) + e.qty } } };

    case 'release':
      if (e.qty <= 0)                     return { ok: false, state: s, error: 'NON_POSITIVE_QTY' };
      if (e.qty > at(s.allocated, e.sku)) return { ok: false, state: s, error: 'OVER_RELEASE' };
      return { ok: true, state: { ...s,
        allocated: { ...s.allocated, [e.sku]: at(s.allocated, e.sku) - e.qty } } };

    case 'ship': {
      if (e.qty > at(s.allocated, e.sku))
        return { ok: false, state: s, error: 'SHIP_EXCEEDS_ALLOCATION' };
      // You cannot ship shirts that are not in the building, even if an
      // earlier write-down means they are still promised to someone (I3).
      if (at(s.onHand, e.sku) - e.qty < 0)
        return { ok: false, state: s, error: 'SHIP_WOULD_GO_NEGATIVE' };
      const s2 = post(e.sku, -e.qty);
      return { ok: true, state: {
        ...s2,
        allocated: { ...s2.allocated, [e.sku]: at(s2.allocated, e.sku) - e.qty },
        shipped: [...s2.shipped, { shipmentId: e.shipmentId, orderId: e.orderId, sku: e.sku, qty: e.qty }],
      }};
    }

    case 'invoice':
      if (!s.shipped.some(x => x.shipmentId === e.shipmentId))  return { ok: false, state: s, error: 'NO_SUCH_SHIPMENT' };
      if (s.invoices.some(i => i.shipmentId === e.shipmentId))  return { ok: false, state: s, error: 'ALREADY_INVOICED' };
      return { ok: true, state: { ...s,
        invoices: [...s.invoices, { invoiceId: e.invoiceId, shipmentId: e.shipmentId }] } };

    case 'return':
      if (e.qty <= 0) return { ok: false, state: s, error: 'NON_POSITIVE_QTY' };
      return { ok: true, state: post(e.sku, e.qty) };
  }
}

The Event type is a discriminated union: a list of the only seven things that can ever happen, each tagged by its kind field. TypeScript refuses to compile if you forget to handle one, which is a free correctness check. State holds the append-only ledger from chapter 1 alongside the derived caches (onHand, allocated) from chapter 8. Keeping both is deliberate: invariant I1 is exactly the claim that they agree, so the test must be able to see both. at() reads a map and treats "missing" as zero. ats() computes available-to-sell as on-hand minus allocated.

apply is the whole business. The inner helper post appends a ledger row and updates the cached onHand in one operation, so the two cannot drift apart by accident.

Each case then enforces its rule and returns either { ok: true, state } or { ok: false, state, error }. The failure branch returns the unchanged state, because a rejected command must leave the world exactly as it found it:

• adjustment allows negative quantities but refuses one that would drive physical stock below zero (I3). Those negative adjustments are the everyday corrections of a warehouse: shrinkage (stock that has quietly disappeared through theft or mis-picking), damage, and cycle counts (a rolling physical recount of a few bins at a time, which regularly finds fewer shirts than the system claimed).
• allocate checks against ats, so the system can never accept a promise larger than what is available (I2).
• ship refuses to move more than was allocated, and also refuses to drive physical stock below zero. That second guard is the one a random test will catch you forgetting, as the fast-check run below demonstrates.
• invoice refuses a shipment that does not exist and refuses to bill the same shipment twice (I4, the software equivalent of a locked cash drawer).

Everything returns new objects via spread syntax, so a failed branch cannot half-modify state.

Generating random operation sequences

Now let fast-check try to break it.

// packages/domain/test/invariants.property.test.ts
import { it, expect } from 'vitest';
import fc from 'fast-check';
import { emptyState, apply, ats, type Event, type State } from '../src/core';

const skuArb   = fc.constantFrom('TEE-BLK-S', 'TEE-BLK-M', 'HOOD-GRY-L');
const orderArb = fc.constantFrom('SO-1001', 'SO-1002');
const qtyArb   = fc.integer({ min: 1, max: 25 });
const shipArb  = fc.constantFrom('SH-A', 'SH-B', 'SH-C');

const eventArb: fc.Arbitrary<Event> = fc.oneof(
  { arbitrary: fc.record({ kind: fc.constant('receipt'    as const), sku: skuArb, qty: qtyArb }), weight: 3 },
  { arbitrary: fc.record({ kind: fc.constant('adjustment' as const), sku: skuArb, qty: fc.integer({ min: -25, max: 25 }) }), weight: 1 },
  { arbitrary: fc.record({ kind: fc.constant('allocate'   as const), sku: skuArb, qty: qtyArb, orderId: orderArb }), weight: 3 },
  { arbitrary: fc.record({ kind: fc.constant('release'    as const), sku: skuArb, qty: qtyArb, orderId: orderArb }), weight: 1 },
  { arbitrary: fc.record({ kind: fc.constant('ship'       as const), sku: skuArb, qty: qtyArb, orderId: orderArb, shipmentId: shipArb }), weight: 2 },
  { arbitrary: fc.record({ kind: fc.constant('invoice'    as const), shipmentId: shipArb, invoiceId: fc.constantFrom('INV-1', 'INV-2') }), weight: 2 },
  { arbitrary: fc.record({ kind: fc.constant('return'     as const), sku: skuArb, qty: qtyArb, orderId: orderArb }), weight: 1 },
);

/** Every invariant, checked against one snapshot of the world. Returns a list of violations. */
export function checkInvariants(s: State): string[] {
  const bad: string[] = [];
  const skus = new Set([...Object.keys(s.onHand), ...Object.keys(s.allocated)]);

  for (const sku of skus) {
    const fromLedger = s.ledger.filter(r => r.sku === sku).reduce((a, r) => a + r.qty, 0);
    if (fromLedger !== (s.onHand[sku] ?? 0)) bad.push(`I1 ${sku}: ledger ${fromLedger} != cache ${s.onHand[sku]}`);
    if ((s.onHand[sku] ?? 0) < 0)            bad.push(`I3 ${sku}: on-hand is negative`);
    if ((s.allocated[sku] ?? 0) < 0)         bad.push(`I2 ${sku}: allocated is negative`);
    // Deliberately absent: a check for ats < 0. A stock write-down can
    // legally leave you promising more than you hold (see fixture S3).
    // I2 is checked in the test below, at the moment a promise is made.
  }

  for (const inv of s.invoices) {
    if (!s.shipped.some(sh => sh.shipmentId === inv.shipmentId)) bad.push(`I4 ${inv.invoiceId}: no shipment`);
  }
  const shipIds = s.invoices.map(i => i.shipmentId);
  if (new Set(shipIds).size !== shipIds.length) bad.push('I4: a shipment was invoiced twice');

  return bad;
}

it('no sequence of operations can break the invariants', () => {
  fc.assert(
    fc.property(fc.array(eventArb, { maxLength: 80 }), (events) => {
      let s = emptyState('acme');
      for (const e of events) {
        const r = apply(s, e);
        if (r.ok) s = r.state;      // rejected commands are legal; they do nothing

        // I2, checked where it belongs: an ACCEPTED allocation never
        // promises more than was available at that moment.
        if (r.ok && e.kind === 'allocate') {
          expect(ats(s, e.sku)).toBeGreaterThanOrEqual(0);
        }
        expect(checkInvariants(s), `after ${e.kind}`).toEqual([]);
      }
    }),
    { numRuns: 1000 },
  );
});

The first four constants are arbitraries for the small vocabulary of the domain: fc.constantFrom picks one item from a fixed list, and fc.integer({ min, max }) picks a whole number in a range. Keeping only three SKUs and two orders is deliberate: a small vocabulary makes collisions likely, and collisions are where bugs live. With ten thousand random SKU names, two events would never touch the same SKU and you would be testing nothing.

eventArb is an arbitrary for whole events. fc.record builds an object by generating each field from its own arbitrary. fc.constant('receipt' as const) pins the kind tag so TypeScript keeps the union narrow. fc.oneof picks one alternative, and the weight numbers bias the mix, making receipts and allocations three times as likely as adjustments, which roughly matches real ERP usage and stops generated sequences from being mostly rejected no-ops.

checkInvariants is the index card turned into code. For every SKU it recomputes on-hand from scratch by folding the ledger and compares that to the cached number (I1, as executable arithmetic), checks that physical stock is non-negative (I3), and checks that allocated quantities are never negative. Then it verifies that every invoice points at a real shipment and that no shipment appears twice (I4). It returns a list of strings rather than throwing, so one run reports every simultaneous violation instead of only the first.

Why I2 is checked at the moment of allocation

Read the comment where a check is missing, because that gap is the interesting design decision. A snapshot of the data alone cannot tell you whether negative available-to-sell is wrong. A legitimate stock write-down leaves you promising more than you hold, and the business calls that an oversell rather than a fault. So I2 is checked as a rule about the operation: right after the core accepts an allocate, available-to-sell for that SKU must still be zero or more. If a random sequence ever gets an over-promise past the guard in apply, that line catches it.

The test itself is four lines of meaning. fc.property(arbitrary, fn) declares the property: "for any array of up to 80 events, this function must not throw." fc.assert runs it 1000 times, overriding the default numRuns of 100. Inside, we start from an empty world, apply each event, and check all the invariants after every single event rather than only at the end. A system that violates I1 momentarily and then repairs itself is still broken, because a report might read it during the gap.

Shrinking, seeds, and replaying a failure

When the property fails, fast-check does not dump eighty events at you. Delete the on-hand guard from the ship case, run the suite, and this is what the terminal prints.

$ pnpm vitest run packages/domain

 FAIL  test/invariants.property.test.ts
       > no sequence of operations can break the invariants

 Property failed after 34 tests
 { seed: -1902337146, path: "33:2:0:1:0:0",
   endOnFailure: true }
 Counterexample: [
   [ {"kind":"receipt","sku":"TEE-BLK-M","qty":1},
     {"kind":"allocate","sku":"TEE-BLK-M","qty":1,
      "orderId":"SO-1001"},
     {"kind":"adjustment","sku":"TEE-BLK-M","qty":-1},
     {"kind":"ship","sku":"TEE-BLK-M","qty":1,
      "orderId":"SO-1001","shipmentId":"SH-A"} ]
 ]
 Shrunk 12 time(s)
 Got AssertionError: after ship
   expected [ 'I3 TEE-BLK-M: on-hand is negative' ]
   to deeply equal []

// Replaying the exact failure: paste seed + path back into
// fc.assert. runAndCheck is the same body used by the
// property above, pulled out into a named function.
it('regression: shipping must respect physical stock', () => {
  fc.assert(
    fc.property(fc.array(eventArb, { maxLength: 80 }), runAndCheck),
    { seed: -1902337146, path: '33:2:0:1:0:0', endOnFailure: true },
  );
});

What each part means:

• "Property failed after 34 tests" means the first 33 random sequences passed and the 34th did not.
• The seed is the number that generated that batch. Feed it back and you get byte-identical inputs, which is what makes a random test debuggable rather than maddening.
• The path is a breadcrumb trail into the shrinking tree, so the replay jumps straight to the minimal case instead of re-shrinking.
• endOnFailure: true tells fast-check not to shrink again on replay.

"Shrunk 12 time(s)" is the payoff: fast-check started from dozens of events with large quantities and cut it down twelve times, landing on four events with quantity 1. Receive one shirt, promise it to a customer, write it off after a cycle count, then ship it anyway. The counterexample is the bug report, and it reads like a sentence a warehouse manager would actually say. The repair is the guard printed in the core above: ship must check physical stock as well as the allocation.

Property tests explore. Example tests pin. So when you fix a bug a property found, always also keep the shrunk counterexample permanently, either as a plain example-based test or via fc.assert(prop, { examples: [[...]] }), which tries it first on every run. Otherwise a future edit to your arbitraries can quietly stop generating that shape, and the bug walks back in unnoticed six months later.

Model-based testing: comparing against a dumber implementation

A second style catches a different class of bug. In model-based testing you run the same random operations against two things at once and assert they always agree: the real implementation, and a deliberately stupid model that is obviously correct but far too slow to ship. The fast-check documentation notes that model-based testing "can also be referred to as Monkey testing to some extent", and gives it dedicated machinery, because a plain array of commands shrinks badly.

// packages/domain/test/model.test.ts
import { it } from 'vitest';
import fc from 'fast-check';
import { emptyState, apply, ats, type State } from '../src/core';

/** The model: NO caches at all. Recompute everything from the raw event log, every time. */
type Model = { events: { sku: string; qty: number }[]; allocated: Record<string, number> };

const onHandOf = (m: Model, sku: string) =>
  m.events.filter(e => e.sku === sku).reduce((a, e) => a + e.qty, 0);

class AllocateCommand implements fc.Command<Model, State> {
  constructor(readonly sku: string, readonly qty: number) {}

  // Only run this command when the MODEL says it is legal.
  check(m: Readonly<Model>): boolean {
    return this.qty <= onHandOf(m as Model, this.sku) - (m.allocated[this.sku] ?? 0);
  }

  run(m: Model, real: State): void {
    m.allocated[this.sku] = (m.allocated[this.sku] ?? 0) + this.qty;

    const r = apply(real, { kind: 'allocate', sku: this.sku, qty: this.qty, orderId: 'SO-1001' });
    if (!r.ok) throw new Error(`real rejected a legal allocation: ${r.error}`);
    Object.assign(real, r.state);

    const modelAts = onHandOf(m, this.sku) - (m.allocated[this.sku] ?? 0);
    if (modelAts !== ats(real, this.sku)) {
      throw new Error(`ATS drift on ${this.sku}: model ${modelAts} vs real ${ats(real, this.sku)}`);
    }
  }

  toString = () => `allocate(${this.sku}, ${this.qty})`;
}

it('the cached implementation always agrees with the recompute-from-scratch model', () => {
  const skus = fc.constantFrom('TEE-BLK-M', 'HOOD-GRY-L');
  const qty  = fc.integer({ min: 1, max: 12 });

  // ReceiptCommand and ShipCommand are written the same way as
  // AllocateCommand above; they are left out here for space.
  const commands = [
    fc.tuple(skus, qty).map(([s, q]) => new ReceiptCommand(s, q)),
    fc.tuple(skus, qty).map(([s, q]) => new AllocateCommand(s, q)),
    fc.tuple(skus, qty).map(([s, q]) => new ShipCommand(s, q)),
  ];

  fc.assert(
    fc.property(fc.commands(commands, { maxCommands: 50 }), (cmds) => {
      fc.modelRun(() => ({ model: { events: [], allocated: {} }, real: emptyState('acme') }), cmds);
    }),
    { numRuns: 500 },
  );
});

The Model type has no caches, just the raw list of stock movements plus allocations, and onHandOf recomputes a balance by summing the whole list every time. That is unacceptably slow at production scale, which is precisely why you built a cache in chapter 8. It is also transparently correct, which is what makes it a good referee.

Each command implements fast-check's Command interface, which has three members:

• check(model) returns true only when the command is legal according to the model, so fast-check skips illegal commands instead of generating garbage.
• run(model, real) performs the operation on both sides and then compares them. Any disagreement throws, which fails the property.
• toString() is what appears in the counterexample, so you read allocate(TEE-BLK-M, 7) rather than an object dump.

fc.commands(...) generates a random scenario, an ordered list of up to fifty command objects, and shrinks it by dropping whole commands rather than mangling arguments, while fc.modelRun(setup, cmds) runs the scenario against a fresh { model, real } pair. That setup function is called again on every shrink attempt, so both worlds must start clean each time.

This is the small-scale, in-process cousin of what TigerBeetle (a database built specifically for financial transactions) does at enormous scale with its VOPR simulator. The VOPR runs the real database code inside a fake world whose clock, network, and disks the test controls, then injects faults — dropped packets, corrupted writes, killed processes — across a whole cluster at roughly 1000× speed.

Their published figures: "3.3 seconds of VOPR simulation gives you 39 minutes of real-world testing time", and "a day gives you 2 years". You are not building a database, but the idea transfers exactly: make the system deterministic, then let a machine explore the state space while you sleep.

Seeded deterministic test data: modeling the weird cases

Property tests cover the domain core. They cannot cover the parts of your ERP that live in SQL, in row-level security policies, and in Next.js server actions. For those you need a database with data in it, and that means fixtures and a seed script.

A fixture is a known, fixed piece of test data: "tenant Acme Apparel, style TEE-BLK in three sizes, one open wholesale order." A seed script is the program that inserts fixtures into an empty database. The word seeded carries the same connotation as in fast-check: given the same starting conditions you get the same database every time. No Math.random(), no new Date(), no auto-generated identifiers. Use fixed IDs and fixed timestamps, so a test can assert "invoice INV-7 totals 4,312.50" and mean it.

Seeding happy-path data is easy: one order, fully shipped, fully invoiced. Resist it. The bugs are in the shapes nobody wants to think about.

// packages/db/seed/scenarios.ts
// Deterministic fixtures. Fixed IDs, fixed timestamps, no randomness anywhere.
import { sql } from './client';

const T = 'acme';                                                  // one tenant for every scenario
const D = (day: number) => `2026-03-${String(day).padStart(2, '0')}T09:00:00Z`;

export const scenarios = {
  /** S1 — Partial shipment. 24 ordered, 10 shipped, 14 still open. */
  async partialShipment() {
    await sql`insert into orders (id, tenant_id, buyer, status, created_at)
              values ('SO-4471', ${T}, 'Bergdorf', 'open', ${D(1)})`;
    await sql`insert into order_lines (id, tenant_id, order_id, sku, qty_ordered)
              values ('OL-1', ${T}, 'SO-4471', 'TEE-BLK-M', 24)`;
    await sql`insert into shipments (id, tenant_id, order_id, shipped_at)
              values ('SH-9001', ${T}, 'SO-4471', ${D(5)})`;
    await sql`insert into shipment_lines (id, tenant_id, shipment_id, order_line_id, sku, qty)
              values ('SL-1', ${T}, 'SH-9001', 'OL-1', 'TEE-BLK-M', 10)`;
  },

  /** S2 — Order revision AFTER partial shipment. Buyer cuts 24 down to 12; 10 already gone. */
  async revisionAfterShipment() {
    await this.partialShipment();
    await sql`update order_lines set qty_ordered = 12, revised_at = ${D(6)}
              where id = 'OL-1' and tenant_id = ${T}`;
    // Open balance is now 12 - 10 = 2. Cut the order to 8 instead and the
    // same arithmetic returns -2, which must clamp to 0 and raise an
    // over-shipped exception rather than flow into ATS as negative demand.
  },

  /** S3 — Negative adjustment colliding with an existing allocation: a legitimate oversell. */
  async negativeAdjustmentUnderAllocation() {
    await sql`insert into inventory_ledger (id, tenant_id, sku, qty, reason, occurred_at)
              values ('LG-1', ${T}, 'HOOD-GRY-L', 40, 'receipt', ${D(1)})`;
    await sql`insert into allocations (id, tenant_id, order_id, sku, qty)
              values ('AL-1', ${T}, 'SO-4472', 'HOOD-GRY-L', 35)`;
    await sql`insert into inventory_ledger (id, tenant_id, sku, qty, reason, occurred_at)
              values ('LG-2', ${T}, 'HOOD-GRY-L', -8, 'cycle_count', ${D(7)})`;
    // on_hand 32, allocated 35 => ATS is -3. Legal. Every report must survive rendering it.
  },

  /** S4 — Return of goods that were already invoiced. Credit note, never a deleted invoice. */
  async returnAfterInvoice() {
    await this.partialShipment();
    await sql`insert into invoices (id, tenant_id, order_id, issued_at, total_cents)
              values ('INV-7', ${T}, 'SO-4471', ${D(6)}, 431250)`;
    await sql`insert into invoice_lines (id, tenant_id, invoice_id, shipment_line_id, qty, unit_cents)
              values ('IL-1', ${T}, 'INV-7', 'SL-1', 10, 43125)`;
    await sql`insert into inventory_ledger (id, tenant_id, sku, qty, reason, occurred_at)
              values ('LG-3', ${T}, 'TEE-BLK-M', 3, 'return', ${D(12)})`;
    await sql`insert into credit_notes (id, tenant_id, invoice_id, qty, amount_cents, issued_at)
              values ('CN-1', ${T}, 'INV-7', 3, 129375, ${D(12)})`;
  },

  /** S5 — Cancelled, then un-cancelled. The classic state-machine trap. */
  async cancelledThenUncancelled() {
    await sql`insert into orders (id, tenant_id, buyer, status, created_at)
              values ('SO-4473', ${T}, 'Nordstrom', 'open', ${D(1)})`;
    await sql`insert into order_lines (id, tenant_id, order_id, sku, qty_ordered)
              values ('OL-9', ${T}, 'SO-4473', 'TEE-BLK-S', 60)`;
    await sql`insert into allocations (id, tenant_id, order_id, sku, qty)
              values ('AL-9', ${T}, 'SO-4473', 'TEE-BLK-S', 60)`;

    await sql`update orders set status = 'cancelled', cancelled_at = ${D(3)} where id = 'SO-4473'`;
    await sql`delete from allocations where id = 'AL-9'`;                     // release on cancel
    await sql`update orders set status = 'open', cancelled_at = null where id = 'SO-4473'`;
    // Deliberately do NOT re-create the allocation. The bug is that most code assumes it exists.
  },
};

T and D exist so every row shares one tenant and every timestamp is a stated calendar date rather than "now", which is what makes the fixture deterministic:

• S1 is the baseline: 24 ordered, 10 shipped, open balance 14.
• S2 layers a revision on top, cutting the order to 12 after 10 have shipped, which leaves 2 still to ship. Push the revision one step further, so that the buyer cuts to 8 when 10 have already gone, and the same subtraction yields −2. That negative number must be caught and reported as an over-shipment. Left alone it flows into your ATS as phantom demand and quietly corrupts what the sales team sees.
• S3 builds a legitimately negative available-to-sell: 40 received, 35 promised, then a cycle count writes off 8, leaving on-hand 32 against allocated 35. That is an oversell rather than a fault, and every report, export, and PDF has to render it without crashing and without showing "−3" to a salesperson as though it were sellable stock.
• S4 covers returns after invoicing: the invoice is never deleted or edited. It is answered by a credit note, because an issued invoice is a legal document and mutating one destroys the audit trail.
• S5 is the nastiest and the most common in practice: an order is canceled (releasing its allocation) and then un-canceled by a salesperson who changed their mind. The order is open again, but the allocation is gone, and half your code assumes an open order has allocations. This fixture makes that assumption fail loudly in CI rather than quietly in March.

Treat this file as a museum of every incident you have ever had. Each time production surprises you, ship two commits: one that repairs the code, and one that adds the surprising shape here. Over a year it becomes the most valuable artifact in the repository: a machine-readable list of what the apparel business actually does, as opposed to what the specification said it does.

Snapshot and golden tests for financial documents

A snapshot test (also called a golden test) works differently from everything above. You do not write down the expected output. You run the code once, read the output carefully, then commit it to the repository as the golden file. Every future run regenerates the output and compares it against that committed version. If a single character differs, the test fails and shows you the difference.

This sounds lazy. For financial documents it is the right tool, because an invoice carries a hundred values at once, plus their arrangement, formatting, and the rounding rules that connect them. A unit test asserting total === 431250 passes happily while the tax line disappears, the currency flips to dollars, the discount is applied before tax instead of after, and the buyer's address renders as [object Object]. A snapshot catches all of those at once, because it compares the entire document.

// apps/web/test/invoice.golden.test.ts
import { it, expect } from 'vitest';
import { buildInvoiceDocument, renderInvoicePdf, extractTextLines } from '@repo/domain/invoicing';
import { scenarios, resetDb } from '@repo/db/seed';

/** Strip anything that legitimately changes between runs, so the diff shows only real changes. */
function normalise(doc: unknown) {
  return JSON.parse(
    JSON.stringify(doc, (key, value) => {
      if (key === 'generatedAt')       return '<TIMESTAMP>';
      if (key === 'pdfChecksum')       return '<CHECKSUM>';
      if (key === 'renderedByVersion') return '<VERSION>';
      return value;
    }),
  );
}

it('invoice for a partially shipped, partially returned order', async () => {
  await resetDb();
  await scenarios.returnAfterInvoice();

  const doc = await buildInvoiceDocument({ tenantId: 'acme', invoiceId: 'INV-7' });

  await expect(normalise(doc)).toMatchFileSnapshot('./__golden__/INV-7.json');
});

it('invoice PDF layout is stable', async () => {
  await resetDb();
  await scenarios.returnAfterInvoice();

  // deterministic: true pins the embedded creation date and document id inside the PDF.
  const pdf   = await renderInvoicePdf({ tenantId: 'acme', invoiceId: 'INV-7', deterministic: true });
  const lines = await extractTextLines(pdf);        // array of visible text lines, in layout order

  await expect(lines.join('\n')).toMatchFileSnapshot('./__golden__/INV-7.pdf.txt');
});

resetDb() empties every table (SQL calls that truncation) so each test starts from a known empty world. scenarios.returnAfterInvoice() loads fixture S4. buildInvoiceDocument returns the full invoice as a plain object: header, buyer, lines, discounts, tax, credit notes, totals.

normalise is the part beginners skip and then regret. Some fields legitimately differ on every run: the generation timestamp, a checksum, a build version. If you snapshot those raw, the test fails every single time, and your team quickly learns to blind-update snapshots, which destroys their value. So the replacer function passed to JSON.stringify swaps volatile fields for fixed placeholders. Everything else — every quantity, every cent, every ordering decision — stays exact.

toMatchFileSnapshot('./__golden__/INV-7.json') writes the golden file on the first run and compares against it forever after. Because it is a real file tracked in git, invoice changes surface in code review as a readable diff: a reviewer literally sees - "creditNoteCents": 129375 next to + "creditNoteCents": 0, and asks where the credit note went. That is a conversation that would otherwise never have happened.

The second test does the same for the PDF, with two tricks. deterministic: true pins the embedded creation date and document identifier, because PDFs normally embed a timestamp that would make every byte differ. And rather than snapshotting binary bytes we extract the visible text lines: a binary diff tells you nothing, whereas a text diff shows plainly that the "Credit note CN-1" line has moved above the totals block.

Invariant checks as production jobs: the accounting close

This section is what separates money software from normal software. Everything so far runs on your laptop and in CI, against fake data. Your invariants, though, are claims about production. So run them in production, on a schedule, forever.

A shop at closing time counts the till, note by note, and compares the total against the day's receipts. Nobody expects a discrepancy. The count happens anyway, every night, because the entire point is to catch the one night when there is one, while the day is still fresh enough to reconstruct. Accountants call this a close. You are going to build one.

Modern Treasury, who build payments and ledger infrastructure for financial companies, state the underlying principle plainly in their Accounting for Developers series: "it's more accurate to store immutable transactions and always compute balances from those transactions. Mutating balances directly creates a system that is prone to errors." The corollary is that if you keep both a ledger and a cache (and you do, for chapter 8's performance reasons), you must continuously verify they still agree.

Start with the SQL. Each invariant becomes one query that returns zero rows when the world is healthy. A view, used below, is a saved query you can select from by name, as though it were a table.

-- supabase/checks/invariants.sql
-- Each view returns ZERO rows when healthy. Any row at all is a violation.

-- I1: the ATS cache must equal a fresh fold of the append-only ledger.
create or replace view check_i1_ledger_vs_cache as
select
  l.tenant_id,
  l.sku,
  sum(l.qty)                          as ledger_qty,
  coalesce(c.on_hand, 0)              as cached_qty,
  sum(l.qty) - coalesce(c.on_hand, 0) as drift
from inventory_ledger l
left join ats_cache c
       on c.tenant_id = l.tenant_id and c.sku = l.sku
group by l.tenant_id, l.sku, c.on_hand
having sum(l.qty) <> coalesce(c.on_hand, 0);

-- I2: promised more than you hold. Legal after a write-down, but a human
-- in sales must see it today. This one warns; it does not page.
create or replace view check_i2_oversold as
select
  a.tenant_id,
  a.sku,
  sum(a.qty)                          as allocated_qty,
  coalesce(c.on_hand, 0)              as on_hand,
  coalesce(c.on_hand, 0) - sum(a.qty) as ats
from allocations a
left join ats_cache c
       on c.tenant_id = a.tenant_id and c.sku = a.sku
group by a.tenant_id, a.sku, c.on_hand
having coalesce(c.on_hand, 0) - sum(a.qty) < 0;

-- I3: physical stock can never be negative (ATS can be; on-hand cannot).
create or replace view check_i3_negative_on_hand as
select tenant_id, sku, sum(qty) as on_hand
from inventory_ledger
group by tenant_id, sku
having sum(qty) < 0;

-- I4a: every invoice line must reference a real shipment line, same tenant, sufficient qty.
create or replace view check_i4_orphan_invoice_lines as
select il.tenant_id, il.invoice_id, il.id as invoice_line_id, il.shipment_line_id
from invoice_lines il
left join shipment_lines sl
       on sl.id = il.shipment_line_id
      and sl.tenant_id = il.tenant_id
where sl.id is null
   or sl.qty < il.qty;

-- I4b: no shipment line may be invoiced twice (this is double-billing a customer).
create or replace view check_i4_double_invoiced as
select tenant_id, shipment_line_id, count(*) as times_invoiced
from invoice_lines
group by tenant_id, shipment_line_id
having count(*) > 1;

-- I5: cross-tenant references. The single worst bug class in multi-tenant software.
create or replace view check_i5_cross_tenant_order_lines as
select ol.tenant_id as line_tenant, o.tenant_id as order_tenant, ol.id as order_line_id
from order_lines ol
join orders o on o.id = ol.order_id
where o.tenant_id <> ol.tenant_id;

Every one of these is a view, so select * from check_i1_ledger_vs_cache re-runs the query on live data:

• check_i1_ledger_vs_cache groups the ledger by tenant and SKU, sums the quantities, left-joins the cache, and the having clause keeps only rows where the two disagree. The drift column is deliberately included: knowing the cache is wrong is useful, but knowing it is wrong by −14 tells you it was probably one missed shipment.
• check_i4_orphan_invoice_lines uses a left join and filters for sl.id is null (the standard SQL idiom for "find rows with no match") and also catches invoice lines billing more units than shipped.
• check_i4_double_invoiced counts invoice lines per shipment line and flags anything above one. That single query is the difference between an honest business and an accidental fraud allegation.
• check_i2_oversold is the odd one out: it lists SKUs promised beyond available stock, which is legal after a write-down, so it belongs on somebody's morning dashboard rather than on a 3am phone call.
• check_i5 flags any order line whose tenant differs from its order's, which should be impossible under correct row-level security, the Postgres feature from chapter 5 that attaches a "only your own rows" filter to every query automatically.

Checking the impossible is exactly the point, because "impossible" is where the worst bugs live. Notice too that sl.tenant_id = il.tenant_id sits inside the join in I4a: chapter 5's rule that every query carries the tenant applies to your safety checks too.

Now the job that runs them.

// apps/worker/src/jobs/invariant-close.ts
import { sql } from '@repo/db';
import { logger } from '@repo/observability';
import { page } from '@repo/observability/pager';

const CHECKS = [
  { id: 'I1',  view: 'check_i1_ledger_vs_cache',          severity: 'page', desc: 'ATS cache drifted from ledger' },
  { id: 'I2',  view: 'check_i2_oversold',                 severity: 'warn', desc: 'Oversold SKU (promised beyond stock)' },
  { id: 'I3',  view: 'check_i3_negative_on_hand',         severity: 'page', desc: 'Negative physical stock' },
  { id: 'I4a', view: 'check_i4_orphan_invoice_lines',     severity: 'page', desc: 'Invoice line without a shipment' },
  { id: 'I4b', view: 'check_i4_double_invoiced',          severity: 'page', desc: 'Shipment line invoiced twice' },
  { id: 'I5',  view: 'check_i5_cross_tenant_order_lines', severity: 'page', desc: 'CROSS-TENANT LEAK' },
] as const;

const SAMPLE_ROWS = 5;   // enough to debug from, few enough not to dump a table into a log

export async function invariantClose() {
  const runId = crypto.randomUUID();
  const startedAt = Date.now();
  let totalViolations = 0;

  for (const check of CHECKS) {
    const rows = await sql.unsafe(`select * from ${check.view} limit ${SAMPLE_ROWS}`);
    const [{ count }] = await sql.unsafe(`select count(*)::int as count from ${check.view}`);

    await sql`insert into invariant_runs (run_id, check_id, violation_count, sample, ran_at)
              values (${runId}, ${check.id}, ${count}, ${JSON.stringify(rows)}, now())`;

    if (count === 0) {
      logger.info({ msg: 'invariant.ok', runId, checkId: check.id });
      continue;
    }

    totalViolations += count;
    const tenants = [...new Set(rows.map((r: any) => r.tenant_id ?? r.line_tenant))];

    logger.error({
      msg: 'invariant.violated',
      runId,
      checkId: check.id,
      description: check.desc,
      violationCount: count,
      affectedTenants: tenants,
      sample: rows,
    });

    if (check.severity === 'page') {
      await page({
        dedupKey: `invariant-${check.id}`,                 // ONE incident, not one page per row
        title:    `[${check.id}] ${check.desc} — ${count} row(s)`,
        runbook:  `https://wiki.internal/runbooks/invariant-${check.id}`,
        details:  { runId, affectedTenants: tenants, sample: rows },
      });
    }
  }

  logger.info({ msg: 'invariant.close.complete', runId, totalViolations, durationMs: Date.now() - startedAt });

  // Freshness alarm: if this job silently stops running, nobody finds out unless we say so.
  await sql`insert into heartbeats (name, beat_at) values ('invariant-close', now())
            on conflict (name) do update set beat_at = now()`;
}

CHECKS is a manifest: a plain list of checks, each with an ID, the view to query, a severity, and a human description. Adding another invariant means one new row here and one new view in SQL, with no branching logic to touch. The severity does real work: page wakes somebody up, warn only writes the log line and the history row, which is why the oversell check can share this job without ever ringing a phone.

For each check the job runs two queries: one to fetch a handful of sample rows, one to count all of them. Both results go into an invariant_runs table, which gives you history. You can later ask "when did I1 first start drifting?" and get an answer to the minute. If the count is zero it logs a quiet success and moves on, and those success lines are what let you build a dashboard that shows green.

What happens when a check fails

If the count is non-zero it collects the distinct affected tenants, writes a structured error log carrying the check ID, the count, the tenants, and up to five sample rows, which is enough context to begin debugging from the log alone at 3am. Then, for a check marked page, it pages a human.

The dedupKey is essential: without it, a check finding 900 broken rows opens 900 incidents and destroys your phone. With it, the pager service groups them into one incident that auto-resolves when the next clean run reports zero. The runbook link does real work too: it points at a written page saying "what I1 means, what usually causes it, how to safely rebuild the cache, who to tell." Write that page on a quiet afternoon, well before the first incident.

The final heartbeats statement is an upsert (insert the row if it is missing, update it if it is already there, in one statement), and it closes a gap that catches almost everyone. An alert that fires on violations is silent in two situations: when everything is fine, and when the job itself is dead. Those look identical from the outside. So the job records a heartbeat on every successful completion, and you configure a separate alert: "page if invariant-close has not beaten in 90 minutes." Now silence means healthy, and only healthy.

Boring, reliable job infrastructure

All of the above needs somewhere to run:

• the invariant close,
• the invoice PDF generation,
• the outbox drain from chapter 3,
• the ATS recomputation from chapter 8,
• the nightly import from chapter 7.

That somewhere is a job queue, and beginners routinely make an expensive mistake here: reaching for Redis or Kafka on day one. Both are excellent pieces of software. Both are also a second database: another service to deploy, secure, back up, and monitor, and, above all, another consistency boundary.

The moment your queue lives outside Postgres, "insert the shipment and enqueue the invoice job" stops being one atomic operation, one where either both parts happen or neither does. You get the other outcome instead: the shipment commits and the job is lost, or the job runs against a shipment that was rolled back.

Postgres already ships the primitive that job queues need: SELECT ... FOR UPDATE SKIP LOCKED. Chapter 2's discussion of locks explains the mechanics. The short version is that when ten workers simultaneously ask for the next job, each locks a different row and the others skip past locked rows instead of waiting behind them. No worker blocks, no job is handed out twice. As pg-boss's own documentation puts it, SKIP LOCKED is "a feature built specifically for message queues to resolve record locking challenges inherent with relational databases", and it is the engine underneath both libraries below.

Two rows in the table mention LISTEN/NOTIFY. That is a message channel built into Postgres: one connection shouts "new job", and any connection that is listening hears it straight away, instead of asking again every few seconds.

Graphile Worker's own documentation states the rationale better than I can: consolidating infrastructure makes sense because "when you're working with a small number of engineers on a project, the more infrastructure you have, the more time you lose to maintenance of that infrastructure." Use Postgres until it visibly hurts. It will take longer to hurt than you think.

A real pg-boss setup for the ERP looks like this.

// apps/worker/src/boot.ts
import { PgBoss } from 'pg-boss';
import { logger } from '@repo/observability';
import { invariantClose } from './jobs/invariant-close';
import { generateInvoicePdf } from './jobs/invoice-pdf';
import { rebuildAtsForTenant } from './jobs/rebuild-ats';

export async function boot() {
  const boss = new PgBoss({
    connectionString: process.env.DATABASE_URL!,
    schema: 'pgboss',
    useListenNotify: true,        // wake workers instantly instead of waiting for the next poll
  });

  boss.on('error', (err) => logger.error({ msg: 'pgboss.error', err }));
  await boss.start();

  // ---- queue definitions -------------------------------------------------
  await boss.createQueue('invoice-pdf-dlq');                 // dead letter queue must exist first
  await boss.createQueue('invoice-pdf', {
    retryLimit: 5,                // 5 retries, so 6 attempts in all
    retryDelay: 5,                // seconds before the first retry
    retryBackoff: true,           // then roughly 10s, 20s, 40s ... with jitter
    retryDelayMax: 3600,          // never wait more than an hour between attempts
    deadLetter: 'invoice-pdf-dlq',
    expireInSeconds: 300,         // 5 minutes with no result means it is stuck
    notify: true,
  });

  // One ATS rebuild at a time; extra requests queue up rather than fight each other.
  await boss.createQueue('rebuild-ats', { policy: 'singleton', retryLimit: 3, retryBackoff: true });

  // The nightly close must never overlap with itself.
  await boss.createQueue('invariant-close', { policy: 'exclusive', retryLimit: 2, retryDelay: 300 });

  // ---- workers -----------------------------------------------------------
  await boss.work('invoice-pdf', { batchSize: 5 }, async (jobs) => {
    for (const job of jobs) await generateInvoicePdf(job.data, { jobId: job.id });
  });
  await boss.work('rebuild-ats',      async ([job]) => rebuildAtsForTenant(job.data, { jobId: job.id }));
  await boss.work('invariant-close',  async ()      => invariantClose());

  // ---- schedules (cron, in UTC) -----------------------------------------
  await boss.schedule('invariant-close', '15 2 * * *', {}, { tz: 'UTC' });   // 02:15 every night

  return boss;
}

Each piece, with the vocabulary defined as it appears. new PgBoss({...}) points the library at your existing database and keeps its own tables in a dedicated pgboss schema so they do not clutter yours. useListenNotify: true starts a listener, so that when a job is inserted Postgres pushes a notification and a worker wakes immediately. Without it, workers poll every couple of seconds, which is fine for nightly work and sluggish for a PDF a user is waiting on. The instance-level useListenNotify runs the listener. The per-queue notify: true opts that queue into emitting notifications.

createQueue declares a queue up front. pg-boss v12 requires this, which is a good thing: it makes every queue's policy explicit in code rather than implicit in whoever happened to call send first. The retry settings are the important part. Retry means: if the job throws, run it again later. Backoff means: wait longer between each successive attempt, so a struggling downstream service is not hammered. pg-boss's documented approximation is retryDelay * 2 ^ retryCount with jitter applied and capped by retryDelayMax. Jitter is deliberate randomness in the delay. Without it, a thousand jobs that failed together retry together and produce a thundering herd.

Dead letter queue is where jobs go to be examined after they exhaust every retry. Setting deadLetter: 'invoice-pdf-dlq' means a PDF that fails six times is copied into that queue rather than vanishing. You then alert on that queue having any depth at all, investigate, fix the cause, and use pg-boss's redrive() to move the jobs back to their source queue. A queue without a dead letter is a queue that silently eats money.

Queue policies, workers, and cron

The policy settings encode concurrency rules. singleton allows only one active job at a time with unlimited queued, which is exactly right for ATS rebuilds, since two simultaneous rebuilds of the same tenant would fight over the same cache rows. exclusive allows only one job queued or active, which is what the nightly close needs: if last night's run is somehow still going, do not start a second. pg-boss also offers short, stately, and key_strict_fifo, the last enforcing strict first-in-first-out ordering per singletonKey, which is useful when every job for order SO-4471 must run in sequence.

boss.work(queue, handler) registers a worker: a long-lived loop that pulls jobs and runs your function, with batchSize: 5 handing it up to five at once to amortize the round trip. boss.schedule(queue, cron, data, options) registers a cron entry: a recurring timer in the five-field syntax minute-hour-day-month-weekday, so '15 2 * * *' means 02:15 daily. Always pin the timezone explicitly, because "2am" moves twice a year and your accountants will notice.

Graphile Worker solves the same problems with a different flavor, and its one unique trick is worth showing.

// apps/worker/src/graphile.ts
import { run, type Task } from 'graphile-worker';

// A task is just an async function. The first argument is the JSON payload you enqueued.
const rebuildAts: Task<{ tenantId: string; sku: string }> = async (payload, helpers) => {
  helpers.logger.info(`rebuilding ATS for ${payload.tenantId}/${payload.sku}`);

  await helpers.withPgClient(async (pg) => {
    await pg.query(
      `insert into ats_cache (tenant_id, sku, on_hand, rebuilt_at)
       select tenant_id, sku, sum(qty), now()
         from inventory_ledger
        where tenant_id = $1 and sku = $2
        group by tenant_id, sku
       on conflict (tenant_id, sku)
       do update set on_hand = excluded.on_hand, rebuilt_at = excluded.rebuilt_at`,
      [payload.tenantId, payload.sku],
    );
  });
};

await run({
  connectionString: process.env.DATABASE_URL!,
  concurrency: 8,
  crontabFile: `${__dirname}/../crontab`,
  taskList: { rebuild_ats: rebuildAts, invariant_close: invariantCloseTask },
});

-- supabase/migrations/0042_ats_rebuild_trigger.sql
-- Enqueue a rebuild straight from the database, in the SAME transaction as the write.
create or replace function enqueue_ats_rebuild() returns trigger
language plpgsql as $$
begin
  perform graphile_worker.add_job(
    'rebuild_ats',
    payload      => json_build_object('tenantId', new.tenant_id, 'sku', new.sku),
    job_key      => 'ats:' || new.tenant_id || ':' || new.sku,   -- de-duplicate per tenant+SKU
    job_key_mode => 'preserve_run_at',                            -- throttle, do not push it back
    run_at       => now() + interval '5 seconds',
    max_attempts => 10,
    priority     => 0
  );
  return new;
end;
$$;

create trigger inventory_ledger_ats_rebuild
after insert on inventory_ledger
for each row execute function enqueue_ats_rebuild();

The TypeScript half first. A task in Graphile Worker is an ordinary async function. payload is the JSON you enqueued and helpers provides a scoped logger plus withPgClient, a connection borrowed from the worker's own pool. The SQL inside is an upsert that recomputes on-hand from the ledger and writes it to the cache, precisely the repair your I1 check would demand. run({...}) starts the worker with a concurrency of 8, a crontab file, and a taskList mapping task names to functions.

The SQL half is the part most queues cannot do. A trigger (a function the database runs by itself whenever a row is inserted, updated, or deleted) fires on every insert into inventory_ledger and calls graphile_worker.add_job() directly. Because the trigger runs inside the same transaction as the ledger insert, the job and the data commit or roll back together: outbox semantics for free, with no outbox table. If the ledger write rolls back, the job never existed.

Deduplication, debouncing, and throttling

job_key is the deduplication key. Every rebuild for acme/TEE-BLK-M shares one key, so a thousand ledger inserts for that SKU within five seconds produce one queued rebuild, not a thousand.

job_key_mode controls what happens on collision. The default, replace, also resets run_at, giving you debouncing, where the job keeps getting pushed back while events keep arriving. We chose preserve_run_at, which keeps the original scheduled time and therefore gives throttling: it runs at most once per window, five seconds after the first event, no matter how many follow. That distinction matters, because debouncing can starve forever under continuous load and throttling cannot. (The third mode, unsafe_dedupe, skips the update even for locked or permanently failed jobs, and the documentation explicitly calls it dangerous. Believe it.)

max_attempts defaults to 25 in Graphile Worker, with the delay between attempts set by the formula exp(least(10, attempt)) seconds. That is about 2.7 seconds before the first retry, 3 hours 34 minutes of waiting accumulated by the ninth, and then a steady 6 hours 7 minutes between every attempt after that. The published table adds up to 95 hours of waiting across the 24 retries, roughly four days, though the project's own introduction page rounds it to "~3 days". We reduced it to 10 attempts, because a cache rebuild that has failed ten times needs a human rather than an eleventh attempt.

Idempotent jobs and at-least-once delivery

One term to nail down, because it is load-bearing. An idempotent job produces the same result whether it runs once or five times. Both libraries promise at-least-once delivery under crashes, because a worker can die after finishing the work but before marking the job complete, and the job then runs again. So the rebuild above is an upsert (safe to repeat), and your invoice generator must check "does an invoice already exist for this shipment?" before creating one. Chapter 3's idempotency keys are the general tool. Write every job as though it will run twice, because eventually it will.

And a rule of thumb about infrastructure generally: add Redis only after you have measured a specific Postgres bottleneck that Redis is known to solve. A blog post recommending it does not count as a measurement. For an apparel ERP with a few hundred tenants and tens of thousands of jobs a day, Postgres-backed queues will not be the limiting factor for years. The limiting factor will be your ability to reason about the system at 3am, and every extra moving part makes that strictly worse.

Structured logging and correlation IDs

A buyer emails on an ordinary weekday morning: "order SO-4471 shows as shipped but we never received an invoice." That order touched a Next.js server action, a database transaction, an outbox row (the small table from chapter 3 that hands work to background jobs), a background job, a webhook from the logistics provider, and a PDF renderer. Six places. Can you reconstruct what happened, in order, in under five minutes?

You can, if you did one thing: attach a correlation ID to everything. A correlation ID (also called a request ID or trace ID) is a random identifier generated at the very start of a request and carried through every subsequent operation that request causes, including into background jobs and out to third-party APIs. Every log line records it. Searching for that single ID reassembles the whole story from six systems into one timeline.

// packages/observability/src/context.ts
import { AsyncLocalStorage } from 'node:async_hooks';
import pino from 'pino';

export type Ctx = {
  correlationId: string;      // one per inbound request; survives into jobs and webhooks
  causationId?: string;       // the id of the thing that DIRECTLY caused this step
  tenantId?: string;
  userId?: string;
  orderId?: string;
};

const store = new AsyncLocalStorage<Ctx>();

const base = pino({
  level: process.env.LOG_LEVEL ?? 'info',
  redact: ['req.headers.authorization', '*.email', '*.taxId', '*.bankAccount'],
  formatters: { level: (label) => ({ level: label }) },
});

const LEVELS = ['trace', 'debug', 'info', 'warn', 'error', 'fatal'] as const;

/** A logger that automatically stamps every line with the current request context. */
export const logger = new Proxy(base, {
  get(target, prop: string) {
    if (!LEVELS.includes(prop as any)) return (target as any)[prop];
    return (obj: Record<string, unknown>, ...rest: unknown[]) =>
      (target as any)[prop]({ ...store.getStore(), ...obj }, ...rest);
  },
});

export function withContext<T>(ctx: Ctx, fn: () => Promise<T>): Promise<T> {
  return store.run(ctx, fn);
}

export const currentContext = () => store.getStore();

// apps/web/src/app/actions/ship-order.ts
'use server';
import { headers } from 'next/headers';
import { withContext, logger } from '@repo/observability';
import { db } from '@repo/db';

export async function shipOrder(input: { orderId: string; lines: { sku: string; qty: number }[] }) {
  const correlationId = (await headers()).get('x-correlation-id') ?? crypto.randomUUID();
  const { tenantId, userId } = await requireSession();

  return withContext({ correlationId, tenantId, userId, orderId: input.orderId }, async () => {
    logger.info({ msg: 'ship_order.started', lineCount: input.lines.length });

    try {
      const shipment = await db.transaction(async (tx) => {
        const sh = await createShipment(tx, input);
        await postLedgerRows(tx, sh);

        // The outbox row carries the correlation id ACROSS the process boundary.
        await tx`insert into outbox (id, tenant_id, topic, payload, correlation_id, created_at)
                 values (${crypto.randomUUID()}, ${tenantId}, 'invoice.requested',
                         ${JSON.stringify({ shipmentId: sh.id })}, ${correlationId}, now())`;
        return sh;
      });

      logger.info({ msg: 'ship_order.committed', shipmentId: shipment.id });
      return { ok: true, shipmentId: shipment.id };
    } catch (err) {
      logger.error({ msg: 'ship_order.failed', err });
      throw err;
    }
  });
}

The first file builds the plumbing. AsyncLocalStorage is a Node feature that holds a value for the duration of an asynchronous call chain: a variable scoped to "this request", visible to every function the request calls, without threading a parameter through forty signatures. pino is the logger. It writes JSON, which is what makes the logs structured. The redact list is not optional in an ERP: authorization headers, customer emails, tax identifiers, and bank details must never reach your log storage, both for privacy law and because logs are read by far more people than your database is.

The Proxy wrapper intercepts calls to logger.info and its siblings and merges the current context into every log object automatically. You write logger.info({ msg: 'ship_order.started' }) and the output still contains the correlation ID, tenant ID, user ID, and order ID without your having remembered to add them. Discipline that depends on remembering breaks in the first busy week. Note the two identifier fields: correlationId stays constant across the entire causal chain, while causationId identifies the immediate parent step, so together they answer both "show me everything about this request" and "what directly triggered this particular job?"

Carrying the ID across the process boundary

The server action shows the pattern in use. It reads a correlation ID from an inbound header if one exists (so a request forwarded from another service keeps its identity) or mints a fresh one, then withContext(...) wraps the whole operation so every log line inside inherits it.

The transaction creates the shipment, posts ledger rows, and writes an outbox row that carries the correlation ID as a column. When the outbox drain job picks that row up minutes later in a different process, it reads correlation_id and opens its own withContext with the same value. The chain survives the process boundary.

Every log field uses a stable machine name like ship_order.committed rather than an English sentence, because you will build alerts and dashboards on those names.

Here is the whole investigation.

# 1. Find the correlation id from any log line mentioning
#    the order. logcli is the command-line client for Loki,
#    Grafana's log store; psql is the Postgres shell.
$ logcli query '{app="erp"} |= "SO-4471" | json
    | line_format "{{.correlationId}}"' --limit 1
9f3c1e7a-4b52-4f2f-9d61-0c5e2a7b1188

# 2. Replay the whole causal chain, across every process,
#    in time order.
$ logcli query '{app="erp"} | json
    | correlationId="9f3c1e7a-4b52-4f2f-9d61-0c5e2a7b1188"' \
    --from=2026-03-05T00:00:00Z \
    --to=2026-03-06T00:00:00Z --forward

10:02:11.204 web    ship_order.started   orderId=SO-4471
                                         tenantId=acme lineCount=3
10:02:11.661 web    ship_order.committed shipmentId=SH-9001
10:02:12.010 worker outbox.claimed       topic=invoice.requested
10:02:12.118 worker invoice.created      invoiceId=INV-7
                                         totalCents=431250
10:02:12.402 worker job.enqueued         queue=invoice-pdf
10:02:13.955 worker invoice_pdf.failed   attempt=1
                                         err="font asset 404"
10:02:19.010 worker invoice_pdf.failed   attempt=2
                                         err="font asset 404"
10:03:31.774 worker invoice_pdf.failed   attempt=6
                                         err="font asset 404"
10:03:31.780 worker job.deadlettered     queue=invoice-pdf-dlq

# 3. Confirm it directly against the queue tables.
$ psql "$DATABASE_URL" -c "
    select id, state, retry_count, output->>'message' as err
      from pgboss.job
     where name = 'invoice-pdf-dlq'
       and data->>'shipmentId' = 'SH-9001';"

Step 1 searches all logs for the order number and extracts the correlation ID from the first match. (|= "SO-4471" keeps lines containing that text, | json parses each line's JSON into fields, and line_format prints just one of them.)

Step 2 filters on that ID alone, across every application, in forward chronological order, and the answer is immediately visible. The invoice was created. The PDF job then failed six times because a font file the renderer needed returned HTTP 404 (the web server's "no such file"), and the job was dead-lettered. The buyer is right: no PDF was ever produced.

Step 3 confirms it in the pg-boss dead letter queue table, showing the stored error message. Total time: about ninety seconds, with no guessing.

Without correlation IDs, this same investigation means grepping three log streams for the order number, discovering that the PDF worker never logged the order number at all (it only ever knew the shipment ID), and eventually giving up and asking someone to check the database by hand. So design your logs for the question "what happened to order #4471?", because that is the only question anyone ever asks: every line gets a correlation ID, a tenant ID, and a stable event name, and every boundary crossing carries the correlation ID forward.

Backups you have actually rehearsed

The least glamorous discipline in the book is also the one most likely to save the company.

What WAL and PITR actually are

Postgres keeps a diary. Before it changes any data file on disk, it first writes a description of that change to a sequential log called the WAL (Write-Ahead Log). The official documentation is blunt: Postgres "maintains a write ahead log (WAL) in the pg_wal/ subdirectory of the cluster's data directory. The log records every change made to the database's data files." The diary is written first and the data second, which is what "write-ahead" means, and it exists primarily so a crashed server can replay it and get back to a consistent state.

That same diary enables more than crash recovery. If you take one complete copy of the database (a base backup) and then keep every WAL file written since, you can restore the base backup and replay the diary forward, stopping wherever you like.

The documentation again: "It is not necessary to replay the WAL entries all the way to the end. We could stop the replay at any point and have a consistent snapshot of the database as it was at that time." That is PITR (Point-In-Time Recovery): restore to 14:22:03 last Tuesday, one second before the bad migration ran.

Continuously copying WAL files somewhere safe is called WAL archiving (archive_mode = on plus an archive_command), and the stopping point is set with recovery_target_time. One constraint to internalize: that stop point must be after the base backup finished. A base backup from Sunday plus WAL from Sunday onward lets you land anywhere from Sunday to now, but nowhere before Sunday.

# What a self-managed PITR restore actually looks like.
# Read it once so the managed version below stops being magic.

# --- ongoing, on the primary (postgresql.conf) ---
# wal_level = replica
# archive_mode = on
# archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
#   %p = full path of the WAL file to archive, %f = its filename.
#   Must exit 0 ONLY on success: on exit 0, Postgres recycles
#   its own local copy of that file.

# --- weekly: take a base backup ---
pg_basebackup -h db.internal -U replicator \
  -D /backups/base-2026-03-01 -Ft -z -Xs -P

# --- the restore, on a SPARE machine, never the live one ---
systemctl stop postgresql
rm -rf /var/lib/postgresql/17/main/*
tar -xzf /backups/base-2026-03-01/base.tar.gz \
  -C /var/lib/postgresql/17/main/

cat >> /var/lib/postgresql/17/main/postgresql.auto.conf <<'EOF'
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-03-05 14:22:03+00'
recovery_target_action = 'promote'
EOF

# The empty signal file tells PG to boot into recovery mode.
touch /var/lib/postgresql/17/main/recovery.signal
systemctl start postgresql

# Verify BEFORE anyone connects. An unverified restore is
# still only a guess.
psql -c "select max(occurred_at) from inventory_ledger;"
psql -c "select count(*) from orders
         where created_at > '2026-03-05 14:00Z';"

The three postgresql.conf settings turn archiving on:

• wal_level = replica makes the WAL detailed enough to rebuild from,
• archive_mode = on enables archiving,
• and archive_command is the shell command Postgres runs for each completed WAL segment (normally 16MB each).

The test ! -f guard refuses to overwrite an already-archived file, because on exit code 0 Postgres assumes the file is safely stored and recycles its local copy. An archive command that lies about success silently destroys your ability to recover.

pg_basebackup takes the full copy: -Ft tar format, -z compressed, -Xs to stream the WAL generated during the backup itself, -P for a progress meter.

The restore section stops Postgres on a spare machine, empties the data directory, unpacks the base backup, and writes three recovery settings: restore_command (the mirror image of archive_command, which fetches an archived WAL file back), recovery_target_time (your chosen moment), and recovery_target_action = 'promote' (once you reach that moment, stop replaying and become a normal writable database). The empty recovery.signal file is what tells Postgres to boot into recovery mode.

The two verification queries at the end are the entire point of the exercise.

What Supabase gives you, and what it does not

On Supabase you do not run those commands. The platform does. What you need to know are the parameters, because they define your RPO and RTO whether you think about them or not.

• Daily backups come with paid plans: 7 days of retention on Pro, 14 on Team, up to 30 on Enterprise (figures as of July 2026). The Free plan gets none at all, so you export your own data with the Supabase CLI. Granularity is one snapshot per day, so your worst-case RPO is 24 hours of lost data. For an ERP recording shipments and invoices, that is not survivable.
• PITR is a paid add-on (Pro, Team, or Enterprise, and it requires at least the Small compute add-on) with 7, 14, or 28 day retention windows, priced at $100 per month for each 7 days of retention, so $100 to $400 depending on the window, as of July 2026. It uses WAL-G for physical backups and WAL archiving, and "by default, we back up WAL files at two-minute intervals". You can aim at "any chosen point with up to seconds of granularity", but because WAL ships every two minutes the worst-case RPO is two minutes.
• Enabling PITR replaces daily backups. You no longer get both, because PITR is strictly finer-grained.
• Restores take the project offline. Supabase's own guidance: "The project is inaccessible during this process, so plan for downtime beforehand," and the downtime scales with database size. That is your RTO, and you should measure it rather than assume it.
• You can also restore into a brand-new project instead of over the top of the live one. Supabase calls it "Restore to a New Project", it lives in the dashboard under Database → Backups, and with PITR enabled you pick the moment in time. A cloned project cannot itself be used as the source of another clone.
• Deleting a project permanently removes its backups too: "When you delete a project, we permanently remove all associated data, including any backups stored in S3." If a hostile or panicking person deletes the project, PITR does not save you. Keep an independent logical backup somewhere you control.

The hard part: per-tenant restore in a shared schema

Chapter 5's architecture creates a problem here. In shared-schema multi-tenancy, all 300 tenants live in the same tables, separated by a tenant_id column and row-level security. PITR restores the entire database to a moment in time.

But the incident you will actually face is much narrower: one tenant's operations manager ran a bad CSV import at 14:10 and destroyed their own price list, while the other 299 tenants have been trading happily ever since. Rolling the whole database back to 14:09 fixes that one tenant and silently deletes four hours of everyone else's work.

You cannot do it. You need a per-tenant restore, and Postgres has no such feature. There are three practical approaches.

# APPROACH A — Side restore, then surgical copy-back.
# 1. Use "Restore to a New Project" to get a copy of the
#    database as it stood at 14:09:00Z. Production is
#    untouched by this step.
# 2. Copy back ONLY the wounded tenant's rows.

psql "$RESTORED_URL" -c "\copy (
    select * from price_list_items where tenant_id = 'acme'
  ) to '/tmp/acme_prices.csv' with csv header"

psql "$PROD_URL" <<'SQL'
begin;

-- Keep the damaged data. NEVER destroy evidence during an incident.
create table quarantine_price_list_items_20260305 as
  select * from price_list_items where tenant_id = 'acme';

delete from price_list_items where tenant_id = 'acme';
\copy price_list_items from '/tmp/acme_prices.csv' with csv header

-- Prove the restore BEFORE committing.
select count(*) as restored_rows from price_list_items
  where tenant_id = 'acme';
-- The next query must return zero rows.
select * from check_i1_ledger_vs_cache where tenant_id = 'acme';

commit;
SQL

Walking through it. Step 1 restores a full copy of the database as it was at 14:09 into a completely separate project, so production is never touched. Step 2 exports only the affected tenant's rows from that copy using \copy, the bulk export command built into psql (the Postgres command-line shell).

On production we open a transaction and first snapshot the current damaged rows into a timestamped quarantine_ table. During an incident you never destroy evidence, because you may discover an hour later that the "damaged" data also contained legitimate edits. Then we delete that tenant's rows and reload the good ones.

Before committing we count the restored rows and run the I1 invariant view scoped to that tenant, which must return zero rows. Only then do we commit. If anything looks wrong, rollback leaves production untouched.

The catch is referential integrity across the time boundary. Referential integrity is the database's rule that a reference must point at something real: an order line that names price list item PL-88 requires a row PL-88 to exist. If, between 14:09 and now, that tenant created orders referencing price list items, deleting and reloading the price list can leave those orders pointing at rows that no longer exist. So this approach works cleanly for self-contained, leaf-ish tables (price lists, style metadata, customer records) and becomes dangerous for deeply linked transactional data.

Which is why the other two approaches exist, and why they are really design decisions made long before the incident:

• Approach B. Make destructive operations reversible by design. This is the real answer, and the payoff for chapter 1's append-only ledger. If nothing is ever hard-deleted — imports supersede prior rows via a new import_batch_id, price changes are new versioned rows, cancellations are status transitions — then "undo the 14:10 import" is a single update ... set superseded = false where import_batch_id = '...' run in production in four seconds, with no restore at all. Every table where you chose versioning over DELETE is a per-tenant restore you will never have to perform.
• Approach C. Per-tenant logical exports. A nightly job dumps each tenant's data to its own file in object storage, a service that stores files by name, such as Amazon S3 or Supabase Storage (tenants/acme/2026-03-05.jsonl.gz). Restoring one tenant becomes an import rather than a database operation, and it doubles as the data-export feature privacy law will require anyway. It lags by up to 24 hours, so it complements PITR rather than replacing it.

Restore drills as calendar events

A backup strategy you have never executed is a hypothesis. Turn it into knowledge with a restore drill: a scheduled, rehearsed, timed practice restore. Put it in the shared team calendar, quarterly, with a named owner. A ticket in a backlog will slide for a year. A calendar event with a person's name on it happens, the same way a fire drill happens.

#!/usr/bin/env bash
# ops/drills/restore-drill.sh — run quarterly.
# Records real, measured numbers. Needs GNU date (coreutils).
set -euo pipefail

DRILL_DATE=$(date -u +%Y-%m-%d)
TARGET_TIME=$(date -u -d '3 hours ago' +'%Y-%m-%dT%H:%M:%SZ')
LOG=ops/drills/results/${DRILL_DATE}.md
START=$(date +%s)

{ echo "# Restore drill ${DRILL_DATE}"
  echo "- Target recovery point: ${TARGET_TIME}"
  echo "- Operator: ${DRILL_OPERATOR:?set DRILL_OPERATOR}"
} > "$LOG"

# 1. In the Supabase dashboard: Database -> Backups ->
#    "Restore to a New Project", pick $TARGET_TIME, start it.
#    The clone is dashboard-driven; the clock starts here.
#    Paste the new project's ref and connection string when
#    the dashboard shows the project as healthy.
read -r -p "New project ref: "        DRILL_REF
read -r -p "New connection string: "  DRILL_URL
echo "- Time to restored database: \
$(( ($(date +%s) - START) / 60 )) min" >> "$LOG"

# 2. Verify CORRECTNESS. A database that boots can still be
#    missing data, so check the data itself.
#    This is the whole point of the drill.
psql "$DRILL_URL" <<'SQL' | tee -a "$LOG"
select 'tenants' as t, count(*) from tenants
union all select 'orders', count(*) from orders
union all select 'ledger', count(*) from inventory_ledger;

-- every hard invariant must also hold on the restored copy
select 'I1'  as check, count(*) from check_i1_ledger_vs_cache
union all select 'I3',  count(*) from check_i3_negative_on_hand
union all select 'I4a', count(*)
  from check_i4_orphan_invoice_lines
union all select 'I4b', count(*) from check_i4_double_invoiced
union all select 'I5',  count(*)
  from check_i5_cross_tenant_order_lines;

-- how much data did we actually lose? THIS is your measured RPO.
select now() - max(occurred_at) as data_gap
  from inventory_ledger;
SQL

# 3. Point a real app build at it and run a smoke test that
#    creates and ships an order.
DATABASE_URL="$DRILL_URL" \
  pnpm --filter @repo/e2e test:smoke | tee -a "$LOG"

echo "- MEASURED RTO: $(( ($(date +%s) - START) / 60 )) min" \
  >> "$LOG"
echo "- Documented RTO target: 240 min" >> "$LOG"
supabase projects delete "$DRILL_REF" --yes

One note on step 1 before the walkthrough. Supabase drives the clone-to-a-new-project flow from the dashboard, so the script pauses and waits for you rather than pretending a command exists. If you would rather restore a dedicated staging project in place, that part can be scripted through the Management API:

# In-place PITR restore. DESTRUCTIVE: never aim this at
# production. $STAGING_REF is a throwaway project.
API=https://api.supabase.com/v1/projects
curl -X POST \
  "$API/$STAGING_REF/database/backups/restore-pitr" \
  -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d "{\"recovery_time_target_unix\": $(date -u -d '3 hours ago' +%s)}"

That endpoint takes one field, recovery_time_target_unix: the moment you want back, written as a Unix timestamp (the number of seconds since 1 January 1970, which is how computers usually pass times around). $SUPABASE_ACCESS_TOKEN is a personal access token you create in your Supabase account settings.

What it does and why each step earns its place. set -euo pipefail makes the script abort on the first error rather than plowing on pretending, and during a drill you want failures to be loud. The target recovery point is three hours ago, so the drill exercises real WAL replay rather than restoring a convenient fresh snapshot. The operator's name is required, because drills need an accountable human.

What the drill measures

Step 1 starts the clone and records how long it took to get a usable database. Step 2 is the part most teams skip. It goes past "the database started": it counts rows, runs every hard invariant view against the restored copy, and measures the gap between the newest ledger row and now, giving your measured RPO, an actual number rather than a vendor's marketing claim. Reusing the invariant views here pays twice over: the same five hard-invariant queries that guard production also validate every restore.

Step 3 points a real application build at the restored database and runs a smoke test: a short end-to-end check of the few operations that matter most, here creating and shipping an order. A database that answers SELECT but rejects writes, because a role is missing or an ID sequence is broken, has not actually been recovered.

Finally the script writes the measured RTO next to the documented target, so any gap is visible in a file in git, and deletes the throwaway project so the drill does not quietly cost money forever.

Commit each drill's output to ops/drills/results/: after a year you have four data points showing how restore time grows with your database, which is what tells you, before a crisis rather than during one, that your four-hour RTO stopped being achievable somewhere around 400 gigabytes.

Putting it together: one loop at four timescales

Every technique in this chapter is the same idea running at a different speed:

• On your laptop, in seconds: property tests hammer the domain core with random operation sequences and assert the invariants after every step.
• In CI, in minutes: seeded fixtures for partial shipments, revisions, negative adjustments, returns, and cancel-then-uncancel run against a real database, and golden files freeze the exact shape of every invoice.
• In production, nightly: those same invariants, expressed as SQL views, are counted by a scheduled job that pages a human and hands them a runbook.
• Across all of it: structured logs carrying a correlation ID that survives into jobs and webhooks.
• And underneath everything: a job queue in the same Postgres as your data, plus a restore you have personally performed and timed.

The through-line is that each layer states the same small set of truths — ledger equals cache, an allocation never promises stock you do not have, every invoice traces to a shipment, no row crosses a tenant boundary — and checks them against a different reality: imagined data, fixture data, live data, restored data. That is what "the bar is different for money software" actually means. It asks for the same claims, verified continuously, at every timescale, with a human on the other end when they fail.

Field notes & further reading

• fast-check — Arbitraries and core blocks, then Why property-based testing? for the shrinking argument, and the model-based testing guide for fc.commands and fc.modelRun. Defaults such as numRuns: 100 and seed: Date.now() are listed in the Parameters API reference.
• PostgreSQL — Continuous Archiving and Point-in-Time Recovery. The primary source for WAL, archive_command, base backups, and recovery_target_time.
• Supabase — Database Backups. Retention windows per plan, the PITR add-on, WAL-G, the two-minute default WAL interval, and the warning that restores take the project offline. Pair it with Duplicate Project for the restore-to-a-new-project flow the drill script uses.
• pg-boss and Graphile Worker. Read pg-boss's queue-policy table and Graphile Worker's job_key page back to back, then Graphile Worker's exponential backoff table so the retry schedule holds no surprises.
• Modern Treasury — Accounting for Developers. Why immutable append-only ledgers beat mutable balances, and the invariant that total debits must equal total credits.
• TigerBeetle — Safety and their write-up on deterministic simulation testing: seeded simulation, injected faults, and assertions that stay switched on in production.