656 lines
26 KiB
SQL
656 lines
26 KiB
SQL
-- Copyright 2026 Kingsrook, LLC. Licensed under the Apache License, Version 2.0.
|
|
-- https://www.apache.org/licenses/LICENSE-2.0
|
|
-- QBit CRM 0.2.0 P1 PostgreSQL reference schema (34 tables).
|
|
-- QQQ table names equal SQL names; Java field names map to snake_case columns.
|
|
-- Unique constraints match QQQ metadata. Relationship columns are indexed;
|
|
-- foreign-key constraints are deliberately not imposed (the QQQ/host layer owns
|
|
-- validation and deletion policy, including polymorphic and optional references).
|
|
-- Optional tenancy: separately include crm-tenancy.changelog.sql with context crm-tenancy.
|
|
-- With tenancy off, do not include it; this base schema has no tenant_key columns.
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_activity (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
activity_type_id INTEGER NOT NULL,
|
|
subject VARCHAR(500) NOT NULL,
|
|
description TEXT,
|
|
contact_id INTEGER,
|
|
company_id INTEGER,
|
|
deal_id INTEGER,
|
|
owner_user_id TEXT NOT NULL,
|
|
assigned_to_user_id TEXT,
|
|
start_date TIMESTAMP WITHOUT TIME ZONE,
|
|
end_date TIMESTAMP WITHOUT TIME ZONE,
|
|
due_date TIMESTAMP WITHOUT TIME ZONE,
|
|
completed_date TIMESTAMP WITHOUT TIME ZONE,
|
|
is_completed BOOLEAN DEFAULT FALSE NOT NULL,
|
|
priority INTEGER,
|
|
duration_minutes INTEGER,
|
|
outcome_id INTEGER,
|
|
location VARCHAR(500),
|
|
conference_link VARCHAR(1000),
|
|
direction INTEGER,
|
|
is_reminder_set BOOLEAN DEFAULT FALSE,
|
|
reminder_date TIMESTAMP WITHOUT TIME ZONE,
|
|
external_calendar_id VARCHAR(500),
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_activity_type_id ON crm_activity (activity_type_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_contact_id ON crm_activity (contact_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_company_id ON crm_activity (company_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_deal_id ON crm_activity (deal_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_owner_user_id ON crm_activity (owner_user_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_assigned_to_user_id ON crm_activity (assigned_to_user_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_outcome_id ON crm_activity (outcome_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_external_calendar_id ON crm_activity (external_calendar_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_activity_outcome (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
activity_type_id INTEGER NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_activity_outcome_1 UNIQUE (activity_type_id, name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_outcome_activity_type_id ON crm_activity_outcome (activity_type_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_activity_participant (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
activity_id INTEGER NOT NULL,
|
|
contact_id INTEGER,
|
|
user_id TEXT,
|
|
role INTEGER,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_participant_activity_id ON crm_activity_participant (activity_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_participant_contact_id ON crm_activity_participant (contact_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_activity_participant_user_id ON crm_activity_participant (user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_activity_type (
|
|
code VARCHAR(50) UNIQUE,
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
icon_name VARCHAR(50),
|
|
is_system BOOLEAN DEFAULT FALSE NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_activity_type_1 UNIQUE (name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_assignment_rule (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(250) NOT NULL,
|
|
description TEXT,
|
|
entity_type INTEGER NOT NULL,
|
|
criteria_json TEXT,
|
|
assign_strategy INTEGER NOT NULL,
|
|
assign_to_user_id TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_assignment_rule_assign_to_user_id ON crm_assignment_rule (assign_to_user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_assignment_rule_member (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
assignment_rule_id INTEGER NOT NULL,
|
|
user_id TEXT NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
last_assigned_date TIMESTAMP WITHOUT TIME ZONE,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_assignment_rule_member_1 UNIQUE (assignment_rule_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_assignment_rule_member_assignment_rule_id ON crm_assignment_rule_member (assignment_rule_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_assignment_rule_member_user_id ON crm_assignment_rule_member (user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_company (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(250) NOT NULL,
|
|
domain VARCHAR(250),
|
|
company_type INTEGER,
|
|
industry_id INTEGER,
|
|
employee_count INTEGER,
|
|
annual_revenue DECIMAL(20,6),
|
|
annual_revenue_currency_code VARCHAR(3) DEFAULT 'USD',
|
|
phone VARCHAR(50),
|
|
website VARCHAR(500),
|
|
address_line1 VARCHAR(250),
|
|
address_line2 VARCHAR(250),
|
|
city VARCHAR(100),
|
|
state VARCHAR(100),
|
|
postal_code VARCHAR(20),
|
|
country VARCHAR(100),
|
|
owner_user_id TEXT NOT NULL,
|
|
parent_company_id INTEGER,
|
|
description TEXT,
|
|
last_activity_date TIMESTAMP WITHOUT TIME ZONE,
|
|
last_contacted_date TIMESTAMP WITHOUT TIME ZONE,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_company_industry_id ON crm_company (industry_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_company_owner_user_id ON crm_company (owner_user_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_company_parent_company_id ON crm_company (parent_company_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_consent_record (
|
|
consent_text TEXT,
|
|
evidence_form_submission_id INTEGER,
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
contact_id INTEGER NOT NULL,
|
|
consent_type INTEGER NOT NULL,
|
|
status INTEGER NOT NULL,
|
|
legal_basis INTEGER,
|
|
source VARCHAR(250),
|
|
consent_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
|
expiry_date TIMESTAMP WITHOUT TIME ZONE,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_consent_record_contact_id ON crm_consent_record (contact_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_contact (
|
|
first_touch_utm_source VARCHAR(500),
|
|
first_touch_utm_medium VARCHAR(500),
|
|
first_touch_utm_campaign VARCHAR(500),
|
|
first_touch_utm_content VARCHAR(500),
|
|
first_touch_utm_term VARCHAR(500),
|
|
first_touch_landing_page VARCHAR(1000),
|
|
first_touch_referrer VARCHAR(1000),
|
|
first_touch_date TIMESTAMP WITHOUT TIME ZONE,
|
|
last_touch_utm_source VARCHAR(500),
|
|
last_touch_utm_medium VARCHAR(500),
|
|
last_touch_utm_campaign VARCHAR(500),
|
|
last_touch_utm_content VARCHAR(500),
|
|
last_touch_utm_term VARCHAR(500),
|
|
last_touch_landing_page VARCHAR(1000),
|
|
last_touch_referrer VARCHAR(1000),
|
|
last_touch_date TIMESTAMP WITHOUT TIME ZONE,
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
first_name VARCHAR(100),
|
|
last_name VARCHAR(100),
|
|
email VARCHAR(320),
|
|
secondary_email VARCHAR(320),
|
|
phone VARCHAR(50),
|
|
mobile_phone VARCHAR(50),
|
|
job_title VARCHAR(150),
|
|
department VARCHAR(100),
|
|
company_id INTEGER,
|
|
lifecycle_stage_id INTEGER,
|
|
lead_source_id INTEGER,
|
|
owner_user_id TEXT,
|
|
address_line1 VARCHAR(250),
|
|
address_line2 VARCHAR(250),
|
|
city VARCHAR(100),
|
|
state VARCHAR(100),
|
|
postal_code VARCHAR(20),
|
|
country VARCHAR(100),
|
|
timezone VARCHAR(50),
|
|
preferred_language VARCHAR(10),
|
|
website VARCHAR(500),
|
|
linkedin_url VARCHAR(500),
|
|
description TEXT,
|
|
lead_score INTEGER DEFAULT 0,
|
|
do_not_email BOOLEAN DEFAULT FALSE,
|
|
do_not_call BOOLEAN DEFAULT FALSE,
|
|
unsubscribe_date TIMESTAMP WITHOUT TIME ZONE,
|
|
last_activity_date TIMESTAMP WITHOUT TIME ZONE,
|
|
last_contacted_date TIMESTAMP WITHOUT TIME ZONE,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_contact_company_id ON crm_contact (company_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_contact_lifecycle_stage_id ON crm_contact (lifecycle_stage_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_contact_lead_source_id ON crm_contact (lead_source_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_contact_owner_user_id ON crm_contact (owner_user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_contact_role (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_contact_role_1 UNIQUE (name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_currency (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
currency_code VARCHAR(3) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
symbol VARCHAR(5),
|
|
exchange_rate_to_base DECIMAL(20,6) DEFAULT 1.0 NOT NULL,
|
|
is_base_currency BOOLEAN DEFAULT FALSE NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
rate_last_updated_date TIMESTAMP WITHOUT TIME ZONE,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_currency_1 UNIQUE (currency_code)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_deal (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(250) NOT NULL,
|
|
pipeline_id INTEGER NOT NULL,
|
|
pipeline_stage_id INTEGER NOT NULL,
|
|
amount DECIMAL(20,6),
|
|
currency_code VARCHAR(3) DEFAULT 'USD',
|
|
amount_in_base_currency DECIMAL(20,6),
|
|
probability_override_pct INTEGER,
|
|
weighted_amount DECIMAL(20,6),
|
|
company_id INTEGER,
|
|
primary_contact_id INTEGER,
|
|
owner_user_id TEXT,
|
|
lead_source_id INTEGER,
|
|
expected_close_date DATE,
|
|
actual_close_date DATE,
|
|
win_loss_reason_id INTEGER,
|
|
stage_entered_date TIMESTAMP WITHOUT TIME ZONE,
|
|
description TEXT,
|
|
priority INTEGER,
|
|
last_activity_date TIMESTAMP WITHOUT TIME ZONE,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_pipeline_id ON crm_deal (pipeline_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_pipeline_stage_id ON crm_deal (pipeline_stage_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_primary_contact_id ON crm_deal (primary_contact_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_company_id ON crm_deal (company_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_owner_user_id ON crm_deal (owner_user_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_lead_source_id ON crm_deal (lead_source_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_win_loss_reason_id ON crm_deal (win_loss_reason_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_deal_contact (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
deal_id INTEGER NOT NULL,
|
|
contact_id INTEGER NOT NULL,
|
|
contact_role_id INTEGER,
|
|
is_primary BOOLEAN DEFAULT FALSE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_deal_contact_1 UNIQUE (deal_id, contact_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_contact_deal_id ON crm_deal_contact (deal_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_contact_contact_id ON crm_deal_contact (contact_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_contact_contact_role_id ON crm_deal_contact (contact_role_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_deal_product (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
deal_id INTEGER NOT NULL,
|
|
product_id INTEGER NOT NULL,
|
|
quantity DECIMAL(20,6) DEFAULT 1 NOT NULL,
|
|
unit_price DECIMAL(20,6) NOT NULL,
|
|
discount_pct DECIMAL(20,6) DEFAULT 0,
|
|
total_amount DECIMAL(20,6),
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_product_deal_id ON crm_deal_product (deal_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_product_product_id ON crm_deal_product (product_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_deal_stage_history (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
deal_id INTEGER NOT NULL,
|
|
from_stage_id INTEGER,
|
|
to_stage_id INTEGER NOT NULL,
|
|
user_id TEXT NOT NULL,
|
|
transition_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
|
duration_in_from_stage_days INTEGER,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_stage_history_deal_id ON crm_deal_stage_history (deal_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_stage_history_from_stage_id ON crm_deal_stage_history (from_stage_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_stage_history_to_stage_id ON crm_deal_stage_history (to_stage_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_deal_stage_history_user_id ON crm_deal_stage_history (user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_email_list (
|
|
list_key VARCHAR(250) NOT NULL UNIQUE,
|
|
require_double_opt_in BOOLEAN DEFAULT TRUE NOT NULL,
|
|
consent_text TEXT,
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(250) NOT NULL,
|
|
slug VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
is_public BOOLEAN DEFAULT TRUE NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
member_count INTEGER DEFAULT 0,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_email_list_1 UNIQUE (name),
|
|
CONSTRAINT uq_crm_email_list_2 UNIQUE (slug)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_email_list_member (
|
|
confirmation_token_hash VARCHAR(64),
|
|
confirmation_sent_date TIMESTAMP WITHOUT TIME ZONE,
|
|
confirmed_date TIMESTAMP WITHOUT TIME ZONE,
|
|
source VARCHAR(250),
|
|
consent_text TEXT,
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
email_list_id INTEGER NOT NULL,
|
|
contact_id INTEGER,
|
|
email VARCHAR(320) NOT NULL,
|
|
first_name VARCHAR(100),
|
|
status INTEGER DEFAULT 1 NOT NULL,
|
|
subscribed_date TIMESTAMP WITHOUT TIME ZONE,
|
|
unsubscribed_date TIMESTAMP WITHOUT TIME ZONE,
|
|
subscription_source VARCHAR(250),
|
|
form_submission_id INTEGER,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_email_list_member_1 UNIQUE (email_list_id, email)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_list_member_email_list_id ON crm_email_list_member (email_list_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_list_member_contact_id ON crm_email_list_member (contact_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_list_member_form_submission_id ON crm_email_list_member (form_submission_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_email_message (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
idempotency_key VARCHAR(250) UNIQUE,
|
|
email_message_id VARCHAR(100),
|
|
delivered_date TIMESTAMP WITHOUT TIME ZONE,
|
|
bounced_date TIMESTAMP WITHOUT TIME ZONE,
|
|
activity_id INTEGER,
|
|
contact_id INTEGER,
|
|
from_address VARCHAR(250),
|
|
from_name VARCHAR(100),
|
|
to_addresses TEXT NOT NULL,
|
|
cc_addresses TEXT,
|
|
bcc_addresses TEXT,
|
|
subject VARCHAR(500) NOT NULL,
|
|
body_html TEXT,
|
|
body_text TEXT,
|
|
sent_date TIMESTAMP WITHOUT TIME ZONE,
|
|
thread_id VARCHAR(250),
|
|
email_template_id INTEGER,
|
|
sequence_enrollment_id INTEGER,
|
|
direction INTEGER NOT NULL,
|
|
status INTEGER,
|
|
is_tracked BOOLEAN DEFAULT TRUE,
|
|
open_count INTEGER DEFAULT 0,
|
|
click_count INTEGER DEFAULT 0,
|
|
first_opened_date TIMESTAMP WITHOUT TIME ZONE,
|
|
first_clicked_date TIMESTAMP WITHOUT TIME ZONE,
|
|
bounce_type INTEGER,
|
|
is_spam_complaint BOOLEAN DEFAULT FALSE,
|
|
reply_to_email_id INTEGER,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_message_email_message_id ON crm_email_message (email_message_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_message_contact_id ON crm_email_message (contact_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_message_activity_id ON crm_email_message (activity_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_message_thread_id ON crm_email_message (thread_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_message_email_template_id ON crm_email_message (email_template_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_message_sequence_enrollment_id ON crm_email_message (sequence_enrollment_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_message_reply_to_email_id ON crm_email_message (reply_to_email_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_email_sequence (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(250) NOT NULL,
|
|
description TEXT,
|
|
owner_user_id TEXT NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
total_steps INTEGER,
|
|
business_days_only BOOLEAN DEFAULT TRUE NOT NULL,
|
|
send_window_start_hour INTEGER,
|
|
send_window_end_hour INTEGER,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_sequence_owner_user_id ON crm_email_sequence (owner_user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_email_template (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(250) NOT NULL,
|
|
subject VARCHAR(500) NOT NULL,
|
|
body_html TEXT NOT NULL,
|
|
body_text TEXT,
|
|
category VARCHAR(100),
|
|
owner_user_id TEXT NOT NULL,
|
|
is_shared BOOLEAN DEFAULT FALSE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_email_template_owner_user_id ON crm_email_template (owner_user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_external_mapping (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
entity_type INTEGER NOT NULL,
|
|
entity_id INTEGER NOT NULL,
|
|
external_system VARCHAR(100) NOT NULL,
|
|
external_id VARCHAR(500) NOT NULL,
|
|
last_sync_date TIMESTAMP WITHOUT TIME ZONE,
|
|
sync_status INTEGER,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_external_mapping_1 UNIQUE (entity_type, entity_id, external_system)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_external_mapping_entity_id ON crm_external_mapping (entity_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_external_mapping_external_id ON crm_external_mapping (external_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_form_submission (
|
|
form_key VARCHAR(250) NOT NULL,
|
|
marketing_consent BOOLEAN DEFAULT FALSE,
|
|
consent_text TEXT,
|
|
landing_page VARCHAR(1000),
|
|
visitor_key VARCHAR(64),
|
|
client_hash VARCHAR(64),
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
form_type INTEGER NOT NULL,
|
|
email VARCHAR(320) NOT NULL,
|
|
first_name VARCHAR(100),
|
|
last_name VARCHAR(100),
|
|
company VARCHAR(250),
|
|
phone VARCHAR(50),
|
|
message TEXT,
|
|
source_url VARCHAR(1000),
|
|
utm_source VARCHAR(500),
|
|
utm_medium VARCHAR(500),
|
|
utm_campaign VARCHAR(500),
|
|
utm_term VARCHAR(500),
|
|
utm_content VARCHAR(500),
|
|
referrer VARCHAR(1000),
|
|
contact_id INTEGER,
|
|
conversion_status INTEGER DEFAULT 1 NOT NULL,
|
|
converted_date TIMESTAMP WITHOUT TIME ZONE,
|
|
converted_by_user_id TEXT,
|
|
metadata TEXT,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_form_submission_contact_id ON crm_form_submission (contact_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_form_submission_converted_by_user_id ON crm_form_submission (converted_by_user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_industry (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_industry_1 UNIQUE (name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_lead_score_rule (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(250) NOT NULL,
|
|
description TEXT,
|
|
entity_type INTEGER NOT NULL,
|
|
field_path VARCHAR(250) NOT NULL,
|
|
operator INTEGER NOT NULL,
|
|
field_value VARCHAR(500),
|
|
score_adjustment INTEGER NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_lead_source (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_lead_source_1 UNIQUE (name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_lifecycle_stage (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
description VARCHAR(500),
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_lifecycle_stage_1 UNIQUE (name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_pipeline (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
description VARCHAR(500),
|
|
is_default BOOLEAN DEFAULT FALSE NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_pipeline_1 UNIQUE (name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_pipeline_stage (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
pipeline_id INTEGER NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
probability_pct INTEGER DEFAULT 0 NOT NULL,
|
|
rot_days INTEGER,
|
|
required_fields_json TEXT,
|
|
is_closed_won BOOLEAN DEFAULT FALSE NOT NULL,
|
|
is_closed_lost BOOLEAN DEFAULT FALSE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_pipeline_stage_1 UNIQUE (pipeline_id, name),
|
|
CONSTRAINT uq_crm_pipeline_stage_2 UNIQUE (pipeline_id, sort_order)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_pipeline_stage_pipeline_id ON crm_pipeline_stage (pipeline_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_product (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(250) NOT NULL,
|
|
sku VARCHAR(100),
|
|
description TEXT,
|
|
unit_price DECIMAL(20,6) NOT NULL,
|
|
currency_code VARCHAR(3) DEFAULT 'USD',
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_product_1 UNIQUE (sku)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_sales_goal (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
pipeline_id INTEGER,
|
|
period_type INTEGER NOT NULL,
|
|
period_start DATE NOT NULL,
|
|
period_end DATE NOT NULL,
|
|
target_amount DECIMAL(20,6) NOT NULL,
|
|
currency_code VARCHAR(3) DEFAULT 'USD',
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_sales_goal_1 UNIQUE (user_id, pipeline_id, period_type, period_start)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_sales_goal_user_id ON crm_sales_goal (user_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_sales_goal_pipeline_id ON crm_sales_goal (pipeline_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_sequence_enrollment (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
sequence_id INTEGER NOT NULL,
|
|
contact_id INTEGER NOT NULL,
|
|
deal_id INTEGER,
|
|
current_step_number INTEGER DEFAULT 0 NOT NULL,
|
|
status INTEGER NOT NULL,
|
|
enrolled_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
|
enrolled_by_user_id TEXT NOT NULL,
|
|
completed_date TIMESTAMP WITHOUT TIME ZONE,
|
|
unenrolled_date TIMESTAMP WITHOUT TIME ZONE,
|
|
unenroll_reason VARCHAR(250),
|
|
next_step_date TIMESTAMP WITHOUT TIME ZONE,
|
|
failure_count INTEGER DEFAULT 0,
|
|
last_failure_message VARCHAR(1000),
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_sequence_enrollment_1 UNIQUE (sequence_id, contact_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_sequence_enrollment_sequence_id ON crm_sequence_enrollment (sequence_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_sequence_enrollment_contact_id ON crm_sequence_enrollment (contact_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_sequence_enrollment_deal_id ON crm_sequence_enrollment (deal_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_sequence_enrollment_enrolled_by_user_id ON crm_sequence_enrollment (enrolled_by_user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_sequence_step (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
sequence_id INTEGER NOT NULL,
|
|
step_number INTEGER NOT NULL,
|
|
step_type INTEGER NOT NULL,
|
|
delay_days INTEGER DEFAULT 0 NOT NULL,
|
|
delay_hours INTEGER DEFAULT 0,
|
|
email_template_id INTEGER,
|
|
task_subject VARCHAR(500),
|
|
task_description TEXT,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_sequence_step_1 UNIQUE (sequence_id, step_number)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_sequence_step_sequence_id ON crm_sequence_step (sequence_id);
|
|
CREATE INDEX IF NOT EXISTS ix_crm_sequence_step_email_template_id ON crm_sequence_step (email_template_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_win_loss_reason (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
type INTEGER NOT NULL,
|
|
sort_order INTEGER NOT NULL,
|
|
is_active BOOLEAN DEFAULT TRUE NOT NULL,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT uq_crm_win_loss_reason_1 UNIQUE (name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_crm_consent_record_evidence_form_submission_id ON crm_consent_record (evidence_form_submission_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_job_state (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(250) NOT NULL UNIQUE,
|
|
last_success TIMESTAMP WITHOUT TIME ZONE,
|
|
last_full_pass TIMESTAMP WITHOUT TIME ZONE
|
|
);
|