-- brick-pipeline. Tables prefixed pipeline_; natural keys (never SERIAL); every child's key starts
-- with its root's key; money NUMERIC(18,2), percentages NUMERIC(5,2). bp_id is BIGINT (shared.yaml).
-- ── the deal ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS pipeline_opportunities (
opportunity_id VARCHAR NOT NULL,
bp_id BIGINT NOT NULL, -- the customer: a business partner
name VARCHAR NOT NULL,
owner VARCHAR NOT NULL, -- the rep's email: row visibility reads it
stage VARCHAR NOT NULL, -- a pipeline_stages.name
currency VARCHAR NOT NULL,
amount NUMERIC(18,2), -- NULL = not yet known (and not forecastable)
close_date DATE,
probability NUMERIC(5,2), -- set only when this deal overrides its stage
category_override VARCHAR,
category_reason VARCHAR,
loss_reason VARCHAR,
last_activity_at TIMESTAMP, -- its own changes and activity reported by the activity brick
created_at TIMESTAMP,
updated_at TIMESTAMP,
won_quote_ids VARCHAR, -- 0.3.0: the accepted quotes it was won with (comma-separated)
voided_quote_ids VARCHAR, -- 0.3.0: quotes whose win a reopen voided — never evidence again
version INTEGER, -- 0.3.0: the optimistic version; NULL (a row from before) = 0
PRIMARY KEY (opportunity_id)
);
CREATE TABLE IF NOT EXISTS pipeline_events (
opportunity_id VARCHAR NOT NULL,
event_id VARCHAR NOT NULL,
action VARCHAR NOT NULL,
detail VARCHAR,
actor VARCHAR NOT NULL,
event_at TIMESTAMP NOT NULL,
PRIMARY KEY (opportunity_id, event_id)
);
-- ── configuration rows: the customer's own sales process (CRM-04 customer_varies) ────────
CREATE TABLE IF NOT EXISTS pipeline_settings (
pipeline_id VARCHAR NOT NULL, -- 'default'
loss_reasons VARCHAR NOT NULL, -- comma-separated, lower-case
risk_quiet_days INTEGER NOT NULL,
risk_from_pct NUMERIC(5,2) NOT NULL,
updated_by VARCHAR,
updated_at TIMESTAMP,
PRIMARY KEY (pipeline_id)
);
CREATE TABLE IF NOT EXISTS pipeline_stages (
pipeline_id VARCHAR NOT NULL,
name VARCHAR NOT NULL,
position INTEGER NOT NULL,
probability NUMERIC(5,2) NOT NULL,
category VARCHAR NOT NULL, -- pipeline | best_case | commit | closed | omitted
is_won BOOLEAN NOT NULL,
is_closed BOOLEAN NOT NULL,
PRIMARY KEY (pipeline_id, name)
);
-- ── CRM-15: the forecast as it stood on a date. One run per date; a re-run REPLACES it ────
CREATE TABLE IF NOT EXISTS pipeline_snapshot_runs (
snapshot_date DATE NOT NULL,
taken_by VARCHAR NOT NULL,
taken_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (snapshot_date)
);
CREATE TABLE IF NOT EXISTS pipeline_snapshot_rows (
snapshot_date DATE NOT NULL,
owner VARCHAR NOT NULL,
period VARCHAR NOT NULL, -- '2026-Q4'
currency VARCHAR NOT NULL,
open_amount NUMERIC(18,2) NOT NULL,
weighted_amount NUMERIC(18,2) NOT NULL,
commit_amount NUMERIC(18,2) NOT NULL,
won_amount NUMERIC(18,2) NOT NULL,
deals INTEGER NOT NULL,
unforecastable INTEGER NOT NULL,
PRIMARY KEY (snapshot_date, owner, period, currency)
);
-- ── 0.2.0: one source document, one deal ─────────────────────────────────────────────────
-- A caller that opens a deal FROM something (a lead being converted: "lead:<lead_id>") names it, so a
-- retry whose first reply was lost finds the deal it already opened instead of opening a second. DataK3
-- enforces nothing unique but a primary key, so the source_ref IS the key and a claim is a plain INSERT:
-- a second claim fails with 23505 and the caller reads the winner (an upsert would silently re-point the
-- ref at the newcomer, and both callers would think they had won).
CREATE TABLE IF NOT EXISTS pipeline_source_refs (
source_ref VARCHAR NOT NULL,
opportunity_id VARCHAR NOT NULL,
created_at TIMESTAMP,
PRIMARY KEY (source_ref)
);
-- ── 0.3.0: the CRM audit's wave 1 (2026-10-17) — additive ────────────────────────────────
-- The columns are at the END of pipeline_opportunities' CREATE above and added here for a bucket that predates
-- them (erp-migrate marks the ALTERs DISRUPTIVE: reads stall for minutes — migrate before rolling 0.3.0). The
-- claim table is new: a save CLAIMS version n+1 with a plain INSERT in the same transaction as its rows, and a
-- racer from the same version fails 23505 -> 409 "changed since you opened it".
ALTER TABLE pipeline_opportunities ADD COLUMN won_quote_ids VARCHAR;
ALTER TABLE pipeline_opportunities ADD COLUMN voided_quote_ids VARCHAR;
ALTER TABLE pipeline_opportunities ADD COLUMN version INTEGER;
CREATE TABLE IF NOT EXISTS pipeline_opportunities_versions (
opportunity_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
PRIMARY KEY (opportunity_id, version)
);