Audit-trail schema plan (Pluto, EVO-57 child)
Per EVO-57 (nqla #5+#6, Elazar 2026-07-17): plan Pluto's audit-trail schema normalization program before any DDL. Two dimensions: (A) coverage - createdBy/createdAt + deletedBy/deletedAt + changelog/archive on SENSITIVE-data tables only, exempt telemetry/system-queue/non-sensitive tables; (B) naming - one consistent field-name convention across audit/soft-delete/changelog/historial columns fleet currently has divergent spellings. Input = PLUTO-580 (EVO-55 phase-1 gap report, db-pluto-cc). Must integrate existing historial engine (pluto-historial-engine-config-driven), not duplicate it. Deliverable is a PLAN (approach + migration sequencing + child bwis for each migration phase), not code - Elazar gates DDL after reviewing the plan. Coordinate with coder-pluto-cc + db-pluto-cc + audit-pluto-ca.
Sub-tickets — 6/12 done
Questions
Activity
-
parent=#2093
-
Plan drafted by audit-pluto-ca + db-pluto-cc (schema inventory): 31 base tables checked, only 4 fully lifecycle-covered. Highest gap: 8 practica* clinical tables lack createdBy. accessRequests/supportTickets/supportTicketMessages/users/lookupOptions/systemSettings each miss 1-2 fields. Exempt: appEvents, dbperfSnapshots, notifications, securityAlerts, emailOutbox, appEventActionClassification. Architecture: preserve the 23 *Archive tables + trigger framework unchanged, keep Historial as sole read-shaping consumer (no duplicate audit store). 6 child WIs filed (PLUTO-584..589 = 583A-F): lifecycle contract+sensitivity matrix -> additive migration -> write-path actor population -> provable-only backfill -> future DB enforcement -> Historial mapping+regression. Awaiting coder-pluto-cc's concurrence before reporting final to pmmaster.
-
coder-pluto-cc concurred, no structural objection - independently converged on the same 6-phase sequence (own duplicate WIs 590-595 canceled, detail folded into 584-589). Sequencing confirmed correct (585 columns -> 586 populate -> 587 backfill -> 588 enforce, dependency order holds). Key scope refinements from coder (logged on 586/589 directly): deletedBy already populated via archive-context on 17 tables - real gap is createdBy (19/68 have it) + updatedBy (11/68); createdBy must be the authenticated actor written directly into the INSERT column list, NOT sourced from the archive-trigger sentinel app.user_id (two separate actor channels, don't conflate - flag in 584 contract); friction-code/mutation-wrapper has zero touchpoint (silent server-side write, no toast/lint interaction); 589 naming sweep found only 2 divergences (studentDeletedAt, bare 'reason' on 2 tables) - low-risk. Plan is converged, reporting to pmmaster.
-
pmmaster: HOLD execution (including 584) - Elazar wants all 3 EVO-57 project plan outlines (mars/pluto/venus) before releasing, and rules the archived-PII retention question first. Note: Pluto's plan avoids the PII-retention issue Mars hit (Mars proposes NEW archive twins per table family = duplicate PII; Pluto reuses the existing 23 archive tables + adds createdBy/updatedBy to base tables only) - flagged to Elazar as possibly fleet-preferred shape. No action pending release.
-
EVO-57 HOLD (Elazar 2026-07-17): no blanket audit-trail coverage - re-scope to per-table justified additions only when Elazar picks it back up. No release GO.