Database

Canonical references for the database surface — the in-repo files are the source of truth, this page summarises the constraints those files encode.

• packages/db/migrations/ — hand-authored, numbered NNNN_*.sql files applied in order by migrate({ connectionString }). They are hand-written, not generated: the drizzle journal is empty so db:generate emits a full snapshot rather than incremental ALTERs and is therefore not used (drizzle-kit is kept for schema typing and db:check). Applied files are tracked by SHA-256 checksum in _app_migrations, so re-runs skip already-applied files. migrate() holds a session advisory lock while applying, so concurrent callers serialize and the loser skips the already-applied files instead of racing on non-transactional create type DDL. This is a migration-time DB lock, not a runtime coordination lock, so it does not breach the no-distributed-locks invariant. CI runs the packages/db isolation/projection suites against a real Postgres via DATABASE_TEST_URL in the integration job; without that variable they skip. DATABASE_TEST_URL must name a database whose name ends with _test (e.g. binance_trading_bot_test): the integration and isolation harnesses TRUNCATE / delete rows on it, and assertTestDatabaseUrl refuses to run against any non-_test target so a value pointed at the live binance_trading_bot cannot wipe real data.
• A migration is immutable once it has shipped. _app_migrations keys on the file name and stores the body's SHA-256, so renaming, renumbering, deleting, or editing an applied file — a comment is enough — makes the runner throw already applied with a different checksum. Refusing to mutate history. on every database that already holds the old value. It throws at that file, before any later migration runs, and the checksum map is read once up front, so a repair migration cannot execute itself out of the hole: recovery means restoring the original bytes or hand-editing the ledger. To change what a shipped migration did, write a new one. CI cannot see this on its own, because every suite migrates a fresh database where a mutated file looks correct; scripts/ci/no-mutated-applied-migration.sh supplies the missing oracle by pinning each file's digest in packages/db/migrations/checksums.json. Adding a migration means adding its line to that manifest. Immutability is only half the rule: a new migration must also take the next unused number, because the runner applies files in name order, so one inserted below the high-water mark runs last on every database that already migrated past it. scripts/ci/no-backfilled-migration.sh enforces that — see A migration number is never reused and never backfilled.
• packages/db/src/schema/ — drizzle schema definitions, one file per logical table group.
• packages/db/src/repo/ — typed repository wrappers over a two-tier scope. Account-scoped functions take an AccountScope (produced only by scopeAccount); profile-scoped functions take a ProfileScope (produced only by scopeProfile). Each scope runs the single ownership check for its tier, so a scoped query cannot be reached without ownership having been proven.

Account isolation policy

