EVO-93: move this app's email-outbox drain off Supabase pg_cron onto a venus systemd timer
Child of EVO-93 (move app email drains off Supabase pg_cron to venus systemd timers). Scope for this app, three steps, no app-code rewrite: 1. INVENTORY. List every scheduled job this app has and where it runs: SELECT jobid, jobname, schedule, active, command FROM cron.job on your Supabase project, plus the crons array in vercel.json, plus whatever your email transport module actually points at (Mars was defaulting to smtp.gmail.com:587). Post the inventory as an event on this WI. 2. CONFIRM THE ROUTE. Verify your outbox-drain HTTP route accepts an external bearer-authed curl from venus - not just a call originating inside Supabase. If it does not, that is the only app-side code change this epic needs. 3. HAND OFF. DM bin-venus-cc the drain URL and the bearer (DM only, never an aro fan-out, never in a commit or a WI body). bin-venus-cc owns EVO-94, the templated evolutiva-emaildrain@<app> systemd unit that replaces the pg_cron job. Do NOT rebuild the outbox layer. emailOutbox + dedupeKey + exponential backoff + reclaim + maxAttempts + the AFTER INSERT wake trigger are sound and stay. Transport is a SEPARATE, LATER step tracked as EVO-95: outbound port 25 from venus is BLOCKED (measured 2026-09-10), so a venus MTA cannot deliver direct-to-MX today and transport stays on an authenticated 587 relay for now. Do not migrate transport as part of this WI. Why now: Mars's Supabase pg_cron launcher went silent for hours on 2026-09-09 (MARS-698) with no alert - a pg_cron-scheduled drain has no backstop and fails invisibly.
Activity
-
coder03-mars-ca
-
coder-mars-ca
-
Resumed after dead coder03-mars-ca assignment; Mars authenticated Venus-host systemd service completed successfully on 2026-10-04 15:32:02 UTC (exit 0, curl --fail-with-body), replacing the 2026-09-10 failed manual run as current route evidence. Timer remains disabled; pg_cron must remain scheduled until a timed unit fire succeeds.
-
2026-10-04 DB cutover precheck (read-only): cron.job jobid=6, jobname=drain-email-outbox, schedule=*/5 * * * *, active=true, command=SELECT public.drain_email_outbox_if_due();. Latest job_run_details rows are succeeded (latest 2026-10-04 15:30:00.262714Z-15:30:00.298648Z, runid 38065); this is not evidence of the Venus systemd timer's scheduled fire. Held: do not disable job 6 until bin-venus-cc reports a successful scheduled-fire timestamp. No other pg_cron rows changed.
-
2026-10-04 15:39Z update from bin-venus-cc: @mars.timer is not enabled/listed by list-timers on Venus; therefore no qualifying scheduled @mars 2xx exists yet. Maintain pg_cron jobid 6 active. Once nw-venus-cc enables the timer and a scheduled service 2xx is reported, disable only jobid 6 and verify active=false. Unit/timer ownership is primary nw-venus-cc (registry row 233).
-
Scheduled @mars.timer service completed with Result=success at 2026-10-04 15:45:27Z, reported by nw-venus-cc after two cycles. This satisfies the scheduled-fire prerequisite for DB owner to unschedule pg_cron jobid 6; require active=false readback. No cron change by coder lane.
-
2026-10-04 DB cutover after coder-mars-ca reported nw-venus-cc's scheduled @mars.service success at 2026-10-04 15:45:27Z (after two timer cycles): preflight confirmed cron.job jobid 6 active=true (*/5, drain-email-outbox). Called cron.unschedule(6), which returned true. PostgreSQL pg_cron removes the row on unschedule rather than retaining active=false; post-read shows zero rows for jobid 6. All 15 remaining jobs were enumerated afterward; no unrelated job was targeted or changed.
-
DB cutover verified: cron.unschedule(6)=true; jobid 6 absent on post-read; 15 remaining jobs unchanged. Source inventory: vercel.json crons=[]; src/lib/email-transport.ts defaults to smtp.gmail.com:587 with env SMTP_HOST/SMTP_PORT overrides (source comment says production sets both). The WI original scope still calls for a complete cron.job inventory event; requesting DB owner post that enumerated list before close.
-
Post-cutover read-only inventory of cron.job after cron.unschedule(6): 15 rows remain; jobid 6 drain-email-outbox is absent. Columns: jobid | jobname | schedule | active | command (SQL whitespace normalized; no credential literals returned).\n5 | cleanup-email-outbox | 0 3 * * * | true | DELETE emailOutbox where status in sent/permanentlyFailed and createdAt older than app.outbox_retention_days (default 7 days)\n7 | prune-notifications | 15 3 * * * | true | DELETE notifications where readAt not null and older than 30 days\n8 | prune-appevents-info | 30 3 * * 0 | true | DELETE appEvents level=info older than 90 days\n9 | prune-appevents-client-env | 45 3 * * 0 | true | DELETE appEvents category=client-env older than 30 days\n10 | prune-dbperf-snapshots | 20 4 * * * | true | DELETE dbperfSnapshots capturedAt older than 90 days\n12 | refresh-multiradicular-stats | 1 */3 * * * | true | SELECT public.refresh_multiradicular_stats_logged()\n13 | cron-send-weekly-kpi | 0 9 * * 1 | true | net.http_get https://mars-evolutiva.pensanta.com/api/cron/send-weekly-kpi; headers from public.cron_auth_header()\n14 | cron-support-tickets-digest | 0 12 * * 1,4 | true | net.http_get https://mars-evolutiva.pensanta.com/api/cron/support-tickets-digest; headers from public.cron_auth_header()\n15 | cron-error-digest | 0 11 * * * | true | net.http_get https://mars-evolutiva.pensanta.com/api/cron/error-digest; headers from public.cron_auth_header()\n16 | cron-ux-pain-digest | 0 13 * * 1 | true | net.http_get https://mars-evolutiva.pensanta.com/api/cron/ux-pain-digest; headers from public.cron_auth_header()\n17 | cron-watchdog | 30 14 * * * | true | SELECT public.cron_watchdog_check()\n18 | cron-rectificar-reminder | 0 9 * * 3 | false | net.http_get https://mars-evolutiva.pensanta.com/api/cron/rectificar-reminder; headers from public.cron_auth_header()\n19 | practicas-integrity-check | 0 * * * * | true | SELECT public.practicas_integrity_check()\n23 | mars-wal-disk-health-check | 7 * * * * | true | SELECT public.wal_disk_health_check()\n25 | prune-cron-job-run-details | 50 4 * * * | true | DELETE cron.job_run_details: succeeded older than 30 days; other statuses older than 180 days\n\nCommands with auth call only the cron_auth_header() helper; no secret value included. Read via psql fallback after Supabase MCP execute_sql returned authentication-required.
-
Complete: all 16 pre-cutover pg_cron jobs accounted for (job 6 removed; 15 remaining inventoried), vercel.json crons=[], SMTP transport source documented. Venus-host bearer-authenticated Mars drain ran successfully, @mars.timer then fired successfully on schedule at 15:45:27Z, and db-mars-ca unscheduled only job 6 with absence on readback. No app-code or SMTP transport change.