CRM Sales Schema — Lab — StealThis.dev

Diagram Views

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 }

SQL Snippets

schema.sql

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);

seed.sql

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');

queries.sql

-- 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;