12 Roadmap, Decisions, and Glossary

You know how every piece works. This chapter gives you the order to build them in, why we chose each piece over its alternatives, what to refuse to build, how to operate the thing once real money runs through it, and what every word in this book means.

The chapters before this one explained the parts. This one puts them in order. Most ERP projects that fail do not fail on theory — the team builds the wrong thing in the wrong order and never reaches a paying customer.

So the roadmap follows one rule: every phase ends in something a real person can use or verify. A phase is finished when you can point at somebody doing a real task — a warehouse worker receives 400 units and the number on screen is right. "Auth is done" does not count.

How to use this chapter

This chapter has six parts, and you will use them at different moments.

• Section 1, the roadmap. Read it once now, then reread the current phase each time you start one. It is the plan.
• Section 2, the decision log. Read it when you want to argue with a choice this book made, or when someone else does.
• Section 3, what not to build. Read it whenever you feel the urge to add a technology. Each entry names the condition that would change the answer.
• Section 4, operating the thing. Read it the week before your first customer goes live, and keep it open during your first incident.
• Section 5, the glossary. Every term this book leans on, in one alphabetical table. When you hit a word you half-know, jump there and come back.
• Section 6, the closing. Two pages on what you now know, what stays hard, and what to do on Monday.

Terms are explained in plain language the first time this chapter uses them, and again in the glossary. Nothing here assumes you remember a definition from chapter 3.

1. The build roadmap

Assume one developer, full time, who has read chapters 1–11, plus one friendly apparel brand who will tell you the truth about your software — worth more than any technical choice here.

Phase 0 — Foundations: repo, database, deploy, one row on a page

Goal. Prove the pipeline works end to end, laptop to production URL to database and back, before any business rule exists.

Build.

• A Turborepo monorepo, one code repository holding several applications plus the packages they share, with apps/web (Next.js App Router) and packages for db, ui, config.
• Separate Supabase projects for production and development. Never share one database.
• Plain SQL migrations, numbered, in git, forward-only. A migration is a small script that changes the database's shape. "Forward-only" means you fix a mistake with a new migration rather than by editing an old one. No ORM schema sync.
• Two tables — tenants and a throwaway ping, and one server-rendered page that prints the row.
• CI on every push. CI (continuous integration) is a robot that runs your checks automatically: types, lint, tests, and migrations against a scratch database. Add a preview deploy per pull request, production on merge, and error tracking proven with one deliberate test error.

Reread. Chapter 10 (stack specifics), chapter C (architecture map).

Gate. Fresh clone to running app in ten minutes, tested by actually deleting node_modules and your local database. Migrations apply to an empty database with zero manual steps. You can roll back production in two minutes.

Time. 3–5 days. The trap: three weeks of tooling. Beginners find monorepo configuration and vanish into generators, presets and Storybook. Phase 0 has one deliverable: a boring page showing a boring row, deployed.

Phase 1 — Tenancy, auth, RLS, catalog

Goal. Two companies log in and each sees only its own products, enforced by the database rather than by your code.

Build.

• tenants, users, memberships (user + tenant + role), permission constants. A tenant is one customer company using your software.
• Supabase Auth for signup, login, reset, invites. Never write your own password hashing or sessions.
• tenant_id uuid not null on every business table, indexed, with RLS enabled in the same migration that creates the table, from one tenant-resolving helper. RLS (Row Level Security) is a rule you store inside Postgres that silently narrows every query to the rows the current user is allowed to see.
• RBAC: a permission helper called by every server action, plus role-restricted policies for price changes, adjustments and voids. RBAC (Role-Based Access Control) means users are given roles, and roles carry permissions.
• The catalog: styles, colorways, size_scales, variants (style × color × size = the SKU, or stock keeping unit), prices (per price list, effective-dated).
• A seed script, a small program that fills a fresh database with fake data, with two tenants using overlapping style names, so leaks show instantly.

Reread. Chapter 5 (RLS, RBAC); chapter 11 for columns; chapter B for the difference between a style and a SKU.

