Database Migrations

Auto-generate your docs

The CGIAR Risk Intelligence Tool uses Prisma for database schema management and migrations. The RDS PostgreSQL database is in a private VPC with no public internet access, so migrations must be run through the Worker Lambda function.

Why Remote Migrations?

The RDS instance is configured with:

Public Access: Disabled
Security Group: Allows inbound PostgreSQL (5432) only from Lambda security group
VPC: Private subnets (no internet gateway route)

Local machines cannot reach the database directly. The Worker Lambda is deployed inside the same VPC and can access the database.

Quick Start

From the project root:

pnpm migrate:remote

This runs scripts/migrate-remote.sh, which sends Prisma migration SQL to the Worker Lambda’s run-sql action.

Migration Pipeline

The migrate-remote.sh script performs the following steps:

1. Discover Migration Files

Prisma migrations are stored in packages/api/prisma/migrations/ with timestamp-prefixed directories:

prisma/migrations/
  20240101000000_init/
    migration.sql
  20240102120000_add_users_table/
    migration.sql
  20240103150000_add_prompts_table/
    migration.sql

The script discovers all migration directories and sorts them by timestamp:

MIGRATION_DIRS=$(find packages/api/prisma/migrations \
  -mindepth 1 -maxdepth 1 -type d | sort)

2. Send SQL to Worker Lambda

For each migration file, the script:

Reads migration.sql
Builds a JSON payload with the run-sql action
Invokes the Worker Lambda
Parses the response for success/failure

PAYLOAD=$(python3 -c "
import json
sql = open('${SQL_FILE}').read()
payload = json.dumps({'action': 'run-sql', 'sql': sql}, ensure_ascii=True)
print(payload)
")

aws lambda invoke --function-name alliance-risk-worker \
  --payload "${PAYLOAD}" \
  --cli-read-timeout 120 \
  result.json

Worker Lambda Handler (packages/api/src/worker.ts):

export const handler = async (event, context) => {
  context.callbackWaitsForEmptyEventLoop = false;

  if (event.action === 'run-sql') {
    const prisma = cachedContext.get(PrismaService);
    const statements = event.sql.split(';').filter(s => s.trim());
    let executed = 0;

    for (const stmt of statements) {
      await prisma.$executeRawUnsafe(stmt);
      executed++;
    }

    return { success: true, executed };
  }

  // ... other actions
};

3. Handle Already-Applied Migrations

If a migration was already applied (e.g., table already exists), Prisma throws an error:

error: relation "users" already exists

The script detects this and skips the migration:

if echo "${ERROR}" | grep -qi "already exists"; then
  echo "✓ ${MIGRATION_NAME}: already applied (skipped)"
else
  echo "✗ ${MIGRATION_NAME} failed: ${ERROR}"
  exit 1
fi

4. Update Migration Tracking Table

Prisma maintains a _prisma_migrations table to track which migrations have been applied:

CREATE TABLE IF NOT EXISTS "_prisma_migrations" (
  "id" VARCHAR(36) NOT NULL,
  "checksum" VARCHAR(64) NOT NULL,
  "finished_at" TIMESTAMPTZ,
  "migration_name" VARCHAR(255) NOT NULL,
  "logs" TEXT,
  "rolled_back_at" TIMESTAMPTZ,
  "started_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "applied_steps_count" INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY ("id")
);

The script inserts a row for each applied migration:

INSERT INTO "_prisma_migrations" (
  "id", "checksum", "finished_at", "migration_name", "applied_steps_count"
)
VALUES (
  '$(uuidgen)', 'remote-migration', now(), '20240101000000_init', 1
)
ON CONFLICT DO NOTHING;

Why? This ensures prisma migrate deploy and prisma migrate status recognize the migrations as applied.

Creating New Migrations

Migrations are created during local development:

1. Modify Prisma Schema

Edit packages/api/prisma/schema.prisma:

model Job {
  id        String   @id @default(uuid())
  type      String
  status    String   @default("PENDING")
  result    Json?
  error     String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

2. Generate Migration

Run Prisma’s migration tool against your local PostgreSQL database:

cd packages/api
DATABASE_URL=postgresql://user@localhost:5432/alliance_risk \
  npx prisma migrate dev --name add_jobs_table

Output:

Prisma Migrate created and applied the following migration:

migrations/
  └─ 20240104180000_add_jobs_table/
      └─ migration.sql

Generated SQL (migration.sql):

-- CreateTable
CREATE TABLE "Job" (
    "id" TEXT NOT NULL,
    "type" TEXT NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'PENDING',
    "result" JSONB,
    "error" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Job_pkey" PRIMARY KEY ("id")
);

3. Commit Migration

Commit the migration file to git:

git add packages/api/prisma/migrations/
git commit -m "feat: add jobs table"

4. Deploy to Remote Database

Run the remote migration script:

pnpm migrate:remote

Output:

▸ Applying migration: 20240101000000_init
✓ 20240101000000_init: already applied (skipped)
▸ Applying migration: 20240102120000_add_users_table
✓ 20240102120000_add_users_table: already applied (skipped)
▸ Applying migration: 20240104180000_add_jobs_table
✓ 20240104180000_add_jobs_table: 5 statements
▸ Updating migration tracking table...

═══════════════════════════════════════════════════
  Migration complete: 5 total statements
═══════════════════════════════════════════════════

Deployment Order

When schema changes are deployed:

Deploy infrastructure (if needed):  pnpm --filter @alliance-risk/infra cfn:deploy dev
Run database migrations:            pnpm migrate:remote
Deploy API:                         pnpm deploy:api
Deploy web (if needed):             pnpm deploy:web

Critical: Migrations must run before deploying the API. If the API expects tables that don’t exist yet, it will crash.

Schema vs. Migrations vs. Seed

File	Purpose	When to Edit
`schema.prisma`	Source of truth for DB schema	When adding/modifying models
`migrations/*/migration.sql`	SQL statements to apply schema changes	Never edit manually (generated by `prisma migrate dev`)
`seed.ts`	Initial data (admin user, sample prompts)	When adding default data

Local Development Workflow

Local development uses a separate PostgreSQL database (not the remote RDS):

1. Set Up Local Database

Create a local PostgreSQL database:

psql -U postgres
CREATE DATABASE alliance_risk;

2. Configure `.env`

Create packages/api/.env:

DATABASE_URL=postgresql://postgres@localhost:5432/alliance_risk
COGNITO_USER_POOL_ID=local
COGNITO_CLIENT_ID=local
COGNITO_REGION=us-east-1
WORKER_LAMBDA_NAME=local
FILE_BUCKET_NAME=local
ENVIRONMENT=development

3. Apply Migrations

Apply all migrations to the local database:

cd packages/api
npx prisma migrate deploy

4. Seed Database

Populate the local database with sample data:

cd packages/api
npx tsx prisma/seed.ts

Seed Script (packages/api/prisma/seed.ts):

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  // Create admin user
  await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email: 'admin@example.com',
      cognitoId: 'local-admin',
      isAdmin: true,
    },
  });

  console.log('✓ Seed complete');
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Troubleshooting

