9ff9ec27e9
- migrations/001_initial.sql: full schema with 12 tables, triggers, indexes - migrate.py: idempotent migration runner with version tracking - README.md: deploy instructions, architecture overview - .gitignore: exclude migration tracking data
246 lines
9.7 KiB
PL/PgSQL
246 lines
9.7 KiB
PL/PgSQL
-- ============================================================================
|
|
-- Jetour BI Dashboard — Initial Schema
|
|
-- Creates all tables, triggers, indexes, and default settings
|
|
-- ============================================================================
|
|
|
|
BEGIN;
|
|
|
|
-- ── Auth ───────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS dashboard_users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(100) NOT NULL UNIQUE,
|
|
email VARCHAR(200),
|
|
password_hash TEXT NOT NULL,
|
|
role VARCHAR(20) DEFAULT 'viewer',
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
last_login TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS password_reset_tokens (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INT NOT NULL REFERENCES dashboard_users(id) ON DELETE CASCADE,
|
|
token VARCHAR(200) NOT NULL UNIQUE,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
used BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_reset_token ON password_reset_tokens(token) WHERE NOT used;
|
|
|
|
-- ── BI Data Tables (populated by CMS BI via n8n) ───────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS prospects (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
prospect_id BIGINT NOT NULL,
|
|
cms_prospect_id INTEGER,
|
|
name VARCHAR(100),
|
|
surname VARCHAR(100),
|
|
id_number VARCHAR(50),
|
|
cell_number VARCHAR(50),
|
|
email_addr VARCHAR(200),
|
|
prospect_status VARCHAR(50),
|
|
sales_person VARCHAR(150),
|
|
source_of_initial_contact VARCHAR(100),
|
|
referral_source VARCHAR(150),
|
|
lost_reason TEXT,
|
|
dt_prospect_created TIMESTAMPTZ,
|
|
dt_updated TIMESTAMPTZ,
|
|
export_guid UUID,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_prospects_status ON prospects(prospect_status);
|
|
CREATE INDEX IF NOT EXISTS idx_prospects_created ON prospects(dt_prospect_created);
|
|
CREATE INDEX IF NOT EXISTS idx_prospects_sales ON prospects(sales_person);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_prospects_cms ON prospects(cms_prospect_id) WHERE cms_prospect_id IS NOT NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS prospect_otps (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
prospect_id BIGINT REFERENCES prospects(prospect_id),
|
|
cms_prospect_id INTEGER,
|
|
mm_code VARCHAR(50),
|
|
make VARCHAR(100),
|
|
model VARCHAR(200),
|
|
model_range VARCHAR(100),
|
|
model_year INTEGER,
|
|
colour VARCHAR(100),
|
|
vin VARCHAR(50),
|
|
stock_no VARCHAR(50),
|
|
amount NUMERIC(18,2),
|
|
otp_new_used VARCHAR(20),
|
|
dt_otp_created TIMESTAMPTZ,
|
|
dt_sold TIMESTAMPTZ,
|
|
dt_invoiced TIMESTAMPTZ,
|
|
export_guid UUID,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_otps_prospect ON prospect_otps(prospect_id);
|
|
CREATE INDEX IF NOT EXISTS idx_otps_model ON prospect_otps(model);
|
|
CREATE INDEX IF NOT EXISTS idx_otps_range ON prospect_otps(model_range);
|
|
CREATE INDEX IF NOT EXISTS idx_otps_created ON prospect_otps(dt_otp_created);
|
|
CREATE INDEX IF NOT EXISTS idx_otps_invoiced ON prospect_otps(dt_invoiced);
|
|
CREATE INDEX IF NOT EXISTS idx_otps_stock ON prospect_otps(stock_no);
|
|
|
|
CREATE TABLE IF NOT EXISTS status_logs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
prospect_id BIGINT REFERENCES prospects(prospect_id),
|
|
old_status VARCHAR(50),
|
|
new_status VARCHAR(50),
|
|
changed_by VARCHAR(100),
|
|
dt_changed TIMESTAMPTZ DEFAULT NOW(),
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_slogs_prospect ON status_logs(prospect_id);
|
|
CREATE INDEX IF NOT EXISTS idx_slogs_date ON status_logs(dt_changed);
|
|
|
|
CREATE TABLE IF NOT EXISTS change_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
table_name VARCHAR(100) NOT NULL,
|
|
record_id BIGINT NOT NULL,
|
|
field_name VARCHAR(100) NOT NULL,
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
changed_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_clog_table ON change_log(table_name, record_id);
|
|
CREATE INDEX IF NOT EXISTS idx_clog_date ON change_log(changed_at);
|
|
|
|
-- Change tracking trigger function
|
|
CREATE OR REPLACE FUNCTION track_changes() RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
col_name TEXT;
|
|
old_val TEXT;
|
|
new_val TEXT;
|
|
BEGIN
|
|
FOR col_name IN
|
|
SELECT column_name FROM information_schema.columns
|
|
WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_TABLE_NAME
|
|
AND column_name NOT IN ('id', 'created_at', 'updated_at', 'export_guid')
|
|
LOOP
|
|
EXECUTE format('SELECT ($1).%I::text, ($2).%I::text', col_name, col_name)
|
|
INTO old_val, new_val USING OLD, NEW;
|
|
IF old_val IS DISTINCT FROM new_val THEN
|
|
INSERT INTO change_log (table_name, record_id, field_name, old_value, new_value)
|
|
VALUES (TG_TABLE_NAME, NEW.id, col_name, old_val, new_val);
|
|
END IF;
|
|
END LOOP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_prospects_changes ON prospects;
|
|
CREATE TRIGGER trg_prospects_changes AFTER UPDATE ON prospects
|
|
FOR EACH ROW EXECUTE FUNCTION track_changes();
|
|
|
|
DROP TRIGGER IF EXISTS trg_otps_changes ON prospect_otps;
|
|
CREATE TRIGGER trg_otps_changes AFTER UPDATE ON prospect_otps
|
|
FOR EACH ROW EXECUTE FUNCTION track_changes();
|
|
|
|
-- ── Appointments ───────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS appointments (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
prospect_id BIGINT REFERENCES prospects(prospect_id),
|
|
cms_prospect_id INTEGER,
|
|
dt_scheduled TIMESTAMPTZ,
|
|
appointment_type VARCHAR(100),
|
|
appointment_status VARCHAR(50),
|
|
confirmed BOOLEAN,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_appt_prospect ON appointments(prospect_id);
|
|
CREATE INDEX IF NOT EXISTS idx_appt_type ON appointments(appointment_type);
|
|
|
|
-- ── Leads ──────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS leads (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
lead_id INTEGER,
|
|
prospect_id BIGINT REFERENCES prospects(prospect_id),
|
|
cms_prospect_id INTEGER,
|
|
name VARCHAR(100),
|
|
surname VARCHAR(100),
|
|
cell_number VARCHAR(50),
|
|
email_addr VARCHAR(200),
|
|
prospect_status VARCHAR(50),
|
|
lead_source VARCHAR(50),
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ── Report Snapshots (pre-computed aggregations) ───────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS report_daily_kpis (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
report_date DATE NOT NULL UNIQUE,
|
|
total_prospects INT DEFAULT 0,
|
|
new_prospects INT DEFAULT 0,
|
|
total_otps INT DEFAULT 0,
|
|
new_otps INT DEFAULT 0,
|
|
sold_count INT DEFAULT 0,
|
|
pipeline_value NUMERIC(18,2) DEFAULT 0,
|
|
avg_otp_value NUMERIC(18,2) DEFAULT 0,
|
|
pending_count INT DEFAULT 0,
|
|
finance_count INT DEFAULT 0,
|
|
lost_count INT DEFAULT 0,
|
|
conversion_rate NUMERIC(5,2) DEFAULT 0,
|
|
generated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS report_model_performance (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
report_date DATE NOT NULL,
|
|
model_group VARCHAR(100) NOT NULL,
|
|
model VARCHAR(200) NOT NULL,
|
|
color VARCHAR(100),
|
|
otp_type VARCHAR(20),
|
|
otp_count INT DEFAULT 0,
|
|
total_amount NUMERIC(18,2) DEFAULT 0,
|
|
avg_amount NUMERIC(18,2) DEFAULT 0,
|
|
sold_count INT DEFAULT 0,
|
|
generated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(report_date, model_group, model, color, otp_type)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS report_salesperson_performance (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
report_date DATE NOT NULL,
|
|
sales_person VARCHAR(150) NOT NULL,
|
|
total_prospects INT DEFAULT 0,
|
|
otp_count INT DEFAULT 0,
|
|
sold_count INT DEFAULT 0,
|
|
finance_count INT DEFAULT 0,
|
|
lost_count INT DEFAULT 0,
|
|
total_pipeline_value NUMERIC(18,2) DEFAULT 0,
|
|
generated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(report_date, sales_person)
|
|
);
|
|
|
|
-- ── App Settings & Recipients ──────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS report_recipients (
|
|
id SERIAL PRIMARY KEY,
|
|
report_type VARCHAR(20) NOT NULL,
|
|
email VARCHAR(200) NOT NULL,
|
|
name VARCHAR(100),
|
|
active BOOLEAN DEFAULT true,
|
|
added_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(report_type, email)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS app_settings (
|
|
key VARCHAR(100) PRIMARY KEY,
|
|
value TEXT,
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ── Default settings ───────────────────────────────────────────────────────
|
|
|
|
INSERT INTO app_settings (key, value) VALUES
|
|
('smtp_port', '587'),
|
|
('smtp_use_tls', 'true')
|
|
ON CONFLICT (key) DO NOTHING;
|
|
|
|
COMMIT;
|