Schema Migration Strategy

Embedded numbered migrations using Go's embed package.

Each migration is a pair of .sql files: NNN_description.up.sql and NNN_description.down.sql. Separate migration directories for SQLite and PostgreSQL (syntax differences).

Directory Structure

migrations/
├── sqlite/
│   ├── 001_initial.up.sql
│   ├── 001_initial.down.sql
│   ├── 002_add_plugin_data.up.sql
│   ├── 002_add_plugin_data.down.sql
│   └── ...
└── postgres/
    ├── 001_initial.up.sql
    ├── 001_initial.down.sql
    └── ...

Rules

• Migrations are embedded in the binary — no external files needed at runtime.
• On startup, Arcan runs all pending migrations automatically.
• A schema_migrations table tracks which migrations have been applied.
• Migrations are sequential — never skip a number.
• Migrations are immutable once released. To change a past migration, create a new one.
• Down migrations are for development only. Production rollback = deploy previous version.
• Every migration file includes a comment header: purpose, date, author.

SQLite Example

-- 001_initial.up.sql
-- Purpose: Create core tables for realms, secrets, auth, policy, audit
-- Date: 2026-04-01

CREATE TABLE realms (
    id          TEXT PRIMARY KEY,  -- UUID
    user_id     TEXT NOT NULL,     -- owner (becomes org_id later)
    name        TEXT NOT NULL,
    slug        TEXT NOT NULL,
    description TEXT DEFAULT '',
    created_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    updated_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    deleted_at  TEXT,              -- soft delete
    UNIQUE(user_id, slug)
);

CREATE INDEX idx_realms_user_slug ON realms(user_id, slug) WHERE deleted_at IS NULL;

PostgreSQL Example

PostgreSQL equivalent uses native types:

-- 001_initial.up.sql (PostgreSQL)
CREATE TABLE realms (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL,
    name        TEXT NOT NULL,
    slug        TEXT NOT NULL,
    description TEXT DEFAULT '',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at  TIMESTAMPTZ,
    UNIQUE(user_id, slug)
);

CREATE INDEX idx_realms_user_slug ON realms(user_id, slug) WHERE deleted_at IS NULL;