S The Runbook: Typical Issues and How to Fix Them

This is the chapter you open at 7am when a rep is on the phone and something is wrong. It is organized symptom-first: find the sentence your user actually said, read the ranked causes, run the diagnostic, apply the fix, then close the loop with the prevention. Every procedure here assumes the append-only ledger from Chapter 1, the schema from Chapter 11, and the stack from Chapter 10.

What you need to know first

A runbook is a written procedure for handling a specific failure. The point of writing one is that at 7am, with a customer waiting, you will not think clearly. You will guess. Guessing is how a small problem becomes a big one. A runbook replaces guessing with reading.

Four ideas run through everything below. Learn them once and the rest of the chapter makes sense.

The four ideas behind every procedure

Symptom, cause, and fix are three different things. "The stock number is wrong" is a symptom — a sentence a human said. The cause might be a missing ledger row, a stale cache, a webhook that never arrived, or a warehouse that shipped without scanning. The fix is whatever makes the cause stop. Beginners jump from symptom straight to fix ("I'll just update the number"), which papers over the cause and guarantees a repeat. Always name the cause out loud before you change anything.

Derived numbers can be wrong; source-of-truth facts usually cannot. In the design from Chapter 1, inventory lives in an append-only ledger — a table where you only ever insert rows describing movements (received 12, shipped 3, adjusted −1) and never update or delete them. Everything a user sees (on-hand, available-to-sell, an aging report) is derived by summing those rows.

So a "wrong number" has only two possible sources: the ledger is missing rows or has extra ones, or the derivation is wrong or stale. Your first diagnostic on almost every data complaint is to recompute the number straight from the ledger and compare it to the screen.

If they agree, the ledger is wrong. If they disagree, the code is wrong. That single fork saves an hour every time.

Idempotency is the property that doing something twice has the same effect as doing it once. Chapter 3 covers how to build it. It matters here because most fixes involve replaying something — a webhook, an import, a job, and a replay is only safe if the operation is idempotent. Otherwise the replay creates duplicates and you now have two problems.

Before replaying anything, confirm the target has a uniqueness key (an external ID, an idempotency key, a natural key) that makes the second run a no-op.

Blast radius is how many people or records a change can hurt. A fix that touches one order has a blast radius of one. A fix that runs UPDATE order_lines SET price = ... without a WHERE clause has a blast radius of every order you have ever taken. Before you run anything that writes, say the blast radius out loud, and wrap it in a transaction you can roll back.

Words used in this chapter

You do not need to memorize these. Read them once, then come back when a term appears in a procedure. Every one of them shows up in a real incident sooner or later.

Trade words used in this chapter

These come from the wholesale apparel business rather than from software. A symptom report will use them, so they need to mean something to you.

SQL notation, and writing down what you did

Two notation conventions in the SQL below. A $1, $2 or $3 is a placeholder for a value you supply — that is how your application passes parameters safely, and it is what you should copy into application code. A :'name' is a psql variable you set first with \set name value; that form is for typing at the psql prompt. Never paste user-supplied text straight into a query string in either case.

Finally: write down what you did. Every fix in this chapter should leave a trail — a row in an incident log, a note on the record, a commit message. Six weeks later, when the same symptom shows up, the trail is worth more than your memory.

Questions to ask before you touch anything

The beginner mistake that does the real damage is making the problem worse. It happens because you start typing before you know what is true. Work this list first. It takes four minutes and it has saved more systems than any monitoring tool.

Two rules that go with the list

First, capture evidence before you change state: copy the current row, the current derived number, the request ID, and the log lines into your incident notes. Once you fix it, the evidence is gone and the post-mortem is guesswork.

Second, if you are not sure, stop the flow rather than change the data. Putting a hold on an order, pausing a sync, or turning off a job is nearly always safer than an UPDATE you have not thought through.

Admin roles ignore row-level security

One safety warning applies to every query in this chapter. When you connect with an admin or postgres role, the row-level security rules stored in the database do not apply to you, because admin roles are allowed to ignore them. The policies that keep tenant A from reading tenant B do nothing for your session, so the protection you rely on everywhere else in the system is absent while you work.

That means two things. A query you run without a tenant_id filter will happily return every customer you have, so always write the filter yourself. And a security test run as an admin will pass even when the policies are broken — you must test as a normal application role, which the access section below shows you how to do.

Triage: how bad is this?

Judge severity by what is moving and who is affected, never by how alarmed the reporter sounds. Use this table so that you and your future colleagues classify the same event the same way.

The fast decision list

Read the list below from top to bottom and stop at the first "yes"; the branch you stop on gives you the severity.

Is data being written incorrectly RIGHT NOW?
  |-- yes --> SEV1. STOP THE WRITER FIRST.
  |            (pause job / disable webhook / feature flag off)
  |            Then diagnose. Do not "fix forward" into a
  |            system that is still corrupting.
  |
  no
  |
Can nobody do their core job?
  |-- yes --> everyone affected?  --> SEV1
  |           one team/tenant?     --> SEV2
  no
  |
Is money wrong, or was customer data exposed?
  |-- yes --> SEV1 if exposure or systemic
  |           SEV2 if one account, contained
  no
  |
Is there a workaround the user can use today?
  |-- yes --> SEV3
  |-- no  --> SEV2

Read it as a checklist rather than a diagram. The first branch carries all the weight. When data is actively being written wrong, stop the process doing the writing. Understanding the bug comes second. A paused integration you can restart is a small problem. Ten thousand bad ledger rows is a weekend.

Inventory and availability

Every symptom in this section resolves to the same fork: is the ledger wrong, or is the derivation wrong? The queries below assume the Chapter 11 shape — an append-only stock_ledger with a signed qty_delta, and derived views for on-hand and available-to-sell (ATS), covered in Chapter 8.

"The stock number is wrong"

-- Set your tenant once, so every query below is scoped.
\set tenant '00000000-0000-0000-0000-000000000001'

-- 1. Recompute on-hand straight from the ledger.
SELECT s.sku_code,
       l.warehouse_id,
       SUM(l.qty_delta) AS on_hand
FROM   stock_ledger l
JOIN   skus s ON s.id = l.sku_id
WHERE  s.sku_code = 'AW26-JKT-001-BLK-M'
  AND  l.tenant_id = :'tenant'
GROUP  BY s.sku_code, l.warehouse_id
ORDER  BY l.warehouse_id;

-- 2. Show every movement, newest first, with its source.
SELECT l.occurred_at, l.qty_delta, l.reason,
       l.ref_type, l.ref_id, l.created_by,
       SUM(l.qty_delta) OVER (ORDER BY l.occurred_at, l.id)
         AS running_balance
FROM   stock_ledger l
JOIN   skus s ON s.id = l.sku_id
WHERE  s.sku_code = 'AW26-JKT-001-BLK-M'
  AND  l.tenant_id    = :'tenant'
  AND  l.warehouse_id = 'WH-MAIN'
ORDER  BY l.occurred_at DESC, l.id DESC
LIMIT  50;

-- 3. Look for duplicate rows from the same source event.
SELECT ref_type, ref_id, sku_id, warehouse_id, qty_delta,
       COUNT(*) AS dup_rows,
       MIN(created_at) AS first_seen,
       MAX(created_at) AS last_seen
FROM   stock_ledger
WHERE  tenant_id  = :'tenant'
  AND  created_at > now() - interval '30 days'
GROUP  BY ref_type, ref_id, sku_id, warehouse_id, qty_delta
HAVING COUNT(*) > 1
ORDER  BY MAX(created_at) DESC;

The first line is a psql variable so you type the tenant id once and every query is scoped to one customer. Query 1 gives you truth: the sum of every movement ever recorded for that SKU, per warehouse. Compare it to the number on the user's screen. If they match, the ledger is missing a real-world movement and you move to query 2.

Query 2 is the audit trail — read down the running_balance column until it stops matching what the warehouse says happened, and the row where it diverges is your incident.

Query 3 catches the replay bug: identical ref_type/ref_id pairs producing more than one row for the same SKU and warehouse means an integration or a job wrote the same movement twice. The column is named dup_rows rather than rows because ROWS is a keyword in PostgreSQL and an unquoted alias invites confusing errors.

Repairing the ledger without editing it

Fix. If a movement is genuinely missing, insert it with a reason code that says so — never edit an existing row. If a duplicate exists, insert a reversing row referencing the duplicate; do not delete. Both keep the ledger append-only and auditable.

BEGIN;

-- Reverse a duplicated receipt (blast radius: 1 row).
-- The NOT EXISTS guard makes re-running this a no-op, so a
-- second copy-paste cannot double-reverse the correction.
INSERT INTO stock_ledger
  (tenant_id, sku_id, warehouse_id, qty_delta, reason,
   ref_type, ref_id, occurred_at, created_by, note)
SELECT d.tenant_id, d.sku_id, d.warehouse_id, -d.qty_delta,
       'correction_reversal',
       'stock_ledger', d.id::text, now(), 'runbook:S',
       'Reversal of duplicate from INC-2026-0712'
FROM   stock_ledger d
WHERE  d.id = 918234           -- the exact duplicate row id
  AND  NOT EXISTS (
         SELECT 1 FROM stock_ledger r
         WHERE  r.ref_type = 'stock_ledger'
           AND  r.ref_id   = d.id::text);

-- Confirm the new balance BEFORE committing.
SELECT SUM(qty_delta) AS on_hand_after
FROM   stock_ledger
WHERE  tenant_id    = (SELECT tenant_id FROM stock_ledger
                        WHERE id = 918234)
  AND  sku_id       = (SELECT sku_id FROM stock_ledger
                        WHERE id = 918234)
  AND  warehouse_id = (SELECT warehouse_id FROM stock_ledger
                        WHERE id = 918234);

COMMIT;   -- or ROLLBACK; if the number is not what you expect

This inserts an exact negative of the duplicate row, tagged so a human reading the ledger in a year understands why. The NOT EXISTS clause means running the block twice inserts nothing the second time. Running the balance check inside the same transaction is the important habit: you see the post-fix number before you commit, and if it is not what you expected you type ROLLBACK and nothing happened. Never run a correction outside a transaction, however small it looks on the screen.

Prevention. Put a unique index on the natural key of each movement source so a replay cannot double-post. The key must include everything that legitimately varies within one source document, or you will block correct writes:

CREATE UNIQUE INDEX stock_ledger_source_dedupe
  ON stock_ledger (tenant_id, ref_type, ref_id, ref_line)
  WHERE ref_type IS NOT NULL;

Note the ref_line column. A single source document often produces several ledger rows: a warehouse transfer writes one negative row at the origin and one positive row at the destination, both carrying the same ref_id. An index on (tenant_id, ref_type, ref_id, sku_id) alone would reject the second row and silently break every transfer.

Give each row from a source document its own line number and include it in the key. Chapter 3 explains why a database constraint beats an application-level "check first, then insert" — the check and the insert are two steps, and two requests can slip between them.

"We oversold a style"

Overselling means you accepted orders for more units than you can ship. It is a concurrency problem, a timing problem, or a policy problem, in that order.

-- Which SKUs are allocated beyond what exists?
WITH on_hand AS (
  SELECT sku_id, warehouse_id, SUM(qty_delta) AS qty
  FROM   stock_ledger
  WHERE  tenant_id = :'tenant'
  GROUP  BY sku_id, warehouse_id
),
alloc AS (
  SELECT sku_id, warehouse_id, SUM(qty_allocated) AS qty
  FROM   allocations
  WHERE  status = 'open' AND tenant_id = :'tenant'
  GROUP  BY sku_id, warehouse_id
)
SELECT s.sku_code, o.warehouse_id,
       o.qty AS on_hand, a.qty AS allocated,
       o.qty - a.qty AS shortfall
FROM   alloc a
JOIN   on_hand o USING (sku_id, warehouse_id)
JOIN   skus s ON s.id = a.sku_id
WHERE  a.qty > o.qty
ORDER  BY (o.qty - a.qty) ASC;

-- Which orders competed for the shortfall, in arrival order?
SELECT o.order_number, o.customer_id, ol.qty, o.created_at,
       o.received_at, o.source
FROM   order_lines ol
JOIN   orders o ON o.id = ol.order_id
WHERE  o.tenant_id = :'tenant'
  AND  ol.sku_id = (SELECT id FROM skus
                     WHERE sku_code = 'AW26-JKT-001-BLK-M')
  AND  o.status NOT IN ('cancelled')
ORDER  BY o.received_at;

The first query lists every SKU where open allocations exceed physical stock, with the shortfall as a negative number — that is your oversell list, and it belongs on a schedule rather than being run only when somebody complains.

The second shows who is competing for those units and in what order they actually reached the server. The received_at versus created_at gap is what tells you whether an offline device caused it: a device writes created_at from its own clock the moment the rep taps save, while received_at is stamped by your server when the order finally uploads.

Fix. Decide the allocation policy with the commercial owner, not alone — usually first-in by received_at, with strategic accounts protected. Then de-allocate the losers, notify sales, and offer a substitution or a revised ship date. Record the de-allocation as an allocation event, not by deleting rows.

Prevention. Take a row-level lock on the availability record inside the order-write transaction (SELECT ... FOR UPDATE) so the second writer waits, as in Chapter 3. Separately, make ATS exclude inbound stock by default and require an explicit "sell against inbound" flag per style.

"Available-to-sell shows negative"

