Database schema
use ? placeholders and are translated to $N for PostgreSQL.
Tables
| Table | Purpose | Key columns |
|---|---|---|
devices | Registered Android devices | id, account_id, name, public_key, key_version, key_fingerprint, fcm_token, status, last_seen_at, created_at, updated_at |
tokens | API tokens (stored as SHA-256 hashes) | id, account_id, prefix, token_hash, scopes, device_id, revoked_at, created_at, last_used_at |
delivery_records | Ephemeral delivery metadata (expires) | message_id, account_id, device_id, status, pushed_at, expires_at, created_at |
accounts | SaaS tenants | id, email, password_hash, name, plan, stripe_customer_id, subscribed, trial_ends_at, subscription_cancels_at, billing_event_at, trial_ending_notice_at, trial_ended_notice_at, tos_accepted_at, email_verified_at, immediate_performance_at, totp_secret, totp_enabled, status, created_at, updated_at |
sessions | Dashboard sessions | token, id, account_id, user_agent, created_at, expires_at |
usage_counts | Monthly message quota counters | account_id, month, message_count — PK (account_id, month) |
team_members | Team membership (member → owner account) | account_id, team_account_id, role, created_at |
team_invites | Pending team invites (stored as hashes) | token_hash, team_account_id, email, role, created_at, expires_at |
payments | Stripe payment/invoice records | id, account_id, stripe_invoice_id, amount_cents, currency, status, created_at |
password_resets | Password-reset tokens (stored as SHA-256 hashes) | token_hash, account_id, created_at, expires_at |
email_verifications | Email-verification tokens (stored as SHA-256 hashes) | token_hash, account_id, created_at, expires_at |
totp_recovery_codes | 2FA one-time recovery codes (stored as SHA-256 hashes) | token_hash, account_id, created_at |
replay_keys | Server replay-protection ids (Mode B, §8 of the protocol) | message_id, account_id, expires_at — PK (message_id, account_id) |
instances | Single-replica tripwire: live prelayd heartbeats (issue #97) | id, updated_at |
schema_migrations | Applied migration versions | version, applied_at |
Data-minimization invariants hold at the schema level (see the PRD §13):
notification plaintext is never persisted — delivery_records carry routing
metadata only and are expired by the janitor (PRELAY_DELIVERY_TTL); full API
tokens are shown once and never stored; usage_counts stores numbers, never
contents.
How migrations run
Store.Migrate() runs automatically at every server startup, before the
HTTP server begins serving (cmd/prelayd/main.go). Each migration is
recorded in schema_migrations; applied versions are skipped. Statements are
idempotent, so a fresh database and an existing database converge to the same
schema.
All pending migrations run inside a single transaction: a failed run rolls
back to the pre-run ledger and a retry replays from the first unapplied version,
so a failure never leaves a half-applied schema. On PostgreSQL the DDL locks are
held until that one commit, so avoid running anything else (e.g. prelayd admin-token) against the database while a startup migration is in progress.
For deployments this means no manual migration step: a docker compose up
/ Coolify redeploy rebuilds the container image, restarts the service, and the
pending migrations apply in place to the persisted volume database before the
server accepts traffic. A failed migration fails the container loudly rather
than serving on a stale schema.
Adding a migration
Migrations are appended to the migrations slice in migrate.go, in order of
a strictly increasing version. Rules:
- Append only. Never edit, renumber, or delete an applied migration — existing databases record applied versions and skipped statements are never re-run.
- One statement per migration. PostgreSQL cannot run multiple statements
in a single
Exec. - Idempotent and safe to re-run. Use
CREATE ... IF NOT EXISTS; column additions use theaddColumnform (a guardedALTER TABLE ... ADD COLUMN, since the bundled SQLite driver predatesADD COLUMN IF NOT EXISTS). Because a failed run rolls the ledger back and the next attempt replays from the first unapplied version, every statement must also be safe to re-apply after a partial run. - Adding a column to an existing table is two edits, both required: add
the column to the table's
CREATE TABLE(for fresh databases) and append anaddColumnmigration (for existing databases). - Type changes and column drops cannot be expressed as
ADD COLUMN; express them as new-table + copy + swap across multiple versioned steps.