Migration Guide

This document covers how database migrations work in Alloy, including naming conventions, the dual-backend requirement, type differences between PostgreSQL and SQLite, how refinery embeds migrations into the binary, keeping version numbers aligned, and testing migrations.

Overview

Alloy supports two database backends: PostgreSQL (multi-tenant with RLS) and SQLite (single-tenant, single-file). Every migration must exist in both backends. Migrations are embedded into the compiled binary via refinery’s embed_migrations! macro — there are no external SQL files at runtime.

Directory Layout

migrations/
├── postgres/
│   ├── V1__create_schema_info.sql
│   ├── V2__create_organizations.sql
│   ├── ...
│   └── V32__add_time_entry_indexes.sql
└── sqlite/
    ├── V1__create_schema_info.sql
    ├── V2__create_organizations.sql
    ├── ...
    └── V32__add_time_entry_indexes.sql

Each directory contains the same set of version numbers. The Rust code in crates/alloy-api/src/db.rs embeds them:

#![allow(unused)]
fn main() {
mod pg_migrations {
    refinery::embed_migrations!("../../migrations/postgres");
}

mod sqlite_migrations {
    refinery::embed_migrations!("../../migrations/sqlite");
}
}

Naming Convention

Migration files follow refinery’s naming scheme:

V{number}__{description}.sql

• V — prefix (uppercase)
• {number} — sequential integer, no zero-padding (1, 2, … 32)
• __ — double underscore separator
• {description} — snake_case description of the change
• .sql — file extension

Examples:

• V7__create_tickets.sql
• V17__add_project_capitalization.sql
• V31__add_ticket_indexes.sql

To find the next version number, check the highest existing number:

ls migrations/postgres/ | sort -t_ -k1 -V | tail -1

Dual-Backend Requirement

Every migration version must exist in both migrations/postgres/ and migrations/sqlite/. Even when a migration is backend-specific (like RLS policies), the other backend needs a matching no-op file to keep version numbers aligned.

Example — V9 enables RLS in PostgreSQL:

migrations/postgres/V9__enable_rls.sql — Full RLS setup with policies, roles, and grants.

migrations/sqlite/V9__enable_rls.sql — No-op placeholder:

-- No-op: RLS is PostgreSQL-only. This migration exists to keep
-- SQLite and PostgreSQL version numbers aligned.

If you forget the counterpart file, the backends will diverge and future migrations will fail with version-mismatch errors.

Type Differences

Write SQL that works on both backends where possible. When that isn’t possible, write backend-specific SQL in each file.

Rules of thumb:

• Always use TEXT instead of VARCHAR — works on both backends.
• Use TEXT for UUIDs on both backends (PostgreSQL can cast, SQLite needs it).
• Timestamps differ: TIMESTAMPTZ + NOW() for PG, TEXT + datetime('now') for SQLite.
• Booleans differ: BOOLEAN for PG, INTEGER for SQLite.
• Avoid SERIAL — use application-generated UUIDs as primary keys instead.

PostgreSQL-Only Features

Some migrations contain PostgreSQL-specific SQL that has no SQLite equivalent:

• Row Level Security (RLS): ALTER TABLE ... ENABLE ROW LEVEL SECURITY, CREATE POLICY, current_setting(). SQLite uses explicit AND org_id = ? in application queries instead.
• Roles and grants: CREATE ROLE, GRANT, ALTER DEFAULT PRIVILEGES.
• PL/pgSQL functions: CREATE OR REPLACE FUNCTION ... LANGUAGE sql.
• DO $$ blocks: Anonymous code blocks for conditional DDL.

For all of these, create a no-op SQLite migration with a comment explaining why.

How Refinery embed_migrations! Works

Refinery’s embed_migrations! macro reads the SQL files at compile time and embeds them as string constants in the binary. This means:

1. No runtime file access — the binary contains all migrations. No need to ship SQL files alongside the executable.
2. Compile-time validation — if a migration file is malformed or missing, the build fails.
3. Version tracking — refinery creates a refinery_schema_history table in the database to track which migrations have been applied.
4. Forward-only — refinery does not support down migrations. To undo a change, write a new migration that reverses it.

The migration runner is triggered in crates/alloy-api/src/db.rs via the Database::migrate() method. In production, auto-migration on startup is controlled by ALLOY_AUTO_MIGRATE=true.

Writing a New Migration

Step 1: Determine the next version number

ls migrations/postgres/ | sort -t_ -k1 -V | tail -1
# If the last file is V32__add_time_entry_indexes.sql, your next is V33

Step 2: Create both files

touch migrations/postgres/V33__your_description.sql
touch migrations/sqlite/V33__your_description.sql

Step 3: Write the SQL

Write the PostgreSQL version first, then adapt for SQLite. If the SQL is identical (e.g., ALTER TABLE ... ADD COLUMN ... TEXT), the files can be the same. If not, write backend-specific SQL in each.

Example — adding a column (identical on both):

ALTER TABLE projects ADD COLUMN archived INTEGER NOT NULL DEFAULT 0;

Example — adding an index (identical on both):

CREATE INDEX idx_tickets_assignee ON tickets(assignee_id);

Example — PostgreSQL-specific (adding RLS policy):

migrations/postgres/V33__add_rls_for_new_table.sql:

ALTER TABLE new_table ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON new_table
  FOR ALL USING (org_id = current_tenant_id());

migrations/sqlite/V33__add_rls_for_new_table.sql:

-- No-op: RLS is PostgreSQL-only.

Step 4: Verify the build

cargo check --workspace --all-targets

This compiles the migrations into the binary. If your SQL files are not in the right location or have naming issues, this step will catch it.

Step 5: Test the migration

Run the full test suite to ensure the migration applies cleanly:

cargo test --workspace --all-features

SQLite tests use in-memory databases and run all migrations from scratch on every test, so any migration issue will surface immediately.

For PostgreSQL tests (gated behind #[cfg(feature = "postgres-tests")]), the migration runner also applies from scratch against a test database.

Keeping Version Numbers Aligned

The version numbers in migrations/postgres/ and migrations/sqlite/ must always match one-to-one. Refinery tracks applied versions per database — if PostgreSQL is on V32 but SQLite only has files up to V31, SQLite tests will fail when they encounter V32 in the PostgreSQL directory (or vice versa if you run them against the wrong directory).

Checklist before committing a migration:

• Both migrations/postgres/V{N}__name.sql and migrations/sqlite/V{N}__name.sql exist
• Version number N is the next sequential integer (no gaps, no duplicates)
• File names match the V{N}__{description}.sql pattern (double underscore)
• cargo check --workspace passes (migrations compile into binary)
• cargo test --workspace passes (migrations apply cleanly)

Common Pitfalls

Seed Data Migrations

Some migrations insert data rather than alter schema. For example, V28__seed_default_workflows.sql inserts default workflow states. These follow the same dual-backend rules — the SQL syntax for INSERT is usually identical across both backends, but watch for type differences in the values (e.g., UUID format, timestamp format).