Purga de logs fresh-start (app_logs + audit_log)

Sub-tickets

Activity

• wi-cli-venus created · 2026-08-07
  parent=#2499
• wi-cli-venus note · 7w ago
  Audit PASS: 7646c654 (docs: add guarded dev log purge runbook) reviewed by audit-mba-l-cc at 07:38 ART. Verified DEV-only current_database gate, fail-closed backup/GO attestations, live-vs-restored counts before atomic TRUNCATE RESTART IDENTITY, timeouts, and sequence reset post-gates. No purge executed.
• wi-cli-venus decision · 7w ago
  Elazar execution GO received via CLI at 07:45 ART. Scope DEV only (movilba_dev); execution remains subject to maintenance pause, stability check, post-maintenance backup restore/count verification, and the guarded script's gates.
• wi-cli-venus assigned · 4w ago
  coder03-mba-l-cc / coder
• coder03-mba-l-cc assigned · 2w ago
  coder03assistant-mba-l-ca
• researcher-mba-l-cc note · 1w ago
  researcher 2026-10-08 13:22 ART: not a decision - Elazar already gave execution GO 2026-08-26, scoped DEV-only, audit already passed the guarded script. This is scheduling/coordination (maintenance pause timing, post-pause backup, sequence reset) for the assigned coder lane, nothing further to escalate.
• db04-mba-l-ca progress · 1w ago
  2026-10-08 21:27 ART SELECT-only PROD then DEV census. Evidence SQL /data-lezama/movilba/evidencia/MOVILBA-9/2026-10-08-log-attribution-census.sql sha256 79df2c3e; report sibling .md sha256 bdfd70e9. PROD movilba/svc_nextjs: app_logs 101756 (2025-12-16 to 2026-10-08 15:54 ART), audit_log 573 (2025-10-06 to 2026-06-17). Levels info 95364/debug 3375/audit 1262/warn 910/error 845. DEV movilba_dev/movilba_user: app_logs 171187 (2025-12-16 to 2026-10-08 16:55), audit_log 1080 (2025-10-06 to 2026-10-08 14:13). Levels info 163158/debug 3403/warn 1907/error 1542/audit 1177. All query runs rc0, no write/purge/backup/sequence action. Attribution warning: 19 PROD current system_import vehicles join CREATE by record_id, only 17 also match domain+creation timestamp; two recycled/different current rows. In the 17 exact cohort in each base, CREATE actor not found in current users (0/17); actor survives in direct app_logs create messages 12/17 and other linked audit_log events 16/17, union 17/17, so original CREATE is sole measured actor link 0/17. This neither proves a real-user mapping nor makes logs disposable. Fresh maintenance-time backup/census still required before any MOVILBA-9 purge.
• db04-mba-l-ca note · 1w ago
  CORRECTION 2026-10-08 21:28 ART: my prior progress event's '0/17 original CREATE event sole actor link' is EVENT-level and MUST NOT be read as audit_log purge safety. All other audit_log events are in the same table and would be purged too. Re-ran corrected SELECT-only SQL sha256 5bf5cd4f, report sha256 93ba7579: among 17 exact current-row lost-creator matches in EACH base, 12 have same actor in direct app_logs creation messages, 5 have NO such measured non-audit-log actor link; history, VDC and vehicle archive carry that actor for 0/17. Therefore audit_log is the ONLY measured table retaining creation actor ID for 5 vehicles in PROD and 5 in DEV. Current public.users resolves 0/17 actor IDs, so real-person mapping remains unverified. Old DEV-only purge artifact PASS covers historical bytes, not current retention safety; no PROD-first purge design exists. No purge, backup, sequence action or DB write executed. Evidence: /data-lezama/movilba/evidencia/MOVILBA-9/2026-10-08-log-attribution-census.md.
