Database Schema & Migrations

Database Schema & Migrations

This page documents the PostgreSQL schema of the Secure Mesh Backend and the SQLx-based migration system that manages it, including the migration file inventory, the advisory-lock-protected runner in db.rs, and how migrations are wired into server startup in main.rs.

Purpose and Scope

The streaming-backend service persists all core domain data — users, groups, messages, statuses, calls, key bundles, push tokens, and refresh tokens — in a PostgreSQL database. Schema changes are managed exclusively through numbered SQL migration files under streaming-backend/migrations/, executed at startup by SQLx’s compile-time-embedded migrate! macro.

This page covers:

• The migration runner (streaming-backend/src/db.rs) — pool creation and the advisory-lock-protected migration execution.
• Startup wiring in streaming-backend/src/main.rs — where and when migrations run relative to the rest of the boot sequence.
• The complete migration inventory (11 files) and a detailed look at the base users table.
• Design intent: why migrations run at startup, why a PostgreSQL advisory lock is used, and how the schema is shaped (Snowflake BIGINT IDs, epoch-millisecond timestamps, explicit indexes).

Deliberately not covered here (see sibling pages in the catalog):

• For how refresh tokens are issued, rotated, and validated, see Authentication & Tokens.
• For message history queries and group membership behavior, see Messaging.
• For identity key bundles and the deterministic key backfill, see Identity & Key Management.
• For call ticket / relay behavior, see Calls.

Overview

The backend is a Rust service (Tokio + Axum HTTP, gRPC-Web, Redis via fred, PostgreSQL via sqlx) that starts by connecting to PostgreSQL, running any pending schema migrations, and only then bringing up Redis, the Snowflake ID service, background tasks, and the HTTP/gRPC listeners.

Schema evolution follows SQLx’s conventional layout: each migration is a file named NNN_description.up.sql (and optionally .down.sql). This repository ships up-only migrations; every applied migration is recorded in SQLx’s _sqlx_migrations bookkeeping table and verified by checksum on subsequent runs.

The key design decisions are:

1. Migrations run at process startup, before any traffic is served — no separate migration binary or CI step is required; a fresh deploy self-heals its schema.
2. A PostgreSQL advisory lock serializes concurrent bootstraps — when multiple backend instances start simultaneously (horizontal scaling), exactly one acquires the lock and runs migrations; the others block and then proceed.
3. Snowflake-generated BIGINT IDs and epoch-millisecond BIGINT timestamps are used throughout — consistent with the shared SnowflakeService (see main.rs), avoiding separate auto-increment sequences and timezone-dependent TIMESTAMP handling.

Architecture

The diagram shows the boot path in main.rs (top), the lock-guarded runner in db.rs (middle), the on-disk migration suite compiled into the binary by sqlx::migrate! (left), and the resulting schema plus SQLx’s bookkeeping table in PostgreSQL (right). The advisory lock is the critical synchronization point: without it, two concurrently booting instances could apply the same migration twice or observe partially migrated state.

Migration Runner (db.rs)

All database access in the backend funnels through the two functions in streaming-backend/src/db.rs: create_pool for connection-pool construction and run_migrations_safely for schema application.

Pool creation

pub async fn create_pool(database_url: &str) -> Result<PgPool> {
    let pool = PgPoolOptions::new()
        .max_connections(20)
        .connect(database_url)
        .await?;
    Ok(pool)
}

Source: db.rs

create_pool builds a sqlx::PgPool with a fixed maximum of 20 connections and connects eagerly (connect, not connect_lazy), so a missing or unreachable DATABASE_URL fails fast at startup rather than on the first request. The pool is later shared across the entire application via Arc<AppStateData> and used by the auth, messaging, status, and refresh-token services.

Advisory-lock-protected migration run

pub async fn run_migrations_safely(pool: &PgPool) -> Result<()> {
    const MIGRATION_LOCK_KEY: i64 = 0x4D494752_4C4F434B;
    let mut conn = pool.acquire().await?;

    sqlx::query("SELECT pg_advisory_lock($1)")
        .bind(MIGRATION_LOCK_KEY)
        .execute(&mut *conn)
        .await?;

    info!("Acquired migration advisory lock. Running SQLx migrations...");
    let migration_res = sqlx::migrate!("./migrations").run(pool).await;

    let _ = sqlx::query("SELECT pg_advisory_unlock($1)")
        .bind(MIGRATION_LOCK_KEY)
        .execute(&mut *conn)
        .await;

    migration_res?;
    info!("Database migrations executed successfully.");
    Ok(())
}

