Schema Tooling & Migrations

​

Architecture & Lifecycle

1. Source of Truth: Database tables, columns, relations, and enums are defined in backend/src/database/schema/.
2. Diff Engine: Drizzle Kit compares the TypeScript definitions against the current migration snapshot and produces a versioned SQL file.
3. Execution: The programmatic migration runner backend/src/database/migrations/migrate.ts applies unexecuted migrations inside a transaction and updates the tracking table.

​

Tooling Configuration

import { defineConfig } from 'drizzle-kit';
import 'dotenv/config';

export default defineConfig({
  schema: './src/database/schema/index.ts',
  out: './src/database/migrations/generated',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

​

Configuration Options

​

CLI Commands & Workflows

​

1. Generate SQL Migrations (db:gen)

cd backend
pnpm run db:gen

• Analyzes changes in src/database/schema/*.ts.
• Prompts for disambiguation if a column was renamed or dropped.
• Creates a new timestamped file in src/database/migrations/generated/ (e.g. 0035_smooth_thanos.sql).
• Appends the new migration metadata to src/database/migrations/generated/meta/_journal.json.

​

2. Apply Migrations Programmatically (db:migrate)

cd backend
pnpm run db:migrate

• Executes backend/src/database/migrations/migrate.ts via tsx.
• Connects to the database and reads the internal __drizzle_migrations table.
• Applies all pending migration files in chronological order within a transaction.
• Logs elapsed execution time upon completion.

​

3. Direct Schema Push (Development Only)

cd backend
pnpm dlx drizzle-kit push --config=./src/config/drizzle.config.ts

​

4. Drizzle Studio

cd backend
pnpm dlx drizzle-kit studio --config=./src/config/drizzle.config.ts

​

Programmatic Migration Runner

import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import 'dotenv/config';
import { log } from "node:console";

const databaseUrl = process.env.DATABASE_URL;

if (!databaseUrl) {
  throw new Error('DATABASE_URL is not configured');
}

const db = drizzle(databaseUrl);

async function main() {
  try {
    const startTime = Date.now();

    await migrate(db, {
      migrationsFolder: './src/database/migrations/generated',
    });

    const elapsed = Date.now() - startTime;
    log(`✅ Migration successfully completed in ${elapsed} ms`);
  } catch (err) {
    log(`🔴 Migration failed: ${err}`);
    throw err;
  }
}

main().finally(() => db.$client.end());

​

Key Implementation Details

• Migration Tracking Table: Drizzle automatically provisions a __drizzle_migrations table in PostgreSQL that stores migration IDs, hashes, and timestamps.
• Atomic Operations: Each migration file executes inside a database transaction. If any statement fails, the entire migration aborts and rolls back, preventing partial schema corruption.
• Connection Teardown: db.$client.end() explicitly closes open pool sockets so the Node process terminates cleanly in CI/CD runners.

​

Migration Journal & File Structure

backend/src/database/migrations/
├── migrate.ts
└── generated/
    ├── 0000_sour_excalibur.sql
    ├── 0001_silky_vapor.sql
    ├── ...
    ├── 0034_amazing_sunset_bain.sql
    └── meta/
        ├── 0000_snapshot.json
        └── _journal.json

​

Journal Metadata (_journal.json)

{
  "version": "7",
  "dialect": "postgresql",
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1787231327787,
      "tag": "0000_sour_excalibur",
      "breakpoints": true
    },
    {
      "idx": 1,
      "version": "7",
      "when": 1787235060484,
      "tag": "0001_silky_vapor",
      "breakpoints": true
    }
  ]
}

​

Schema Invariants & Postgres Features

​

1. Generated Always As Columns

total_cost: numeric('total_cost').generatedAlwaysAs(
  sql`"input_cost" + "output_cost"`
),

• Rule: Do not insert or update total_cost directly in application code or INSERT statements. PostgreSQL computes it automatically upon row write.

​

2. PostgreSQL Custom Enums

export const roleEnum = pgEnum('role_enum', ['admin', 'user']);
export const reviewStatus = pgEnum('review_status_enum', ['pending', 'analyzing', 'completed', 'failed']);

• Drizzle Generation Behavior: Drizzle Kit automatically emits safe type creation blocks in generated SQL:
  DO $$ BEGIN
    CREATE TYPE "public"."review_status_enum" AS ENUM('pending', 'analyzing', 'completed', 'failed');
  EXCEPTION
    WHEN duplicate_object THEN null;
  END $$;
  

​

3. Foreign Key Cascades

• users.id cascading to sessions, pull_requests, byok, and workspace_settings.
• When modifying relation keys, ensure onDelete: 'cascade' is declared so orphaned child rows do not block deletion.

​

4. Native UUID Generation

id: uuid('id').primaryKey().defaultRandom(),

​

Production Deployment Checklist

1. Never edit generated SQL files manually unless addressing custom data migrations (backfills). If you modify an existing migration file, you must update the corresponding snapshot in meta/ or regenerate the migration.
2. Release Phase Migrations: In containerized hosting environments (Fly.io, Railway, AWS ECS, Kubernetes), execute pnpm run db:migrate in the deployment release hook before starting new container instances.
3. Connection Pooling:
   • For transactional web traffic, the backend connects through the pooled connection string (pooler.neon.tech).
   • For DDL migrations (db:migrate), ensure session timeouts are sufficient to accommodate table alterations and index builds.
4. Non-Destructive Changes: Use the Expand and Contract pattern for schema changes:
   • Step 1 (Expand): Add new nullable columns or tables. Deploy application code that writes to both old and new columns.
   • Step 2 (Backfill): Run data migration script to populate new columns.
   • Step 3 (Contract): Drop old unused columns in a subsequent release.