• coder03assistant-mba-l-ca progress · 1w ago
  2026-10-08 live SELECT-only PROD then DEV census: report /data-lezama/movilba/evidencia/MOVILBA-9/2026-10-08-log-attribution-census.md SHA-256 93ba75798b1a58e65a933269a872f19bbf933067ce254537c7430ee653199224; SQL sibling SHA-256 5bf5cd4fe9882221b2a2dabe38926d42a0150309fa357503a2642dd94dccc32d. app_logs/audit_log counts PROD 101,756/573; DEV 171,187/1,080. Exact-current-row cohort with lost creator and matched CREATE identity is 17 in each base. In each base, audit_log is the only measured table carrying the actor ID for 5/17; none of the 17 historical actor IDs resolve to current users. No purge/backup/sequence/DDL/write executed. Therefore full audit_log purge would destroy sole measured actor evidence for 5 vehicles per base; decision to preserve vs destroy must go to Elazar. Existing guarded DEV-only script SHA 161ac509...e4e9c2 is not a PROD design and its 2026-08-26 PASS applies only to those bytes, not today's data. No new purge artifact written.
• db04-mba-l-ca note · 1w ago
  FINAL SCOPE CORRECTION 2026-10-08: proposed fresh-start truncates BOTH app_logs and audit_log, not one table. In each base, the only measured creation-actor link for the 17 exact-match lost-creator vehicles is their UNION: app_logs direct keyed creation messages retain actor on 12/17, audit_log is necessary for the other 5/17, and vehicle/history/VDC/archive creator fields retain actor on 0/17. A full two-table purge therefore removes the measured actor link for 17/17. Current users resolves 0/17 historical actor IDs; retention value is Elazar's decision, not DB-lane judgement. Prior 0/17 sole CREATE-event and 5/17 audit_log-only figures answer narrower questions and are NOT purge-safety denominators. Corrected report /data-lezama/movilba/evidencia/MOVILBA-9/2026-10-08-log-attribution-census.md; no purge or write.
• db04-mba-l-ca verified · 1w ago
  Final evidence pin after operation-scope correction: /data-lezama/movilba/evidencia/MOVILBA-9/2026-10-08-log-attribution-census.sql SHA256 5bf5cd4fe9882221b2a2dabe38926d42a0150309fa357503a2642dd94dccc32d (SELECT-only query rc0 PROD+DEV); sibling .md SHA256 29d6c0a093afb22502eccb58d367cbca68756a27a360310439755fd9f4579e78 (explicit full two-table purge would erase measured actor link for 17/17). Earlier SQL 79df2c3e and reports bdfd70e9/93ba7579 were real intermediate bytes, superseded by corrections; do not cite them as final. No purge/write.
• coder03assistant-mba-l-ca progress · 1w ago
  Correction to my prior progress: db04 corrected the census interpretation. The 5/17 figure is vehicles whose creator actor appears in audit_log but not in measured direct creation app_logs; it is not the total evidence lost by the proposed full purge. The UNION of app_logs + audit_log is the only measured actor link for all 17/17 exact-match lost-creator vehicles in each base (12 app_logs direct, five requiring audit_log). Current public.users resolves 0/17. A full two-table TRUNCATE therefore removes the measured actor link for 17/17, not five. Corrected report SHA-256 29d6c0a093afb22502eccb58d367cbca68756a27a360310439755fd9f4579e78; SQL SHA-256 5bf5cd4fe9882221b2a2dabe38926d42a0150309fa357503a2642dd94dccc32d. No write or purge; route preserve-vs-destroy decision to Elazar. Earlier note's phrasing is superseded by this correction.
• db04-mba-l-ca verified · 1w ago
  Final table-overlap input for Elazar, SELECT-only in PROD and DEV at 2026-10-08 21:29 ART, psql rc0 both: among 17 exact current-vehicle lost-creator matches in EACH base, creation actor ID appears in both audit_log CREATE and directly keyed app_logs create messages for 12; audit_log alone for 5; app_logs alone 0; neither 0. Thus retaining audit_log preserves the measured actor ID for 17/17 if app_logs alone is purged; retaining only app_logs preserves 12/17; purging both loses all 17/17. Current users resolves 0/17 IDs, no real-person mapping verified. This is actor-attribution scope only, not a general app_logs retention verdict. Evidence SQL /data-lezama/movilba/evidencia/MOVILBA-9/2026-10-08-log-attribution-census.sql and sibling report repinned after overlap query; no write/purge.
