Database Migrations

The Realtime Platform uses Knex for database migrations, managed in the @realtime/database package. Migrations run automatically on backend startup and can also be run manually.

Auto-Migration on Startup

The backend calls runMigrations() before starting the HTTP server. This:

Creates the knex_migrations tracking table if it doesn't exist
Applies any pending migrations in order
Skips already-applied migrations (idempotent)
Logs which migrations were applied

If the database is unreachable, the backend logs a warning and starts anyway.

Manual Commands

# Apply all pending migrations
pnpm --filter @realtime/database migrate:latest

# View migration status
pnpm --filter @realtime/database migrate:status

# Rollback the last batch
pnpm --filter @realtime/database migrate:rollback

# Create a new migration file
pnpm --filter @realtime/database migrate:make add_new_table

Migration Files

Located at packages/database/src/migrations/:

Migration	Description
`20240101000001_event_outbox`	Transactional outbox table with partial index on unprocessed rows
`20240101000002_signing_keys`	JWT signing key storage with rotation support
`20240101000003_topics`	Topic registry table
`20240101000004_schemas`	Versioned schema definitions with topic FK and compatibility mode
`20240101000005_mappings`	Domain mappings and mapping version promotion tables
`20240101000006_webhooks`	Webhook endpoints and delivery log with indexed history
`20240101000007_event_traces`	Event trace storage for the debugger
`20240101000008_applications`	Multi-tenant applications table
`20240101000009_add_application_id`	Adds `application_id` column to all entity tables
`20240101000010_environments`	Environment-scoped fields
`20240101000011_deployments`	Deployment management tables (deployments, items, comments)
`20240101000012_user_auth`	Users, roles, permissions, sessions, invites, password reset tokens

Each migration has up() and down() functions for forward and rollback.

Writing a New Migration

Create the migration file

pnpm --filter @realtime/database migrate:make my_new_table

This creates a new timestamped TypeScript file in the migrations directory.

Implement up() and down()

import type { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('my_table', (table) => {
    table.string('id').primary();
    table.string('name').notNullable();
    table.timestamp('created_at').defaultTo(knex.fn.now());
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('my_table');
}

Apply the migration

pnpm --filter @realtime/database migrate:latest

Or simply restart the backend — it auto-applies pending migrations.

Info

Migration files are TypeScript and are loaded at runtime by Knex (excluded from the tsc build). They work correctly from both src/ and dist/ directories.

Knex Configuration

The Knex config (packages/database/src/knexfile.ts) reads connection details from environment variables:

Connection priority:

DATABASE_URL (full connection string)
Individual POSTGRES_* variables

Connection pool: min 2, max 10

Best Practices

Always implement both up() and down() for every migration
Use createTableIfNotExists / dropTableIfExists for safety
Never modify a migration that has already been applied to production
Create a new migration for schema changes instead
Test migrations with migrate:latest followed by migrate:rollback to verify both directions work