Framebase — Database Schema

erDiagram
    users {
        uuid id PK
        text email UK
        text full_name
        text avatar_url
        timestamptz created_at
        timestamptz updated_at
    }

    accounts {
        uuid id PK
        text name
        text slug UK
        enum plan
        text stripe_customer_id UK
        text stripe_subscription_id
        timestamptz trial_ends_at
        timestamptz created_at
        timestamptz updated_at
    }

    account_members {
        uuid id PK
        uuid account_id FK
        uuid user_id FK
        enum role
        timestamptz invited_at
        timestamptz accepted_at
        timestamptz created_at
    }

    sites {
        uuid id PK
        uuid account_id FK
        text name
        text slug UK
        text domain
        jsonb settings
        boolean is_active
        timestamptz created_at
        timestamptz updated_at
    }

    site_members {
        uuid id PK
        uuid site_id FK
        uuid user_id FK
        enum role
        timestamptz invited_at
        timestamptz accepted_at
        timestamptz created_at
    }

    widgets {
        uuid id PK
        uuid site_id FK
        enum type
        text name
        jsonb config
        text embed_key UK
        boolean is_active
        timestamptz created_at
        timestamptz updated_at
    }

    interactions {
        uuid id PK
        uuid widget_id FK
        uuid site_id FK
        uuid account_id FK
        enum type
        enum status
        text contact_email
        text contact_name
        jsonb metadata
        timestamptz created_at
        timestamptz updated_at
    }

    messages {
        uuid id PK
        uuid interaction_id FK
        uuid sender_id FK
        enum sender_type
        text body
        jsonb attachments
        timestamptz created_at
    }

    submissions {
        uuid id PK
        uuid interaction_id FK
        jsonb fields
        text source_url
        text ip_address
        text user_agent
        timestamptz created_at
    }

    bookings {
        uuid id PK
        uuid interaction_id FK
        timestamptz starts_at
        timestamptz ends_at
        int duration_minutes
        enum status
        text timezone
        jsonb form_data
        text calendar_event_id
        timestamptz created_at
        timestamptz updated_at
    }

    invitations {
        uuid id PK
        uuid account_id FK
        uuid site_id FK
        text email
        enum scope
        enum role
        uuid invited_by FK
        text token UK
        timestamptz expires_at
        timestamptz accepted_at
        timestamptz created_at
    }

    usage_counters {
        uuid id PK
        uuid account_id FK
        int site_count
        int widget_count
        int interaction_count
        int member_count
        timestamptz period_start
        timestamptz period_end
        timestamptz updated_at
    }

    users ||--o{ account_members : "belongs to"
    accounts ||--o{ account_members : "has"
    accounts ||--o{ sites : "contains"
    accounts ||--|| usage_counters : "tracks"
    sites ||--o{ site_members : "has"
    users ||--o{ site_members : "collaborates"
    sites ||--o{ widgets : "has"
    widgets ||--o{ interactions : "generates"
    sites ||--o{ interactions : "scoped to"
    accounts ||--o{ interactions : "rolls up"
    interactions ||--o{ messages : "thread"
    interactions ||--o| submissions : "data"
    interactions ||--o| bookings : "booking"
    accounts ||--o{ invitations : "sends"
    sites ||--o{ invitations : "sends"
    users ||--o{ messages : "sends"