Home | submit

Internal CRM SQL

0 Points, by ianegan 46mins ago

    CREATE TABLE companies (
        id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        name        text NOT NULL,
        website     text,
        created_at  timestamptz NOT NULL DEFAULT now()
    );
    CREATE TABLE users (
        id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        name        text NOT NULL,
        email       text NOT NULL UNIQUE
    );

    -- additional tables.
    CREATE TABLE activities (
        id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        deal_id     bigint REFERENCES deals(id),
        user_id     bigint REFERENCES users(id),
        activity_type text NOT NULL,
        notes       text,
        created_at  timestamptz NOT NULL DEFAULT now()
    );
    -- additional 1 to many contact info
    CREATE TABLE contact_info (
        id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        contact_id  bigint NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
        info_type   text NOT NULL,       -- e.g. 'email', 'phone', 'address', 'social'
        label       text,                -- e.g. 'work', 'home', 'billing'
        value       text NOT NULL,
        is_primary  boolean NOT NULL DEFAULT false,
        created_at  timestamptz NOT NULL DEFAULT now()
    );

    CREATE INDEX idx_contact_info_contact_id ON contact_info(contact_id);


Answer Count: 0