Database

npm run db:push          # Push schema changes to dev database
npx drizzle-kit push     # Alternative direct push
npx drizzle-kit studio   # Open Drizzle Studio for visual DB management

Schema Conventions

All Drizzle table definitions live in shared/schema.ts. When adding tables:

1. Define the table with pgTable()
2. Create insert schema with createInsertSchema from drizzle-zod
3. Export insert type via z.infer<typeof insertSchema>
4. Export select type via typeof table.$inferSelect
5. Run npm run db:push to apply

Safety Rules

• Always use Drizzle ORM for migrations (never raw DDL)
• Never execute destructive SQL (DROP, DELETE, UPDATE) without explicit approval
• Use transactions for multi-table operations

Dual Migration System

1. Drizzle SQL Migrations (migrations/)

Generated by Drizzle Kit from the schema definition. These are the canonical DDL migrations.

• Schema source: shared/schema.ts (Drizzle ORM table definitions)
• Config: drizzle.config.ts (output dir: ./migrations, dialect: PostgreSQL)
• Generated files: Sequential SQL files like 0000_brainy_mother_askani.sql
• Metadata: migrations/meta/ contains Drizzle's migration journal

Dev workflow:

npx drizzle-kit push     # Push schema changes directly to dev DB (no migration file)
npx drizzle-kit generate  # Generate a migration SQL file from schema diff

Use db:push during development for rapid iteration. Generate migration files before committing for production deployments.

2. TypeScript Startup Migrations (server/migrations/)

Runtime migrations that execute on server startup. Used for:

• Adding columns with ALTER TABLE ... ADD COLUMN IF NOT EXISTS
• Data transformations (fixing legacy ownership, cleaning orphaned rows)
• Creating indexes after tables exist
• Dropping deprecated tables/constraints

Each migration is a standalone .ts file exporting an async function.

Current files:

server/migrations/
├── auto-research-refresh-001.ts
├── companies-theme-001.ts
├── composite-indexes-001.ts
├── db-hygiene-001.ts
├── documents-001.ts
├── drop-plaid-001.ts
├── fix-shared-ownership.ts
├── fk-indexes-001.ts
├── funding-interest-001.ts
├── google-id-001.ts
├── icp-config-001.ts
├── inflation-per-entity-001.ts
├── marcela-voice-001.ts
├── notification-logs-001.ts
├── property-photos-001.ts
└── research-config-001.ts

Naming Convention

TypeScript migrations: {feature}-{sequence}.ts

• Feature: kebab-case descriptor of what the migration does
• Sequence: three-digit counter starting at 001
• Examples: research-config-001.ts, funding-interest-001.ts

Special migrations: Descriptive names without sequence numbers for one-off fixes:

• fix-shared-ownership.ts, drop-plaid-001.ts

Export convention: Each file exports a named function (not default):

// Standard pattern
export async function runMigration(): Promise<void> { ... }

// Or descriptive name
export async function runDbHygiene001() { ... }
export async function fixLegacyOwnership() { ... }

Orchestration: runMigrationsAndSeeds()

Located in server/index.ts (called after HTTP server starts listening). Follows a strict ordering:

Phase 1: Parallel Independent Migrations

Migrations that don't depend on each other run in parallel for faster startup:

const [
  { runMigration: runResearchConfig001 },
  { runMigration: runInflationPerEntity001 },
  // ... 9 more independent migrations
] = await Promise.all([
  import("./migrations/research-config-001"),
  import("./migrations/inflation-per-entity-001"),
  // ...
]);
await Promise.all([
  runResearchConfig001(),
  runInflationPerEntity001(),
  // ...
]);

Phase 2: Sequential Dependencies

Migrations that depend on Phase 1 results run one at a time:

// Must run after table-creating migrations
const { runNotificationLogs001 } = await import("./migrations/notification-logs-001");
await runNotificationLogs001();

// FK indexes must run after all tables exist
const { runFkIndexes001 } = await import("./migrations/fk-indexes-001");
await runFkIndexes001();

Phase 3: Cleanup

Data hygiene and legacy fixes:

const { runDbHygiene001 } = await import("./migrations/db-hygiene-001");
await runDbHygiene001();

const { fixLegacyOwnership } = await import("./migrations/fix-shared-ownership");
await fixLegacyOwnership();

Phase 4: Seeding

After all migrations complete:

await seedAdminUser();  // Users first (FK dependency)

// Independent seeds run in parallel
await Promise.all([
  seedMissingMarketResearch(),
  seedMarketRates(),
  seedDefaultLogos(),
  seedUserGroups(),
  seedFeeCategories(),
  seedServiceTemplates(),
  seedPropertyPhotos(),
  seedGlobalAssumptions(),
]);

// Dependent seeds run sequentially
await seedCompanies();
await seedUserCompanyAssignments();

// Post-seed cleanup
await cleanOrphanedLogos();

Idempotency Requirements

Every TypeScript migration must be safe to run multiple times:

// CORRECT — idempotent
await db.execute(sql`
  ALTER TABLE global_assumptions
  ADD COLUMN IF NOT EXISTS research_config jsonb DEFAULT '{}'::jsonb
`);

// CORRECT — check before dropping
const result = await db.execute(sql`
  SELECT constraint_name FROM information_schema.table_constraints
  WHERE table_name = ${table} AND constraint_name IN (${drop}, ${keep})
`);
if (names.has(drop) && names.has(keep)) {
  await db.execute(sql.raw(`ALTER TABLE "${table}" DROP CONSTRAINT "${drop}"`));
}

// WRONG — will fail on second run
await db.execute(sql`ALTER TABLE foo ADD COLUMN bar text`);

Writing a New Migration

1. Add the column/table to shared/schema.ts first (Drizzle schema is the source of truth)
2. Create server/migrations/{feature}-{sequence}.ts:

   import { db } from "../db";
   import { sql } from "drizzle-orm";
   
   const TAG = "{feature}-{sequence}";
   
   export async function runMigration(): Promise<void> {
     try {
       await db.execute(sql`
         ALTER TABLE {table}
         ADD COLUMN IF NOT EXISTS {column} {type} DEFAULT {default}
       `);
       console.info(`[INFO] [${TAG}] Column added (or already existed)`);
     } catch (error: any) {
       console.error(`[ERROR] [${TAG}] Migration failed: ${error.message}`);
       throw error;
     }
   }
   

3. Register in runMigrationsAndSeeds() in server/index.ts:
   • If independent: add to Phase 1 parallel block
   • If depends on other migrations: add after the appropriate phase
4. Test locally with npx drizzle-kit push before committing

Rules

• Never modify existing SQL migration files in migrations/ — they are immutable history
• TypeScript migrations must be idempotent (use IF NOT EXISTS, check before drop)
• Errors in migrations are caught and logged but do not crash the server
• Seed functions run after all migrations — never mix seeding into migration files
• The schema in shared/schema.ts is always the source of truth for table structure

Key Files