Source: db.rs

The function’s behavior, step by step:

1. Acquire a dedicated connection from the pool. The advisory lock is session-scoped in PostgreSQL, so it must be taken and released on the same connection — this is why a conn handle is pulled before locking rather than running the lock query on the pool directly.
2. Take a named advisory lock with key 0x4D494752_4C4F434B ("MIGR" "LOCK" in ASCII — 0x4D494752 = “MIGR”, 0x4C4F434B = “LOCK”). pg_advisory_lock blocks until the lock is granted, so concurrent instances queue up instead of racing.
3. Run SQLx migrations. The sqlx::migrate!("./migrations") macro embeds every *.sql file in the migrations/ directory into the binary at compile time, in lexical (numeric) order. .run(pool) applies only migrations not yet recorded in _sqlx_migrations, in a transaction each, and records the checksum of each applied file.
4. Release the lock. The unlock is executed on the same connection, best-effort (let _ = ...), so it never masks a migration failure.
5. Propagate errors. migration_res? returns any migration failure to the caller, which aborts startup (see below).

The lock key is a hardcoded constant, meaning every instance of every deployment of this service uses the same lock — exactly what you want for a single logical database, but note it would serialize migration runs even if multiple environments shared one database.

Startup Wiring (main.rs)

Migrations are invoked in main() immediately after the pool is created and before Redis, Snowflake, or any HTTP/gRPC listener starts:

let cfg = Config::from_env();
info!("Starting Secure Mesh Backend");

let db_pool = db::create_pool(&cfg.database_url).await?;
db::run_migrations_safely(&db_pool).await?;

Source: main.rs

The ordering in main.rs (lines 32–115) is deliberate:

1. dotenv().ok() — load .env if present (ignored in production where real env vars exist).
2. tracing_subscriber::fmt::init() — logging before anything else.
3. Config::from_env() — validate all configuration up front.
4. db::create_pool(...) — connect to PostgreSQL.
5. db::run_migrations_safely(...) — schema is guaranteed current before any service logic runs.
6. Redis client build/connect, SnowflakeService::verify_and_init, service construction.
7. Background tasks: identity-key backfill (identity::backfill_missing_keys) and the 600-second status purge loop.
8. gRPC server, then Axum HTTP server (axum::serve — the final .await, keeping the process alive).

Because ? is used at steps 4–5, a database that is unreachable or a migration that fails prevents the process from ever listening for traffic — the service never serves requests against a stale or half-migrated schema.

Migration Inventory

The suite lives in streaming-backend/migrations/ and is ordered numerically. All files are *.up.sql only — no down-migrations are shipped in this repository, so rollback is handled by restoring a backup/snapshot rather than by sqlx downgrade scripts.

Note on column-level detail: only 001_users.up.sql was fully inspected for this page; the exact columns of migrations 002–011 are defined in their respective files, which are linked below.

The base users table

CREATE TABLE users (
  id            BIGINT       PRIMARY KEY,
  username      VARCHAR(64)  UNIQUE NOT NULL,
  email         VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  avatar_url    VARCHAR(512),
  created_at    BIGINT       NOT NULL
);
CREATE INDEX idx_users_email    ON users(email);
CREATE INDEX idx_users_username ON users(username);

Source: 001_users.up.sql

Design intent visible in this one table carries through the whole schema:

• id BIGINT PRIMARY KEY — IDs are client/server-generated Snowflake values (see SnowflakeService), not SERIAL/IDENTITY sequences. This enables offline-friendly ID generation, chronological sortability, and shard/machine-id partitioning.
• created_at BIGINT NOT NULL — epoch-millisecond timestamps stored as integers, avoiding TIMESTAMPTZ parsing overhead and timezone ambiguity; the application converts to/from chrono values.
• UNIQUE constraints plus explicit indexes — username and email are both UNIQUE NOT NULL (enforced at the DB level, the ultimate backstop for registration races), and the redundant-looking idx_users_email / idx_users_username indexes serve lookups and the unique-constraint enforcement structures. avatar_url is nullable and length-capped (512).
• password_hash is VARCHAR(255) NOT NULL — sized for bcrypt/argon2 output, and stored non-null because no unauthenticated user may exist.

Core Flow: Boot-Time Migration Sequence

Key behaviors in this sequence:

