Data Schema

-- Core tables
CREATE TABLE clients (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    -- Personal details (encrypted)
    legal_name_encrypted BYTEA,
    email_encrypted BYTEA,
    phone_encrypted BYTEA,
    -- ... other fields
);

CREATE TABLE deals (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id),
    project_type TEXT,
    status TEXT,
    bank_chosen TEXT,
    loan_amount DECIMAL,
    -- ... other fields
);

CREATE TABLE pending_changes (
    id SERIAL PRIMARY KEY,
    entity_type TEXT,  -- 'client' or 'deal'
    entity_id INTEGER,
    field_name TEXT,
    proposed_value JSONB,
    source_type TEXT,   -- 'form', 'webhook', 'document', 'portal'
    status TEXT,       -- 'pending', 'approved', 'rejected'
    reviewed_by INTEGER,
    reviewed_at TIMESTAMP
);

-- Repeatable sections stored as JSONB or separate tables
CREATE TABLE client_properties (...);
CREATE TABLE client_dependants (...);
CREATE TABLE deal_loan_tranches (...);
CREATE TABLE deal_banks_approached (...);