📢 This documentation relates to the Open-Source Trading Engine upgrade which is launching shortly →
OS Trading Engine
Technical Documentation
Database
Migrations

Migrations

Nexgent uses Prisma Migrate for database schema management. Migrations are version-controlled SQL files that track schema changes over time.

Migration Location

packages/backend/src/infrastructure/database/migrations/
├── 20251211112932_init/
│   └── migration.sql
├── 20251212112507_remove_price_sol/
│   └── migration.sql
├── 20251213231745_add_swap_payload/
│   └── migration.sql
├── 20251220051448_add_balance_snapshots/
│   └── migration.sql
├── 20251220230728_add_dca_fields/
│   └── migration.sql
└── ...

Each migration folder contains:

  • migration.sql - The SQL statements executed
  • Timestamp prefix for ordering

Common Commands

Development Workflow

# Create migration from schema changes and apply it
pnpm --filter backend db:migrate
 
# Apply pending migrations without creating new ones
pnpm --filter backend db:migrate:deploy
 
# Generate Prisma client after schema changes
pnpm --filter backend db:generate
 
# Reset database (drops all data)
pnpm --filter backend db:reset
 
# View migration status
pnpm --filter backend prisma migrate status

Package.json Scripts

{
  "scripts": {
    "db:generate": "prisma generate",
    "db:migrate": "prisma migrate dev",
    "db:migrate:deploy": "prisma migrate deploy",
    "db:reset": "prisma migrate reset",
    "db:studio": "prisma studio"
  }
}

Development vs Production

Development (db:migrate)

Interactive mode that:

  1. Detects schema changes
  2. Prompts for migration name
  3. Creates migration file
  4. Applies migration
  5. Regenerates Prisma client
pnpm --filter backend db:migrate
 
# Output:
# ✔ Enter a name for the new migration: add_user_preferences
# Applying migration `20260115120000_add_user_preferences`
# ✔ Generated Prisma Client

Production (db:migrate:deploy)

Non-interactive mode that:

  1. Applies all pending migrations
  2. Fails if migrations would cause data loss
pnpm --filter backend db:migrate:deploy
 
# Output:
# 2 migrations found in prisma/migrations
# Applying migration `20260114120000_add_api_scopes`
# Applying migration `20260115120000_add_user_preferences`
# ✔ All migrations applied
⚠️

Never run db:migrate in production. Always use db:migrate:deploy which only applies existing migrations.

Creating Migrations

1. Modify the Schema

Edit schema.prisma:

model User {
  id          String   @id @default(uuid())
  email       String   @unique
  // Add new field
  preferences Json?    @map("preferences")
}

2. Create Migration

pnpm --filter backend db:migrate
# Enter name: add_user_preferences

3. Review Generated SQL

-- migrations/20260115120000_add_user_preferences/migration.sql
ALTER TABLE "users" ADD COLUMN "preferences" JSONB;

4. Commit Migration

git add packages/backend/src/infrastructure/database/migrations/
git add packages/backend/src/infrastructure/database/schema.prisma
git commit -m "Add user preferences field"

Migration Examples

Adding a Column

// schema.prisma
model Agent {
  // existing fields...
  maxPositions Int @default(5) @map("max_positions")
}

Generated SQL:

ALTER TABLE "agents" ADD COLUMN "max_positions" INTEGER NOT NULL DEFAULT 5;

Adding an Index

model AgentTransaction {
  // existing fields...
  
  @@index([agentId, createdAt])
}

Generated SQL:

CREATE INDEX "agent_transactions_agent_id_created_at_idx" 
ON "agent_transactions"("agent_id", "created_at");

Adding a Table

model Notification {
  id        String   @id @default(uuid()) @db.Uuid
  userId    String   @map("user_id") @db.Uuid
  message   String
  read      Boolean  @default(false)
  createdAt DateTime @default(now()) @map("created_at")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  @@index([userId])
  @@map("notifications")
}

Generated SQL:

