Outbox durable y timer externo de Terra operativos

Sub-tickets

Activity

• wi-cli-venus created · 5w ago
• wi-cli-venus note · 5w ago
  Inventory returned by coder-terra-cc 2026-09-10: cron.job on eprpywqwzsvrjeaforei has 0 rows (no pg_cron job at all, terra or enamel); vercel.json has no crons key; transport is nodemailer on an authenticated 587 relay via readMailConfig(), single chokepoint sendGuarded(). public.emailOutbox and public.appEventRelays exist in the shared schema but have never held a row and no terra code path references them - src/lib/access-notify.ts states in its header that it ships as attempted notification, never durable delivery, with no outbox, no retry, no queue. Terra sends inline, in-request, from the server action. So the pg_cron migration premise does not hold here: there is nothing to migrate. Steps 2 and 3 of the original scope are unexecutable - no drain route to bearer-auth, no URL to hand bin-venus-cc. RESCOPED (epic-owner call, coder-mars-cc): this WI becomes BUILD terra's emailOutbox + drain route to the same contract the other apps already satisfy, then hand the route to bin-venus-cc for an evolutiva-emaildrain@terra timer. Not closed as not-applicable: terra's exposure is worse than the one EVO-93 was opened for - a fire-and-forget send inside the request loses the notification with no row and no retry, where a pg_cron-scheduled drain at least leaves a durable pending row. It is a build, not a migration, and it does not block EVO-94. Blocked on an unrelated outage first: PostgREST on this project has been 503 PGRST002 for ~31h because the project's API > Exposed schemas setting is empty. No agent holds that dashboard control; escalated to Elazar.
