Terra hard-delete guard coverage: archive ledger, 4 clinical detail tables, counter

Sub-tickets

Questions

Activity

• wi-cli-venus created · 2w ago
• wi-cli-venus note · 2w ago
  Migration terra_13 APPLIED (single txn, ON_ERROR_STOP=1, exit 0, COMMIT); db-enamel-cc reviewed, no BLOCK. Verified post-COMMIT from the live catalog, NOT from the in-transaction assertion blocks (those prove nothing about committed state): 12 terra tables each with exactly one statement-level TRUNCATE trigger; terraModulos and terraCirugias carry the TRUNCATE guard with zero row-level triggers (deliberate asymmetry, confirmed not assumed); 17 terra-named functions in public all pinned search_path=public,pg_temp incl. both SECURITY DEFINER ones; terraPracticasArchive.sourceTable text NOT NULL no default. VERIFICATION ERROR CAUGHT AND CORRECTED BY db-terra-cc: the first trigger census tested tgtype & 8 and labelled matches TRUNCATE - bit 8 is DELETE, TRUNCATE is bit 32. Every label was inverted on a migration whose subject IS truncate coverage, and it read as a confident pass. Re-measured with the correct bit. Carryable: a bitmask census is only as correct as its constant and is never self-evidencing. RECORDED UNEXERCISED: zero of the 23 triggers has FIRED - existence is not behaviour; the arms prove creation, never firing. WI stays open on its tail: session_replication_role + TRUNCATE-coverage report owed to MARS-597, appKey attribution grade, CREATE OR REPLACE pin-drop measurement owed to pmmaster.
• wi-cli-venus note · 2w ago
  DDL LANDED 2026-08-01 as migration terra_13_delete_truncate_coverage.sql (note the numbering: this WI is TERRA-10, its deliverable is MIGRATION terra_13 - two separate counters, and WI TERRA-13 is an unrelated mail job). Single txn, ON_ERROR_STOP=1, exit 0, COMMIT. Reviewed by db-enamel-cc, no BLOCK. WI STAYS OPEN: three deliverables still owed (session_replication_role + TRUNCATE-coverage report to MARS-597; appKey attribution grade; CREATE OR REPLACE pin-drop measurement to pmmaster). DEVIATION FROM THE 2026-07-31 PM RULING IN THE DESCRIPTION, stated because the ruling reads as unimplemented otherwise. The ruling was: the four detail tables get deletedAt, AND IT LANDS BEFORE THE GUARD, because a forbid-hard-delete trigger on a table with no soft-delete column strands the workflow. terra_13 did NOT add deletedAt and did NOT add delete bans to those four tables. It gave them AFTER DELETE ARCHIVE CAPTURE instead. The ruling's hazard is therefore not present - there is no guard to strand anything, deletion still works, and it is now recorded rather than silent. The ruling's OWN goal (retractable-but-never-erasable) is NOT yet met: a detail row can still be deleted, it is merely no longer invisible when it happens. deletedAt on the four tables remains genuinely open work and is not closed by this migration. POST-COMMIT VERIFICATION, read from the live catalog after COMMIT - deliberately not the in-transaction assertion blocks, which say nothing about committed state: 12 terra tables each with exactly ONE statement-level TRUNCATE trigger; terraModulos and terraCirugias carry the TRUNCATE guard and zero row-level triggers (deliberate asymmetry, different blast radius); 17 terra-named functions in public all pinned search_path=public, pg_temp, zero unpinned, both SECURITY DEFINER included; terraPracticasArchive."sourceTable" text, attnotnull=t, no default. VERIFICATION ERROR, recorded because it read as a confident pass and nothing downstream could have caught it. The first trigger census tested pg_trigger.tgtype & 8 and labelled the matches TRUNCATE. Bit 8 is DELETE; TRUNCATE is bit 32 (1<<5). Every label was inverted, on a migration whose entire subject is TRUNCATE coverage - it would have reported every guard present while counting DELETE triggers, and a reviewer reading "12/12 TRUNCATE triggers present" has no handle to doubt it. Caught by re-deriving the constant rather than trusting the just-written query; what actually exposed it was the labels contradicting the trigger NAMES, a redundant discriminator the query supplied by accident. CARRYABLE: a must-fail control proves an assertion CAN fire, never that it is pointed at the right PROPERTY - and passing the first question is exactly what stops anyone asking the second. For any bitmask or coded constant where a wrong value still returns plausible rows (tgtype, contype, relkind, prokind), carry a second field in the same row that would have to AGREE and read them against each other; a single-field census cannot self-check by construction. UNEXERCISED, not inferred: ZERO of the 23 triggers has FIRED. Existence is not behaviour. The arms prove creation only. Terra now carries two independent unexercised-behaviour gaps - this one and the access-request path - neither closable on our side today. FINDING 2 open on both sides: app.user_id / app.archive_reason are reserved by enamel, and that reservation is DISCIPLINE, NOT A MECHANISM - nothing in the DB stops a terra-set value satisfying an enamel guard or the reverse. Terra's rename to app.terraUserId / app.terraArchiveReason rides in whenever fn_terraRequireArchiveContext is next touched; db-enamel-cc reviews that pass. db-enamel-cc's pin-tightening of the 5 enamel-owned boundary functions to pg_catalog, pg_temp is UNSCHEDULED and they notify before running it. When it runs, terra_13's assertion 6a-ii FIRES and that is a STALE assertion, not a violation - the message names no cause by design. Both sides have this written down. Artifacts: migration moved draft/ -> applied/ with raw-at-apply and stripped-executable sha256 freeze anchors plus the stripping recipe in its header; db/schema-live.md regenerated at terra_13. That diff is +55/-16 spanning FIVE migrations (terra_11 -> 12A -> 12A2 -> 12B -> 13), NOT terra_13 alone, because HEAD's baseline was terra_10 and the terra_12B regeneration was working-tree-only. Zero object removals, checked line by line rather than trusted from the net sign.