CREATE TABLE "notifications" (
  "id" UUID NOT NULL DEFAULT gen_random_uuid(),
  "user_id" UUID NOT NULL,
  "message" TEXT NOT NULL,
  "read" BOOLEAN NOT NULL DEFAULT false,
  "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "notifications_pkey" PRIMARY KEY ("id")
);
 
CREATE INDEX "notifications_user_id_idx" ON "notifications"("user_id");
 
ALTER TABLE "notifications" ADD CONSTRAINT "notifications_user_id_fkey" 
  FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Renaming a Column

Prisma detects renames and asks for confirmation:

// Before
model Agent {
  bio String?
}
 
// After
model Agent {
  description String? @map("description")
}
pnpm --filter backend db:migrate
# ⚠ We need to rename the column `bio` to `description`
# Do you want to continue? (y/n)

Handling Data Migrations

For migrations that require data transformation:

1. Create Empty Migration

pnpm --filter backend prisma migrate dev --create-only
# Enter name: backfill_trading_config

2. Edit Migration SQL

-- migrations/20260115120000_backfill_trading_config/migration.sql
 
-- Add column as nullable first
ALTER TABLE "agents" ADD COLUMN "trading_config" JSONB;
 
-- Backfill existing rows with default config
UPDATE "agents" 
SET "trading_config" = '{"stopLoss":{"enabled":true,"defaultPercentage":-32}}'::jsonb
WHERE "trading_config" IS NULL;
 
-- Now make it NOT NULL if needed
-- ALTER TABLE "agents" ALTER COLUMN "trading_config" SET NOT NULL;

3. Apply Migration

pnpm --filter backend db:migrate:deploy

Rollback Strategy

Prisma doesn't support automatic rollbacks. For rollbacks:

Option 1: Reverse Migration

Create a new migration that undoes changes:

pnpm --filter backend db:migrate
# Enter name: revert_user_preferences
ALTER TABLE "users" DROP COLUMN "preferences";

Option 2: Database Restore

Restore from backup for critical issues:

# Restore from backup
pg_restore -d nexgent backup.dump

Best Practices

1. Small, Focused Migrations

# Good: One change per migration
add_user_preferences
add_notification_table
add_agent_max_positions
 
# Bad: Multiple unrelated changes
update_user_and_add_notifications_and_fix_agents

2. Test Migrations Locally

# Reset and reapply all migrations
pnpm --filter backend db:reset
 
# Verify schema matches
pnpm --filter backend prisma validate

3. Review Generated SQL

Always check the generated SQL before committing:

cat migrations/20260115120000_add_feature/migration.sql

4. Backup Before Production Deploy

# Backup database
pg_dump nexgent > backup_$(date +%Y%m%d).sql
 
# Apply migrations
pnpm --filter backend db:migrate:deploy

5. Use Transactions

Prisma wraps each migration in a transaction. For multi-step migrations, keep steps in one file:

-- All or nothing
BEGIN;
ALTER TABLE "agents" ADD COLUMN "max_positions" INTEGER;
UPDATE "agents" SET "max_positions" = 5;
ALTER TABLE "agents" ALTER COLUMN "max_positions" SET NOT NULL;
COMMIT;

Troubleshooting

Migration Drift

If the database schema doesn't match migrations:

# Check drift
pnpm --filter backend prisma migrate diff \
  --from-migrations ./prisma/migrations \
  --to-schema-datamodel ./prisma/schema.prisma
 
# Baseline if needed (marks existing DB state as migrated)
pnpm --filter backend prisma migrate resolve --applied 20251211112932_init

Failed Migration

If a migration fails partway through:

# Mark as rolled back
pnpm --filter backend prisma migrate resolve --rolled-back 20260115120000_failed
 
# Fix the migration SQL, then reapply
pnpm --filter backend db:migrate:deploy

Schema Sync Issues

If Prisma client is out of sync:

# Regenerate client
pnpm --filter backend db:generate
 
# If still issues, reset node_modules
rm -rf node_modules/.prisma
pnpm install
pnpm --filter backend db:generate
Tables ReferenceRedis Patterns