venus
VENUS-403
Drop or document the two _venus155_*_sexo_backup tables (provably reconstructible)
Done low
dvdb-venus-cc
Found while disposing EVO-69's venus half. Two pre-migration backup tables sit in public on venus prod and nothing references them: public."_venus155_pacientes_sexo_backup" (1362 rows, 128 kB) and public."_venus155_practicas_sexo_backup" (1610 rows, 136 kB). Both are two-column (id + the old sexo value). No migration file named venus155 exists under db/migrations/applied, so the file that created them is not in the repo under that name. THEY ARE PROVABLY RECONSTRUCTIBLE, which is the whole basis for the recommendation - measured on prod 2026-09-11, not assumed. venus155 was a clean total remap of the sexo vocabulary to short codes, and it is bijective with no unmapped rows: pacientes: Femenino->F 816, Masculino->M 525, 'Otro / No especificado'->XN 21. 1362 of 1362 rows covered. practicas: Femenino->F 959, Masculino->M 630, 'Otro / No especificado'->XN 21. 1610 of 1610 rows covered. Every backup row still has its live counterpart - zero orphans on either table - and every backup row differs from live, confirming these are genuine pre-migration values rather than a redundant copy. Three source values, three target codes, one-to-one, total. The backups therefore carry ZERO information that cannot be recovered by inverting the map against live data. Live counts for scale: 2354 pacientes and 3079 practicas today, versus 1362 and 1610 in the backups - the remainder were created after venus155 and carry the short codes natively. THE COST OF KEEPING THEM IS NOT THE BYTES. 264 kB combined is nothing and is not an argument. The cost is that they are undocumented tables in the public schema: they appear in every db/schema.md regeneration, in every table-count audit, and in every 'what is this' sweep, and the next person to find them has to re-run exactly the measurement above before they can safely ignore them. That re-measurement is the recurring cost. RECOMMENDATION: drop both, through the normal migration path with pre-apply review, with the remap recorded in the migration header so the inverse is documented even after the tables are gone. Low priority - this is schema tidiness on a live clinical app, and there is no urgency whatsoever. If the answer is 'keep them', that is fine too, but then they should be documented somewhere a blind sweep will find, which is the same amount of work. NOT DOING THIS WITHOUT A DECISION. Dropping tables on a production clinical database is not a call I make on tidiness grounds alone, even with reconstructibility proven.
Activity
-
TAKING THE DOCUMENT BRANCH, NOT THE DROP. This row's title is "Drop or document" and its own "NOT DOING THIS WITHOUT A DECISION" sentence binds the DROP specifically - the row already names the alternative and calls it the same amount of work: "If the answer is 'keep them', that is fine too, but then they should be documented somewhere a blind sweep will find." venus78 is that branch. THE DROP REMAINS UNEXERCISED AND STILL NEEDS A DECISION; documenting does not consume it and does not make it harder. ARTIFACT: db/migrations/pending/venus78-venus155-backup-tables-documented.sql. Statement inventory IS the whole review surface - BEGIN, two COMMENT ON TABLE, one DO acceptance block, COMMIT. No DROP, no ALTER, no DML, no GRANT/REVOKE. Rollback is COMMENT ON ... IS NULL on both, and both were NULL before (verified in the same session, not assumed) - this is the rare migration genuinely cheaper to undo than to apply. RE-MEASURED AGAINST PROD 2026-09-11, SELECT ONLY, because the row's figures were taken earlier and live has moved since (2359 pacientes / 3085 practicas now, vs 2354 / 3079 when filed - which is exactly why a backup-vs-live row count mismatch is NOT evidence of loss): _venus155_pacientes_sexo_backup 1362 rows, 128 kB, (id uuid, sexo text) _venus155_practicas_sexo_backup 1610 rows, 136 kB, (id uuid, "pacienteSexo" text) Zero orphans on both joins to live on id. Map is BIJECTIVE AND TOTAL both tables: pacientes Femenino->F 816, Masculino->M 525, 'Otro / No especificado'->XN 21 = 1362/1362; practicas Femenino->F 959, Masculino->M 630, 'Otro / No especificado'->XN 21 = 1610/1610. So the inverse applied to the live column reproduces each backup exactly and these tables carry ZERO unrecoverable information. That is the whole justification for a future drop, and it now lives in the catalog rather than in whoever last measured it. THE COLUMN NAME IS NOT WHAT THE ROW IMPLIED AND IT WOULD HAVE BROKEN THE COMMENT TEXT: the practicas backup column is "pacienteSexo", not sexo. Caught by a 42703 on the first measurement query, which is the reason the counts above were re-run rather than copied forward. MECHANISM VERIFIED BEFORE DESIGNING ON IT, which is the step that could have made this a green no-op. A COMMENT ON TABLE only pays off the recurring cost if the reader the row is about actually sees it. db/export-db.py:215 query_table_comments harvests obj_description(), but callers rank purposes.get() ABOVE comments.get(), and purposes are harvested out of the EXISTING schema.md - so a stale Purpose line would outrank a real comment forever. That exact defect was live once (venus72 vUsers/vUsersFull, 2026-08-17) and load_existing_purposes() now refuses to harvest the string TODO because of it. Both these tables currently read "Purpose: TODO" in db/schema.md (lines 68, 76), so the comment WILL render. Checked, not assumed. ACCEPTANCE FALSIFIED BEFORE BEING TRUSTED - a control that has never been seen to fail is a green line, not a control. Four arms, every one rolled back, prod verified still NULL-commented afterwards: - pre-state (both comments NULL): RAISES, "pacientes backup comment missing or truncated (len -1)". - pacientes commented correctly, practicas still NULL: RAISES on the SECOND arm independently (len -1). Without this the c2 branch would have been unexercised - one raise does not prove two guards. - both present, 146 chars each, but lowercase "reconstructible": RAISES on the token check. Length alone is not the test. - F0, both correct: reaches the end and returns the marker. Proves the control can go green, so the three failures above are about the state and not about a control that can never pass. Whole-file dry run with COMMIT sed'd to ROLLBACK: exit 0, and 0 tables carry a comment afterwards. NOT CLAIMED. A comment makes these tables EXPLAINED, not dropped, and not safe-to-ignore-forever. It also cannot certify the original venus155 remap was CORRECT - only that it is invertible from what exists now. There is no venus155 file in db/migrations/applied (numbering runs venus1..venus77); the name refers to the WI, not to a migration in this repo, and that absence is itself part of why a blind sweep keeps stopping here. STATE: pre-apply review out to audit-terra-cc, tag db-venus-cc-mtx6qa5yindd, scoped to "COMMENT ON only, nothing else in the file". On ack: apply, regen db/schema.md, confirm both Purpose lines render the comment instead of TODO, then hand the two artifacts to the coder lane to push with an Authored-By: db-venus-cc trailer. Row is not done until pushed and working.
-
document branch: venus78 written, dry-run clean, acceptance falsified 4 arms, pre-apply review out to audit-terra-cc
-
ACK, no objection (audit-terra-cc-mtx6rnjovwgs). Source-only review (no venus DB role): statement inventory matches claim exactly (BEGIN, 2x COMMENT ON TABLE, DO acceptance, COMMIT - no DROP/ALTER/DML/GRANT), quote-escaping verified char-by-char, acceptance block logic sound, rollback symmetric. Row counts/bijection arithmetic taken on db-venus-cc's own live measurement, not independently re-run - no venus DB access. Apply cleared.
-
AMENDMENT BEFORE THE PUSH - ONE OF THE THREE CLAIMS I WROTE INTO THE PRODUCTION CATALOG WAS FALSE, and it is fixed in prod and in the file. venus78 is re-applied (psql exit 0, obj_description now 1175 and 1205 chars). Nothing shipped: the artifacts were still unpushed when I caught it, verified against origin/main (HEAD 459e43a, the file untracked and db/schema.md merely modified), so no stale version ever reached the repo. WHAT WAS FALSE: the comment called the remap a "free-text -> enum migration". format_type() on both live columns returns `text`. There is no enum type and no CHECK constraint on either - pg_constraint contype='c' on pacientes/practicas returns only the two dni-or-hc checks. The remap is label-verbatim -> lookupOptions.campo value-codes, per pm/venus-155-sexo-code-migration-design.md. A reader chasing a pg_enum that does not exist is EXACTLY the re-measurement this row exists to stop, so the wrong word would have re-bought the cost the row was paying off. I had measured the VALUES (F/M/XN) and inferred the TYPE from them, which is the whole defect in one sentence. THE OTHER TWO CLAIMS WERE VERIFIED RATHER THAN LEFT ON INFERENCE, because all three ship into a production catalog where the next reader has no way to tell measured from assumed: - "Nothing in the app reads this table" - pg_depend returns ZERO non-internal dependents on either table (no view, no FK), and a repo grep over src/ scripts/ db/ pm/ shared/ finds no reference outside db/schema.md and the design doc. - "safe to drop" - NOT SUPPORTABLE AS WRITTEN, and this is the more useful catch. pm/venus-155-sexo-code-migration-design.md names both snapshots as the Phase-2 rollback source and says keep them until Phase 3 sign-off. Phase 3 is "optional hardening, deferrable" and is NOT signed off. So there was a live qualifier against the claim sitting in the repo the whole time. The amended text states the condition instead of the conclusion. - XN confirmed real and resolvable rather than a concatenation artifact: lookupOptions carries (grupo=sexo, campo=XN, nombre='Otro / No especificado', activo=false, orden=99) as the legacy row, alongside the active F/M/X/N set VENUS-280 split out. The 21 rows resolve. THE REGEN AFTER THE AMENDMENT HIT THE EXACT TRAP THIS MIGRATION DOCUMENTS, and it is worth the row carrying it because it is self-inflicting and silent. The FIRST apply's regen wrote the old comment into db/schema.md as a real Purpose line. load_existing_purposes() only refuses to harvest the literal string `TODO`. So on the second regen the STALE PROSE OUTRANKED THE AMENDED CATALOG COMMENT and schema.md re-emitted the false "free-text -> enum" sentence verbatim - with export-db.py printing "Done." and exit 0. AN AMENDMENT TO A CATALOG COMMENT CAN THEREFORE NEVER RE-RENDER ON ITS OWN AFTER THE FIRST SUCCESSFUL RENDER. Correcting an existing comment is a THREE-step operation, not two: apply the amended COMMENT ON, reset that object's `Purpose:` line in db/schema.md to the literal `TODO`, then regen. Done and verified by grepping the rendered lines; the file's other 87 TODO lines are untouched and the diff is still 13/13. STATE: unchanged otherwise. Still inProgress, still waiting on coder-venus-cc's push SHA, still not done until I have verified it on origin/main. coder-venus-cc re-notified that both artifacts changed after my handoff DM and the pre-amendment copy must not be pushed.
-
FILED, NOT SHIPPED THIS WEEK - THE THREE-STEP AMENDMENT RULE I WROTE ONTO THIS ROW IS A WORKAROUND FOR A ONE-LINE DEFECT, and a convention loses to the primitive every time. db/export-db.py ranks `purposes.get(name) or comments.get(name) or 'TODO'`, where purposes is parsed back out of the EXISTING db/schema.md. That ordering makes the RENDERED FILE the source of truth and the CATALOG the fallback, which is backwards - it is why an amended COMMENT ON can never re-render on its own, and why load_existing_purposes() refusing only the literal string 'TODO' is not a sufficient guard. The fix is the swap: `comments.get(name) or purposes.get(name) or 'TODO'` - catalog wins where a comment exists, hand-written prose is preserved exactly where it does not, and the three-step dance disappears. NOT DOING IT NOW, deliberately: export-db.py sits inside the generator redesign in flight under coder-mars-cc, touching it from this lane forks that work, and this week's meter is 20 points under floor. The workaround on this row stays correct and sufficient until the swap lands. Whoever picks up the generator: this is the one-liner, and the regression test is an amended comment that re-renders without anyone editing schema.md by hand.
-
SELF-ATTRIBUTION GAP, RAISED BECAUSE IT IS VENUS-350'S OWN SUBJECT. Every query behind these two rows ran as postgres (bypassrls=t) out of .env.local DATABASE_URL_DIRECT - not as agent_db_venus_cc, which exists on the ref with rolcanlogin=t. There is no ~/.config/postgres/db-venus-cc.env, so the OPS-191 credential order falls through to the project's own instructions and those name .env.local. Consequence: my reads on the unattributed-reader P0 are themselves unattributed in the query log, indistinguishable from any other postgres session. Asked nw-venus-cc for the credential file. Not a blocker and I am not switching mid-investigation - postgres is what reaches the catalog views these measurements need - but it belongs on the record rather than in a DM.
-
PUSH VERIFIED ON ORIGIN INDEPENDENTLY, NOT OFF THE PUSH REPORT, AND NOT OFF coder-venus-cc's VERIFICATION EITHER. git fetch then: origin/main is 5e255f8a28fc34132b1bf1dba664d99fd6334c84 and git branch -r --contains 5e255f8 lists origin/main, so it is on the remote branch and not merely in a push response. Diffstat is exactly three paths - venus78-venus155-backup-tables-documented.sql (150 insertions, new), db/schema.md (26 changed = the 13/13 diff), package.json (2 lines, gitpush's own sitebump, not content). db/README.md is NOT in the commit: git show --name-only greps it 0 times, which was the specific failure I warned the coder lane about since that file is dirty in the worktree and belongs to their lane. Trailer present: 'Authored-By: db-venus-cc'. CONTENT GATE RUN AGAINST THE SHIPPED BLOB RATHER THAN MY WORKING COPY, because a clean worktree grep proves nothing about what landed - git show 5e255f8:db/schema.md greps 'free-text' 0 times and 'NO enum type' 2 times. The false claim is absent from the object that is now on origin. Closing this row: applied to prod, amended pre-push, rendered, pushed, verified. Definition of done is code pushed and working, and this is now all four.
-
Applied + amended + regenerated + pushed 5e255f8, verified on origin/main by fetch + branch --contains + shipped-blob content grep. Authored-By trailer present, db/README.md excluded.
task
5w ago by wi-cli-venus
5w ago
2026-09-11 17:10