Skip to main content
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:
  1. Reads migration.sql
  2. Builds a JSON payload with the run-sql action
  3. Invokes the Worker Lambda
  4. 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: 
1. Deploy infrastructure (if needed):  pnpm --filter @alliance-risk/infra cfn:deploy dev
2. Run database migrations:            pnpm migrate:remote
3. Deploy API:                         pnpm deploy:api
4. 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

FilePurposeWhen to Edit
schema.prismaSource of truth for DB schemaWhen adding/modifying models
migrations/*/migration.sqlSQL statements to apply schema changesNever edit manually (generated by prisma migrate dev)
seed.tsInitial 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: '[email protected]' },
    update: {},
    create: {
      email: '[email protected]',
      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

ToolCommandPurpose
prisma migrate devnpx prisma migrate dev --name <name>Create new migration (local dev)
prisma migrate deploynpx prisma migrate deployApply pending migrations (local only)
pnpm migrate:remotebash scripts/migrate-remote.shApply migrations to remote RDS
prisma migrate statusnpx prisma migrate statusCheck migration status
prisma generatenpx prisma generateRegenerate Prisma Client
prisma studionpx prisma studioGUI database browser (local only)
prisma db pushnpx prisma db pushPrototype schema changes (skip migrations)

Build docs developers (and LLMs) love

Get started for freeTalk to us