• wi-cli-venus note · 5w ago
  TERRA-209 BUILD COMPLETE IN-TREE, UNPUSHED (marker: terra209-build-mtv6a). 490/490 node --test, tsc clean. FILES. New: src/lib/email/outbox-core.ts (pure, zero imports - backoff, MAX_ATTEMPTS, reclaim lease, sendDurable, drainOnce, authorizeDrain); src/lib/email/outbox.ts (the five pool statements); src/lib/email/drain-route.ts (route body); src/app/api/cron/drain-email-outbox/route.ts; src/lib/email/outbox-core.test.ts (18 tests). Modified: src/lib/access-notify.ts (enqueue-always at the sendGuarded seam, header corrected - it predicted this seam and the prediction held); src/lib/access-notify-core.ts (tenth outcome queuedForRetry); src/lib/proxy-session.test.ts (api-route census answered with a decision, not an append). CONTRACT is Mars-supplied, read off mars/src/app/api/cron/drain-email-outbox/route.ts, not invented: GET, Authorization: Bearer <CRON_SECRET> compared timing-safely, 401 mismatch / 503 secret-unset, 200 {ok:true,...} / 500 {ok:false,error}, runtime nodejs, force-dynamic, maxDuration 60, batch 10 by nextAttemptAt, ROUTE CLAIMS AND SENDS. Failed runs also write appEvents category=cron action=drain-email-outbox-failed - Mars found the 500 alone was a blind spot. NO MIGRATION, NO GO NEEDED. Measured before writing: emailOutbox has relrowsecurity=false, INSERT to postgres+service_role, 0 user triggers, ix_emailOutbox_drain present. Terra already writes the equally-shared appEvents by direct pool INSERT with appKey (src/lib/logger.ts), so enqueue follows the established precedent. Also verified category value cron EXISTS in lookupOptions appEventCategory (17 values) - Mars vocabulary that happens to be valid here, checked rather than assumed, because the emailOutbox schema itself did NOT transfer (Mars runs a different project; ours is 23 columns with no dedupeKey). DESIGN POINTS WORTH REVIEW. 1. Enqueue-always, never enqueue-on-failure. The row commits before the socket opens. Venus built enqueue-on-failure and records it as their top do-differently. 2. Inline send STAYS. Per Mars: sending in-request is not the defect - Mars does it too via after() and treats the cron as a BACKSTOP. The defect was the absence of a durable row behind it. 3. RECLAIM. First draft was wrong and the fix is the design: the inline path claims BEFORE sending, so a process dying mid-send left the row claimed and a pending-only drain would never see it again - the exact stranding the outbox exists to prevent, reintroduced by its own bookkeeping. claimBatch now also takes claimed rows whose claimedAt is older than a 10-minute lease. Reclaiming early would double-send a merely-slow mail, so the boundary is asserted from both sides. 4. Terminal rows keep their claim token, rows returning to pending release it - forced in opposite directions by ck_emailOutbox_terminal_keepsToken and ck_emailOutbox_pending_unclaimed, with ck_emailOutbox_claim_paired tying claimedAt to the token. 5. queuedForRetry is the tenth notify outcome and the first that is not a failure: the row exists and the drain owns the send. Folding it into smtpError would file a work item against a send that will succeed on its own. COVERAGE STATEMENT. The five SQL statements in outbox.ts have ZERO execution coverage - I am read-only and PostgREST is down, so nothing has run them. Unverified specifically: $3 used twice in markFailed (SET and CASE) resolving cleanly, JS array to recipientsTo[], and the CTE FOR UPDATE SKIP LOCKED feeding UPDATE...FROM due on the transaction pooler. Requesting db-terra-cc run these as a BEGIN/ROLLBACK dry-run under db-terra/evidence/ before anyone trusts them. The pure core is fully exercised; the SQL is not. PRODUCERS, stated plainly because it changes what shipping means: exactly ONE. /sin-acceso -> AccessRequestForm -> requestTerraAccess -> notifyAccessRequest now enqueues. The path that is broken LIVE right now - /solicitar-acceso and /identidad-en-revision -> reportAccessIssue -> fn_reportAccessIssue -> accessIssueReports - does NOT notify at all and is untouched by this WI. TERRA-59 notification gap remains open; COORDINATION_GROUP is still unresolved, so even the wired path resolves zero recipients today. RETENTION, for pm/Elazar not for me: sent rows retain recipientsTo and bodyText, i.e. a requester address and their free-text reason, in a SHARED co-tenant table with no purge job. That is a policy decision, not a coding one. Flagging rather than inventing a TTL. STALE PROSE left deliberately: src/lib/email/transport.ts still says terra ships attempted-notification-not-durable in two places. Now false. Not edited in this pass to keep the diff reviewable; worth a follow-up. BLOCKED ON, neither mine: (a) db/migrations/applied/terra_210_terra_auth_question_views.sql still untracked, so .gitpush-pre.sh refuses every push by every agent in the repo; (b) CRON_SECRET does not exist in Vercel yet - until it is set, every timer fire is a 503 plus an error row. Handoff order once unblocked: set CRON_SECRET project-scoped, ship, PTD, curl once with a wrong bearer expecting 401 and once correct expecting 200, THEN DM bin-venus-cc the URL and bearer. Not handing bin-venus-cc a route that answers 503.
• wi-cli-venus assigned · 5w ago
  coder02-terra-ca
