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