Sendoka
Dashboard

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 — single-use 24h tokens.
  • 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

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

Column Type Notes
id text PK usr_...
name text nullable
email text unique, not null
password_hash text bcrypt, nullable (OAuth users may lack one)
email_verified timestamp set by verify flow or OAuth auto-signup
image text avatar URL
created_at / updated_at timestamp

organizations — organizations.ts

Column Type Notes
id text PK org_...
name text
slug text unique
owner_id text FK users.id
stripe_customer_id text nullable
plan_status text free (default) / pro / enterprise
created_at / updated_at timestamp

org_members

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

Column Type Notes
id text PK mem_...
org_id FK organizations
user_id FK users
role text owner / member

api_keys — api-keys.ts

Column Notes
id key_...
name / key_prefix / key_hash / last_four key parts
environment live / test
last_used_at updated fire-and-forget on validation
expires_at nullable — validation rejects expired keys
revoked_at soft delete

messages — messages.ts

Column Notes
id / org_id / api_key_id keys
channel / status / environment
Email/SMS body fields channel-dependent
provider_message_id / provider_response SES/SNS tracking
tags / metadata / error_message
scheduled_at future-dated sends; picked up by /api/cron/send-scheduled
sent_at / delivered_at / created_at / updated_at timestamps

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

Column Notes
id whk_...
url / events[] / secret delivery config + HMAC signing secret
previous_secret nullable — used during rotation window
previous_secret_expires_at when the previous secret stops being accepted
enabled toggle without delete

webhook_deliveries — webhook-deliveries.ts

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

Column Notes
id whd_...
org_id / endpoint_id scope
event message.*
payload jsonb snapshot of the fired event
status pending / delivered / failed
attempts int, ≤ 5 (MAX_ATTEMPTS)
last_status_code / last_error most recent attempt
next_attempt_at exponential backoff + jitter
delivered_at on success

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.

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. 24h TTL, single-use via used_at.

password_resets — password-resets.ts

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

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