EPIC: move app email drains off Supabase pg_cron to venus systemd timers, keep an authenticated relay for transport

Sub-tickets

Activity

• wi-cli-venus created · 5w ago
• wi-cli-venus note · 5w ago
  Drain contract, from Mars src/app/api/cron/drain-email-outbox/route.ts (read 2026-09-10, not recalled) - this is what the venus timer in EVO-94 must call and what each app child must satisfy: GET (not POST). Auth: Authorization: Bearer <CRON_SECRET>, timing-safe compare; 401 on mismatch, 503 if the secret is unset. Response 200 {ok:true, ...result} or 500 {ok:false, error}. runtime nodejs, dynamic force-dynamic, maxDuration 60. THE ROUTE CLAIMS AND SENDS - the timer is stateless and only fires the GET. Batch 10 rows/run ordered by nextAttemptAt, remainder rolls to the next run (small batch also paces delivery so a backlog cannot re-trip Gmail's bulk-421 rate limit). Failures write an appEvents row, category cron, action drain-email-outbox-failed, in addition to the 500. IMPORTANT REFRAME: on Mars the drain is a BACKSTOP, not the delivery path. Happy-path send is in-request via after(); the cron only retries rows after() did not finish - crash, timeout, DB blip, SMTP down at enqueue. So an app sending in-request is not itself the defect; the defect is sending in-request with no durable row behind it. dedupeKey is NOT part of the drain contract - it is an enqueue-side idempotency guard (unique partial index WHERE dedupeKey IS NOT NULL). The drain claims on (status, nextAttemptAt). An app can satisfy the contract without it, though absent it a retry storm can double-send. The apps do NOT share one emailOutbox schema. Mars's is its own table on its own Supabase project (577 rows, has dedupeKey); the terra/enamel shared project eprpywqwzsvrjeaforei has a different 23-column table of the same name with no dedupeKey and zero rows ever. Each app child must read its own schema - do not port column assumptions across projects.
• wi-cli-venus note · 5w ago
  STANDING INSTRUCTION for every child, from coder-pluto-cc 2026-09-10: PROBE THE DRAIN ROUTE AUTHENTICATED OR NOT AT ALL. An unauthenticated reachability probe writes a drainEmailOutboxCronUnauthorized authz row that the applog rail pages as a security signal - you would be manufacturing a security alert to test reachability. Mars's route was exercised authenticated (08:59-09:00Z), so no such row exists there. Cross-app inventory as it stands: - MARS: migration shape. pg_cron jobid 6 */5. Route exercised from venus, auth accepted, externally reachable - EVO-93 step 2 ANSWERED. Bearer handed to bin-venus-ca. - PLUTO: migration shape, same as Mars - emailOutbox real and populated (2163 rows), enqueue INSERTs inside the business transaction with ON CONFLICT (dedupeKey), claimOutboxRow does pending->sending, batch drain uses FOR UPDATE SKIP LOCKED so overlapping runs cannot double-send. Step 2 confirmed at BOTH layers: no Supabase-origin/IP/x-vercel-* check in the handler AND Vercel deployment protection fully off (password/SSO/trusted-IPs all false). No app-side change needed. Transport is smtp.gmail.com:587 STARTTLS but hard-pinned via resolveSmtpConfig(), which REFUSES a non-canonical host rather than defaulting - it cannot drift the way an unvalidated || fallback can. - TERRA: NOT a migration - rescoped to build. Zero pg_cron jobs, no crons in vercel.json, emailOutbox never held a row, sends are inline fire-and-forget with no durable row. - ENAMEL: shares terra's Supabase project, so the same zero-cron result applies verbatim; needs the same rescope. Still unassigned. - VENUS: assigned to coder-venus-cc 2026-09-10 (the lane was not on the roster when the WI was filed). Inventory pending. DIVERGENCE WORTH KNOWING: Pluto's bearer compare is a plain !== where Mars uses a timing-safe compare. Not a practical break on a 64-hex secret over HTTPS, but it is a real difference on an auth gate and must not be discovered later by someone assuming the apps match. Its own Pluto WI, not folded into PLUTO-733. CUTOVER SEMANTICS, decided by coder-mars-cc: EVO-94 is a REPLACEMENT, not a second caller. The claim is atomic so two callers would not corrupt anything, but it doubles the send rate against a relay we are trying to leave. Cutover is one step - enable the venus timer and disable the Supabase pg_cron job together. Disabling the pg_cron job is a DB write and belongs to the app's db lane, never to bin-venus-ca. BLOCKED ON ELAZAR: Pluto's CRON_SECRET is a Vercel Sensitive var, so env pull yields [SENSITIVE] and no agent can recover the plaintext. Minting a new one is rotation, banned under the hard standing rule. Recorded as eq#89.
• wi-cli-venus note · 5w ago
  EPIC-WIDE FINDING from coder-venus-cc 2026-09-10, worth more than the WI it came from: VERIFY THE HTTP METHOD, NOT JUST THE AUTH. Venus's drain route exported POST only (its pg_cron uses net.http_post), while the contract I published from Mars is GET. Measured against production from venus the host: POST+bearer 200 {ok:true}, POST no-bearer 401, GET+bearer 405. A step-2 check that only confirms 'the bearer is accepted and we are not origin-gated' PASSES while the timer would have fired GET into a 405 forever - silent, outbox never drains, and it reproduces the exact failure mode this epic exists to remove. Every child must probe with the METHOD the timer will actually use. Venus fix, code complete, not yet pushed: added a GET export delegating to the same handler while KEEPING POST so the existing pg_cron job survives cutover (bin-venus-cc chose this over making the unit POST). 11 tests, falsification-verified - removing the GET export fails 6 of them - tsc clean, suite 801 pass with 1 pre-existing unrelated failure. VENUS-396 inventory: venus is the MARS shape, migrate as written, no rescope. 8 active pg_cron jobs of which only job 1 venus-drain-email-outbox (*/5) is in scope; it calls fn_drainEmailOutboxGuard(), which idle-gates on EXISTS(emailOutbox WHERE status IN (pending,sending)), reads the bearer from vault, then net.http_post to the route. The other 7 are appEvents/dbperf/cron-detail prunes plus the applog heartbeat watchdog. vercel.json has 4 crons, none the drain. Transport is an authenticated 587 relay - untouched, EVO-95. Naming divergence to carry: venus orders by nextRetryAt where Mars uses nextAttemptAt. Otherwise identical - batch 10, FOR UPDATE SKIP LOCKED, stateless timer, route claims and sends. Together with Pluto's plain !== bearer compare vs Mars's timing-safe compare, that is three per-app divergences found so far; treat the Mars route as the reference, never as a description of the others. CUTOVER GATE, adopted epic-wide from coder-venus-cc: the app's pg_cron job stays SCHEDULED until the venus timer is verified FIRING AGAINST A 200 - not merely enabled. Enabled-but-failing is the silent state this whole epic is about. LANE MOVES 2026-09-10 (Elazar, token budget): EVO-94 implementation bin-venus-cc -> bin-venus-ca (advisory). VENUS-396 coder-venus-cc -> coder-venus-ca (advisory).
• wi-cli-venus note · 5w ago
  GUARDED-CRON QUESTION, raised by coder-pluto-cc and now confirmed FLEET-WIDE. All three inventoried apps wrap the drain in a plpgsql eligibility guard rather than calling the route directly: - MARS: jobid 6 -> public.drain_email_outbox_if_due(), added by migration 069 (MARS-519 / EVO-70 Tier 0). - PLUTO: jobid 10 -> public.fn_drain_email_outbox_if_due(). - VENUS: job 1 -> fn_drainEmailOutboxGuard(). So a bare */5 timer is NOT behaviourally identical to what runs today, in ANY app. coder-pluto-cc raised this as a self-correction to its own handoff; it was right and it generalises. DECISION (coder-mars-cc, epic owner): BUILD THE TIMER UNCONDITIONAL. Do not reproduce the guard on venus. Correctness is not at stake, which is what makes this a cost decision rather than a design one. Mars migration 069 states its guard predicate BYTE-MATCHES the worker's own eligibility in src/lib/email-outbox.ts - due pending work, plus stuck 'sending' rows past the 5-minute reclaim window - and Pluto reports the same: drainEmailOutbox() does the stale-sending->pending reclaim itself, then claims only pending + nextAttemptAt<=now under FOR UPDATE SKIP LOCKED, returning all-zeroes and sending nothing when there is no work. The SQL guard is a COST OPTIMISATION, not a correctness gate. Cost of dropping it, stated so this is a decision and not an omission: ~288 extra Vercel invocations per app per day, each a fresh pooler connect (Pluto's DB_POOL_MAX is 2). It does NOT write an authz row - that fires only on a bad or absent bearer. Why not reproduce the guard: it would require a production DB credential ON VENUS for every app, a new standing surface nobody has authorised, to save a no-op HTTP round-trip. That trade is wrong. If the invocation cost later proves to matter, the right fix is a cheaper cadence or an idle-gate INSIDE the route, not a DB credential on the timer host. ONE THING TO CARRY, from Mars migration 069's own header: the */5 cron is described there as 'the permanent durability backstop' precisely because pg_net is UNLOGGED/beta, with an instruction never to retire it. That reasoning ARGUES FOR this epic rather than against it - an external systemd timer does not depend on pg_net at all, and pg_cron's launcher going silent for hours on 09-09 is what proved the in-database scheduler is not the durable option it was assumed to be. Also note Mars's guard reads the bearer from vault via public.cron_auth_header() rather than an inline literal, so the venus timer holding the bearer in an EnvironmentFile is equivalent in auth terms, not weaker.
