Database Migrations

ForgeX uses Prisma for database schema management. Each service has its own independent database and migration history.

Quick Reference

Local Development

# Create migration
cd services/bids/backend
npx prisma migrate dev --name add_new_field

# Apply migrations
npx prisma migrate deploy

# Push schema (no migration)
npx prisma db push

# Reset database (WARNING: Deletes data!)
npx prisma migrate reset

Production

# 1. Start Cloud SQL Proxy
.\cloud-sql-proxy.exe forge-475221:us-south1:forge-postgres-prod --port=5432

# 2. Set DATABASE_URL
export DATABASE_URL='postgresql://forgeapp:PASSWORD@127.0.0.1:5432/bids_db'

# 3. Push schema
npx prisma db push

# 4. Redeploy backend

Prisma Schema

Each service has its own prisma/schema.prisma file:

services/
├── bids/backend/prisma/schema.prisma      # Bids database schema
├── projects/backend/prisma/schema.prisma  # Projects database schema
└── field/backend/prisma/schema.prisma     # Field database schema

Example Schema (Bids)

// services/bids/backend/prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  role      UserRole @default(ESTIMATOR)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  bids      Bid[]
  
  @@index([email])
}

model Bid {
  id          String   @id @default(uuid())
  name        String
  jobLocation String?
  clientId    String
  createdById String
  status      BidStatus @default(DRAFT)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  
  client      Client   @relation(fields: [clientId], references: [id])
  createdBy   User     @relation(fields: [createdById], references: [id])
  scopes      Scope[]
  
  @@index([clientId])
  @@index([createdById])
  @@index([status])
}

enum UserRole {
  ADMIN
  ESTIMATOR
  PM
}

enum BidStatus {
  DRAFT
  SUBMITTED
  AWARDED
  REJECTED
}

info

Prisma schemas are typed and type-safe. TypeScript types are automatically generated from the schema.

Migration Workflow

Local Development

1

Edit Schema

Make changes to prisma/schema.prisma:

model Bid {
  id          String   @id @default(uuid())
  name        String
  // ✨ Add new field
  description String?
  // ... rest of model
}

2

Create Migration

cd services/bids/backend
npx prisma migrate dev --name add_description_to_bid

This creates:

• Migration SQL file in prisma/migrations/
• Updates Prisma Client types
• Applies migration to local database

3

Review Migration

Check the generated SQL:

-- prisma/migrations/20250129_add_description_to_bid/migration.sql
ALTER TABLE "Bid" ADD COLUMN "description" TEXT;

4

Test Changes

Use the new field in your code:

const bid = await prisma.bid.create({
  data: {
    name: "New Bid",
    description: "This is a test", // ✅ Type-safe!
    clientId: "...",
    createdById: "..."
  }
});

5

Commit Migration

git add prisma/migrations/
git add prisma/schema.prisma
git commit -m "Add description field to Bid model"

Production Deployment

warning

Always backup the database before applying migrations to production!

1

Backup Database

gcloud sql backups create --instance=forge-postgres-prod

2

Start Cloud SQL Proxy

.\cloud-sql-proxy.exe forge-475221:us-south1:forge-postgres-prod --port=5432

3

Set DATABASE_URL

cd services/bids/backend
export DATABASE_URL='postgresql://forgeapp:YOUR_PASSWORD@127.0.0.1:5432/bids_db'

4

Push Schema

npx prisma db push

This applies schema changes without creating a migration file (faster for production).

5

Verify Schema

# Check table structure
psql -h 127.0.0.1 -U forgeapp -d bids_db -c "\d \"Bid\""

6

Deploy Backend

Rebuild and deploy backend with updated Prisma Client:

# From repo root
gcloud builds submit --config services/bids/backend/cloudbuild.yaml --timeout=20m
gcloud run deploy forge-bids-backend --image gcr.io/forge-475221/bids-backend:latest ...

☁️

GCP Setup Guide

Complete backend deployment commands

Common Migration Patterns

Add Field

Add nullable field:

model Bid {
  // ... existing fields
  description String?  // ✅ Nullable (safe)
}

Add required field with default:

model Bid {
  // ... existing fields
  status BidStatus @default(DRAFT)  // ✅ Has default (safe)
}

Migration:

npx prisma migrate dev --name add_field

Remove Field

Remove field:

model Bid {
  // Remove: description String?
  // ... other fields
}

warning

Destructive! Data in this column will be lost.

Migration:

npx prisma migrate dev --name remove_description

Backup first:

docker exec bidmanager-bids-db pg_dump -U postgres bids_db > backup.sql

Rename Field

Rename field:

model Bid {
  // Old: jobLocation String?
  // New:
  address String?  // Prisma will detect rename
}

Migration:

npx prisma migrate dev --name rename_job_location_to_address

tip

Prisma will prompt: "We found a change that might be a rename. Is this correct?" Choose Yes.

Add Relation

Add 1-to-many relation:

model Bid {
  // ... existing fields
  notes Note[]
}

