Schema & conventions

One Postgres database holds several per-domain schemas. The hand-authored SQL in db/migrations is the source of truth for the DDL (the Drizzle definitions in db/src/schema are the typed query builder, not the authority — drizzle-kit's generator can't express the RLS + role model coherently, so the migrations own it).

The schemas

The core model now spans seven schemas (the first five began in 0001_init.sql; later migrations split the append-only audit and global system surfaces explicitly):

Schema	Owns
tenant	client_account (the RLS anchor), subscriber, and commercial config; later: agent, email_send, inbound_number, short_url.
identity	Clerk-member projections, role, permission (shared catalogue), member role/scope links and first-party api_key records.
survey	survey (versioned definitions), question, option; later: channel_session, themes/topics.
response	transaction, answer, attempt, note; alert and lead are added in later migrations.
compliance	suppression, unsubscribed, retained deletion_record, GDPR operations and durable subject-erasure/object-proof ledgers.
audit	The append-only, tenant-isolated log activity ledger.
system	Global feature flags/cron health and owner-only provider-correlation state.

Hosted identity, credentials, organisations and sessions live in Clerk; Postgres stores only the application's membership/authorisation projections and first-party API keys. The link between a Clerk Organization and a tenant is the auth_org_id text column on tenant.client_account (holding the org_… id). The identity.permission catalogue is shared/reference: it carries no client_account_id and is read-only to tenants (RLS-exempt).

public_id (UUIDv7) vs internal id (bigint)

Every table follows the same key convention:

• Internal id — bigint generated always as identity primary key. Used for foreign keys and joins; never exposed outside the system, so sequence values never leak.
• public_id — a uuid not null unique (a time-ordered UUIDv7, minted in the domain layer rather than a DB default) for any id that leaves the system: API ids, the respondent-facing transaction token, email tracking tokens. External ids stay opaque and index-friendly.

Routes and domain code address rows by public_id; the adapter resolves to the internal id for joins. Branded id types in @voc/core (SurveyId, ResponseId, …) keep these straight at the type level.

Other universal conventions

• Tenant discriminator. Every tenant-owned row carries client_account_id bigint not null — the sole RLS key, designed in (never derived via joins). tenant.client_account is the one exception: its own id is the client_account_id, so its RLS policy keys on id.
• Tenant-bound relationships. Every relationship between tenant-owned tables carries client_account_id on both sides of a validated composite foreign key. RLS controls row visibility, but a scalar foreign key does not prove that a known internal parent id belongs to the writer's tenant. Migrations 0114 and 0115 upgrade existing relationships and add the required links the original schema omitted.
• Timestamps. created_at / updated_at as timestamptz not null default now() (UTC) — never bare timestamp.
• Open shapes. jsonb for tenant custom data, survey-definition fragments (question.rules, option.action), and provider payloads.
• Idempotency. Externally-driven write tables carry an idempotency_key with a tenant-scoped unique constraint, so at-least-once delivery is safe to retry — e.g. uq_txn_idem on response.transaction (client_account_id, idempotency_key).
• Soft state, hard audit. Domain rows may be mutable; audit.log and compliance.deletion_record survive PII purge. Migration 0118 restricts deletion certificates to read/append operations for request traffic, so retained evidence cannot be updated, deleted, or truncated.

A representative table from 0001_init.sql:

create table if not exists response.transaction (
  id                bigint generated always as identity primary key,
  public_id         uuid not null,                       -- respondent-facing token (UUIDv7)
  client_account_id bigint not null,
  survey_id         bigint not null,
  survey_version    integer not null,
  channel           text not null,                       -- online | email | sms | ivr_* | embedded
  state             text not null default 'in_flight',   -- in_flight | complete | expired
  idempotency_key   text not null,
  custom_data       jsonb not null default '{}'::jsonb,
  created_at        timestamptz not null default now()
);
create unique index uq_txn_idem on response.transaction (client_account_id, idempotency_key);

Survey definitions are versioned: publishing snapshots a version, and a captured transaction records the survey_version it was collected under, so editing questions never rewrites history. Answers reference questions by id, and response.answer is a single table across all channels (value_text / value_numeric / value_json).

The high-volume tables (response.transaction / answer) are plain tables today; monthly RANGE partitioning is a deferred scale step (see the partitioning note in 0001_init.sql). audit.log is the single append-only activity ledger.

Next: Multi-tenancy & RLS — how client_account_id becomes an enforced isolation boundary — and Migrations.