Designing a CRM Data Model That Doesn't Break in 6 Months

Most CRM data models look clean at v1 and become unmaintainable by v3. Here's the schema design process I use — from domain modeling to PostgreSQL — that scales with business requirements.

CRM Data Model Design

The hardest part of building a CRM isn't the UI. It's the data model. Get it wrong, and every new feature requires a migration that breaks something.

Start With Domain, Not Tables

Before writing SQL, map the business concepts:

- Contact — a person or company

- Deal — an opportunity linked to a contact

- Activity — a log entry (call, email, meeting) on any entity

- Stage — a step in the pipeline

- Pipeline — an ordered collection of stages

These are your aggregates. Build your schema from them, not from UI screens.

The Core Schema

-- Contacts
CREATE TABLE contacts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type VARCHAR(20) CHECK (type IN ('person', 'company')),
  name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  custom_fields JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Pipelines & Stages
CREATE TABLE pipelines (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  is_default BOOLEAN DEFAULT FALSE
);

CREATE TABLE stages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pipeline_id UUID REFERENCES pipelines(id),
  name TEXT NOT NULL,
  position INTEGER NOT NULL,
  win_probability INTEGER DEFAULT 0
);

-- Deals
CREATE TABLE deals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  contact_id UUID REFERENCES contacts(id),
  stage_id UUID REFERENCES stages(id),
  value DECIMAL(12,2),
  currency CHAR(3) DEFAULT 'USD',
  closed_at TIMESTAMPTZ,
  custom_fields JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

The JSONB Pattern for Custom Fields

Every CRM eventually needs custom fields. Don't add columns for them — you'll end up with 40 nullable columns. Use JSONB:

-- Index specific custom field
CREATE INDEX idx_contacts_custom_source 
  ON contacts ((custom_fields->>'source'));

This gives you flexibility without sacrificing query performance for the fields you actually filter on.

Activity Log as Append-Only

Never update activity records. Make it an append-only log:

CREATE TABLE activities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_type VARCHAR(20) NOT NULL, -- 'deal', 'contact'
  entity_id UUID NOT NULL,
  type VARCHAR(30) NOT NULL, -- 'call', 'email', 'note', 'stage_change'
  payload JSONB NOT NULL,
  actor_id UUID REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

This pattern gives you a full audit trail, timeline views, and analytics — all from one table.

What to Avoid

- Don't put pipeline logic in the application layer. Use DB constraints for stage ordering.

- Don't use soft deletes everywhere. Archive records properly.

- Don't normalize prematurely. JSONB for flexible data is fine.

The Test for a Good Model

Can you answer these queries without joins that span 5+ tables?

- All deals closing this month by stage

- All activities for a contact in last 30 days

- Win rate by pipeline stage

If yes, your model is solid.