Database Schema & Drizzle ORM

Database state is defined using Drizzle ORM with 26 schema files in apps/web/lib/db/schema/.

---

Schema Architecture

apps/web/lib/db/schema/
├── rbac.ts            # roles, role_permissions, user_roles
├── profiles.ts        # User profiles & preferences
├── leads.ts           # Leads, deals, pipelines, stages
├── contacts.ts        # Contacts & organizations
├── vendors.ts         # Vendors & vendor_strike_logs
├── tasks.ts           # Tasks, subtasks, task_templates
├── finance.ts         # Invoices, time_entries, incentive_reports
├── ai.ts              # ai_chat_history, case_studies
└── whatsapp.ts        # whatsapp_chats, whatsapp_messages

---

Database Connection Strategy

Per AGENTS.md, Neon PostgreSQL connections are split into two pools:

1. Runtime Transaction Pool (DATABASE_RUNTIME_URL): Used for standard app traffic in serverless runtimes (Vercel, Cloudflare Workers). Uses unnamed prepared statements only.
2. Owner System DB (DATABASE_URL): Direct owner client used for migrations, system bootstrapping, and querying sensitive security tables (roles, role_permissions, user_roles).

---

Migration Workflow

# Generate new migration files after schema edits
pnpm db:generate

# Execute pending migrations against target database
pnpm db:migrate

[!CAUTION] Do NOT use drizzle-kit push directly against production. Always generate and apply migration scripts.