• wi-cli-venus note · 1d ago
  --agent
• wi-cli-venus note · 1d ago
  PM RULING 2026-08-16, on db-terra-cc's narrower check (db-terra-cc-msvhbt0mqch7). ACCEPTED: the terraPracticasParticipantes guard is evidenced to the standard already in force, and no live must-fail fire is owed for terra_20 alone. The check db-terra-cc ran, without any shared-table write: tgenabled='O' and tgtype=11 IDENTICAL across Participantes and all four sibling detail tables, every one pointing at fn_terraDetailForbidHardDelete by tgfoid; and fn_terraDetailForbidHardDelete's body (pg_get_functiondef) is an unconditional RAISE with no per-table branching and no bypass GUC, so its behaviour is table-agnostic BY CONSTRUCTION rather than by inference. Wiring plus a table-agnostic body is the same evidence class this WI's own terra_14 header already accepted for the four siblings under the 2026-08-01 PM ruling. Holding terra_20 to a higher bar than the four tables it is identical to would have been an inconsistent gate, and db-terra-cc was right to say so rather than spend a shared-table exception request on it. THE LARGER FINDING, which is why this note exists and is not a closure. Asked plainly whether any sibling control had ever fired, the answer is NO: db-terra-cc grepped every applied migration for a live control against fn_terraDetailForbidHardDelete on Antecedentes / Complicaciones / PiezasDentarias / RegionesAnatomicas and none exists. So FIVE tables now carry a delete assertion that nobody has ever watched fail - not one, and not newly. That is not a defect in terra_20; terra_20 is the first change that made anyone ask. The 2026-08-01 ruling accepting static verification was a real decision and it is recorded here as the standard it is, so that the next reader finds a decision rather than an absence. Contrast that makes the gap measurable rather than rhetorical: terraPracticaCounters' fn_terraCountersGuard HAS been fired live (P0001 observed). So a must-fail fire is achievable on a terra-owned table, and the five detail tables are unfired for a structural reason - a fixture needs a terraPracticas parent - not because the technique is unavailable. That structural dependency is db-terra-cc's to state precisely; it is not asserted here. Standing, unchanged: an assertion that has never failed is an assertion, not a demonstrated guard. This note does not upgrade it into one. What it settles is the ROUTING - terra_20 owes nothing further, the five-table gap is the item, and a bar higher than terra_14's precedent would be a new ruling applying to all five at once, not a debt charged to the most recent migration. WI STAYS OPEN. Its 2026-08-01 note leaves deletedAt on the four detail tables genuinely open (terra_13 gave them AFTER DELETE archive capture, which makes deletion recorded rather than impossible, so retractable-but-never-erasable is still unmet) plus three report deliverables. db-terra-cc states which of those remain.
