> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.adid.dev/llms.txt.
> For full documentation content, see https://docs.adid.dev/llms-full.txt.

# Migrations & Seed

The API uses raw SQL migrations (verified at `packages/api/migrations/*.sql`) executed in **filename-sorted order**. The migration runner is `cmd/migrate` (invoked by `make db-migrate`). The seeder (`cmd/seed`) inserts a small set of demo users, schemas, and trust framework rows for development. The ER diagram above shows the core Postgres schema produced by these migrations and seeded by `cmd/seed`.

#### 14.6.1 Migration files (current as of v2.0)

| File                               | Purpose                                                                                           |
| ---------------------------------- | ------------------------------------------------------------------------------------------------- |
| `001_init.sql`                     | Bootstrap — `dids`, `credentials`, `schemas`, `revocations`, `agents` core tables and indexes.    |
| `002_did_indexes.sql`              | Performance — composite indexes on `dids(user_id, status)` and `credentials(issuer_did, status)`. |
| `002_vc_enhancements.sql`          | VC v2.0 fields — `proof_jwt`, `status_list_id`, `status_list_idx`.                                |
| `003_uat_tables.sql`               | UAT support — feature flags, sandbox flags.                                                       |
| `004_users_and_auth.sql`           | `users`, `sessions`, `oauth_links` tables; role column with default `holder`.                     |
| `005_wallets_and_oauth.sql`        | Embedded MPC wallet storage; OAuth nonce/state.                                                   |
| `006_data_access.sql`              | Audit / data-subject-access grants.                                                               |
| `006_delegates_and_attributes.sql` | DID delegates and attributes — supports `POST /dids/{did}/delegates` and `/attributes`.           |

> **Note on duplicate `006_` prefixes:** the runner sorts filenames; ties are broken alphabetically. `006_data_access.sql` is applied before `006_delegates_and_attributes.sql`.

#### 14.6.2 Running migrations

```bash
# From repo root
make db-migrate

# Equivalent:
cd packages/api && go run ./cmd/migrate
```

The migrator:

1. Connects via the same env vars as the server (`DB_HOST`, `DB_PASS`, ...).
2. Ensures a `schema_migrations` table exists.
3. Reads `migrations/` directory.
4. For each file not yet recorded, runs the SQL inside a transaction and records the filename in `schema_migrations`.
5. Stops on first error (transaction rolled back).

> **Idempotency:** migrations are NOT idempotent by themselves. Once applied, they cannot be re-run; you must write a new migration to alter previously applied schema.

#### 14.6.3 Authoring a new migration

1. Pick the next sequence number: `007_<short-name>.sql`.
2. Always wrap DDL in `BEGIN; ... COMMIT;` (the runner runs it inside a tx, but explicit is safer).
3. Add an inverse rollback migration in the same numbered pair if your runner supports `down/` migrations. 4. Add `CREATE INDEX CONCURRENTLY` for production-load tables — but be aware: `CONCURRENTLY` cannot run inside a transaction, so it requires a separate migration file with the directive `-- migrate:run-in-tx false` (or equivalent). 5. Test on a copy of production data before merging.

#### 14.6.4 Seeding the database

```bash
# From repo root
cd packages/api && go run ./cmd/seed
```

The seeder (verified at `packages/api/cmd/seed/main.go`) creates:

* **Demo users** — `admin@ida.local` (admin), `issuer@example.com` (issuer), `verifier@example.com` (verifier), `alice@example.com` (holder).
* **Demo schemas** — `EmploymentVC`, `EducationDegreeVC`, `KYC_LightVC`.
* **Demo trust framework rows** — pre-trusts `issuer@example.com` for all schemas.
* **Sample DIDs and credentials** for each demo user.

Pass `--reset` to wipe before seeding (destructive — dev only):

```bash
go run ./cmd/seed --reset
```

> **Production:** never run the seeder on production. Use a one-off SQL script for the initial admin user only.

#### 14.6.5 Backups before migrations

Always snapshot before applying a migration in production:

```bash
pg_dump --format=custom --file=backup-pre-007.dump \
  postgres://ida:$DB_PASS@$DB_HOST:5432/ida
```

Store the dump in object storage and verify with `pg_restore --list backup-pre-007.dump`.

***