> ## Documentation Index
> Fetch the complete documentation index at: https://alliancebioversityciat-alliance-risk-analysis-tool.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Migrations

> Running Prisma migrations on RDS via Worker Lambda

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:

```bash theme={null}
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:

```bash theme={null}
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

```bash theme={null}
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`):**

```typescript theme={null}
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:

```bash theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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`:

```prisma theme={null}
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**:

```bash theme={null}
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`):**

```sql theme={null}
-- 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:

```bash theme={null}
git add packages/api/prisma/migrations/
git commit -m "feat: add jobs table"
```

### 4. Deploy to Remote Database

Run the remote migration script:

```bash theme={null}
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:

```bash theme={null}
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

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

```bash theme={null}
psql -U postgres
CREATE DATABASE alliance_risk;
```

### 2. Configure `.env`

Create `packages/api/.env`:

```bash theme={null}
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:

```bash theme={null}
cd packages/api
npx prisma migrate deploy
```

### 4. Seed Database

Populate the local database with sample data:

```bash theme={null}
cd packages/api
npx tsx prisma/seed.ts
```

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

```typescript theme={null}
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`:

```bash theme={null}
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:

```yaml theme={null}
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:

```bash theme={null}
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`:

```bash theme={null}
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:

```bash theme={null}
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:**

```json theme={null}
{
  "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:

```bash theme={null}
cd packages/api
npx prisma studio
```

**Accessible at:** [http://localhost:5555](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:

```bash theme={null}
cd packages/api
npx prisma migrate dev --name rollback_add_jobs_table
```

Edit `migration.sql`:

```sql theme={null}
DROP TABLE "Job";
```

### 2. Apply the Rollback Migration

```bash theme={null}
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) |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.