• wi-cli-venus audit-finding · 5w ago
  db-terra-cc dry-run of the five outbox.ts statements, marker terra209-build-mtv6a, BEGIN/ROLLBACK, zero residue verified (0 fixture rows, 0 leftover prepared statements). ONE BLOCKING DEFECT: claimBatch fails at PARSE time with 42702 column reference "id" is ambiguous - UPDATE "emailOutbox" o ... FROM due ... RETURNING ROW_COLUMNS is unqualified and both o and due expose id, so EVERY call throws and the drain has never dequeued anything. Invisible to tsc and to unit tests because the string is only a string until Postgres parses it. Fix verified in the same run: qualify the RETURNING list with o. (ROW_COLUMNS has exactly one call site). The three named risks are all CLEAN against the fixed form: markFailed's twice-bound $3 infers once to text and both branches discriminate correctly (claimToken nulled on pending, retained on failed, matching ck_emailOutbox_pending_unclaimed and ck_emailOutbox_terminal_keepsToken); the JS array binds as a real 2-element text[]; the CTE with FOR UPDATE SKIP LOCKED feeding UPDATE...FROM is a single statement so the transaction pooler is not an issue, and it claimed both populations 2/2 (one due, one reclaim-expired at -30min). markSent and claim both correct. NOT ESTABLISHED: concurrency - SKIP LOCKED was never exercised against a second live session, so disjoint batches remain unproven. Evidence: agents/db-terra/evidence/2026-09-10-terra_209-outbox-dryrun.result.md
• wi-cli-venus note · 5w ago
  OPEN RISK CARRIED FORWARD, not a status update (marker: terra209-concurrency-mtv8h). 42702 is FIXED in the working tree by coder-terra-cc: ROW_COLUMNS at src/lib/email/outbox.ts:47 is now qualified with o. on all eleven columns, the comment marks the alias load-bearing, one call site (the RETURNING at :161). 490/490, tsc clean - which is exactly as meaningless for that class of defect after the fix as it was before it, and the comment says so. THE PROPERTY THIS DESIGN EXISTS TO GUARANTEE IS STILL UNMEASURED. Two concurrent drains taking the same batch would DOUBLE-SEND. That is the single failure the claim/lease machinery is built to prevent, and it is the one thing nobody has demonstrated. db-terra-cc established, and this is a tooling limit rather than an oversight: SKIP LOCKED needs two connections holding row locks simultaneously. execute_sql is one implicit transaction per call and calls are sequential, so a BEGIN never survives to the next call and two open transactions cannot coexist. One session can only show that one drain takes rows - which the dry-run already showed. The two ways out are both refused on purpose: dblink is AVAILABLE but NOT INSTALLED on this project, and CREATE EXTENSION is DDL on a shared co-tenant project plus an extension that opens outbound connections, so it is a db-enamel-cc GO and an Elazar call; psql from two shells is barred by the fleet-wide SELECT-only rule, and a rolled-back UPDATE is still an UPDATE. The dblink question is with Elazar. DO NOT CLOSE THIS AS FINE BECAUSE SKIP LOCKED IS STANDARD. That is a claim about the clause, not a measurement of this statement. State it as UNPROVEN until somebody with two live write sessions runs it. Second unproven item from the same dry-run, same discipline: arm 7 showed the statements created zero non-terra rows, which does NOT show the appKey predicates would EXCLUDE an enamel row, because there was none to exclude. appEvents and emailOutbox are both shared, and a missing appKey filter reading another app's rows is the failure that has already broken three conclusions on this lane. WHY THIS EVENT EXISTS AT ALL. The dry-run moved claimBatch from "unexercised" to "provably broken and now fixed", which is a real gain and also the hazard: a statement that parses and passes seven arms LOOKS finished. Whoever ships this should see both gaps before it goes out, not after.
• wi-cli-venus titleChanged · 5w ago
  Terminar y operar el outbox durable de Terra con drain externo desde Venus; no existe pg_cron que migrar
• wi-cli-venus descriptionChanged · 5w ago
  was: 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.
• wi-cli-venus statusChanged · 5w ago
  Rescopado de migracion inexistente a build/ship/operacion del outbox durable; codigo local existe pero deploy, secreto, ejercicio de ruta y timer Venus siguen pendientes.
• wi-cli-venus verified · 5w ago
  Metadata reconciliada por coder02-terra-ca contra fuente local y dry-run DB02; separa codigo, DB, deploy y comportamiento ejercitado.
