ADR 0002 — PostgreSQL on shared Supabase Sandbox

Status

Accepted

Context

MAIL-72 needs a tested PostgreSQL migration path and environment configuration for LOCAL / PREVIEW / PRODUCTION. Inbox Agent shares a Supabase project with other work. Sprint 01 also needs a queue decision so later tickets do not invent Redis or SQS.

Decision

• Host PostgreSQL on the shared Supabase project Sandbox (ref hijznwisiirxdknbnvyz, host db.hijznwisiirxdknbnvyz.supabase.co).
• Place Inbox Agent objects in schema inbox_agent. Do not drop or alter unrelated schemas or tables.
• Enable row-level security on Inbox Agent tables. Do not expose inbox_agent on the Supabase Data API in Sprint 01.
• Use a repository-owned migration runner over database/migrations/*.sql, recorded in inbox_agent.schema_migrations.
• Fail closed when required process environment is missing: APP_ENV, DATABASE_URL, TOKEN_ENCRYPTION_KEY, SESSION_SECRET.
• Sprint 01 queue technology is a PostgreSQL transactional outbox only. No Redis or SQS. The outbox table is not created in this ADR; later tickets add it after the runner exists.
• MAIL-72 bootstrap migration creates only the schema and history table. MailMessage and other domain entities are out of scope.

Alternatives considered

• Dedicated Supabase project or Render Postgres: rejected for Sprint 01; Sandbox is the approved shared host.
• Redis / SQS for jobs: rejected for Sprint 01; keep the queue in the same transactional store as domain writes.
• Cloud-provider migration history (supabase_migrations) as the app source of truth: rejected. The shared project already has other apps; Inbox Agent tracks its own history in inbox_agent.schema_migrations.

Consequences

• CI and local environments apply the same SQL files. npm run db:migrate requires DATABASE_URL. npm run migrate:ci uses DATABASE_URL when present, otherwise an ephemeral PGlite database so a fresh database still reaches head without Stream A workflow edits.
• Later Stream B tickets (MAIL-73, MAIL-74, MAIL-101) add tables inside inbox_agent as numbered SQL files.
• Shared-sandbox operators must not run destructive SQL outside inbox_agent.