Database Schema & Drizzle ORM

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

---

Schema Architecture

apps/web/lib/db/schema/          # 66 files; key areas below, see the directory for the full list
├── rbac.ts                      # roles, role_permissions, user_roles
├── profiles.ts / departments.ts # user profiles, teams, departments
├── leads.ts / deals.ts          # leads; deals + deal_stage_history
├── pipelines.ts                 # service_divisions, services, pipelines, pipeline_stages
├── contacts.ts / organizations.ts
├── vendors.ts                   # vendors + vendor_strike_logs
├── tasks.ts / sequences.ts      # tasks, subtasks, templates; outreach sequences
├── projects.ts / artifacts.ts   # projects, milestones, deliverables, artifacts
├── delivery.ts                  # Delivery Studio drafts + spend tracking
├── lifecycles.ts                # lifecycle definitions, stages, events
├── finance.ts / time-entries.ts # invoices, commissions; time tracking
├── contracts.ts / compliance.ts # contracts, compliance deadlines
├── influencer*.ts / creators.ts # influencer discovery, lists, creators
├── notifications.ts / calendar-events.ts
├── copilot.ts / agent*.ts       # copilot + agent hub, runs, executions
├── action-proposals.ts / action-executions.ts
├── whatsapp.ts / email.ts / files.ts
└── activity-log.ts / comments.ts # record timelines + comments

---

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

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.