• Blocking, not failing, on contention. pg_advisory_lock waits. If instance B starts while instance A is mid-migration, B’s lock call simply blocks until A finishes; B then runs migrate! and finds zero pending migrations.
• Session-scoped lock. The lock lives on the specific connection conn acquired at the top of run_migrations_safely; it is released on that same connection, and it is automatically released if that connection drops — so a crash mid-migration cannot leave the lock held forever.
• Checksum verification. SQLx stores a checksum per applied migration. If a developer edits an already-applied *.up.sql file, the next startup fails with a checksum-mismatch error rather than silently applying divergent schema — the safety mechanism that makes up-only migrations tenable.

Schema Domain Map

The eleven migrations map onto five logical domains that the application’s modules consume:

This grouping reflects how the runtime services consume the schema: RefreshTokenService reads refresh_tokens; the identity backfill task reads identity_seed and user_keys and writes users keys; StatusService::purge_expired_statuses sweeps statuses on a 600-second timer (spawned in main.rs); and the messaging/group modules query groups and messages. The schema is intentionally denormalized along service boundaries — e.g., a bio and status background color are ALTER TABLE ... ADD COLUMN migrations (009, 011) onto existing tables rather than new tables, keeping query patterns simple at the cost of wide rows.

Usage Examples

Adding and applying a migration

The only requirement to add schema changes is dropping a new numbered file into streaming-backend/migrations/ and rebuilding. The sqlx::migrate! macro embeds it at compile time, and the next startup applies it automatically. The pattern used by the existing suite is demonstrated by 011_add_user_bio.up.sql (an ALTER TABLE migration in the same style as 009_add_status_bg_color.up.sql).

The runner is invoked exactly once, from main.rs, with no arguments beyond the pool:

let db_pool = db::create_pool(&cfg.database_url).await?;
db::run_migrations_safely(&db_pool).await?;

Source: main.rs

Writing a new table migration

Follow the conventions of 001_users.up.sql — Snowflake BIGINT primary key, epoch-millisecond BIGINT timestamps, explicit UNIQUE/indexes on lookup columns:

CREATE TABLE users (
  id            BIGINT       PRIMARY KEY,
  username      VARCHAR(64)  UNIQUE NOT NULL,
  email         VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  avatar_url    VARCHAR(512),
  created_at    BIGINT       NOT NULL
);
CREATE INDEX idx_users_email    ON users(email);
CREATE INDEX idx_users_username ON users(username);

Source: 001_users.up.sql

Calling the runner programmatically

run_migrations_safely is a plain async function on a PgPool, so it can be reused outside startup (e.g., in integration tests that bring up a scratch database before spawning services):

pub async fn run_migrations_safely(pool: &PgPool) -> Result<()> {
    const MIGRATION_LOCK_KEY: i64 = 0x4D494752_4C4F434B;
    let mut conn = pool.acquire().await?;

    sqlx::query("SELECT pg_advisory_lock($1)")
        .bind(MIGRATION_LOCK_KEY)
        .execute(&mut *conn)
        .await?;

    info!("Acquired migration advisory lock. Running SQLx migrations...");
    let migration_res = sqlx::migrate!("./migrations").run(pool).await;

    let _ = sqlx::query("SELECT pg_advisory_unlock($1)")
        .bind(MIGRATION_LOCK_KEY)
        .execute(&mut *conn)
        .await;

    migration_res?;
    info!("Database migrations executed successfully.");
    Ok(())
}

Source: db.rs

Configuration Options

There is no runtime toggle to disable migrations: every process start applies pending migrations. To skip them in a test or tooling context, call the downstream functions directly instead of main().

API Reference

create_pool(database_url: &str) -> Result<PgPool>

Creates an eager sqlx::PgPool with 20 max connections.

Parameters:

