# data-model.md ## Core Business Objects | Object | Owner | Purpose | |---|---|---| | **Tenant** | Platform | An adviser firm. Holds plan + logo. | | **User** | Tenant | An adviser within a firm. Roles: admin/adviser/support. | | **Client** | Tenant | The end customer being planned for. PII encrypted (AES-256-GCM). | | **Scenario** | Client | A what-if retirement plan. Holds 1..N assets. | | **Asset** | Scenario | A holding (cash, OEIC, SIPP, ISA, workplace pension, state pension). Optionally linked to a Fund. | | **Fund** | Platform | Investable security (UK OEICs, ETFs, etc.). Verified provenance flag. | | **FundPerformance** | Fund | Per-year annual return history. | | **SimulationRun** | Scenario | Immutable record of a Monte Carlo execution. | | **IngestRun** | Tenant | Audit record of a fund/performance CSV import. | | **TaxBracket** | Platform/Tenant | HMRC Income Tax bracket for a tax year. Global default + per-tenant override. | | **AuditLog** | Tenant | Append-only record of sensitive actions (UK GDPR Art.5(2) accountability). | --- ## MySQL Tables ### Identity & Tenancy ``` tenants (id, name, plan, logo_path, gdpr_dpo_email, created_at) plans (id, name, price_gbp, price_zar, max_runs_month, max_clients, csv, pdf, ...) users (id, tenant_id, email, password_hash, role, name, created_at) sessions (id, user_id, expires_at, payload) ``` **`plans` column notes:** - `price_gbp` — GBP monthly price; primary billing column. - `price_zar` — retained for backward compatibility with migrated data. All queries use `COALESCE(price_gbp, price_zar, 0) AS price_gbp`. ### Clients & Scenarios ``` clients (id, tenant_id, adviser_id, name_enc, age, life_expectancy, ukgdpr_consent, popi_consent, consent_date, ni_number_enc, id_number_enc, created_at) scenarios (id, tenant_id, client_id, adviser_id, name, risk_profile, inflation_rate, withdrawal_amount, simulation_years, created_at) assets (id, tenant_id, scenario_id, asset_type, fund_id, isin, sedol, jse_code, name, value, equity_weight, bond_weight, cash_weight, drawdown_rate, is_drawdown_source, is_locked, priority_order) ``` **`clients` column notes:** - `ukgdpr_consent` — UK GDPR Art.6 lawful basis (replaces `popi_consent`). Both columns retained for migration compat; app reads `COALESCE(ukgdpr_consent, popi_consent)`. - `ni_number_enc` — AES-256-GCM encrypted National Insurance number (UK GDPR Art.32 PII). Replaces `id_number_enc` (SA ID number). Both columns present for migration compat. - `life_expectancy` — default 87 (ONS UK average life expectancy). **`assets.asset_type` values:** - `cash` — cash / money market - `unit_trust` — OEIC or unit trust - `sipp` — SIPP drawdown (`TAXABLE_FRACTION = 0.75`; 25% PCLS tax-free) - `isa` — Stocks & Shares ISA (`TAXABLE_FRACTION = 0.0`; fully tax-free) - `workplace_pension` — DC workplace pension (`TAXABLE_FRACTION = 0.75`) - `state_pension` — UK State Pension income stream (not a capital pot; see below) **State Pension asset note:** `state_pension` assets hold the *annual income amount* in `value`, not a capital pot. `withdraw()` always returns 0.0. The simulation engine subtracts `annualIncome()` from the annual target before drawing from the portfolio. `applyReturn()` inflates `value` by the ONS CPI rate each year (triple-lock proxy). ### Funds ``` funds (id, isin, sedol, jse_code, name, fund_manager, asisa_category, fund_type, benchmark_index, ter, risk_rating, inception_date, is_active, is_verified, is_custom, source, last_synced_at, data_hash, pdf_filename, pdf_confidence_score, pdf_uploaded_at, pdf_last_verified, alloc_equity_local, alloc_equity_intl, alloc_bonds, alloc_cash, alloc_resources, alloc_financials, alloc_unit_trust) fund_performance (id, fund_id, year, annual_return, source, verified) fund_ingest_runs (id, source, app_version, mode, dry_run, log_file, status, counts_json, started_at, finished_at, error) fund_types (id, code, label, sort_order) ``` **`funds` column notes:** - `sedol` — UK SEDOL (7-character stock identifier). Primary UK identifier. - `jse_code` — retained for backward compatibility with migrated data. All reads use `COALESCE(sedol, jse_code) AS sedol`; writes populate both columns. - `asisa_category` — column name retained for compat; stores IA sector classification (e.g. "IA Global", "IA UK All Companies") for UK funds. - `is_custom = 1` — fund added by a tenant adviser (not platform-verified). `is_custom = 0` — platform fund. - `alloc_*` — asset allocation breakdown as % (DECIMAL 5,2, NULL if unknown). Auto-fills equity/bond/cash scenario weights when a fund is selected. - `pdf_*` — evidence trail for PDF-ingested fund data. **`fund_types` values (UK):** `unit_trust`, `etf`, `oeic`, `money_market`, `other`. Managed via `/admin/fund-types`. ### Simulation & Reporting ``` simulation_runs (id, tenant_id, scenario_id, adviser_id, run_count, success_rate, median_years_remaining, worst_depletion_year, median_path_json, worst_path_json, best_path_json, confidence_bands_json, credits_charged, created_at) market_data (year, equity_return, bond_return, cash_return, inflation, source) ``` **`market_data` UK sources:** - `equity_return` — FTSE All-Share total return - `bond_return` — FTSE UK Gilts All Stocks total return - `cash_return` — SONIA (Bank of England overnight rate) - `inflation` — ONS CPI (all items) - `source` — `ftse-russell`, `boe`, `ons-cpi`, `uk-market-data` ### Tax (HMRC) ``` tax_brackets (id, tenant_id, tax_year, threshold_start, threshold_end, base_tax, marginal_rate, created_at, updated_at) ``` > `tenant_id = 0` holds the global HMRC defaults; per-tenant rows override. **UK 2024/25 brackets seeded (`database/uk_tax_2025.sql`):** | Band | From (£) | To (£) | Base tax (£) | Marginal rate | |---|---|---|---|---| | Personal Allowance | 0 | 12,570 | 0 | 0% | | Basic rate | 12,570 | 50,270 | 0 | 20% | | Higher rate | 50,270 | 100,000 | 7,540 | 40% | | PA taper (effective) | 100,000 | 125,140 | 27,432 | 60% | | Additional rate | 125,140 | NULL | 42,516 | 45% | ### State Pension & NI ``` ni_brackets (id, tax_year, lower_limit, upper_limit, rate, ni_class, created_at) state_pension_rates (id, tax_year, weekly_rate_full, qualifying_years_full, min_qualifying_years, created_at) client_ni_records (id, client_id, tenant_id, qualifying_years, estimated_weekly_pension, source, updated_at) state_pension_deferral (id, client_id, tenant_id, deferral_weeks, uplift_rate, created_at) ``` ### Billing ``` subscriptions (id, tenant_id, plan_id, status, stripe_sub_id, started_at, expires_at) credit_packs (id, tenant_id, balance, last_topup_at) invoices (id, tenant_id, amount_gbp, status, stripe_ref, paid_at) ``` **Billing column notes:** - `stripe_sub_id` — Stripe subscription ID (replaces `payfast_token`). Both columns present for compat. - `amount_gbp` — invoice amount in GBP. ### Platform ``` audit_log (id, tenant_id, user_id, action, target_type, target_id, ip, user_agent, created_at) ``` --- ## Relationships ``` tenant ─┬─< users ├─< clients ──< scenarios ──< assets ──> funds (optional FK) ├─< simulation_runs ├─< fund_ingest_runs ├─< subscriptions ─< invoices ├─< credit_packs ├─< tax_brackets (overrides) └─< audit_log funds ──< fund_performance clients ──< client_ni_records clients ──< state_pension_deferral ``` --- ## Data Ownership Rules - **Single source of truth: MySQL.** No duplicate system of record. - **Tenants own everything** their users create. Cross-tenant queries are structurally impossible (BaseRepository enforces it). - **Funds are platform-owned.** Tenants reference them by FK; tenants cannot mutate the funds table directly (admin-only). - **Fund performance** is platform-owned, ingested via CSV uploads in admin. - **Tax brackets** are platform-owned by default (`tenant_id = 0`); tenants may override per year if their planning policy diverges. - **Verified flag is sticky** — an unverified ingest cannot downgrade a row that already has `is_verified = 1`. - **Simulation runs are immutable.** Re-running creates a new row; old runs remain queryable for audit. - **Audit log is append-only.** No `DELETE` from `audit_log` ever (UK GDPR Art.5(2) accountability). - **PII encryption** on `clients.name_enc` and `clients.ni_number_enc` — AES-256-GCM at-rest per UK GDPR Art.32. --- ## Indexes (current) ```sql -- High-traffic lookups CREATE INDEX idx_assets_scenario ON assets(scenario_id, tenant_id); CREATE INDEX idx_scenarios_client ON scenarios(client_id, tenant_id); CREATE INDEX idx_runs_scenario ON simulation_runs(scenario_id, tenant_id, id DESC); CREATE UNIQUE KEY uniq_fund_isin ON funds(isin); CREATE UNIQUE KEY uniq_fund_sedol ON funds(sedol); CREATE INDEX idx_perf_fund_year ON fund_performance(fund_id, year); CREATE UNIQUE KEY uniq_bracket ON tax_brackets(tenant_id, tax_year, threshold_start); CREATE INDEX idx_audit_target ON audit_log(tenant_id, target_type, target_id); ``` --- ## Phase-3 schema additions (planned) ```sql ALTER TABLE assets ADD COLUMN maturity_date DATE NULL, ADD COLUMN interest_rate DECIMAL(6,4) NULL, ADD COLUMN vesting_date DATE NULL, ADD COLUMN tax_wrapper VARCHAR(40) NULL, -- 'sipp','isa','gia','lisa','workplace' ADD COLUMN monthly_contribution DECIMAL(14,2) DEFAULT 0, MODIFY COLUMN asset_type VARCHAR(40) NOT NULL; ALTER TABLE clients ADD COLUMN retirement_date DATE NULL, ADD COLUMN current_age_months INT NULL; ALTER TABLE scenarios ADD COLUMN retirement_date DATE NULL, ADD COLUMN start_phase ENUM('accumulation','drawdown') DEFAULT 'drawdown'; ```