-- brick-lead. Tables prefixed lead_; natural keys (never SERIAL); every child's key starts with its root's
-- key. bp_id is BIGINT (modules/shared.yaml). Empty text is '' rather than NULL, so a filter on it is plain.
-- ── the lead: a person (keyed by email) or a company (keyed by domain) ──────────────────────
CREATE TABLE IF NOT EXISTS lead_leads (
lead_id VARCHAR NOT NULL, -- 'L-' + blake2b of the normalised email or domain (derived)
kind VARCHAR NOT NULL, -- person | company
name VARCHAR NOT NULL,
email VARCHAR NOT NULL, -- '' for a company lead
company VARCHAR NOT NULL,
domain VARCHAR NOT NULL, -- the company's web domain; '' if unknown or free-mail
title VARCHAR NOT NULL,
country VARCHAR NOT NULL, -- ISO 3166 alpha-2
industry VARCHAR NOT NULL,
size_band VARCHAR NOT NULL,
source VARCHAR NOT NULL,
owner VARCHAR NOT NULL, -- the rep's email: row visibility reads it
status VARCHAR NOT NULL, -- new | working | qualified | disqualified | converted
disqualify_reason VARCHAR NOT NULL,
bp_id BIGINT, -- CRM-03: what conversion produced, kept step by step
contact_id VARCHAR,
opportunity_id VARCHAR,
converted_at TIMESTAMP,
converted_by VARCHAR NOT NULL,
created_at TIMESTAMP,
updated_at TIMESTAMP,
version INTEGER, -- 0.2.0: the optimistic version; NULL (a row from before) = 0
PRIMARY KEY (lead_id)
);
-- CRM-14: the company's fit profile (domain/lead.py profile()), derived from the lead's attributes and
-- rewritten with them. Its own table so loading a lead never drags a vector through code that has no use
-- for one. Ranked with pgvector's cosine distance, `profile <=> $target`.
CREATE TABLE IF NOT EXISTS lead_profiles (
lead_id VARCHAR NOT NULL,
profile VECTOR(31) NOT NULL,
updated_at TIMESTAMP,
PRIMARY KEY (lead_id)
);
CREATE TABLE IF NOT EXISTS lead_events (
lead_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 (lead_id, event_id)
);
-- ── CRM-10: engagement, the score derived from it, and each scoring run ──────────────────
CREATE TABLE IF NOT EXISTS lead_signals (
lead_id VARCHAR NOT NULL,
signal_id VARCHAR NOT NULL, -- derived from kind + time: a re-delivered event is one row
kind VARCHAR NOT NULL, -- email_opened_pricing, webinar_attended, ...
event_at TIMESTAMP NOT NULL,
source VARCHAR NOT NULL, -- who reported it (a campaign id, 'website')
created_at TIMESTAMP,
PRIMARY KEY (lead_id, signal_id)
);
CREATE TABLE IF NOT EXISTS lead_scores (
lead_id VARCHAR NOT NULL,
score INTEGER NOT NULL, -- 0–100, derived (domain/lead.py score())
fit_points INTEGER NOT NULL,
engagement_points INTEGER NOT NULL,
reasons VARCHAR NOT NULL, -- '; '-joined: why it is this number
run_id VARCHAR NOT NULL, -- the run that computed THIS value (an unchanged score keeps it)
scored_at TIMESTAMP,
PRIMARY KEY (lead_id)
);
CREATE TABLE IF NOT EXISTS lead_score_runs (
run_id VARCHAR NOT NULL,
run_at TIMESTAMP NOT NULL,
as_of TIMESTAMP NOT NULL,
run_by VARCHAR NOT NULL,
leads_scored INTEGER NOT NULL,
scores_changed INTEGER NOT NULL,
qualified INTEGER NOT NULL,
PRIMARY KEY (run_id)
);
-- ── configuration rows: how this business qualifies a lead (config_rows: scoring_policy) ──
CREATE TABLE IF NOT EXISTS lead_scoring_policy (
policy_id VARCHAR NOT NULL, -- 'default'
qualify_threshold INTEGER NOT NULL,
signal_window_days INTEGER NOT NULL,
updated_by VARCHAR,
updated_at TIMESTAMP,
PRIMARY KEY (policy_id)
);
CREATE TABLE IF NOT EXISTS lead_scoring_rules (
policy_id VARCHAR NOT NULL,
rule_kind VARCHAR NOT NULL, -- fit | signal
attribute VARCHAR NOT NULL, -- fit: industry | size_band | country | region | source; signal: its name
value VARCHAR NOT NULL, -- fit: the attribute's value; signal: ''
points INTEGER NOT NULL,
max_count INTEGER NOT NULL,
PRIMARY KEY (policy_id, rule_kind, attribute, value)
);
-- ── 0.2.0: the CRM audit's wave 1 (2026-10-17) — additive ────────────────────────────────
-- C3: the optimistic version. The column is at the END of lead_leads' 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 version n+1 with a plain INSERT into lead_leads_versions in the same transaction as its rows (an import
-- claims each lead's in bulk); a racer from the same version fails 23505 -> 409 "changed since you opened it".
ALTER TABLE lead_leads ADD COLUMN version INTEGER;
CREATE TABLE IF NOT EXISTS lead_leads_versions (
lead_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
PRIMARY KEY (lead_id, version)
);