186 lines
7.2 KiB
SQL
186 lines
7.2 KiB
SQL
-- Enums
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'userrole') THEN
|
|
CREATE TYPE userrole AS ENUM ('ADMIN', 'USER');
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'campaignstatus') THEN
|
|
CREATE TYPE campaignstatus AS ENUM ('DRAFT', 'SCHEDULED', 'SENDING', 'DONE', 'FAILED');
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'recipientstatus') THEN
|
|
CREATE TYPE recipientstatus AS ENUM ('PENDING', 'SENT', 'DELIVERED', 'READ', 'FAILED');
|
|
END IF;
|
|
END$$;
|
|
|
|
-- users
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR NOT NULL,
|
|
password_hash VARCHAR NOT NULL,
|
|
role userrole NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS ix_users_email ON users (email);
|
|
CREATE INDEX IF NOT EXISTS ix_users_id ON users (id);
|
|
|
|
-- contacts
|
|
CREATE TABLE IF NOT EXISTS contacts (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
phone_e164 VARCHAR NOT NULL,
|
|
first_name VARCHAR,
|
|
last_name VARCHAR,
|
|
email VARCHAR,
|
|
opted_in BOOLEAN NOT NULL,
|
|
conversation_window_open BOOLEAN NOT NULL,
|
|
source VARCHAR,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now(),
|
|
CONSTRAINT uq_user_phone UNIQUE (user_id, phone_e164)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_contacts_user_phone ON contacts (user_id, phone_e164);
|
|
CREATE INDEX IF NOT EXISTS ix_contacts_id ON contacts (id);
|
|
CREATE INDEX IF NOT EXISTS ix_contacts_user_id ON contacts (user_id);
|
|
|
|
-- contact_tags
|
|
CREATE TABLE IF NOT EXISTS contact_tags (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name VARCHAR NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_contact_tags_id ON contact_tags (id);
|
|
CREATE INDEX IF NOT EXISTS ix_contact_tags_user_id ON contact_tags (user_id);
|
|
|
|
-- contact_tag_map
|
|
CREATE TABLE IF NOT EXISTS contact_tag_map (
|
|
id SERIAL PRIMARY KEY,
|
|
contact_id INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
tag_id INTEGER NOT NULL REFERENCES contact_tags(id) ON DELETE CASCADE,
|
|
CONSTRAINT uq_contact_tag UNIQUE (contact_id, tag_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_contact_tag_map_id ON contact_tag_map (id);
|
|
|
|
-- dnd_list
|
|
CREATE TABLE IF NOT EXISTS dnd_list (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
phone_e164 VARCHAR NOT NULL,
|
|
reason TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
CONSTRAINT uq_dnd_user_phone UNIQUE (user_id, phone_e164)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_dnd_list_id ON dnd_list (id);
|
|
CREATE INDEX IF NOT EXISTS ix_dnd_list_phone_e164 ON dnd_list (phone_e164);
|
|
CREATE INDEX IF NOT EXISTS ix_dnd_list_user_id ON dnd_list (user_id);
|
|
|
|
-- lists
|
|
CREATE TABLE IF NOT EXISTS lists (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name VARCHAR NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_lists_id ON lists (id);
|
|
CREATE INDEX IF NOT EXISTS ix_lists_user_id ON lists (user_id);
|
|
|
|
-- list_members
|
|
CREATE TABLE IF NOT EXISTS list_members (
|
|
id SERIAL PRIMARY KEY,
|
|
list_id INTEGER NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
|
|
contact_id INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
CONSTRAINT uq_list_contact UNIQUE (list_id, contact_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_list_members_contact_id ON list_members (contact_id);
|
|
CREATE INDEX IF NOT EXISTS ix_list_members_id ON list_members (id);
|
|
CREATE INDEX IF NOT EXISTS ix_list_members_list_id ON list_members (list_id);
|
|
|
|
-- templates
|
|
CREATE TABLE IF NOT EXISTS templates (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name VARCHAR NOT NULL,
|
|
language VARCHAR NOT NULL,
|
|
body_text TEXT NOT NULL,
|
|
is_whatsapp_template BOOLEAN NOT NULL,
|
|
provider_template_name VARCHAR,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_templates_id ON templates (id);
|
|
CREATE INDEX IF NOT EXISTS ix_templates_user_id ON templates (user_id);
|
|
|
|
-- campaigns
|
|
CREATE TABLE IF NOT EXISTS campaigns (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name VARCHAR NOT NULL,
|
|
template_id INTEGER REFERENCES templates(id) ON DELETE SET NULL,
|
|
list_id INTEGER REFERENCES lists(id) ON DELETE SET NULL,
|
|
status campaignstatus NOT NULL,
|
|
scheduled_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_campaigns_id ON campaigns (id);
|
|
CREATE INDEX IF NOT EXISTS ix_campaigns_status ON campaigns (status);
|
|
CREATE INDEX IF NOT EXISTS ix_campaigns_user_id ON campaigns (user_id);
|
|
|
|
-- campaign_recipients
|
|
CREATE TABLE IF NOT EXISTS campaign_recipients (
|
|
id SERIAL PRIMARY KEY,
|
|
campaign_id INTEGER NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
|
|
contact_id INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
status recipientstatus NOT NULL,
|
|
provider_message_id VARCHAR,
|
|
last_error TEXT,
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_campaign_recipients_campaign_status ON campaign_recipients (campaign_id, status);
|
|
CREATE INDEX IF NOT EXISTS ix_campaign_recipients_campaign_id ON campaign_recipients (campaign_id);
|
|
CREATE INDEX IF NOT EXISTS ix_campaign_recipients_contact_id ON campaign_recipients (contact_id);
|
|
CREATE INDEX IF NOT EXISTS ix_campaign_recipients_id ON campaign_recipients (id);
|
|
|
|
-- send_logs
|
|
CREATE TABLE IF NOT EXISTS send_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
campaign_id INTEGER REFERENCES campaigns(id) ON DELETE CASCADE,
|
|
contact_id INTEGER REFERENCES contacts(id) ON DELETE CASCADE,
|
|
provider VARCHAR NOT NULL,
|
|
request_payload_json JSON,
|
|
response_payload_json JSON,
|
|
status VARCHAR NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_send_logs_campaign_id ON send_logs (campaign_id);
|
|
CREATE INDEX IF NOT EXISTS ix_send_logs_created_at ON send_logs (created_at);
|
|
CREATE INDEX IF NOT EXISTS ix_send_logs_id ON send_logs (id);
|
|
CREATE INDEX IF NOT EXISTS ix_send_logs_user_id ON send_logs (user_id);
|
|
|
|
-- jobs
|
|
CREATE TABLE IF NOT EXISTS jobs (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
type VARCHAR NOT NULL,
|
|
payload_json JSON NOT NULL,
|
|
status VARCHAR NOT NULL,
|
|
attempts INTEGER NOT NULL,
|
|
run_after TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
last_error TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_jobs_status_run_after ON jobs (status, run_after);
|
|
CREATE INDEX IF NOT EXISTS ix_jobs_id ON jobs (id);
|
|
CREATE INDEX IF NOT EXISTS ix_jobs_user_id ON jobs (user_id);
|
|
|
|
-- google_tokens
|
|
CREATE TABLE IF NOT EXISTS google_tokens (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
encrypted_token TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS ix_google_tokens_user_id ON google_tokens (user_id);
|
|
CREATE INDEX IF NOT EXISTS ix_google_tokens_id ON google_tokens (id);
|