# TripTalley — SQLite ERD

Entity-relationship diagram for the TripTalley schema. Amounts are integer
**minor units** (cents) + an ISO-4217 currency code — never floats.
`Settlement` is intentionally **not** a table (computed on demand). `fx_rate`
is a server-side cache so a trip re-tallies without re-hitting the FX API.

```mermaid
erDiagram
    USER ||--o{ GROUP_MEMBER : "belongs to"
    GROUP ||--o{ GROUP_MEMBER : "has"
    USER ||--o{ GROUP : "created"
    GROUP ||--o{ TRIP : "has"
    TRIP ||--o{ EXPENSE : "contains"
    USER ||--o{ EXPENSE : "paid (payer)"
    EXPENSE ||--o{ EXPENSE_PARTICIPANT : "split among"
    USER ||--o{ EXPENSE_PARTICIPANT : "owes share"
    GROUP ||--o{ INVITE : "invites via"

    USER {
        int     id PK
        text    email UK
        text    password_hash
        text    name
        text    home_currency "ISO-4217; default display ccy"
        int     created_at
        int     updated_at
    }

    GROUP {
        int     id PK
        text    name
        int     created_by FK "USER.id"
        int     created_at
        int     updated_at
    }

    GROUP_MEMBER {
        int     group_id PK,FK "GROUP.id"
        int     user_id PK,FK "USER.id"
        text    role "owner | member"
        int     joined_at
    }

    TRIP {
        int     id PK
        int     group_id FK "GROUP.id"
        text    name
        date    start_date "nullable"
        date    end_date "nullable"
        text    settlement_currency "canonical netting ccy"
        int     created_at
        int     updated_at
    }

    EXPENSE {
        int     id PK
        text    client_uuid UK "idempotency key for sync"
        int     trip_id FK "TRIP.id"
        int     payer_id FK "USER.id"
        int     amount_minor "in original_currency"
        text    original_currency "ISO-4217"
        date    spent_on "drives historical FX"
        text    gps_suggested_currency "nullable; suggestion only"
        bool    currency_overridden
        text    split_type "equal | custom | percentage"
        text    note "nullable"
        bool    deleted "soft delete for sync"
        int     created_at
        int     updated_at "last-write-wins"
    }

    EXPENSE_PARTICIPANT {
        int     expense_id PK,FK "EXPENSE.id"
        int     user_id PK,FK "USER.id"
        int     share_weight "custom=minor units; pct=basis points; equal=null"
    }

    INVITE {
        int     id PK
        int     group_id FK "GROUP.id"
        text    token UK "HMAC-signed; not guessable"
        int     created_by FK "USER.id"
        int     expires_at
        bool    revoked
        int     created_at
    }

    FX_RATE {
        date    as_of PK "ECB publish date actually used"
        text    base PK "ISO-4217"
        text    quote PK "ISO-4217"
        text    rate "decimal as string"
        int     fetched_at
    }
```

## Cardinality summary

- A **User** belongs to many **Groups** (via `GROUP_MEMBER`); a Group has many
  Users. Many-to-many.
- A **Group** owns many **Trips**; a Trip belongs to one Group.
- A **Trip** contains many **Expenses**; each Expense has exactly one **payer**
  (a User) and belongs to one Trip.
- An **Expense** is split among many **Users** through
  `EXPENSE_PARTICIPANT` (many-to-many join carrying the per-user share).
- A **Group** issues many **Invites** (signed, expiring links).
- `FX_RATE` is a standalone cache keyed by `(as_of, base, quote)` — no FK; it
  serves every trip's tally.

## Notes for the SQLite implementation

- `EXPENSE.client_uuid` is `UNIQUE` and is what `POST /sync/push` upserts on —
  re-sending after a dropped connection updates rather than duplicates.
- Soft deletes (`EXPENSE.deleted`) so removals propagate through periodic sync.
- Composite PKs on the join tables (`GROUP_MEMBER`, `EXPENSE_PARTICIPANT`,
  `FX_RATE`) — enforced via `PRIMARY KEY (a, b)`.
- Store money as `INTEGER` minor units; currency exponent lives in a static
  code table, not the DB rows.
- Enable `PRAGMA foreign_keys = ON` (SQLite doesn't enforce FKs by default).