CRM Sales Schema — StealThis.dev

Code

create table users (
  id bigint primary key generated always as identity,
  email text not null unique,
  full_name text not null,
  created_at timestamptz not null default now()
);

create table leads (
  id bigint primary key generated always as identity,
  source text not null,
  full_name text not null,
  email text,
  company_name text,
  status text not null check (status in ('new','qualified','disqualified')),
  owner_id bigint references users(id),
  created_at timestamptz not null default now()
);

create table accounts (
  id bigint primary key generated always as identity,
  name text not null,
  industry text,
  owner_id bigint references users(id),
  created_at timestamptz not null default now()
);

create table contacts (
  id bigint primary key generated always as identity,
  account_id bigint not null references accounts(id) on delete cascade,
  full_name text not null,
  email text,
  title text,
  created_at timestamptz not null default now()
);

create table deals (
  id bigint primary key generated always as identity,
  account_id bigint not null references accounts(id),
  owner_id bigint not null references users(id),
  name text not null,
  stage text not null check (stage in ('prospecting','proposal','negotiation','won','lost')),
  amount_cents integer not null default 0 check (amount_cents >= 0),
  expected_close_date date,
  created_at timestamptz not null default now()
);

create table activities (
  id bigint primary key generated always as identity,
  deal_id bigint references deals(id) on delete cascade,
  lead_id bigint references leads(id) on delete cascade,
  user_id bigint not null references users(id),
  type text not null check (type in ('call','email','meeting','note')),
  summary text not null,
  happened_at timestamptz not null,
  created_at timestamptz not null default now(),
  check ((deal_id is not null) or (lead_id is not null))
);

create index idx_leads_owner_status on leads(owner_id, status);
create index idx_deals_owner_stage on deals(owner_id, stage);
create index idx_activities_happened_at on activities(happened_at desc);

insert into users (email, full_name)
values
  ('sales1@crm.dev', 'Sales Rep 1'),
  ('sales2@crm.dev', 'Sales Rep 2');

insert into leads (source, full_name, email, company_name, status, owner_id)
values
  ('linkedin', 'Mia Ortiz', 'mia@northwind.com', 'Northwind', 'qualified', 1),
  ('webinar', 'Ivan Lee', 'ivan@globex.com', 'Globex', 'new', 2);

insert into accounts (name, industry, owner_id)
values
  ('Northwind', 'Logistics', 1),
  ('Globex', 'Manufacturing', 2);

insert into contacts (account_id, full_name, email, title)
values
  (1, 'Mia Ortiz', 'mia@northwind.com', 'Head of Ops'),
  (2, 'Ivan Lee', 'ivan@globex.com', 'CTO');

insert into deals (account_id, owner_id, name, stage, amount_cents, expected_close_date)
values
  (1, 1, 'Northwind Annual Plan', 'proposal', 240000, current_date + 20),
  (2, 2, 'Globex Pilot', 'prospecting', 60000, current_date + 35);

insert into activities (deal_id, user_id, type, summary, happened_at)
values
  (1, 1, 'meeting', 'Proposal walkthrough completed', now() - interval '2 days'),
  (2, 2, 'call', 'Qualification call done', now() - interval '1 day');

-- 1) Pipeline value by stage
select
  stage,
  count(*) as deals,
  sum(amount_cents) / 100.0 as amount_usd
from deals
group by stage
order by amount_usd desc;

-- 2) Activity volume per rep (last 30 days)
select
  u.email,
  count(a.id) as activities_30d
from users u
left join activities a
  on a.user_id = u.id
 and a.happened_at >= now() - interval '30 days'
group by u.id
order by activities_30d desc;

-- 3) Open deals ordered by expected close date
select
  d.id,
  d.name,
  d.stage,
  d.expected_close_date,
  a.name as account_name
from deals d
join accounts a on a.id = d.account_id
where d.stage not in ('won', 'lost')
order by d.expected_close_date nulls last;

erDiagram
  users ||--o{ leads : owns
  users ||--o{ accounts : owns
  accounts ||--o{ contacts : includes
  accounts ||--o{ deals : signs
  users ||--o{ deals : manages
  deals ||--o{ activities : logs
  leads ||--o{ activities : logs
  users ||--o{ activities : performs

  users {
    bigint id PK
    text email
  }

  leads {
    bigint id PK
    bigint owner_id FK
    text status
  }

  accounts {
    bigint id PK
    bigint owner_id FK
    text name
  }

  contacts {
    bigint id PK
    bigint account_id FK
    text email
  }

  deals {
    bigint id PK
    bigint account_id FK
    bigint owner_id FK
    text stage
    int amount_cents
  }

  activities {
    bigint id PK
    bigint deal_id FK
    bigint lead_id FK
    bigint user_id FK
    text type
  }

Pipeline-oriented CRM schema for B2B sales processes.