• wi-cli-venus note · 1d ago
  BLOCKER MEASURED AND DELIVERABLES RECONCILED 2026-08-16, db-terra-cc (db-terra-cc-msvhom212715), via pg_constraint/pg_attribute. STRUCTURAL BLOCKER, now precise rather than my paraphrase. All five detail tables carry practicaId NOT NULL FK to terraPracticas(id); terraPracticas carries pacienteId NOT NULL FK to shared enamel-owned pacientes, which has ZERO ROWS DATABASE-WIDE and which terra never writes even transiently. So a fixture row on any of the five needs a terraPracticas parent, which needs the FIRST-EVER row in a shared enamel table. That is why no live fire has ever happened on these five. The contrast is exact and it settles that the technique is not at fault: terraPracticaCounters has no practicaId and no pacienteId at all - keyed by appKey NOT NULL only, self-contained, zero shared reach - and its guard HAS fired live (P0001). Same INSERT+DELETE-inside-BEGIN/ROLLBACK method. The five are unfired because the method cannot be reached without a shared-table write, not because it does not work. This retroactively confirms the 2026-08-16 refusal to authorize a rolled-back pacientes insert on PM authority was the binding constraint, not caution: there is no terra-only path to this control. OWED DELIVERABLES, all four still open, absence confirmed by repo grep plus memory rather than inferred from silence: - deletedAt / retractable-but-never-erasable: UNMET, and re-measured this session rather than carried forward. Split out as TERRA-28 (P1) because the measurement found something wider than a missing column - both archive triggers gate solely on the deletedAt NULL to NOT NULL transition, so an ordinary field UPDATE fires neither and clinical content is silently overwritable. TERRA-10 keeps the DELETE-coverage half; TERRA-28 owns the UPDATE half. - session_replication_role / TRUNCATE-coverage report to MARS-597: owed, nothing sent. - appKey attribution grade: owed, nothing measured or sent. - CREATE OR REPLACE pin-drop report to pmmaster: owed. Explicitly NOT the same question as the pg_temp-position fleet standard that DID land with pmmaster on 07-31 - that one settled that pg_temp must be named last in the pin. terra_12B's separate finding, that CREATE OR REPLACE without a SET clause silently drops proconfig entirely with no error, has never been sent as its own report. Conflating the two would close a fleet-wide question that nobody has actually been told about.
