Skip to main content

Database Ops

SABLE's operational database work is almost entirely in sable-agents-demo/supabase/migrations/ — there is no separate ORM migration tool. This page collects the patterns worth knowing before writing a new migration or debugging a slow query against the larger tables.

Migrations are the only path to schema change​

Per the production boundary (see Developer Onboarding), schema changes are new migration files reviewed in a PR — never a direct supabase db push from a workstation. supabase migration list (from sable-agents-demo/supabase/) confirms every local migration is applied remotely; supabase db diff shows what a linked project would need to catch up to local migration files.

Read the comment lines — they're documentation​

Migration files in this repository are written with the reasoning inline, not just the SQL. For example, 20260909200000_index_inactive_staged_rows.sql opens with the actual production incident that motivated it — a cleanup query hitting the 8-second statement_timeout 235 times in twenty hours against an 18 GB, 3.1-million-row spreadsheet_rows table because nothing indexed the filter it scanned on. That kind of context does not survive in the schema alone; read the file, not just \d table_name.

The largest tables and why they need care​

TableScale (as of writing)Operational note
spreadsheet_rows~18 GB, 3.1M rowsIngested spreadsheet content staged for indexing; index_active = false rows are the exception (superseded by a re-index) and should be deleted within a day — see the cleanup pattern below.
knowledge_chunks~1.8 GBSame staged/superseded cleanup path as spreadsheet_rows; both got a partial index on (indexed_at) where index_active = false so the cleanup query is an index lookup, not a full scan.

The batch-delete pattern: server-side, not client-paginated​

discard_stale_staged_rows(p_table, p_cutoff, p_limit) is a SECURITY DEFINER function, callable only by service_role, that deletes up to p_limit stale staged rows from an explicitly whitelisted table (spreadsheet_rows or knowledge_chunks — the table name is never interpolated from caller input). This exists because deleting by a client-supplied list of ids puts the ids in the request: 2,000 UUIDs is roughly 75 KB, and the HTTP/2 path in front of PostgREST rejects requests well under that (fathom-sync's 400-id in() filter failed at roughly 15 KB in production on 2026-09-09). One server-side call, one bounded statement, no oversized URL.

The general shape — index the exact predicate a scheduled cleanup filters on, then delete through a small whitelisted RPC rather than a client-built query — is the pattern to reach for the next time a "delete the stale rows" job needs to scale past a few hundred rows.

Locking discipline​

supabase db push runs each migration inside a transaction, so a new index build takes a SHARE lock that blocks writes to that table for the duration of the scan (reads continue uninterrupted). CREATE INDEX CONCURRENTLY is not available inside a transactional migration, so when a table only receives writes during a specific, known window (document indexing, for spreadsheet_rows and knowledge_chunks), the tradeoff is explicit in the migration comment: merge and deploy when nobody is actively indexing, rather than paying for CONCURRENTLY's extra complexity.

Advisors, not guesswork​

Two hardening migrations in September 2026 (see Security Posture) came directly from Supabase's built-in advisor findings, and just as importantly, two other advisor suggestions were deliberately not applied because the reviewer traced through what they would actually break (moving the vector extension out of public would stop a search_path-pinned function from resolving the vector type; pg_net isn't relocatable). Run the advisors before assuming a schema change is safe, and read every suggestion's actual effect before applying it — "the linter said so" is not sufficient justification on its own.

Local helper tests​

cd sable-agents-demo/supabase
npm test        # node --test tests/*.mjs
npm run seed     # node seed.mjs

Where the code lives​

  • sable-agents-demo/supabase/migrations/*.sql — every schema change, in order, with reasoning.
  • sable-agents-demo/supabase/tests/*.mjs — backend helper tests.
  • sable-agents-demo/supabase/seed.mjs — the seed script.
  • sable-agents-demo/trigger/documents.ts — the staged-generation cleanup task that motivated the partial indexes above.