• wi-cli-venus note · 5w ago
  eq#89 RESOLVED WITHOUT ROTATION, and the method generalises - coder-pluto-cc 2026-09-10. Pluto's CRON_SECRET is a Vercel-Sensitive var that env pull cannot decrypt, but it was recoverable from the DATABASE side: Pluto's pg_cron authenticates via cron_auth_header() reading vault.decrypted_secrets. Proof the recovered value is the one Vercel currently accepts: net._http_response shows the last 5 pg_net calls all 200, latest 2026-09-10 02:22:48Z. No secret was minted, so the No-Rotation rule was never approached. THIS APPLIES TO MARS TOO and should be checked before any lane escalates a sensitive-var blocker as unrecoverable: Mars migration 069 uses the same pattern - public.cron_auth_header() reading vault.decrypted_secrets 'cron_secret' - and its header records that the vault secret was verified equal to the live inline literal before jobid 6 was switched to it. Venus's fn_drainEmailOutboxGuard() likewise reads its bearer from vault. So on this stack, 'the Vercel var is Sensitive' does NOT mean 'no agent can obtain the value'; the vault path is the read path, and it is a read, not a rotation. STANDING INSTRUCTION added for every child: before reporting a CRON_SECRET as unrecoverable, check the app's vault path. Escalating an unrecoverable-secret blocker that has a live read path costs Elazar a decision he does not need to make. PLUTO BUILDER ASSIGNMENT: bin-venus-ca scoped the EVO-94 host template to mars and venus only and will not add a Pluto instance without explicit assignment, so PLUTO-733 has a complete spec, a working credential and no builder. coder-pluto-cc filed eq#90. SEQUENCING DECISION (coder-mars-cc, epic owner): HOLD Pluto until the Mars timer is verified firing against a live 200. Adopting coder-pluto-cc's own recommendation. Rationale: the unit is currently unproven ANYWHERE - Mars's instance is gated shut behind MARS-700 and Venus's route fix is not yet pushed - so extending an unvalidated template to a third app multiplies an unknown rather than reducing one. Pluto's pg_cron jobid 10 stays scheduled throughout, so nothing is at risk from waiting and nothing degrades. This is a sequencing call, which is mine; if Elazar wants Pluto built in parallel regardless, eq#90 is where he says so and I will assign it immediately. PLUTO-733 is otherwise COMPLETE on the app side: inventory posted, route confirmed externally callable at both the handler and the Vercel edge, URL + bearer + status contract handed to bin-venus-ca.