The data model is one operator (users) → many accounts → many profiles. An account is a first-class row (accounts, owner_id → users.id) holding one Binance API key pair, one binance_mode, and one user-data stream; profiles.account_id → accounts.id. Ownership is a two-hop chain — accounts.owner_id = operatorId and profiles.account_id = accountId — resolved in a single query by scopeProfile(db, operatorId, accountId, profileId) (and scopeAccount(db, operatorId, accountId) for account-level surfaces). UserId (the operator) and AccountId are distinct branded types, so misplacing one for the other is a compile error. The typed repository layer is the single enforcement point; app code resolves a scope via profileRepo / accountRepo (or the *FromScope variants) and ownership is checked exactly once per scope. There is no DB-side RLS — the cross-account integration tests in apps/api/__tests__/cross-account.test.ts and packages/db/__tests__/isolation/*.test.ts verify the policy in CI, and packages/db/__tests__/repo/ast-check.test.ts statically enforces the scope-first contract.

See also: account-isolation.md.

override_actions: result vs outcome

override_actions backs two unrelated operator flows, and each owns its own nullable jsonb column. They are not interchangeable:

column	meaning	written by	read by
result	the action's SIDE-EFFECT payload — Binance's convertDust response	finalize(), on a dust transfer only	the dust-history route
outcome	what the operator actually GOT: { status, reason?, at }	every terminal write (settle / finalize / the sweep)	GET /override, the SPA

outcome is stamped by EVERY terminal transition, so "the row is closed out" and "the row carries an outcome" are one fact — a row marked done with no outcome cannot tell a filled force-sell apart from one the exchange refused, and reads on the symbol page exactly like a success. The two columns stay separate for the same reason: sharing one would make null mean both "still pending" and "settled, but the payload is not an outcome", which no reader can disambiguate without guessing at the shape.

Migration 0072 adds the outcome column plus the two indexes these reads need — a (profile_id, symbol, created_at desc) index for "the newest override for this symbol, settled or not", and a partial index on the pending symbol-scoped rows for the stranded-row sweep.

All four terminal paths funnel through one private consume() in packages/db/src/repo/override-actions.ts, whose outcome argument is required, so a new "mark it done" writer cannot reintroduce the outcome-less row. The stranded-row sweep is account-tier (reapExpiredForAccount, on AccountRepo): two statements per account per sweep — one per stranding branch, see below — rather than a scope-resolve plus an UPDATE for every active profile.

picked_up_at: a write-once record, not a lease

Migration 0075 adds a third nullable column, and it is the odd one out: nothing is gated on it, and nothing ever clears it.

written	read	cleared by	gates any behaviour
once per override, by the tick, when it marks itself about to dispatch and before applyAll runs (markPickedUp, guarded picked_up_at is null so a retry cannot slide the timestamp)	one consumer: reapExpiredForAccount, which only asks whether it is NULL	nothing	nothing

(It is of course in the WHERE of both sweep statements — "gates" means no path branches on it the way the cancel route branches on a claim, and the dispatch gate on the claim's own CAS reply.)

The "cleared by" column is why this is a new column and not a reuse of processing_at. A claim is a lease: it exists in order to be handed back. Two paths null it — releaseClaim, when a side-effect failed and the next tick should retry, and reapStaleProcessing, when a worker died holding a claim. A lease therefore cannot carry a fact that must OUTLIVE the crash: the crash is what summons the stale-claim reaper, and the reaper clears exactly the evidence the sweep would need to read. picked_up_at is written once and cleared by nothing, so it survives.

This is why the two columns stayed separate once the tick DID become a claiming consumer of overrides. The claim means "work is in flight now", which is inherently revocable; the breadcrumb means "work was once in flight", which must never be revoked. Merging them would put the durable fact back under a reaper's control.

The two also differ in span, which is what keeps a claim from swallowing cancellation. processing_at is read as a guard — deletePendingForSymbol skips a claimed row — so the tick holds it only across the window where a dispatch is genuinely in flight, and settleOverride releases it before re-arming the Redis key. An operator cancel arriving inside that window is answered 409, never silently dropped; outside it the row is deletable again, and the compensating re-arm still infers "the operator revoked this" from that cancel having deleted the row. consumed_at is terminal and would settle the override the tick is still holding.

Both columns are also read at ARM time. record settles the row a new override replaces superseded, in one transaction with the insert, because the operator's Redis key is overwritten blindly and only the newest override can run — but it settles only rows where BOTH are null. A claimed row would disappear from findActiveForSymbol and take the cancel route's 409 with it; a breadcrumbed one is destined for the sweep's unknown, the only outcome that notifies, and terminal writes are immutable, so an early superseded would suppress it permanently. Same asymmetry as everywhere else here: the lease and the breadcrumb both mean "someone else owns this row's ending".

No index. The sweep's predicate is unchanged and its two branches are disjoint halves of the same pending set already covered by override_actions_pending_symbol_idx; a second index on a two-valued column would earn nothing and cost every override write. What the two branches mean for the operator is in worker-pipeline.md.

orders: account-owned, profile-referencing

An order is ACCOUNT-domain: its Binance id is unique per account, the user-data stream that reconciles it is per account, and it keeps resting on the exchange whether or not the strategy that placed it still exists. Migration 0073 moves the table onto that footing:

column	before	after (0073)	why
account_id	—	NOT NULL, FK → accounts ON DELETE CASCADE	The owner. CASCADE because deleting the account destroys the key pair: nothing can ever query, cancel or reconcile those orders again, so there is no one left to keep the row for.
profile_id	NOT NULL, FK (implicit CASCADE)	NULLABLE, FK → profiles ON DELETE SET NULL	A reference, not ownership. Deleting a profile DETACHES its orders instead of destroying them: a resting order is real money, and its ledger row must outlive the strategy or the order is unreconcilable.

The backfill is total (profile_id was NOT NULL with an FK before, so every row joins to a profile and every profile to an account), which is what lets SET NOT NULL on account_id be unconditional.

New index orders_account_binance_order_id (account_id, binance_order_id): the seek every reconciliation path now makes (user-data stream, orphan sweep, adopt route, detached-orders-reconcile), and the only way a detached row is reachable at all.

The partial unique index orders_one_live_per_intent (profile_id, symbol, intent) WHERE closed_at IS NULL needs no change: Postgres treats NULLs as distinct in a unique index, so no two detached rows ever conflict and a detached row never blocks a new live slot for the (recreated) profile.

A recovery row — an order that IS live on Binance but whose normal bookkeeping did not land — is written by orders.insertTracking under a reserved intent, `${intent}:untracked:${binanceOrderId}`. intent is an open, strategy-owned string (no CHECK since 0026), and the reservation is what keeps the row out of the strategy's live slot: that slot is very often ALREADY HELD by the still-resting previous order — which is the single most likely reason the normal write failed in the first place — so an insert under the strategy's own intent would conflict on the partial unique index and be silently swallowed, leaving the live order with zero local trace. The row stays fully visible where it matters: the orphan sweep and the exposure guard read account_id / closed_at (intent-blind), and the fill adopter seeks by (account_id, binance_order_id).

trade_archive.missing_cost_basis: an under-count must say so

profit is cost-basis matched, so a SELL whose realized_pnl is NULL contributes nothing rather than booking its proceeds as a zero-cost gain. That arithmetic is correct, and it is also unreadable once written: profit = 0 renders as +0.00, and the operator reads a real trade as flat. The count was already computed at archive time and only logged, which put the whole signal outside the row it describes.

Migration 0080 adds missing_cost_basis integer NOT NULL DEFAULT 0: how many of the cycle's SELLs had no cost basis. A positive value means the row's profit is a conservative under-count. The API-derived net figure, profit − fees_quote, inherits the same gap; the API carries the count so the archive page shows an n/a marker described as "P/L unavailable" instead of a number nobody measured.

The bought and sold totals under-count on exactly the same rows, so they are not a safe fallback: total_buy_quote is Σ cost_basis_quote, which an un-costed SELL contributes nothing to, and total_sell_quote is derived as total_buy_quote + profit so that profit = sell − buy holds exactly. A fully un-costed cycle therefore renders 0 in both money columns despite real coins changing hands, and the period rollups above the table count it as zero. The page does not say so: the row's marker states only that the P/L is unavailable, and the paragraph that used to explain the under-counting money columns was deleted because it cost more of a phone screen than the rows it described. The fact survives in the user guide, not in the UI.

The default is 0, which is not a claim about existing rows — it is the assumption the UI already made before the column existed. Backfilling it would mean re-deriving cost bases that no longer exist, which is precisely the fabrication the NULL was protecting against.

trade_archive.fee_basis: zero is a value, not evidence

fees_quote is the additional quote-currency adjustment the cost-basis model has not already charged, not a completeness sentinel. A BUY base-asset fee can have a legitimate zero adjustment when orders.base_commission_netted exactly matches the commission Binance later reports; zero can also accompany missing or unpriceable evidence.

Migration 0090 adds default-false completeness markers to trade_archive and equity_snapshots, plus the nullable application-owned base-commission amount on orders. Existing values stay intact but are not presented as exact Net P/L; older writers can omit the new columns during a rolling deployment.

Migration 0090 was where that started, as a fees_quote_complete boolean. A boolean has two readings and the data has three, and the missing one is the one that matters: a fee reconstructed from the account's commission-rate table is not the charge Binance reported, but it is not a hole either. Under a boolean the reconstruction had to be filed as one or the other, and both answers are wrong in a way the operator cannot see — call it complete and a rate-derived number sits under an unmarked profit factor, call it incomplete and a whole account paying commission in BNB reports no Net P/L at all.

Migration 0093 replaces the boolean on both tables with fee_basis text not null default 'unknown', constrained to exact / estimated / unknown by a named CHECK. It is a text column with a CHECK rather than a native enum because adding a value to a Postgres enum cannot run in the same transaction as rows that use it, which would make the next tier a two-deploy change. The tiers rank unknown(0) < estimated(1) < exact(2) and every fold takes the weakest member: a window is only as trustworthy as its worst row, and a rank maximum would read a window holding one reconstructed cycle as fully proven. An empty set folds to exact, which is not a claim about missing evidence but the arithmetic identity that keeps a profile with no closed trades from reporting its zero as unaccounted-for.

The 0093 update derives each existing row's tier from the evidence already stored rather than defaulting the table, in six arms applied in order. A row the old writer already certified keeps exact unchanged. A row with no archived orders, or an empty fees map, is a hole (unknown). A row whose charges are all zero and whose stored total is zero to match is exact, as is one whose charges are entirely in the quote asset and whose total reproduces that leg exactly. A total exceeding the quote-asset charge means something was valued by reconstruction (estimated). A base-asset charge alongside a total that still reproduces the quote leg is estimated too, because orders[].base_commission_netted shipped with 0090 and was never backfilled, so no row here can prove the cost basis absorbed it. Anything else — including a base-asset charge on a row whose total falls short of its own quote leg, which two live rows do — is unknown.

Every certifying arm carries that reproducibility test explicitly, and the two that look like shape checks are the ones that most need it: charges that are all zero imply a zero total, and a base leg means a netting only if the rest of the total still adds up. The migration refuses to run if it finds a row marked complete alongside an empty fees map or no archived orders at all, because either combination means a producer wrote a certification it had no evidence for and the derivation would silently launder it.

The boolean is not dropped in 0093. On the live cluster the migrate hook is an Argo CD Job at sync-wave: "1" and the Deployment is at "2", so a migration commits a full wave before any pod is replaced and the previous image is still serving; dropping a column there removes it out from under running code, which is how 0091 dead-lettered 178 jobs on 2026-08-25. strategy: Recreate does not prevent it — it orders pod-vs-pod, never migration-vs-pod. 0093 is therefore the expand half only, and the column is left in place with its default false so the new writer, which no longer names it, keeps inserting cleanly. A contract migration drops it once no pre-0093 image can be rolled back to.

Display follows the tier uniformly: unknown withholds the derived figure, estimated shows it and says so in words, exact shows it unmarked. The edge-decay verdict is one bar at two sites: the Slack alert in edge-decay-monitor.cron.ts and the on-screen badge from useEdgeVerdict both refuse anything below exact, so the screen never shows a decay warning the alert channel would not have sent.

The equity curve is the one surface that neither withholds nor filters, and the reason is that a stamped tier cannot recover. A snapshot's realised leg is sumProfitInRange(quote, EPOCH, now), an all-time fold, so its tier is the weakest cycle the profile has ever closed and closing more only weakens it. Fee reconciliation does lift an archive row out of unknown, but it rewrites the archive alone — no snapshot is ever re-stamped, so a recorded point keeps its tier until retention drops it. A gate on unknown there is not "defer until the evidence improves", it is a profile whose curve stops for good the first time a historical fill goes unvalued — and, because 0090 shipped the completeness marker default false with no backfill, every point recorded before this release converts to unknown, so the same gate would blank the whole history on deploy. The worker therefore records every point with its tier stamped, listForProfileInRange returns every tier, and the card folds the weakest tier across the plotted window onto the Net P/L headline.

A commission charged in an asset that is neither the base nor the quote — BNB on a discounted account — is valued by reconstructing the rate Binance applied, never by a current ticker. GET /api/v3/account/commission returns the account's per-symbol maker/taker and buyer/seller legs plus its BNB discount, and the charge is quoteQty x rate: a rate stays correct for a fill from months ago in a way a price cannot, and pricing a historic fee at today's market would fabricate exactly the number fee_basis exists to qualify. The rates are fetched lazily — only after a pass finds a fee it could not otherwise value — and memoised per symbol for the life of the job, so an ordinary cycle spends no extra request weight. When the lookup is unavailable or its payload does not validate in full, the commission stays unpriced and the row stays incomplete, which is byte-for-byte the behaviour that predates rate reconstruction.

trade_archive.fees: plain decimal text, never an exponent

The per-asset commission totals in the fees jsonb are strings, so Postgres normalises nothing on the way in and whatever the writer spelled is what every reader gets. The writer used Decimal#toString(), which switches to exponential notation outside decimal.js's -7 / 21 exponent thresholds — and a BNB commission on a discounted account clears the small side routinely, so rows stored 1e-8 where the archive page renders 0.00000001. The producer now formats with toFixed() through asDecimalString, which has no such threshold and is exact at any magnitude.

Migration 0092 repairs what was already stored. It rewrites only the values that are actually exponent-shaped, matched by regex, so an already-plain value comes back byte-identical and no non-numeric string can reach the ::numeric cast that does the re-spelling. It touches neither fees_quote, which is numeric(38,18) and was normalised by Postgres on write, nor the fee-evidence marker of the day (fees_quote_complete, since replaced by fee_basis): re-spelling a string is not new fee evidence, and a row that could not be valued before the migration still cannot be after it.

trade_archive.archived_at: paginate on the microsecond token

The archive page pages by keyset on (archived_at, id), and archived_at is timestamptz — microseconds. The cursor it emitted was that row's timestamp as the node-postgres driver hands it back: a JavaScript Date, which is milliseconds. A row at .123200 is strictly older than a page boundary at .123456, yet satisfies neither archived_at < .123000 nor archived_at = .123000, so the walk skipped it on every later page and nothing reported the loss — a silent failure in the operator's own trade history. Two archives in one millisecond is ordinary: a recovery backfill writes a cycle per iteration.

listForProfilePaginated therefore returns each row with a cursorToken — to_char(archived_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'), the full microsecond value as an ISO string — and the route emits ${cursorToken}__${id} and binds the timestamp half straight back as a string, cast to timestamptz in the predicate so it stays a direct column comparison rather than a string compare. Never re-parse that half through new Date: that is the truncation the token exists to avoid. This is the same treatment audit_logs, action_logs and backtest_runs already use; trade_archive was the paginated timestamp column that had not received it. The column keeps its precision — only the cursor changed. Two tests pin the two halves, with two rows differing only below the millisecond each: packages/db/__tests__/isolation/trade-archive.test.ts pins the predicate, binding a full-precision token straight back, and apps/api/__tests__/routes/archive.test.ts pins the emit side — that nextCursor carries six fractional digits at all, and that the sibling row is reachable on the next page. The boundary schema also rejects the two ISO instants Postgres cannot bind: there is no year zero in AD/BC notation, so 0000-… comes back as SQLSTATE 22008, and a fractional second long enough to overrun the datetime parser's fixed work buffer is refused outright rather than rounded away — each an unhandled 500 on a route whose declared failures are 422 and 503. z.iso.datetime() is calendar-aware (it rejects month 13, day 32, 2023-02-29, 24:00, :60) but leaves exactly those two gaps, so isBindableTimestamp in @app/contracts closes them with a length cap and a year-range test — a positive range assertion, not a 0000- blocklist, because a blocklist only refuses the spellings it was told about. Every cursor reader shares it through one compositeCursor factory (apps/api/src/lib/cursor.ts), parameterised by separator and by whether a bare timestamp is honoured, so the archive, audit-log and backtest-run readers cannot drift apart; the action-log cursor's own regex still owns its microsecond precision and defers the calendar to the same predicate.

The keyset predicate is a row comparison, (archived_at, id) < ($ts::timestamptz, $id::uuid), not the equivalent a < x OR (a = x AND b < y) pair. Both return the same rows, but only the row comparison becomes a btree start condition: with the OR form the boundary stays a per-row filter, so every page walked the profile's archive from its newest row and discarded everything above the cursor, and page cost scaled with the archive's total size rather than with limit. trade_archive_profile_archived_id (migration 0086, (profile_id, archived_at desc, id desc)) supplies the matching order. Measured on 5,000 rows with the cursor 4,000 deep: before, a Seq Scan feeding a top-N heapsort, 4,003 rows removed by filter, 92 shared buffer hits, 2.374 ms; after, an Index Scan with both cursor columns in the Index Cond, 0 rows removed by filter, 4 shared buffer hits, 0.035 ms. packages/db/__tests__/isolation/trade-archive-plan-shape.test.ts pins all three properties — no Sort node, an Index Cond naming the index and both cursor columns, and zero rows removed by filter — because a fix that only added the index would still leave the boundary as a filter.

backfill_attempts.symbol_unavailable: a terminal reason the counts cannot express

The other reasons a coin lands in the "nothing to recover" note are derived from the reconstruct drop counts, and all of them assume the trade history was read. A delisted coin never gets that far: the backfill handler needs the symbol's base/quote assets from the exchange-info cache, and without them it cannot value a single fill.

Migration 0081 adds symbol_unavailable boolean NOT NULL DEFAULT false. The handler stamps it when the symbol is absent from a primed cache — absence alone is not evidence, because a cold cache looks identical, so the keyspace is probed for the account's mode first and the job is retried when it is empty. The flag outranks the count-derived reasons on the API projection: with round_trips, skipped_orphan_sells and dropped_overshoot all 0, the count-derived reason would read open-or-pre-history, which names the wrong cause and points the operator at a window that does not exist.

Like 0080's default, false is not a claim about existing rows: it is the state every row already implied before the column existed, and a marker re-opens by itself once a later fill makes it stale.

condition_states: a level store beside the edge stream

action_logs records edges — that something changed. Operators ask level questions: what is wrong now, and since when. The two are not interchangeable, and writing only on change is what exposes the gap: a symbol stuck on one reason for three weeks has exactly one transition row, written three weeks ago. Once that row is past the retention horizon the query returns nothing, so the log viewer is emptiest for the most-stuck symbol, which is the worst possible failure mode for a diagnostic.

Migration 0077 therefore stores the level as a level. condition_states is keyed (profile_id, condition, symbol), carries the reason as code plus the producer's own detail payload, and stamps since when the reason began. since is the column that makes aggressive action_logs pruning safe: durations stay exact after the opening edge has been swept. Size is bounded by open conditions × symbols rather than by time, because a row exists only while its condition is open and resolving deletes it.

It is a plain table rather than more action_logs rows for a reason that is structural, not stylistic. action_logs is a TimescaleDB hypertable partitioned on time, and a unique index on a hypertable must contain every partitioning column — so a key of (profile_id, condition, symbol) cannot exist there at all, and adding time to it makes every write a new row rather than a replaced one. ON CONFLICT DO UPDATE on the state key is simply unavailable. The read is also unindexable in practice: "current condition per subject" over the log is a DISTINCT ON over a jsonb expression key that none of the three action_logs indexes can serve, where here it is a primary-key lookup.

symbol is an empty-string sentinel rather than NULL when the condition is about the profile itself, because Postgres forbids nullable columns in a primary key and the alternative — two partial unique indexes — would force two upsert paths for one write. PROFILE_SUBJECT is exported from @app/db so no reader has to spell the sentinel itself. condition is text rather than an enum so adding a producer needs no migration; the closed set lives in packages/contracts/src/condition.ts, where readers already validate it.

Both writes go through one recordCondition, which writes nothing when the code is unchanged, so the per-tick hot path stays free. 0082 adds a nullable change_key for the case where the reason holds but the threshold it names has moved: comparing code alone dropped that write, leaving the stored detail advertising a number that is no longer the live gate.

Dashboard read-through caches

The two dashboard projections cache their composed payload in Redis to absorb the SPA's 5s poll without re-running the per-profile × per-symbol Postgres + Redis fan-in on every request:

• getAggregateForAccount — the per-account cross-profile home rollup. Key dashboardAggregateCacheKey(accountId) (tenant:<accountId>:dashboard-aggregate:cache), TTL DASHBOARD_AGGREGATE_TTL_S = 5.
• getProfileDashboard — the per-profile view. Key profileKey(scope, 'dashboardCache'), TTL PROFILE_DASHBOARD_TTL_S = 5.

Because reads are cached, a write must drop the affected keys or the UI replays a stale payload until the TTL expires (the "action is slow to refresh" lag). The bustDashboardCache middleware (apps/api/src/middleware/) is mounted app-wide and, after any successful (2xx) non-GET request by an authenticated user, reads the {accountId} from the account-scoped path and deletes that account's aggregate key plus the per-profile key when the path also carries a {profileId}. The SPA's on-success refetch then recomputes immediately. Invalidation is best-effort via invalidateDashboardCaches — a Redis failure is swallowed so it never turns a successful write into a 5xx; the read just waits out the TTL. The cache holds composed DTOs only; the source of truth stays in Postgres, and ownership is re-proven on the recompute path, so a stale or dropped cache is never an isolation concern.

Hypertables: the root heap

action_logs and candles are TimescaleDB hypertables (0005_hypertables.sql). Their rows live in chunks; the parent relation — the root heap — is meant to stay empty. TimescaleDB's planner expands a hypertable to its chunks and leaves the parent out of the plan, so a row that does end up in the root heap is invisible to every statement naming the hypertable: select, update and delete alike, with or without only. Chunk-drop retention never reaches it either, so a stranded row outlives every retention horizon indefinitely.

alter table ... set not null is the exception. It does not go through the planner, it scans the heap directly. A migration that backfills a column and then constrains it is therefore reading two different sets of rows, and fails on exactly the rows nothing else can see — the backfill reports every row updated and the constraint still reports a null. This is what aborted 0076 after deployment: action_logs had 17 rows stranded in its root heap, and all three Job retries failed identically. 0075a_action_logs_root_heap_drain.sql drains them back through the hypertable (delete from only ... feeding an insert, so routing files them into a chunk), which also puts them back under the retention horizon.

How the rows got there was not established. The one documented way to reach the root heap is timescaledb.restoring, the mode timescaledb_pre_restore() sets so a dump can be reloaded without routing; an insert made while it is on lands in the parent. Whether that is what happened here is unproven — the Postgres logs for the window had rotated away.

Any migration adding a constraint to a hypertable column must drain the root heap first. When a backfill claims success and the constraint still fails, compare from <hypertable> against from only <hypertable> before theorising about concurrent writers. Drain-before-constrain is also what forced this repo's one deliberately backfilled migration: 0076 was already failing in the field, and the apply loop rethrows at the first failing file, so no forward-numbered repair could ever be reached — the drain had to sort below it as 0075a. That file is the sole entry in no-backfilled-migration.sh's grandfather list, and the documented precedent any future stuck-migration repair is measured against.

One measured subtlety: the parent is excluded from the plan only once the hypertable owns at least one chunk, so a backfill on a chunkless hypertable does reach the root heap. No regression test covers this — the insert has to be made under timescaledb.restoring, which survives in the parent through 2.27.1 and is discarded from 2.28.0, so the fixture cannot be built on the deployed 2.29.2 image.

Pool checkout deadlines

Every pool is created by createPool (packages/db/src/pool.ts) with connectionTimeoutMillis: POOL_CHECKOUT_TIMEOUT_MS — 5 seconds, for all three kinds. Left unset, pg-pool queues a checkout with no timer at all: once max connections are held, every later checkout on every route waits indefinitely, with no error, no log line, and no bound of its own. The api pool is 10 connections, so one route holding four of them per request needed only three concurrent requests to take the whole process dark while its health check kept answering.

The one option arms both of pg-pool's deadlines, which is why the value is seconds rather than the few hundred milliseconds a queue wait alone would deserve — it also has to cover a cold connect to a healthy database:

• queue wait — the pool was full and nothing came free in time. Rejects with Error('timeout exceeded when trying to connect').
• cold connect — a brand-new connection did not finish its TCP + startup handshake in time. Rejects with Error('Connection terminated due to connection timeout'), carrying the driver's Connection terminated unexpectedly on cause.

Neither error carries a SQLSTATE or an error class, so poolCheckoutTimeoutKind classifies them by exact message equality (walking the cause chain, because drizzle wraps what the driver threw). Exact rather than substring on purpose: a reworded pg-pool release then fails closed — a saturated pool degrades from 503 back to 500, which is wrong but not a lie — and takes packages/db/__tests__/pool-checkout-timeout.test.ts red with it, since that suite drives a real pg.Pool into both paths against a net blackhole rather than asserting hand-written fixtures. Substring matching would instead relabel any unrelated error whose message quotes the phrase as backpressure.

One value serves all three kinds because only api ever queues: worker fans out per job against 25 connections, admin is max: 2 with no concurrent fan-out, and migrate() opens a bare pg.Client so migrations never touch a pool. It is a constant rather than an environment variable — the failure it prevents is unbounded queueing, and an operator who can set it can also set it back to a hang.

The api error handler maps a classified checkout timeout, and a statement cancelled by withStatementTimeout (SQLSTATE 57014), to 503 SERVICE_UNAVAILABLE, logged at warn with the request path. They are load events, not defects: an unhandled error line for a saturated pool reads as a bug and sends whoever is on call looking for one.

Each of the three gets its own message and its own log name, because they take different remedies and the log line is what an operator acts on:

Log name	Fault	Remedy
db_pool_checkout_timeout	queue wait — the pool stayed full	raise *_DB_POOL_MAX, or shed load
db_connect_timeout	cold connect — the database never completed a handshake	fix the database; raising the pool max makes this worse
db_statement_timeout	one statement outran its budget	find the slow query

Collapsing the first two into "connections exhausted" would be wrong for the second: pg-pool only dials a new connection when the pool has room, so a cold-connect timeout means the pool was not full, and pointing the operator at the pool size aims more concurrent attempts at a server already failing to answer.

The two bounds are complementary and a route needs both. GET /profiles/{id}/trade-archive is the worked example: its four reads used to run as a Promise.all, taking a pooled connection each, and now run in sequence inside one withStatementTimeout(di.db, 5_000, …) transaction whose ownership scope is minted on the same connection. One request therefore holds one connection. GET /profiles/{id}/discovery and GET /account/health now follow the same shape, and the health bar is the reason it matters most: it summed realised P/L per profile, so its checkout burst grew with the profile count and an operator with a handful of profiles could empty the pool on one poll of a bar that polls. One grouped rollupRealizedByProfileForAccount read replaces that fan-out, left-joined from profiles so a profile that closed nothing still reports a zero in its own currency. Redis reads stay outside these transactions and concurrent — a different pool with a different failure mode, and holding them outside costs nothing. The transaction issues up to five statements that can stall on a view=full request — the ownership join, then the four reads — so the worst case hold is 25s, five times the per-statement budget. A view=rollup request, which is what every polling surface asks for, issues two. That deliberately exceeds the 5s checkout deadline: under sustained archive concurrency a waiter can be rejected while the holder is still legitimately working. The budget bounds a pathological stall, not the healthy case (roughly a tenth of a second per read), and it is not sized down to make the arithmetic tidier because there is no production timing for listForProfileInRange against a large archive. It no longer doubles as a size limit on the archive: the paginated read is index-served and costs limit rows, so listForProfileInRange — unpaginated by design, since the rollup is period-scoped rather than page-scoped — is the read this cap now exists for. apps/api/__tests__/routes/archive.test.ts asserts both properties from the pool itself — peak concurrent checkouts during one request, and that a set_config('statement_timeout', …) was actually issued on the acquired connection — because the response body cannot show either.

One consequence for alerting, and one thing the metric's name hides. DBPoolStarved fires on pg_pool_waiting > 0 held for 5m, but that series is sampled by the worker, over the worker pool (apps/worker/src/boot/runtime-gauges.ts), and nowhere else — the api pool has never had a gauge, so api-side starvation was invisible here long before this change. For the pool the rule does watch, the deadline does weaken it: the gauge is an instantaneous waitingCount, so it never accumulated, it stayed high, and a waiter ejected after 5s is less likely to hold it non-zero across five consecutive one-minute samples. The rule is deliberately left as-is (any waiter is still the condition, and retuning it without production data would trade sensitivity for noise). The api's equivalent signal is http_requests_total{status="503"}, which an ejected checkout now raises — a different pool in a different process, so a 503 rate with DBPoolStarved silent is a different incident, unless Postgres itself has run out of connection slots and both pools starve together.

One asymmetry this leaves in the OpenAPI document. A pool-checkout 503 is raised by errorHandler before any route-specific logic, so it is now reachable on every route, while only the routes that open a withStatementTimeout transaction of their own declare 503:. That is a known gap in the document, not a claim that the response cannot happen. Filling it in route by route is the wrong repair — 124 createRoute calls each declare their own responses, so enumerating a transport-level condition on every one of them drifts the moment a route is added, and a partially-applied version is worse than none. The repair is a shared response builder every router composes, which is a change to the whole api surface rather than to the pool, so it is tracked separately; the routes that declare 503: today are the ones that raise it from their own statement budget, which is a route-level fact and does belong on the operation.