• wi-cli-venus descriptionChanged · 5w ago
  was: ALCANCE ACTUAL, RECONCILIADO 2026-09-10. El inventario invalido la premisa original: cron.job tiene cero filas y vercel.json no define crons, por lo que no hay launcher pg_cron para migrar. CODIGO LOCAL NO SHIPPED: el working tree agrega enqueue-always, claim con lease y reclaim, markSent/markFailed, drainOnce y GET /api/cron/drain-email-outbox protegido por CRON_SECRET; notifyAccessRequest usa ese outbox y mantiene el envio inline. La suite completa quedo 499/499, typecheck limpio y lint sin errores con 4 advertencias preexistentes. DB DRY RUN: DB02 encontro y la fuente corrigio un RETURNING ambiguo 42702; el formulario corregido paso BEGIN/ROLLBACK sin residuo. LIMITES DE EVIDENCIA: la exclusion entre apps y dos drains concurrentes no fueron ejercitados con sesiones simultaneas; la ruta no esta desplegada ni probada con bearer incorrecto/correcto; CRON_SECRET no esta configurado en Vercel; Venus aun no tiene el timer. BLOQUEO DE SHIP: db/migrations/applied/terra_210_terra_auth_question_views.sql esta aplicado pero no versionado y el pre-push congela el repo hasta una decision de procedencia. TRABAJO DE CODIGO PENDIENTE: corregir el comentario obsoleto de transport.ts, completar pruebas razonables de aislamiento y concurrencia sin inventar evidencia, y dejar el route contract listo para auditoria. HANDOFF POST-SHIP: despues de PTD y curls 401/200, entregar a bin-venus-cc URL y bearer solamente por DM para el timer evolutiva-emaildrain@terra. No incluir el secreto en BWI, commit ni logs. La retencion de recipientsTo y bodyText requiere politica humana y no se inventa aqui.
• wi-cli-venus verified · 5w ago
  Independent audit PASS 2026-09-10 (audit-terra-ca, tag audit-terra-ca-mtvnp4fj4iqj) at current source/BWI boundary. Excludes commit, deploy/PTD, authenticated-browser/live-email, concurrent-drain, and rerun of claimed 509-test gate.
• wi-cli-venus progress · 5w ago
  Scoped source shipped in d3ea382 as v0.31.50; CRON_SECRET is configured in Vercel production scope and deployment dpl_GKQxq7iwvYsxX2x4DEd3V4MqWuJs is building. 401/200 route exercise and Venus timer handoff remain pending.
• wi-cli-venus descriptionChanged · 5w ago
  was: ESTADO VERIFICADO 2026-09-10. CODIGO LOCAL NO SHIPPED: outbox durable con enqueue antes del socket, claim por appKey y lease/reclaim, markSent/markFailed, backoff acotado, drain batch y /api/cron/drain-email-outbox protegido por CRON_SECRET. Contrato declarado correctamente como entrega al menos una vez: aceptar SMTP y fallar markSent puede producir reenvio; se prefiere duplicado a perdida. Todos los fallos de enqueue, claim, send, settle y log se absorben segun contrato y estan probados; una fila rota no aborta las siguientes. El comentario de reclaim y transport quedaron reconciliados. EVIDENCIA: auditor independiente PASS y ejecuto suite 507/507; gate combinado posterior: focales 58/58, typecheck PASS, suite 509/509, lint 0 errores y 3 warnings preexistentes. DB02 hizo dry-run rollback sin residuo. NO SHIPPED/NO OPERADO: falta resolver TERRA-211, commit/deploy/PTD, configurar CRON_SECRET y entregar URL+bearer por DM a bin-venus-cc para el timer. No hay pg_cron que migrar. Retencion de recipientsTo/bodyText queda como politica humana.
• wi-cli-venus verified · 5w ago
  Post-deploy audit PASS: exact SHA d3ea382, runtime v0.31.50, health dbOk/mailOk, wrong bearer 401, public image proxy 404 parity. Empty correct-bearer drain remains wiring-only evidence.
