Leads & CRM — Data Model

Tables in libs/db/src/lib/schema/leads.ts.

`leads` — canonical lead

Column	Type	Notes
`id`	`uuid` PK
`name`	`varchar(255)`	Nullable.
`email`	`varchar(255)`	Nullable. Not globally unique — same email can be a lead in multiple orgs. Dedup is per-org (see `organization_leads`).
`phone`	`varchar(50)`	Nullable.
`locale`	`varchar(5)`	Optional.
`source`	`lead_source` enum NOT NULL default `website`	`minisite
`status`	`lead_status` enum NOT NULL default `new`	`new
`note`	`text`	Free-text.
`status_changed_at`	`timestamptz`	Bumped on every status change.
`created_at` / `updated_at`	`timestamptz`

Indexes: leads_status_created_idx on (status, created_at).

No deleted_at — leads are not soft-deletable.

`organization_leads` — per-org link

Column	Type	Notes
`id`	`uuid` PK
`lead_id`	`uuid` NOT NULL	FK → `leads.id`.
`organization_id`	`uuid` NOT NULL	FK → `organizations.id`. Direct org boundary.
`assigned_to_user_id`	`uuid`	FK → `users.id`. Staff member responsible for follow-up.
`converted_membership_id`	`uuid`	FK → `memberships.id`. Set once on convert; sentinel for “already converted”.
`trial_date`	`timestamptz`	Optional scheduled trial appointment.
`campaign_id`	`uuid`	FK → `lead_campaigns.id`. The hook bucket for ingested leads (the ad/form/campaign). Null for minisite/manual leads.
`pipeline_id`	`uuid`	FK → `lead_pipelines.id`. The lead’s current pipeline. Null until first placed; resolves to the org’s default pipeline.
`stage_id`	`uuid`	FK → `lead_pipeline_stages.id`. The lead’s current stage; its `category` is mirrored onto `leads.status`.
`created_at`	`timestamptz`

Indexes: organization_leads_org_created_idx (org, created_at); organization_leads_org_assignee_idx (org, assignee); organization_leads_org_campaign_idx (org, campaign); organization_leads_org_stage_idx (org, stage).

No updated_at and no deleted_at.

`platform_leads` — waitlist tag

Column	Type	Notes
`id`	`uuid` PK
`lead_id`	`uuid` NOT NULL	FK → `leads.id`.
`created_at`	`timestamptz`

Pure tagging table; no per-row business state.

`lead_status_events` — audit history

Column	Type	Notes
`id`	`uuid` PK
`lead_id`	`uuid` NOT NULL	FK → `leads.id` ON DELETE CASCADE.
`from_status`	`lead_status` nullable	Null for the initial creation event.
`to_status`	`lead_status` NOT NULL
`from_stage_id`	`uuid`	FK → `lead_pipeline_stages.id`. The stage moved out of (null for the first placement).
`to_stage_id`	`uuid`	FK → `lead_pipeline_stages.id`. The stage moved into.
`changed_by_user_id`	`uuid`	FK → `users.id`. Null for public-minisite lead creation.
`changed_at`	`timestamptz` default `now()`

Indexes: lead_status_events_lead_changed_idx on (lead_id, changed_at).

Append-only — no update or delete paths.

`lead_activity_events` — unified timeline ledger (FIT-227)

Complements lead_status_events (semantic status flips on the primary placement) with everything else that happens to a contact: secondary pipeline add/remove/stage moves, same-category primary stage moves, course purchases, and per-recipient bulk-email sends. Append-only; written best-effort (history must never fail the operation it records).

