Data Architecture & Pipeline Modeling

The Secret to a CRM That Actually Gets Used

The #1 reason sales reps and business owners abandon traditional CRMs is field fatigue. You open a contact card and you’re confronted with 87 generic fields like Fax Number, Assistant's Name, SIC Code, and Annual Revenue (USD).

In 7-Day CRM, we design from first principles:

• Only capture fields that trigger an action or inform a decision.
• Map your exact sales stages instead of forcing yourself into generic “Qualification / Presentation / Negotiation”.

---

Core Entities in Your Custom CRM

Every high-performance CRM revolves around 4 fundamental tables:

┌──────────────┐         ┌──────────────┐
│  COMPANIES   │1       *│   CONTACTS   │
│──────────────│─────────│──────────────│
│ id           │         │ id           │
│ name         │         │ company_id   │
│ domain       │         │ full_name    │
│ industry     │         │ email, phone │
└──────────────┘         └──────────────┘
                                │ 1
                                │
                                │ *
┌──────────────┐         ┌──────────────┐
│  ACTIVITIES  │*       1│    DEALS     │
│──────────────│─────────│──────────────│
│ id           │         │ id           │
│ deal_id      │         │ contact_id   │
│ type (call/  │         │ title, value │
│ email/note)  │         │ stage_id     │
│ content      │         │ next_step    │
└──────────────┘         └──────────────┘

---

The Mega-Prompt: Custom Schema Generator

Run this prompt inside your Claude Project. Replace the bracketed text with your business details:

I need a complete relational database schema for my business CRM.

Here is what we do:
- Business Type: [e.g., B2B High-Ticket Consulting / Local Home Services / SaaS / Digital Agency]
- Average Deal Size: [e.g., $5,000 - $25,000]
- Typical Sales Cycle: [e.g., 14 to 30 days]
- Our Sales Stages:
  1. New Inquiry (Inbound form or referral)
  2. Discovery Scheduled
  3. Proposal Sent
  4. Contract / Verbal Agreement
  5. Closed Won / Active Client
  6. Closed Lost

Please generate:
1. Complete PostgreSQL (Supabase-ready) DDL with UUID primary keys, foreign keys, timestamps, indexes, and ENUMs for stages.
2. An alternative JSON/Airtable field mapping in case I use Airtable.
3. Realistic sample seed data (5 contacts, 3 companies, 4 active deals with notes) so my CRM isn't empty on launch.

---

Sample Supabase SQL DDL (Ready to Paste)

If you’re using Supabase, open the SQL Editor in your Supabase dashboard and run the generated SQL:

-- 1. Create Enums
CREATE TYPE deal_stage AS ENUM (
  'new_inquiry',
  'discovery_scheduled',
  'proposal_sent',
  'verbal_agreement',
  'closed_won',
  'closed_lost'
);

CREATE TYPE activity_type AS ENUM ('call', 'email', 'meeting', 'note', 'task');

-- 2. Companies Table
CREATE TABLE companies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  domain TEXT,
  industry TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 3. Contacts Table
CREATE TABLE contacts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID REFERENCES companies(id) ON DELETE SET NULL,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  phone TEXT,
  lead_source TEXT DEFAULT 'Organic',
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 4. Deals Table
CREATE TABLE deals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  contact_id UUID REFERENCES contacts(id) ON DELETE CASCADE,
  company_id UUID REFERENCES companies(id) ON DELETE SET NULL,
  title TEXT NOT NULL,
  value NUMERIC(12, 2) NOT NULL DEFAULT 0.00,
  stage deal_stage NOT NULL DEFAULT 'new_inquiry',
  probability INTEGER DEFAULT 20,
  next_step TEXT,
  next_step_due TIMESTAMPTZ,
  closed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 5. Activities & Timeline Table
CREATE TABLE activities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  deal_id UUID REFERENCES deals(id) ON DELETE CASCADE,
  contact_id UUID REFERENCES contacts(id) ON DELETE CASCADE,
  type activity_type NOT NULL DEFAULT 'note',
  summary TEXT NOT NULL,
  details TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

---

Day 2 Homework & Milestone

1. Run the SQL script in your database or set up your tables in Airtable.
2. Verify that you can insert 1 test contact and 1 test deal.
3. Save the schema definition file inside your Claude Project Knowledge files so Claude references it in Day 3.

Next up in Day 3: We will use Claude Artifacts to generate a lightning-fast, interactive visual React UI with a draggable Kanban pipeline, deal drawer, and quick activity logger!