• wi-cli-venus titleChanged · 5w ago
  Outbox durable desplegado; falta habilitar/verificar el timer externo de Venus
• wi-cli-venus titleChanged · 5w ago
  Outbox durable y timer externo de Terra operativos
• wi-cli-venus descriptionChanged · 5w ago
  was: ESTADO ACTUAL 2026-09-10. SHIPPED: d3ea382, v0.31.50, deployment dpl_GKQxq7iwvYsxX2x4DEd3V4MqWuJs Ready. Outbox durable implementa enqueue previo al socket, claim por appKey con lease/reclaim, settle, backoff y entrega al menos una vez; /api/cron/drain-email-outbox esta protegido por CRON_SECRET. GATES: 509/509 tests, typecheck PASS, build PASS, lint 0 errores y 3 warnings; auditor independiente PASS post-deploy. RUNTIME EJERCITADO: bearer incorrecto devuelve 401 y bearer correcto devuelve 200 con claimed:0; eso prueba ruta, auth, verbo, red y configuracion, no procesamiento SMTP ni cola no vacia. CRON_SECRET esta configurado y fue entregado privadamente, sin argv/log/BWI/ARO, al carril bin. PENDIENTE OPERATIVO: habilitar/verificar la instancia Terra/enamel de la plantilla evolutiva-emaildrain existente en Venus. SIGUE NO PROBADO: dos drains concurrentes con SKIP LOCKED, fixture cross-tenant y una entrega real no vacia. Retencion de recipientsTo/bodyText es decision humana.
• wi-cli-venus completed · 5w ago
  Completado: codigo, deploy y unica instancia externa @terra activos; auditorias de app y host PASS. Empty-path solamente, sin afirmar entrega no vacia.
• wi-cli-venus verified · 5w ago
  Final independent audit PASS 2026-09-10: evolutiva-emaildrain@terra.timer enabled and active/waiting, last service success/exit 0; empty-queue and non-empty/concurrency boundaries preserved.
• wi-cli-venus verified · 5w ago
  Scheduled timer invocation independently observed at 2026-09-10 18:45:03 UTC: evolutiva-emaildrain@terra.service Starting and Finished with Result=success and exit status 0; timer remained enabled active/waiting. Queue was empty, so this proves scheduled authenticated wiring only, not SMTP delivery or non-empty processing.
• wi-cli-venus verified · 5w ago
  MEDICION SUPAVISOR :6543 (pooler de TRANSACCIONES, el DATABASE_URL de src/db/pool.ts) - marker SUPAGUC-mtwr7k. A PASA, B PASA, NO hay incidente, no hay fuga de identidad. Evidencia: agents/db-terra/evidence/2026-09-11-supavisor-txn-guc.result.md. A) BEGIN; set_config('request.jwt.claim.sub','<uuid real de auth.users>',true); SELECT auth.uid() -> devuelve el uuid, no NULL. Esa es exactamente la forma que usa la app: access-issue-actions.ts:82-90 saca un cliente del pool, hace BEGIN explicito, el set_config y la llamada a la funcion en ese mismo cliente. fn_reportAccessIssue NO levanta 28000 por este camino y el sintoma de Laura no nace aca. A2, el contraste que importa: mismo backend (pid 466130), sin BEGIN, dos statements en autocommit -> auth.uid() da NULL. O sea el BEGIN es load-bearing, no adorno: sin el, 28000 para todo el mundo. B) 30 transacciones limpias seguidas sin set_config, TODAS en el pid 466130 - el mismo backend que acababa de tener el claim sembrado - las 30 dan NULL. Mas 15 transacciones desde 5 conexiones cliente separadas, tambien todas en el pid 466130, todas NULL. La columna pid es el punto: 30 NULLs en 30 backends distintos no probarian nada sobre reuso; estos reusaron el backend sembrado y igual no vieron identidad. Mecanismo, no suerte: set_config(...,true) lo revierte Postgres en el COMMIT/ROLLBACK, antes de que el backend vuelva al pool, asi que Supavisor no puede filtrar lo que Postgres ya descarto. LO QUE ESTO NO PRUEBA: que Supavisor resetee estado de sesion entre clientes - B mide semantica de Postgres, no higiene del pooler. La variante peligrosa es un set a nivel SESION (el tercer argumento caido, o un SET pelado): en modo transaccion Supavisor NO resetea GUCs de sesion entre clientes, y esa si podria darle a B el auth.uid() de A. NO la medi a proposito: sembrar un claim de sesion en el pooler compartido puede hacerselo a un usuario real concurrente. Queda como hipotesis, y la mitigacion es regla de codigo: el 'true' es obligatorio siempre. Tampoco ejercita fn_reportAccessIssue en si, ni despeja /solicitar-acceso punta a punta (PostgREST seguia 503 PGRST002). SKIP LOCKED sigue sin medir, excluido por pedido. claimBatch tampoco se corrio aca: por psql seria un UPDATE y la regla de flota es SELECT-only; si se quiere, va en un dry-run aparte por execute_sql.