• db04-mba-l-ca note · 1w ago
  Final evidence bytes after overlap matrix: SQL sha256 ffff1f0d3af063a40dc8b8e03328a004d2965fa019d6296018269c469c3b6fcf; report sha256 08f00eeea820a474e1773c5d1fbdc56b35add776f829ee52af00114cd867fcfb. Earlier pins are superseded, not missing. Both exact SQL legs executed rc0; matrix 12 both / 5 audit_log only / 0 app_logs only / 0 neither in each base.
• db04-mba-l-ca verified · 1w ago
  PM-requested direct ANY-row four-cell SELECT on exact 17 matched current vehicles, run SELECT-only in PROD and DEV 2026-10-08 21:30 ART, psql rc0 both. Predicates stated in SQL output header: EXISTS any public.app_logs row with user_id=cohort audit actor; EXISTS any public.audit_log row with user_id=cohort audit actor; no message, vehicle or time filter. Result EACH base: both=17, app_logs-only=0, audit_log-only=0, neither=0. This is actor-ID presence anywhere, NOT creation-to-vehicle attribution. Directly keyed creation-event matrix separately remains both=12, audit_log-only=5. Exact SQL/report files in /data-lezama/movilba/evidencia/MOVILBA-9/ repinned by next SHA note; no write or purge.
• db04-mba-l-ca note · 1w ago
  Final evidence pins for PM's ANY-row predicate: SQL sha256 8de15e600c48673188c496d8c3efc3064f412fa4f176b0bd86bd75ec1b36b1a5; report sha256 ce34f98e8df6c7ff4f5a81c8cecc1d5e15ded99f7db67d385b49fc64e27a8347. Earlier pins were before the ANY-row query. Both exact SQL legs rc0. No further modification planned.
• coder03assistant-mba-l-ca note · 1w ago
  Decision received 2026-10-09 via PM: option B, purge both app_logs and audit_log with no retention window; proceed with preparation only. This does not authorize this coding lane to execute SQL: DB lane owns all mutating runs. Existing artifact 2026-08-26 is DEV-only and stale as a PROD design; prepare a new PROD-first runbook/artifact with target guards, preflight, sequence reset checks, rollback-safe control, and no execution until DB lane confirms the maintenance/backup preconditions.
• coder03assistant-mba-l-ca statusChanged · 1w ago
  Preparing a new PROD-first purge artifact and DB-lane preflight; no SQL will be executed by coder lane.
• coder03assistant-mba-l-ca note · 1w ago
  Prepared, not executed, a new parameterized PROD-first candidate at docs/migrations/2026-10-09-movilba-9-log-purge-PROD-Y-DEV.sql (SHA256 ce0428ae9d514af8d1a1629a70a49fdf03d4792f483e2b9638adac709f003d7d). It accepts only movilba/movilba_dev with a caller-supplied exact current_user, shows pre/post table counts, uses pg_get_serial_sequence, TRUNCATEs only app_logs + audit_log RESTART IDENTITY (no CASCADE), and commits only with apply=true plus verified backup counts, stopped-writer attestation, and elazar_go=yes; default is ROLLBACK. Old 2026-08-26 artifact remains DEV-only and unchanged. Current census says the full purge knowingly removes measured actor-ID presence for 17/17 cohort vehicles in both bases. Fresh per-base preflight counts/sequence values requested from db04; no backup, rehearsal, or write performed by coder lane. Control rehearsal and execution are pending DB lane after audit; no approval to apply inferred from this note.
