CRM & Tasks

Synced from docs/generated/erd/ — do not hand-edit.
Regenerate with bun run docs:erd (needs staging reader), then this script runs automatically.
Labels: docs/db/erd-table-labels.yml · Runbook: docs/runbooks/erd-schema-docs.md

Description

Companies, prospects, unified tasks/feedback, groups, custom fields.
Catalog §2.5–2.6. Note mid-migration: legacy crm_* may coexist with tasks/feedback.

Tables

Pipeline entities

Companies, prospects, contacts

Name	Columns	Comment	Type
companies	20		BASE TABLE
public.contacts	17	Person identity (auth-linked via contact_auth); used across CRM and membership — Not: A member by itself — membership is members + org seat	BASE TABLE
industry_segments	8		BASE TABLE
prospects	22		BASE TABLE

Work management

Unified tasks and feedback

Name	Columns	Comment	Type
feedback	16	Inbound member feedback (#1556): praise / suggestion / complaint. A genuinely different lifecycle from tasks — stays its own table. contact_id is canonical; no member_id column.	BASE TABLE
interventions	10		BASE TABLE
member_activity_feed	9		BASE TABLE
recommended_interventions	16		BASE TABLE
tasks	21	Unified CRM activity log (#1556): every planned or completed activity. A completed task with its outcome filled in IS the logged interaction. type=channel, category=purpose, status=lifecycle.	BASE TABLE

Governance bodies

Groups (renamed from committees,

Name	Columns	Comment	Type
group_meetings	13		BASE TABLE
group_members	14		BASE TABLE
groups	15		BASE TABLE

Extensibility

Custom fields and opportunity intel

Name	Columns	Comment	Type
custom_field_definitions	15		BASE TABLE
custom_field_values	8		BASE TABLE
opportunity_clusters	12		BASE TABLE
opportunity_recommendations	17		BASE TABLE

-

Name	Columns	Comment	Type
organizations	40		BASE TABLE

Relations

erDiagram

"public.companies" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.companies" }o--o| "public.contacts" : "FOREIGN KEY (primary_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.contacts" }o--o| "public.organizations" : "FOREIGN KEY (primary_org_id) REFERENCES organizations(id) ON DELETE SET NULL"
"public.contacts" }o--o| "public.organizations" : "FOREIGN KEY (source_org_id) REFERENCES organizations(id) ON DELETE SET NULL"
"public.custom_field_definitions" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.custom_field_values" }o--|| "public.custom_field_definitions" : "FOREIGN KEY (field_definition_id) REFERENCES custom_field_definitions(id) ON DELETE CASCADE"
"public.custom_field_values" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.feedback" }o--o| "public.companies" : "FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE"
"public.feedback" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.feedback" }o--o| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE RESTRICT"
"public.group_meetings" }o--|| "public.groups" : "FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE"
"public.group_meetings" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.group_members" }o--|| "public.groups" : "FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE"
"public.groups" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.industry_segments" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.interventions" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.opportunity_clusters" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.organizations" }o--o| "public.organizations" : "FOREIGN KEY (parent_org_id) REFERENCES organizations(id) ON DELETE SET NULL"
"public.organizations" }o--o| "public.contacts" : "FOREIGN KEY (default_approval_reviewer_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.prospects" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.prospects" }o--o| "public.contacts" : "FOREIGN KEY (assigned_to_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.prospects" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.prospects" }o--o| "public.contacts" : "FOREIGN KEY (referred_by_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.recommended_interventions" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.tasks" }o--o| "public.companies" : "FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE"
"public.tasks" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.tasks" }o--o| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE RESTRICT"

"public.companies" {
  uuid id ""
  text org_id ""
  uuid primary_contact_id ""
}
"public.contacts" {
  uuid id ""
  text primary_org_id ""
  text source_org_id ""
}
"public.custom_field_definitions" {
  uuid id ""
  text org_id ""
}
"public.custom_field_values" {
  uuid field_definition_id ""
  text org_id ""
}
"public.feedback" {
  uuid company_id ""
  uuid contact_id ""
  text org_id ""
}
"public.group_meetings" {
  uuid group_id ""
  text org_id ""
}
"public.group_members" {
  uuid group_id ""
}
"public.groups" {
  uuid id ""
  text org_id ""
}
"public.industry_segments" {
  text org_id ""
}
"public.interventions" {
  text org_id ""
}
"public.member_activity_feed" {
}
"public.opportunity_clusters" {
  text org_id ""
}
"public.opportunity_recommendations" {
}
"public.organizations" {
  uuid default_approval_reviewer_contact_id "Org-level fallback reviewer for two-step send (#5698). Routing chain: the sender's own reviewer, then this, then all org admins. NULL means fall through to the admins, which is the pre-#5698 behaviour."
  text id ""
  text parent_org_id "#671 self-referencing hierarchy pointer. NULL = root org. Direct writes are blocked by trg_block_direct_parent_org_id_writes for every role except service_role/postgres/supabase_admin — the only write path for authenticated/interactive callers is public.reparent_organization(). Cycle-prevented by trg_prevent_org_hierarchy_cycle. #quot;Is association#quot; is derived (EXISTS a child), never stored."
}
"public.prospects" {
  uuid assigned_to_contact_id ""
  uuid contact_id ""
  text org_id ""
  uuid referred_by_contact_id "Contact who referred this prospect (member or prospect subject). #4232 reciprocal CRM link."
}
"public.recommended_interventions" {
  text org_id ""
}
"public.tasks" {
  uuid company_id ""
  uuid contact_id ""
  text org_id ""
}

---

Generated by tbls