---
title: Migrations
description: 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:

```txt
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

1. **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.

2. **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.

3. **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.

4. **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.

### Verifying an organization

`pnpm db:verify:org` runs `verify-org.ts` - a helper for checking an organization's state during development.

### Seeding an admin

`pnpm db:seed:admin` runs `seed-admin.ts`, which calls `AdminAuthService.seed` with admin credentials so you can sign in to the admin console.

### Script anatomy

All scripts follow the same shape (see `migrate.ts`/`seed.ts`):

```ts
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.