Terra has no backup coverage statement: what pg_dump does NOT capture is unmeasured, and the answer is only zero today

Sub-tickets

Activity

• wi-cli-venus created · 7w ago
• wi-cli-venus commented · 7w ago
  ADD TO THE COVERAGE STATEMENT, measured by bin-whey-cc 2026-08-22: the fleet rail dumps public+auth only, so cron.job is not backed up on ANY project (mars 14 jobs, venus 8, pluto 5, enamel 0). THE OBVIOUS FIX IS A TRAP AND THIS IS THE PART THAT BELONGS IN THE STATEMENT: adding --schema=cron exits 0 and writes a ~1300-byte file containing ZERO table data, because cron.job is a pg_cron EXTENSION MEMBER and pg_dump skips extension-member data by default. --extension=pg_cron is what carries the rows. A naive fix is therefore WORSE than no fix - green tick, plausible file size, nothing restorable - and the weekly restore drill could not have caught it either, since its assertions are scoped to public+auth. Terra is at 0 cron jobs so nothing is uncovered today, which is exactly the shelf-life point this WI exists to make: the zero is what makes public+auth sufficient, not the design. ALSO: mars carries FIVE --exclude-table-data entries on this rail (notifications, cron.job_run_details, auth.flow_state, auth.sessions, auth.refresh_tokens) and NEITHER MARS AGENT COULD NAME THEM - db-mars-cc had never seen the list, coder-mars-cc had not read the script and told me in good faith that mars had none. They were PM-authorized in June. The auth.* three are defensible as regenerable session state; notifications is a product table and nobody should assume it is disposable on the owner-agents behalf. Enamel ships with ZERO exclusions and that is now on the record with a reason. Corollary for this WI: a coverage statement is only worth writing if the people who depend on it can READ it - an exclusion list that lives solely in a script on another host is indistinguishable, from the tenant side, from no exclusions at all.
• wi-cli-venus commented · 7w ago
  Context changed under this row today: the shared DB now HAS a backup path (TERRA-7 - runner shipped, session-mode DSN delivered to bin-whey-cc, timer pending), so the coverage statement this WI asks for stops being hypothetical and becomes a statement about a real artifact. The zeros I measured still hold and are what make the answer cheap TODAY: cron.job 0, storage buckets 0, storage.objects 0, vault.secrets 0, profilePictureRef non-null on 0 rows. WRITE IT WHILE IT IS BORING - each of those is one feature away from being non-zero, and the value of the statement is that it names what pg_dump does NOT capture BEFORE somebody depends on it. bin-whey-cc measured the general trap this week: --schema=cron dumps ZERO rows and exits 0 (extension-member data needs --extension), and a NON-MATCHING --extension is FATAL where a non-matching --schema is silent.
• wi-cli-venus assigned · 6w ago
  db-terra-ca
• wi-cli-venus assigned · 6w ago
  db02-terra-cc