• database_url (&str): PostgreSQL connection string (e.g., postgres://user:pass@host:5432/db).

Returns: Ok(PgPool) on successful connection; the pool is ready for queries immediately.

Throws / errors:

• sqlx::Error (connection failure, bad URL, auth failure) — surfaced as crate::error::Result; aborts startup when ? is used in main().

Source: db.rs

run_migrations_safely(pool: &PgPool) -> Result<()>

Applies all pending SQLx migrations, serialized by a PostgreSQL advisory lock.

Parameters:

• pool (&PgPool): the shared connection pool; migrations run against it while the lock is held on a separately acquired connection.

Returns: Ok(()) once all migrations are applied and the lock is released.

Throws / errors:

• sqlx::Error — pool acquisition failure, advisory lock query failure, or any migration failure (including checksum mismatch on an already-applied migration). Errors propagate via migration_res?; the unlock is still attempted best-effort first.

Source: db.rs

Failure Modes, Edge Cases & Concurrency

Concurrent instance startup (the case the advisory lock solves)

In a horizontally scaled deployment, several backend processes can boot at once. Without coordination they would race to apply the same migration, and SQLx’s _sqlx_migrations bookkeeping could observe partially applied state. run_migrations_safely serializes this with pg_advisory_lock(0x4D494752_4C4F434B):

• Only the first instance acquires the lock and applies pending migrations.
• Other instances block inside pg_advisory_lock and, once granted, run migrate! which finds nothing pending — a no-op.

Note the lock is session-scoped: it is held on the dedicated conn handle, not the pool. If the process crashes mid-migration, PostgreSQL frees the lock with the session, so a restarted instance can retry cleanly — no stale lock to clear manually.

Checksum mismatch on edited migrations

SQLx records a checksum for each applied migration in _sqlx_migrations. Editing an already-applied *.up.sql file (even a comment) makes the embedded migration’s checksum differ from the recorded one, and the next startup fails with a checksum error. This is intentional: up-only migrations mean the authoritative history must be immutable. The remedy is a new NNN_...up.sql file, not an edit.

Startup abort on failure

create_pool uses eager connect and run_migrations_safely’s error is propagated with ? before any listener is bound. Consequences:

• An unreachable DATABASE_URL or a failed migration prevents the process from ever accepting traffic — no half-migrated or schema-less service.
• Deploy orchestration sees a non-zero exit and can keep the old instance running / roll back.

Unlock masking

The unlock is deliberately best-effort (let _ = ...). If unlocking fails, the error is discarded so the real migration result is what the caller sees. Since the lock is session-scoped, a leaked lock self-heals when the connection closes.

Lock key collision

The lock key is a fixed constant shared by all instances of this service. This is correct for a single shared database but would serialize migration runs across different services if they shared a database and used the same key — worth remembering if the database is ever multi-tenant at the service level.

No down migrations

Only .up.sql files exist in streaming-backend/migrations/. SQLx’s migrate! supports .down.sql files for migrate revert, but this repository intentionally ships none; rollback is by database snapshot/restore, and schema changes must therefore be designed to be additive and backward compatible.

Performance & Operational Notes

• Migrations run once per process start, before serving. Startup cost is one pool connect plus any pending DDL; steady-state request latency is unaffected.
• Compile-time embedding. sqlx::migrate!("./migrations") bakes the SQL into the binary, so no runtime filesystem access to the migration directory is needed in production — deployment is a single artifact.
• Pool sizing. 20 max connections is the shared pool for both migrations and all runtime queries; migrations are brief, so the lock is held only momentarily per instance (except when waiting on another instance).
• Lock wait time. pg_advisory_lock has no timeout; an instance waiting behind a long-running migration blocks startup indefinitely. For very large migrations, this is acceptable (boot-time) but is worth knowing when diagnosing “slow startup” in a scaled deployment.
• Operational check. After a deploy, _sqlx_migrations shows the applied versions; a failed startup leaves the checksum/version rows consistent because each migration runs in its own transaction.

Extension Points

1. Adding a migration — create NNN_description.up.sql in streaming-backend/migrations/; the sqlx::migrate! macro picks it up at the next compile and the runner applies it at the next startup. Follow existing conventions: BIGINT Snowflake IDs, BIGINT epoch-ms timestamps, explicit indexes, additive changes only.
2. Adding down migrations — SQLx supports .down.sql siblings if reversible migrations are ever required; the runner needs no changes.
3. Reusing the runner — run_migrations_safely(&pool) is a standalone async function; integration tests can call it directly against a scratch database before spawning services.
4. Schema consumers — the identity-key backfill task (identity::backfill_missing_keys, spawned in main.rs) consumes 010_identity_seed + 006_user_keys; the status purge loop consumes 004_statuses; RefreshTokenService consumes 008_refresh_tokens. New features touching these tables should preserve their contracts.

Related Links

• db.rs — pool + migration runner
• main.rs — startup sequence
• 001_users.up.sql — base users table
• Migration suite directory
• Related catalog pages: Authentication & Tokens (refresh/push tokens), Identity & Key Management (user keys + identity seed), Messaging (groups/messages), Presence & Calls (statuses/calls), Configuration (DATABASE_URL handling in config.rs).