> ## Documentation Index
> Fetch the complete documentation index at: https://dhanurgo.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Schema

> Entity-relationship diagrams and table documentation

# Database Schema

HostMetrics uses Supabase (PostgreSQL) with 25+ tables organized into four domains.

## Core Tables

```mermaid theme={null}
erDiagram
    profiles ||--o{ vehicles : "owns"
    profiles ||--o{ trips : "imports"
    profiles ||--o{ expenses : "tracks"
    profiles ||--o{ earnings : "imports"
    profiles ||--o{ csv_imports : "uploads"
    profiles ||--o{ documents : "stores"
    profiles ||--o{ misc_income : "records"

    vehicles ||--o{ trips : "assigned to"
    vehicles ||--o{ documents : "has"
    vehicles ||--o{ maintenance : "serviced"
    vehicles ||--o{ incidents : "involved in"
    vehicles ||--o{ vehicle_photos : "has"

    expenses ||--o{ expense_vehicles : "split across"
    expense_vehicles }o--|| vehicles : "allocated to"

    trips {
        uuid id PK
        uuid user_id FK
        text reservation_id UK
        text vehicle_id
        text guest_name
        timestamptz trip_start
        timestamptz trip_end
        text trip_status
        numeric trip_price
        numeric total_earnings
        numeric boost_price
        numeric delivery
        numeric extras
        numeric tolls_and_tickets
        numeric late_fee
        numeric cleaning
        numeric gas_reimbursement
        numeric on_trip_ev_charging
        numeric post_trip_ev_charging
        numeric cancellation_fee
        numeric additional_usage
    }

    vehicles {
        uuid id PK
        uuid user_id FK
        text turo_vehicle_id
        text name
        text license_plate
        text vin
        text status
        text make
        text model
        integer year
    }

    expenses {
        uuid id PK
        uuid user_id FK
        uuid vehicle_id FK
        text category
        text description
        numeric amount
        date expense_date
        text vendor
        boolean is_recurring
    }
```

## Investor Tables

```mermaid theme={null}
erDiagram
    investors ||--o{ investor_vehicles : "assigned"
    investors ||--o{ investor_payments : "receives"
    investors ||--o{ investor_charges : "charged"
    investors ||--o{ investor_activities : "logged"
    investors ||--o{ investor_agreements : "has"

    investor_vehicles }o--|| vehicles : "linked to"

    investors {
        uuid id PK
        uuid user_id FK
        text name
        text email
        numeric owner_share
        numeric investor_share
        text payment_frequency
        integer payment_day
        text status
        text expense_treatment
    }

    investor_vehicles {
        uuid id PK
        uuid investor_id FK
        uuid vehicle_id FK
        boolean include_trip_price
        boolean include_delivery_fees
        boolean include_extras
        boolean include_tolls
        boolean include_late_fees
        boolean include_gas_reimbursement
        boolean include_ev_charging
        boolean include_cleaning_fees
        boolean include_cancellation_fees
        boolean include_additional_usage
        boolean include_boost_earnings
        text expense_treatment
    }

    investor_payments {
        uuid id PK
        uuid investor_id FK
        numeric amount
        date payment_date
        text status
        text payment_method
        text notes
    }

    investor_agreements {
        uuid id PK
        uuid investor_id FK
        text version
        text status
        numeric owner_share
        numeric investor_share
        text payment_frequency
        date effective_date
        date expiry_date
        date signed_date
    }
```

## Toll Tables

```mermaid theme={null}
erDiagram
    toll_accounts ||--o{ toll_transactions : "has"
    toll_transactions }o--o| vehicles : "matched to"

    toll_accounts {
        uuid id PK
        uuid user_id FK
        text provider
        text account_id
        text status
        timestamptz last_synced
    }

    toll_transactions {
        uuid id PK
        uuid user_id FK
        uuid toll_account_id FK
        text provider
        date transaction_date
        text transaction_time
        text plaza_name
        text roadway
        numeric amount
        text tag_number
        text plate
        uuid matched_vehicle_id FK
        uuid matched_trip_id FK
    }
```

## Billing Tables

```mermaid theme={null}
erDiagram
    plans ||--o{ subscriptions : "subscribes"
    profiles ||--o{ subscriptions : "has"
    profiles ||--o{ payment_methods : "saves"
    subscriptions ||--o{ invoices : "generates"

    plans {
        uuid id PK
        text name
        numeric price_monthly
        numeric price_annual
        jsonb features
        boolean is_active
    }

    subscriptions {
        uuid id PK
        uuid user_id FK
        uuid plan_id FK
        text stripe_subscription_id
        text status
        timestamptz current_period_end
    }
```

## Multi-Tenant Isolation

Every table includes a `user_id` column with Row Level Security (RLS) policies:

```sql theme={null}
-- Example RLS policy (applied to all tables)
CREATE POLICY "Users can only see their own data"
    ON trips FOR ALL
    USING (user_id = auth.uid());
```

All application queries go through `getCurrentUserId()` in `src/lib/db/_client.ts`, which extracts the authenticated user's ID from the Supabase session.