• wi-cli-venus assigned · 5w ago
  coder-terra-cc
• wi-cli-venus audit-finding · 5w ago
  ENAMEL-16's DRAIN HALF DOES NOT EXIST IN THIS DATABASE - marker OUTBOX209-nq4v7p, measured by db-terra-cc 2026-09-11. Does not reopen this row: terra's half is shipped and works, and nothing here is terra's to fix. WHAT THE TABLE SAYS VS WHAT IS THERE. obj_description on public."emailOutbox" reads 'Durable outbound-email queue drained out-of-band by pg_cron (ENAMEL-16) ... this is a co-tenanted table'. Measured live: SELECT * FROM cron.job returns ZERO ROWS. pg_cron is installed at 1.6.4 with nothing scheduled. A pg_proc sweep across EVERY schema for prosrc ILIKE '%emailOutbox%' is likewise empty - no function, view or rule anywhere in the database touches the table. The documented drain mechanism is absent, not merely idle. CONVERGING EVIDENCE FROM THIS ROW'S OWN DESCRIPTION: the only enabled drain instance on venus is evolutiva-emaildrain@terra.timer, and '@enamel' was deliberately not created because APP_KEY=terra. So there is no enamel drainer in pg_cron, none in the database, and none on the venus timer host. CONSEQUENCE: any row enamel enqueues into this outbox is never picked up by anything. emailOutbox currently holds ZERO rows at any appKey and appEvents holds zero outbox/drain/claim actions, so nothing is stranded today and there is no backlog to recover - this is a latent gap, not an incident. It becomes data loss in the user-visible sense (mail silently never sent, with a durable row asserting it was queued) on enamel's first enqueue. SIDE EFFECT WORTH RECORDING: the 5-minute reclaim horizon in the table comment documents a reclaimer that does not exist, so terra's 10-minute STALE_CLAIM_MS has no counterparty to race. coder-terra-cc's decision to keep 10 and diverge loudly rather than match the comment is correct and now unopposed - reclaiming late only delays a mail, reclaiming early double-sends one that was merely slow. He has also bound the SQL interval to the constant via make_interval, closing a real drift: the horizon was typed twice (constant plus a literal interval '10 minutes') with nothing tying them together, so changing the constant would have left the statement on the old horizon with every test still green. COVERAGE BOUND, material: this proves no DB-RESIDENT drainer. An enamel APP-SIDE drainer on their own scheduler would be invisible from this lane, the same limit that applies to the userEmails sweep. What is disproven is the comment's specific pg_cron claim, which is checkable and false. DISPOSITION: escalation to Elazar with roster evidence, NOT a request filed at db-enamel-cc. roster(db-enamel-cc) returns zero rows and the 49-agent registry contains no enamel agent of any role, so there is no address to ask. Same disposition as TERRA-215.