-- Migration 005: Estimate-to-Close Funnel + Jobber Connector models
-- P2: remodeling sector gap plan
-- =============================================================================
-- Estimate-to-Close Funnel
-- =============================================================================
CREATE TABLE IF NOT EXISTS estimates (
id VARCHAR(36) PRIMARY KEY,
company_id VARCHAR(36) NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
source VARCHAR(50) DEFAULT 'manual' CHECK (source IN ('servicetitan', 'jobber', 'hubspot', 'angi', 'manual')),
external_id VARCHAR(128),
connector_id VARCHAR(36),
prospect_name VARCHAR(255) DEFAULT '',
prospect_email VARCHAR(255) DEFAULT '',
prospect_phone VARCHAR(50) DEFAULT '',
address VARCHAR(500) DEFAULT '',
city VARCHAR(100) DEFAULT '',
state VARCHAR(50) DEFAULT '',
zip_code VARCHAR(20) DEFAULT '',
project_type VARCHAR(100) DEFAULT '',
description TEXT DEFAULT '',
estimate_amount FLOAT,
estimated_cost FLOAT,
estimated_margin FLOAT,
stage VARCHAR(50) DEFAULT 'lead_received' CHECK (stage IN ('lead_received', 'contacted', 'estimate_delivered', 'estimate_accepted', 'estimate_rejected', 'won', 'lost', 'expired')),
lost_reason VARCHAR(200) DEFAULT '',
lead_received_at DATETIME,
contacted_at DATETIME,
estimate_delivered_at DATETIME,
estimate_accepted_at DATETIME,
estimate_rejected_at DATETIME,
closed_at DATETIME,
angi_lead_id VARCHAR(36) REFERENCES angi_leads(id) ON DELETE SET NULL,
crm_deal_id VARCHAR(36) REFERENCES crm_deals(id) ON DELETE SET NULL,
internal_notes TEXT DEFAULT '',
created_at DATETIME,
updated_at DATETIME
);
CREATE INDEX IF NOT EXISTS idx_estimates_company_id ON estimates(company_id);
CREATE INDEX IF NOT EXISTS idx_estimates_external_id ON estimates(external_id);
CREATE INDEX IF NOT EXISTS idx_estimates_connector_id ON estimates(connector_id);
CREATE INDEX IF NOT EXISTS idx_estimates_stage ON estimates(stage);
-- =============================================================================
-- Jobber connector models
-- =============================================================================
CREATE TABLE IF NOT EXISTS jobber_clients (
id VARCHAR(36) PRIMARY KEY,
company_id VARCHAR(36) NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
external_id VARCHAR(128) NOT NULL,
first_name VARCHAR(255) DEFAULT '',
last_name VARCHAR(255) DEFAULT '',
email VARCHAR(255) DEFAULT '',
phone VARCHAR(50) DEFAULT '',
company_name VARCHAR(255) DEFAULT '',
address_line1 VARCHAR(500) DEFAULT '',
address_line2 VARCHAR(500) DEFAULT '',
city VARCHAR(100) DEFAULT '',
state VARCHAR(100) DEFAULT '',
postal_code VARCHAR(20) DEFAULT '',
country VARCHAR(100) DEFAULT 'US',
jobber_timezone VARCHAR(50) DEFAULT '',
jobber_language VARCHAR(10) DEFAULT 'en',
status VARCHAR(20) DEFAULT 'active',
properties_json JSON DEFAULT '{}',
created_at DATETIME,
updated_at DATETIME,
synced_at DATETIME,
UNIQUE(company_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_jobber_clients_company_id ON jobber_clients(company_id);
CREATE INDEX IF NOT EXISTS idx_jobber_clients_external_id ON jobber_clients(external_id);
CREATE TABLE IF NOT EXISTS jobber_quotes (
id VARCHAR(36) PRIMARY KEY,
company_id VARCHAR(36) NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
external_id VARCHAR(128) NOT NULL,
client_id VARCHAR(36) REFERENCES jobber_clients(id) ON DELETE SET NULL,
name VARCHAR(255) DEFAULT '',
description TEXT DEFAULT '',
status VARCHAR(50) DEFAULT 'draft',
amount FLOAT,
currency VARCHAR(3) DEFAULT 'USD',
tax_amount FLOAT,
discount_amount FLOAT,
sent_at DATETIME,
accepted_at DATETIME,
rejected_at DATETIME,
expires_at DATETIME,
estimate_id VARCHAR(36) REFERENCES estimates(id) ON DELETE SET NULL,
line_items_json JSON DEFAULT '[]',
properties_json JSON DEFAULT '{}',
created_at DATETIME,
updated_at DATETIME,
synced_at DATETIME,
UNIQUE(company_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_jobber_quotes_company_id ON jobber_quotes(company_id);
CREATE INDEX IF NOT EXISTS idx_jobber_quotes_external_id ON jobber_quotes(external_id);
CREATE INDEX IF NOT EXISTS idx_jobber_quotes_client_id ON jobber_quotes(client_id);
CREATE TABLE IF NOT EXISTS jobber_jobs (
id VARCHAR(36) PRIMARY KEY,
company_id VARCHAR(36) NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
external_id VARCHAR(128) NOT NULL,
client_id VARCHAR(36) REFERENCES jobber_clients(id) ON DELETE SET NULL,
name VARCHAR(255) DEFAULT '',
description TEXT DEFAULT '',
status VARCHAR(50) DEFAULT 'draft',
project_type VARCHAR(100) DEFAULT '',
estimated_revenue FLOAT,
actual_revenue FLOAT,
estimated_cost FLOAT,
actual_cost FLOAT,
start_date DATETIME,
end_date DATETIME,
estimated_end_date DATETIME,
address_line1 VARCHAR(500) DEFAULT '',
address_line2 VARCHAR(500) DEFAULT '',
city VARCHAR(100) DEFAULT '',
state VARCHAR(100) DEFAULT '',
postal_code VARCHAR(20) DEFAULT '',
estimate_id VARCHAR(36) REFERENCES estimates(id) ON DELETE SET NULL,
quote_id VARCHAR(36) REFERENCES jobber_quotes(id) ON DELETE SET NULL,
properties_json JSON DEFAULT '{}',
created_at DATETIME,
updated_at DATETIME,
synced_at DATETIME,
UNIQUE(company_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_jobber_jobs_company_id ON jobber_jobs(company_id);
CREATE INDEX IF NOT EXISTS idx_jobber_jobs_external_id ON jobber_jobs(external_id);
CREATE INDEX IF NOT EXISTS idx_jobber_jobs_client_id ON jobber_jobs(client_id);
CREATE TABLE IF NOT EXISTS jobber_invoices (
id VARCHAR(36) PRIMARY KEY,
company_id VARCHAR(36) NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
external_id VARCHAR(128) NOT NULL,
client_id VARCHAR(36) REFERENCES jobber_clients(id) ON DELETE SET NULL,
job_id VARCHAR(36) REFERENCES jobber_jobs(id) ON DELETE SET NULL,
invoice_number VARCHAR(100) DEFAULT '',
status VARCHAR(50) DEFAULT 'draft',
amount FLOAT,
currency VARCHAR(3) DEFAULT 'USD',
tax_amount FLOAT,
paid_amount FLOAT DEFAULT 0.0,
issued_at DATETIME,
due_date DATETIME,
paid_at DATETIME,
line_items_json JSON DEFAULT '[]',
properties_json JSON DEFAULT '{}',
created_at DATETIME,
updated_at DATETIME,
synced_at DATETIME,
UNIQUE(company_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_jobber_invoices_company_id ON jobber_invoices(company_id);
CREATE INDEX IF NOT EXISTS idx_jobber_invoices_external_id ON jobber_invoices(external_id);
CREATE INDEX IF NOT EXISTS idx_jobber_invoices_client_id ON jobber_invoices(client_id);
CREATE INDEX IF NOT EXISTS idx_jobber_invoices_job_id ON jobber_invoices(job_id);