The people table
crm_people is the contact record - one wide row per person per org, optionally linked to a company and deduped on email + LinkedIn. It carries identity, professional history, contact details, lifecycle, enrichment and provenance, and inherits ownership from its company rather than holding an owner of its own. This page traces it top-down: how it links to the rest of the CRM, how a row is born and read, then its full schema and the rules that govern it.
System design
interactive For a storage table the system design is the data model itself — the row's shape and how it links to the rest of the CRM. crm_people links up to its company (ownership inherited, no owner of its own) and out to its activity, comms, signals and lists. Hover or click a table to trace its link and see its key columns; the schema section below expands the columns, the rules section the constraints.
crm_people links up to its company and out to its activity, comms, signals and lists.
Data flow — birth of a row
interactiveHow a contact lands and is read back, as a closed loop. Three origins converge on one service, which dedupes, matches a company, writes under RLS, rehosts the avatar, then serves the list — and the read closes back to the start. Click a stage to follow it.
Schema design
interactiveThe row is wide, so the columns are grouped into six labelled blocks - identity, professional, contact, lifecycle, enrichment and provenance - and every block is laid out at once so the whole row reads in one pass. Colour marks each column's kind; click a column to expand its category and any table it references. Everything is org-scoped; provenance columns sit on every row.
Identity
7Who the contact is, the tenant scope every row carries, and the optional company link.
- primary key
Primary key.
- text
Display name.
- text
Points at the R2-rehosted copy, not the source.
- unique
Unique per (org, email) when not null. A dedup key.
- unique
Unique per (org, linkedin_url) when not null. A dedup key. CHECK: email IS NOT NULL OR linkedin_url IS NOT NULL.
Professional
6Role, history and skills - mostly enrichment-filled descriptors of the person.
- text
Job title.
- text
Fallback company label when company_id is null.
- text
Freeform bio.
- array
GIN-indexed skill set.
- array
Spoken languages.
- jsonb
Full role history. GIN-indexed.
Contact
4Phone and location. Phone columns were normalized in ENG-662.
- text
Office number.
- text
Normalized from old phone_number / phone_source (ENG-662).
- text
Freeform city / region.
- text
Country.
Lifecycle
5Where the contact sits in the funnel and the labels applied to them.
- enum
identified → prospect → qualified_lead → opportunity → customer_contact → former_customer_contact · partner_contact · vendor_contact · disqualified.
- text
Why it changed.
- timestamp
When it last moved.
- array
GIN-indexed freeform labels.
- boolean
Pinned by the owning user.
Enrichment
4Provenance, relevance and email deliverability state.
- array
Provenance URLs. GIN-indexed.
- number
Discovery relevance (ENG-505).
- text
active · unsubscribed · bounced (ENG-330).
- timestamp
Last deliverability change.
Provenance
6Who/what created and last touched the row.
- uuid
Set when a workflow created the row.
- uuid
Last workflow to touch it.
- text
user · workflow · integration.
- uuid
Human authorship.
- timestamp
Audit timestamps.
- number
Optimistic-concurrency counter.
One contact, materialized. Maya Chen is discovered by People Sonar, deduped on her email and linkedin_url, then matched to her account. Follow the keys down the cards: the crm_people row links up to its crm_companies account via company_id (ownership inherited, no owner of its own) and out to a person_signals join that attaches a job-change signal.
crm_peoplethe contact row - deduped on email + linkedin, owner inherited from the companycrm_companiesthe linked account - supplies the owner the contact inheritsperson_signalsa join row - attaches the job-change signal that surfaced the leadIndexes, RLS & triggers
modelThe database is storage only - no business logic. What it does enforce: tenant isolation, dedup uniqueness, the indexes that keep filters fast, and the triggers that keep derived state honest.
Row-level security on every read: is_member_of_organization(organization_id). Superadmins bypass. The OrgScopedClient applies the same filter at the app layer, so a missing filter can never leak another org.
A person has no owner column. Ownership is inherited from the linked company - distinct from companies and deals, which each carry their own owner.
Unique (organization_id, email) and unique (organization_id, linkedin_url), both partial where the column is not null. A CHECK requires at least one of the two.
Array + JSONB search on skills, sources and experience_history.
handle_updated_at() maintains updated_at. Activity + communication inserts bump the person’s last-activity state via their own triggers.
crm_people holds no rules of its own - tables, indexes, RLS and triggers only. Every read is gated by the same org-membership check at two layers (the scoped client and Postgres RLS), so the contact list one user sees can never include another tenant's rows.