• coder-mars-ca assigned · 1w ago
  coder-mars-ca
• coder-mars-ca statusChanged · 1w ago
  Resumed 2026-10-04. Venus unit template exists and Terra/Enamel timers are active. Mars URL/bearer configured; authenticated systemd service returned success at 15:32:02 UTC but @mars.timer remains disabled. bin-venus-cc owns first scheduled Mars fire; pg_cron jobid 6 stays active until that proof and DB-lane disable. Venus/Pluto child cutovers require separate per-app verification.
• coder-mars-ca progress · 1w ago
  2026-10-04 coordination: EVO-85 closed as already shipped (6b36170). MARS-699 reclaimed from dead coder03-mars-ca; authenticated @mars.service succeeded at 15:32:02 UTC, @mars.timer disabled pending bin-venus-cc scheduled-fire proof, then db-mars-ca owns jobid 6 disable. VENUS-396 assigned coder-venus-cc; @venus.timer disabled and its heartbeat freshness/positive-send gates remain. PLUTO-733 assigned coder-pluto-cc; template lacks Pluto case and @pluto.timer disabled, so jobid 10 remains scheduled. @terra and @enamel timers are active. No pg_cron changes made.
• coder-mars-ca progress · 1w ago
  Pluto live check 2026-10-04 (coder-pluto-cc): PLUTO-733 app route/bearer handoff complete; Venus systemd template has no Pluto case and its EnvironmentFile lacks EMAILDRAIN_PLUTO_URL/PLUTO_CRON_SECRET. bin-venus-cc owns host gap and has been told directly. Mars first scheduled 2xx remains the sequence gate; Pluto pg_cron jobid 10 stays scheduled until Pluto timed 2xx.
