ERD.md

Entity Relationship Diagram

erDiagram
    USERS ||--o{ STAFF : has
    USERS ||--o{ ENTREPRENEURS : has
    USERS ||--o| CUSTOMERS : may_link
    USERS ||--o{ NOTIFICATIONS : receives

    CUSTOMERS ||--o{ BOOKINGS : makes
    CUSTOMERS ||--o{ ORDERS : places
    CUSTOMERS ||--o{ REVIEWS : writes

    TEAMS ||--o{ STAFF : has_members
    TEAMS ||--o{ BOOKINGS : assigned_to

    SERVICES ||--o{ BOOKINGS : booked_as

    BOOKINGS ||--o| PAYMENTS : paid_by
    BOOKINGS ||--o{ REVIEWS : reviewed_in

    PRODUCTS ||--o{ INVENTORY : stocked_as
    PRODUCTS ||--o{ ORDER_ITEMS : ordered_as
    PRODUCTS ||--o{ REVIEWS : reviewed_in

    ORDERS ||--o{ ORDER_ITEMS : contains
    ORDERS ||--o| PAYMENTS : paid_by
    ORDERS }o--o| ENTREPRENEURS : placed_by

    PAYMENTS ||--o{ TRANSACTIONS : backed_by
    PAYMENTS ||--o{ REFUNDS : may_have

    SUPPORTED_ASSETS ||--o{ PAYMENTS : "referenced by web3_asset"

Loading

Notes on relationships

• A payment belongs to either an order or a booking (enforced by payment_needs_order_or_booking), never both — matching how checkout works in freclean-api.
• supported_assets has no formal foreign key from payments.web3_asset (it's a symbol match, not an ID reference) — this mirrors how freclean-payment's adapter looks up an asset by symbol at verification time.
• audit_log intentionally has no foreign keys at all — it must remain writable even if the referenced entity is later deleted, so the audit trail survives.

See freclean-docs/whitepaper/14-database-architecture.md for the ecosystem-level (less detailed) version of this diagram.