A negative ATS is usually correct arithmetic reporting a real problem. ATS is typically on-hand minus open allocations plus (optionally) inbound. If allocations exceed on-hand, the number goes negative and it is telling you the truth.

1. Run the shortfall query above. If it returns the same SKU, ATS is right and you have an oversell, not a display bug.
2. If ATS is negative but on-hand exceeds allocations, the derivation is wrong. Check for double-counted allocations: an allocation row that was never closed when the shipment posted.
3. Check for ledger rows dated in the future. A receipt with occurred_at in next month will be excluded by an as-of-today derivation but included by a naive one.

-- Allocations that should have been closed by a shipment.
SELECT a.id, a.order_line_id, a.qty_allocated, a.status,
       sh.id AS shipment_id, sh.shipped_at
FROM   allocations a
JOIN   order_lines ol ON ol.id = a.order_line_id
JOIN   shipment_lines sl ON sl.order_line_id = ol.id
JOIN   shipments sh ON sh.id = sl.shipment_id
WHERE  a.tenant_id = :'tenant'
  AND  a.status = 'open'
  AND  sh.shipped_at IS NOT NULL
ORDER  BY sh.shipped_at;

-- Ledger rows dated in the future.
SELECT id, sku_id, qty_delta, reason, occurred_at, created_at
FROM   stock_ledger
WHERE  tenant_id = :'tenant'
  AND  occurred_at > now()
ORDER  BY occurred_at;

The first query finds allocations still marked open even though the goods shipped — each one is inflating your allocated total and pushing ATS negative. It uses plain inner joins on purpose: an outer join plus a WHERE sh.shipped_at IS NOT NULL filter throws away the unmatched rows anyway, so the outer join only costs you clarity. The second query finds rows with a future occurred_at, which nearly always means a bad date on an import or a timezone bug. Both are cheap to run and worth putting on a nightly check.

"The warehouse count does not match the system"

This is a cycle count variance. Treat it as a normal business process, not an incident, unless the variance is large or systemic.

Fix. Post an adjustment with a reason code that names the category (cycle_count_variance, shrink, damage, miscount_corrected). Never post a single blended "adjustment" — you lose the ability to measure whether your shrink is getting worse.

-- Freeze window check: did anything move during the count?
-- Timestamps carry an explicit UTC offset so the comparison
-- does not depend on the session's timezone setting.
SELECT l.id, s.sku_code, l.qty_delta, l.reason, l.occurred_at
FROM   stock_ledger l
JOIN   skus s ON s.id = l.sku_id
WHERE  l.tenant_id    = :'tenant'
  AND  l.warehouse_id = 'WH-MAIN'
  AND  l.occurred_at >= timestamptz '2026-07-24 06:00+00'
  AND  l.occurred_at <  timestamptz '2026-07-24 09:30+00'
ORDER  BY l.occurred_at;

-- Sibling-SKU offset check (same style, opposite variance).
SELECT st.style_code, s.sku_code, v.counted_qty, v.system_qty,
       v.counted_qty - v.system_qty AS variance
FROM   cycle_count_lines v
JOIN   skus s   ON s.id = v.sku_id
JOIN   styles st ON st.id = s.style_id
WHERE  v.count_id = 'CC-2026-0724'
  AND  v.counted_qty <> v.system_qty
ORDER  BY st.style_code, s.sku_code;

The freeze-window query lists every movement recorded while the count was running; if the variance equals one of those quantities, what you have is a timing artifact rather than a real discrepancy, and the fix is to re-count with a freeze.

Note the half-open range (>= start, < end) instead of BETWEEN: BETWEEN includes both ends, so a movement at exactly 09:30:00 would be counted by this window and by the next one. The second query groups variances by style so you can spot the classic +6 on black / −6 on navy pattern that means someone counted the wrong bin.

Prevention. Freeze the location during counts (block ledger writes for that warehouse and bin), and count by bin rather than by SKU so the sibling-confusion failure mode becomes visible.

"An item shows in stock but the warehouse cannot find it"

Phantom inventory. Order of probability:

1. it is physically there but in the wrong bin;
2. it shipped and the pick was never confirmed;
3. it was received against the wrong SKU;
4. it is damaged and sitting in a hold area that your derivation still counts as sellable;
5. theft.

1. Pull the ledger for that SKU (query 2 above). Find the last inbound row and its reference — a PO receipt, a return, a transfer.
2. Check whether the same PO line received other SKUs of the same style. If a receipt posted 24 units of the wrong colorway, the physical goods are on the shelf under a different label.
3. Ask the warehouse to search by style and color rather than by SKU, and to check the QC hold and returns areas.
4. If the last movement is a pick that never became a shipment, look for an abandoned pick task.

Fix. If found in the wrong location, post a location transfer. If genuinely gone, post a shrink adjustment and open a variance investigation. Do not silently zero the SKU.

Across all five inventory symptoms, one habit does the heavy lifting: every ledger row needs a reason code from a fixed list short enough to memorize (receipt, shipment, transfer, cycle-count, shrink, damage, sample, correction-reversal). Six months in, "what is our shrink rate by warehouse?" becomes a five-line query instead of an archaeology project. Chapter 8 shows the reports this makes possible.

Orders

"The order is not showing in the warehouse"

-- One query that answers 90% of "where is my order?"
SELECT o.order_number, o.status, o.hold_reason,
       o.warehouse_id, o.ship_start_date, o.cancel_date,
       o.created_at, o.received_at,
       (SELECT COUNT(*) FROM order_lines ol
         WHERE ol.order_id = o.id) AS lines,
       (SELECT COUNT(*) FROM allocations a
         JOIN order_lines ol2 ON ol2.id = a.order_line_id
        WHERE ol2.order_id = o.id AND a.status = 'open')
         AS open_allocations,
       (SELECT COUNT(*) FROM outbound_events e
        WHERE e.ref_type = 'order' AND e.ref_id = o.id
          AND e.state = 'failed') AS failed_pushes
FROM   orders o
WHERE  o.tenant_id    = :'tenant'
  AND  o.order_number = 'SO-2026-04412';

This single row tells you the status, the hold, the dates, whether it is allocated, and whether the push to the warehouse system failed. Run it first for any "where is my order" report.

If failed_pushes is above zero, go to the dead-letter procedure in the Integrations section. If open_allocations is zero while lines is above zero, the order has never had stock reserved against it, so you have an availability problem to solve rather than a delivery one. If ship_start_date is in the future, nothing is broken and the answer is a sentence to the reporter.

Prevention. Put a "stuck orders" panel on the ops dashboard: orders older than 24 hours with no allocation and no hold reason. Silent stalls are the most common ERP complaint and the easiest to monitor for.

"A duplicate order was created"

Causes in order:

• the rep double-tapped submit on a slow connection;
• an offline device uploaded the same draft twice after a reconnect;
• an EDI 850 purchase order was transmitted twice by the retailer's network provider;
• a sales rep re-keyed an order that had already synced;
• a retry in your own job queue that was not idempotent.

-- Find near-identical orders for the same customer.
SELECT a.order_number AS ord_a, b.order_number AS ord_b,
       a.customer_id, a.total_amount, a.source,
       a.created_at, b.created_at,
       b.created_at - a.created_at AS gap
FROM   orders a
JOIN   orders b
  ON   b.tenant_id    = a.tenant_id
 AND   b.customer_id  = a.customer_id
 AND   b.total_amount = a.total_amount
 AND   b.id > a.id
 AND   b.created_at >= a.created_at
 AND   b.created_at <  a.created_at + interval '30 minutes'
WHERE  a.tenant_id  = :'tenant'
  AND  a.created_at > now() - interval '14 days'
ORDER  BY a.created_at DESC;

-- Confirm the line sets are identical before merging.
SELECT order_id,
       md5(string_agg(sku_id::text || ':' || qty, ','
             ORDER BY sku_id)) AS line_fingerprint
FROM   order_lines
WHERE  order_id IN ($1, $2)
GROUP  BY order_id;

The first query finds candidate duplicates by customer, amount, and a short time window. The second computes a fingerprint of each order's lines: it lists every SKU and quantity in a fixed order, joins them into one string, and hashes it.

If the two hashes match, the orders are truly identical and you can safely cancel one. If the hashes differ, they are two real orders that happen to total the same, and canceling would lose business. Always run the second query before acting on the first.

Fix. Cancel the later duplicate with reason duplicate_of and store the surviving order number on it. Release its allocations. If it was already invoiced, credit it rather than deleting.

Prevention. Require a client-generated idempotency key on order creation — a UUID minted on the device when the rep starts the order, sent with every submit attempt, and enforced by a unique index. This is the single highest-value change you can make for offline sales. Chapters 3 and 6 cover it in depth.

"The order shipped to the wrong address"

1. Read the audit trail for the order's ship-to fields: SELECT * FROM audit_log WHERE record_type='order' AND record_id=$1 ORDER BY changed_at;. You need to know whether the address changed after the pick ticket printed.
2. Compare the address on the shipment record with the address on the order. If the shipment snapshot differs, the address was edited mid-flight and the label used the old copy.
3. Check whether the customer has multiple ship-to locations and the wrong one was default-selected.
4. Check the retailer's routing guide requirements — for some accounts the ship-to is dictated by the EDI 850 and any local edit is wrong by definition (Chapter G).

Fix. Contact the carrier for an intercept if the package is still in network; otherwise arrange return and reship, and record the cost against the right cause (your error versus customer-supplied error) so you can measure it.

Prevention. Snapshot the ship-to address onto the shipment record at pick time and make it immutable. Then a later edit to the customer record cannot silently disagree with what was printed. Block ship-to edits once an order reaches picking; require a cancel-and-rebook instead.

"An order was canceled but shipped anyway"

This is a race, and it is worth understanding precisely because the same shape recurs everywhere. The cancel was written after the pick task was created, so the warehouse queue already held a copy of the work.

-- Reconstruct the timeline from the audit log and events.
-- Every branch returns the same five columns, in the same
-- order and of the same types, which UNION ALL requires.
SELECT 'order_status'   AS src,
       changed_at       AS at,
       old_value        AS detail_a,
       new_value        AS detail_b,
       changed_by       AS actor
FROM   audit_log
WHERE  record_type = 'order' AND record_id = $1
  AND  field = 'status'
UNION ALL
SELECT 'event', created_at, event_type, state, actor
FROM   outbound_events
WHERE  ref_type = 'order' AND ref_id = $1
UNION ALL
SELECT 'shipment', shipped_at, id::text, carrier, created_by
FROM   shipments
WHERE  order_id = $1
ORDER  BY at;

This stitches three sources into one chronological list: status changes, integration events, and the shipment itself. The column aliases are set once in the first branch and reused, because UNION ALL matches columns by position, not by name — if the second branch returned a different number of columns, or a text where the first had a timestamp, the query would fail.

Read the result and find the moment the cancel landed relative to the pick and the label purchase. That timestamp comparison is the diagnosis. If the cancel preceded the pick and the pick still happened, your cancel is not propagating. If the cancel landed after the label, the process is working and the answer is a tighter cancel cut-off.

Fix. Treat it as a return: create an RMA, expect the goods back, credit the invoice, and tell the customer before they see the box arrive.

Prevention. Two changes. First, make cancellation a state transition guarded in the database (a check constraint or a trigger that refuses cancelled once picked_at is set), so the UI cannot promise something the warehouse cannot honor. Second, have the warehouse client re-verify order status at pack time as well as at pick time.

"The cancel date passed and nobody noticed"

In wholesale, orders carry a start-ship date and a cancel date; ship after the cancel date and the retailer can refuse the goods or charge you back (Chapters D and G). Nobody noticing is a monitoring failure. The software did exactly what it was told; no one told it to raise its hand.

-- Orders at risk, bucketed. Run daily; email the sales team.
SELECT o.order_number, c.name AS customer, o.cancel_date,
       o.cancel_date - current_date AS days_left,
       o.status,
       SUM(ol.qty * ol.unit_price) AS at_risk_value
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
JOIN   order_lines ol ON ol.order_id = o.id
WHERE  o.tenant_id = :'tenant'
  AND  o.status IN ('open','allocated','picking')
  AND  o.cancel_date <= current_date + 14
GROUP  BY o.order_number, c.name, o.cancel_date, o.status
ORDER  BY o.cancel_date;

This lists every live order whose cancel date is within two weeks, with the dollar value at risk. Negative days_left means already expired.

The comparison adds a plain integer rather than an interval because cancel_date is a calendar date: adding interval '14 days' to a date converts it to a timestamp and drags a timezone into a question that has nothing to do with clocks. Schedule this as a daily job that posts to a Slack channel and emails the account owner; the report is worthless if a human has to remember to run it.

Fix for orders already past cancel: get a written extension from the buyer before shipping, and store the extension on the order. Shipping past cancel without written approval is how you earn a chargeback.

"Prices on the order are wrong"

Fix. Correct the order lines only if the order has not been invoiced. If it has, issue a credit and rebill; never silently change an invoiced price, because the customer's accounts-payable system already has the old number and a silent change guarantees a deduction later (Chapter I).

Prevention. Snapshot price onto the order line at write time, and store the price_list_id and price_list_version alongside it. Every pricing dispute then becomes a lookup rather than an argument.

Integrations

Everything in this section depends on one design decision from Chapter 4: store the raw payload of every inbound and outbound message before you process it. If you did that, most of these problems are a replay. If you did not, stop reading and go build it, because without stored payloads you cannot recover from any of them.