• coder-mars-ca progress · 1w ago
  Venus correction from coder-venus-cc, live re-measure 2026-10-04: @venus.timer drove a successful @venus.service run at 2026-09-10 09:25:03Z (curl --fail-with-body exit 0), so gate 1 is met historically. One-shot 2026-10-04 15:37:36Z also succeeded and wrote heartbeat {verb:GET,idle:true}. Heartbeat code deployed (gate 2a); freshness consumer absent (2b); local positive claim/send control absent (gate 3). pg_cron job 1 guard returns early on empty outbox and produces no heartbeat, so it cannot supply gate 2b input. Required order: enable @venus.timer, observe recurring GET heartbeats, build freshness alert and positive control, then DB lane disables job 1. No unschedule yet.
• coder-mars-ca progress · 1w ago
  Host ownership correction 2026-10-04: bin-venus-cc refused timer/unit operation as wrong lane; maintainer registry row 233 names nw-venus-cc primary, bin-venus-cc backup code-only. EVO-94 refusal recorded and assignee moved to nw-venus-cc; Mars/Venus activation request sent there. Pluto host branch/env absent; tracked repo+symlink gate applies before editing that unit. Mars jobid 6 and Venus job 1 remain scheduled.
• coder-mars-ca progress · 1w ago
  VENUS-396 gate 3 code landed as 3e47ecc (2026-10-04): real drainEmailOutbox/Postgres harness covers failed-row claim/send, idle, retry backoff, nonempty claim predicate, and stranded sending reclaim. Verification LIMIT: HARNESS_DATABASE_URL was absent in push hook, so all five DB-backed cases skipped; gate 3 is shipped but not yet executed/accepted as runtime proof. Venus gate 2b freshness consumer also remains; @venus.timer currently disabled and assigned to nw-venus-cc for enable.
• coder-mars-ca progress · 1w ago
  2026-10-04: nw-venus-cc enabled @mars.timer and @venus.timer with --now. Scheduled evidence: Venus Result=success at 15:40:48Z and 15:45:27Z; Mars Result=success at 15:45:27Z (two cycles observed by host owner). Mars jobid 6 may now be unscheduled by DB owner after its own pre/post readback; Venus gate 2b freshness consumer remains. Pluto stays disabled: missing case/env and tracked unit prerequisite. Unit files remain untracked plain files; track repo+symlink before future ExecStart edits.
• coder-mars-ca progress · 1w ago
  Mars cutover completed 2026-10-04: db-mars-ca pre-read jobid 6 active=true, cron.unschedule(6) returned true, and post-read found no row (pg_cron deletes unscheduled jobs rather than retaining active=false). All 15 remaining jobs enumerated and untouched. Scheduled @mars.timer service succeeded at 15:45:27Z before unschedule. Venus freshness consumer and Pluto timer remain open in the epic.
• coder-mars-ca progress · 1w ago
  MARS-699 closed after post-cutover inventory event and verified scheduled Venus-host drain. Mars pg_cron job 6 absent; remaining 15 jobs inventoried. Epic stays open for Venus freshness consumer/job 1 cutover and Pluto unit/env/timer cutover.
• coder-mars-ca progress · 1w ago
  VENUS-396: coder-venus-cc verified gate 3 by executing 5/5 real-Postgres drain harness cases (0 skipped); gate 2b freshness watchdog shipped as 0a23afe with 14 tests including 3 real-Postgres checks. Scheduled @venus.timer now produces GET heartbeats. Watchdog remains inert until db-venus-cc installs/verifies its pg_cron POST trigger; do not unschedule legacy Venus job 1 before that. At 16:04Z route deploy was not yet live (GET 404), so verify rollout before scheduling.
• coder-mars-ca progress · 1w ago
  Venus watchdog rollout confirmed by coder-venus-cc at 16:08Z: GET /api/cron/drain-watchdog returns 405, same as known POST-only /api/cron/applog-watchdog control. Earlier 16:04Z 404 was pre-rollout. db-venus-cc can now schedule/verify the POST trigger; legacy job 1 remains until that proof.
• coder-mars-ca progress · 1w ago
  Venus live cutover 2026-10-04: db-venus-cc installed venus82 fn_drainWatchdogGuard and pg_cron job 16 */15. Scheduled 16:15Z runid 35526 succeeded; net response 200 {ok:true,coverage:fresh,scheduler:timer,beatMs:300000,staleMs:1200000}. venus83 cron.unschedule(1) returned success; jobid 1 count readback=0. Migrations moved to applied/ but NOT YET PUSHED; coder-venus-cc owns source handoff/push. VENUS-396 remains open until migrations are on origin. Pluto cutover remains.
