Custom CRM Data Model: Tables, Fields and Audit Trails

A good CRM data model has a small set of core objects (contacts, companies, deals, pipelines and activities) connected by explicit relationship tables, plus a deliberate plan for custom fields, change history and permissions. The decisions that hurt most later are storing custom fields the wrong way, tying each contact to only one company and skipping an audit trail. In PostgreSQL, a practical default is real columns for core fields, a JSONB column for user-defined custom fields, triggers for the audit log and row-level security for record access. This guide walks through each piece with illustrative SQL.

Key Takeaways

• Model CRM data the way established platforms do: objects, records, properties, associations, pipelines with ordered stages and activities.
• Use link tables for many-to-many relationships such as contacts to companies and contacts to deals.
• PostgreSQL caps tables at 1,600 columns, and dropped columns still count, so adding a column per custom field doesn't scale.
• JSONB with a GIN index is the practical default for custom fields; promote fields you report on heavily to real columns.
• Keep activities (what happened with a customer) separate from the audit trail (who changed which data).

Start With How Established CRMs Model Data

You don't need to invent CRM concepts. HubSpot's developer documentation describes its CRM as objects (contacts, companies, deals, tickets and custom objects), records (individual entries), properties (fields), associations between records (which can carry labels), pipelines with ordered stages, and activities such as calls, emails, meetings, notes, tasks and postal mail (HubSpot Developers). Salesforce follows the same broad pattern of standard objects, custom objects, fields and relationships.

Borrowing this vocabulary has two benefits. Your team already understands it, and data maps cleanly if you're migrating off HubSpot or GoHighLevel. The value of a custom build comes from modeling your specific business on top of these basics, not from reinventing them.

Core Objects and Relationships

Here's the core of a typical custom CRM, shown as a table instead of a diagram. Each row is a database table.

Table	What it holds	Key columns	Relates to
tenants	Each organization using the CRM (if multi-tenant)	id, name, plan	Everything below via tenant_id
users	People who log in	id, tenant_id, email, role	Owns contacts, deals and activities
companies	Organizations you sell to	id, tenant_id, name, domain, owner_id	Contacts (via contact_companies), deals
contacts	Individual people	id, tenant_id, name, email, phone, owner_id	Companies, deals, activities
contact_companies	Link between contacts and companies	tenant_id, contact_id, company_id, role, is_primary	Many-to-many
pipelines	A sales or service process	id, tenant_id, name	Stages, deals
pipeline_stages	Ordered steps in a pipeline	id, pipeline_id, name, position, probability	Deals
deals	Opportunities	id, tenant_id, company_id, stage_id, amount, close_date, owner_id	Contacts (via deal_contacts), activities
deal_contacts	Who is involved in each deal	tenant_id, deal_id, contact_id, role	Many-to-many
deal_stage_history	Every stage change	deal_id, from_stage_id, to_stage_id, changed_at	Reporting on pipeline speed
activities	Calls, emails, meetings, notes, tasks	id, tenant_id, type, occurred_at, user_id	Records via activity_links
custom_field_definitions	Fields each tenant adds	tenant_id, object_type, key, data_type	Contacts, companies, deals
audit_log	Every change to important data	table_name, row_id, operation, old and new values	All audited tables

The tenants table only matters if several organizations share the system. If you sell the CRM as a product, its plan column will be driven by your billing provider; our Stripe subscription billing guide explains how webhooks keep it in sync.

An illustrative sketch of the contact and company relationship:

-- Illustrative only
create table contacts (
  id         uuid primary key default gen_random_uuid(),
  tenant_id  uuid not null references tenants(id),
  first_name text,
  last_name  text,
  email      text,
  owner_id   uuid references users(id),
  custom     jsonb not null default '{}',
  created_at timestamptz not null default now(),
  unique (tenant_id, email)
);

create table contact_companies (
  tenant_id  uuid not null references tenants(id),
  contact_id uuid not null references contacts(id),
  company_id uuid not null references companies(id),
  role       text,                 -- e.g. 'Decision maker', 'Billing'
  is_primary boolean not null default false,
  primary key (contact_id, company_id)
);

Consider composite foreign keys on (tenant_id, id), backed by a unique constraint on those columns in the parent table, so a link row can't point at a record in another tenant; foreign-key checks bypass row-level security, so the database won't catch a cross-tenant link any other way.

Why Contacts Need a Link Table to Companies

The tempting shortcut is a single company_id column on each contact. It breaks as soon as you meet a consultant who works with three clients, a board member, or someone who changes jobs and stays a good lead. A link table handles all of these, and the role column gives you something like HubSpot's association labels.

Pipelines and Stages Are Data, Not Code

Store pipelines and their ordered stages as rows, not as hard-coded values. That lets sales managers add a stage without a deployment, and it lets you run several pipelines (new business, renewals, service) side by side. Record every stage change in a history table. It's the simplest reliable way to answer "how long do deals sit in proposal?" later.

