CASCADE uses PostgreSQL with Drizzle ORM for type-safe database access. All data access goes through repository modules — no raw SQL in application code.
src/db/schema/
erDiagram
organizations ||--o{ projects : "has"
organizations ||--o{ users : "has"
organizations ||--o{ org_memberships : "has"
organizations ||--o{ prompt_partials : "has"
projects ||--o{ project_integrations : "has"
projects ||--o{ project_credentials : "has"
projects ||--o{ agent_configs : "has"
projects ||--o{ agent_definitions : "has"
projects ||--o{ agent_trigger_configs : "has"
projects ||--o{ agent_runs : "tracks"
projects ||--o{ pr_work_items : "maps"
agent_runs ||--o| agent_run_logs : "has"
agent_runs ||--o{ agent_run_llm_calls : "logs"
agent_runs ||--o| debug_analyses : "analyzed by"
agent_runs ||--o| debug_analysis_status : "status"
users ||--o{ sessions : "has"
users ||--o{ org_memberships : "has"
organizations {
text id PK
text name
jsonb settings
}
org_memberships {
uuid id PK
uuid user_id FK
text org_id FK
text role
}
projects {
text id PK
text org_id FK
text name
text repo
text base_branch
text model
integer max_iterations
integer watchdog_timeout_ms
numeric work_item_budget_usd
jsonb agent_engine
jsonb engine_settings
}
project_integrations {
uuid id PK
text project_id FK
text category
text provider
jsonb config
jsonb triggers
}
project_credentials {
uuid id PK
text project_id FK
text env_var_key
text value
}
agent_configs {
uuid id PK
text project_id FK
text agent_type
text model
integer max_iterations
text agent_engine
jsonb agent_engine_settings
integer max_concurrency
text system_prompt
text task_prompt
text update_channel
}
agent_trigger_configs {
uuid id PK
text project_id FK
text agent_type
text event
boolean enabled
jsonb parameters
}
agent_runs {
uuid id PK
text project_id FK
text agent_type
text status
text model
integer llm_iterations
integer gadget_calls
numeric cost_usd
integer duration_ms
text pr_url
text work_item_id
text error
}
agent_run_logs {
uuid id PK
uuid run_id FK
text cascade_log
text engine_log
}
agent_run_llm_calls {
uuid id PK
uuid run_id FK
integer call_number
jsonb request
jsonb response
integer input_tokens
integer output_tokens
numeric cost_usd
integer duration_ms
}
| Table | Purpose | Key constraints |
|---|---|---|
organizations |
Multi-tenant organization definitions | — |
projects |
Per-project config (repo, model, budget, engine) | repo UNIQUE |
project_integrations |
Integration configs with category/provider | UNIQUE(project_id, category) |
project_credentials |
Encrypted credentials keyed by env var name | UNIQUE(project_id, env_var_key) |
agent_configs |
Per-agent-type overrides per project | UNIQUE(project_id, agent_type), project_id NOT NULL |
agent_definitions |
Agent YAML definitions (built-in + custom) | UNIQUE(agent_type) |
agent_trigger_configs |
Trigger enable/disable + parameters per project/agent/event | UNIQUE(project_id, agent_type, event) |
workflow_status_definitions |
Custom workflow status definitions (key, label, optional dispatch agent_type, sort order). Built-in statuses live in code (BUILTIN_WORKFLOW_STATUSES); this table only stores custom definitions. |
UNIQUE(status_key) |
agent_runs |
Agent execution records with status, cost, duration | Indexed on project_id, status, started_at |
agent_run_logs |
Cascade log + engine log per run | One-to-one with agent_runs |
agent_run_llm_calls |
LLM request/response pairs with token/cost tracking | — |
prompt_partials |
Org-scoped prompt template customizations | UNIQUE(org_id, name) |
pr_work_items |
Maps PRs and external alert sources to PM work items for run-link display and alert idempotency | Partial unique indexes on (project_id, pr_number), (project_id, work_item_id), and (project_id, external_source, external_id) when those values are present |
webhook_logs |
Raw webhook payloads for debugging | — |
users |
Dashboard users (email, bcrypt hash, role) | Org-scoped (org_id = home org, role = global role) |
org_memberships |
Multi-org membership: links a user to an org with a per-org role, so one account can belong to many orgs. users.org_id/users.role remain the home org + global role. Read by effective-org resolution + the per-org actor-role helper (spec 021 plan 2); written by the grant mutation (users.addExistingUserToOrg) and the membership-mirroring create, and read by membership-based member listing (spec 021 plan 3). The listing returns BOTH the per-org role and the global users.role so the Settings → Users editor keeps targeting the global role until the plan-4 UI reconciliation. The idempotent home-org backfill runs in migration 0053 and is re-run by 0054 so accounts created via the old createUser (and bootstrap superadmins) never vanish from the inner-join listing. |
UNIQUE(user_id, org_id) |
sessions |
Session tokens for cookie auth (30-day expiry); active_org_id (nullable) tracks the org the session is currently acting in for multi-org |
— |
debug_analyses |
AI debug analysis results | — |
debug_analysis_status |
Durable, cross-process lifecycle status (running / failed) for a debug analysis. The analysis runs in a separate worker container, so an in-memory flag is invisible to the dashboard API; the worker (and the dashboard at trigger time) writes this row instead. It is deleted on success — a present debug_analyses row is then the completed signal — and a running row older than DEBUG_ANALYSIS_RUNNING_STALE_MS is treated as stale (idle) so a crashed worker never wedges the run. Status read precedence (uniform in queue mode + local dev): active running → completed (a persisted debug_analyses row wins over a stale terminal status row) → failed → idle. failed is written only for catchable in-process errors (the runner's catch, plus the pre-runner project-config-load failure); a hard kill (watchdog/OOM) leaves the running row to self-stale to idle rather than surfacing failed, with router-side reconciliation on non-zero container exit the deliberate follow-up. |
PK on analyzed_run_id, FK → agent_runs ON DELETE CASCADE |
src/db/repositories/
Each table has a dedicated repository providing typed query methods. Key repositories:
| Repository | Purpose |
|---|---|
configRepository |
Load full project config from DB, merge integrations + credentials |
configMapper |
Transform raw DB rows to typed ProjectConfig objects |
credentialsRepository |
Credential CRUD with transparent encryption/decryption |
runsRepository |
Run lifecycle (create, update status, query by project/status) |
runLogsRepository |
Store and retrieve cascade + engine logs |
llmCallsRepository |
Log and query LLM request/response pairs |
agentConfigsRepository |
Per-agent settings CRUD |
agentDefinitionsRepository |
Agent definition CRUD (YAML ↔ JSONB) |
agentTriggerConfigsRepository |
Trigger enable/disable/params per project/agent/event |
workflowStatusDefinitionsRepository |
Custom workflow status definition CRUD; backs cascade workflow-statuses * and the workflowStatuses tRPC router |
integrationsRepository |
Query integration configuration |
projectsRepository |
Project CRUD |
organizationsRepository |
Organization CRUD |
usersRepository |
User management |
partialsRepository |
Prompt partial CRUD |
prWorkItemsRepository |
PR ↔ work item mapping |
webhookLogsRepository |
Webhook audit trail |
debugAnalysisRepository |
Debug analysis results + durable cross-process analysis lifecycle status (debug_analysis_status: mark running/failed, clear on success, read run state, staleness check) |
src/db/client.ts
DatabaseContextclass wraps Drizzle instance +pg.PoolgetDb()returns a singleton, lazily initialized fromDATABASE_URL- SSL support with optional CA certificate (
DATABASE_CA_CERT) - In workers, the DB connection is initialized eagerly (before env scrub removes
DATABASE_URL)
Migrations are hand-written SQL files in src/db/migrations/, tracked by drizzle-kit's journal (meta/_journal.json).
- Create
src/db/migrations/NNNN_description.sql - Add entry to
src/db/migrations/meta/_journal.jsonwith uniquewhentimestamp andtagmatching filename - Run
npm run db:migrate
| Command | Purpose |
|---|---|
npm run db:migrate |
Apply pending migrations |
npm run db:generate |
Generate migration SQL from schema changes |
npm run db:push |
Push schema directly (dev only) |
npm run db:studio |
Open Drizzle Studio |
npm run db:seed |
Seed from config/projects.json |
npm run db:bootstrap-journal |
Register existing migrations (one-time for push-initialized DBs) |