model Note {
  id        String   @id @default(uuid())
  content   String
  bidId     String
  createdAt DateTime @default(now())
  
  bid       Bid      @relation(fields: [bidId], references: [id])
  
  @@index([bidId])
}

Migration:

npx prisma migrate dev --name add_notes

Add Index

Add index for faster queries:

model Bid {
  // ... fields
  
  @@index([status])          // Single column
  @@index([clientId, status]) // Composite index
}

Migration:

npx prisma migrate dev --name add_bid_indexes

info

Indexes speed up queries but slow down writes. Add indexes on frequently queried columns.

Change Type

Change column type:

model PricingItem {
  // Old: unitCost Float
  // New:
  unitCost Decimal @db.Decimal(10, 2)  // Precise decimal
}

Migration:

npx prisma migrate dev --name change_unit_cost_to_decimal

warning

Type changes may lose data if incompatible (e.g., String → Int).

Prisma Client

After migrations, Prisma Client is automatically updated with new types:

// TypeScript knows about the new field!
const bid = await prisma.bid.create({
  data: {
    name: "New Bid",
    description: "This is a test", // ✅ Auto-complete works!
    clientId: "...",
    createdById: "..."
  }
});

// TypeScript catches errors!
const bid2 = await prisma.bid.create({
  data: {
    name: "New Bid",
    invalidField: "Oops" // ❌ Type error: Property 'invalidField' does not exist
  }
});

info

Prisma Client is type-safe and auto-generated from your schema.

Seed Data

Seed scripts populate the database with initial data:

// services/bids/backend/prisma/seed.js

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function main() {
  // Create default variables
  await prisma.variable.createMany({
    data: [
      { name: 'wasteFactor', value: 5, location: 'Houston' },
      { name: 'markupPercent', value: 15, location: 'Houston' }
    ]
  });
  
  // Create pricing items
  await prisma.pricingItem.createMany({
    data: [
      { category: 'CONCRETE', name: '3000 PSI', unitCost: 125, unit: 'CY' },
      { category: 'REBAR', name: '#4 Rebar', unitCost: 0.65, unit: 'LB' }
    ]
  });
  
  console.log('✅ Seed data created');
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Run seed:

# Local
docker-compose exec bids-backend npx prisma db seed

# Or directly
cd services/bids/backend
npx prisma db seed

Configure in package.json:

{
  "prisma": {
    "seed": "node prisma/seed.js"
  }
}

Prisma Studio

Visual database editor:

cd services/bids/backend
npx prisma studio

Opens at http://localhost:5555 with GUI for browsing and editing data.

info

Prisma Studio is great for debugging and manual data fixes in development.

Troubleshooting

Migration fails with 'column does not exist'

Problem: Schema out of sync with database

Solutions:

1. Reset database (dev only):

npx prisma migrate reset

2. Force push schema:

npx prisma db push --force-reset

3. Manual fix:

psql -h localhost -U postgres -d bids_db -c "ALTER TABLE \"Bid\" ADD COLUMN \"description\" TEXT;"
npx prisma generate

Prisma Client out of date

Problem: Type errors after schema changes

Solution: Regenerate Prisma Client:

npx prisma generate

This updates the @prisma/client package with new types.

Migration history diverged

Problem: Local migrations don't match production

Solution:

1. Backup production database
2. Get production migration history:

psql -h 127.0.0.1 -U forgeapp -d bids_db -c "SELECT * FROM _prisma_migrations;"

3. Delete local migrations:

rm -rf prisma/migrations/

4. Create new baseline:

npx prisma migrate dev --name init

Can't connect to database

Problem: Error: P1001: Can't reach database server

Solutions:

1. Check DATABASE_URL:

echo $DATABASE_URL

2. Verify database is running:

docker ps | grep postgres

3. Test connection:

psql postgresql://postgres:devpassword@localhost:5432/bids_db

Migration takes too long

Problem: Large table migrations time out

Solution:

Use ALTER TABLE with NOT NULL in steps:

1. Add column as nullable
2. Populate data
3. Alter to NOT NULL

ALTER TABLE "Bid" ADD COLUMN "description" TEXT;
UPDATE "Bid" SET "description" = '';
ALTER TABLE "Bid" ALTER COLUMN "description" SET NOT NULL;

Best Practices

🗄️

Always Backup First

Before destructive migrations:

docker exec bidmanager-bids-db pg_dump -U postgres bids_db > backup.sql

📄

Use Descriptive Names

Migration names should describe the change:

# ✅ Good
npx prisma migrate dev --name add_description_to_bid

# ❌ Bad
npx prisma migrate dev --name update

🔬

Test Migrations

Test migrations on a copy of production data before applying to production.

🧩

Keep Migrations Small

One logical change per migration. Easier to debug and rollback.

⭕

Use Indexes

Add indexes for frequently queried columns:

@@index([status])

🛡️

Avoid Destructive Changes

Prefer additive changes (new columns, tables) over removals or renames in production.

Next Steps

📁

Project Structure

Understand where Prisma schemas live

🐳

Docker Compose

Run migrations in Docker environment

🗄️

Database Backups

Backup before schema changes

☁️

GCP Setup

Deploy schema changes to production