Database Schema

Drizzle source: src/lib/db/schema/*.ts. Dialect: Postgres. 32 tables, grouped below by domain.

Table inventory

Auth + identity

• users — accounts, password hashes, OAuth identity, session_version for revocation.
• organizations — top-level account. stripe_customer_id for billing.
• org_members — many-to-many membership with role (owner / member).
• user_sessions — per-device session rows; revocation drops the JWT.
• two_factor_secrets — TOTP secret + bcrypt-hashed backup codes (1-to-1 with users).
• email_verifications — signup 6-digit email codes (code_hash + attempts, 10 min, token in the sendoka_ev cookie) and legacy single-use 24h link tokens. Reaped after 7 days by /api/cron/retention.
• login_codes — emailed 6-digit sign-in codes; sha256 hash, attempts cap, superseded on re-request.
• phone_verifications — signup SMS codes; same three defences as login_codes, and the row id doubles as the pending-session token the sendoka_pv cookie carries. Reaped after 7 days by /api/cron/retention.
• trusted_devices — opaque per-browser tokens that skip the emailed code; 30 days after a full sign-in, 1 hour (VERIFICATION_TRUST_TTL_MS) when minted by a verification step to carry its hand-off.
• password_resets — single-use 1h tokens; bumps session_version on use.
• team_invites — 7d invite tokens unique on (org_id, email).
• api_keys — sha256 hash + last four, live/test, per-key rate limit + IP allowlist + scopes + expiry.

Tenants (platform mode)

• tenants — sub-orgs inside a platform-style customer. status, optional monthly_email_cap / monthly_sms_cap.
• tenant_usage — per-tenant counters by (tenant_id, channel, environment, period).

Sending — outbound

• messages — every outbound email + SMS. headers jsonb carries List-Unsubscribe.
• message_events — opens, clicks, unsubscribe pings.
• templates — reusable bodies keyed by (org_id, tenant_id, slug).
• template_versions — immutable snapshots per template revision.
• audiences — named recipient lists (+ contacts, audience_members).
• contacts — recipient records with metadata; tenant-scoped.
• audience_members — composite PK (audience_id, contact_id).
• messaging_pools — SMS sending pools across owned numbers.
• suppressions — bounce / complaint / unsubscribe / STOP list, per-org + per-tenant scoped.

Sending — inbound

• inbound_messages — received emails (and SMS replies); supports threading via in_reply_to / conversation_id.

Domains + SMS registration

• domains — SES DKIM identities, region, warmup.
• brands — 10DLC sender brand registration.
• campaigns — 10DLC + toll-free campaigns linked to a brand.
• phone_numbers — owned senders. Two kind values: number (E.164, US/CA via AWS provisioning, populates e164) and alphanumeric (UK/AU/EU sender ID, populates sender_id, no AWS round-trip). e164 is nullable; uniqueness enforced per kind via (org_id, e164) and (org_id, sender_id).

Webhooks

• webhook_endpoints — customer-registered receivers with HMAC secret + rotation grace.
• webhook_deliveries — one row per fanout attempt, retried by cron.

Billing + usage

• usage — monthly counters per (org_id, channel, environment, period).
• api_key_usage — per-key per-month rollups; powers per-key dashboards.

Operations

• audit_logs — append-only activity log.
• idempotency_keys — 24h replay store for single-send endpoints.
• daily_stats — nightly rollup of messages + events.
• health_probes — Postgres + Redis liveness samples (every 5 min); 30d retention.
• org_exports — async export job queue; processed by /api/cron/run-exports.

Highlights

Tables

users — users.ts

organizations — organizations.ts

org_members

Composite unique on (org_id, user_id). Multi-org supported.

api_keys — api-keys.ts

messages — messages.ts

Status values: queued | scheduled | sending | sent | delivered | bounced | failed | canceled. sending is the cron's atomic-claim marker; stale rows (> 5 min) are reclaimable.

Indexes: (org_id, created_at), (org_id, channel), (provider_message_id), (status), (status, scheduled_at).

domains

Unchanged — SES DKIM verification records.

webhook_endpoints — webhooks.ts

webhook_deliveries — webhook-deliveries.ts

One row per fan-out attempt. Powers retry cron.

Indexes: (status, next_attempt_at), (endpoint_id, created_at), (org_id, created_at).

audit_logs — audit.ts

Append-only org activity. Indexes on (org_id, created_at) and (action).

usage_records

Monthly counters per (org_id, channel, environment, period). UPSERT increments by by (default 1). Batch sends increment by successCount.

overage_reports — overage-reports.ts

Durable ledger of what report-overage has already metered to Stripe. Unique on (org_id, period, channel); status is pending / reported / failed.

The cron claims a row before calling Stripe and flips it to reported only on success, so a period can never be billed twice. The deterministic overage:{org}:{period}:{channel} meter identifier is not sufficient on its own — Stripe's identifier de-duplication only covers a rolling ~24h window, and the 0 6 1-5 * * schedule spans five days. A row stuck in pending (run died mid-Stripe-call) is never re-fired automatically; it surfaces in the run's stuck list for manual reconciliation.

idempotency_keys

key PK, (org_id) index. 24h TTL. Cleanup via /api/cron/cleanup-idempotency.

email_verifications — email-verifications.ts

Token PK, FK to users, indexes on user_id and created_at. Single-use via used_at. One row carries ONE proof, and code_hash says which:

• code row (code_hash set) — signup's 6-digit code. Only sha256(code) is stored, attempts caps guesses at 5 inside a 10-minute expires_at, and a new send supersedes prior rows. The token never leaves the server: it lives in the httpOnly sendoka_ev cookie and names the row, exactly as phone_verifications ids do.
• link row (code_hash NULL) — the legacy 24h click-through token, mailed in a URL.

verifyEmailCode refuses link rows and consumeVerificationToken refuses code rows. Reaped after 7 days by /api/cron/retention.

The bare created_at index (migration 0046, twinned on phone_verifications) is what that sweep runs on. It filters on created_at alone, so the user_id index cannot serve it and neither can the phone table's composite, whose created_at is its second column — both sweeps seq-scanned tables that grow a row per signup and per resend. Since each DELETE is capped at 20k rows a run, a scan that outgrows the Neon statement timeout stops the sweep making progress at all rather than merely slowing it.

password_resets — password-resets.ts

Token PK, FK to users. 1-hour TTL, single-use via used_at.

phone_verifications — phone-verifications.ts

pvc_ PK, FK to users, indexes (user_id, created_at) and (created_at). Signup SMS codes: only sha256(code) is stored, attempts caps guesses at 5 inside the 10-minute TTL, and requesting a new code supersedes the prior one. phone is stored per row, not read from users.phone at verify time, so changing the pending number cannot retroactively validate a code texted to a different one.

Two things about this table are load-bearing beyond the code itself:

• The PK is the pending-session token. The sendoka_pv cookie carries this id, so it names a row that exists, expires and can be revoked — rather than a user id, which is not secret and which anyone could therefore forge.
• attempts is only ever incremented by the database, in one statement with the cap in the WHERE. Read-modify-write does not hold under concurrency.

Nothing else reaps these rows — an abandoned signup can never sign in, so the session-authenticated deletion route cannot reach them — so /api/cron/retention deletes them after 7 days.

team_invites — team-invites.ts

Token PK, 7d TTL. Unique (org_id, email).

templates — templates.ts

Reusable message bodies keyed by (org_id, slug). Channel: email | sms.

two_factor_secrets — two-factor.ts

Per-user TOTP secret + backup codes. PK is user_id (1-to-1).

Relationships

users ──1──owns──> organizations ──┬──> api_keys
  │                │                ├──> messages
  └── org_members ─┘                ├──> domains
  │                                 ├──> webhook_endpoints
  │                                 │    └──> webhook_deliveries
  │                                 ├──> usage_records
  │                                 ├──> audit_logs
  │                                 ├──> team_invites
  │                                 └──> templates
  │
  ├── email_verifications
  ├── password_resets
  └── two_factor_secrets  (1-to-1)

idempotency_keys — org_id scoped but not FK'd