640 lines
24 KiB
MySQL
640 lines
24 KiB
MySQL
|
-- PostgreSQL Initial Schema for Autotask Sync
|
||
|
|
-- This migration creates all entity tables with audit fields, foreign keys, and sync_history table
|
||
|
|
|
||
|
|
-- Enable UUID extension if needed
|
||
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- CORE ENTITY TABLES
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
-- Companies table
|
||
|
|
CREATE TABLE IF NOT EXISTS companies (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
company_name VARCHAR(255),
|
||
|
|
company_number VARCHAR(100),
|
||
|
|
phone VARCHAR(50),
|
||
|
|
fax VARCHAR(50),
|
||
|
|
website VARCHAR(255),
|
||
|
|
address1 VARCHAR(255),
|
||
|
|
address2 VARCHAR(255),
|
||
|
|
city VARCHAR(100),
|
||
|
|
state VARCHAR(50),
|
||
|
|
postal_code VARCHAR(20),
|
||
|
|
country VARCHAR(100),
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
company_type INTEGER,
|
||
|
|
owner_resource_id BIGINT,
|
||
|
|
territory_id BIGINT,
|
||
|
|
market_segment_id BIGINT,
|
||
|
|
competitor_id BIGINT,
|
||
|
|
billing_address1 VARCHAR(255),
|
||
|
|
billing_address2 VARCHAR(255),
|
||
|
|
billing_city VARCHAR(100),
|
||
|
|
billing_state VARCHAR(50),
|
||
|
|
billing_postal_code VARCHAR(20),
|
||
|
|
billing_country VARCHAR(100),
|
||
|
|
tax_id VARCHAR(50),
|
||
|
|
tax_exempt BOOLEAN DEFAULT false,
|
||
|
|
tax_region_id BIGINT,
|
||
|
|
currency_id INTEGER,
|
||
|
|
invoice_method INTEGER,
|
||
|
|
invoice_template_id BIGINT,
|
||
|
|
quote_template_id BIGINT,
|
||
|
|
key_account_icon INTEGER,
|
||
|
|
last_activity_date TIMESTAMP,
|
||
|
|
last_tracked_modification_date_time TIMESTAMP,
|
||
|
|
api_vendor_id INTEGER,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Resources (Users) table
|
||
|
|
CREATE TABLE IF NOT EXISTS resources (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
first_name VARCHAR(100),
|
||
|
|
last_name VARCHAR(100),
|
||
|
|
email VARCHAR(255),
|
||
|
|
user_name VARCHAR(100),
|
||
|
|
title VARCHAR(100),
|
||
|
|
office_phone VARCHAR(50),
|
||
|
|
mobile_phone VARCHAR(50),
|
||
|
|
office_extension VARCHAR(20),
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
location_id BIGINT,
|
||
|
|
resource_type INTEGER,
|
||
|
|
pay_roll_identifier VARCHAR(100),
|
||
|
|
hire_date DATE,
|
||
|
|
travel_availability_pct DECIMAL(5,2),
|
||
|
|
survey_resource_rating DECIMAL(3,2),
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Contacts table
|
||
|
|
CREATE TABLE IF NOT EXISTS contacts (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
company_id BIGINT NOT NULL,
|
||
|
|
first_name VARCHAR(100),
|
||
|
|
last_name VARCHAR(100),
|
||
|
|
title VARCHAR(100),
|
||
|
|
email_address VARCHAR(255),
|
||
|
|
email_address2 VARCHAR(255),
|
||
|
|
email_address3 VARCHAR(255),
|
||
|
|
phone VARCHAR(50),
|
||
|
|
extension VARCHAR(20),
|
||
|
|
alternate_phone VARCHAR(50),
|
||
|
|
mobile_phone VARCHAR(50),
|
||
|
|
fax VARCHAR(50),
|
||
|
|
address_line VARCHAR(255),
|
||
|
|
address_line1 VARCHAR(255),
|
||
|
|
city VARCHAR(100),
|
||
|
|
state VARCHAR(50),
|
||
|
|
zip_code VARCHAR(20),
|
||
|
|
country VARCHAR(100),
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
name_prefix VARCHAR(20),
|
||
|
|
name_suffix VARCHAR(20),
|
||
|
|
facebook_url VARCHAR(255),
|
||
|
|
twitter_url VARCHAR(255),
|
||
|
|
linked_in_url VARCHAR(255),
|
||
|
|
primary_contact BOOLEAN DEFAULT false,
|
||
|
|
account_physical_location_id BIGINT,
|
||
|
|
solicitation_opt_out BOOLEAN DEFAULT false,
|
||
|
|
room_number VARCHAR(50),
|
||
|
|
last_activity_date TIMESTAMP,
|
||
|
|
last_modified_date TIMESTAMP,
|
||
|
|
api_vendor_id INTEGER,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP,
|
||
|
|
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Projects table
|
||
|
|
CREATE TABLE IF NOT EXISTS projects (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
company_id BIGINT NOT NULL,
|
||
|
|
project_name VARCHAR(255),
|
||
|
|
project_number VARCHAR(100),
|
||
|
|
description TEXT,
|
||
|
|
start_date_time TIMESTAMP,
|
||
|
|
end_date_time TIMESTAMP,
|
||
|
|
estimated_time DECIMAL(10,2),
|
||
|
|
actual_hours DECIMAL(10,2),
|
||
|
|
estimated_sale_cost DECIMAL(15,2),
|
||
|
|
labor_estimated_costs DECIMAL(15,2),
|
||
|
|
labor_estimated_revenue DECIMAL(15,2),
|
||
|
|
project_cost_estimated_margin_percentage DECIMAL(5,2),
|
||
|
|
status INTEGER,
|
||
|
|
type INTEGER,
|
||
|
|
project_lead_resource_id BIGINT,
|
||
|
|
account_executive_resource_id BIGINT,
|
||
|
|
owner_resource_id BIGINT,
|
||
|
|
creator_resource_id BIGINT,
|
||
|
|
completed_percentage DECIMAL(5,2),
|
||
|
|
completed_date_time TIMESTAMP,
|
||
|
|
duration INTEGER,
|
||
|
|
original_estimated_revenue DECIMAL(15,2),
|
||
|
|
estimated_time_cost DECIMAL(15,2),
|
||
|
|
purchase_order_number VARCHAR(100),
|
||
|
|
business_division_subdivision_id INTEGER,
|
||
|
|
line_of_business_id BIGINT,
|
||
|
|
department INTEGER,
|
||
|
|
last_activity_date_time TIMESTAMP,
|
||
|
|
last_activity_person_type INTEGER,
|
||
|
|
last_activity_resource_id BIGINT,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP,
|
||
|
|
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (project_lead_resource_id) REFERENCES resources(id) ON DELETE SET NULL,
|
||
|
|
FOREIGN KEY (owner_resource_id) REFERENCES resources(id) ON DELETE SET NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Tickets table
|
||
|
|
CREATE TABLE IF NOT EXISTS tickets (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
company_id BIGINT NOT NULL,
|
||
|
|
ticket_number VARCHAR(100),
|
||
|
|
title VARCHAR(255),
|
||
|
|
description TEXT,
|
||
|
|
status INTEGER,
|
||
|
|
priority INTEGER,
|
||
|
|
queue_id INTEGER,
|
||
|
|
issue_type INTEGER,
|
||
|
|
sub_issue_type INTEGER,
|
||
|
|
source INTEGER,
|
||
|
|
assigned_resource_id BIGINT,
|
||
|
|
assigned_resource_role_id BIGINT,
|
||
|
|
contact_id BIGINT,
|
||
|
|
account_physical_location_id BIGINT,
|
||
|
|
due_date_time TIMESTAMP,
|
||
|
|
estimated_hours DECIMAL(10,2),
|
||
|
|
completed_date TIMESTAMP,
|
||
|
|
create_date TIMESTAMP,
|
||
|
|
created_by_contact_id BIGINT,
|
||
|
|
last_activity_date TIMESTAMP,
|
||
|
|
last_customer_notification_date_time TIMESTAMP,
|
||
|
|
last_customer_visible_activity_date_time TIMESTAMP,
|
||
|
|
first_response_date_time TIMESTAMP,
|
||
|
|
resolution_plan_date_time TIMESTAMP,
|
||
|
|
resolved_date_time TIMESTAMP,
|
||
|
|
first_response_assigned_resource_id BIGINT,
|
||
|
|
first_response_initiating_resource_id BIGINT,
|
||
|
|
project_id BIGINT,
|
||
|
|
opportunity_id BIGINT,
|
||
|
|
change_approval_board INTEGER,
|
||
|
|
change_approval_status INTEGER,
|
||
|
|
change_approval_type INTEGER,
|
||
|
|
change_info_field1 VARCHAR(255),
|
||
|
|
change_info_field2 VARCHAR(255),
|
||
|
|
change_info_field3 VARCHAR(255),
|
||
|
|
change_info_field4 VARCHAR(255),
|
||
|
|
change_info_field5 VARCHAR(255),
|
||
|
|
contract_id BIGINT,
|
||
|
|
monitor_id BIGINT,
|
||
|
|
monitor_type_id INTEGER,
|
||
|
|
ticket_type INTEGER,
|
||
|
|
ticket_category INTEGER,
|
||
|
|
service_level_agreement_id INTEGER,
|
||
|
|
resolution TEXT,
|
||
|
|
purchase_order_number VARCHAR(100),
|
||
|
|
ticket_completion_date TIMESTAMP,
|
||
|
|
last_activity_person_type INTEGER,
|
||
|
|
last_activity_resource_id BIGINT,
|
||
|
|
current_service_thermometer_rating INTEGER,
|
||
|
|
previous_service_thermometer_rating INTEGER,
|
||
|
|
service_thermometer_temperature INTEGER,
|
||
|
|
api_vendor_id INTEGER,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP,
|
||
|
|
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (assigned_resource_id) REFERENCES resources(id) ON DELETE SET NULL,
|
||
|
|
FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
|
||
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Tasks table
|
||
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
title VARCHAR(255),
|
||
|
|
description TEXT,
|
||
|
|
status INTEGER,
|
||
|
|
priority INTEGER,
|
||
|
|
assigned_resource_id BIGINT,
|
||
|
|
assigned_resource_role_id BIGINT,
|
||
|
|
department_id INTEGER,
|
||
|
|
estimated_hours DECIMAL(10,2),
|
||
|
|
remaining_hours DECIMAL(10,2),
|
||
|
|
hours_to_be_scheduled DECIMAL(10,2),
|
||
|
|
start_date_time TIMESTAMP,
|
||
|
|
end_date_time TIMESTAMP,
|
||
|
|
completed_date_time TIMESTAMP,
|
||
|
|
create_date_time TIMESTAMP,
|
||
|
|
creator_resource_id BIGINT,
|
||
|
|
completed_by_resource_id BIGINT,
|
||
|
|
last_activity_date_time TIMESTAMP,
|
||
|
|
project_id BIGINT,
|
||
|
|
ticket_id BIGINT,
|
||
|
|
phase_id BIGINT,
|
||
|
|
allocation_code_id BIGINT,
|
||
|
|
task_type INTEGER,
|
||
|
|
task_is_billable BOOLEAN DEFAULT true,
|
||
|
|
task_number VARCHAR(100),
|
||
|
|
purchase_order_number VARCHAR(100),
|
||
|
|
can_client_portal_user_complete_task BOOLEAN DEFAULT false,
|
||
|
|
creator_type INTEGER,
|
||
|
|
task_category_id INTEGER,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP,
|
||
|
|
FOREIGN KEY (assigned_resource_id) REFERENCES resources(id) ON DELETE SET NULL,
|
||
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Configuration Items table
|
||
|
|
CREATE TABLE IF NOT EXISTS configuration_items (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
company_id BIGINT NOT NULL,
|
||
|
|
product_id BIGINT,
|
||
|
|
reference_title VARCHAR(255),
|
||
|
|
reference_number VARCHAR(100),
|
||
|
|
serial_number VARCHAR(100),
|
||
|
|
install_date DATE,
|
||
|
|
warranty_expiration_date DATE,
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
daily_cost DECIMAL(15,2),
|
||
|
|
hourly_cost DECIMAL(15,2),
|
||
|
|
monthly_cost DECIMAL(15,2),
|
||
|
|
per_use_cost DECIMAL(15,2),
|
||
|
|
setup_fee DECIMAL(15,2),
|
||
|
|
contact_id BIGINT,
|
||
|
|
location_id BIGINT,
|
||
|
|
vendor_id BIGINT,
|
||
|
|
installed_by_id BIGINT,
|
||
|
|
installed_by_contact_id BIGINT,
|
||
|
|
parent_configuration_item_id BIGINT,
|
||
|
|
notes TEXT,
|
||
|
|
create_date TIMESTAMP,
|
||
|
|
created_by_person_id BIGINT,
|
||
|
|
last_modified_time TIMESTAMP,
|
||
|
|
last_activity_person_type INTEGER,
|
||
|
|
impersonator_creator_resource_id BIGINT,
|
||
|
|
configuration_item_category_id BIGINT,
|
||
|
|
configuration_item_type INTEGER,
|
||
|
|
datto_availability DECIMAL(5,2),
|
||
|
|
datto_device_memory_megabytes BIGINT,
|
||
|
|
datto_drives_errors BOOLEAN,
|
||
|
|
datto_hostname VARCHAR(255),
|
||
|
|
datto_internal_ip VARCHAR(50),
|
||
|
|
datto_kernel_version_id BIGINT,
|
||
|
|
datto_last_check_in_date_time TIMESTAMP,
|
||
|
|
datto_nic_speed_kilobits_per_second BIGINT,
|
||
|
|
datto_number_of_agents INTEGER,
|
||
|
|
datto_number_of_drives INTEGER,
|
||
|
|
datto_number_of_logical_volumes INTEGER,
|
||
|
|
datto_number_of_volumes INTEGER,
|
||
|
|
datto_off_site_storage_used_bytes BIGINT,
|
||
|
|
datto_os_version_id BIGINT,
|
||
|
|
datto_percentage_used DECIMAL(5,2),
|
||
|
|
datto_protected_kilobytes BIGINT,
|
||
|
|
datto_remote_ip VARCHAR(50),
|
||
|
|
datto_serial_number VARCHAR(100),
|
||
|
|
datto_uptime_seconds BIGINT,
|
||
|
|
datto_used_kilobytes BIGINT,
|
||
|
|
datto_z_pool_percentage DECIMAL(5,2),
|
||
|
|
device_networking_id BIGINT,
|
||
|
|
last_backup_date TIMESTAMP,
|
||
|
|
last_backup_status INTEGER,
|
||
|
|
os_version_id BIGINT,
|
||
|
|
service_id BIGINT,
|
||
|
|
service_bundle_id BIGINT,
|
||
|
|
snmp_location VARCHAR(255),
|
||
|
|
snmp_name VARCHAR(255),
|
||
|
|
snmp_contact VARCHAR(255),
|
||
|
|
api_vendor_id INTEGER,
|
||
|
|
device_type VARCHAR(100),
|
||
|
|
rmm_device_uid VARCHAR(255),
|
||
|
|
rmm_device_audit_architecture_id BIGINT,
|
||
|
|
rmm_device_audit_display_adaptor_id BIGINT,
|
||
|
|
rmm_device_audit_domain_id BIGINT,
|
||
|
|
rmm_device_audit_external_ip_address VARCHAR(50),
|
||
|
|
rmm_device_audit_hostname VARCHAR(255),
|
||
|
|
rmm_device_audit_ip_address VARCHAR(50),
|
||
|
|
rmm_device_audit_mac_address VARCHAR(50),
|
||
|
|
rmm_device_audit_manufacturer_id BIGINT,
|
||
|
|
rmm_device_audit_missing_patch_count INTEGER,
|
||
|
|
rmm_device_audit_mobile_network_operator_id BIGINT,
|
||
|
|
rmm_device_audit_mobile_number VARCHAR(50),
|
||
|
|
rmm_device_audit_model_id BIGINT,
|
||
|
|
rmm_device_audit_motherboard_id BIGINT,
|
||
|
|
rmm_device_audit_operating_system_id BIGINT,
|
||
|
|
rmm_device_audit_processor_id BIGINT,
|
||
|
|
rmm_device_audit_service_pack_id BIGINT,
|
||
|
|
rmm_device_audit_snmp_contact VARCHAR(255),
|
||
|
|
rmm_device_audit_snmp_location VARCHAR(255),
|
||
|
|
rmm_device_audit_snmp_name VARCHAR(255),
|
||
|
|
rmm_device_audit_software_status_id BIGINT,
|
||
|
|
rmm_device_audit_storage_bytes BIGINT,
|
||
|
|
rmm_open_alert_count INTEGER,
|
||
|
|
rmm_device_audit_description VARCHAR(255),
|
||
|
|
rmm_device_audit_device_type_id BIGINT,
|
||
|
|
rmm_device_audit_last_user VARCHAR(255),
|
||
|
|
rmm_device_audit_memory_bytes BIGINT,
|
||
|
|
source_cost_id BIGINT,
|
||
|
|
source_cost_type INTEGER,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP,
|
||
|
|
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Contracts table
|
||
|
|
CREATE TABLE IF NOT EXISTS contracts (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
company_id BIGINT NOT NULL,
|
||
|
|
contract_name VARCHAR(255),
|
||
|
|
contract_number VARCHAR(100),
|
||
|
|
description TEXT,
|
||
|
|
start_date DATE,
|
||
|
|
end_date DATE,
|
||
|
|
time_reporting_requires_start_and_stop_times INTEGER,
|
||
|
|
service_level_agreement_id INTEGER,
|
||
|
|
contract_type INTEGER,
|
||
|
|
contract_category INTEGER,
|
||
|
|
status INTEGER,
|
||
|
|
business_division_subdivision_id INTEGER,
|
||
|
|
contact_id BIGINT,
|
||
|
|
contact_name VARCHAR(255),
|
||
|
|
billing_preference INTEGER,
|
||
|
|
purchase_order_number VARCHAR(100),
|
||
|
|
setup_fee DECIMAL(15,2),
|
||
|
|
setup_fee_allocation_code_id BIGINT,
|
||
|
|
estimated_cost DECIMAL(15,2),
|
||
|
|
estimated_hours DECIMAL(10,2),
|
||
|
|
estimated_revenue DECIMAL(15,2),
|
||
|
|
over_budget_dollar_amount DECIMAL(15,2),
|
||
|
|
over_budget_hours DECIMAL(10,2),
|
||
|
|
contract_period_type VARCHAR(50),
|
||
|
|
opportunity_id BIGINT,
|
||
|
|
renewed_contract_id BIGINT,
|
||
|
|
is_default_contract BOOLEAN DEFAULT false,
|
||
|
|
internal_currency_setup_fee DECIMAL(15,2),
|
||
|
|
internal_currency_over_budget_dollar_amount DECIMAL(15,2),
|
||
|
|
internal_currency_estimated_cost DECIMAL(15,2),
|
||
|
|
internal_currency_estimated_revenue DECIMAL(15,2),
|
||
|
|
exclusion_contract_id BIGINT,
|
||
|
|
internal_currency_monthly_revenue DECIMAL(15,2),
|
||
|
|
internal_currency_quarterly_revenue DECIMAL(15,2),
|
||
|
|
internal_currency_semi_annual_revenue DECIMAL(15,2),
|
||
|
|
internal_currency_yearly_revenue DECIMAL(15,2),
|
||
|
|
internal_currency_one_time_revenue DECIMAL(15,2),
|
||
|
|
compliance BOOLEAN,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP,
|
||
|
|
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Billing Items table
|
||
|
|
CREATE TABLE IF NOT EXISTS billing_items (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
company_id BIGINT,
|
||
|
|
product_id BIGINT,
|
||
|
|
description TEXT,
|
||
|
|
quantity DECIMAL(10,2),
|
||
|
|
rate DECIMAL(15,2),
|
||
|
|
total_amount DECIMAL(15,2),
|
||
|
|
line_discount_dollars DECIMAL(15,2),
|
||
|
|
line_discount_percent DECIMAL(5,2),
|
||
|
|
tax_category_id INTEGER,
|
||
|
|
internal_currency_line_discount_dollars DECIMAL(15,2),
|
||
|
|
allocation_code_id BIGINT,
|
||
|
|
invoice_id BIGINT,
|
||
|
|
vendor_id BIGINT,
|
||
|
|
expense_item BOOLEAN DEFAULT false,
|
||
|
|
task_id BIGINT,
|
||
|
|
ticket_id BIGINT,
|
||
|
|
project_id BIGINT,
|
||
|
|
our_cost DECIMAL(15,2),
|
||
|
|
list_price DECIMAL(15,2),
|
||
|
|
unit_cost DECIMAL(15,2),
|
||
|
|
unit_price DECIMAL(15,2),
|
||
|
|
extended_price DECIMAL(15,2),
|
||
|
|
tax_dollars DECIMAL(15,2),
|
||
|
|
internal_currency_unit_price DECIMAL(15,2),
|
||
|
|
internal_currency_total_amount DECIMAL(15,2),
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP,
|
||
|
|
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE SET NULL,
|
||
|
|
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE SET NULL,
|
||
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- PICKLIST TABLES
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
-- Statuses picklist
|
||
|
|
CREATE TABLE IF NOT EXISTS statuses (
|
||
|
|
value INTEGER PRIMARY KEY,
|
||
|
|
label VARCHAR(100) NOT NULL,
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
is_system BOOLEAN DEFAULT false,
|
||
|
|
sort_order INTEGER,
|
||
|
|
parent_value INTEGER,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Issue Types picklist
|
||
|
|
CREATE TABLE IF NOT EXISTS issue_types (
|
||
|
|
value INTEGER PRIMARY KEY,
|
||
|
|
label VARCHAR(100) NOT NULL,
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
is_system BOOLEAN DEFAULT false,
|
||
|
|
sort_order INTEGER,
|
||
|
|
parent_value INTEGER,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Sub-Issue Types picklist
|
||
|
|
CREATE TABLE IF NOT EXISTS sub_issue_types (
|
||
|
|
value INTEGER PRIMARY KEY,
|
||
|
|
label VARCHAR(100) NOT NULL,
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
is_system BOOLEAN DEFAULT false,
|
||
|
|
sort_order INTEGER,
|
||
|
|
parent_value INTEGER,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Work Types picklist
|
||
|
|
CREATE TABLE IF NOT EXISTS work_types (
|
||
|
|
value INTEGER PRIMARY KEY,
|
||
|
|
label VARCHAR(100) NOT NULL,
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
is_system BOOLEAN DEFAULT false,
|
||
|
|
sort_order INTEGER,
|
||
|
|
parent_value INTEGER,
|
||
|
|
-- Audit fields
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
is_deleted BOOLEAN DEFAULT false,
|
||
|
|
deleted_at TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- SYNC HISTORY TABLE
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sync_history (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
entity_type VARCHAR(100) NOT NULL,
|
||
|
|
sync_type VARCHAR(50) NOT NULL CHECK (sync_type IN ('full', 'incremental', 'entity-specific')),
|
||
|
|
status VARCHAR(50) NOT NULL CHECK (status IN ('started', 'in_progress', 'completed', 'failed')),
|
||
|
|
started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
completed_at TIMESTAMP,
|
||
|
|
records_added INTEGER DEFAULT 0,
|
||
|
|
records_updated INTEGER DEFAULT 0,
|
||
|
|
records_deleted INTEGER DEFAULT 0,
|
||
|
|
error_message TEXT,
|
||
|
|
triggered_by VARCHAR(255)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- INDEXES
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
-- Companies indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_companies_is_active ON companies(is_active);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_companies_is_deleted ON companies(is_deleted);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_companies_synced_at ON companies(synced_at);
|
||
|
|
|
||
|
|
-- Resources indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_resources_email ON resources(email);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_resources_is_active ON resources(is_active);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_resources_is_deleted ON resources(is_deleted);
|
||
|
|
|
||
|
|
-- Contacts indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contacts_company_id ON contacts(company_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contacts_email_address ON contacts(email_address);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contacts_is_active ON contacts(is_active);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contacts_is_deleted ON contacts(is_deleted);
|
||
|
|
|
||
|
|
-- Projects indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_projects_company_id ON projects(company_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_projects_is_deleted ON projects(is_deleted);
|
||
|
|
|
||
|
|
-- Tickets indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tickets_company_id ON tickets(company_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tickets_assigned_resource_id ON tickets(assigned_resource_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tickets_status ON tickets(status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tickets_contact_id ON tickets(contact_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tickets_is_deleted ON tickets(is_deleted);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tickets_create_date ON tickets(create_date);
|
||
|
|
|
||
|
|
-- Tasks indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_assigned_resource_id ON tasks(assigned_resource_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_project_id ON tasks(project_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_ticket_id ON tasks(ticket_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_is_deleted ON tasks(is_deleted);
|
||
|
|
|
||
|
|
-- Configuration Items indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_config_items_company_id ON configuration_items(company_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_config_items_contact_id ON configuration_items(contact_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_config_items_serial_number ON configuration_items(serial_number);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_config_items_is_active ON configuration_items(is_active);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_config_items_is_deleted ON configuration_items(is_deleted);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_config_items_rmm_device_uid ON configuration_items(rmm_device_uid);
|
||
|
|
|
||
|
|
-- Contracts indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contracts_company_id ON contracts(company_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contracts_status ON contracts(status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contracts_is_deleted ON contracts(is_deleted);
|
||
|
|
|
||
|
|
-- Billing Items indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_billing_items_company_id ON billing_items(company_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_billing_items_task_id ON billing_items(task_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_billing_items_ticket_id ON billing_items(ticket_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_billing_items_project_id ON billing_items(project_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_billing_items_is_deleted ON billing_items(is_deleted);
|
||
|
|
|
||
|
|
-- Sync History indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sync_history_entity_type ON sync_history(entity_type);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sync_history_status ON sync_history(status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sync_history_started_at ON sync_history(started_at DESC);
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- COMMENTS
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
COMMENT ON TABLE companies IS 'Autotask companies/accounts';
|
||
|
|
COMMENT ON TABLE resources IS 'Autotask resources (users/technicians)';
|
||
|
|
COMMENT ON TABLE contacts IS 'Company contacts';
|
||
|
|
COMMENT ON TABLE projects IS 'Autotask projects';
|
||
|
|
COMMENT ON TABLE tickets IS 'Autotask service tickets';
|
||
|
|
COMMENT ON TABLE tasks IS 'Autotask tasks';
|
||
|
|
COMMENT ON TABLE configuration_items IS 'Configuration items (devices/assets)';
|
||
|
|
COMMENT ON TABLE contracts IS 'Service contracts';
|
||
|
|
COMMENT ON TABLE billing_items IS 'Billing/invoice line items';
|
||
|
|
COMMENT ON TABLE sync_history IS 'Tracks all sync operations from Autotask';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN companies.is_deleted IS 'Soft delete flag - true if deleted in Autotask';
|
||
|
|
COMMENT ON COLUMN companies.synced_at IS 'Last time this record was synced from Autotask';
|
||
|
|
COMMENT ON COLUMN sync_history.sync_type IS 'Type of sync: full, incremental, or entity-specific';
|
||
|
|
COMMENT ON COLUMN sync_history.status IS 'Current status: started, in_progress, completed, or failed';
|