-- ============================================================ -- Retirement Decision Engine — CostusWorx © 2026 -- retireuk.costusworx.co.za (UK edition) -- ============================================================ SET NAMES utf8mb4; SET time_zone = '+00:00'; -- UTC (UK standard; BST handled at application layer) -- ── Plans (prices in GBP) ──────────────────────────────────── CREATE TABLE plans ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, -- Solo, Practice, Enterprise max_advisers SMALLINT NOT NULL DEFAULT 1, max_clients SMALLINT NOT NULL DEFAULT 25, max_runs_month SMALLINT NOT NULL DEFAULT 100, has_white_label TINYINT(1) NOT NULL DEFAULT 0, has_api TINYINT(1) NOT NULL DEFAULT 0, has_pdf TINYINT(1) NOT NULL DEFAULT 1, has_csv TINYINT(1) NOT NULL DEFAULT 0, price_gbp DECIMAL(9,2) NOT NULL DEFAULT 0.00, -- GBP (was price_zar) created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO plans (name, max_advisers, max_clients, max_runs_month, has_white_label, has_api, has_pdf, has_csv, price_gbp) VALUES ('Free', 1, 5, 10, 0, 0, 0, 0, 0.00), ('Solo', 1, 25, 150, 0, 0, 1, 0, 29.00), ('Practice', 5, 200, 750, 0, 0, 1, 1, 99.00), ('Enterprise', 999, 999, 999, 1, 1, 1, 1, 499.00); -- ── Tenants ────────────────────────────────────────────────── CREATE TABLE tenants ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, subdomain VARCHAR(60) NOT NULL UNIQUE, -- mycompany.retireuk.costusworx.co.za plan_id TINYINT UNSIGNED NOT NULL DEFAULT 1, status ENUM('active','suspended','cancelled') NOT NULL DEFAULT 'active', logo_path VARCHAR(255) DEFAULT NULL, primary_color VARCHAR(7) DEFAULT '#1B4F72', -- hex for white-label theming created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (plan_id) REFERENCES plans(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Subscriptions ───────────────────────────────────────────── CREATE TABLE subscriptions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL, plan_id TINYINT UNSIGNED NOT NULL, status ENUM('active','past_due','cancelled','trialling') NOT NULL DEFAULT 'trialling', started_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, stripe_sub_id VARCHAR(100) DEFAULT NULL, -- Stripe (replaces PayFast for UK) created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tenant_id) REFERENCES tenants(id), FOREIGN KEY (plan_id) REFERENCES plans(id), INDEX idx_tenant (tenant_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Credits (pay-per-report) ────────────────────────────────── CREATE TABLE credits ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL UNIQUE, balance SMALLINT NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (tenant_id) REFERENCES tenants(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Users ───────────────────────────────────────────────────── CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NULL, -- NULL for superadmin (no tenant context) email VARCHAR(150) NOT NULL, password_hash VARCHAR(255) NOT NULL, name VARCHAR(120) NOT NULL, role ENUM('admin','adviser','viewer','superadmin') NOT NULL DEFAULT 'adviser', status ENUM('active','suspended') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_tenant_email (tenant_id, email), FOREIGN KEY (tenant_id) REFERENCES tenants(id), INDEX idx_tenant (tenant_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Clients (PII encrypted at application layer — UK GDPR Art.32) ──────────── CREATE TABLE clients ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL, adviser_id INT UNSIGNED NOT NULL, name_enc TEXT NOT NULL, -- AES-256-GCM encrypted ni_number_enc TEXT DEFAULT NULL, -- National Insurance number (replaces SA ID) age TINYINT UNSIGNED NOT NULL, life_expectancy TINYINT UNSIGNED NOT NULL DEFAULT 87, -- UK ONS 2024 life expectancy ukgdpr_consent TINYINT(1) NOT NULL DEFAULT 0, -- UK GDPR (replaces POPI) consent_date DATETIME DEFAULT NULL, status ENUM('active','archived') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tenant_id) REFERENCES tenants(id), FOREIGN KEY (adviser_id) REFERENCES users(id), INDEX idx_tenant (tenant_id), INDEX idx_adviser (adviser_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── UK GDPR Consent log (replaces popi_consents) ───────────── CREATE TABLE gdpr_consents ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL, client_id INT UNSIGNED NOT NULL, consent_type VARCHAR(60) NOT NULL, -- 'simulation_data','pdf_report','marketing' lawful_basis VARCHAR(40) NOT NULL DEFAULT 'consent', -- GDPR lawful basis given_at DATETIME DEFAULT NULL, withdrawn_at DATETIME DEFAULT NULL, created_by INT UNSIGNED NOT NULL, FOREIGN KEY (tenant_id) REFERENCES tenants(id), FOREIGN KEY (client_id) REFERENCES clients(id), FOREIGN KEY (created_by) REFERENCES users(id), INDEX idx_client (client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Scenarios ───────────────────────────────────────────────── CREATE TABLE scenarios ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL, client_id INT UNSIGNED NOT NULL, adviser_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, risk_profile ENUM('conservative','moderate','aggressive') NOT NULL DEFAULT 'moderate', inflation_rate DECIMAL(5,4) NOT NULL DEFAULT 0.0250, -- 2.5% UK long-run CPI target withdrawal_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, -- annual, GBP simulation_years TINYINT UNSIGNED NOT NULL DEFAULT 25, retirement_date DATE NULL, -- NULL/today/past = drawdown starts now monthly_contribution DECIMAL(12,2) NOT NULL DEFAULT 0.00, -- GBP/month during accumulation contribution_escalation DECIMAL(5,4) NOT NULL DEFAULT 0.0000, -- annual, e.g. 0.0250 = 2.5% created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tenant_id) REFERENCES tenants(id), FOREIGN KEY (client_id) REFERENCES clients(id), FOREIGN KEY (adviser_id) REFERENCES users(id), INDEX idx_tenant (tenant_id), INDEX idx_client (client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Assets ──────────────────────────────────────────────────── -- UK asset types added; SA legacy types retained for backward-compat. CREATE TABLE assets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL, scenario_id INT UNSIGNED NOT NULL, asset_type ENUM( -- UK types 'sipp', 'isa', 'workplace_pension', 'state_pension', -- generic (both regions) 'cash', 'unit_trust', 'fixed_deposit', 'offshore', -- SA legacy (backward-compat only) 'living_annuity' ) NOT NULL, name VARCHAR(120) NOT NULL, value DECIMAL(14,2) NOT NULL DEFAULT 0.00, -- GBP equity_weight DECIMAL(5,4) NOT NULL DEFAULT 0.0000, bond_weight DECIMAL(5,4) NOT NULL DEFAULT 0.0000, cash_weight DECIMAL(5,4) NOT NULL DEFAULT 1.0000, drawdown_rate DECIMAL(5,4) NOT NULL DEFAULT 0.0000, -- annual proportion is_drawdown_source TINYINT(1) NOT NULL DEFAULT 0, is_locked TINYINT(1) NOT NULL DEFAULT 0, priority_order TINYINT UNSIGNED NOT NULL DEFAULT 1, -- UK fund identifiers (replaces jse_code with SEDOL) sedol VARCHAR(7) DEFAULT NULL COMMENT 'UK Stock Exchange Daily Official List code', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tenant_id) REFERENCES tenants(id), FOREIGN KEY (scenario_id) REFERENCES scenarios(id) ON DELETE CASCADE, INDEX idx_scenario (scenario_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── UK Market Index Registry ─────────────────────────────────── -- FTSE Russell / BoE / ONS indices (replaces JSE/SARB/StatsSA). -- See uk_market_data.sql for the actual historical return rows. CREATE TABLE market_index_registry ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, asset_class ENUM('equity','bonds','cash','inflation') NOT NULL UNIQUE, index_code VARCHAR(20) NOT NULL, index_name VARCHAR(120) NOT NULL, provider VARCHAR(80) NOT NULL, provider_url VARCHAR(255) DEFAULT NULL, notes TEXT DEFAULT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- UK default benchmarks (see uk_market_data.sql for full data population) INSERT INTO market_index_registry (asset_class, index_code, index_name, provider, provider_url, notes) VALUES ('equity', 'FTAS', 'FTSE All-Share Total Return Index', 'FTSE Russell', 'https://www.ftserussell.com', 'Broad UK market ~600 stocks. Use FTSE100/UKX for large-cap.'), ('bonds', 'FTSE-GILTS', 'FTSE UK Gilts All Stocks Total Return', 'FTSE Russell', 'https://www.ftserussell.com', 'UK government gilts. Standard fixed-income benchmark.'), ('cash', 'SONIA', 'Sterling Overnight Index Average', 'Bank of England', 'https://www.bankofengland.co.uk', 'UK overnight rate proxy. Replaced LIBOR 2021.'), ('inflation', 'CPI-ONS', 'Consumer Price Index (All Items)', 'ONS', 'https://www.ons.gov.uk', 'UK CPI published monthly by ONS. Triple-lock proxy for State Pension modelling.'); -- ── Market Data ─────────────────────────────────────────────── -- Populated by uk_market_data.sql. CREATE TABLE market_data ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, year YEAR NOT NULL UNIQUE, equity_return DECIMAL(7,4) NOT NULL COMMENT 'FTSE All-Share TR', bond_return DECIMAL(7,4) NOT NULL COMMENT 'FTSE Gilts All Stocks TR', cash_return DECIMAL(7,4) NOT NULL COMMENT 'BoE Base Rate / SONIA', inflation DECIMAL(7,4) NOT NULL COMMENT 'ONS CPI annual', equity_index VARCHAR(20) NOT NULL DEFAULT 'FTAS', bond_index VARCHAR(20) NOT NULL DEFAULT 'FTSE-GILTS', cash_index VARCHAR(20) NOT NULL DEFAULT 'SONIA', data_source VARCHAR(120) NOT NULL DEFAULT 'FTSE Russell / BoE / ONS', verified TINYINT(1) NOT NULL DEFAULT 0, INDEX idx_year (year) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Simulation Runs ─────────────────────────────────────────── CREATE TABLE simulation_runs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL, scenario_id INT UNSIGNED NOT NULL, adviser_id INT UNSIGNED NOT NULL, run_count SMALLINT NOT NULL DEFAULT 500, success_rate DECIMAL(5,2) NOT NULL, median_years_remaining TINYINT NOT NULL, worst_depletion_year SMALLINT DEFAULT NULL, median_path_json MEDIUMTEXT NOT NULL, worst_path_json MEDIUMTEXT NOT NULL, best_path_json MEDIUMTEXT NOT NULL, confidence_bands_json MEDIUMTEXT NOT NULL, credits_charged TINYINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tenant_id) REFERENCES tenants(id), FOREIGN KEY (scenario_id) REFERENCES scenarios(id), FOREIGN KEY (adviser_id) REFERENCES users(id), INDEX idx_tenant (tenant_id), INDEX idx_scenario (scenario_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Audit Log (UK GDPR / FCA requirement) ──────────────────── CREATE TABLE audit_log ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED DEFAULT NULL, action VARCHAR(80) NOT NULL, entity_type VARCHAR(50) DEFAULT NULL, entity_id INT UNSIGNED DEFAULT NULL, ip_address VARCHAR(45) DEFAULT NULL, user_agent VARCHAR(255) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_tenant (tenant_id), INDEX idx_user (user_id), INDEX idx_action (action), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Usage tracking (quota enforcement) ─────────────────────── CREATE TABLE usage_monthly ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL, month CHAR(7) NOT NULL, run_count SMALLINT NOT NULL DEFAULT 0, UNIQUE KEY uq_tenant_month (tenant_id, month), FOREIGN KEY (tenant_id) REFERENCES tenants(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Run uk_tax_2025.sql next to seed HMRC brackets ─────────── -- ── Run uk_state_pension_schema.sql for State Pension tables ── -- ── Run uk_market_data.sql for FTSE/BoE/ONS historical data ──