-- brick-quote. Every table is prefixed `quote_` (at tier 1 bricks share a bucket), has a natural
-- primary key (never SERIAL), and every child's key starts with quote_id, so the repository finds
-- stale rows under a quote at any depth. Money is NUMERIC(18,2); a percentage is NUMERIC(5,2).
-- Creation columns sit at the end, so a new column is an append (pattern: erp-platform BRICKS.md).
-- ── the Quote aggregate ──────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS quote_quotes (
quote_id VARCHAR NOT NULL,
bp_id BIGINT NOT NULL, -- the customer: a business partner (modules/shared.yaml).
-- NOT account_id, which is a GL account across modules
opportunity_id VARCHAR, -- the deal; owned by the pipeline brick
currency VARCHAR NOT NULL,
owner VARCHAR NOT NULL, -- the rep's email: row visibility reads it
reference VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
revision INTEGER, -- 0.2.0: the optimistic version; NULL (a row from before) = 0
PRIMARY KEY (quote_id)
);
CREATE TABLE IF NOT EXISTS quote_versions (
quote_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
status VARCHAR NOT NULL, -- DRAFT SENT ACCEPTED DECLINED SUPERSEDED (EXPIRED is derived)
valid_until DATE,
sent_at TIMESTAMP,
approved_by VARCHAR,
approved_pct NUMERIC(5,2), -- the discount the approval was given FOR
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (quote_id, version)
);
CREATE TABLE IF NOT EXISTS quote_lines (
quote_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
line_no INTEGER NOT NULL,
product_id VARCHAR NOT NULL,
description VARCHAR,
quantity INTEGER NOT NULL,
list_price NUMERIC(18,2) NOT NULL,
unit_price NUMERIC(18,2) NOT NULL,
currency VARCHAR NOT NULL,
price_book_id VARCHAR NOT NULL, -- where the list price came from …
priced_on DATE NOT NULL, -- … and as of which day
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (quote_id, version, line_no)
);
-- The line total spread across the months it is recognised in: the rows a revenue report reads.
-- Written by the same save as the line, so it can never disagree with it.
CREATE TABLE IF NOT EXISTS quote_line_schedule (
quote_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
line_no INTEGER NOT NULL,
revenue_month DATE NOT NULL, -- the first of the month
amount NUMERIC(18,2) NOT NULL,
PRIMARY KEY (quote_id, version, line_no, revenue_month)
);
-- ── history: appended, never part of the aggregate (a save would delete what it did not load) ──
CREATE TABLE IF NOT EXISTS quote_events (
quote_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 (quote_id, event_id)
);
-- ── configuration rows: changed by the deal desk, no deploy ─────────────────────────────────
CREATE TABLE IF NOT EXISTS quote_discount_policy (
policy_id VARCHAR NOT NULL, -- 'default'
auto_approve_pct NUMERIC(5,2) NOT NULL,
max_pct NUMERIC(5,2) NOT NULL,
updated_by VARCHAR,
updated_at TIMESTAMP,
PRIMARY KEY (policy_id)
);
-- The price book. Lives here until a second brick (invoicing, purchasing) needs to write prices;
-- then it moves to its own brick and quote reads it through a seam. A price is never edited in
-- place: a new valid_from row supersedes it, so a line priced last year re-prices identically.
CREATE TABLE IF NOT EXISTS quote_prices (
price_book_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
currency VARCHAR NOT NULL,
valid_from DATE NOT NULL,
list_price NUMERIC(18,2) NOT NULL,
description VARCHAR,
updated_by VARCHAR,
updated_at TIMESTAMP,
PRIMARY KEY (price_book_id, product_id, currency, valid_from)
);
-- ── 0.2.0: the CRM audit's wave 1 (2026-10-17) — additive ────────────────────────────────
-- C3: the optimistic version of the whole quote. "revision", because `version` is the offer the customer reads.
-- The column is at the END of quote_quotes' CREATE above and added here for a bucket that predates it (erp-migrate
-- marks the ALTER DISRUPTIVE: reads stall for minutes — migrate before rolling 0.2.0). A save CLAIMS revision n+1
-- with a plain INSERT into quote_quotes_versions in the same transaction as its rows; a racer from the same
-- revision fails 23505 -> 409 "changed since you opened it". Eight lines added at once no longer keep one.
ALTER TABLE quote_quotes ADD COLUMN revision INTEGER;
CREATE TABLE IF NOT EXISTS quote_quotes_versions (
quote_id VARCHAR NOT NULL,
revision INTEGER NOT NULL,
saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
PRIMARY KEY (quote_id, revision)
);
-- H4: one quote in flight per deal, under concurrency. The n-th quote opened on a deal claims (opportunity_id, n)
-- with a plain INSERT; two opens that both counted n-1 collide, and the loser answers the winner's quote.
CREATE TABLE IF NOT EXISTS quote_deal_quotes (
opportunity_id VARCHAR NOT NULL,
seq INTEGER NOT NULL,
quote_id VARCHAR NOT NULL,
created_at TIMESTAMP,
PRIMARY KEY (opportunity_id, seq)
);