appEvents prune sweeps (info/warn) failing every run: postgres missing UPDATE grant
Jobs 7 (venus-prune-appevents-info) and 8 (venus-prune-appevents-warn) have failed EVERY run in the visible 7-day cron.job_run_details window (2026-09-28..2026-10-04, 7/7 each), all with: ERROR: permission denied for table appEvents nested under the FK-generated lock check: SELECT 1 FROM ONLY "public"."appEvents" x WHERE "id" = $1 FOR KEY SHARE OF x nested under the job's own DELETE FROM "appEvents" WHERE level=... AND "createdAt" < now()-90d AND "categoryId" NOT IN (...). ROOT CAUSE, confirmed by live reproduction (safe, rolled back): role postgres lacks UPDATE on "appEvents" (has SELECT/DELETE/INSERT/REFERENCES/TRIGGER). The sole FK referencing appEvents, uxPainDigestRelay_eventId_fkey (NO ACTION both ways, uxPainDigestRelay has zero rows ever), makes every DELETE against appEvents run a FOR KEY SHARE lock-check row-by-row, and that lock acquisition requires UPDATE privilege on this PG/Supabase config, not just SELECT/DELETE. Reproduced: BEGIN; GRANT UPDATE ON "appEvents" TO postgres; <job 7's exact DELETE>; -> DELETE 66; ROLLBACK (no data changed). CONCRETE SYMPTOM: 66 outboundEmailSent info rows older than 90d (oldest from 2026-06-14) found still present while closing VENUS-322 - this is that bug's downstream effect, not a different retention-window interpretation. Not a false alarm. BLAST RADIUS CORRECTION: jobs 12 (error) and 13 (debug) show 7/7 "succeeded" in the same window, but their predicates currently match zero rows - a DELETE with zero matching rows never triggers the FK lock check. Same latent bug, just unexercised ("a control that hasn't been seen to fail is a green line, not a control"). All four appEvents-level prune jobs (7/8/12/13) share the identical DELETE-against-appEvents shape and are all exposed. FIX PREPARED, NOT YET APPLIED: db/migrations/pending/venus84-grant-appevents-update-for-prune.sql - GRANT UPDATE ON "appEvents" TO postgres. Does not reopen real updates: fn_appevents_immutable() unconditionally RAISEs on TG_OP='UPDATE' regardless of grants; its bypass clause only matches TG_OP='DELETE'. The missing grant was redundant defense-in-depth on top of that trigger, with the side effect of breaking the prune DELETEs the same trigger is written to allow. ALTERNATIVE CONSIDERED, not taken: change uxPainDigestRelay_eventId_fkey's ON DELETE/UPDATE action instead of granting UPDATE, since that table has zero rows ever. Granting UPDATE is simpler, matches postgres's already-broad grant set, and the trigger-level block makes it safe; noting the alternative for the record in case defense-in-depth-via-grants is wanted back later for a different reason. Needs apply (DATABASE_URL_DIRECT, pending -> applied per convention) then push via a coder lane (db-venus-cc holds no push authority).
Activity
-
CLOSED - venus84 landed 2b01348, live PTD on v1.17.31. PUSHED because db-venus-cc holds no push authority; GO pmmaster-evolutiva-cc (tag pmmaster-evolutiva-cc-muug1catrvku). File went straight into db/migrations/applied/ - it was never committed to pending/, so there is no rename in history. db/schema.md regenerated per the apply checklist. PTD: /api/health returns version 1.17.31, deploymentId dpl_2X2F6U2UMtiRvwBimNsHGUWXQvrN, dbOk true; origin/main carries the GRANT line and package.json 1.17.31, so the landed diff is the pushed diff, not just the reported SHA. RE-VERIFIED LIVE BEFORE PUSHING rather than taken from the handoff: has_table_privilege(postgres,appEvents,UPDATE) is t and role_table_grants lists all six; last failures are jobs 7 and 8 at 2026-10-04 04:05Z/04:10Z with the exact permission-denied message; jobs 12 and 13 succeeded the same morning on zero-row predicates, which is the unexercised-control half of this row. APPEND-ONLY GUARANTEE CHECKED AT THE SOURCE, not accepted from the migration header that asserts it: fn_appevents_immutable bypasses only on TG_OP=DELETE AND app.bypass_immutable=true AND current_user=postgres and RAISEs otherwise, with trg_appevents_immutable BEFORE DELETE OR UPDATE at tgenabled=A, so an UPDATE by any role including postgres still raises. The grant restores the prune DELETE and reopens no write path. INDEPENDENT EVIDENCE THE PRUNE RAN: the schema regen in the same commit shows appEvents 8570 -> 5952 between the 16:19 and 23:21 exports today, a 2618-row drop on a sweep that had deleted nothing for at least a week. CROSS-ROW: this is the cause of the residual I flagged on VENUS-322 earlier today from the other end - outboundEmailSent rows surviving from 2026-06-14 past a 90-day info retention. The sweep was not running; that 66-row backlog is now 0. COVERAGE STATEMENT: closed on the privilege being live, the guarantee being intact, and a manual execution of job 7s exact DO block. NOT PROVEN and the only thing left: a SCHEDULED run succeeding - the manual run proves the privilege, not the cron path. 2 prunable info rows remain (level=info, >90d, non-security category), so job 7 at 2026-10-05 04:05Z is the first unattended evidence. If that fire still fails, this reopens. NOT EXERCISED: jobs 12 and 13 against a non-empty predicate, which remains untested by anything in this row.