Column	Type	Notes
`id`	`uuid` PK
`organization_lead_id`	`uuid` NOT NULL	FK → `organization_leads.id` ON DELETE CASCADE — GDPR erase cascades the history.
`type`	`lead_activity_type` enum NOT NULL	`pipeline_added \| pipeline_removed \| stage_moved \| purchase \| email_sent`.
`pipeline_id`	`uuid`	FK → `lead_pipelines.id` ON DELETE SET NULL.
`from_stage_id` / `to_stage_id`	`uuid`	FK → `lead_pipeline_stages.id` ON DELETE SET NULL.
`actor_user_id`	`uuid`	FK → `users.id`. Null for system writes (purchase webhook, email processor).
`payload`	`jsonb`	Display-name snapshots taken at write time (`pipelineName`, `fromStageName`, `toStageName`, `programName`, `subject`) so the timeline renders without joins and survives renames/deletes.
`created_at`	`timestamptz` default `now()`

Indexes: lead_activity_events_lead_created_idx (organization_lead_id, created_at).

The lead detail read (GET …/leads/:id) merges these with status events and the contact’s conversation_messages into a single timeline array (newest first, explicit kind per entry) — see buildTimeline in organization-leads.service.ts.

Lead ingestion tables

These power the lead-ingestion pipeline (Meta Lead Ads, WhatsApp, …). integration_connections lives in libs/db/src/lib/schema/integrations.ts; lead_campaigns + lead_attribution are in leads.ts.

`integration_connections` — per-org external connection

Column	Type	Notes
`id`	`uuid` PK
`organization_id`	`uuid` NOT NULL	FK → `organizations.id`.
`provider`	`integration_provider` enum NOT NULL	`meta_lead_ads
`status`	`integration_connection_status` enum NOT NULL default `pending`	`pending
`external_account_id`	`varchar(255)`	`page_id` (Meta) \| `phone_number_id` (WhatsApp). Inbound webhooks route on this. Nullable while `pending` (e.g. OAuth done, page not yet picked).
`encrypted_credentials`	`text`	Access token, encrypted at rest (same scheme as `payment_provider_configs`). Nullable for a pre-token `pending` row.
`config`	`jsonb`	Channel extras (e.g. WhatsApp `{wabaId, displayPhoneNumber, verifiedName}`).
`created_at` / `updated_at`	`timestamptz`

Indexes: integration_connections_org_idx (org); unique integration_connections_provider_account_idx (provider, external_account_id) — one provider account maps to exactly one org connection (and is the webhook-routing lookup).

`lead_campaigns` — the hook bucket

The named group a lead is attributed to (an ad / form / campaign), shown in the UI.

