The companies table
crm_companies is the account record — the anchor of the CRM. One wide org-scoped row per company carries firmographics, funding, lifecycle, ownership, lead score and provenance. 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_companies is the anchor: most other tables point back here. 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 that hold the model together.
crm_companies is the hub. Hover or click a table to trace what links to it.
Data flow — birth of a row
interactiveHow a company lands and is read back, as a closed loop. Three origins converge on one service, which dedupes, enriches, writes under RLS, rehosts the logo, 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 seven labelled blocks - identity, firmographics, funding, lifecycle, lead score, social 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 and soft-delete columns sit on every row.
Identity
7Who the company is and the tenant + ownership scope every row carries.
- primary key
Primary key.
- text
Display name.
- unique
Unique per (org, website) when not null.
- text
Normalized to eTLD+1 — the primary dedup key.
- text
Points at the R2-rehosted copy, not the source.
Firmographics
7Mostly enrichment-filled descriptors of the business.
- text
Sector classification.
- text
e.g. SaaS, marketplace.
- number
Headcount when known.
- text
Bucketed size, e.g. 51-200.
- number
Year founded.
- text
Freeform + structured HQ.
- text
ISO country.
Funding
7Capital history. Numeric columns drive filters; display columns render the chip.
- jsonb
Full round history. GIN-indexed.
- number
Sortable / filterable total.
- text
Pre-formatted, e.g. $42M.
- text
e.g. Series B.
- timestamp
Most recent raise.
- mixed
Exit state; acquisition_info is jsonb.
- mixed
Revenue estimate + bucket.
Lifecycle
7Where the account sits in the funnel and what happens next.
- enum
identified → prospect → qualified_lead → opportunity → customer → former_customer · partner · vendor · disqualified.
- text
Why it changed.
- timestamp
When it last moved.
- timestamp
Bumped by activities + comms triggers.
- timestamp
Drives the follow-up queue.
- array
GIN-indexed freeform labels.
Lead score
5The 0-10 lead score and its provenance (ENG-619).
- number
Higher = hotter.
- text
Explanation behind the score.
- text
manual or sonar.
- timestamp
Last scored.
- uuid
Who/what scored it.
Social & people
5Public profiles and the people arrays surfaced from enrichment.
- text
Public profiles.
- text
Generic company contact.
- array
Named individuals from enrichment.
- array
GIN-indexed competitor set.
- array
Provenance URLs. GIN-indexed.
Provenance
7Who/what created and last touched the row, plus soft delete (ENG-658).
- uuid
Last workflow to touch it.
- text
user · workflow · integration.
- uuid
Human authorship.
- mixed
Review gate for discovered rows.
- timestamp
Soft delete — queries filter NULL.
- mixed
Audit + optimistic-concurrency counter.
One account, materialized. Northwind Robotics is discovered by Company Sonar, deduped on its domain, enriched, then written under RLS. Follow the keys down the cards: the crm_companies row anchors a linked crm_people contact (its primary_contact_id) and a company_signals join that attaches a funding signal. The same row every list view, deal and activity reads back.
crm_companiesthe account row - one per company per org, anchor of the CRMcrm_peoplethe primary contact - inherits the account owner, links back via company_idcompany_signalsa join row - attaches the funding signal that lifted the lead scoreIndexes, 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.
Unique (organization_id, website) where website is not null. Composite (org, domain), (org, name), (org, lifecycle_stage), (org, owner_id) back the hot filters.
Hot paths get partials: the unassigned-open pool (owner_id NULL + early stages), opportunities, customers, and rows with recent activity.
Array + JSONB search on tags, sources, competitors and funding_rounds.
handle_updated_at() maintains updated_at. Activity + communication inserts bump last_interaction_at via their own triggers.
deleted_at marks a row removed; list queries filter deleted_at IS NULL. The UI can toggle deleted rows back into view.
crm_companies 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 account list one user sees can never include another tenant's rows.