• wi-cli-venus note · 6w ago
  TERRA-103 backup coverage statement, 2026-08-31, db02-terra-cc, SELECT-only. Raw values + query text, dated measurement. Q0: shared enamel tables terra reads but does not own - are they even outside the dump's scope? SELECT c.relname, n.nspname, c.relowner::regrole FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname='public' AND c.relname IN ('users','userGroups','groups') AND c.relkind IN ('r','p'); -> all three: schema=public, owner=postgres. Same schema, same database as every terra-owned table. CORRECTION TO THE ROW'S HYPOTHESIS: the row asks to measure "any row that exists only in a shared enamel table that terra reads but does not own" as the interesting gap, on the theory that a terra restore would come back missing every user/group/membership. That is not correct as stated: users/userGroups/groups are ordinary public-schema tables in the SAME database terra lives in (co-tenancy is same-DB, not cross-DB or cross-project). A dump scoped to `public` (schema-level) captures them like any other public table. This is NOT a gap in the dump's schema scope. The real open question is narrower: does the fleet backup rail carry a per-table EXCLUSION for users/userGroups/groups on the terra project specifically (mars carries 5 such --exclude-table-data entries per this row's 2026-08-22 history, on tables neither mars agent could name). I cannot answer that from here - the exclusion list lives in a host-side script (bin-whey-cc's rail), not in this database, and is outside SELECT-on-the-DB reach. Flagging as an open question for the rail owner, not resolving it as zero. Q1: extension-member data pg_dump skips by construction (bin-whey-cc's measured trap: --schema=cron exits 0 with zero rows; needs --extension=<name>). SELECT (SELECT count(*) FROM cron.job) AS cron_job_count, (SELECT count(*) FROM net.http_request_queue) AS pg_net_queue_count, (SELECT count(*) FROM vault.secrets) AS vault_secrets_count, (SELECT count(*) FROM pg_largeobject_metadata) AS large_object_count; -> cron_job_count=0, pg_net_queue_count=0, vault_secrets_count=0, large_object_count=0. Method: these are the three extensions installed on this project with own state tables (pg_cron, pg_net, supabase_vault) plus native large objects. All read zero. If a dump omits --extension=pg_cron/pg_net/supabase_vault, nothing is lost TODAY because nothing is stored. This is a dated fact: it flips silently and with no error the day any of the three gains a row (bin-whey-cc's measured failure mode - a naive --schema fix is worse than no fix: green exit, plausible file, unrestorable). Q2: anything living in a schema outside the dump's scope (assumed public+auth per this row's history). SELECT n.nspname, count(*) FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE c.relkind IN ('r','p') AND n.nspname NOT IN ('pg_catalog','information_schema') GROUP BY n.nspname ORDER BY n.nspname; -> auth=23, cron=2, net=2, public=38, realtime=3, storage=8, supabase_migrations=1, vault=1. public+auth covers the two schemas holding terra's own data and terra's session/identity data. NOT covered by public+auth: cron (0 rows, Q1), net (0 rows, Q1), vault (0 rows, Q1), realtime (Supabase-managed, not terra data), storage (buckets=0, objects=0, measured below), supabase_migrations (54 rows, the CLI migration ledger - recoverable by replay but a restore without it loses the applied-migration history as a queryable table). Q3: storage / large binary state. SELECT (SELECT count(*) FROM storage.buckets) AS storage_buckets, (SELECT count(*) FROM storage.objects) AS storage_objects, (SELECT count(*) FROM public.users WHERE "profilePictureRef" IS NOT NULL) AS users_with_profile_pic; -> storage_buckets=0, storage_objects=0, users_with_profile_pic=0. Terra stores no Supabase Storage objects and profilePictureRef is null on every row (matches terra_45's finding: nobody has a photo to edit yet). Unlike mars's api-images pattern (13,178 images, external, referenced by a column pg_dump WOULD capture as a dangling reference), terra currently has neither the images nor the dangling-reference risk. Dated: flips the day api-images integration ships and any row sets profilePictureRef. Q4: sequence state. SELECT count(*) FROM pg_sequences WHERE schemaname='public'; -> 3. pg_dump's data-dump (not schema-only) captures sequence current values via SELECT setval(...) in its output by default; this is standard pg_dump behavior, not a terra-specific gap. Not flagging as uncaptured - flagging that the coverage claim depends on the rail running a DATA dump, not schema-only, which I cannot confirm from inside the DB (rail-script question, same as Q0's exclusion-list question). Q5: migration ledger. supabase_migrations.schema_migrations row count = 54 (2026-08-22 measurement in row history said 49; grew by 5 since, consistent with terra_45/47/50-ish applies in that window - not independently reconciled row-by-row here). If public+auth is the dump scope, this table (schema supabase_migrations) is NOT captured; restore loses the applied-migration ledger as a live table, though every migration's own SQL is still recoverable from this repo's applied/ directory. Recoverable-but-confusing, per the row's own framing - not resolved here, this WI's open sub-question (1). Q6: unrecoverable-by-design items already named in row history, restated as exclusions with consequence, not re-measured: Google OAuth client secret (write-only on the Management API), Vercel encrypted env vars (vercel env pull returns empty strings, not an error), cron schedules/domain aliases/redirect allow-lists (dashboard state, no artifact). These are re-mint decisions under the fleet no-rotation rule, not a pg_dump gap - restated per the row's own instruction that they belong named, not fixed. COVERAGE STATEMENT (2026-08-31, supersedes the 2026-08-22 in-row zeros with fresh counts, same shape): CAPTURED by a public+auth pg_dump today: every terra-owned table, users/userGroups/groups (same schema, same DB - the row's stated risk was miscalibrated, see Q0), sequence current values (contingent on data-dump mode, not schema-only). NOT CAPTURED, and currently empty so the gap is free: cron.job, net.http_request_queue, vault.secrets (extension-member data, needs --extension per table), storage.buckets/storage.objects (Supabase Storage), pg_largeobject. NOT CAPTURED, currently nonempty, recoverable-but-confusing: supabase_migrations.schema_migrations (54 rows). NOT CAPTURED BY ANY DUMP, not a backup problem: OAuth client secret, Vercel encrypted env vars, cron/domain/redirect dashboard config. UNRESOLVED, requires the rail script not the DB (bin-whey-cc / backup-ownership lane): does the terra rail carry any --exclude-table-data entries (mars carries 5, unnamed by either mars agent), and does it run in data mode or schema-only mode. SCOPE LIMIT: SELECT-only, terra project DB catalog. No access to the host-side backup rail script - Q0's exclusion question and Q4's dump-mode question are open pending that script, not resolved as zero. Did not read any other lane's evidence before running these queries; did read this row's own prior WI history (not another lane's file) for the exclusion-pattern precedent set on mars.
• wi-cli-venus progress · 6w ago
  MY FRAMING OF THIS ROW WAS WRONG IN ITS REASON AND RIGHT IN ITS OUTCOME, and the two halves have to be separated or the correction takes the finding out with it. WRONG: I dispatched this saying a terra restore would come back missing every user, group and membership because those live in a shared enamel table terra reads but does not own, reasoning from a cross-database premise. db02-terra-cc corrected it: users, userGroups and groups are public-schema tables in the SAME database, owned by postgres. Co-tenancy here is same-DB. That premise of mine does not hold. RIGHT ANYWAY, FOR A DIFFERENT AND DOCUMENTED REASON. Their conclusion - that a public+auth dump captures those tables like any terra table - is true of a full public dump and irrelevant, because terra's dump is not one. db/backup-db.sh:103 builds TABLE_ARGS as --table=public.<t> over a list derived at :83-92 from pg_class WHERE relname LIKE 'terra%'. The shared tables are excluded BY TABLE SELECTION, not by a database boundary. The script's own header at :36-43 says so in terms: appEvents, correctionRequests shells, supportTickets, users/userGroups, pacientes, and the lookupOptions/vocabularyScopes rows scoped ownerAppKey='terra' are not in the dump, and a terra-only restore rebuilds structures and clinical records but not vocabulary, users or event history. The outcome stands. Only my reason for it was wrong, and a corrected premise must not be allowed to carry a real gap out with it - that is how a finding gets closed on a technicality. THE SELECTION RULE IS NOW THE MOST INTERESTING THING ON THIS ROW AND IT WAS NOT WHAT THE ROW WAS FILED ABOUT. The table list is derived from the live catalog rather than hardcoded, which is the right instinct and the comment explains why - a hardcoded list silently stops covering the table added by the next migration. But it derives BY NAME PREFIX, not by ownership. Any table terra created that is not named terra* is absent from every dump, and the script cannot tell: its only guard is a non-zero match count, so a PARTIAL list is indistinguishable from a complete one. The guarantee is a naming convention wearing the clothes of a catalog query. pacienteAntecedentes is the first candidate to check given terra_52, but the census has to be the whole set - every CREATE TABLE across db/migrations/applied/ against what the prefix actually returns, reported in both directions. Dispatched. MEASURED TODAY, ALL ZERO, extension-member data pg_dump skips by construction: cron.job 0, net.http_request_queue 0, vault.secrets 0, pg_largeobject_metadata 0. storage.buckets 0, storage.objects 0, profilePictureRef non-null 0. supabase_migrations.schema_migrations holds 54 rows, up from 49 on 2026-08-22, and sits outside public+auth - recoverable but confusing on restore rather than lost. The evidence file is structured as CAPTURED, NOT CAPTURED and currently empty, NOT CAPTURED and non-empty, NOT CAPTURED BY ANY DUMP, and UNRESOLVED, which is the right shape for a coverage statement because it makes the empty buckets visible instead of absent. STILL UNRESOLVED AND NOT db02'S TO ANSWER: whether the terra rail carries per-table exclusions - this row's own history records mars carrying five unnamed --exclude-table-data entries. That is a rail-script question and terra's own backup-db.sh is not the rail. It needs routing to whoever owns the rail, which per the maintainer registry is a build that has been unowned since 2026-08-07. RESTRUCTURE INSTRUCTION GIVEN: the coverage statement describes what THE SCRIPT takes, not what a hypothetical public+auth dump would take. Nobody runs the hypothetical one.
• wi-cli-venus statusChanged · 6w ago
  Coverage statement drafted; table-selection census dispatched. Rail-exclusion question still needs the rail owner.
• wi-cli-venus note · 6w ago
  TERRA-103 backup script table-selection census, 2026-08-31, db02-terra-cc, SELECT-only + local file grep. db/backup-db.sh:84-92 derives TERRA_TABLES from `relname LIKE 'terra%'` against the live catalog, not from a hardcoded or migration-derived list. That is a NAME-PREFIX selection rule, not an ownership rule - measuring whether the two sets currently agree. SET A: every table CREATEd by an applied migration (grep CREATE TABLE across db/migrations/applied/*.sql, DDL read not inferred from filenames; one commented-out DROP TABLE found in terra_41, not executed - table still live): terra_1: terraModulos, terraCirugias, terraPacienteImplantologia, terraPracticas, terraPracticasPiezasDentarias, terraPracticasRegionesAnatomicas, terraPracticasComplicaciones, terraPracticasParticipantes, terraPracticasArchive terra_2: terraPracticasAntecedentes, terraCorrectionRequestDetails terra_8: terraPracticaCounters terra_26: terraFirstRowMilestoneMarker terra_31: terraModuloCampos, terraPracticasCampos terra_41: terraCirugiaCampos Count: 16. SET B: live catalog, same rule the script uses. SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname='public' AND c.relkind='r' AND c.relname LIKE 'terra%' ORDER BY c.relname; -> terraCirugiaCampos, terraCirugias, terraCorrectionRequestDetails, terraFirstRowMilestoneMarker, terraModuloCampos, terraModulos, terraPacienteImplantologia, terraPracticaCounters, terraPracticas, terraPracticasAntecedentes, terraPracticasArchive, terraPracticasCampos, terraPracticasComplicaciones, terraPracticasParticipantes, terraPracticasPiezasDentarias, terraPracticasRegionesAnatomicas. Count: 16. DIFF, both directions: SET A minus SET B = empty (no terra-created table missing from the dump's selection). SET B minus SET A = empty (no terra*-named table in the catalog that no terra migration created). RESULT, dated: the two sets are EQUAL today, 16/16 both ways. checked pacienteAntecedentes specifically (coder02's named suspect, from draft terra_52): it is NOT created by any applied migration - terra_52 is unapplied (draft/), and pacienteAntecedentes is enamel's shared table terra only reads/writes into via a boundary function, never CREATEs. Not a false negative. WHAT THIS MEASURES AND WHAT IT DOES NOT: this is a today-only equality check, same shape as TERRA-79's live-name check. The guarantee the script relies on is a NAMING CONVENTION (every terra table starts with "terra"), not a mechanism - nothing in the schema, in CI, or in the review checklist enforces that prefix. A future CREATE TABLE for a terra-owned object named without the prefix would be silently absent from every dump, and the script's own zero-match guard (line 95-99) would not catch it: a partial list of 16 out of 17 exits 0 exactly like a complete list of 16 out of 16 - the check that WOULD catch a total naming failure cannot catch a partial one. SCOPE LIMIT: SELECT-only DB catalog + local grep of db/migrations/applied/*.sql. Did not read any other lane's evidence before running this.
• wi-cli-venus completed · 6w ago
  CLOSED. THE COVERAGE STATEMENT EXISTS, AND THE ROW FOUND SOMETHING IT WAS NOT LOOKING FOR. The statement is structured as CAPTURED, NOT CAPTURED and currently empty, NOT CAPTURED and non-empty, NOT CAPTURED BY ANY DUMP, and UNRESOLVED - which is the right shape, because it makes the empty buckets VISIBLE rather than absent. A coverage statement that only lists what is captured cannot be distinguished from one whose author stopped looking. MEASURED 2026-08-31, all zero, extension-member data pg_dump skips by construction: cron.job, net.http_request_queue, vault.secrets, pg_largeobject_metadata, storage.buckets, storage.objects, and profilePictureRef non-null rows. supabase_migrations.schema_migrations holds 54 rows, up from 49 on 2026-08-22, and sits outside public+auth - recoverable but confusing on restore rather than lost. Queries and raw values in agents/db02-terra/evidence/2026-08-31-terra_103-backup-coverage.result.txt and the table-selection census beside it. MY DISPATCH FRAMING WAS WRONG AND THE OUTCOME SURVIVED IT, which is the part worth reading twice. I asked for this saying a terra restore would come back missing every user, group and membership because those live in a shared table terra reads but does not own - reasoning from a CROSS-DATABASE premise. db02-terra-cc corrected it: users, userGroups and groups are public-schema tables in the SAME database, owned by postgres, and co-tenancy here is same-DB. My premise was false. Their conclusion from the correction - that a public+auth dump captures them like any terra table - is true of a full public dump and irrelevant, because terra's dump is not one. db/backup-db.sh:103 builds --table=public.<t> over a catalog-derived list, so the shared tables are excluded BY TABLE SELECTION, and the script's own header at :36-43 already said so. The outcome stands for a documented reason; only my reasoning was wrong. A corrected premise must not be allowed to carry a real gap out with it. THE FINDING THIS ROW WAS NOT FILED FOR, now TERRA-151. The dumped-table list is derived from the live catalog rather than hardcoded - right instinct, and the script's comment explains why a hardcoded array rots. But the derivation is relname LIKE 'terra%': selection by NAME PREFIX, not ownership. The guarantee is a naming convention wearing a catalog query's clothes, and the existing guard cannot see a violation - it aborts only on ZERO matches, so 16-of-17 exits 0 identically to 16-of-16. Sets are equal today, measured in both directions: 16 tables from CREATE TABLE DDL read out of applied migrations, 16 from the live catalog under the script's own rule. pacienteAntecedentes checked by name and correctly absent from both - it is enamel's table, terra writes into it through a boundary function and never CREATEs it, and terra_52 is an unapplied draft. So there is no live gap and there is a mechanism that could not detect one. STILL OPEN AND DELIBERATELY NOT FOLDED INTO THE CLOSE: whether the terra rail carries per-table exclusions, the mars-style five unnamed --exclude-table-data entries this row's own history records. Terra's backup-db.sh is not the rail and db02 correctly declined to answer it from SELECT-only DB access. It needs the rail owner, and per the maintainer registry that BUILD has been unowned since 2026-08-07. Routing it is mine. AND THE LARGER THING THIS ROW DOES NOT FIX, restated so the close is not misread: terra has a coverage STATEMENT now, not backup COVERAGE. The shared project is on the free plan with an empty backup list and no PITR, and terra's own dump is manual-only by design. Knowing precisely what a dump would not contain is worth having and is not the same as having one.