The deals pipeline
The deal pipeline is three tables: crm_deals is the opportunity record, crm_pipeline_stages is the per-org customizable stage set, and crm_pipeline_config is the visual order of deals within each board column. Since ENG-931 the stage is a customizable row, not a hardcoded enum. This page traces it top-down: where it is served from, the life of a deal, then the full schema, relationships and rules.
System design
interactiveStart from the top. The pipeline board never touches storage directly — it talks to the deals subdomain (pipeline CRUD, stage moves, weighted forecast) and the pipeline_stages subdomain (per-org custom stages, admin-gated). Those are the only things that read or write the three deal tables. Click a node to trace what it connects to.
Data flow — life of a deal
interactiveHow a deal lands, renders on the board and closes, as a closed loop. A deal is created with a stage, a trigger seeds its board position, the kanban renders it, drags reorder it, stage moves shuffle it across columns, and a won or lost stage closes it — then the loop returns to the start. Click a stage to follow it.
Schema
interactiveThe pipeline spans three tables. Pick one; colour marks each column's kind, and clicking a column expands its category and any table it references. Everything is org-scoped; the deal row stores a stage key that resolves into crm_pipeline_stages, and crm_pipeline_config carries the board ordering.
One row per opportunity per org. The stage is a composite FK into crm_pipeline_stages (ENG-931), not an enum.
- primary key
Primary key.
- text
Display name of the deal.
- text
Freeform detail.
- foreign key
Composite FK (organization_id, stage) → crm_pipeline_stages (ENG-931, not an enum).
→ crm_pipeline_stages - number
Win likelihood. Can default from the stage.
- number
Deal value.
- text
ISO currency code.
- timestamp
Forecasted close.
- timestamp
Set when the deal reaches a won or lost stage.
- text
manual · sonar · referral.
- text
Origin app.
- uuid
Originating record.
- jsonb
Org-defined extras.
- array
Freeform labels.
- text
Why a lost deal was lost.
- array
Competing vendors on the deal.
- text
What happens next.
- timestamp
Soft delete (ENG-1066) — queries filter NULL.
- timestamp
Audit timestamps.
Relationships
interactiveA deal sits between an account, a contact, an owner and its stage, with a board-ordering row alongside it. Hover or click a table to trace its link and see its key columns.
crm_deals links to its account, contact, owner, customizable stage and board-ordering row.
Pipeline rules, invariants & triggers
modelThe database is storage only — no business logic. What it does enforce: the customizable per-org pipeline, the won/lost invariant, the triggers that keep board ordering honest, tenant isolation and soft delete.
ENG-931 — stages are rows, not an enum. An org renames, reorders, adds or removes them. The stable key is what a deal stores, so a relabel never rewrites deal rows.
Exactly one won stage and one lost stage per org, enforced by partial unique indexes on stage_type.
auto_add_deal_to_pipeline_config on insert seeds the board row; handle_deal_stage_change on a stage change moves it across columns; handle_updated_at maintains updated_at.
is_member_of_organization gates every read. Pipeline-stage writes are further gated to org owners and admins.
deleted_at marks a deal removed; list queries filter deleted_at IS NULL.
The forecast sums amount times probability across open deals; won and lost stages set actual_close_date and drop out of the open total.
The 7 default stages seeded per org on creation, left to right with their default probability:
Storage only — the pipeline's editability lives in data (rows), not code. An org reshapes its funnel by editing crm_pipeline_stages rows; deals store the stable key, so a relabel never rewrites a deal. The won/lost invariant and board ordering are held by constraints and triggers, and every read is gated by the same org-membership check, so one tenant's pipeline can never include another's deals.