Database schema

Postgres 16, schema-per-tenant.

Public schema

Cross-tenant tables. Every business table references public.tenants(id).

Table	Purpose
`tenants`	Organisations + `onboarding_state`
`tenant_domains`	Verified sending domains
`tenant_users`	Clerk users ↔ tenants
`email_addresses`	Hosted mailboxes ↔ users
`api_keys`	Hashed `kx_…` keys with permission scopes
`suppression_list`	Per-tenant blocked recipients
`outbound_messages`	Every send (queued → sent → delivered → …)
`tracking_tokens`	Open/click token → message_id mapping
`webhooks` / `webhook_deliveries`	Endpoints + delivery attempts
`newsletter_unsubscribes`	Token → tenant/list/subscriber routing
`newsletter_confirm_tokens`	Double opt-in tokens
`domain_warmup`	Per-domain daily send budget
`activity_log`	Audit log
`email_tracking_events`	Legacy per-event log
`audit_logs`	Cross-tenant audit log
`analytics_events`	Generic event store

Tenant schema (`org_<slug>`)

Provisioned automatically by a trigger on public.tenants. Tables:

Table	Purpose
`threads`	Email threads
`emails`	Individual email records
`attachments`	File attachments (MinIO pointers)
`email_reads`	Read receipts
`ai_suggestions`	AI-generated reply candidates
`labels`, `folders`	User-defined organisation
`email_forwards`	Forwarding rules
`auto_replies`	Vacation / auto-reply
`email_filters`	Inbox rules
`tickets`	Helpdesk
`contacts`, `deals`, `companies`	CRM
`campaigns`	Newsletter broadcasts
`calendar_events`	Calendar
`files`	Koltrix Drive
`newsletter_lists`	Audiences
`newsletter_subscribers`	People on lists
`subscriber_segments`	Smart filters
`email_templates`	Reusable HTML
`inbox_views`	Saved per-user filters
`sequences`, `sequence_steps`	Drip automations
`aliases`	Email aliases

Migrations

SQL files in koltrix-api/migrations/, applied automatically on API startup. The migrator records applied files in public.schema_migrations.

ID strategy

UUIDs everywhere (uuid_generate_v4() / gen_random_uuid()). No autoincrement columns. Tokens for tracking, unsubscribes, and confirms are 32 hex chars (crypto/rand).

Multi-tenant safety