Migration fails with “relation already exists”

Cause: Migration was already applied (the tracking table is out of sync) Solution: The script automatically skips these. If the migration truly needs to run, drop the table manually via run-sql:

aws lambda invoke --function-name alliance-risk-worker \
  --payload '{"action":"run-sql","sql":"DROP TABLE IF EXISTS jobs"}' \
  /tmp/result.json

Then re-run pnpm migrate:remote.

`DATABASE_URL` not set

Cause: The CloudFormation template is missing the DATABASE_URL environment variable on the Worker Lambda Solution: Verify the CloudFormation template has:

WorkerLambda:
  Environment:
    Variables:
      DATABASE_URL: !Sub
        - "postgresql://${Username}:${Password}@${Host}:5432/alliance_risk"
        - Username: postgres
          Password: !Sub "{{resolve:secretsmanager:${DbSecret}:SecretString:password}}"
          Host: !GetAtt Database.Endpoint.Address

Prisma Client out of sync with schema

Cause: Schema was modified but Prisma Client was not regenerated Solution: Regenerate Prisma Client:

cd packages/api
npx prisma generate

This updates .prisma/client with the latest schema.

Migration timeout (Lambda times out after 120s)

Cause: Migration SQL is too large or slow Solution: Increase --cli-read-timeout in migrate-remote.sh:

aws lambda invoke --function-name alliance-risk-worker \
  --payload "${PAYLOAD}" \
  --cli-read-timeout 300 \
  result.json

Direct SQL Execution (Advanced)

For ad-hoc SQL queries or schema inspection, you can invoke the Worker Lambda directly:

aws lambda invoke --function-name alliance-risk-worker \
  --payload '{"action":"run-sql","sql":"SELECT * FROM users LIMIT 5"}' \
  /tmp/result.json

cat /tmp/result.json

Response:

{
  "success": true,
  "executed": 1
}

Important: $executeRawUnsafe does not return query results, only the number of rows affected. Use Prisma Studio or a SQL client for inspecting data.

Prisma Studio (Local Only)

Prisma Studio provides a GUI for browsing and editing database data:

cd packages/api
npx prisma studio

Accessible at: http://localhost:5555 Remote Database: Prisma Studio cannot connect to the remote RDS (private VPC). Use it only for local development.

