DB: mandate createdAt insert-timestamp fleet-wide (updatedAt recommended)

Sub-tickets — 1/1 done

Activity

• wi-cli-venus created · 2026-07-19
• wi-cli-venus note · 2026-07-19
  Elazar APPROVED + GO 2026-07-19: name-flexibility carve-out accepted. Codifying to evolutiva-db.md via coder-mars-cc; per-app backfill children to file. Elazar sub-ask: confirm the live practicas + grades tables have createdAt (he saw studentExamScoresArchive + practicasIntegrityInventory in mars's 8-gap list and read them as the practicas/grades tables). Per mars's report those are the archive-mirror (archivedAt + mirrors live createdAt) + an ops-integrity log — live tables have createdAt. Confirming live practicas.createdAt explicitly via db-mars-cc.
• wi-cli-venus note · 2026-07-19
  CODIFIED: coder-mars-cc pushed 32d2773 (evolutiva-db.md new §Insert-Timestamp (createdAt) — Mandatory), all 4 clauses intact. Backfill children: PLUTO-611 filed (systemSettings createdAt) -> db-pluto-cc; venus child pending (15 catalog tables + lookupOptions NOT NULL); mars pending (confirm compliant + live practicas/grades createdAt for Elazar). Elazar re-confirmed approve+go, 'do backfills when possible'.
• wi-cli-venus note · 2026-07-19
  Mars practicas/grades confirm (live information_schema): practicas.createdAt + studentExamScores.createdAt + practicaReviews.createdAt all = timestamptz NOT NULL DEFAULT now(). Elazar's 2 'gap' names were non-domain: studentExamScoresArchive (mirror, archivedAt) + practicasIntegrityInventory (ops log, id+seenAt). Mars VERDICT: compliant, no backfill child. Answered Elazar.
• wi-cli-venus note · 2026-07-19
  PLUTO-611 LANDED: 081d4ce v2.20.17, mig 077, systemSettings.createdAt added NOT NULL DEFAULT now(). Pluto gap closed. EVO-69 remaining: venus backfill child (15 catalog tables + lookupOptions NOT NULL) only. Mars compliant (no child), doc codified 32d2773.
• wi-cli-venus assigned · 5w ago
  db-venus-cc
• wi-cli-venus note · 5w ago
  VENUS DISPOSITION (db-venus-cc, measured live 2026-09-11, venus prod) - venus's half of this fleet mandate is SATISFIED; VENUS-285 closed done. Evidence: zero public base tables carry a nullable or defaultless createdAt (scoped to relkind='r' - an unscoped information_schema query returns 35 v* VIEWS, which structurally cannot hold NOT NULL or a default, and reads as 35 false defects; anyone auditing another ref should scope the same way or they will report noise). 41 of 47 base tables carry createdAt NOT NULL DEFAULT. Of the remaining 6: four carry a domain-named equivalent with the identical property - dbperfSnapshots.capturedAt, practicasArchive.archivedAt, practicaParticipationsArchive.archivedAt, uxPainDigestRelay.deliveredAt, all NOT NULL DEFAULT now() - which is the mandate's substance under a different name, and two are dead venus155 pre-migration backup tables with no timestamp at all, flagged for a retention decision rather than a schema fix. RECOMMENDATION FOR THE FLEET WORDING, take it or leave it: state the mandate as 'an insert-stamped NOT NULL DEFAULT timestamp column' rather than literally 'createdAt', otherwise every ref will either rename evidence-table columns for cosmetics or report a compliant table as non-compliant.
• wi-cli-venus note · 5w ago
  VENUS HALF COMPLETE, INCLUDING THE DOCUMENTATION CLAUSE - db-venus-cc 2026-09-11. Not closing this row; see the last paragraph for why that is not my call. Venus compliance was already measured and recorded here on 2026-09-11 12:07 (41 of 47 base tables carry createdAt NOT NULL DEFAULT; four more carry a domain-named equivalent with the identical property - dbperfSnapshots.capturedAt, practicasArchive.archivedAt, practicaParticipationsArchive.archivedAt, uxPainDigestRelay.deliveredAt; two were pre-migration backup tables). What was NOT checked then is clause 4 of the carve-out, the one that makes the rest usable: 'all such equivalents to be DOCUMENTED in evolutiva-db.md for blind-query discoverability'. CHECKED NOW, and it is satisfied. shared/md/evolutiva-db.md line 38 (§Insert-Timestamp, codified by coder-mars-cc at 32d2773) names the equivalents BY CATEGORY: archive tables archivedAt, snapshots capturedAt, alerts detectedAt, delivery deliveredAt, edit-locks lockedAt, log tables' own insert ts. All four venus equivalents fall under categories already listed - archivedAt twice, capturedAt once, deliveredAt once. No venus-specific addition is needed and none should be made. AND DELIBERATELY SO: the doc should NOT grow a per-table list. A category mapping stays true as tables are added; an enumerated per-table list across four refs drifts the first time anyone adds a snapshot table and forgets the doc, and a drifted discoverability index is worse than a categorical one because it reads as exhaustive. Anyone wanting the venus-specific mapping should query the catalog, which is authoritative and cannot drift. THE TWO REMAINING VENUS TABLES ARE NOT A COMPLIANCE GAP and are now off this row: _venus155_pacientes_sexo_backup and _venus155_practicas_sexo_backup are pre-migration value backups, not domain/transactional/junction/lookup tables, so the mandate does not reach them. Filed as VENUS-403 with the evidence - measured today, the venus155 remap is bijective and total (Femenino->F, Masculino->M, 'Otro / No especificado'->XN; 1362/1362 and 1610/1610 rows covered, zero orphans), so both backups are provably reconstructible from live data. That is a retention/tidiness decision, not a schema-mandate one. MY EARLIER WORDING RECOMMENDATION STANDS and the doc already implements its substance - state the mandate as 'an insert-stamped NOT NULL DEFAULT timestamp column' rather than literally 'createdAt', so no ref renames evidence-table columns for cosmetics or reports a compliant table as non-compliant. FLEET STATE AS I CAN SEE IT: venus satisfied (this note), pluto closed via PLUTO-611 (081d4ce, v2.20.17, migration 077), mars verified compliant with no backfill child, doc codified 32d2773 with all four clauses intact. WHY I AM NOT CLOSING THIS. The mandate's scope line says 'enamel greenfield bakes in' - a forward commitment, not a verified state, and I have not verified it. I also should not: enamel is another app's database and querying it is outside my host/project lane. Closing a fleet row on three of four refs would record a compliance claim for a ref nobody measured, which is precisely the shape this row exists to prevent. Someone who can see enamel - db-enamel-cc - should confirm, or pmmaster-evolutiva-cc should decide the row closes without it. Venus owes nothing further either way.
• wi-cli-venus verified · 5w ago
  enamel (ref eprpywqwzsvrjeaforei) MEASURED 2026-09-11 by db-enamel-cc, scoped to pg_class.relkind='r' per db-venus-cc's warning. 40 base tables, 34 views, 0 matviews - views excluded before counting, so no is_nullable=YES false positives. ALL 40 base tables carry at least one NOT NULL timestamptz with a now() default. 38 of 40 are clean on the intent as well as the letter: 35 via createdAt timestamptz NOT NULL DEFAULT now(), and the domain-named equivalents the carve-out allows - terraPracticasArchive.archivedAt, terraFirstRowMilestoneMarker.firedAt, terraPacienteAntecedenteChanges.changedAt - each already NOT NULL DEFAULT now(), so no redundant second createdAt was added. TWO GENUINE GAPS, narrow and named: supportTicketCounters and terraPracticaCounters have NO createdAt at all; their only NOT-NULL now()-defaulted timestamptz is updatedAt, and both writers overwrite it on every increment - fn_assignTicketNumber and fn_terraAssignPracticaNumber each do ON CONFLICT DO UPDATE SET lastNumber = lastNumber + 1, updatedAt = now(). So those two satisfy the rule as written and do NOT preserve insert time; the column is an update-time column wearing a NOT-NULL now() default. Both are singleton rows (1 each) backing gapless numbering, so forensic value is low and terraPracticaCounters is terra-owned, but reporting them as clean would be false. Enamel is otherwise compliant and the 'greenfield bakes it in' forward commitment on this row is now a measurement rather than a promise.
• wi-cli-venus note · 5w ago
  VENUS RE-AUDITED UNDER db-enamel-cc's TRAP - my earlier venus disposition was shape-only and one of its four claims does not survive. db-venus-cc 2026-09-11. THE TRAP, which is db-enamel-cc's finding and is correct: a column-shape audit cannot tell an insert-time column from an update-time one. Both are timestamptz NOT NULL DEFAULT now(); the default only fires on INSERT, but any later UPDATE ... SET col = now() converts it to a last-touch column while the shape audit keeps reporting compliance. Only the WRITER decides. Enamel's supportTicketCounters and terraPracticaCounters fail exactly this way. My venus notes of 12:07 and 14:1x asserted compliance from column shape alone, so they had to be re-run against writers, not columns. VENUS RE-MEASURED, three instruments instead of one: (1) WRITERS. Zero public functions on venus contain both ON CONFLICT and now() in prosrc - the enamel defect's exact shape does not exist in venus's DB functions. App-side there are three ON CONFLICT ... DO UPDATE sites that stamp now(): practica-actions.ts:1579 (selfRegisteredAt), alumnos-actions.ts:122 (updatedAt), scripts/repair-access-grant.ts:136 (updatedAt). All three are on tables that ALSO carry their own createdAt, so insert time is preserved in a separate column and none of them is this defect. (2) STRUCTURAL PROOF where it exists. practicasArchive and practicaParticipationsArchive each carry an UPDATE+DELETE immutability trigger (fn_archives_immutable, venus66) plus a TRUNCATE statement trigger. Re-stamping archivedAt is not merely unobserved there, it is impossible. That is the strongest form of this evidence and the only one of the four that has it. (3) n_tup_upd AS FALSIFIER. dbperfSnapshots 91 ins / 0 upd - capturedAt confirmed insert-time. practicaParticipationsArchive 5209 ins / 0 upd. practicasArchive 3642 ins / 1 upd - ONE update exists on a table whose trigger forbids it. Almost certainly predates venus62-66 (n_tup_upd is cumulative since the last stats reset), but I am recording it rather than rounding it to zero, because 'one update on an immutable table' is exactly the kind of number that gets rounded away and then cited as clean. THE CLAIM THAT DOES NOT SURVIVE: uxPainDigestRelay. I listed deliveredAt NOT NULL DEFAULT now() as a compliant domain-named equivalent. The table has 0 inserts, EVER - n_tup_ins 0, n_live_tup 0, and its only mention anywhere in src/ or scripts/ is a comment in ux-pain-digest.ts:222. An empty table cannot testify about its own column semantics. Worse, deliveredAt is delivery-time by name: if the relay is ever built to enqueue a row and stamp delivery when it lands, that column becomes the enamel defect precisely. This is consistent with the separately-known state that the ux-pain friction rail has never delivered (delivered:false 9/9). CORRECTED VENUS VERDICT: 43 of 47 base tables carry createdAt NOT NULL DEFAULT now(); 3 more carry a domain-named equivalent now verified insert-time by writer evidence, not shape (dbperfSnapshots.capturedAt, practicasArchive.archivedAt, practicaParticipationsArchive.archivedAt); 1 is UNEXERCISED, not compliant (uxPainDigestRelay.deliveredAt, zero rows ever). The two _venus155_*_sexo_backup tables remain out of scope as pre-migration backups, filed separately as VENUS-403. NOT FILING A FIX FOR uxPainDigestRelay. Adding a createdAt to a table that has never held a row is schema churn against a hypothetical. The right disposition is that whoever builds the relay writer decides then, with the constraint stated here: if a row is inserted at enqueue and deliveredAt is stamped on delivery, the table needs a separate insert-stamped column, because deliveredAt will not be one. Recorded so that decision is made with this in view rather than rediscovered. FLEET WORDING, third recommendation and the one that actually matters now. The mandate as written is satisfiable by a column that preserves no insert time, on three of four refs measured. It should say: an insert-stamped NOT NULL DEFAULT now() column that NO WRITER UPDATES - and compliance evidence should name the instrument (immutability trigger, writer audit, or n_tup_upd), not the column definition. Without that, every future audit of this mandate reproduces the shape-only error that both enamel and venus just made independently. That is coder-mars-cc's doc lane, not mine to edit. STATE: venus corrected above. enamel measured by db-enamel-cc (38 of 40, two real gaps). pluto closed via PLUTO-611. mars verified compliant. All four refs now have writer-aware or explicitly-caveated verdicts. Whether the two enamel counter gaps and venus's one unexercised table block the close is pmmaster-evolutiva-cc's call; venus owes no schema change either way.
• wi-cli-venus note · 5w ago
  CORRECTION to my enamel verdict posted 40 minutes ago (db-enamel-cc): one of the two gaps I reported as MEASURED was prospective. Following db-venus-cc's n_tup_upd suggestion I re-read the rows instead of only the writers. pg_stat_user_tables on enamel: terraPracticaCounters 4 ins / 157 upd, supportTicketCounters 4 ins / 18 upd - an apparent 39x overwrite rate. But lastNumber is 6 on terraPracticaCounters and 0 on BOTH supportTicketCounters rows, whose updatedAt still sits at the 2026-07-31 09:05 seed insert and has never moved. 151 of those 157 updates and all 18 of the ticket ones were ROLLED BACK - n_tup_ins/n_tup_upd count attempted tuples, committed or not. Same signature across the tree: pacientes 87 ins / 0 del / 6 live, terraPracticas 157 ins / 1 del / 6 live. CORRECTED ENAMEL VERDICT: 40 base tables, 34 views, 0 matviews. 38 clean. terraPracticaCounters genuinely does not preserve insert time (updatedAt = 2026-08-30 14:21:40, its 6th commit, not its insert). supportTicketCounters WILL NOT preserve it on the first real ticket but currently does - reporting it as 'does not' was wrong. INSTRUMENT RULE THIS ESTABLISHES, and it matters for every ref still to be audited on this row: three instruments answer this question and each is valid in ONE DIRECTION ONLY. Column SHAPE cannot distinguish insert-time from update-time at all (both are NOT NULL DEFAULT now()). prosrc names the writer but only for writers that live in the database. n_tup_upd = 0 IS evidence nothing was ever re-stamped; n_tup_upd > 0 is NOT evidence of an overwrite, because it counts attempts and an aborted transaction leaves the column untouched. The only affirmative instrument is an enforced immutability trigger, which makes re-stamping impossible rather than unobserved. Compliance evidence on this row should name the instrument AND its direction, never just the column definition.
• wi-cli-venus note · 5w ago
  INSTRUMENT CORRECTION - n_tup_upd IS VALID IN ONE DIRECTION ONLY. db-enamel-cc found this against my own recommendation; recording it here because my previous note on this row cited the instrument without the caveat and would otherwise be read backwards. n_tup_ins and n_tup_upd COUNT ROLLED-BACK TRANSACTIONS. An aborted transaction leaves the column untouched and still increments the counter. So: n_tup_upd = 0 IS evidence no row was ever re-stamped - zero attempts, committed or not. n_tup_upd > 0 is NOT evidence of overwrite. It counts attempts. db-enamel-cc's measurement, which is the proof: terraPracticaCounters 4 ins / 157 upd reads as a 39x overwrite rate, but lastNumber is 6 - 151 of the 157 aborted. supportTicketCounters 4 ins / 18 upd with lastNumber 0 on both appKeys and updatedAt still at its 2026-07-31 seed insert, so all 18 aborted and that column today IS its insert time. Same statistic across their tree: ~150 attempted práctica writes, 6 survived. Never quote the ratio; read the row. DB-ENAMEL-CC CORRECTED ITS OWN VERDICT ON THIS ROW off that: of its two reported gaps, supportTicketCounters is PROSPECTIVE ('will not preserve insert time on the first real ticket'), not measured, because nothing has overwritten it yet. terraPracticaCounters is genuine - updatedAt 2026-08-30 14:21:40 is its 6th commit, not its insert. One real gap, one forward-looking one. CONSEQUENCE FOR MY OWN VENUS NUMBER, and it resolves an anomaly I flagged rather than deepening it: I reported practicasArchive at 3642 ins / 1 upd and called out that single update as sitting on a table whose fn_archives_immutable trigger forbids UPDATE. Under the direction rule that count cannot be read as a successful overwrite at all. A heap update inside a transaction that later aborted is the likelier reading than a pre-trigger commit, and either way the column was not re-stamped. I am not chasing it further. Venus's three verified equivalents stand: dbperfSnapshots.capturedAt, practicasArchive.archivedAt, practicaParticipationsArchive.archivedAt - the two archive columns carrying structural proof (enforced immutability trigger) rather than statistical evidence, which is why they were never dependent on this counter. SHARPENED FLEET WORDING, db-enamel-cc's refinement of my recommendation and the version that should land in evolutiva-db.md: compliance evidence must name the instrument AND ITS DIRECTION, because all three instruments in play are valid one way only. Shape proves nothing in either direction. prosrc proves a writer DOES overwrite but cannot prove none does - app-side writers are invisible to it. n_tup_upd proves absence of attempts but never presence of overwrite. Only an enforced immutability trigger is bidirectional, and almost no table has one. A verdict on this mandate that does not name its instrument and direction is not checkable by the next reader, which is how both enamel and venus independently shipped a shape-only verdict in the first place. Three refs have now each corrected their own verdict on this row (venus twice, enamel once) and every correction came from changing instrument, never from new data. coder-mars-cc owns the doc edit.
• db-venus-ca progress · 3w ago
  2026-09-24 handoff/status: Venus leg last verified 2026-09-11: 43/47 base tables have createdAt NOT NULL DEFAULT now(); 3 have writer/immutability-supported insert-time equivalents; uxPainDigestRelay.deliveredAt is unexercised (zero inserts), so not claimed compliant. Fleet doc codified; Mars and Pluto previously verified; Enamel counter gap and name-flexibility carve-out/close decision remain with fleet PM. No new live DB verification today and no Venus schema change or close claimed. Next: pmmaster-evolutiva-cc decide fleet disposition and assign any Enamel follow-up.
• pmmaster-evolutiva-cc decision · 1w ago
  pmmaster ruling, 2026-10-04: (1) Name-flexibility carve-out APPROVED on Elazar's behalf per standing drumbeat GO authority — already independently converged on by 3 PMs, already applied fleet-wide in venus/pluto/enamel verdicts with no issue, doc already codified at evolutiva-db.md. No further Elazar sign-off needed to use it going forward. (2) Enamel's 2 counter gaps (supportTicketCounters, terraPracticaCounters) do NOT block this row's close — filed as a separate additive follow-up WI to db-enamel-ca, low forensic value, singleton gapless-numbering rows, not a reason to hold the fleet mandate open. Closing EVO-69.
• pmmaster-evolutiva-cc completed · 1w ago
  Fleet createdAt mandate satisfied: venus 43/47+3 verified equivalents (1 unexercised table left correctly unclaimed, VENUS-403 backups out of scope), pluto closed via PLUTO-611, mars compliant, enamel 38/40 with the 2 genuine gaps spun off as a follow-up WI rather than blocking close. Name-flexibility carve-out ratified on Elazar's behalf.