appEvents historical email residue: 1354 rows pre-redaction, purge needs ruling

Sub-tickets

Activity

• db-venus-cc created · 1w ago
  parent=#4553
• db-venus-cc note · 1w ago
  Census 2026-10-08 ~17:20Z, appEvents total 7985 rows (2026-05-13..2026-10-08). Email regex, positive control users.email 41/41. Text columns: message 51 rows (logout 49, 2026-05-14..10-01; error-shown 2 'Ya existe una cuenta activa con ese correo (addr)' 08-01..08-07); errorMessage 0; errorStack 0; userAgent 11 = crawler contact addresses (AgentTrustBot 6, ClaudeBot 5) - not personal. detail jsonb walked recursively, excl .messageId and .verdicts[].unit: .actor 1138 (practicaCreated 934, practicaEdited 91, complicacionesCompletadas 79, crearPracticaFailed 32, completarComplicacionesFailed 1, participantAddedByApprover 1; 06-09..10-07), .recipients[] 89 (07-11..10-05), .to 58 (07-10..10-05), .recipientEmail 11 (06-29..09-19), .author 11 (07-13..09-22), .email 4 (05-26..08-07), .detail.email 3 (06-24..07-02), .approvedBy 2, .detail.approvedBy 1. Distinct rows any column: 1365 (incl. the 11 UA) = 1354 personal-address rows, 2026-05-14..2026-10-07 21:39Z. Coverage: 0 hits after 10-07 21:39Z, but that is a ~1-day window on a few-per-day producer, so the census does not prove the live fix. DNI: regex positive control pacientes.dniNormalizado 2707/2746, practicas.pacienteDni 3086/3596; negatives (uuid, timestamp, 1.17.38, 10-digit) all false. 0 DNI-shaped values in message/errorMessage/errorStack/route (strict and loose regex) and in detail string leaves; numeric 7-8 digit leaves are all *Ms/pid counters (staleMs 424, intervalMs 261, pid 205, beatMs 86). One row 9d01204e (getPracticasList 2026-06-13) has detail.filters.pacienteDni = empty string (length 0) - key present, no value. Purge path facts: trg_appevents_immutable blocks UPDATE/DELETE; repair must ride app.bypass_immutable GUC as the prune crons do; a570b58's repair script (scripts/repair-access-grant.ts) only stops writing detail.email going forward, it does not touch historical .actor/.recipients/.to/message rows.
• db-venus-cc blocked · 1w ago
  Purge/redaction of 1354 historical appEvents rows is a prod data mutation; awaiting pmmaster-evolutiva-cc / Elazar ruling (redact-in-place via bypass GUC vs delete vs keep). No DDL needed for either option.
• db-venus-cc note · 1w ago
  CORRECTION to purge-path facts: fn_appevents_immutable allows only DELETE, only as current_user=postgres with app.bypass_immutable=true. Every UPDATE is rejected, so redact-in-place needs DDL first (a scoped UPDATE bypass, which is a Class-A change to an audit control, VENUS-347). Options: (a) DELETE the rows via the existing prune bypass (loses the events entirely), (b) DDL then UPDATE-redact, (c) keep and let retention age them out.
• db-venus-cc blocked · 1w ago
  Purge of 1354 historical appEvents rows is a prod data mutation; awaiting pmmaster-evolutiva-cc / Elazar ruling: (a) DELETE via existing postgres+app.bypass_immutable path, (b) Class-A DDL to allow scoped UPDATE then redact, (c) keep until retention.
• db-venus-cc note · 1w ago
  Retention (cron.job, read 2026-10-08): DELETE info/warn >90d, error >180d, debug >7d, all excluding appEventCategory rows with params.security=true. So option (c) clears most residue by ~2027-01-05 (info/warn) / ~2027-04-05 (error); security-category rows never age out.
• db-venus-cc note · 1w ago
  blocked DM to pmmaster-evolutiva-cc tag db-venus-cc-muztd242i1gl (options A/B/C).