Rollback Migrations

Prisma does not support automatic rollback. To rollback a migration:

1. Create a New Migration

Write the inverse SQL manually:

cd packages/api
npx prisma migrate dev --name rollback_add_jobs_table

Edit migration.sql:

DROP TABLE "Job";

2. Apply the Rollback Migration

pnpm migrate:remote

Better approach: Modify the schema and generate a new forward migration instead of rolling back.

Best Practices

Never edit migration.sql manually — Always use prisma migrate dev to generate migrations
Test migrations locally first — Run against local PostgreSQL before deploying to RDS
One migration per feature — Keep migrations small and focused
Commit migrations to git — Migrations are part of the codebase, not infrastructure
Run migrations before deploying API — Ensure schema is up-to-date before code expects new tables
Avoid destructive migrations — Drop tables only when absolutely necessary (backup first)

Migration Safety

RDS Backups: 7-day retention (automated)
Snapshot on Deletion: RDS stack has DeletionPolicy: Snapshot (production)
Test Environment: Always test migrations in dev/staging before production
Idempotent Migrations: Use IF NOT EXISTS, IF EXISTS where possible
Transaction Wrapping: Each migration runs in a transaction (automatic rollback on failure)

Schema Management Tools

Tool	Command	Purpose
`prisma migrate dev`	`npx prisma migrate dev --name <name>`	Create new migration (local dev)
`prisma migrate deploy`	`npx prisma migrate deploy`	Apply pending migrations (local only)
`pnpm migrate:remote`	`bash scripts/migrate-remote.sh`	Apply migrations to remote RDS
`prisma migrate status`	`npx prisma migrate status`	Check migration status
`prisma generate`	`npx prisma generate`	Regenerate Prisma Client
`prisma studio`	`npx prisma studio`	GUI database browser (local only)
`prisma db push`	`npx prisma db push`	Prototype schema changes (skip migrations)

Setup

AWS Infrastructure

Operations

Why Remote Migrations?

Quick Start

Migration Pipeline

1. Discover Migration Files

2. Send SQL to Worker Lambda

3. Handle Already-Applied Migrations

4. Update Migration Tracking Table

Creating New Migrations

1. Modify Prisma Schema

2. Generate Migration

3. Commit Migration

4. Deploy to Remote Database

Deployment Order

Schema vs. Migrations vs. Seed

Local Development Workflow

1. Set Up Local Database

2. Configure `.env`

3. Apply Migrations

4. Seed Database

Troubleshooting

Migration fails with “relation already exists”

`DATABASE_URL` not set

Prisma Client out of sync with schema

Migration timeout (Lambda times out after 120s)

Direct SQL Execution (Advanced)

Prisma Studio (Local Only)

Rollback Migrations

1. Create a New Migration

2. Apply the Rollback Migration

Best Practices

Migration Safety

Schema Management Tools

​Why Remote Migrations?

​Quick Start

​Migration Pipeline

​1. Discover Migration Files

​2. Send SQL to Worker Lambda

​3. Handle Already-Applied Migrations

​4. Update Migration Tracking Table

​Creating New Migrations

​1. Modify Prisma Schema

​2. Generate Migration

​3. Commit Migration

​4. Deploy to Remote Database

​Deployment Order

​Schema vs. Migrations vs. Seed

​Local Development Workflow

​1. Set Up Local Database

​2. Configure .env

​3. Apply Migrations

​4. Seed Database

​Troubleshooting

​Migration fails with “relation already exists”

​DATABASE_URL not set

​Prisma Client out of sync with schema

​Migration timeout (Lambda times out after 120s)

​Direct SQL Execution (Advanced)

​Prisma Studio (Local Only)

​Rollback Migrations

​1. Create a New Migration

​2. Apply the Rollback Migration

​Best Practices

​Migration Safety

​Schema Management Tools

Why Remote Migrations?

Quick Start

Migration Pipeline

1. Discover Migration Files

2. Send SQL to Worker Lambda

3. Handle Already-Applied Migrations

4. Update Migration Tracking Table

Creating New Migrations

1. Modify Prisma Schema

2. Generate Migration

3. Commit Migration

4. Deploy to Remote Database

Deployment Order

Schema vs. Migrations vs. Seed

Local Development Workflow

1. Set Up Local Database

2. Configure `.env`

3. Apply Migrations

4. Seed Database

Troubleshooting

Migration fails with “relation already exists”

`DATABASE_URL` not set

Prisma Client out of sync with schema

Migration timeout (Lambda times out after 120s)

Direct SQL Execution (Advanced)

Prisma Studio (Local Only)

Rollback Migrations

1. Create a New Migration

2. Apply the Rollback Migration

Best Practices

Migration Safety

Schema Management Tools