crm.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(120) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role VARCHAR(50) DEFAULT 'sales',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE leads (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150),
    phone VARCHAR(50),
    source VARCHAR(100), -- e.g. Website, Facebook Ads, Referral
    status VARCHAR(50) DEFAULT 'new', -- new, contacted, nurturing, lost, converted
    owner_id BIGINT REFERENCES users(id), -- salesperson responsible
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE accounts (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    industry VARCHAR(120),
    website VARCHAR(150),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE contacts (
    id BIGSERIAL PRIMARY KEY,
    account_id BIGINT REFERENCES accounts(id),
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(150),
    phone VARCHAR(50),
    position VARCHAR(100),
    owner_id BIGINT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE deals (
    id BIGSERIAL PRIMARY KEY,
    contact_id BIGINT REFERENCES contacts(id),
    account_id BIGINT REFERENCES accounts(id),
    name VARCHAR(150) NOT NULL,
    value DECIMAL(10,2),
    stage VARCHAR(50), -- e.g., prospect, demo, negotiation, won, lost
    close_date DATE,
    owner_id BIGINT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE activities (
    id BIGSERIAL PRIMARY KEY,
    related_type VARCHAR(50), -- lead, contact, deal
    related_id BIGINT,        -- dynamic FK
    user_id BIGINT REFERENCES users(id),
    activity_type VARCHAR(50), -- call, email, meeting, task
    subject VARCHAR(255),
    due_date TIMESTAMP,
    status VARCHAR(50) DEFAULT 'open', -- open/completed
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE notes (
    id BIGSERIAL PRIMARY KEY,
    related_type VARCHAR(50), -- lead, contact, deal
    related_id BIGINT,
    user_id BIGINT REFERENCES users(id),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);


-- | Module                  | Purpose                                 |
-- | ----------------------- | --------------------------------------- |
-- | **Users**               | CRM system users (staff/sales team)     |
-- | **Roles / Permissions** | Optional access control                 |
-- | **Leads**               | Potential customers (unqualified)       |
-- | **Contacts**            | People (qualified)                      |
-- | **Accounts**            | Companies/Organizations                 |
-- | **Deals**               | Sales opportunities (pipeline)          |
-- | **Tasks / Activities**  | Follow-ups, calls, meetings             |
-- | **Notes**               | Logs and comments per lead/contact/deal |