Database — tenancy spine

This document covers the four tables laid down in Phase 1. The full schema (messages, contacts, media_objects, audit_log, rate_limit_buckets, inngest_idempotency) lands in later phases and is described in ../plan/architecture.md §1. Only the tenancy primitives are normative as of v0.1.

Conventions

Every table has id uuid PRIMARY KEY DEFAULT gen_random_uuid() (Postgres pgcrypto).
Every timestamp is timestamptz.
ON DELETE RESTRICT by default; exceptions are explicit in the column comments.
Foreign keys cascade only when the parent row is logically the root of the data (e.g. clients → api_keys cascades because keys cannot outlive their owner). Numbers do not cascade — losing a number must not silently drop history.

Tables

`clients`

The tenant root. The owner (is_owner = true) is special: their key has the * wildcards, and stdio mode synthesises an auth context with this client’s id.

Column	Type	Notes
`id`	uuid PK
`name`	text unique	kebab-case, e.g. `internal-projectx`
`display_name`	text	human-facing
`is_owner`	bool	partial unique `clients_one_owner_uq` enforces ≤1
`disabled_at`	timestamptz null	flipping this gates every API key the client holds
`metadata`	jsonb	free-form, treat as PII when logging
`created_at`	timestamptz

`phone_numbers`

One row per Meta WABA number under our control. The actual Meta access token is not stored — only an indirection (access_token_secret_ref like secrets://wa_access_token_<id>) resolved at runtime.

Column	Notes
`wa_phone_number_id`	unique; Meta’s numeric ID
`wa_business_account_id`	the WABA the number belongs to
`access_token_secret_ref`	secret pointer; never the resolved token
`webhook_verify_token_ref`	per-number; leaking one does not enable webhook hijack across the fleet
`disabled_at`	flipping removes the number from the auth/grant pipeline

No app_secret column: the App Secret is per Meta App, shared by every number in the App, and lives in env (WA_APP_SECRET). If we ever split into multiple Meta Apps, add a nullable app_secret_ref then.

`api_keys`

Hashed credentials for non-owner clients. The plaintext token is never stored.

hash = HMAC_SHA256(API_KEY_PEPPER, full_token). HMAC, not Argon2id, because the key already carries 140 bits of entropy and slow hashing would dominate request latency without adding meaningful security.
The pepper lives outside the DB (env / SOPS in Phase 7), so a stolen DB dump cannot replay against the auth endpoint.
prefix is the indexed lookup column (12 chars: wamcp_<env>_<first 4 of secret>).
rotated_from_id is a self-FK recording rotation lineage for the dual-accept window described in ../plan/architecture.md §3.

Partial indexes:

api_keys_active_client_idx on (client_id) WHERE revoked_at IS NULL — the hot path.
api_keys_expiry_idx on (expires_at) WHERE expires_at IS NOT NULL — for the rotation sweeper.

`client_phone_grants`

The two-layer authorisation key: a request is authorised only when both the API-key scopes (tools:<name>, numbers:<id>) and a non-revoked row here permit the action.

Why two layers: rotating a key must not silently regrant numbers; revoking a number from a client must affect every key they hold immediately via one row update.

daily_message_cap overrides api_keys.daily_msg_limit per (client, number) — useful for staged rollouts.

Active grants are unique per (client_id, phone_number_id) via client_phone_grants_active_uq (partial unique on revoked_at IS NULL); historical revoked rows accumulate for audit.

Tenant isolation

We do not use Postgres RLS — we issue one DB role for the app, and RLS shines when each tenant gets its own role. Instead, every query against a tenant-scoped table goes through src/db/scoped.ts, which takes clientId as a mandatory first argument. The Phase-4 ESLint rule will forbid raw pool.query against tenant tables outside that module. Cross-tenant isolation tests in CI assert client B cannot read or write client A’s rows via any tool, scope, or grant path.

The schema stays RLS-ready in case we change our mind.

`contacts`

One row per (phone_number_id, wa_id) the server has seen. wa_id is E.164 with no leading + (Meta convention). The same human under different WABA numbers is a distinct contact because conversation state lives per-number.

Column	Notes
`phone_number_id`	FK; `ON DELETE RESTRICT` so a number with history cannot be dropped silently
`wa_id`	E.164 no-plus
`profile_name`	From Meta `contacts[].profile.name` on inbound
`display_name`	Operator override (Phase 4+)
`first_seen_at` / `last_seen_at`	The latter updated on every inbound; cheap row-level write

Unique per (phone_number_id, wa_id) via contacts_phone_wa_id_uq.

`messages`

The conversation log. Inbound + outbound in one table, distinguished by direction. Body content (body, payload, raw) is durable here by design but must never appear in log lines.

Column	Notes
`phone_number_id`	FK; `ON DELETE RESTRICT`
`client_id`	Nullable for inbound (number-owned); always set for outbound
`direction`	`'inbound' \| 'outbound'` (CHECK constraint)
`wa_message_id`	Meta `wamid`; partial unique (`WHERE wa_message_id IS NOT NULL`) so in-flight outbound rows can exist before the Meta call returns
`status`	Outbound: `queued \| sent \| delivered \| read \| failed`. Inbound: `received \| read`
`error_code`	Meta error integer when `status='failed'`
`payload` / `raw`	Normalised view vs sanitised original. See webhook.md for sanitisation rules
`ts`	Meta event time
`created_at`	Server-side ingest time. This is the pagination cursor — see below

Pagination cursor

get_messages paginates by (created_at ASC, id ASC). The messages_cursor_idx index covers this. Architecture decision: created_at is the only orderable timestamp on the row. ts can be backfilled by Meta and wa_message_id (wamid) has no documented ordering.

`inngest_idempotency`

Dedupe primary key. The webhook handler inserts event_id with ON CONFLICT DO NOTHING and short-circuits on rowCount === 0. The same table is used by Inngest events from Phase 3 onwards (the key shape is shared).

Migrations

Hand-written SQL files under drizzle/, applied by src/db/migrate.ts which tracks applied files in drizzle_migrations. Every migration starts with -- WHY: explaining the change (not the SQL).