-- The table that makes this chapter possible.
CREATE TABLE integration_events (
  id             bigserial PRIMARY KEY,
  tenant_id      uuid NOT NULL,
  direction      text NOT NULL CHECK (direction IN ('in','out')),
  source         text NOT NULL,          -- 'shopify','edi','qbo'
  topic          text NOT NULL,          -- 'orders/create', '856'
  external_id    text,                   -- vendor's event id
  payload        jsonb NOT NULL,         -- exact body received
  headers        jsonb,
  received_at    timestamptz NOT NULL DEFAULT now(),
  processed_at   timestamptz,
  state          text NOT NULL DEFAULT 'pending'
                 CHECK (state IN ('pending','processed',
                                  'failed','skipped')),
  attempts       int  NOT NULL DEFAULT 0,
  last_error     text
);

CREATE UNIQUE INDEX integration_events_dedupe
  ON integration_events (source, external_id)
  WHERE external_id IS NOT NULL;

CREATE INDEX integration_events_stuck
  ON integration_events (state, received_at)
  WHERE state IN ('pending','failed');

The unique index on (source, external_id) is the deduplication guarantee: if the same vendor event arrives twice, the second insert fails harmlessly and you process it once. The CHECK on state stops a typo in one worker from inventing a fifth state that no query looks for.

The partial index on stuck states makes the "what is not moving?" query instant even when the table has millions of rows — a partial index only stores rows matching its WHERE clause, so it stays small no matter how much processed history accumulates.

Keep the payload as jsonb and never modify it; it is your evidence. The table will also fill up with customer names and addresses, so it belongs inside your normal access controls and retention policy rather than in a debug bucket.

"Shopify inventory does not match"

Shopify models stock as an inventory item (one per variant) with an inventory level at each location. It tracks eight quantity states: incoming, on_hand, available, committed, reserved, damaged, safety_stock, and quality_control.

Shopify's documentation states that "the on_hand state equals the sum of inventory quantities in the following states: available, committed, reserved, damaged, safety_stock, quality_control", so incoming sits outside that total. It also states that "you can't use the Admin API to adjust or move inventory quantities in the committed state. Inventory quantities in the committed state are only affected by the creation and fulfillment of a merchant's orders." (Verified at shopify.dev, 25 July 2026.)

That one definition explains most mismatches. Your ERP's sellable number lines up with Shopify's available rather than its on_hand. The gap between the two is everything Shopify has set aside: units committed to unfulfilled orders, plus anything sitting in reserved, damaged, safety_stock, or quality_control. On most stores the committed figure is the bulk of it, but check the other four before you conclude the sync is broken.

Fix. Run a full reconciliation rather than chasing individual SKUs. Pull every inventory level from Shopify, join to your ledger-derived balances, and write the differences to a reconciliation table you can read as a report.

-- The reconciliation report: read the sign, not just the size.
SELECT r.sku_code, r.location, r.erp_qty, r.shopify_qty,
       r.erp_qty - r.shopify_qty AS delta,
       CASE
         WHEN r.erp_qty - r.shopify_qty > 0
           THEN 'shopify_under (missed push or POS sale)'
         WHEN r.erp_qty - r.shopify_qty < 0
           THEN 'shopify_over (missed order or double push)'
       END AS interpretation
FROM   inventory_reconciliation r
WHERE  r.tenant_id = :'tenant'
  AND  r.run_id = (SELECT MAX(run_id)
                   FROM   inventory_reconciliation
                   WHERE  tenant_id = :'tenant')
  AND  r.erp_qty <> r.shopify_qty
ORDER  BY abs(r.erp_qty - r.shopify_qty) DESC
LIMIT  100;

Sorting by absolute delta puts the worst offenders first, and the interpretation column translates the sign into a hypothesis so a non-engineer can triage the list. A healthy system produces a report with a handful of rows, all small, all explained by in-flight orders. A report with hundreds of rows means the sync is broken, not that individual SKUs drifted.

Prevention. Push absolute quantities with inventorySetQuantities rather than deltas with inventoryAdjustQuantities wherever the API allows it; setting an absolute number is idempotent, adding a delta is not, so a retried set is harmless and a retried delta doubles. Run reconciliation nightly and alert on row count, not on individual rows.

"A webhook was missed"

Shopify's documented delivery behavior, verified at shopify.dev on 25 July 2026:

• "If Shopify receives no response or an error, it retries 8 times over the next 4 hours."
• "Any response outside the 200 range, including 3XX codes, is treated as an error."
• "Shopify has a one-second connection timeout and a five-second timeout for the entire request."
• "After 8 consecutive failures, the subscription is automatically deleted if it was configured using the Admin API", and warning emails go to the app's emergency developer email address.

Three consequences for you:

• Your handler must respond in well under five seconds, which means it should store the payload and return 200 immediately, then do the real work in a background job.
• A silent integration may mean your subscription no longer exists rather than that events stopped.
• And the emergency developer email address must be a mailbox a human actually reads; if it points at a founder's dead alias, the warning arrives nowhere.

1. Confirm the subscription still exists. Query the vendor's webhook subscription list through their API — do not assume.
2. Look for gaps in your stored events by topic and hour.
3. Backfill the gap by polling the vendor's API for the affected window, then replay.

-- Hourly volume by topic; look for hours with zero.
SELECT date_trunc('hour', received_at) AS hour,
       topic, COUNT(*) AS events
FROM   integration_events
WHERE  tenant_id = :'tenant'
  AND  source = 'shopify'
  AND  received_at > now() - interval '3 days'
GROUP  BY 1, 2
ORDER  BY 1 DESC, 2;

Print this and look down the column for hours that are missing entirely or unusually thin. A shop that normally produces 40 orders/create events an hour and produced zero between 02:00 and 06:00 has a gap, and now you know exactly which window to backfill from the vendor's API.

A quiet overnight hour on a domestic-only store is normal; compare against the same hour last week before you call it an incident.

// Replay stored payloads through the same handler that
// processes live traffic. Never write a separate code path.
import { handleShopifyEvent } from "../integrations/shopify";
import { db } from "../db";

const MAX_BATCH = 200;

async function replay(ids: number[]) {
  if (ids.length > MAX_BATCH) {
    throw new Error(`refusing to replay ${ids.length} at once`);
  }
  for (const id of ids) {
    // Claim the row first. The state filter means a row
    // already processed is skipped, so a re-run is safe.
    const ev = await db.oneOrNone(
      `UPDATE integration_events
          SET state = 'pending', attempts = attempts + 1
        WHERE id = $1 AND state IN ('pending','failed')
        RETURNING *`, [id]);
    if (!ev) {
      console.log(`skip  ${id} (already processed)`);
      continue;
    }
    try {
      await handleShopifyEvent(ev.topic, ev.payload, {
        replay: true, eventId: ev.external_id,
      });
      await db.none(
        `UPDATE integration_events
            SET state = 'processed', processed_at = now(),
                last_error = NULL
          WHERE id = $1`, [id]);
      console.log(`ok    ${id} ${ev.topic}`);
    } catch (err) {
      // Store the message only. Full error objects from HTTP
      // clients often embed request headers, and those carry
      // your API token.
      const msg = err instanceof Error ? err.message : "unknown";
      await db.none(
        `UPDATE integration_events
            SET state = 'failed', last_error = $2
          WHERE id = $1`, [id, msg.slice(0, 500)]);
      console.error(`FAIL  ${id} ${ev.topic}: ${msg}`);
    }
  }
}

Replay calls handleShopifyEvent, the same function live webhooks call, and that is the detail to protect above all others. A separate "replay script" drifts from production behavior and eventually produces different results, which is worse than not replaying at all.

Three safety details separate this loop from the obvious version of it:

• The UPDATE ... RETURNING claims the row and filters on state, so an event already processed is skipped instead of applied twice.
• The batch size is capped, so a typo in the ID list cannot replay your whole history.
• And only err.message is stored, because HTTP client error objects routinely include the request headers, and your Authorization header is in there.

The replay: true flag exists only so the handler can skip side effects that should not repeat, such as sending a customer email. Run it with a small list first and read the output before you feed it a hundred IDs.

Verifying the HMAC signature yourself

Signature verification is the other half of this. If your handler is rejecting valid traffic, Shopify counts those rejections toward the eight failures that delete your subscription, so a broken signature check quietly turns into a dead integration. Verify it by hand against a saved request body:

# verify_hmac.py — check one saved webhook body.
# Read the raw bytes from disk and the secret from the
# environment. Both matter: passing the secret as a command
# argument would expose it to anyone who can run `ps`, and
# reading the body as text can change the bytes.
import base64, hashlib, hmac, os, sys

secret = os.environb.get(b"SHOPIFY_API_SECRET")
if not secret:
    sys.exit("SHOPIFY_API_SECRET is not set")

body = open(sys.argv[1], "rb").read()      # raw bytes
header = sys.argv[2].strip()               # X-Shopify-Hmac-SHA256

digest = hmac.new(secret, body, hashlib.sha256).digest()
computed = base64.b64encode(digest).decode()

# compare_digest takes the same time whichever way it fails,
# so an attacker cannot learn the secret one byte at a time.
if hmac.compare_digest(computed, header):
    print("MATCH")
else:
    print("MISMATCH")
    print("computed:", computed)
    print("header:  ", header)

Run it as python3 verify_hmac.py saved-body.json "$HEADER_VALUE". Three things this gets right that a quick shell one-liner does not:

• It reads the body as bytes, so a trailing newline is preserved — shell command substitution strips trailing newlines, which changes the bytes and guarantees a mismatch you will waste an hour on.
• It takes the secret from the environment rather than the command line, so the secret never appears in your shell history or in the process list where any other user on the machine can read it.
• And it compares with compare_digest rather than ==, which is the standard defense against timing attacks.

If this prints MATCH but your server still rejects the request, your framework is parsing the JSON before you compute the HMAC; capture and hash the raw body before any middleware touches it.

Finally, use the X-Shopify-Webhook-Id header as your external_id. Shopify documents it as the value that lets apps "detect and skip duplicates", and the separate X-Shopify-Event-Id header correlates deliveries that came from the same merchant action. Storing the webhook id in the unique index above turns a redelivery into a harmless no-op.

"QuickBooks will not accept the invoice"

QuickBooks Online returns structured faults with a numeric code and a message. Look the number up in Intuit's error-code reference rather than memorizing them, because the list changes. The classes you will actually hit:

"The EDI 856 was rejected"

EDI is the file-based messaging standard retailers use for purchase orders and shipping notices. The 856 is the Advance Ship Notice — the message that tells a retailer what is in each carton before the truck arrives (Chapters G and H). Rejections come back in two different envelopes and beginners confuse them constantly. In the notation below, AK5-01 means "the first field of the AK5 segment", and each field is defined by a numbered entry in the shared X12 dictionary, so "element 717" identifies a list of codes you can look up.

A 997 Functional Acknowledgment reports syntax: did the file parse, were segments in the right order, were elements the right type. The codes live in specific numbered X12 data elements, and knowing which element a code came from is what lets you look it up:

