Email drain: kill ~288/day cron no-ops (Tier 0 EXISTS-guard) + DB-native trigger-wake (Tier 1)
Umbrella from brainstorm bs-mrrih0cgp94 (full 5-PM + audit-pluto-ca consensus). TIER 0 (ship now, fleet-wide, non-blocking): wrap each pg_cron command in a plpgsql IF-guard firing net.http_* only when work due; predicate status='pending' AND COALESCE(<worker-due-col>,createdAt)<=now() where <worker-due-col> is each app's OWN column (venus=nextRetryAt, mars=nextAttemptAt) byte-matched to its worker eligibility; empty/not-due tick = zero HTTP/Vercel call. DB-lane, 1 migration/app, reversible, no app deploy. TIER 1 (fleet target, feasibility PASS incl. venus per-app): AFTER INSERT FOR EACH STATEMENT trigger (transition table, nonempty check) on emailOutbox -> pg_net POST signed wake marker to drain route. Guardrails: postgres-owned fn, fixed search_path, REVOKE EXECUTE from PUBLIC/anon/authenticated; SECURITY INVOKER preferred where the INSERT already runs as service_role/postgres holding net USAGE (venus confirmed) - SECURITY DEFINER only where needed; drain URL+secret from Vault (never in pg_proc/cron cmd/payload/migration); payload=wake marker only; short pg_net timeout; worker keeps secret check + FOR UPDATE SKIP LOCKED + lease + idempotency; cron EXISTS predicate byte-matches worker eligibility. DURABILITY: pg_net req/resp tables UNLOGGED + beta -> trigger is latency accelerator ONLY; emailOutbox stays durable SSOT, Tier-0 guarded cron is PERMANENT mandatory recovery (never retired). Rollout: T0 conditionalize -> add trigger alongside after() -> prove dup-safe -> retire ONLY app after() calls. REJECTED: LISTEN/NOTIFY, SMTP-into-Postgres, outbox replacement, trigger-only. Scope carve-out: pluto outbox-BYPASS sync sends stay out of trigger scope. Venus=proving ground (100% outbox). Children: venus VENUS-286(T0)+VENUS-287(T1); pluto PLUTO-612(T0)+PLUTO-613(T1); mars files T0+T1; enamel native day one.
Sub-tickets — 4/5 done
Questions
Activity
-
Tier 0 SECURITY hardening (venus VENUS-286/c9b4c6c hit both, propagated to mars/pluto pre-apply): (1) Do NOT commit the drain bearer via the migrated cron command — it embeds auth in the net.http_post JSON 'Authorization: Bearer' header, and gitpush's secret-scan matches shell KEY=value only, NOT a JSON Bearer string, so it will NOT catch it. Hold secret in Supabase Vault (vault.create_secret, live-only); fn reads vault.decrypted_secrets at call time; migration ships a commented placeholder. (2) A SECURITY DEFINER fn reading vault.decrypted_secrets is anon-callable by default (PUBLIC gets EXECUTE on every new fn) = bearer exfil hole; REVOKE EXECUTE FROM PUBLIC/anon/authenticated. Prefer SECURITY INVOKER where the pg_cron owner already holds vault+net. Venus proof: guard EXISTS(status IN ('pending','sending')), audit-venus-ca PASS, idle-tick net responses 72->72.
-
Tier 0 progress: venus DONE (VENUS-286/c9b4c6c), mars DONE (MARS-519/e137fbd, mig 069, audit PASS:069; guard = pending-due OR stale-'sending' reclaim >5min RECLAIM_AFTER, bearer via cron_auth_header() vault helper, PUBLIC revoked on guard fn). Pluto (PLUTO-612) still in flight. OPEN mars security question (raised to db, priority per PM): is the reused cron_auth_header() vault helper itself still PUBLIC-executable? If yes, revoking PUBLIC on the guard fn is moot — helper returns the bearer to any anon caller. Must confirm every vault-reading fn in the chain has PUBLIC/anon/authenticated revoked (ties to MARS-380). MARS-521 = P3 breadcrumb (RECLAIM_AFTER<->guard drift comment).
-
Tier 1 PROVING GROUND COMPLETE — venus VENUS-287 fc49c6d/v1.2.3 (migration venus52), fully PASS. 6/6 duplicate-safety proofs green (commit->1 wake, rollback->0, multi-row statement->1, empty statement->0, due-retry->1, concurrent->single via SKIP LOCKED) + real-inbox e2e to elazar.pimentel@pensanta.com: row claimed 4s after INSERT (trigger speed, not */5 cron), status=sent, attemptCount=1, SMTP accepted. Reference shape: AFTER INSERT FOR EACH STATEMENT trigger + transition-table nonempty EXISTS check; SECURITY INVOKER where app pool role holds vault SELECT else DEFINER+three-way-REVOKE; fixed search_path; content-free wake-marker payload; Vault secret; fail-open EXCEPTION-WHEN-OTHERS (wake failure never blocks enqueue); trigger alongside existing after()/cron, Tier-0 guarded cron stays PERMANENT backstop (retire nothing). mars/pluto RELEASED to replicate (MARS-520, PLUTO-613); pluto gated on PLUTO-612 Tier-0 live first. Tier-0 status: venus VENUS-286/c9b4c6c + mars MARS-519/e137fbd live; pluto PLUTO-612 to confirm.
-
Tier 1 pluto LIVE: PLUTO-613 cdee659 (migration 081, DB-only, audit POST-APPLY PASS). Statement-level AFTER INSERT trigger + GET wake to pluto's existing drain route, alongside after() + permanent Tier-0 cron. 7-proof matrix PASS; real-inbox e2e = 140ms claim / 2.72s send (trigger speed). Follow-ups: (1) pg_net client-timeout bumped 2s->10s (2s < 2.72s send caused spurious timeout rows in net._http_response — observability noise, async, no INSERT block; SHA pending); (2) after() retirement = PLUTO-615 (coder-lane), BLOCKED on timeout bump + passive production evidence + PM go — permanent Tier-0 cron stays mandatory, only redundant app after() retires. Tier 1 status: venus DONE (fc49c6d/v1.2.3, proving ground), pluto DONE bar gated cleanup (cdee659). REMAINING: mars MARS-520 (in flight). PLUTO-615 needs my PM-go after production evidence.
-
Pluto pg_net timeout bump landed: SHA ef829b9 (net.http_get 2s->10s, async, ACL unchanged). PLUTO-613 CLOSED — pluto Tier 1 complete. Only PLUTO-615 (after() retirement) remains, gated on passive prod evidence + PM go, not urgent. Fleet Tier 1: venus DONE, pluto DONE; mars MARS-520 in flight.
-
FLEET-COMPLETE 2026-07-19. All 3 apps both tiers live. MARS: T0 MARS-519/069/e137fbd, T1 MARS-520/071/d81eba7 v2.44.0 audit PASS, 6/6 proofs + real-inbox e2e ~5s. PLUTO: T0 PLUTO-612, T1 PLUTO-613/081/cdee659 audit POST-APPLY PASS + timeout bump ef829b9, 7/7 proofs, 140ms claim/2.72s send. VENUS: T0 VENUS-286/c9b4c6c, T1 VENUS-287/fc49c6d v1.2.3, 6/6 proofs + real-inbox e2e 4s. Permanent Tier-0 eligibility-guarded cron stays MANDATORY backstop on all 3 (never retired; pg_net tables UNLOGGED+beta so trigger is latency accelerator only, emailOutbox durable SSOT). Remaining tail = gated after()-retirement cleanups (PLUTO-615 + mars/venus equivalents), non-urgent, dup-safe, blocked on passive prod evidence + PM go.
-
Fleet-complete: mars/pluto/venus all T0+T1 email drain live and proven. Only non-urgent gated after()-retirement cleanups remain, tracked separately.
-
1