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.
`created_at`	`timestamptz`

Indexes: organization_leads_org_created_idx (org, created_at); organization_leads_org_assignee_idx (org, assignee).

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
`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.

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).