Seed & queries

Empty tables are boring. Let's put a tiny world in there and ask the questions a CRM UI asks.

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

Or from the repo root - same idea. queries.sql has the snippets below ready to .read.

Seed shape (what's in there)

• 2 users (owners)
• 3 companies
• 5 contacts
• a handful of deals across stages
• a few activities (notes / calls)

Open with headers for sanity:

.headers on
.mode column
SELECT id, name, email FROM users;
SELECT id, name FROM companies;

Query: company with its contacts

Screen: company detail page.

SELECT
  co.id AS company_id,
  co.name AS company_name,
  c.id AS contact_id,
  c.name AS contact_name,
  c.email,
  c.title
FROM companies AS co
LEFT JOIN contacts AS c
  ON c.company_id = co.id
 AND c.deleted_at IS NULL
WHERE co.id = 1
  AND co.deleted_at IS NULL
ORDER BY c.name;

Flat rows → your API groups them into { company, contacts: […] }.

Query: deal pipeline by stage

Screen: kanban / board.

SELECT
  stage,
  COUNT(*) AS deal_count,
  SUM(amount) AS total_amount
FROM deals
WHERE deleted_at IS NULL
  AND stage NOT IN ('won', 'lost')  -- open pipeline only
GROUP BY stage
ORDER BY
  CASE stage
    WHEN 'lead' THEN 1
    WHEN 'qualified' THEN 2
    WHEN 'proposal' THEN 3
    WHEN 'negotiation' THEN 4
    ELSE 5
  END;

Comments for the code above:

• aggregate for the column headers ("3 deals · $120k")
• CASE gives pipeline order instead of alphabetical stage names
• closed stages filtered out for the "open board" view

Cards inside a column:

SELECT id, title, amount, owner_id, company_id
FROM deals
WHERE deleted_at IS NULL
  AND stage = 'negotiation'
ORDER BY updated_at DESC;

Query: contact timeline

Screen: contact detail → activity feed.

SELECT
  a.id,
  a.kind,
  a.body,
  a.created_at,
  u.name AS author
FROM activities AS a
JOIN users AS u ON u.id = a.user_id
WHERE a.contact_id = 1
  AND a.deleted_at IS NULL
ORDER BY a.created_at DESC
LIMIT 50;

Query: my open deals

Screen: "My pipeline" for the logged-in user.

SELECT
  d.id,
  d.title,
  d.stage,
  d.amount,
  co.name AS company_name
FROM deals AS d
JOIN companies AS co ON co.id = d.company_id
WHERE d.owner_id = 1
  AND d.deleted_at IS NULL
  AND d.stage NOT IN ('won', 'lost')
ORDER BY d.updated_at DESC;

Query: search contacts

Screen: topbar search / list filter.

SELECT c.id, c.name, c.email, co.name AS company_name
FROM contacts AS c
LEFT JOIN companies AS co ON co.id = c.company_id
WHERE c.deleted_at IS NULL
  AND (
    c.name LIKE '%' || :q || '%'
    OR c.email LIKE '%' || :q || '%'
  )
ORDER BY c.name
LIMIT 20 OFFSET 0;

In the sqlite3 CLI you can't use named :q binds the same way - swap in a literal while practicing:

AND (c.name LIKE '%ada%' OR c.email LIKE '%ada%')

In app code, always bind parameters - never string-concat user search into SQL.

Try it

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

Next: patterns that make the UI feel like a product - CRM UI patterns.