(Segment layout verified at the Stedi X12 997 reference and each code list on its own element page, 25 July 2026. Note that element 725 is named "Data Element Reference Number" in the 005010 release used here; Stedi's newer 8010 pages call it "Data Element Reference Code".) The distinction between elements 715 and 717 matters in practice: a functional group can come back P, partially accepted, which no single transaction set ever does, and a partial acceptance is the one that quietly ships half your cartons.

The 824: business-content rejections

An 824 Application Advice reports business content: the file parsed fine, but the data is wrong — a PO number you do not have, a UPC that is not on the item master, a quantity that exceeds the ordered quantity. Its OTI segment carries a code from X12 element 110, Application Acknowledgment Code.

The codes come in four families, one per level, and the second letter is consistent: A accept, C accept with data content change, E accept with error, H on hold, P partial accept/reject, R reject. So:

• the transaction-set family is TA, TC, TE, TH, TP, TR;
• the item family is IA, IC, IE, IP, IR;
• the functional-group family is GA, GC, GE, GH, GP, GR;
• and the batch family is BA, BC, BE, BP, BR.

(Segment structure verified at the Stedi 824 reference and the code list at the element 110 page, 25 July 2026.)

Decoding a real 997

ISA*00*          *00*          *ZZ*YOURID  ...
GS*FA*YOURID*PARTNERID*20260724*0930*1*X*005010
ST*997*0001
AK1*SH*4021*005010
AK2*856*000000042
AK3*DTM*38**8
AK4*2*373*8*20260732
AK5*R*5
AK9*R*1*1*0
SE*8*0001
GE*1*1
IEA*1*000000001

The ISA line is abbreviated here; a real one is fixed-width, 106 characters before the segment terminator, which is why it is padded with so many spaces. Read the rest bottom-up:

• AK9*R*1*1*0 means the whole functional group was rejected: one transaction set included, one received, zero accepted.
• AK5*R*5 rejects the transaction set, with reason code 5, "One or More Segments in Error".
• AK3*DTM*38**8 points at the DTM (date/time reference) segment at position 38, with segment error 8, "Segment Has Data Element Errors".
• AK4*2*373*8*20260732 narrows it to position 2 within that segment, which is X12 element 373 (Date), with data element error 8, "Invalid Date", and helpfully copies the offending value: 20260732, a date with day 32.

So your ASN carried an impossible ship date. Every 997 rejection you receive decodes this way, and doing it yourself is far faster than emailing the trading partner.

Fix procedure. Correct the source data, regenerate the 856 with a new control number (never resend the same control number), transmit, and watch for the 997. Log the rejection reason against the shipment so you can count rejection causes by trading partner at month end.

"The integration stopped silently"

Silent stoppage is the worst failure mode because nothing is on fire; the numbers just quietly stop being true. The cure is a heartbeat: for every integration, record the last successful message time, and alert when it exceeds the expected interval.

-- Integration heartbeat. Alert on any row where stale = true.
WITH expectations(source, topic, max_gap) AS (
  VALUES ('shopify','orders/create', interval '2 hours'),
         ('shopify','inventory_levels/update', interval '2 hours'),
         ('edi','850',  interval '24 hours'),
         ('edi','810',  interval '24 hours'),
         ('qbo','invoice_sync', interval '2 hours'),
         ('3pl','shipment_confirm', interval '4 hours')
)
SELECT e.source, e.topic,
       MAX(ie.received_at) AS last_seen,
       now() - MAX(ie.received_at) AS age,
       (now() - COALESCE(MAX(ie.received_at),
                         timestamptz 'epoch')) > e.max_gap
         AS stale
FROM   expectations e
LEFT   JOIN integration_events ie
       ON ie.source = e.source AND ie.topic = e.topic
      AND ie.state  = 'processed'
      AND ie.tenant_id = :'tenant'
GROUP  BY e.source, e.topic, e.max_gap
ORDER  BY stale DESC, age DESC;

The expectations list encodes what "normal" looks like for each feed; anything quieter than that is stale. Run it every fifteen minutes from a scheduled job and alert on stale = true.

Two details do real work. The tenant filter sits in the ON clause rather than the WHERE clause, because a filter on the right-hand table of a LEFT JOIN placed in WHERE silently turns it back into an inner join and the never-started feeds vanish. And the COALESCE to the epoch means a feed with zero events ever still appears as stale — a feed that never started is the failure you are most likely to miss.

Common silent causes, in order:

• an expired OAuth token;
• a deleted webhook subscription (see the Shopify auto-delete behavior above);
• a worker process that crashed and was never restarted;
• a dead-letter queue nobody looks at;
• a vendor IP allowlist change;
• an expired certificate on an AS2 connection (AS2 is the encrypted file-transfer protocol most retailers use for EDI, and its certificates expire like any other).

The crashed-worker case is worth a specific check. If your worker runs on a Linux server under systemd, the service file needs Restart=always or a crash is permanent; run systemctl status your-worker to see whether it is running and how many times it has restarted. If it runs as a container, check the orchestrator's restart policy and the container's exit code. Either way, a worker that dies quietly and stays dead is indistinguishable, from the outside, from a vendor who stopped sending.

"We are being rate limited"

Shopify's GraphQL Admin API uses a leaky-bucket model. The bucket has a fixed capacity: every query drops in a number of points based on how much work it asks for, and the bucket drains at a steady rate. Small queries cost little, big ones cost a lot, and you are throttled when the bucket fills.

The documented drain (restore) rates are 100 points per second on Standard, 200 on Advanced, 1,000 on Shopify Plus, and 2,000 on Commerce Components/Enterprise. Exceeding the bucket returns HTTP 429, the standard "too many requests" response, and Shopify states "the recommended backoff time is one second." (Verified at shopify.dev, 25 July 2026; plan tiers and rates change, so re-check before you tune.)

// Respect the cost field the API returns instead of guessing.
type Throttle = { currentlyAvailable: number;
                  maximumAvailable: number;
                  restoreRate: number };

async function callWithBudget<T>(
  fn: () => Promise<{ data: T; extensions?: any }>,
  minHeadroom = 200,
): Promise<T> {
  for (let attempt = 0; attempt < 6; attempt++) {
    try {
      const res = await fn();
      const t: Throttle | undefined =
        res.extensions?.cost?.throttleStatus;
      if (t && t.restoreRate > 0
            && t.currentlyAvailable < minHeadroom) {
        const wait = Math.min(
          (minHeadroom - t.currentlyAvailable) / t.restoreRate,
          30);
        await new Promise(r => setTimeout(r, wait * 1000));
      }
      return res.data;
    } catch (e: any) {
      if (e?.status !== 429) throw e;
      // Exponential backoff with jitter, so a fleet of
      // workers does not all retry on the same tick.
      const base = Math.min(2 ** attempt, 30) * 1000;
      const wait = base / 2 + Math.random() * (base / 2);
      await new Promise(r => setTimeout(r, wait));
    }
  }
  throw new Error("rate limit: gave up after 6 attempts");
}

The function does two jobs. It reads the throttle status the API returns with every response and sleeps before it runs out of budget, which avoids most 429s entirely. When a 429 happens anyway it backs off exponentially, capped at 30 seconds, with random jitter so ten workers do not all wake up at the same instant and hammer the API again.

The restoreRate > 0 guard matters: without it, a vendor response with a zero or missing restore rate divides by zero and the worker sleeps forever. The minHeadroom value reserves budget so an urgent single-order call is not blocked by a bulk sync. Run bulk work on a separate credential or a separate schedule from interactive work whenever the vendor allows it.

Prevention. Batch reads (GraphQL lets you fetch what you need in one query instead of N), use bulk operations for large exports, cache reference data, and move nightly syncs off the hours when your users are working.

Sync and devices

This section assumes the PowerSync setup from Chapter 6. SQLite is a small database that lives inside the app on each iPad, so the rep can keep working with no signal. A sync service copies rows down from your Postgres database into that local copy, and an upload path queues every local write and sends it to your backend when the network returns. Rows are grouped into sync buckets — sets of rows that share a rule about who may receive them, and each device downloads only the buckets it qualifies for.

"The iPad will not sync"

PowerSync's documented behavior explains most of these reports in one sentence: "by default, uploads are processed before downloads, so a backlogged upload queue delays receiving new data." One write the server keeps rejecting stops everything behind it, including the fresh availability numbers the rep is asking about. (Verified at docs.powersync.com, 25 July 2026.)

Reading the device's sync status

// Put this behind a hidden "diagnostics" tap in the app.
// A rep at a show can read it to you over the phone.
export function syncDiagnostics(db: PowerSyncDatabase) {
  const s = db.currentStatus;
  return {
    connected:      s.connected,
    connecting:     s.connecting,
    hasSynced:      s.hasSynced,
    lastSyncedAt:   s.lastSyncedAt?.toISOString() ?? null,
    downloading:    s.downloading,
    uploading:      s.uploading,
    // Show the message only. A raw error object can contain
    // the auth token from the failed request.
    downloadError:  s.downloadError?.message ?? null,
    uploadError:    s.uploadError?.message ?? null,
  };
}

// Pending local writes waiting to reach the server.
// Passing true also computes the byte size, which costs a
// scan of the queue table, so do not call it in a loop.
const stats = await db.getUploadQueueStats(true);
console.log(`pending ops: ${stats.count}, size: ${stats.size}`);

Ship a diagnostics screen in the app from day one. When a rep calls from a trade show, "connected: false, lastSyncedAt: 09:12, pending ops: 47" tells you in one sentence what three minutes of questions would not.

Show the error message rather than the whole error object: the object often carries the failed request, and the failed request carries the user's access token, which you do not want read aloud in a crowded booth or pasted into a support ticket. PowerSync also runs a hosted diagnostics client at diagnostics-app.powersync.com where you can inspect a specific user's buckets and local rows.

Fix ladder, in order, least destructive first:

1. Confirm real internet: open a website. On venue wifi, complete the captive portal login.
2. Switch the iPad to cellular or a personal hotspot. Venue wifi is unreliable by nature; the trade show chapter covers connectivity planning.
3. Force a token refresh: sign out and back in. This fixes expired-credential loops.
4. Read the upload error. If one operation is rejected repeatedly, fix the server-side cause or explicitly discard that operation — never let it block the queue silently.
5. Only as a last resort, clear the local database and re-sync. Do this only after confirming the upload queue is empty, otherwise you destroy unsent orders.

"Orders written at the show are missing"

1. Ask which device and which rep, and get the device's diagnostics output.
2. Check the server for orders from that rep in the show window, including drafts and rejected uploads.
3. Check the upload queue on the device. Pending means not lost — they are still on the iPad.
4. Check your API logs for 4xx responses to that device's uploads. A validation error on the server rejects the write and, depending on your connector, may drop or block it.
5. Check the local SQLite database directly through the diagnostics client if the app UI cannot see them.

-- Server side: what did we actually receive from that rep?
SELECT o.order_number, o.status, o.created_at AS device_time,
       o.received_at AS server_time,
       o.received_at - o.created_at AS lag,
       o.device_id, o.idempotency_key
FROM   orders o
WHERE  o.tenant_id  = :'tenant'
  AND  o.created_by = $1                   -- rep user id
  AND  o.created_at BETWEEN $2 AND $3      -- show window
ORDER  BY o.created_at;

-- Rejected uploads, if you log them (you should).
-- Only the first 200 characters of the body are shown, and
-- the log table itself must never store auth headers.
SELECT received_at, device_id, http_status, error_code,
       payload ->> 'idempotency_key' AS key,
       left(payload::text, 200) AS payload_head
FROM   api_request_log
WHERE  path = '/api/sync/upload'
  AND  http_status >= 400
  AND  received_at BETWEEN $2 AND $3
ORDER  BY received_at;

The first query shows what reached the server and how long each write took to arrive; a large lag is normal for offline work and is not a defect.

The second is the one that finds genuinely lost orders — writes the server refused. If you are not logging rejected sync uploads with their payloads, add it before your next show; without it, "missing orders" is unanswerable.

When you build that log, strip the Authorization header before storing, set a retention window of 30 or 60 days, and keep the table behind the same access controls as customer data, because request bodies contain customer names and prices.

Prevention. Make server-side validation for synced writes permissive: accept the order into a quarantine state and flag it for human review rather than rejecting it outright. A rejected order at a trade show is lost revenue; a quarantined one is a five-minute cleanup on Monday.

"Two reps sold the same units"

This is the offline-first trade-off, and the honest answer is that you cannot fully prevent it — two devices with no connectivity cannot coordinate. What you can do is make it rare and make it visible.

1. Run the oversell query from the Inventory section, filtered to the show window.
2. Order the competing orders by received_at — the server's arrival time, not the device's clock, which may be wrong.
3. Apply your written allocation policy. Have one before the show, agreed with sales leadership.
4. Tell the losing rep before they tell the customer. This is a relationship problem as much as a data problem.

Prevention. Publish a show-specific availability pool that is deliberately smaller than true stock. There is no industry-standard buffer size; pick one, measure your oversell rate over a season, and adjust it. Sync availability more aggressively whenever connectivity exists, and show reps a "last updated" timestamp next to every availability figure so they know how stale it is. Never display an availability number without its age.

"The app shows old prices"

Causes in order:

• the device has not synced since the price list changed;
• the price list is scoped by a sync rule the device does not match (new list, no membership row);
• the app cached prices in memory and never re-read;
• the price change was made in a draft state and never published;
• the customer is on a contract price the rep is not seeing because contract pricing lives in a table that is not synced.

-- Server truth for one customer's effective prices.
SELECT s.sku_code, pl.name AS price_list, pli.unit_price,
       pl.version, pli.effective_from, pli.effective_to
FROM   customers c
JOIN   price_lists pl ON pl.id = c.price_list_id
JOIN   price_list_items pli ON pli.price_list_id = pl.id
JOIN   skus s ON s.id = pli.sku_id
WHERE  c.id = $1
  AND  current_date >= pli.effective_from
  AND  (pli.effective_to IS NULL
        OR current_date <= pli.effective_to)
ORDER  BY s.sku_code;

Run this and read the version column, then compare it against the version the device reports in its diagnostics. If they match, the device is current and the problem is a caching bug in the app; if they differ, it is a sync problem. Exposing price_list_version in both places turns a vague argument into a two-value comparison.

The date test is written out longhand rather than using COALESCE(effective_to, 'infinity') because an explicit IS NULL check reads more clearly to someone who did not write it and does not depend on a magic date value being accepted by the column type.

Prevention. Version every price list, display the version and its sync timestamp in the app, and refuse to write an order against a price-list version older than an agreed number of days without an explicit override that gets logged.

Money

"The invoice total is wrong by a few cents"

Cents-level errors are almost always rounding or floating-point storage. Money must be stored as integer minor units (cents) or as numeric, never as float or double precision. Chapter 2 covers the types; this is the symptom that proves it.

-- 1. Find money columns stored as floating point. Fix these.
SELECT table_name, column_name, data_type
FROM   information_schema.columns
WHERE  table_schema = 'public'
  AND  data_type IN ('real','double precision')
  AND  (column_name ILIKE '%price%'
     OR column_name ILIKE '%amount%'
     OR column_name ILIKE '%total%'
     OR column_name ILIKE '%cost%');

-- 2. Recompute an invoice from its lines and compare.
SELECT i.invoice_number,
       i.total_amount                            AS stored_total,
       SUM(round(il.qty * il.unit_price, 2))     AS line_sum,
       i.tax_amount, i.freight_amount, i.discount_amount,
       i.total_amount
         - (SUM(round(il.qty * il.unit_price, 2))
            + COALESCE(i.tax_amount,0)
            + COALESCE(i.freight_amount,0)
            - COALESCE(i.discount_amount,0))     AS difference
FROM   invoices i
JOIN   invoice_lines il ON il.invoice_id = i.id
WHERE  i.tenant_id = :'tenant'
  AND  i.invoice_number = $1
GROUP  BY i.id;

Query 1 is an audit you should run once and then never need again — any hit is a bug waiting to happen, because binary floating point cannot represent 0.1 exactly and the errors accumulate over thousands of lines.

Query 2 rebuilds the invoice total from its parts; the difference column tells you whether the discrepancy is in the line arithmetic, the tax, the freight, or the discount. It groups by i.id rather than by every selected column, which PostgreSQL allows because i.id is the primary key and every other invoice column depends on it.

The second common cause is rounding at the wrong level. Rounding each line then summing gives a different answer from summing then rounding. Pick one rule, write it down, and apply it identically in the ERP, on the printed invoice, and in the EDI 810 invoice message. A retailer whose accounts-payable system rounds differently from you will short-pay by cents forever, and each shortfall becomes a deduction you have to research (Chapter I).

"A payment was applied to the wrong invoice"

Model payment application as its own append-only table (payment_applications: payment_id, invoice_id, amount, applied_at, applied_by, reverses_id). Then a misapplication is fixed by inserting a reversal and a new application, and the history survives.

BEGIN;

-- Reverse the wrong application.
INSERT INTO payment_applications
  (tenant_id, payment_id, invoice_id, amount, applied_at,
   applied_by, note, reverses_id)
SELECT tenant_id, payment_id, invoice_id, -amount, now(),
       'runbook:S', 'Misapplied — see INC-2026-0725', id
FROM   payment_applications
WHERE  id = $1
  AND  NOT EXISTS (SELECT 1 FROM payment_applications r
                   WHERE  r.reverses_id = $1);

-- Apply it to the correct invoice.
INSERT INTO payment_applications
  (tenant_id, payment_id, invoice_id, amount, applied_at,
   applied_by, note)
SELECT tenant_id, payment_id, $2, amount, now(),
       'runbook:S', 'Reapplied from INC-2026-0725'
FROM   payment_applications
WHERE  id = $1;

-- Verify both invoices before committing.
SELECT i.invoice_number, i.total_amount,
       COALESCE(SUM(pa.amount),0) AS applied,
       i.total_amount - COALESCE(SUM(pa.amount),0)
         AS open_balance
FROM   invoices i
LEFT   JOIN payment_applications pa ON pa.invoice_id = i.id
WHERE  i.id IN ((SELECT invoice_id FROM payment_applications
                  WHERE id = $1), $2)
GROUP  BY i.id;

COMMIT;   -- or ROLLBACK; if either balance looks wrong

Three statements in one transaction: reverse, reapply, verify. The NOT EXISTS guard on the reversal means running the block twice does not reverse the same application twice.

The verification query prints the resulting open balance on both invoices, so you confirm the money landed where it should before you commit. If either balance looks wrong, type ROLLBACK and think again. Then push the same correction to QuickBooks; a fix that only exists in your ERP will be undone by the next sync.

"A deduction cannot be matched"

A deduction (or chargeback) is money a retailer withholds from a payment. Matching it means identifying which invoice and which reason. Chapters G and I cover the business side; here is the mechanical procedure.

-- Unmatched cash: payments received with no application.
SELECT p.id, p.customer_id, c.name, p.amount,
       p.received_at, p.reference,
       p.amount - COALESCE(SUM(pa.amount),0) AS unapplied
FROM   payments p
JOIN   customers c ON c.id = p.customer_id
LEFT   JOIN payment_applications pa ON pa.payment_id = p.id
WHERE  p.tenant_id = :'tenant'
GROUP  BY p.id, c.name
HAVING p.amount - COALESCE(SUM(pa.amount),0) <> 0
ORDER  BY p.received_at;

-- Candidate invoices by amount, for a stubborn deduction.
-- Money is numeric, so a two-cent tolerance is exact here.
SELECT invoice_number, total_amount, invoice_date, po_number
FROM   invoices
WHERE  tenant_id   = :'tenant'
  AND  customer_id = $1
  AND  abs(total_amount - $2::numeric) <= 0.02
  AND  invoice_date BETWEEN $3::date - 120 AND $3::date
ORDER  BY invoice_date DESC;

Treat the first query as your unapplied-cash report and read it every week — every row on it is money whose home you do not know.

The second is the amount-matching fallback with a two-cent tolerance and a 120-day window, which finds most deductions that reference a document number you cannot recognize. The explicit ::numeric cast keeps the comparison in exact decimal arithmetic; leave it out and a parameter that arrives as a float reintroduces the rounding problem you are trying to diagnose.

"The customer says they paid"

1. Ask for the payment method, date, amount, and reference (check number, ACH trace, or remittance advice). Without a reference this is unresolvable.
2. Search payments by amount and date range across the whole tenant, not just that customer — misapplied cash is often sitting on a sibling account.
3. Search unapplied cash (query above). Payments received but not applied are the single most common answer.
4. Check whether the payment went to a different entity — a factor, a different bank account, or an old lockbox address.
5. If nothing is found, ask for proof: a canceled check image or the ACH trace number. Then check the bank statement directly.

Prevention. Import bank transactions daily and auto-match on amount and reference. The gap between "money hit the bank" and "money appears in the ERP" is where these disputes live; shrinking it to a day removes most of them.

Imports

Chapter 7 covers import architecture. This section is the failure catalog.

"The import created duplicate customers"

-- Find likely duplicates by normalised name.
SELECT lower(regexp_replace(name, '[^a-z0-9]', '', 'gi')) AS key,
       array_agg(id ORDER BY created_at) AS ids,
       array_agg(name ORDER BY created_at) AS names,
       COUNT(*) AS n
FROM   customers
WHERE  tenant_id = :'tenant'
GROUP  BY 1
HAVING COUNT(*) > 1
ORDER  BY n DESC;

-- Which import batch created each one?
SELECT c.id, c.name, c.created_at, ib.filename,
       ib.uploaded_by, ib.uploaded_at
FROM   customers c
LEFT   JOIN import_rows ir ON ir.created_record_id = c.id
LEFT   JOIN import_batches ib ON ib.id = ir.batch_id
WHERE  c.id = ANY($1::uuid[]);

The first query normalizes names by stripping everything that is not a letter or digit and lower-casing the result, which catches the whitespace and punctuation variants that defeat exact matching.

The i flag on the regex makes the character class case-insensitive, so capital letters survive the strip and are folded by lower() afterwards. The second query traces each duplicate back to the file and the person who uploaded it — useful for the fix and essential for the prevention conversation.

Merging duplicates without losing history

Fix. Merge, do not delete. Pick a surviving record, repoint child rows (orders, invoices, addresses, contacts) to it, and mark the loser as merged_into = <survivor> with a status of merged so old links still resolve.

-- STEP 1, BEFORE ANYTHING ELSE: ask the database which
-- tables reference customers. Do not rely on memory; a
-- table added last month will not be in your head.
SELECT conrelid::regclass AS child_table, conname
FROM   pg_constraint
WHERE  confrelid = 'customers'::regclass
  AND  contype = 'f'
ORDER  BY 1;

-- STEP 2: name the two records once, as psql variables.
\set survivor '00000000-0000-0000-0000-0000000000aa'
\set dupe     '00000000-0000-0000-0000-0000000000bb'

-- STEP 3: repoint every child table the query above listed.
-- Blast radius: every row of every table named there.
BEGIN;
SET LOCAL statement_timeout = '30s';

UPDATE orders    SET customer_id = :'survivor'
 WHERE customer_id = :'dupe';
UPDATE invoices  SET customer_id = :'survivor'
 WHERE customer_id = :'dupe';
UPDATE payments  SET customer_id = :'survivor'
 WHERE customer_id = :'dupe';
UPDATE addresses SET customer_id = :'survivor'
 WHERE customer_id = :'dupe';
UPDATE contacts  SET customer_id = :'survivor'
 WHERE customer_id = :'dupe';

UPDATE customers
   SET status = 'merged', merged_into = :'survivor',
       merged_at = now(), merged_by = 'runbook:S'
 WHERE id = :'dupe';

-- STEP 4: nothing should still point at the loser.
SELECT 'orders'   AS t, COUNT(*) FROM orders
  WHERE customer_id = :'dupe'
UNION ALL SELECT 'invoices',  COUNT(*) FROM invoices
  WHERE customer_id = :'dupe'
UNION ALL SELECT 'payments',  COUNT(*) FROM payments
  WHERE customer_id = :'dupe'
UNION ALL SELECT 'addresses', COUNT(*) FROM addresses
  WHERE customer_id = :'dupe'
UNION ALL SELECT 'contacts',  COUNT(*) FROM contacts
  WHERE customer_id = :'dupe';

COMMIT;   -- only if every count above is zero

Read this as four steps, in order:

• Step 1 asks PostgreSQL's own catalog which tables have a foreign key pointing at customers; if it lists a table your UPDATE statements do not cover, add it before you go further.
• Step 2 sets the two IDs as psql variables so you type each one once — the earlier habit of writing $survivor inline does not work at the psql prompt, because a bare dollar sign starts dollar-quoting and the statement fails in a confusing way.
• Step 3 does the work inside a transaction with a 30-second statement timeout, so a merge that runs into a lock gives up instead of blocking your order-entry screens.
• Step 4 must return zeros on every line; if it does not, you missed a child table and committing would leave orphaned references. Type ROLLBACK and start again.

Prevention. Give every customer an external key (the account number you and they both use) and enforce UNIQUE (tenant_id, external_key). Make the importer match on that key and refuse rows without one.

"The spreadsheet failed to upload"

1. Ask for the exact error text and the file. Then open the file yourself.
2. Check size and row count against your limits. Serverless platforms cap request body size; large files need a direct-to-storage upload plus a background job.
3. Check the encoding. Files saved from Excel on Windows are often Windows-1252, not UTF-8; a single curly apostrophe or accented character breaks a strict UTF-8 parser.
4. Check for merged cells, multiple header rows, a title row above the headers, and hidden trailing rows — all common in buyer-supplied files.
5. Check the extension against the actual format. A file named .csv that is really .xlsx (or an HTML table saved with a .xls name) fails confusingly.

# What is this file, really? (-I on macOS, -i on Linux)
file -I orders.csv
# orders.csv: text/plain; charset=iso-8859-1

# Convert to UTF-8. Run it WITHOUT -c first. GNU iconv, the
# version on Linux, stops at the first byte it cannot map
# and prints the byte offset, which tells you exactly where
# the file goes wrong.
iconv -f WINDOWS-1252 -t UTF-8 orders.csv > orders.utf8.csv
# iconv: illegal input sequence at position 41822

# Only if that fails and you accept losing those bytes, add
# -c, which drops them silently. Then compare byte counts:
# a smaller file means characters were thrown away.
iconv -c -f WINDOWS-1252 -t UTF-8 orders.csv > orders.utf8.csv
wc -lc orders.csv orders.utf8.csv

# Headers and stray title rows.
head -3 orders.utf8.csv

# Are all rows the same width? Uneven columns break parsers.
# Note this naive split also miscounts rows that contain a
# quoted comma, which is itself worth knowing about.
awk -F',' '{print NF}' orders.utf8.csv | sort | uniq -c

file -I reports the detected character set — if it says anything other than utf-8 or us-ascii, convert before parsing. The macOS version of the flag is capital -I; on Linux it is lower-case -i.

Run iconv without -c first, because the failure message is the useful part: it names the byte position of the first character it could not handle, and that position usually lands you on the exact bad row. Reach for -c only when you have decided the loss is acceptable, and then compare byte counts with wc -lc, because -c discards those bytes without a word.

The two versions of the tool behave differently here: GNU iconv on Linux stops on bad input, while the macOS and BSD version substitutes a replacement character instead, so check what your machine actually did rather than assuming.

The awk line counts fields per row and tallies the results; a healthy CSV prints one line, meaning every row has the same field count. Two or more lines means ragged rows, usually from unescaped commas inside a description field. Because awk -F',' does not understand quoting, a perfectly valid file can look ragged here, so treat the output as a reason to open the file rather than as proof of a problem.

Prevention. Accept messy files. Detect encoding automatically, skip blank leading rows, tolerate extra columns, and show the user a preview of the first ten parsed rows before committing anything. Import errors should be reported per row with the row number, never as one fatal message.

"Dates came in a year off"

Three separate bugs wear this costume.

-- Sanity check any date column after an import.
SELECT min(ship_start_date), max(ship_start_date),
       COUNT(*) FILTER (WHERE ship_start_date < DATE '2000-01-01')
         AS impossible_past,
       COUNT(*) FILTER (WHERE ship_start_date > current_date + 1095)
         AS impossible_future
FROM   orders
WHERE  import_batch_id = $1;

-- Column types: business dates should be `date`.
SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_schema = 'public'
  AND  table_name  = 'orders'
  AND  column_name LIKE '%date%';

The first query is a two-second post-import check that catches every one of the three bugs above: a 1926 date lands in impossible_past, and a timezone shift shows up as a minimum and maximum that are one day off from what the file said.

Three years is expressed as current_date + 1095 rather than an interval so the whole comparison stays in date arithmetic. The second query confirms the storage type, and filters on the schema as well as the table name — without that filter it also matches a table of the same name in another schema and reports types that belong to something else. A ship date is a calendar fact with no time and no timezone; storing it as timestamptz creates the off-by-one-day bug permanently.

"Leading zeros are missing from SKUs"

Excel strips leading zeros from anything it decides is a number. Microsoft's own guidance lists the data types this ruins: "social security numbers, phone numbers, credit card numbers, product codes, account numbers, or postal codes".

It also warns that a custom number format applied afterwards "will not restore leading zeros that were removed prior to formatting. It will only affect numbers that are entered after the format is applied." On long codes it is blunter still: "Excel has a maximum precision of 15 significant digits, which means that for any number containing 16 or more digits, such as a credit card number, any numbers past the 15th digit are rounded down to zero." (Verified at support.microsoft.com, 25 July 2026.)

Excel also converts values that look like dates or scientific notation, so a style code of 07-24 can become a date and 1E5 can become 100000.

The 15-digit limit is the one that bites apparel companies specifically. An SSCC-18 is eighteen digits long, so pasting one into a numeric cell rounds the last three digits down to zero, and no amount of reformatting brings them back. Format the column as Text before the paste, or the number is gone.

Fix for data already imported: if the codes are fixed width, pad them back. Verify against a known-good source before you rewrite anything.

-- Preview first. NEVER run the UPDATE without seeing this.
SELECT count(*) AS will_change
FROM   skus
WHERE  tenant_id = :'tenant'
  AND  length(sku_code) < 10
  AND  sku_code ~ '^[0-9]+$';

SELECT id, sku_code, lpad(sku_code, 10, '0') AS repaired
FROM   skus
WHERE  tenant_id = :'tenant'
  AND  length(sku_code) < 10
  AND  sku_code ~ '^[0-9]+$'
LIMIT  50;

BEGIN;
UPDATE skus
   SET sku_code = lpad(sku_code, 10, '0')
 WHERE tenant_id = :'tenant'
   AND length(sku_code) < 10
   AND sku_code ~ '^[0-9]+$';
-- Compare the reported row count with will_change above.
COMMIT;   -- or ROLLBACK; if the counts differ

Run the count first, then the preview, then the update. The preview shows old and repaired values side by side, and the regular expression ^[0-9]+$ restricts the change to codes that are purely digits from start to finish, so an alphanumeric SKU is never touched.

The tenant filter is doing real work here: without it this UPDATE rewrites SKU codes for every customer on the system, which is the largest blast radius in this chapter. PostgreSQL prints UPDATE n after the statement; if n does not equal will_change, something matched that you did not expect, so type ROLLBACK.

Prevention. Never ask users for CSV when you can avoid it. If you must, provide a template with the code columns pre-formatted as Text, tell users to import through Excel's Get & Transform / Power Query rather than double-clicking the CSV, and validate SKU codes against your item master at upload time so a mangled code is rejected instead of silently creating a new item.

Performance

"A page is slow"

Find the actual slow query. Do not guess, do not add indexes speculatively, and do not rewrite code you have not measured.

-- Enable once. On Supabase: Database > Extensions.
-- On a server you manage, pg_stat_statements must also be
-- listed in shared_preload_libraries in postgresql.conf,
-- which needs a server restart. CREATE EXTENSION alone is
-- not enough there.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Worst queries by total time spent. Start here.
SELECT calls,
       round(total_exec_time::numeric, 0)  AS total_ms,
       round(mean_exec_time::numeric, 1)   AS mean_ms,
       rows,
       left(query, 90) AS query
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20;

-- Worst by single-execution latency (what users feel).
SELECT calls,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       round(total_exec_time::numeric,0) AS total_ms,
       left(query, 90) AS query
FROM   pg_stat_statements
WHERE  calls > 5
ORDER  BY mean_exec_time DESC
LIMIT  20;

-- Reset the window before a targeted measurement.
-- This discards statistics for the whole database, so do
-- not do it while a colleague is mid-investigation.
SELECT pg_stat_statements_reset();

Order by total_exec_time to find where your database spends its life. The winner is often a fast query called ten thousand times — the N+1 problem, where the application fetches a list and then fires one extra query per item in it.

Order by mean_exec_time to find what a user experiences as a hang. Reset the statistics, reproduce the slow page once, then re-read: now the top row is almost certainly your culprit.

Two practical notes. The reset wipes everyone's numbers, so say so before you run it. And on a self-managed server the extension needs to be preloaded at startup, which is why CREATE EXTENSION succeeds but the view stays empty until you edit postgresql.conf and restart. (Column names and the preload requirement verified against the PostgreSQL documentation, 25 July 2026.)

Reading an EXPLAIN plan

-- Read the plan with real timings and real buffer counts.
-- ANALYZE here means "actually run the query", so never do
-- this with an INSERT, UPDATE or DELETE outside a
-- transaction you intend to roll back.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...your query...;

-- Safe pattern for inspecting a write:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET ...;
ROLLBACK;

Three things to look for in the output:

• A Seq Scan — a full read of every row — on a large table inside a loop means a missing index.
• A large gap between rows= (the planner's estimate) and actual rows= means the statistics are stale, so run ANALYZE tablename;.
• High read counts in the BUFFERS line compared with hit counts mean the data is coming from disk rather than memory, which points at either an undersized instance or a query touching far more data than it needs.

Take the warning in the comment literally: EXPLAIN ANALYZE executes the statement, so running it against a DELETE deletes the rows.

"The report times out"

1. Find the query with pg_stat_statements as above.
2. Check whether it is aggregating raw ledger rows over a long window. Sums over millions of rows will not get fast enough by tuning alone.
3. Decide between three structural fixes. A covering index holds every column the query asks for, so PostgreSQL answers from the index and never reads the table. A materialized summary stores the totals once and refreshes them on a schedule. A rolling snapshot table records the balance at the end of each day so the report adds up days instead of individual movements (Chapter 8 covers available-to-sell snapshotting).
4. In the meantime, set a per-statement timeout so a runaway report cannot hold connections hostage.

-- Per-role timeouts. All three settings default to 0, which
-- means "no limit", so nothing is protected until you set
-- them.
-- These take effect on the role's NEXT connection; existing
-- sessions keep their old values.
ALTER ROLE reporting_user SET statement_timeout = '60s';
ALTER ROLE web_app        SET statement_timeout = '15s';
ALTER ROLE web_app        SET idle_in_transaction_session_timeout
                            = '30s';
ALTER ROLE web_app        SET lock_timeout = '5s';

-- Check what a role currently has.
SELECT rolname, rolconfig FROM pg_roles
WHERE  rolname IN ('web_app','reporting_user');

These three settings prevent most self-inflicted outages:

• statement_timeout aborts a query that runs too long.
• idle_in_transaction_session_timeout ends sessions that opened a transaction and wandered off holding locks — the most common cause of a database that suddenly stops accepting writes.
• lock_timeout makes a blocked statement fail fast instead of queueing behind a long transaction.

PostgreSQL documents all three with the same sentence, "A value of zero (the default) disables the timeout", so you get no protection until you set them explicitly. (Verified against the PostgreSQL client configuration documentation, 25 July 2026.) The step people miss is the last one: ALTER ROLE changes what future connections inherit, so a long-lived connection pool must be restarted before the new limits apply. The final query lets you confirm what actually stuck.

"The app is slow only in the morning"

Time-correlated slowness has a small number of causes and they are easy to distinguish.

-- What is running right now, longest first?
SELECT pid, usename, application_name, state,
       now() - query_start AS runtime,
       wait_event_type, wait_event,
       left(query, 80) AS query
FROM   pg_stat_activity
WHERE  state <> 'idle'
  AND  pid <> pg_backend_pid()
ORDER  BY runtime DESC;

-- Vacuum in progress?
SELECT * FROM pg_stat_progress_vacuum;

Run the first query during the slow window, not after. The runtime column names your problem immediately, and wait_event tells you whether the session is working, waiting on a lock, or waiting on disk. If the top rows belong to a batch job, move the job. Batch work should finish before the first user logs in.

Autovacuum, in the second query, is PostgreSQL's background housekeeping; it reclaims space from updated and deleted rows, and it is not something to switch off — if it is hurting you during the day, tune when and how fast it runs rather than disabling it.

Access and login

"A user cannot log in"

Supabase's documented default auth rate limits, verified at supabase.com on 25 July 2026:

• 2 emails per hour on the built-in email service, counted as the sum of requests project-wide and raisable only by configuring your own SMTP provider;
• 30 one-time passwords per hour project-wide;
• a 60-second window per user before another magic link, sign-up confirmation, or password-reset request is allowed;
• verification requests at 360 per hour per IP address with bursts up to 30;
• token refresh at 1,800 per hour per IP with bursts up to 30;
• multi-factor challenge and verify at 15 per hour per IP;
• and anonymous sign-ins at 30 per hour per IP.

Two of these bite in practice. The 2-emails-per-hour default makes password resets look broken during onboarding, and the per-IP limits are counted per address, so an entire office or trade show booth sharing one internet connection is one IP as far as the limit is concerned. Fifteen multi-factor attempts per hour across a whole booth is not many.

-- Is the auth account healthy, and does the app row exist?
SELECT u.id, u.email, u.created_at,
       u.email_confirmed_at, u.last_sign_in_at,
       u.banned_until,
       p.id AS profile_id, p.is_active,
       (SELECT COUNT(*) FROM memberships m
         WHERE m.user_id = u.id) AS tenant_memberships
FROM   auth.users u
LEFT   JOIN profiles p ON p.user_id = u.id
WHERE  u.email ILIKE '%' || $1 || '%';

Read the result left to right:

• A null email_confirmed_at means they never clicked the link.
• A banned_until in the future means someone disabled the account.
• A null profile_id means authentication works but your application never created the corresponding app-level row, which produces a login that appears to succeed and then dumps the user on an error page.
• Zero tenant_memberships means they can sign in but belong to nothing.

Note that this query reads the auth schema, which holds password hashes and tokens in other columns — select the columns you need, never SELECT *, and do not paste the output into a shared channel.

Fix. Confirm the address manually if you can verify identity out of band, resend the invite, or create the missing profile and membership rows. Set up custom SMTP before you onboard real users so the 2-per-hour default stops mattering.

"The second factor is lost or rejected"

Second factors fail in specific ways, and each has a different fix. Know which kind you deployed before the call comes in.

Two rules cover the whole table. Never let one device be the only way into an account, and never reset a factor without verifying identity through a channel you already trust — a phone call to the number on file, or confirmation from the customer's own administrator. A support process that resets multi-factor authentication on the strength of an email is an open door, because the attacker's first step is to send that email.

"A rep can see accounts that are not theirs"

This is a SEV1 in a multi-tenant system, and it is the one symptom in this chapter where your first action is to restrict access rather than to investigate. Chapter 5 covers row-level security (RLS) — rules held in the database that filter which rows each user can see, applied to every query no matter which application asked.

1. Determine the scope: is it cross-tenant (another company's data) or intra-tenant (another rep's accounts in the same company)? Cross-tenant is a breach with notification obligations. Intra-tenant is a permissions bug.
2. Confirm RLS is actually enabled on every table that holds tenant data — the usual cause is a table created by a later migration where nobody added the policy.
3. Reproduce with an impersonated session before changing anything, so you can prove the fix worked.

Five row-level security checks

-- 1. Any table without RLS is a hole. Expect zero rows.
SELECT c.relname AS table_name, c.relrowsecurity AS rls_enabled,
       c.relforcerowsecurity AS rls_forced
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  n.nspname = 'public'
  AND  c.relkind = 'r'
  AND  c.relrowsecurity = false
ORDER  BY c.relname;

-- 2. RLS switched on but no policy written: every read by a
-- normal user returns nothing. Users report "my data
-- disappeared", which looks nothing like a security bug.
SELECT c.relname AS table_name
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
LEFT   JOIN pg_policy p ON p.polrelid = c.oid
WHERE  n.nspname = 'public'
  AND  c.relkind = 'r'
  AND  c.relrowsecurity
GROUP  BY c.relname
HAVING COUNT(p.oid) = 0;

-- 3. Which roles ignore RLS entirely? Your app must not
-- connect as any of these.
SELECT rolname, rolsuper, rolbypassrls
FROM   pg_roles
WHERE  rolsuper OR rolbypassrls;

-- 4. Read the policies on one table.
SELECT polname, polcmd,
       pg_get_expr(polqual, polrelid)      AS using_expr,
       pg_get_expr(polwithcheck, polrelid) AS check_expr
FROM   pg_policy
WHERE  polrelid = 'orders'::regclass;

-- 5. Reproduce as the affected user, inside a transaction.
-- Set the claims BEFORE switching role, and run this from a
-- session that is NOT a superuser: a superuser bypasses every
-- policy, so the test would pass even if RLS were broken.
BEGIN;
SET LOCAL "request.jwt.claims" =
  '{"sub":"USER-UUID-HERE","role":"authenticated"}';
SET LOCAL ROLE authenticated;
SELECT COUNT(*) FROM orders;   -- their scope only
ROLLBACK;

Five checks, in the order you should run them:

• The first lists tables where row-level security is switched off; in a multi-tenant database the correct answer is an empty result, and any row is a potential leak.
• The second catches the opposite mistake — security enabled with no policy behind it, which denies everything and gets reported as missing data rather than as a security problem.
• The third is the check that invalidates all the others if you skip it: a role marked rolsuper or rolbypassrls ignores policies completely, so if your application connects as one, none of your policies are doing anything.
• The fourth prints the policy expressions in readable form.
• The fifth impersonates a user for one transaction so you can count what they can actually see, then rolls back so nothing persists and your own session returns to normal.

The claims are set before the role switch because after switching to a restricted role you may no longer be permitted to set them.

Prevention. Add a test to your CI pipeline, the automated checks that run on every code change before it can be merged, that fails the build if any table in the tenant schema lacks row-level security, using query 1 above. Add integration tests that log in as user A and assert zero visibility of user B's records, running as a normal application role. These two tests catch the failure that a code review will not.

"Someone left the company"

Offboarding is a checklist, and it should be written down before you need it. Run it the same hour the person leaves.

-- What does this user own? Run BEFORE disabling them.
SELECT 'orders_open' AS kind, COUNT(*) FROM orders
  WHERE owner_user_id = $1
    AND status NOT IN ('shipped','cancelled')
UNION ALL SELECT 'customers', COUNT(*) FROM customers
  WHERE account_manager_id = $1
UNION ALL SELECT 'approvals_pending', COUNT(*) FROM approvals
  WHERE approver_user_id = $1 AND decided_at IS NULL;

Run this before you disable the account. Each number is work that will stop moving the moment the user is gone, and reassigning it takes thirty seconds now versus a week of confused questions later. Keep the query in the offboarding checklist itself so nobody has to remember it exists.

"A customer wants their data"

Under the California Consumer Privacy Act as amended (CCPA/CPRA), the California Attorney General states that "businesses must respond to your request within 45 calendar days" and that "they can extend that deadline by another 45 days (90 days total) if they notify you." The same guidance states that if a business asks for personal information to verify identity, "it can only use that information for this verification purpose." (Verified at oag.ca.gov, 25 July 2026. The GDPR timelines in the EU and UK differ; check the jurisdictions you actually operate in and take legal advice — this chapter is not it.)

1. Verify identity through a channel you already trust — an email on file, or a phone number on the account. Do not accept a request from an unverified address.
2. Log the request with a due date. A calendar reminder at day 30 is the difference between compliant and not.
3. Scope it: which records, which tenant, which systems (your ERP, your email provider, your analytics, your support desk).
4. Export in a portable format (JSON or CSV) and deliver through a channel that requires authentication, never as an open link.
5. For deletion requests, work out what you are legally required to retain — invoices and tax records generally must be kept. Delete or anonymize what you can, document what you cannot, and tell the customer which is which.

Deploys and infrastructure

"The site is down"

Work outward from your code to the internet. Each step is 30 seconds.

# 1. Is it down for everyone, or just this person?
curl -sS -o /dev/null \
  -w "%{http_code} %{time_total}s\n" \
  https://erp.example.com/api/health

# 2. What do the logs say? (Vercel CLI)
# --no-branch matters: by default this filters to your
# current git branch, so on a feature branch you see nothing.
vercel logs --environment production --level error \
  --since 30m --no-branch
vercel logs --environment production --status-code 5xx \
  --since 30m --no-branch --limit 20 --json

# 3. Is DNS resolving to what you expect?
dig +short erp.example.com
dig +short erp.example.com CNAME
# Ask the domain's own nameserver, bypassing local caches.
dig @$(dig +short NS example.com | head -1) erp.example.com

# 4. Is the TLS certificate valid and unexpired?
echo | openssl s_client -connect erp.example.com:443 \
  -servername erp.example.com 2>/dev/null \
  | openssl x509 -noout -dates -subject

# 5. Has the domain itself expired?
whois example.com | grep -i 'expir'

# 6. Is the database reachable and accepting connections?
psql "$DATABASE_URL" -c "select now(), version();"

Six commands, six different layers:

• A non-200 from the health endpoint with a fast response time means the app is running and returning an error; a slow or hanging response means it is down or cannot reach the database.
• The vercel logs flags shown are current as of 25 July 2026 (documented here); the --no-branch flag is the one people miss, because the command defaults to filtering by your current git branch and an empty result looks like an outage in the logging system rather than a filter.
• If dig returns nothing or the wrong target, the problem is DNS, and the third dig asks the domain's authoritative nameserver directly, which tells you whether the record is genuinely wrong or your machine is holding a stale cached answer.
• If the certificate's notAfter date has passed, that is the whole outage; certificates normally renew automatically over ACME, and the usual reason renewal stopped is that a DNS record or a redirect changed and the renewal challenge can no longer be answered.
• The whois line catches the embarrassing one: an expired domain registration takes everything down at once and no amount of application debugging will find it.
• If psql hangs, jump to the connections section below.

Also check your vendors' status pages before you debug your own code — a platform incident wastes hours if you do not notice it. Subscribe to status feeds for your host, your database, your payment processor, and your EDI provider, and put the links in this runbook next to the commands above.

"A deploy broke something"

Roll back first, diagnose second. Vercel's Instant Rollback repoints your production domains to a deployment that was previously live. Its documentation records four behaviors that matter during an incident:

• Vercel "won't update environment variables if you change them in the project settings and will roll back to a previous build";
• cron jobs "will be reverted to the state of the rolled back deployment";
• only deployments previously aliased to a production domain are eligible, so most preview deployments are not;
• and "after a rollback, Vercel turns off auto-assignment of production domains", meaning new pushes to your production branch will not go live until you undo the rollback.

(Verified at vercel.com/docs, 25 July 2026.)

# Roll back from the dashboard: Project > Production
# Deployment tile > Instant Rollback > choose deployment
# > Confirm.
#
# IMPORTANT: while rolled back, pushes to the production
# branch do NOT go live. When the fix is ready, promote a
# deployment to restore normal behaviour:
vercel promote <deployment-url-or-id>

# Find what actually changed between the two deployments.
git log --oneline <last-good-sha>..<bad-sha>
git diff --stat <last-good-sha>..<bad-sha>

The git log range between the last known-good commit and the broken one is your suspect list, and it is usually short enough to read. Rolling back buys you the time to read it calmly.

The step teams forget is the promote: a project left in the rolled-back state looks healthy while every subsequent deploy silently fails to reach users, and the next person to push spends an afternoon wondering why their fix has no effect. Put "undo the rollback" on the incident checklist as its own line item. The one thing a code rollback cannot undo is a database migration — see the next section.

"A migration failed halfway"

1. Read the error. Postgres migration failures are usually explicit: a constraint violation, a lock timeout, or a type conflict.
2. Determine whether the migration was transactional. If your tool wraps each migration in a transaction and the failure occurred inside it, nothing was applied and you can fix and re-run. If it created an index CONCURRENTLY, which builds without blocking writes, and therefore cannot run inside a transaction — or was split across statements, part of it landed.
3. Inspect the real schema. Do not trust the migration file.
4. Check the migration ledger table for a row marked applied that should not be.

-- What does the schema actually look like now?
-- (\d+ is a psql command, not SQL. It will not work from
-- application code or a GUI query tab.)
\d+ orders

-- Invalid indexes are the classic half-applied artefact
-- from a failed CREATE INDEX CONCURRENTLY.
SELECT c.relname AS index_name, i.indisvalid
FROM   pg_index i
JOIN   pg_class c ON c.oid = i.indexrelid
WHERE  NOT i.indisvalid;

-- Drop and recreate an invalid index. Neither statement can
-- run inside BEGIN/COMMIT; run them one at a time with
-- autocommit on. Dropping an index a query depends on will
-- make that query slow until the rebuild finishes.
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer;
CREATE INDEX CONCURRENTLY idx_orders_customer
  ON orders (tenant_id, customer_id);

-- What does the migration tool think it applied?
SELECT * FROM schema_migrations ORDER BY version DESC LIMIT 10;

\d+ orders in psql prints the table's real current definition, which is the only source of truth after a partial failure. The invalid-index query finds the specific artifact CREATE INDEX CONCURRENTLY leaves behind when interrupted: an index that exists, cannot be used by queries, and still costs you on every write.

Drop it concurrently and recreate it. Both concurrent statements refuse to run inside an explicit transaction, so if your client wraps everything in BEGIN you will get an error rather than a rebuilt index. Finally, reconcile the migration ledger so the tool's belief matches reality — otherwise your next deploy skips or re-runs the wrong file.

Prevention. Set a short lock_timeout for migration sessions so a migration waiting on a lock fails fast instead of blocking every query in the system. Test every migration against a restored copy of production, not against an empty local database.

"We restored a backup and lost data"

Restoring rolls the database back to a moment in time. Everything written after that moment is gone unless you captured it. This is why the restore procedure has steps before and after the restore itself. Decide your two numbers in advance: the recovery point objective (how many minutes of writes you can afford to lose) and the recovery time objective (how long you can afford to be down). They drive what you pay for.

Provider backups and point-in-time recovery

Supabase's documented backup options as of 25 July 2026:

• the Free plan has no automated backups and expects you to export manually with the CLI;
• Pro keeps "the last 7 days of daily backups",
• Team "the last 14 days",
• and Enterprise "up to 30 days".

Point-in-time recovery is a paid add-on for Pro, Team, and Enterprise projects, and a project using it "must also use at least a Small compute add-on". The published add-on pricing is $0.137 per hour (about $100 per month) for a 7-day recovery window, $0.274 per hour (about $200) for 14 days, and $0.55 per hour (about $400) for 28 days.

Write-ahead log files are backed up "at two-minute intervals", so "in the worst case scenario, PITR achieves a Recovery Point Objective (RPO) of two minutes." (Verified at supabase.com; pricing changes, so check the current pricing page before you budget.)

# Take your own logical backup before any risky operation.
# --no-owner and --no-privileges drop ownership and grant
# statements, so the dump restores cleanly into a database
# where the role names differ.
pg_dump "$DATABASE_URL" \
  --format=custom --no-owner --no-privileges \
  --file="pre-incident-$(date -u +%Y%m%dT%H%M%SZ).dump"

# THIS FILE CONTAINS EVERY CUSTOMER RECORD IN READABLE FORM.
# Keep it on an encrypted disk, never commit it to git, and
# delete it when the incident is closed:
#   rm pre-incident-*.dump
#
# Do not rely on "secure delete" flags. On current macOS the
# rm -P flag "has no effect" per its own man page, and GNU
# shred warns it cannot guarantee erasure on journalling or
# copy-on-write filesystems or on SSDs. Full-disk encryption
# (FileVault, LUKS) is the control that actually works.

# Restore a single table from that dump into a scratch
# database so you can compare rather than overwrite.
pg_restore --data-only --table=orders \
  --dbname="$SCRATCH_DATABASE_URL" \
  pre-incident-20260725T071200Z.dump

# Fix sequences after any restore that rewound them.
# COALESCE handles the empty-table case, which otherwise
# passes NULL to setval and errors.
psql "$DATABASE_URL" -c "
SELECT setval(
  pg_get_serial_sequence('stock_ledger','id'),
  COALESCE((SELECT MAX(id) FROM stock_ledger), 1));"

The pg_dump line is the thirty seconds that saves your weekend; run it before every risky operation, including migrations you are unsure about. Treat the resulting file as the most sensitive object on your laptop — it is your entire customer database in one readable archive, and an incident is exactly when people email such files around.

The comment block about deletion matters because the usual advice is out of date: macOS documents its rm -P flag as having no effect, and the GNU coreutils manual warns that shred "cannot reliably operate on regular files" when the filesystem does not overwrite in place, which covers ext4 in journal mode, Btrfs, XFS, ZFS, NTFS, and any SSD doing wear leveling (GNU coreutils manual, checked 25 July 2026). Keep the dump on an encrypted volume and delete it normally.

Name the dump file explicitly in the pg_restore command rather than using a wildcard, because a wildcard that matches two files fails in a way that reads like a corrupt archive. The selective restore into a scratch database lets you diff a single table against production instead of restoring everything.

The setval call is the step everyone forgets: after a restore, sequences can be behind the highest existing ID, and the next insert fails on a duplicate key.

"The database ran out of connections"

Postgres has a fixed connection limit. Serverless functions scale out and each instance can open its own connection, so a traffic spike opens hundreds of connections against a limit designed for dozens.

Supabase's published limits by compute size, as of 25 July 2026, are 60 direct and 200 pooled connections on Nano and Micro, 90 and 400 on Small, 120 and 600 on Medium, 160 and 800 on Large, and 240 and 1,000 on XL, rising from there. (Verified at supabase.com.) Supabase describes the direct figures as recommended values you can tune for your workload; the pooled client ceilings go with the compute size, so raising those means resizing compute. Check the current table before you plan capacity — these numbers move with the platform.

-- How many connections, and who is holding them?
SELECT usename, application_name, state, COUNT(*)
FROM   pg_stat_activity
GROUP  BY usename, application_name, state
ORDER  BY COUNT(*) DESC;

-- Sessions idle inside a transaction: the usual culprit.
SELECT pid, usename, application_name,
       now() - state_change AS idle_for, left(query, 60)
FROM   pg_stat_activity
WHERE  state = 'idle in transaction'
ORDER  BY state_change;

-- Ask a session to stop, politely. Blast radius: 1 session.
-- The client sees a cancelled query, not a dropped
-- connection, so try this first.
SELECT pg_cancel_backend(12345);

-- Only if cancel does not work: terminate the session.
-- Any open transaction is rolled back and the client's
-- connection dies. Never run this against every row of
-- pg_stat_activity at once.
SELECT pg_terminate_backend(12345);

Group by user and application name and you immediately see whether the connections belong to your web app, a background worker, a reporting tool, or somebody's abandoned psql session, which is why setting application_name in every service is worth the five minutes.

Sessions in idle in transaction are holding locks and connections while doing nothing; they are the most common cause of this outage. Try pg_cancel_backend first: it cancels the running statement and leaves the connection alive, which is the gentler action. Escalate to pg_terminate_backend only when canceling does not clear it, do it one process ID at a time, and re-check between each one.

Fix and prevention. Route serverless traffic through the transaction-mode pooler — Supavisor on port 6543 for Supabase, with direct connections on 5432 reserved for long-lived servers. Transaction mode means a connection is handed to you for the length of one transaction and then given to somebody else, which is what lets a few connections serve many callers.

Supabase documents that "Transaction mode does not support prepared statements", so you must turn that feature off in your client library or every query errors. A prepared statement is a query the client registers on the connection once and then reuses by name; because the next transaction may land on a different connection, the name is not there any more. (Verified at supabase.com, 25 July 2026.)

Cap your application's own pool size, set idle_in_transaction_session_timeout, and never open a database connection at module scope in a serverless function without a pooling story.

Data correctness

"Two customers are duplicated"

Covered mechanically in the Imports section above. The additional case is duplicates created through the UI by two salespeople entering the same new account. Prevention is a fuzzy-match warning at creation time: when a user types a name that closely matches an existing customer, show the match and make them confirm. A soft warning at creation costs seconds; a merge costs an hour.

"A style was deleted that should not have been"

The right answer is that nothing should ever be hard-deleted. Use a deleted_at timestamp and filter it out in views. Then this symptom is a one-line fix.

-- Soft-deleted? Restore it.
SELECT id, style_code, name, deleted_at, deleted_by
FROM   styles
WHERE  tenant_id  = :'tenant'
  AND  style_code = 'AW26-JKT-001';

BEGIN;
UPDATE styles
   SET deleted_at = NULL, deleted_by = NULL
 WHERE tenant_id  = :'tenant'
   AND style_code = 'AW26-JKT-001';
-- Confirm children are still intact.
SELECT COUNT(*) AS sku_count
FROM   skus
WHERE  style_id = (SELECT id FROM styles
                   WHERE  tenant_id  = :'tenant'
                     AND  style_code = 'AW26-JKT-001');
COMMIT;

If deleted_at is set, restoring is trivial and the child SKUs are untouched — check the count anyway, because a cascade you did not know about would show up here as a zero. Every statement carries the tenant filter, since style codes are only unique within a tenant and the subquery would fail or match the wrong row without it. If the row is genuinely gone, you are in restore territory: bring a backup up as a scratch database, extract the missing rows, and re-insert them with their original IDs so existing foreign keys reconnect.

Prevention. Revoke DELETE on master-data tables from your application role entirely: REVOKE DELETE ON styles, skus, customers FROM web_app;. The database then enforces the policy that a code review might miss. Grant it back temporarily, inside a transaction, on the rare occasion a real deletion is agreed.

"Someone edited a shipped order"

1. Read the audit log for that order to establish what changed, when, and by whom.
2. Determine whether the edit propagated: did the invoice change, did an EDI 810 go out, did QuickBooks receive an update?
3. Restore the shipped values on the order from the shipment snapshot, which should be immutable.
4. Notify accounting if anything financial moved.

-- The audit trail for one order.
SELECT changed_at, changed_by, table_name, field,
       old_value, new_value
FROM   audit_log
WHERE  record_type = 'order' AND record_id = $1
ORDER  BY changed_at;

-- Guard: refuse edits to shipped orders at the DB level.
CREATE OR REPLACE FUNCTION block_shipped_order_edit()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.status IN ('shipped','invoiced','closed')
     AND (NEW.customer_id  IS DISTINCT FROM OLD.customer_id
       OR NEW.ship_to_id   IS DISTINCT FROM OLD.ship_to_id
       OR NEW.total_amount IS DISTINCT FROM OLD.total_amount)
  THEN
    RAISE EXCEPTION
      'Order % is % and cannot be edited (use a credit memo)',
      OLD.order_number, OLD.status;
  END IF;
  RETURN NEW;
END $$;

DROP TRIGGER IF EXISTS trg_block_shipped_order_edit ON orders;
CREATE TRIGGER trg_block_shipped_order_edit
  BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION block_shipped_order_edit();

The audit query answers what happened. The trigger stops it happening again by refusing, at the database level, any change to commercially meaningful fields on an order that has already shipped.

IS DISTINCT FROM is used rather than <> because a plain inequality returns null when either side is null, and a null result does not trigger the exception, so a field being set from null to a value would slip through. The DROP TRIGGER IF EXISTS line makes the script safe to run twice, which matters when you are pasting it in at speed.

Before you deploy this, check that no legitimate background job updates shipped orders, or you will convert a data-integrity problem into a failing job at 2am.

Database-level guards beat application checks because they apply to every path in: the web app, a background job, a script you run at 7am, and a future developer who has not read this chapter.

Running an incident

For anything SEV1 or SEV2, use a defined structure. Google's SRE book separates three roles:

• The incident commander "holds the high-level state about the incident" and "structure[s] the incident response task force, assigning responsibilities according to need and priority".
• The operations lead "works with the incident commander to respond to the incident by applying operational tools to the task at hand" — in practice, the only person typing changes into the system.
• The communications lead is "the public face of the incident response task force", whose duties "include issuing periodic updates to the incident response team and stakeholders".

At your size one person may hold all three, but naming them out loud still helps, because it forces you to notice when you are trying to type fixes and answer the phone at the same time.

The living incident document

The same chapter states that "the incident commander's most important responsibility is to keep a living incident document." Start it at minute one. It becomes your post-mortem, your customer communication source, and your evidence.

INC-2026-0725-01  Oversell on AW26 core styles
Severity: SEV2       Started: 2026-07-25 07:12Z
IC: Ben              Ops: Ben              Comms: Dana
Status: MITIGATED

TIMELINE (UTC, append only, never edit past entries)
07:12  Rep reports ATS negative on AW26-JKT-001.
07:19  Confirmed: 14 SKUs allocated beyond on-hand.
07:24  MITIGATION: disabled auto-allocation job (pg-boss
       queue 'allocate' paused). Bleeding stopped.
07:31  Cause hypothesis: allocations not closed on
       shipment for 3PL-confirmed shipments since deploy
       dpl_8f2 (07-24 22:10Z).
07:48  Confirmed by query: 212 open allocations with
       shipped_at set.
08:05  FIX: closed the 212 stale allocations in one txn.
       ATS recomputed; 14 SKUs back to positive.
08:10  Rolled back dpl_8f2 via Instant Rollback.
08:15  Re-enabled 'allocate' queue. Monitoring.
08:40  Promoted dpl_8f1 to clear the rolled-back state so
       production deploys go live again.

IMPACT
- 14 SKUs, 6 customers, 11 orders overcommitted by 42 units.
- Largest single account: 3 orders, 18 units.
- No incorrect shipments. No customer-visible error page.

ACTIONS (owner, due)
- [ ] Regression test for allocation close  (Ben, 07-28)
- [ ] Nightly stale-allocation alert        (Ben, 07-29)
- [ ] Notify the 6 affected accounts        (Dana, 07-25)

Note the shape: timestamps in UTC, append-only entries, mitigation logged separately from fix, impact quantified in units and customers rather than adjectives, and actions with a named owner and a date.

The impact block carries both the total and the worst single account, because the email you send each customer quotes their number and the summary you send internally quotes the total; keeping both in one place stops the two disagreeing. ("pg-boss" in the 07:24 line is a job-queue library that stores its queues in PostgreSQL; the point is that you recorded exactly which queue you paused, so re-enabling it later is unambiguous.)

The 08:40 line is there deliberately — it is the "undo the rollback" step from the deploy section, and writing it into the timeline is how you stop a project sitting in a rolled-back state for a week. Write the document as you go; reconstructing a timeline afterwards produces a worse document and takes longer.

Telling the customer

Tell them early, tell them specifically, and tell them what you are doing. Vagueness reads as evasion. Three templates follow; adapt the wording, keep the structure.

The problem-found message

SUBJECT: Issue affecting your orders — we are on it

Hi [Name],

At 7:12am ET this morning we found a problem in our
system that let three of your orders be confirmed for
more units than we actually have in stock.

Affected: PO 88421, PO 88433, PO 88440
Total units over-committed on your orders: 18

What we have done: we stopped the process that caused
it at 7:24am, and no incorrect shipments went out.

What happens next: [Name] from our sales team will call
you before noon with the exact revised quantities and
ship dates. You do not need to do anything right now.

I am sorry for the disruption. I will send you a short
written summary of the cause and the fix by Monday.

[Your name], [role], [direct phone]

The structure is: what happened, exactly what is affected, what you already did, what happens next and when, an apology, and a named human with a phone number. No jargon, no passive voice hiding who did what, and no promise you cannot keep. Sending it before the customer discovers the problem is worth more than any wording.

The outage message

SUBJECT: [Resolved] Ordering was unavailable 09:15-09:52 ET

Hi [Name],

Our ordering system was unavailable between 9:15am and
9:52am ET today. Orders placed during that window were
not saved and will need to be re-entered.

Cause: a change we deployed last night interacted badly
with a database migration. We reverted it at 9:48am.

Your impact: we can see 2 orders from your team that
started but did not complete. Our team has the details
and will call to confirm them with you.

Prevention: we are adding an automated check that would
have caught this before the change reached production.

[Your name], [role], [direct phone]

The outage template adds a precise window and an honest one-line cause. Give a real cause, at a level a non-engineer can follow; "technical difficulties" damages trust more than an outage does. State the customer's specific impact rather than a general one — you have the data, so use it.

The invoice-correction message

SUBJECT: Incorrect invoice — correction issued

Hi [Name],

Invoice INV-2026-3318 dated 07/18 was billed at the
wrong price. You were charged $4,182.00; the correct
amount is $3,946.50.

We have issued credit memo CM-2026-0114 for $235.50.
No action is needed from you. If your AP team has
already scheduled payment on the original amount, the
credit will apply to your next statement.

Cause: a price list was updated after your order was
written, and the order picked up the newer price
instead of the price we quoted you.

We have changed the system so an order keeps the price
that was quoted at the time it was written.

[Your name], [role], [direct phone]

Money errors need numbers: original, correct, difference, and the document number of the correction. Say explicitly whether the customer must do anything, because their accounts-payable department's first question is whether this changes their payment run. Naming the cause in one sentence prevents the follow-up email.

The blameless post-mortem

Write a post-mortem for every SEV1 and SEV2, and for any SEV3 that surprised you. Google's SRE book lists the standard triggers:

• "user-visible downtime or degradation beyond a certain threshold",
• "data loss of any kind",
• "on-call engineer intervention (release rollback, rerouting of traffic, etc.)",
• "a resolution time above some threshold",
• and "a monitoring failure (which usually implies manual incident discovery)".

It also states the principle: "Blameless postmortems are a tenet of SRE culture" — assume everyone acted reasonably with the information they had, because "You can't 'fix' people, but you can fix systems and processes to better support people making the right choices when designing and maintaining complex systems."

What blameless means in practice

Write "the deploy process allowed a migration to reach production without being tested against production-shaped data" rather than "Ben deployed a bad migration". The first sentence generates an action item. The second generates defensiveness and, next time, a hidden incident.

Two rules keep post-mortems useful. First, every action item gets an owner and a date and lives in the same backlog as feature work, or it will not happen. Second, review the document with someone who was not involved; the SRE book is blunt about the alternative — "An unreviewed postmortem might as well never have existed."

Ask specifically: "how would we have detected this ten minutes earlier?" Detection improvements pay back faster than prevention improvements, because new failure modes keep arriving and finding them sooner always helps.

Symptom to mechanism map

Every symptom in this chapter, mapped to the chapter that explains why the system behaves that way. When a fix here does not work, the mechanism chapter is where you go next.

Field notes & further reading

• PostgreSQL Wiki: Lock Monitoring — the canonical blocking-session query joining pg_locks to pg_stat_activity. Copy it into a saved snippet now; you will want it when the database stops accepting writes.
• PostgreSQL: pg_stat_statements — the official column reference (calls, total_exec_time, mean_exec_time, rows, buffer counters), the shared_preload_libraries requirement, and how to reset it. This is how you find the actual slow query instead of guessing.
• Google SRE Book: Postmortem Culture — the blameless post-mortem standard, the triggers that require one, and the antipatterns. Free online, and the shortest path to a review process people will actually use.
• Google SRE Book: Managing Incidents — the incident commander, operations lead, and communications lead split, the living incident document, and the "stop the bleeding" priority quoted earlier in this chapter. Read it once before your first SEV1, not during it.
• Shopify: Verify webhook deliveries — retry counts, the one-second connection and five-second request timeouts, HMAC verification, and the X-Shopify-Webhook-Id deduplication pattern. Read it before you write your first webhook handler.
• Shopify: API rate limits — the leaky-bucket model, current per-plan point rates, and the recommended backoff. Check it whenever you plan a bulk sync.
• PowerSync: Troubleshooting — the diagnostics client, sync status fields, upload-queue blocking behavior, and the too-many-buckets error PSYNC_S2305 (raised when a user exceeds the default limit of 1,000 sync buckets). The upload-before-download detail explains most "it will not sync" reports.
• Stedi EDI reference: X12 997 — segment-by-segment reference for functional acknowledgments, with linked element pages carrying the code values for elements 715, 717, 718, 720, and 723. Free, accurate, and faster than asking your trading partner.