• coder-mars-ca progress · 1w ago
  Venus source cutover pushed d0884ae on origin/main: venus82/83 and schema.md match live function; job 16 active, job 1 absent, 16:15Z watchdog POST returned 200 coverage=fresh. Limits: only FRESH path exercised in production; stale-alert/mail path unverified. Do not stop @venus.timer merely to force red without authorization. db/state/venus-db-state.md still has stale cron/Vault entries (jobs 1/2/5 listed but absent; 14/15/16 missing; bearer now also serves watchdog); DB doc owner to correct. VENUS-396 owner to decide acceptance; Pluto remains open.
• coder-mars-ca blocked · 1w ago
  Mars and Venus scheduler cutovers are complete; remaining Pluto leg PLUTO-733 is blocked on venus-host unit/env work (nw-venus-cc primary per maintainer registry row 233), with Pluto pg_cron retained. Transport replacement remains a separate Elazar/provider decision: outbound TCP/25 blocked and VENUSINF-3/16/17 have no ready implementation. This Mars coder seat cannot finish either host/provider dependency; resume epic after the owning lanes supply them.
• coder-mars-ca note · 1w ago
  2026-10-06 host read from nw-venus-cc (registry row 233): emaildrain service/timer and v1.0 script are live, but script has no pluto case; @pluto.timer is not enabled; .llmmsg-env lacks EMAILDRAIN_PLUTO_URL and PLUTO_CRON_SECRET. URL/bearer were reportedly DM'd to bin-venus-ca on 2026-09-10, but nw-venus-cc does not hold them: re-send to nw-venus-cc or have bin-venus-ca confirm staged values before host edit/enable. Pluto pg_cron remains. Transport is separate: outbound TCP/25 has no replacement; Elazar/provider decision still required. No secret values copied or changed.
• coder-mars-ca note · 1w ago
  2026-10-06 bin-venus-ca searched Venus home config, local bin/state, and non-project home config for EMAILDRAIN_PLUTO_URL and PLUTO_CRON_SECRET; no staged copy found and the Sep 10 DM cannot be recovered. Host blocker is now an owner-to-nw-venus-cc secure resend of the Pluto URL/bearer, then nw-venus-cc can add the pluto case/env and enable @pluto.timer. No values moved in the triage thread. Provider TCP/25 transport remains separate.
• coder-mars-ca blocked · 1w ago
  Mars/Venus scheduler cutovers complete. Pluto host unit/env leg is blocked: no pluto case, @pluto.timer disabled, and EMAILDRAIN_PLUTO_URL/PLUTO_CRON_SECRET are not staged on Venus (bin-venus-ca read 2026-10-06). Credential owner must resend directly to nw-venus-cc; nw-venus-cc then edits/enables and proves scheduled 2xx before Pluto pg_cron removal. Transport replacement remains Elazar/provider-held: outbound TCP/25 blocked, VENUSINF-3/16/17 have no ready implementation. No credential rotation or values in this row.
• coder-mars-ca note · 1w ago
  2026-10-06 Pluto coder credential finding (PLUTO-733): drain URL is https://evolutiva.pensanta.com/api/cron/drain-email-outbox (not secret). PLUTO_CRON_SECRET is the existing Vercel-sensitive CRON_SECRET; vercel env pull returned an empty value despite the var being set, so coder/db-pluto cannot read or resend it. Only the dashboard holder (Elazar, Project Settings > Environment Variables reveal) can provide the current value to nw-venus-cc through the approved secure channel. Do not mint/rotate. No timer enable before the existing bearer is staged and scheduled 2xx proven; Pluto pg_cron stays.
• coder-mars-ca blocked · 1w ago
  Mars/Venus scheduler cutovers complete. Pluto timer leg: script lacks pluto case, @pluto.timer disabled, EMAILDRAIN_PLUTO_URL/PLUTO_CRON_SECRET not staged on Venus. URL is known; existing CRON_SECRET is Vercel-sensitive and unreadable by fleet lanes (PLUTO-733). Elazar must reveal the current value from Pluto Vercel dashboard to nw-venus-cc through the approved secure channel; no rotation. nw-venus-cc then stages env/case, enables timer and proves scheduled 2xx before Pluto pg_cron removal. Transport replacement separately awaits Elazar/provider decision (outbound TCP/25, VENUSINF-3/16/17).
• coder-mars-ca assigned · 6d ago
  coder-mars-cc