Database

Klaxon uses PostgreSQL 16+ with schema managed by sqlx migrations. Both Rust binaries (klaxon-server and klaxon-auth) share the same database and run migrations on startup — whichever starts first wins the race; the migration system is idempotent.

Schema Overview

20 SQL migrations define the full schema. Every tenant-scoped table carries org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE; application code always filters by org_id.

Core tables

Table	Purpose
orgs	Organizations (tenants). is_personal = true for auto-created single-user orgs
users	User accounts
user_identities	Links a user to an IdP (provider, provider_user_id)
org_memberships	Which users belong to which orgs (with a role_id)
agents	Registered MCP agents (API-only clients)
sessions	User bearer tokens (one-per-login; refresh on OAuth flow)
api_keys	Agent-scoped bearer tokens
magic_links	Magic-link login tokens (transient)

Items & activity

Table	Purpose
klaxon_items	Notifications / questions / forms — the core entity
klaxon_comments	Item discussion threads
klaxon_attachments	File metadata
attachment_blobs	BYTEA storage for uploaded files
klaxon_watchers	Per-item subscriber list
klaxon_audit	Append-only mutation log
klaxon_templates	Reusable form schemas
item_responses	Form answers (structured, validated at submit)
saved_views	Named filter presets (per user)

Channels & routing

Table	Purpose
channels	Item grouping — open / private / direct / team-scoped
channel_memberships	User ↔ channel junction
channel_rules	Per-user notification prefs
settings	Org-level key/value config
emailed_items	Email-send dedupe tracking

Teams (migration 0017)

Table	Purpose
teams	Named subgroups within an org
team_memberships	User ↔ team junction with role (lead / member)

Invitations (migration 0018)

Table	Purpose
invitations	Email + link invites, email-locked when email set

Roles & permissions (migration 0020)

Table	Purpose
roles	System roles (org_id = NULL) + org-local custom roles

Billing (migrations 0016 + 0019)

Table	Purpose
plans	Catalog (free / team / enterprise) with limits and features JSONB
org_subscriptions	One row per org, tracks plan + Stripe IDs + status
notification_counters	Per-org monthly meter, one row per (org_id, period_month)

Delivery outboxes & push

Table	Purpose
notification_queue	Push delivery outbox (transient)
push_subscriptions	FCM device tokens + Web Push subscriptions
webhooks	Outbound webhook configs
webhook_deliveries	Webhook delivery outbox (transient)

OAuth 2.1

Table	Purpose
oauth_clients	Registered OAuth clients (RFC 7591 dynamic registration)
oauth_authorization_codes	Short-lived codes between /authorize and /token
oauth_tokens	Access + refresh tokens; refresh rotates on use

Enum types

Type	Values
item_level	info, warning, error, success
item_status	open, answered, dismissed, expired, withdrawn
actor_type	user, agent, system
channel_mode	open, private, direct

The legacy user_role enum was dropped in migration 0020_roles.sql — roles are now first-class rows joined via role_id.

Key indexes

Index	Purpose
idx_items_org_cursor	Cursor-based pagination: (org_id, created_at DESC, id DESC)
idx_items_org_open	Partial: open non-archived items (default list view)
idx_items_snoozed	Partial: snoozed items due for wake
idx_items_deadline	Partial: items with deadlines
idx_items_tags	GIN: tag containment queries
idx_items_fts	GIN: full-text search on title + message
idx_api_keys_active	Partial: non-revoked keys (fast auth lookup)
idx_oauth_tokens_hash	Unique on token_hash for bearer lookup
idx_org_memberships_role	(role_id) — permission checks

Triggers

• Every table with an updated_at column has a BEFORE UPDATE trigger that sets updated_at = now().
• org_subscriptions_personal_pin (migration 0016) rejects any UPDATE that would change plan_id on an org where is_personal = true.

Migrations

Migrations run automatically on server startup:

sqlx::migrate!().run(&pool).await?;

Migration history

#	File	Purpose
0001	0001_initial.sql	Core schema: orgs / users / items / channels / audit
0002	0002_user_roles.sql	(superseded by 0020) role enum on memberships
0003	0003_webhooks.sql	Outbound webhook configs + delivery queue
0004	0004_channel_routes.sql	Per-channel routing + quorum JSONB
0005	0005_magic_links.sql	Magic-link login tokens
0006	0006_callback_url.sql	callback_url on items
0007	0007_email_prefs.sql	Email prefs + emailed_items dedupe
0008	0008_attachment_blobs.sql	BYTEA attachment storage
0009	0009_item_responses.sql	Structured form answers table
0010	0010_user_identities.sql	IdP linkage for users
0011	0011_oauth_clients.sql	OAuth client registry
0012	0012_oauth_authorization_codes.sql	Short-lived auth codes
0013	0013_oauth_tokens.sql	Access + refresh tokens with rotation
0014	0014_magic_links_optional_org.sql	Magic-link signup without preexisting org
0015	0015_multi_org_membership.sql	Users can belong to many orgs
0016	0016_billing.sql	Plans + org_subscriptions + Stripe plumbing
0017	0017_teams.sql	Teams + team_memberships + team-scoped channels
0018	0018_invitations.sql	Email + link invitations
0019	0019_plan_limits.sql	Refresh plan limits + notification_counters
0020	0020_roles.sql	Custom roles + permission strings

Multi-tenancy

Every tenant-scoped table has org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE. Application code always filters by org_id. Row-Level Security (RLS) is planned but not yet enforced at the database layer — don't rely on it.

Event broadcast

The server uses Postgres LISTEN/NOTIFY on the klaxon_events channel:

SELECT pg_notify('klaxon_events', '{"org_id":"...","kind":"item.created","id":"..."}');

All server replicas subscribe and forward events to connected WebSocket / SSE clients.

Demo-data seed

A canonical seed lives at crates/klaxon-server/fixtures/seed.sql. It populates every table so e2e tests and marketing screenshots have realistic content. A test at crates/klaxon-server/tests/seed.rs loads it against a fresh DB — keep it green when adding schema.

Backups

For managed PostgreSQL (RDS, Cloud SQL, CNPG), use the provider's automated backup. For self-hosted:

pg_dump -U klaxon -h localhost klaxon > backup.sql