Column	Type	Notes
`id`	`uuid` PK
`organization_id`	`uuid` NOT NULL	FK → `organizations.id`. Org-scoped.
`name`	`varchar(255)` NOT NULL	Display name (derived from the form/ad headline on auto-create).
`channel`	`lead_source` enum NOT NULL	`facebook
`color`	`varchar(32)`	Optional UI color.
`external_ref`	`varchar(255)`	The provider key the bucket is resolved on (Meta form/ad id; CTWA `source_id`).
`is_archived`	`boolean` NOT NULL default `false`	Hide without deleting.
`created_at` / `updated_at`	`timestamptz`

Indexes: unique lead_campaigns_org_channel_ref_idx (organization_id, channel, external_ref) — the upsert key, so the same ad resolves to one bucket per org.

`lead_attribution` — 1:1 raw attribution

One row per ingested lead with the full provenance + raw payload.

Column	Type	Notes
`id`	`uuid` PK
`lead_id`	`uuid` NOT NULL UNIQUE	FK → `leads.id` ON DELETE CASCADE. 1:1 with the lead.
`external_lead_id`	`varchar(255)`	`leadgen_id` (Meta) \| `ctwa_clid` (WhatsApp). Idempotency key for the processor.
`form_id` / `form_name`	`varchar(255)`	Meta lead form.
`ad_id` / `ad_name` / `adset_name`	`varchar(255)`	Meta ad hierarchy.
`meta_campaign_id` / `meta_campaign_name`	`varchar(255)`	Meta campaign.
`ref_payload`	`text`	Raw `ref`/tracking string.
`source_url` / `headline` / `body`	`text`	CTWA creative (the ad the WhatsApp lead tapped).
`consent`	`jsonb`	Captured consent, when present.
`raw`	`jsonb`	Full inbound payload, for forensics.
`created_at`	`timestamptz`

Indexes: lead_attribution_external_lead_id_idx (external_lead_id) — the processor’s idempotency lookup.

Configurable pipelines

Tables in libs/db/src/lib/schema/lead-pipelines.ts. An org-scoped, user-defined sales funnel that replaces the fixed five-state board with as many named, ordered stages as the studio wants. Each stage maps to a semantic category (the legacy lead_status enum) — this is the keystone: leads.status is kept in sync with the lead’s current stage category, so conversion, analytics and the lead-funnel automations keep working unchanged. Every org gets one default pipeline seeded on first use (stages New / Contacted / Trial booked / Converted / Lost). See ADR-0012.

`lead_pipelines`

Column	Type	Notes
`id`	`uuid` PK
`organization_id`	`uuid` NOT NULL	FK → `organizations.id`. Org boundary.
`name`	`varchar(255)` NOT NULL	Display name.
`position`	`integer` NOT NULL default `0`	Ordering across an org’s pipelines.
`is_default`	`boolean` NOT NULL default `false`	Where new leads land when none is specified.
`is_archived`	`boolean` NOT NULL default `false`	Hide without deleting.
`created_at` / `updated_at`	`timestamptz`

Indexes: lead_pipelines_org_position_idx (org, position); unique partial lead_pipelines_org_default_idx (org) WHERE is_default — at most one default pipeline per org. (The predicate is a bare boolean sql column, not eq(col,true): drizzle-kit serializes a literal-valued partial-index predicate as an invalid = $1 bind param in DDL.)

`lead_pipeline_stages`

Column	Type	Notes
`id`	`uuid` PK
`pipeline_id`	`uuid` NOT NULL	FK → `lead_pipelines.id` ON DELETE CASCADE.
`name`	`varchar(255)` NOT NULL	Stage label (free-text org data). The web board shows a localized label for un-renamed defaults.
`position`	`integer` NOT NULL default `0`	Column order.
`category`	`lead_status` enum NOT NULL	Semantic mapping onto the lifecycle. Drives conversion (`converted`), analytics, and which automations fire.
`color`	`varchar(32)`	Optional UI color.
`show_in_reports`	`boolean` NOT NULL default `true`	Per-stage “count toward funnel reports” toggle (mirrors GHL).
`is_archived`	`boolean` NOT NULL default `false`
`created_at` / `updated_at`	`timestamptz`

Indexes: lead_pipeline_stages_pipeline_position_idx (pipeline, position).

Unified inbox

Tables in libs/db/src/lib/schema/conversations.ts. A channel-agnostic thread between the studio and a contact (a lead or a member), aggregating messages across channels (email today; manual notes always; WhatsApp/SMS later) into one inbox via a ChannelAdapter registry. See ADR-0012.

`conversations`

Column	Type	Notes
`id`	`uuid` PK
`organization_id`	`uuid` NOT NULL	FK → `organizations.id`. Org boundary.
`lead_id`	`uuid`	FK → `leads.id`. The contact, when it’s a lead.
`contact_user_id`	`uuid`	FK → `users.id`. The contact, when it’s a member. Exactly one of lead/user is set.
`channel`	`conversation_channel` enum NOT NULL	`manual
`status`	`conversation_status` enum NOT NULL default `open`	`open
`subject`	`text`	Thread subject from the first message; reused with a `Re:` prefix on replies (added in migration `0070`).
`assigned_to_user_id`	`uuid`	FK → `users.id`. Must be an org member (enforced in `assign`).
`external_contact_id`	`varchar(255)`	Provider-side contact key (email address, WA `wa_id`, …). NULL for manual.
`last_message_at` / `last_message_preview` / `last_direction`	denormalized	Inbox-list fields, avoiding a per-row join.
`last_inbound_at`	`timestamptz`	Drives WhatsApp’s 24h window later.
`unread_count`	`integer` NOT NULL default `0`	Staff-side; `+1` on inbound, cleared on read.
`created_at` / `updated_at`	`timestamptz`

Indexes: conversations_org_status_last_msg_idx; conversations_org_assignee_idx; conversations_lead_idx; conversations_contact_user_idx; unique conversations_org_channel_contact_idx (org, channel, external_contact_id) — one thread per contact key per channel per org (idempotent thread resolution).

`conversation_messages`

Column	Type	Notes
`id`	`uuid` PK
`conversation_id`	`uuid` NOT NULL	FK → `conversations.id` ON DELETE CASCADE.
`organization_id`	`uuid` NOT NULL	Denormalized for direct scoping.
`direction`	`message_direction` enum NOT NULL	`inbound
`channel`	`conversation_channel` enum NOT NULL
`body`	`text`	Message text.
`sender_user_id`	`uuid`	FK → `users.id`. Staff sender of an outbound; NULL for inbound.
`external_message_id`	`varchar(255)`	Provider id (email `Message-Id`, WA `wamid`). Used for threading + inbound idempotency.
`delivery_status`	`message_delivery_status` enum NOT NULL default `sent`	`queued
`error_message`	`text`	Set when a send fails (surfaced from Resend).
`sent_at` / `read_at`	`timestamptz`
`metadata`	`jsonb`	Channel-specific payload.
`created_at`	`timestamptz`

Indexes: conversation_messages_conversation_created_idx; unique partial conversation_messages_channel_external_id_idx (channel, external_message_id) WHERE external_message_id IS NOT NULL — delivery-receipt match + inbound dedup. Writes use onConflictDoNothing so concurrent deliveries can’t 500.

Relations

leads.platformLead — one-to-one optional (platform_leads.lead_id).
leads.organizationLead — one-to-one optional (organization_leads.lead_id).
leads.statusEvents — many (lead_status_events.lead_id).
organization_leads.organization — one.
organization_leads.assignedTo — optional one (user).
organization_leads.convertedMembership — optional one (membership).

The schema models leads.platformLead and leads.organizationLead as 1:1 — a single leads row is conceptually either a platform lead or an org lead, not both. (There is no UNIQUE constraint enforcing this at the DB layer — TODO: verify whether the service enforces.)

Lifecycle

New lead (org)

INSERT leads (status='new').
INSERT organization_leads referencing it.
INSERT lead_status_events (from=null, to=new).
INSERT tasks of type contact_lead (best-effort).

Status update

UPDATE leads.status + status_changed_at.
INSERT lead_status_events (from=prev, to=new).

Convert

Find-or-INSERT users shell by email.
INSERT memberships (role='member'|input.role, status='active', source_lead_id=leadId).
UPDATE organization_leads.converted_membership_id = newMembership.id.
UPDATE leads.status='converted' + event row.
Emit MEMBERSHIP_ACTIVATED.

Multi-org isolation pattern

organization_leads.organization_id is the direct boundary on the staff CRM side.
leads itself is shared across orgs (because leads.id can be referenced by 0 or 1 platform_lead and 0+ organization_leads). In practice the dedup check is at the (org, email/phone) level, so an email can have multiple leads rows — one per org — each with its own organization_leads link.
Every read joins through organization_leads.organization_id. The leads row is never accessed without the join.

Soft-delete vs hard-delete

None for any leads table. There’s no deletion path exposed; lost leads use status='lost' rather than deletion.
lead_status_events is the only table with a CASCADE on parent delete (ON DELETE CASCADE if a leads row is hard-deleted — which doesn’t happen in the service).

Leads & CRM — Data Model

leads — canonical lead

organization_leads — per-org link

platform_leads — waitlist tag

lead_status_events — audit history

lead_activity_events — unified timeline ledger (FIT-227)