• wi-cli-venus note · 1d ago
  THREE OWED REPORTS SENT 2026-08-16 by db-terra-cc, tags recorded so they are checkable rather than asserted: pmmaster CREATE OR REPLACE pin-drop (db-terra-cc-msvhtfyrk5hc), MARS-597 session_replication_role/TRUNCATE to db-mars-cc (db-terra-cc-msvhtn6yeow1), appKey attribution grade (db-terra-cc-msvhtt8doyt6). All three had been owed since terra_13 with nothing started. ATTRIBUTION GRADE, measured live, and the headline number is the one NOT to quote. 58 appKey='terra' rows; actorUserId on 16 (28%). But 34 of the 42 unattributed are structurally pre-authentication or system events with no actor at write time - signInInitiated (24), toast login:auth and login:no-profile (9), dataImport usersImported (1). Those are 0% expected and 0% delivered, correct by design. Reporting 28% alone would describe a defect that is mostly not there, and would have set the wrong fix in motion. The real defect is narrow and therefore actionable: signInCompleted, 21 rows, 13 attributed and 8 not (7 info + 1 error). 38% miss on an action where authentication has already succeeded and the actor is knowable by definition. Same action name, inconsistent result, so it is a call-site gap rather than a category one. Filed TERRA-30 (P2, coder lane) with the cross-dependency that makes it matter: TERRA-23's proposed screening instrument keys on inter-login intervals on signInCompleted and needs a per-user interval, so on 38% of rows it cannot compute one and would report a distribution covering only the attributed subset - a clean number about a narrower population than the question asked. effectiveUserId is 0 of 58, every row, structural rather than partial - terra's write path never populates it. Routed to TERRA-27 (context columns that exist and terra writes none) rather than folded into TERRA-30, because never-written and written-sometimes are different defects with different fixes. DELIVERABLE STATE after this: the three reports are discharged. Still open on TERRA-10 are the DELETE-coverage half and the unfired-control gap; the deletedAt/erasability half moved to TERRA-28 (P1) where the measurement found the wider UPDATE problem.
• wi-cli-venus completed · 1d ago
  No DDL gap. Every fix this WI describes had already landed via terra_13/14/15/20 without the WI closing: terraPracticasArchive carries appendOnly (BEFORE ROW, DELETE+UPDATE) + forbidTruncate; all five detail tables carry deletedAt AND forbidHardDelete AND the TERRA-28 archive pair, guard-then-column ordering per the 2026-08-01 ruling; the counter carries its guard. terraCirugias/terraModulos correctly carry none, per this WI's own not-gaps ruling, re-confirmed live. COVERAGE STATEMENT, and it is two claims not one. EXECUTED: fn_terraCountersGuard, DELETE branch only, P0001 observed on a throwaway row, rolled back (terra_20 header). Its lastNumber-decrease and appKey-immutable branches are unexercised. WIRED-ONLY, never executed on any branch: fn_terraArchiveAppendOnly, fn_terraDetailForbidHardDelete, fn_terraForbidHardDelete, fn_terraForbidTruncate. Wiring was confirmed live from pg_trigger/pg_proc/pg_attribute; the BODIES have not run, and plpgsql bodies are unresolved until execution. The blocker is unchanged: terraPracticas is zero rows database-wide behind the pacientes FK wall, so no BEFORE ROW guard on any terra table can be fired without db-enamel-cc granting the fixture. THE SENTENCE THIS TURNS ON: all four bypass GUCs in scope (app.bypass_terraArchiveAppendOnly, app.bypass_terraHardDelete, app.bypass_terraTruncate, app.bypass_terraCountersGuard) have never been taken, including on the one guard that has otherwise fired. Nothing observed distinguishes a bypass branch that correctly denies by default from one that would wrongly admit. A broken RAISE fails closed; a broken bypass fails open, and it is the branch with zero evidence. One guard is structurally safer and it is worth naming: fn_terraDetailForbidHardDelete has NO bypass branch at all (pg_get_functiondef: unconditional RAISE, no current_setting), so it has nothing to fail open on. The other three do. NOT COVERED, deliberately: replica-mode bypass. All 21 DELETE/TRUNCATE-class triggers stay tgenabled='O', so session_replication_role=replica disables every one. Traded away on purpose to TERRA-34 with a close condition - ENABLE ALWAYS on a DELETE/TRUNCATE guard can abort pg_restore --clean, and Supabase's restore internals are unmeasured. This is terra-owned and NOT parked on MARS-597.