• audit-mba-l-cc audit-finding · 1w ago
  audit-mba-l-cc 2026-10-09: source-only review of docs/migrations/2026-10-09-movilba-9-log-purge-PROD-Y-DEV.sql (pin ce0428ae.. confirmed exact). Elazar's GO for purging BOTH tables (option B, no retention) confirmed in this item's own history (PM-relayed decision 14:17:13Z), with the 17/17 actor-ID-loss consequence explicitly known and accepted before the decision - not inferred by me. SQL review: default apply=false -> ROLLBACK, but the TRUNCATE genuinely executes inside the transaction before rollback (rehearsal exercises the real statement, not a no-op - avoids the MOVILBA-648 root-cause pattern of an ensayo that never runs the write). GATE 0 (unconditional) pins exact target_database in (movilba,movilba_dev), current_database match, current_user match via caller-supplied expected_user, both tables exist, both have owned serial sequences. GATE 1 (apply-only) requires backup_verified=yes AND writers_stopped=yes AND elazar_go=yes AND non-negative backup counts AND EXACT equality between live counts and caller-supplied backup counts - a stale backup (taken before writers stopped, since app_logs keeps growing) fails this gate rather than silently truncating past it. TRUNCATE scoped exactly to public.app_logs, public.audit_log RESTART IDENTITY, no CASCADE. POST-block verifies both tables empty and both sequences truly reset (last_value=1, is_called=false) before the final apply/rollback branch. Independently verified in PROD: zero FK constraints anywhere reference app_logs or audit_log (confrelid check) - truncating either orphans nothing. statement_timeout 30s / lock_timeout 5s are reasonable guards. Safe DB-lane control plan (requested): 1) enter maintenance mode, stop all writers; 2) take the backup only AFTER writers are confirmed stopped (file's own comment: a pre-maintenance backup is not a stable rollback point since app_logs keeps growing); 3) count rows in that backup, these become app_logs_backup_count/audit_log_backup_count; 4) run with apply=false (default) against PROD first to exercise the real TRUNCATE+verify+ROLLBACK path and confirm rc0; 5) run with apply=true + the three yes-attestations + exact backup counts against PROD; 6) verify PROD output (0/0, sequences reset); 7) repeat steps 1-6 against movilba_dev only, never in parallel with PROD per the file's own instruction; 8) delete the temporary backup once both bases pass post-apply verification. No gap found in the file's own stated plan - it already states this sequence; my review found no missing step and no unsafe gap. No pnpm gates run - this is a standalone psql artifact, not app code; nothing in src/tools touches it, confirmed by grep for the filename returning only this file itself and its own bwi reference.
• audit-mba-l-cc audited · 1w ago
  audit pass: source review only of 2026-10-09-movilba-9-log-purge-PROD-Y-DEV.sql; Elazar GO verified in item history; gates not applicable (SQL artifact, no app-code gate touches it)
  payload

  {"scope":"source review only of 2026-10-09-movilba-9-log-purge-PROD-Y-DEV.sql; Elazar GO verified in item history; gates not applicable (SQL artifact, no app-code gate touches it)","outcome":"pass","evidence":"pin ce0428ae.. exact; GATE0/GATE1 logic traced by hand - exact-equality backup-count check blocks stale backups; TRUNCATE scope exact, no CASCADE; POST-block verifies empty+sequence reset; zero FK constraints reference either table in PROD (confrelid check, independently queried)","reportRef":null}

• coder03assistant-mba-l-ca note · 1w ago
  Audit PASS on source for exact candidate SHA256 ce0428ae9d514af8d1a1629a70a49fdf03d4792f483e2b9638adac709f003d7d. Auditor confirmed the rehearsal executes TRUNCATE inside a transaction before ROLLBACK, exact backup-count equality gate, exact two-table scope/no CASCADE, and zero FK references to either target in PROD. This is a source verdict only: no rehearsal, mutant control, backup, or purge has run. Execution sequence remains maintenance mode -> backup after writers stopped -> PROD rehearsal/control and verification -> PROD apply with gates/counts -> verify -> DEV only after PROD, never parallel -> remove temporary backup.
• coder03-mba-l-cc note · 1w ago
  Elazar decision 2026-10-09: waives writers_stopped=yes gate explicitly. App not live, precision doesn't matter - proceed with backup/purge with web logging live. db04 cleared to run backup+migration without stopping app writers.
• coder03-mba-l-cc note · 1w ago
  PROD+DEV purge applied by db04, SHA ce0428ae. PROD 101864/573, DEV 171187/1080 rows purged. Readback: both tables 0 rows both bases, sequences reset. Evidence /data-lezama/movilba/evidencia/MOVILBA-9/2026-10-09-1151-purge-apply.md sha256 bb68fc83...
• coder03-mba-l-cc completed · 1w ago
  completed