Deployment

Migrations & Seed

View as Markdown

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)

FilePurpose
001_init.sqlBootstrap — dids, credentials, schemas, revocations, agents core tables and indexes.
002_did_indexes.sqlPerformance — composite indexes on dids(user_id, status) and credentials(issuer_did, status).
002_vc_enhancements.sqlVC v2.0 fields — proof_jwt, status_list_id, status_list_idx.
003_uat_tables.sqlUAT support — feature flags, sandbox flags.
004_users_and_auth.sqlusers, sessions, oauth_links tables; role column with default holder.
005_wallets_and_oauth.sqlEmbedded MPC wallet storage; OAuth nonce/state.
006_data_access.sqlAudit / data-subject-access grants.
006_delegates_and_attributes.sqlDID 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

$# 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

$# 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):

$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:

$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.