Gate. A test logs in as tenant A, queries every table dynamically, and gets zero tenant-B rows, so a new table without a policy fails CI. Another asserts no table has rowsecurity = false. (rowsecurity is a real column in Postgres's built-in pg_tables view: it is true when row security is switched on for that table.)

Time. 2–3 weeks. The trap: "we'll add RLS later." Later never comes, and two hundred queries then assume a where tenant_id = ? that one endpoint forgot. Second trap: the service-role key bypasses RLS entirely — grep for it in CI.

Phase 2 — Inventory ledger, balance cache, first invariant checker

Goal. Every stock change is an immutable, explained row, and a cached balance that always agrees with the sum of those rows.

Build.

• inventory_ledger: tenant, variant, location, signed quantity_delta, reason, source type and id, actor, occurred-at, idempotency key. An idempotency key is a unique string the caller sends with a request so that a repeat of the same request is recognized and ignored. The table is append-only, enforced by a trigger that raises on update or delete and by revoking those privileges from the app's database role.
• Reason codes: receipt, adjustment, allocation, deallocation, shipment, return, transfer_in, transfer_out, cycle_count, opening_balance. A reversal reuses the reason code of the row it cancels and adds a reverses_ledger_id pointer, so every row still says plainly which bucket it moved.
• inventory_balances: one row per (tenant, variant, location) with on-hand and allocated, maintained by exactly one write path.
• Receipt and adjustment screens (adjustments need a reason and a permission). Reversals negate a row and point at it. Nothing is ever deleted.
• An invariant checker job that recomputes from the ledger per tenant and alerts on mismatch. An invariant is a statement that must always be true — here, "the cache equals the sum of the ledger."
• The ledger tape screen: every row for one variant, newest first, running balance, source link.

Reread. Chapter 1 (ledger design), chapter 2 (constraints, triggers, indexes), chapter 9 (invariants as properties).

Gate. Ledger sum equals cache after thousands of randomly ordered receipts, adjustments and reversals. UPDATE/DELETE fails as the app role. One receipt submitted twice with one key produces one row.

Time. 2–3 weeks. The trap: keeping a mutable quantity column "for convenience." Within a month two numbers disagree and nothing says which is right. Second trap: skipping the tape screen — it answers angry customer email for as long as the product lives.

Phase 3 — Customers, orders, allocation, ATS

Goal. A rep enters an order, the system reserves stock correctly under concurrency, and available-to-sell reflects reality.

Build.

• customers (bill-to, ship-to, terms, currency, credit hold); orders and order_lines (variant, quantity, price, discount, requested ship date, cancel date).
• Order status as an enforced state machine: draft → confirmed → allocated → partially_shipped → shipped → closed, plus canceled. A state machine is a fixed list of allowed states and allowed moves between them.
• Allocation writes ledger rows into the allocated bucket. Leave on-hand alone; decrementing it is what breaks the audit trail. Deallocation on cancel, reduction and deletion is also a new row.
• The concurrency-correct routine: in one transaction, lock the balance row with SELECT … FOR UPDATE (or take an advisory lock — a Postgres lock on a number you choose, here tenant plus variant), read available, check it fits, insert the ledger row, update the cache, commit. Sort variant ids before locking multi-line orders so you never deadlock.
• ATS (available-to-sell) = on-hand − allocated, optionally projected with inbound purchase orders (POs) by date; cached and invalidated on every ledger write.
• An order grid that fills a whole size run in one pass — the screen that decides whether people like your software.

Reread. Chapter 3 (idempotency, concurrency, TOCTOU), chapter 8 (ATS caching), chapter 2 (FOR UPDATE under Read Committed).

Gate. The oversell test: 10 units, 100 concurrent single-unit requests, exactly 10 succeed — run 20 times in CI; flaky means broken. Canceling returns exactly the allocated quantity, even after partial shipment.

Time. 3–4 weeks. The trap: check-then-act without a lock. Code reads "available = 10," decides that is enough, and inserts, while another request does the same. This is TOCTOU (time of check to time of use): the fact was true when you checked it and false when you used it. It is invisible to manual testing and surfaces during your customer's flash sale.

Phase 4 — Fulfillment, shipments, invoices, payments

Goal. An order becomes a physical shipment and then a document someone pays, with the money side reconciling to the goods side.

Build.

• Pick lists grouped for a human walking a warehouse; shipments and shipment_lines with carrier, tracking, cartons, weights; packing-list PDFs.
• Shipping writes ledger rows in one transaction: reduce on-hand, release the matching allocation. Partial shipment leaves the remainder allocated.
• invoices generated from shipments, so an invoice always covers the units that actually left the building — with terms, due date and a gapless number sequence.
• payments plus payment_applications: one payment can cover several invoices and one invoice can be paid in several installments, so model the join table. A paid boolean cannot represent that.
• Credit memos and RMAs, where returns write stock back and offset receivables, plus an AR aging report. An RMA (return merchandise authorization) is the record for goods coming back; AR aging is a report grouping unpaid invoices by how late they are.

Reread. Chapter B (invoices, terms, factoring), chapter 1 (why issued documents are immutable), chapter 11 (money columns).

Gate. Half-shipping releases exactly half the allocation and drops on-hand by the shipped quantity. Invoice numbers are strictly increasing with no gaps under concurrent creation. Money arithmetic ties to hand-computed values to the cent.

Time. 3–4 weeks. The trap: editing issued documents. Someone will ask you to "just change the price on invoice 1042"; allow it and your records diverge from the customer's and from QuickBooks, silently. Void or credit instead.

Phase 5 — The import tool

Goal. A prospect's messy spreadsheet becomes clean data in one sitting, which is what actually closes the sale.

Build.

• CSV and XLSX upload that survives reality: BOM characters (a few invisible bytes Excel writes at the very start of a file), Excel dates, leading-zero SKUs mangled into numbers, merged cells, trailing blanks, curly quotes.
• A column-mapping UI that remembers the mapping per tenant per import type, so the second import takes seconds.
• Import types in priority order: styles and variants, customers, opening balances, open orders, price lists.
• A staging table per batch, a holding area where uploaded rows land before they touch real tables, where validation annotates each row valid, warning or error with a human sentence and the exact cell.
• A dry run with a diff — "12 new styles, 340 updated variants, 4 rows skipped and why" — approved before commit.
• A downloadable error report they fix in Excel and re-upload, plus an import batch id on every row so a bad import reverses wholesale.

Reread. Chapter 7 (staging tables, dry-run diffs, error surfaces), chapter 3 (re-uploading must not duplicate).

Gate. A real customer's real file imports unmodified, or produces an error report a non-technical person can act on. The same file twice makes no duplicates. One bad row does not half-commit.

Time. 3–4 weeks. The trap: building it last. Data migration is one of the most common reasons ERP deals stall — a prospect looks at retyping 4,000 SKUs and stays on their spreadsheet. Second trap: an importer that thinks its job is to be right.

Phase 6 — First integration (Shopify), then QuickBooks

Goal. Orders flow in and financials flow out, with reconciliation proving nothing was lost.

Build.

• OAuth install, encrypted per-tenant credentials, a connection health record with last-successful-sync timestamps. OAuth is the standard "log in over there, grant this app access" handshake, so you never hold the customer's password.
• Webhook receivers with HMAC verification. A webhook is an HTTP call the other system makes to you when something happens; an HMAC signature is a short code computed from the message and a shared secret, which proves the message came from them and was not altered. Shopify sends its signature in the X-Shopify-Hmac-Sha256 header. The handler does two things only: persist the raw payload into integration_events keyed by the provider's event id, and return 200 fast. Real work happens in a job.
• An explicit mapping table (their variant ↔ yours, their customer ↔ yours) with handling for unmatched SKUs.
• Outbound inventory sync via the outbox: a row written in the same transaction as the ledger row, drained by a worker with retries and backoff. The outbox pattern exists so "we changed stock" and "tell Shopify" either both happen or neither does.
• Reconciliation: a scheduled job pulling the last N days from their API and diffing against yours, with gaps landing in an operator queue.
• Then QuickBooks: push invoices, credit memos and payments; reconcile on document number and open AR.

Reread. Chapter 4 (integrations, reconciliation); chapter 3 (outbox, idempotency, retries); chapter 9 (scheduling, dead letters).

Gate. Replay one webhook 100 times: one order, one set of ledger rows. Kill the worker mid-sync, restart, end correct. Delete a webhook before processing and confirm reconciliation finds it.

Time. 4–6 weeks for Shopify, 3–4 more for QuickBooks; EDI (electronic data interchange) is a separate project. The trap: trusting webhooks. Shopify's own documentation warns that delivery is not guaranteed and tells you to ignore duplicates using the X-Shopify-Webhook-Id header, so a system that only listens is quietly wrong: webhooks for latency, polling reconciliation for truth.

Phase 7 — Reporting, PDFs, exports

Goal. The owner answers their weekly questions without asking you.

Build.

• The core set: open orders by customer and delivery month, on-hand and ATS by style, sell-through by style, shipped and invoiced by period, AR aging, bookings versus shipped.
• CSV export on every report, because finance opens everything in Excel anyway.
• Server-rendered PDFs — invoice, packing list, pick list, linesheet, order confirmation — templated, tenant-branded, deterministic.
• Pre-aggregated tables or materialized views for anything scanning the full ledger. A materialized view is a query whose result Postgres stores on disk so reads are fast. No 30-second queries during business hours.
• Date boundaries in the tenant's timezone (stored UTC, timezone shown), so nobody argues about a Monday order landing in last week.

Reread. Chapter 8 (reporting, PDFs, caching), chapter 2 (query plans, indexes, materialized views).

Gate. Every report ties out to the ledger — shipped units in the period report equal shipment ledger rows in that window. Exports respect RLS, including inside materialized views, where cross-tenant leaks hide.

Time. 2–3 weeks; timebox it. The trap: building a general report builder. Six similar reports tempt you into a drag-and-drop query engine — a six-month product with its own security model, when the customer wanted five specific reports.

Phase 8 — The offline trade-show app

Goal. A rep writes orders on an iPad in a convention center with no usable network, and everything merges correctly afterward.

Build.

• PowerSync between Postgres and on-device SQLite, with sync rules scoped to that user's tenant, their customers and the active season — never the whole database. SQLite is a small database that lives in a file on the device itself; sync rules decide which rows each device is allowed to hold.
• An offline order flow: pick a customer, browse a cached linesheet, fill a size run, save. Client-generated UUIDv7 ids give rows stable keys before the server sees them.
• A written conflict policy per table. Order headers: last-write-wins. Order lines: append. Inventory: not writable on device.
• Visible sync status (pending count, last sync, a screenshot-able errors view) and image caching with a size limit.
• Server-side revalidation on reconnect: offline orders checked against real ATS, with anything that would oversell going to a "3 lines need attention" screen rather than being silently trimmed.

Reread. Chapter 6 (sync rules, conflicts, CRDTs — data structures that merge concurrent edits without a referee), chapter 3 (every offline write needs a client-supplied key).

Gate. Airplane mode: 20 orders written over an hour arrive exactly once. Two devices editing one order converge to a state you can explain in one sentence. The device's SQLite file holds zero other-tenant rows.

Time. 3–4 weeks. The trap: allocating inventory offline. Two reps in two cities cannot both reserve the last 12 units without a shared authority — offline writes orders, the server allocates. Second trap: syncing everything.

Phase 9 — Hardening: backups, on-call, error inbox, habits

Goal. The system survives your mistakes, your provider's outages, and your first security questionnaire.

Build.

• A rehearsed restore. "Backups are enabled" is a checkbox. Getting your data back is a skill, and you only have it if you have practiced. Pick a timestamp, restore via PITR (point-in-time recovery — rewinding the database to a chosen moment) to a new instance, point staging at it, verify a known row, record the elapsed time. Quarterly, with the date written down.
• A stated RPO and RTO, plus evidence you meet them. RPO is how much data you can afford to lose; RTO is how long you can afford to be down.
• The error inbox: every unhandled exception, failed job, dead-lettered event and red invariant check in one place, triaged to zero daily, with alerts routed somewhere that wakes you. A dead-lettered event is one that failed so many times the system parked it for a human.
• An audit log of who changed what, when. Build SOC 2 habits before the audit, SOC 2 is the security report enterprise buyers ask for, which means least privilege, MFA (a second login factor such as a code from a phone), access reviews, reviewed pull requests, rotated secrets, and an incident response document.
• A staging environment with production schema and scrubbed data, so migrations are rehearsed.

Reread. Chapter 9 (PITR, invariants, queue operations), chapter 5 (roles, least privilege).

Gate. A restore drill completed and timed, with the runbook written from what you actually did on the day. Invariant checkers catch a deliberate corruption inside the interval. Alerts have reached your phone.

Time. 2–3 weeks, then permanent low-level effort. The trap: believing "backups are on" means you have backups. Untested backups fail exactly when needed — retention shorter than the mistake you are trying to undo, a restore that takes far longer than you promised anyone, or an encryption key that lived in the database you just lost.

Phase summary

Add the phases up and the fast end is about 28 weeks, the slow end about 39 — roughly six and a half to nine months for one developer working full time, and that assumes nothing goes badly wrong. Six weeks describes a demo. Eighteen months usually means you built something from section 3.

2. The decision log

Every architecture is a set of bets. Writing them down stops you relitigating settled questions at 2 a.m., and tells future-you when a bet should be re-examined.

D1 · Append-only ledger over a mutable quantity

• Alternatives: a mutable quantity column; full event sourcing.
• Why: a mutable number cannot answer "why is this 47?" A ledger can, forever, and append-only writers simplify concurrency.
• Revisit when: a tenant's ledger grows into the tens or hundreds of millions of rows — the exact number depends on your hardware, so measure rather than guess. Then add checkpoint rows and partitioning, still never mutating history.

D2 · Postgres only — no Redis, no Kafka

• Alternatives: Redis for cache and locks; Kafka or SQS for events.
• Why: one datastore is one backup and one consistency model — locks from FOR UPDATE, caching from tables, queueing from pg-boss.
• Revisit when: cache reads dominate database CPU, or job volume grows past what one Postgres instance comfortably serves alongside your application traffic.

D3 · Shared schema with tenant_id + RLS

• Alternatives: schema-per-tenant; database-per-tenant.
• Why: one migration run, one pool, one set of indexes; schema-per-tenant multiplies migration risk by customer count.
• Revisit when: a contract demands physical isolation, or one tenant degrades everyone. Because the code is already tenant-scoped, that becomes a data move rather than a rewrite.

D4 · Server actions over tRPC or REST

• Alternatives: tRPC; hand-rolled REST; GraphQL.
• Why: one client means end-to-end types with no schema duplication and no fetching library. Next.js is explicit that a server action is reachable by a direct POST request whether or not your UI calls it, so authenticate, authorize and validate inside every one.
• Revisit when: a second consumer appears — mobile app, partner, customer script.

D5 · NUMERIC for money over integer cents

• Alternatives: integer minor units; floating point (never).
• Why: cost rolls, discounts and per-unit freight carry fractional cents, and NUMERIC(14,4) stores them exactly.
• Revisit when: realistically never. Round only at issuance, and store the rounded value on the document.

D6 · UUIDv7 primary keys over bigserial

• Alternatives: bigserial; random UUIDv4.
• Why: offline clients mint ids before the server sees them, which rules out database sequences, and UUIDv7 is time-ordered, so index locality stays near-sequential. UUIDv7 is standardized in RFC 9562 (May 2024), and PostgreSQL 18, the current release as of July 2026, ships a built-in uuidv7() function; on older servers, generate the id in application code.
• Revisit when: you drop offline entirely. Order and invoice numbers stay separate gapless sequences regardless.

D7 · A maintained balance cache over on-read aggregation

• Alternatives: sum the ledger per read; a materialized view.
• Why: availability is read thousands of times per write, and core Postgres has no incremental refresh — REFRESH MATERIALIZED VIEW discards the old contents and re-runs the whole query, and CONCURRENTLY only avoids blocking readers (it still recomputes, and it requires a unique index).
• Revisit when: the checker keeps finding drift. Drift means a second write path exists, so delete the path and keep the cache.

D8 · pg-boss over an external queue

• Alternatives: SQS, Inngest, Temporal, BullMQ.
• Why: pg-boss stores jobs in Postgres and can create them inside an existing database transaction, which is exactly what the outbox needs — no dual write, no "the job fired but the transaction rolled back." It leans on Postgres's SKIP LOCKED for locking, and ships retries with exponential backoff plus cron scheduling.
• Revisit when: you need durable multi-day workflows, or job volume saturates the database.

D9 · Supabase over raw Postgres on a VM

• Alternatives: RDS or self-managed plus your own auth; Neon.
• Why: auth, storage, a connection pooler and managed backups on day one, and real Postgres, so RLS, triggers and raw SQL behave as documented. Budget for the backup tier you actually need: as of July 2026, Supabase includes daily backups on Pro (7 days), Team (14 days) and Enterprise (up to 30 days), while point-in-time recovery is a paid add-on that requires at least the Pro plan and a Small compute add-on, starting around $100 per month for 7 days of retention.
• Revisit when: cost exceeds managed Postgres plus your own ops time, or you need an extension the platform blocks; exit is pg_dump plus rewiring auth.

D10 · Turborepo monorepo over polyrepo

• Alternatives: separate repos with published packages; one un-workspaced app.
• Why: schema, types and validation are shared by the web app, workers and offline app; in a polyrepo a schema change becomes a publish-and-bump dance.
• Revisit when: separate teams have separate release cadences — for a small ERP company, never.

D11 · PowerSync over custom offline sync

• Alternatives: hand-rolled change tokens; Electric (formerly ElectricSQL, which has since repositioned around general HTTP sync and agents); Zero; no offline at all. Replicache, long the obvious comparison here, is in maintenance mode, and its authors point users at Zero.
• Why: correct bidirectional sync is genuinely hard — ordering, partial failure, clock skew, stale-client schema evolution, and PowerSync is built for exactly the Postgres-to-device-SQLite case.
• Revisit when: your need shrinks to "cache a read-only linesheet," in which case delete the sync engine.

D12 · Transactional outbox over inline API calls

• Alternatives: call Shopify inside the request; fire-and-forget after commit; CDC (change data capture, where a tool tails the database's own change log).
• Why: an inline call ties your transaction to a third party's uptime and creates the dual-write problem: commit succeeds, call fails, nothing records the intent.
• Revisit when: enough integrations exist that a general CDC pipeline pays for itself.

D13 · Idempotency keys at every write boundary

• Alternatives: content-based dedupe downstream; hoping retries do not happen.
• Why: retries are guaranteed, and a unique constraint on (tenant_id, idempotency_key) turns a distributed-systems problem into a database constraint.
• Revisit when: never; extend it, so every new write endpoint gets a key before it gets a UI.

D14 · Authorization in the database as well as in app code

• Alternatives: middleware checks alone; an external policy engine.
• Why: defense in depth — app checks are the fast path with good error messages, RLS is the backstop when someone forgets a where clause, and a separate policy engine drifts away from the schema it guards.
• Revisit when: permissions become field-level; then layer app policy on top of RLS.

D15 · Plain SQL migrations over ORM-generated schema

• Alternatives: Prisma migrate; auto-sync; a migration DSL.
• Why: the schema uses partial indexes, check and exclusion constraints, triggers and RLS policies, which generators handle badly — you hand-edit the output anyway.
• Revisit when: your tool fully supports those features and writing SQL is a measured bottleneck.

D16 · Reconciliation alongside every integration

• Alternatives: trust webhooks; manual spot checks.
• Why: reconciliation turns silent divergence into a visible queue item, and it is the only mechanism that can catch events you never received.
• Revisit when: never remove it; widen the interval once an integration has been clean for months.

D17 · Property-based tests for invariants

• Alternatives: fixed example tests; manual QA.
• Why: your bugs live in orderings you did not imagine, cancel after partial ship after a return, and property tests generate thousands of orderings, then shrink a failure to its minimal case.
• Revisit when: a test goes flaky, which is almost always real nondeterminism in your code.

D18 · PITR with rehearsed restores over nightly dumps

• Alternatives: pg_dump nightly; a replica as "the backup."
• Why: most real disasters are logical — a bad migration, a bad script, a wrong where clause, and a replica copies that mistake in milliseconds, while PITR lands you on the second before it.
• Revisit when: never for the primary strategy; add logical dumps as an independent second copy.

D19 · Void and reverse instead of deleting or editing

• Alternatives: hard delete; soft delete with free editing.
• Why: financial and inventory documents are evidence, and voiding with a reversing entry matches how accountants already work.
• Revisit when: a document is still a draft — drafts may be edited freely. The boundary is issuance: once a document has been sent to a customer, it is history.

D20 · Single currency and location now, modeled but not abstracted

• Alternatives: build multi-currency and multi-warehouse up front; hardcode with no columns.
• Why: carrying currency and location_id from day one makes the second one a feature rather than a migration nightmare, but FX tables (foreign-exchange rates) and stock transfers can wait.
• Revisit when: a signed customer sells in a second currency or holds stock in a second location.

3. What NOT to build

Each is a legitimate technology that is wrong for you, now. The trigger is the condition that would change the answer.

Distributed architecture: microservices, GraphQL, Kubernetes

Microservices. Splitting inventory, orders and billing turns single transactions into distributed sagas — multi-step workflows where each step needs a compensating action to undo it if a later step fails. Time when: distinct teams block each other shipping, or one component must scale independently. A solo developer, or a handful of engineers in one room, has neither problem.

GraphQL for your own frontend. It solves many clients with divergent needs; you have one client and control both ends. What you get instead is resolvers, N+1 query problems (one query for a list, then one more per item), and depth limiting to stop a malicious query nesting forever. Time when: third parties build against your API and each needs a different shape.

Kubernetes. Platform hosting plus a small worker host runs this product; Kubernetes swaps that for cluster upgrades, ingress configuration (the routing layer that gets traffic into the cluster) and permanent on-call. Time when: you have a full-time infrastructure engineer and a reason platform hosting cannot satisfy.

Configurability: plugin systems and rules engines

A plugin system. Founders imagine customers extending the product; customers ask for a field and a report. Time when: you have declined the same customization from five paying customers and can name the extension points. Until then, webhooks plus a good CSV export cover nearly every request of this kind.

A rules engine. "Let customers configure allocation priority" produces a programming language with no debugger, tests or types, which support must reason about on a Friday evening. Time when: the same rule shape is hardcoded for four customers and only the numbers differ. Then expose those numbers as settings and leave the logic in code.

Solved elsewhere: authentication and real-time updates

Custom authentication. Password hashing, session rotation, reset-token expiry, timing-safe comparison, MFA, enumeration defenses — all solved, all catastrophic to get wrong. Time when: never. If an enterprise buyer needs SAML SSO (single sign-on through their corporate identity provider), buy that too.

Real-time everything. Live-binding every screen costs a websocket layer (a permanently open connection between browser and server), subscription lifecycle bugs and mysterious load. Inventory matters in real time; a customer list does not. Time when: users demonstrably collide on the same record, and even then, start with a "this changed, refresh" indicator.

Scope no customer has paid for yet: currencies, EDI, mobile

Premature multi-currency and multi-warehouse. Real multi-currency means FX sourcing, settlement-date rates, period-close revaluation and realized gains; real multi-warehouse means in-transit transfers and per-location costing. Time when: a paying customer needs it. Keep the columns (D20); skip the machinery.

Your own EDI translator. EDI is how large retailers exchange orders and invoices, and the North American standard, X12, is decades of accumulated variation in which every trading partner differs. Writing a parser takes about a week; passing each partner's certification takes months. Time when: three customers need the same partner and vendor fees exceed your engineering cost.

A mobile app for everything. The offline app for trade shows has a real reason to exist — no network. Everything else is a responsive web app. Time when: you need a capability the web cannot reach: sustained barcode scanning, background sync, offline authority.

4. Operating the thing

The morning check (ten minutes, every business day)

1. Error inbox — triage to zero: fix, file, or dismiss with a note. An inbox you stop reading is worse than none.
2. Invariant checkers — every tenant green? One red is a drop-everything event: a number a customer trusts is wrong right now.
3. Job queue — is pg-boss draining? Anything retried more than three times or dead-lettered overnight?
4. Integration health — last successful sync per connection. No sync in 24 hours is broken even with no errors: expired tokens fail silently.
5. Reconciliation gaps — orders upstream and missing here. Resolve same day; gaps compound.
6. Database — open connections against the pool limit, slowest queries, disk growth, and autovacuum lag. Autovacuum is the background job that reclaims space from old row versions; when it falls behind, tables bloat and queries slow down.
7. Backups — PITR window intact and covering your stated RPO.

Build one internal page showing all seven; if it takes longer than ten minutes, the page is not good enough.

Alerts worth having

Treat the thresholds below as starting points for a small system, then tune them against your own traffic. The urgency column matters more than the number.

Two rules. An alert that is always ignored must be retuned or deleted — fatigue is how real incidents get missed. And every alert names the runbook step that resolves it.

Diagnostic: "the number is wrong"

A customer says ATS on a style shows 340 and should be 412. Do not guess, and do not "fix" the cache.

Step 0 — pin the question down. Which exact variant? Which location? What number do they expect, and from where — a count, a report, another system? Half these calls end here, because the customer is comparing on-hand with ATS, and those two numbers are supposed to differ by whatever is allocated.

-- Step 1: identify the variant precisely.
select v.id, v.sku, s.name as style, v.color, v.size
from variants v
join styles s on s.id = v.style_id
where v.tenant_id = :tenant
  and (v.sku = :sku or s.name ilike '%' || :style_name || '%')
order by s.name, v.color, v.size;

That query takes whatever the customer gave you — a SKU code, or a fragment of a style name, and lists the matching variants with their id, color and size. ilike is a case-insensitive text match, and the % characters mean "anything can appear here," so "ridge" finds "Ridgeline Hoodie".

You are looking for exactly one row. If several come back, go back to the customer before writing another query, because diagnosing the wrong variant wastes an afternoon.

-- Step 2: does the cached balance agree with the ledger?
-- This is the fork in the road.
select
  b.on_hand                as cached_on_hand,
  b.allocated              as cached_allocated,
  b.on_hand - b.allocated  as cached_available,
  coalesce(sum(l.quantity_delta) filter (
      where l.reason not in ('allocation','deallocation')
    ), 0) as ledger_on_hand,
  coalesce(sum(l.quantity_delta) filter (
      where l.reason in ('allocation','deallocation')
    ), 0) as ledger_allocated
from inventory_balances b
left join inventory_ledger l
       on l.tenant_id   = b.tenant_id
      and l.variant_id  = b.variant_id
      and l.location_id = b.location_id
where b.tenant_id  = :tenant
  and b.variant_id = :variant
group by b.on_hand, b.allocated;

This puts the two versions of the truth side by side. The first three columns read the cached balance row. The last two add up the ledger from scratch: every row whose reason is neither an allocation nor a deallocation moves physical stock, so it belongs to on-hand, and the two allocation reasons move the reserved bucket.

filter (where …) tells Postgres which rows feed each sum, and coalesce(…, 0) turns "no ledger rows at all" into a zero instead of an empty value. This split is why reversal rows reuse the reason code of the row they cancel: a generic reversal reason would land in the wrong bucket and make this query lie.

The fork: a cache bug, or a ledger to read

If the two sides disagree, you have a cache bug: some code path wrote inventory outside the single write path. Find it, fix it, then rebuild the cache from the ledger. Never patch the number by hand — you will be back next week.

If they agree (the usual case), the ledger is telling the truth, and the question becomes which row is wrong or missing. Read the tape.

-- Step 3: the tape. Every movement, newest first, with two
-- running balances: one for on-hand, one for allocated.
select
  l.occurred_at, l.reason, l.quantity_delta,
  sum(l.quantity_delta) filter (
    where l.reason not in ('allocation','deallocation')
  ) over (
    order by l.occurred_at, l.id
    rows between unbounded preceding and current row
  ) as running_on_hand,
  sum(l.quantity_delta) filter (
    where l.reason in ('allocation','deallocation')
  ) over (
    order by l.occurred_at, l.id
    rows between unbounded preceding and current row
  ) as running_allocated,
  l.source_type, l.source_id, l.actor_id,
  l.idempotency_key, l.note
from inventory_ledger l
where l.tenant_id   = :tenant
  and l.variant_id  = :variant
  and l.location_id = :location
order by l.occurred_at desc, l.id desc
limit 200;

The tape is the story of this variant, one movement per line. The two over (…) expressions are window functions: for each row they add up every earlier row, oldest first, so you can watch the balance climb and fall.

rows between unbounded preceding and current row is the phrase that means "everything up to and including this line." The results are then shown newest first, which is how a human reads a bank statement. Keeping on-hand and allocated in separate columns stops the two buckets blurring into one meaningless total.

The five shapes a discrepancy takes

Compare the tape to the customer's story. The gap between them will be one of these.

(a) A movement is missing — a receipt nobody entered, or an event that never arrived from an integration:

-- (a) Did an event arrive and fail, or never arrive at all?
select id, provider, event_type, external_id, status,
       attempts, last_error, received_at, processed_at
from integration_events
where tenant_id = :tenant
  and received_at >= :window_start
  and (status <> 'processed' or last_error is not null)
order by received_at desc;

This lists every inbound event in the time window that is not cleanly processed, newest first, with the attempt count and the last error message. Rows here mean the event reached you and something went wrong afterwards, which you can usually replay. An empty result is the more worrying answer: the event never arrived, and only reconciliation against the provider's API will prove it.

(b) A movement appears twice — a missing or non-unique idempotency key:

-- (b) Did one source document write more than one ledger row?
select source_type, source_id,
       count(*)             as row_count,
       sum(quantity_delta)  as total
from inventory_ledger
where tenant_id = :tenant
  and variant_id = :variant
group by source_type, source_id
having count(*) > 1
order by row_count desc;

Each source document — one receipt, one shipment line — should produce exactly one ledger row per variant. This groups the ledger by source and keeps only the groups with more than one row, so anything listed is a double-write. The total column tells you how much stock the duplicate invented. Fix the cause first (a missing unique constraint on the idempotency key), then reverse the extra row.

(c) One row's quantity is wrong — follow source_type/source_id to the source document and compare against the paperwork. Correct it with a reversal row plus a fresh, correct row. Do not edit the original.

(d) They compared the wrong two numbers — on-hand against ATS, one location against all locations, or two different timezone boundaries.

(e) Allocation is stale — units still reserved for canceled or closed orders:

-- (e) Are units still allocated to orders that are finished?
select l.source_id as order_line_id,
       sum(l.quantity_delta) as net_allocated,
       ol.quantity_ordered,
       o.status
from inventory_ledger l
left join order_lines ol on ol.id = l.source_id
left join orders o       on o.id = ol.order_id
where l.tenant_id  = :tenant
  and l.variant_id = :variant
  and l.reason in ('allocation','deallocation')
group by l.source_id, ol.quantity_ordered, o.status
having sum(l.quantity_delta) <> 0
   and (o.status is null or o.status in ('cancelled','closed'));

For each order line, this adds every allocation and deallocation together. A net of zero means the reservation was released properly. Anything other than zero on an order that is canceled or closed — or on an order line that no longer exists, which is the is null case — is stock being held for nothing.

That is your missing 72 units. Release it with deallocation rows, then go and find the code path that skipped the release.

Closing the loop with the customer

Step 4 — close the loop. Send the customer the tape with a plain-English paragraph: what happened, when, who did it, what you changed. Then ask yourself one question. Could an invariant check have caught this? If yes, add it today.

When you ship a bad deploy

1. Roll back first, diagnose second. Never debug in production while customers are broken. This is why Phase 0 rehearsed the rollback.
2. Check the migration. Code reverts in seconds; schema does not. Expand-only migrations — add the new column, keep the old one working, remove it in a later release — are what make a code rollback safe.
3. Assess data damage. Query rows created in the bad window. Because inventory is a ledger, corrections are reversal rows, so no restore is needed.
4. Only then consider PITR. Restoring loses every legitimate write since the restore point: right for a destructive mistake, wrong for values you can correct forward. When unsure, restore to a separate instance and merge the good rows back.
5. Write the postmortem the same day — blameless and factual: timeline, cause, why the gates missed it, the one check you are adding.

Communicating during an incident

• Tell them before they tell you. If monitoring found it, say so quickly — within about fifteen minutes. Customers forgive outages. What they hold against you is finding out about one before you told them.
• Describe impact in their language. Say "order entry is failing for all users." Do not say "the allocation service returns 500s."
• Promise a time for the next update rather than a time for the fix, and send that update even when it says "still investigating." Silence is what damages trust.
• Do not speculate about cause while you are still wrong. Guessing publicly twice is worse than "we do not know yet."
• Be specific about data — "no data lost; 14 orders between 10:05 and 10:40 need re-entry, list attached."
• Follow up in writing within 48 hours with cause, fix and prevention. That document decides your renewal.

5. The complete glossary

Every term this book uses, defined once and in plain language. Look up whatever you have half-forgotten, then go straight back to the work.

6. Closing

Here is what you now know that most working developers do not. Why an inventory number should never be a mutable column, and how to prove your cache is right rather than hope. What happens when two people sell the same unit in the same millisecond, and three ways to stop it.

That webhooks lie by omission. That tenant isolation belongs in the database, because application code forgets. That a backup you have not restored is a guess.

And what a cancel date is, why a chargeback happens, and why the size run matters — the part most engineers building ERPs never learn, and the part that makes software people want to use.

The four problems that stay hard

These four do not get easier with practice:

• Distributed truth: once your data lives in two places you have a consistency problem with no perfect answer, and the patterns in this book make divergence quick to detect. None of them prevent it.
• A changing business: your customer will start dropshipping, add a warehouse, launch in Europe, and schemas that were right become subtly wrong; keep the model small enough that changing it takes a week.
• Saying no: some requests are the product, most are one company's idiosyncrasy.
• Pressure: a customer on the phone, wrong numbers on screen, and the discipline to follow the diagnostic instead of guessing.

Where to start on Monday

On Monday morning: create the repository, do Phase 0 exactly as written, and stop when the boring page shows the boring row. Then find one apparel brand willing to talk for an hour a week and tell you when your software is annoying. Build Phases 1 and 2 with them watching.

Show them the ledger tape; watch their face when they realize they can finally see why a number is what it is. You will get things wrong — the ledger means you can find out how, and fix it forward. That is the whole point.

Field notes & further reading

• Supabase — Row Level Security. Policy syntax for Phase 1, plus the two performance rules from the warning above: index the columns your policies filter on, and wrap policy function calls in a select. The page publishes before-and-after benchmarks for both.
• PostgreSQL — Transaction Isolation. Read the Read Committed section before writing allocation; it explains exactly how SELECT … FOR UPDATE behaves when another transaction gets there first, and therefore why check-then-act needs a lock.
• PostgreSQL — Continuous Archiving and PITR. What a restore actually does, step by step, and why an untested one is a guess.
• PostgreSQL — UUID functions. The built-in uuidv7() and gen_random_uuid(), and the note that v7 is time-ordered. Relevant to D6 and Phase 8.
• Supabase — Database backups. Which plans include daily backups, what the PITR add-on costs and requires, and the recovery point objective it targets. Read this before you promise a customer an RPO.
• pg-boss. The queue from chapter 9 — see SKIP LOCKED, retries with exponential backoff, cron scheduling, and creating jobs inside an existing transaction, which is the mechanic behind your outbox drainer.
• PowerSync documentation. Read the architecture overview and sync rules before writing any Phase 8 code.
• Stedi — X12 EDI 850 Purchase Order. A free, segment-by-segment view of a real EDI document you are choosing not to build. The sibling pages for 810, 855, 856 and 997 are worth ten minutes each.