Migrations

Tooling

Migrations use Drizzle Kit (drizzle-kit):

Command	Purpose
npm run db:generate	Generate migration from schema changes
npm run db:migrate	Apply pending migrations
npm run db:studio	Open Drizzle Studio (GUI)

Migration History

Migration	Purpose
0000	Initial schema: buyers, sessions, otp_codes, favorites, consents, document_downloads
0001	Change favorites.unit_id to text
0002	Change document_downloads.unit_id to text
0003	Add buyer profile fields (title, biological_sex)
0004	Directus collections: agents, units, pages, portal_settings
0005	Unit images junction table
0006	Directus leads view
0007	Portal settings theme columns (colors)
0008	Multi-tenant tables: organizations, projects, portals, portal_domains, users, memberships, agent_project_assignments
0009	Add project_id to all tenant-scoped tables (buyers, sessions, otp_codes, favorites, consents, document_downloads, agents, units, unit_images, pages)
0010	Enable RLS on tenant-scoped tables, implement policies
0011	FORCE RLS on critical tables
0012	audit_events and jobs tables with jsonb columns
0013	Convertella OTP helper functions (PostgreSQL functions for OTP operations)
0014	Tenant constraints on pages + additional RLS policies
0015	Buyers org/portal refs (denormalize organization_id and portal_id onto buyers)
0016	Slug uniqueness constraints + portal RLS fixes
0017	CRM sessions and login tokens tables
0018	Unit images R2 key (support for direct R2 storage alongside Directus file IDs)

Migration Conventions

1. Never modify an existing migration — always create a new one
2. Migration files are named NNNN_descriptive_name.sql
3. Meta snapshots are stored in drizzle/meta/ alongside the journal
4. Destructive migrations need an expand/contract plan, not a one-step deploy
5. Production migrations run only during release; testing migrations run continuously

Migration Runner

The migrate.mjs script handles:

• Running Drizzle migrations
• Creating dedicated Directus DB user (directus_app) with restricted permissions
• Granting appropriate schema access

Migration Flow (spike/cf-runtime)

flowchart LR
  Generate[npm run db:generate] --> MigrateDev[npm run db:migrate-local]
  MigrateDev --> Commit[Commit migration + meta snapshot]
  Commit --> TestRun[CI: migrate test database]
  TestRun --> Release[Create release tag]
  Release --> ProdRun[migrate production database]