• db-venus-cc note · 1w ago
  CORRECTION to my own earlier bookkeeping: the 1365 figure in my census note already EXCLUDED the 11 userAgent-crawler rows (that count came from message/errorMessage/errorStack/detail only; userAgent was a separate query). My '1354 personal = 1365 - 11' subtraction was wrong - there is no overlap to subtract (verified: comm -12 against the 11 UA ids = 0 matches). Correct scope for the ruling's option A: 1365 rows (same predicate: message/errorMessage/errorStack text match OR a detail jsonb leaf match, excluding .messageId/.verdicts[].unit), NOT 1354. The 11 UA rows are additional and are excluded per the ruling (bots, kept). Re-derived manifest id list, re-verified against live catalog just now: 1365 ids, sha256 ce282caa2be791bdc7967ebb8080e89ea5ac12c14a8585c7b38c4c939a265f0f, saved durably at ~/gdrive/projects/evolutiva/venus/venus486-appevents-email-purge-2026-10-09/venus486-delete-manifest-1365-ids.txt (ids only, no addresses, no other columns).
• db-venus-cc note · 1w ago
  DELETE executed 2026-10-09 ~02:23Z via DATABASE_URL_DIRECT (current_user=postgres), one transaction: BEGIN; SET LOCAL app.bypass_immutable='true'; DELETE ... USING purge_ids; postflight predicate count=0; COMMIT. Full teed session: ~/gdrive/projects/evolutiva/venus/venus486-appevents-email-purge-2026-10-09/venus486-purge-session.log. Pre-delete drift checks: 0 live-predicate ids outside manifest, 0 manifest ids already missing - exact match. DELETE reported 1365 rows (= manifest count exactly). Post-delete: predicate re-scan = 0 matches; 11 userAgent crawler rows untouched; final appEvents total 6901 (was 7985).
• db-venus-cc note · 1w ago
  Effect on views/digests: checked all 25 views depending on appEvents (pg_depend). Live re-run post-delete: v_appEvents_security=69 rows, v_appEvents_404=1 row, vTitularDigestCandidates=0, vSupportTicketSlaCandidates=0, vTelemetryCanaryHealth=covered/288 - all normal, no breakage (none are stored/materialized, all recompute live, so there is nothing to repair). REPORTABLE SIDE EFFECT: action=crearPracticaFailed and completarComplicacionesFailed now have 0 rows total, all-time (count/min/max all null) - every historical row for these two logCaughtError (level=error) actions carried the email in detail.actor, so the purge erased their ENTIRE history, not just a tail. action=error-shown kept 697 of 699 rows (only its 2 email-bearing rows removed) - unaffected otherwise. Current 24h/7d/30d windows (v_appEvents_recurring, v_appEvents_alerts, v_appEvents_security) show none of these three actions right now regardless (all deleted rows predate 2026-09-09), so no live dashboard discontinuity today - but any future 'crearPracticaFailed has never happened' read is now true only because the record was erased, not because it stopped happening. NOT exercised: a before/after row-level diff on level/action/createdAt for all 1365 rows - only the id manifest was preserved pre-delete, not full row content; recovery would require the external pg_dump backup lane (bin-venus-ca/nw-whey-cc), not attempted here.
• db-venus-cc unblocked · 1w ago
  cleared: Purge of 1354 historical appEvents rows is a prod data mutation; awaiting pmmaster-evolutiva-cc / Elazar ruling: (a) DELETE via existing postgres+app.bypass_immutable path, (b) Class-A DDL to allow scoped UPDATE then redact, (c) keep until retention.
• db-venus-cc completed · 1w ago
  Option A executed: 1365 personal-email appEvents rows deleted (11 crawler userAgent rows kept). Manifest/log durable at gdrive venus486-appevents-email-purge-2026-10-09/. Side effect flagged: crearPracticaFailed + completarComplicacionesFailed lost all-time history (every row for those 2 actions carried the email).