Portal database model

The portal owns the operational data. This diagram shows the entities that matter and the relationships between them; framework tables (Solid Queue/Cache/Cable, Active Storage) are omitted on purpose. Diagrams are code: update this page in the same PR that changes the schema.

erDiagram
    COMPANY ||--o{ USER : employs
    COMPANY ||--o{ DOCUMENT : "scoped docs"
    COMPANY ||--o{ CONTRACT_DOCUMENT : holds
    COMPANY ||--o{ DONATION : "pledge 1%"
    COMPANY }o--|| CHARITY : "chosen cause"
    DONATION }o--|| CHARITY : "paid to"
    USER ||--o{ DOCUMENT : authors
    USER ||--o{ AUDIT_EVENT : performed
    USER ||--o{ LOGIN_EVENT : "signed in"
    USER ||--o{ BANNER_VERSION : created

    MARKETING_CONTACT ||--o{ MARKETING_CONSENT : "append only ledger"
    MARKETING_TOPIC ||--o{ MARKETING_CONSENT : about
    DEMO_STORE ||--o{ MARKETING_CONTACT : "first seen on"
    DEMO_STORE ||--o{ MARKETING_CONSENT : source
    MARKETING_CONTACT ||--o| VOUCHER : "one ever"

    AB_TEST ||--o{ AD_CAMPAIGN : splits
    USER ||--o{ AD_CAMPAIGN : approves

    GROWTH_SCENARIO {
        string name
        jsonb params "levers, hires, projects, ad budgets"
    }
    DEBT {
        int balance_pence
        int monthly_payment_pence
    }
    COMMITTED_PROJECT {
        int monthly_amount_cents
        string currency
        bool vat_included
    }
    ACTUAL_ENTRY {
        date entry_date
        int amount_pence "signed"
    }
    WORKSHOP_REQUEST {
        string company_name
        jsonb interests
        int estimated_monthly_pence
        int estimated_oneoff_pence
        int status
    }
    MARKETING_CONTACT {
        string email UK
        string first_name
        string last_name
        string company_name
        datetime confirmed_at "verified inbox"
    }
    VOUCHER {
        string code UK
        int discount_pct "1 per topic, cap 5"
        datetime expires_at "3 months"
        datetime redeemed_at
        datetime voided_at
        string void_reason
    }
    MARKETING_CONSENT {
        string action "granted or withdrawn"
        datetime occurred_at
        string source_page
    }
    ACCESS_REQUEST {
        string organisation
        int kind "client agency recruiter"
        int status
    }
    COMPANY {
        string name
        string vat_number
    }
    USER {
        string email UK
        string role
        string otp_secret "encrypted"
    }

Standalone tables (no foreign keys, by design): growth_scenarios, debts, committed_projects, actual_entries (the growth simulator treats them as independent facts), access_requests, workshop_requests (inbox items until converted), charities (vetted list), crunch_connections (accounting OAuth held by a user).

Conventions

• Money is integer pence/cents, never floats; the column name says which.
• Evidence tables (marketing_consents, audit_events, login_events) are append only: rows are never edited, state is derived from the latest row.
• Anything a client could see is scoped by company_id; admin-only data has no scope.