Skip to content
next-expo-supabase-agent-harness

ADR: query shapes as a generated artifact, and plans as a scale-lane probe

Status: accepted · Date: 2026-02-05 · Supersedes: the "no EXPLAIN plan probe" position recorded in docs/harness/README.md

Context

Section titled “Context”

The tenancy design rests on one performance claim, stated in three places and verified in none: that org_id = ANY((SELECT private.member_org_ids())::uuid[]) combined with the list screen's keyset order is an ordered index scan, not a filter over every row the tenant owns followed by a Sort.

Everything the repo had was compatible with that claim being false. schema-rls proves the index exists. The pgTAP structural suite proves its leading column is the tenant key. tenancy proves the predicate is one of the reviewed uncorrelated forms. All three are true of an index that serves the filter and leaves the sort to be done in memory, and all three are true of a database whose planner has decided to ignore the index entirely.

The previously-recorded decision was that there is deliberately no EXPLAIN probe, because "a plan is a planner opinion at one statistics snapshot" and gating on it made a suite flap. That reasoning is correct about where such a probe was being run — against a seeded test database of a few rows, where the planner correctly reads one page rather than using an index. It is not an argument against the probe; it is an argument about cardinality.

Decision

Section titled “Decision”

1. The query manifest is GENERATED BY EXECUTION, never written down

Section titled “1. The query manifest is GENERATED BY EXECUTION, never written down”

tools/gen-query-shapes.mjs drives each DAL function through a harness-owned recording port and records the builder chain it produced. contracts regen-diffs the result.

A hand-authored manifest is a tautology. The same turn writes the DAL and the manifest, and when a check goes red the cheapest repair is to edit the manifest until it agrees with the code. The only manifest worth gating on is one that no edit survives.

The recorder is a Proxy, not a fake with methods on it. A fake implements what it knows about, so the first thing an unreviewed builder call does is crash — and the obvious repair is to add the method to the fake, which silently teaches the instrument to ignore it. The Proxy records every method by name, which is what lets .range()/.offset() red by name rather than being certified absent by an instrument that could not have seen them.

Two closures keep it non-vacuous, both enforced by the generator rather than by review: a probe module re-exports its DAL as a namespace and generation fails if any exported function has no probe, and generation fails if a probe issues no query at all.

2. Static where it is decidable, live where it is not

Section titled “2. Static where it is decidable, live where it is not”

query-shapes (in the chain, ~60ms) answers a question that is decidable from migration text: given the columns a statement filters on and the columns it orders by, either some index carries them as a prefix in a single scan direction or none does. That is a one-line authoring-time mistake and belongs in the turn that makes it.

db-perf (the path-filtered db-scale CI lane) answers the question that needs a real planner, real statistics and two million rows: did the planner actually choose it.

Neither subsumes the other, and the split is what makes the second one honest. db-perf refuses to certify a table below minRows in tools/db-perf-baseline.json — it SKIPS loudly locally and FAILS in CI. The scale seed's row count is overridable so a constrained runner can go smaller; the floor is what stops that knob from buying a green.

3. Plan SHAPE is asserted; milliseconds are printed and never compared

Section titled “3. Plan SHAPE is asserted; milliseconds are printed and never compared”

A wall-clock threshold on a shared CI runner is a coin flip, and a flaky perf gate is a deleted perf gate — which takes the real assertion with it. What is asserted is structural and does not flap: which index the planner chose, no Sort above a keyset leaf, no node whose parent relationship is SubPlan, and buffer counts in the right order of magnitude.

SET enable_seqscan = off is not used. It is the classic way to make this kind of gate lie: with it, the planner will use any index, so a table whose only index is useless still produces an Index Scan node and the assertion becomes a statement about the flag.

What this found

Section titled “What this found”

The gate was not written to fix a known bug. Both of the following were found by running it.

The keyset seek was O(page number)

Section titled “The keyset seek was O(page number)”

The shipped DAL expressed its cursor the way every keyset tutorial does — one disjunction covering both lexicographic cases:

created_at.lt.X, and(created_at.eq.X, id.lt.Y)

It is logically correct, it is what the DAL's own comment cited use-the-index-luke.com for, and PostgreSQL cannot turn a top-level OR into an index range. The whole predicate lands in Filter:, so the scan still starts at the tenant's newest row and discards everything the user has already been shown. Measured against 1.1M seeded rows, page 1000:

spelling Index Cond Rows Removed by Filter buffers
one disjunction org_id = $1 1115 1798
range + tie-break org_id = $1 AND created_at <= $2 3 8

That is the cost of OFFSET wearing a keyset costume. The DAL now sends the range as its own predicate alongside the tie-break — exactly equivalent to (created_at, id) < (X, Y), which is the form PostgreSQL would prefer and which PostgREST's filter grammar cannot express — and query-shapes now reds any keyset seek carrying no range predicate on its leading sort column. The static rule exists because the live probe earned it.

parseIndexes could not see a constraint added in a multi-action ALTER TABLE

Section titled “parseIndexes could not see a constraint added in a multi-action ALTER TABLE”

ALTER TABLE t DROP CONSTRAINT c, ADD CONSTRAINT c PRIMARY KEY (...) — the shape a tenant re-scope uses to swap PRIMARY KEY (id) for PRIMARY KEY (org_id, id) — parsed as a pure DROP, because the drop half scanned the whole statement and the add half was anchored to the table name. Every consumer of that parser therefore believed notes had no primary key, silently vacating the leading-column and partition-ready-unique rules that depend on it.

Honest losses

Section titled “Honest losses”
  • The probe list is authored, even though the shapes are not. A probe that exercised fewer branches would record fewer shapes. The function closure stops a query from being omitted entirely, and db-perf runs the real statements at scale, but a branch nobody probes is a branch nobody measures.
  • db-perf measures ONE tenant — the largest, chosen by counting rows rather than by a fixture id. Skew inside a tenant is not modelled.
  • Plans are per-configuration. The lane's verdict is about the Supabase local stack's Postgres at its default settings, on 2M rows with a 3% whale. A production instance with different work_mem, different cardinality or different skew may plan differently. The shape assertions are the ones most likely to transfer; the buffer budgets are the least.
  • query-budget measures a workload, not a request. It bounds the whole live-api-proof suite and reds a normalized statement executed too many times. It cannot attribute a count to an endpoint.
  • Nothing here bounds concurrency. A plan that is correct and cheap for one caller is still 400 callers' worth of work when 400 tenants run it at once; that is the per-role statement timeout's job, and it bounds duration, not concurrency.

Sources

Section titled “Sources”