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, committees, 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`	20		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

Committees

Name	Columns	Type
`committee_meetings`	12	BASE TABLE
`committee_members`	14	BASE TABLE
`committees`	13	BASE TABLE

Extensibility

Custom fields and opportunity intel

Name	Columns	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`	35		BASE TABLE

Relations

erDiagram

"public.committee_meetings" }o--|| "public.committees" : "FOREIGN KEY (committee_id) REFERENCES committees(id) ON DELETE CASCADE"
"public.committee_meetings" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.committee_members" }o--|| "public.committees" : "FOREIGN KEY (committee_id) REFERENCES committees(id) ON DELETE CASCADE"
"public.committees" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"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.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.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.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.committee_meetings" {
  uuid committee_id ""
  text org_id ""
}
"public.committee_members" {
  uuid committee_id ""
}
"public.committees" {
  uuid id ""
  text org_id ""
}
"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.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" {
  text id ""
}
"public.prospects" {
  uuid assigned_to_contact_id ""
  uuid contact_id ""
  text org_id ""
}
"public.recommended_interventions" {
  text org_id ""
}
"public.tasks" {
  uuid company_id ""
  uuid contact_id ""
  text org_id ""
}

Generated by tbls