Migrations

The migration workflow - how schema changes are authored, applied, and recorded, plus the seeds and registry scripts.

All schema changes in Africa OS are SQL migrations in infra/database/migrations, applied in order by Effect’s PgMigrator and recorded in platform_migrations. This page documents the workflow.

How migrations work

Each migration is a numbered .sql file:

infra/database/migrations/
├── 0001_identity.sql
├── 0002_platform_users.sql
├── 0003_organizations.sql
├── 0004_permissions.sql
├── 0005_applications.sql
├── 0006_organization_lifecycle.sql
├── 0007_application_registry_metadata.sql
├── 0008_retail_products.sql
├── 0009_admin.sql
└── 0009_admin_waitlist_emails.sql

The name format is <number>_<slug>.sql. migrate.ts reads them, sorts them, splits each file into individual statements, and applies the pending ones through PgMigrator:

In order - files sort by their numeric prefix.
Recorded - applied migrations are stored in platform_migrations.
Re-runnable - running pnpm db:migrate again is a no-op for already-applied files.
Atomic - all pending migrations run inside one transaction; a failure midway leaves the database exactly where it started.

Adding a migration

Pick the next number

Check the highest numeric prefix and use the next one. If two files legitimately share a number (as 0009_admin.sql and 0009_admin_waitlist_emails.sql do), they still apply in filename order.

Write forward-only SQL

Migrations are forward-only by convention - they assume the previous state and leave the database consistent. Use IF NOT EXISTS on tables/indexes and be explicit about check constraints and defaults.

Handle status refinements carefully

When narrowing a CHECK constraint (as 0006 did for lifecycle statuses), backfill any rows that would violate the new constraint before adding it. 0006 sets every 'active' organization to 'pending' for exactly this reason.

Apply it

Run pnpm db:migrate against your local database and confirm it applies cleanly.

The migrate script

infra/database/scripts/migrate.ts is an infrastructure script, not a library:

It reads DATABASE_URL from the validated config.
It builds its own PgClient and runs PgMigrator as a Node main program (NodeRuntime.runMain).
It provides Logger.Live so migration logging flows through the shared logger.
Platform services never call it - they go through @africaos/database.

Seeds

Seeds live in infra/database/seeds/ and are plain .sql files written to be re-runnable: every statement upserts by a stable id or slug.

Seed	When used	Content
`development.sql`	`pnpm db:seed` (default)	A working local environment: users, organizations across the lifecycle, the application registry, roles. Deterministic upserts.
`production.sql`	`pnpm db:seed:production`	The environment-agnostic baseline only.

The seed script (seed.ts) reads the file and executes statements one at a time (the client’s prepared-statement path accepts a single command per call). It stays quiet on purpose: a run either succeeds with exit code zero or fails loudly.

Registering applications

pnpm db:register-apps runs register-apps.ts, which reads every application package’s app.config.ts, validates the definitions against the registry schema, and upserts them into platform.applications. Run it after adding or changing an application’s config.

import "dotenv/config";
import { env } from "@africaos/config";
import { NodeRuntime, NodeServices } from "@effect/platform-node";
import { PgClient } from "@effect/sql-pg";
import { Effect, FileSystem, Redacted } from "effect";

const program = Effect.gen(function* () {
  // ... read files, run statements
});

program.pipe(
  Effect.provide(PgClient.layer({ url: Redacted.make(env.DATABASE_URL) })),
  Effect.provide(NodeServices.layer),
  NodeRuntime.runMain
);

They resolve their input directories from their own location (fileURLToPath), so they work regardless of the working directory.

Guidelines

Never hand-edit applied migrations. Write a new migration for the next change.
Forward-only by default. Do not write rollbacks; the migration ledger handles history.
Add IF NOT EXISTS for re-entrant schema objects.
Backfill before constraining when narrowing valid values.
Test locally before committing - run pnpm db:migrate + pnpm db:seed on a fresh database.