Custom Fields: Columns vs JSONB vs EAV

Every CRM eventually needs fields you didn't plan for: industry, lead source detail, roof type, contract renewal date. There are three common ways to store them in PostgreSQL.

Approach	How it works	Strengths	Weaknesses	Best for
Real columns	Add a column to the table per field	Fast, typed, easy to report on and index	Schema change per field; PostgreSQL caps tables at 1,600 columns; poor fit for per-tenant fields	Core fields everyone uses
JSONB column	One custom column holding key-value pairs	Flexible, indexable, no schema change per field	Types enforced in the app; reporting needs indexes or promotion	User-defined custom fields
EAV (entity-attribute-value)	A separate row per field value	Very flexible, no schema changes	Complex queries, weak typing, harder reporting	Rarely the right choice for new builds

Real Columns

Columns are the best choice for fields you filter, sort and report on constantly. PostgreSQL makes adding them cheap in many cases: ADD COLUMN with no default, or with a non-volatile default, needs no table rewrite and is "very fast even on large tables." Volatile defaults, stored generated columns and identity columns do trigger a rewrite (PostgreSQL ALTER TABLE).

The limits show up with user-defined fields. A table can have at most 1,600 columns (fewer if rows don't fit an 8 KB page), and dropped columns still count toward that limit (PostgreSQL limits). For multitenant systems, Microsoft warns against "column-level tenant customization" and recommends a dedicated table for tenant custom data instead (Microsoft Learn).

JSONB

PostgreSQL recommends jsonb over json for most applications. It's stored in a decomposed binary format that is "significantly faster" to process and can be indexed. It doesn't preserve whitespace, key order or duplicate keys (PostgreSQL JSON types), which doesn't matter for custom fields.

Indexing choices matter. The default GIN operator class, jsonb_ops, supports the ?, ?|, ?&, @>, @? and @@ operators. The jsonb_path_ops class supports only @>, @? and @@, but is "usually much smaller and faster" for containment queries. An expression index on a single key is smaller still, but only helps queries on that key (PostgreSQL JSON types).

-- Illustrative only
create table custom_field_definitions (
  id          uuid primary key default gen_random_uuid(),
  tenant_id   uuid not null references tenants(id),
  object_type text not null,   -- 'contact', 'company', 'deal'
  key         text not null,   -- 'industry'
  label       text not null,   -- 'Industry'
  data_type   text not null,   -- 'text', 'number', 'date', 'select'
  options     jsonb,           -- allowed values for 'select'
  unique (tenant_id, object_type, key)
);

create index contacts_custom_idx on contacts using gin (custom jsonb_path_ops);

-- Find roofing contacts for one tenant
select id, first_name, last_name
from contacts
where tenant_id = $1
  and custom @> '{"industry": "roofing"}';

Two cautions from the PostgreSQL docs. Any update locks the whole row, so keep JSON documents "to a manageable size." And each document should ideally hold data that business rules can't sensibly split apart (PostgreSQL JSON types). In practice: a modest set of custom field values per record is fine; a growing history or log is not, so give that its own table.

EAV

Entity-attribute-value stores each field value as its own row: record ID, field name, value. It looks flexible, but reasoning through real use shows the problems. A simple report that filters on three custom fields needs three joins, every value tends to be stored as text so type checks move into application code, and the database can't enforce required fields. JSONB gives you most of the flexibility with far simpler queries.

A Practical Hybrid

A practical hybrid is columns for core fields, JSONB for custom fields and a definition table that tells the interface how to display and validate each field. This is a pragmatic trade-off: PostgreSQL's docs suggest each JSON document ideally holds one atomic datum, and a bag of custom field values bends that advice in exchange for flexibility. When a custom field becomes central to reporting, promote it to a real column with a migration.

Activity Logging: Calls, Emails, Meetings and Notes

Activities record what happened with a customer. Use one activities table with a type column, plus a link table so a single activity can attach to many records.

-- Illustrative only
create table activities (
  id          uuid primary key default gen_random_uuid(),
  tenant_id   uuid not null references tenants(id),
  type        text not null check (type in ('call','email','meeting','note','task')),
  occurred_at timestamptz not null,
  user_id     uuid references users(id),
  subject     text,
  body        text,
  details     jsonb not null default '{}',  -- call duration, meeting link, email message ID
  created_at  timestamptz not null default now()
);

create table activity_links (
  tenant_id   uuid not null references tenants(id),
  activity_id uuid not null references activities(id),
  record_type text not null,  -- 'contact', 'company', 'deal'
  record_id   uuid not null,
  primary key (activity_id, record_type, record_id)
);
create index on activity_links (record_type, record_id);

Because record_id points at different tables, the database can't enforce it with a foreign key; validate it in the application or use separate nullable foreign-key columns per record type.

An email to three people about one deal becomes one activity with four links, so it shows on all three contacts and the deal. For synced email and calendar data, store the provider's message or event ID in details and check it before inserting, so re-syncs don't create duplicates.

Building an Audit Trail

The audit trail answers a different question: who changed which data, and what was it before? This matters when a deal amount changes unexpectedly, a contact's owner is reassigned, or a customer asks you to prove what happened.

The PostgreSQL documentation includes a row-level audit trigger pattern that records the operation, timestamp, user and row values. It also notes that statement-level triggers using transition tables "can be significantly faster" when a statement changes many rows, such as a bulk import (PostgreSQL triggers).

-- Illustrative only, adapted from the row-level pattern in the PostgreSQL docs
create table audit_log (
  id         bigint generated always as identity primary key,
  table_name text not null,
  row_id     uuid not null,
  operation  text not null,  -- INSERT, UPDATE, DELETE
  changed_by text default current_setting('app.user_id', true),
  changed_at timestamptz not null default now(),
  old_values jsonb,
  new_values jsonb
);

create function log_change() returns trigger as $$
begin
  if (tg_op = 'DELETE') then
    insert into audit_log (table_name, row_id, operation, old_values)
    values (tg_table_name, old.id, tg_op, to_jsonb(old));
  elsif (tg_op = 'UPDATE') then
    insert into audit_log (table_name, row_id, operation, old_values, new_values)
    values (tg_table_name, new.id, tg_op, to_jsonb(old), to_jsonb(new));
  else
    insert into audit_log (table_name, row_id, operation, new_values)
    values (tg_table_name, new.id, tg_op, to_jsonb(new));
  end if;
  return null;
end;
$$ language plpgsql;

create trigger deals_audit
after insert or update or delete on deals
for each row execute function log_change();

Note the app.user_id setting. Most applications connect to the database with one shared account, so without passing the real user's ID into the session, every change would appear to come from the same user. Make the audit table append-only for the application role.

Permissions: Who Can See Which Records

CRM permissions usually work at two levels. Role permissions decide what a user can do (edit deals, export contacts, manage settings). Record permissions decide which records they can see: everything, their team's, or only their own.

PostgreSQL row-level security can enforce record-level rules in the database. USING controls which rows are visible, WITH CHECK controls which rows can be written, and multiple policies combine: permissive policies with OR, restrictive policies with AND (PostgreSQL row security).

-- Illustrative only
alter table deals enable row level security;
alter table deals force row level security;  -- apply to the table owner too

-- Restrictive: must always be in the current tenant
create policy deals_tenant on deals as restrictive
  using (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

-- Permissive: reps see their own deals, managers see all
create policy deals_visibility on deals
  using (
    owner_id = nullif(current_setting('app.user_id', true), '')::uuid
    or current_setting('app.role', true) = 'manager'
  )
  with check (
    owner_id = nullif(current_setting('app.user_id', true), '')::uuid
    or current_setting('app.role', true) = 'manager'
  );

Settings such as app.role and app.tenant_id can be set by any database session, so they're only trustworthy when your application server is the only thing connecting to the database. Alternatively, have the policy check the user's role against the memberships table instead of a session setting.

Keep the same rules in your application code as well, and test them. Our multi-tenant SaaS architecture guide covers the ways RLS can be bypassed.

Designing for Reporting

Reporting is where data model shortcuts show up. A few practitioner rules:

• Keep history, not just current state. The stage history table powers win rates, stage duration and forecast accuracy.
• Make custom fields reportable. JSONB custom fields make reporting harder unless they're indexed or promoted to columns. Promote the fields managers filter on every week.
• Store lead source data on the contact or deal. Campaign, source and click identifiers let you connect closed revenue back to marketing spend.
• Separate heavy reporting from daily use. Run large reports against a read replica or summary tables so they don't slow down reps logging calls.

When to Just Configure HubSpot or Salesforce

A custom data model isn't always the answer. If your process fits standard objects, a standard pipeline and standard reports, configuring HubSpot or Salesforce is usually cheaper than building. Building means you own everything those platforms handle for you: data migration, duplicate detection and merging, email and calendar sync, integrations and ongoing maintenance.

Custom makes sense when your core workflow doesn't fit standard objects, when you're paying for workarounds and add-ons to approximate it, or when per-seat costs keep climbing. If you plan to sell your CRM to other businesses rather than use it internally, the scope grows into a full product; our guide on how to build a SaaS platform covers what that adds.

Getting the Model Right Before the Interface

A CRM's screens are relatively easy to redesign; its data model shapes everything for years. Start with proven objects, use link tables for relationships, choose a deliberate custom field strategy, log activities and changes separately and enforce permissions in both code and database. If you're planning a CRM built around your own process, Robustech's custom CRM development service designs and builds data models you own outright.