CRM schema design

We're not building Salesforce. We're building a tiny CRM-shaped schema you could put a list page and a deal board on top of.

Runnable files live in examples/crm/ - this chapter walks the design; next chapter loads data and queries it.

What the UI needs

Picture a simple CRM:

• Companies - accounts you sell to
• Contacts - people at those companies
• Deals - opportunities in a pipeline (stage + amount)
• Activities - notes / calls / emails on a contact or deal
• Users - your teammates who own deals

That's enough to make real screens: company detail with contacts, contact timeline, deals board by stage.

Entity sketch

The schema

-- examples/crm/schema.sql (abbreviated walkthrough)

PRAGMA foreign_keys = ON;

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  deleted_at TEXT
);

CREATE TABLE companies (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  website TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  deleted_at TEXT
);

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  company_id INTEGER REFERENCES companies(id),
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  title TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  deleted_at TEXT
);

CREATE TABLE deals (
  id INTEGER PRIMARY KEY,
  company_id INTEGER NOT NULL REFERENCES companies(id),
  contact_id INTEGER REFERENCES contacts(id),
  owner_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  stage TEXT NOT NULL CHECK (
    stage IN ('lead', 'qualified', 'proposal', 'negotiation', 'won', 'lost')
  ),
  amount REAL NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  deleted_at TEXT
);

CREATE TABLE activities (
  id INTEGER PRIMARY KEY,
  contact_id INTEGER REFERENCES contacts(id),
  deal_id INTEGER REFERENCES deals(id),
  user_id INTEGER NOT NULL REFERENCES users(id),
  kind TEXT NOT NULL CHECK (kind IN ('note', 'call', 'email', 'meeting')),
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  deleted_at TEXT,
  CHECK (contact_id IS NOT NULL OR deal_id IS NOT NULL)
);

Comments for the code above:

• deleted_at - soft delete everywhere user-facing "remove" might happen
• stage / kind as CHECK enums - simple; a real app might use lookup tables
• activities must hang off a contact or a deal (or both) - the CHECK enforces "not floating in space"
• deals always have a company + owner; contact is optional (company-level opportunity)

Indexes for the screens we care about

CREATE INDEX idx_contacts_company ON contacts(company_id)
  WHERE deleted_at IS NULL;

CREATE INDEX idx_deals_stage ON deals(stage)
  WHERE deleted_at IS NULL;

CREATE INDEX idx_deals_owner ON deals(owner_id)
  WHERE deleted_at IS NULL;

CREATE INDEX idx_activities_contact ON activities(contact_id, created_at DESC)
  WHERE deleted_at IS NULL;

CREATE INDEX idx_activities_deal ON activities(deal_id, created_at DESC)
  WHERE deleted_at IS NULL;

Company detail, pipeline board, owner filter, timelines - covered.

What I deliberately skipped

• Custom fields / EAV spaghetti
• Multi-currency
• Full-text search engines
• Auth, roles, permissions tables

Enough schema to learn. Not enough to bill enterprise customers. 👌

Load it:

sqlite3 examples/crm/crm.db < examples/crm/schema.sql

Next: Seed & queries - fake data and the SQL behind the screens.