`docs/DATA-MODEL.md`

48 lines
# Data Model

Authoritative target schema is in `SPEC.md`. This file mirrors what's actually implemented in `db/schema.sql` and notes deferred tables.

## Implemented today (8 tables)

| Table | Why it exists in v0 |
|---|---|
| `user_account` | single-row identity for Steve; will become per-household later |
| `consent_grant` | every connector binding writes one; revocation marks `revoked_at` |
| `connector_account` | one row per Gmail account; refresh token AES-256-GCM-encrypted |
| `source_message` | every fetched Gmail message lands here, raw JSONB payload + hash |
| `document` | one per email attachment / source_message body PDF (stored in `uploads/`) |
| `purchase` | one per inferred order; links back to the source_message |
| `purchase_item` | line items (when extractable); brand/model/GTIN as discovered |
| `audit_log` | append-only; every state change writes one row |

## Deferred to later milestones

| Table | Milestone |
|---|---|
| `org_account` | Foundation (M1) |
| `admin_account` | Foundation (M1) |
| `service_commitment` | Rights expansion (M5) |
| `medical_asset` | Medical expansion (M6) |
| `pharma_product` | Medical expansion (M6) |
| `coverage_policy` | Claim MVP (M4) |
| `registration_requirement` | Ingestion MVP (M2) |
| `recall_event` | Protection MVP (M3) |
| `recall_match` | Protection MVP (M3) |
| `rights_rule_snapshot` | Rights expansion (M5) |
| `claim_case` | Claim MVP (M4) |
| `action_queue` | Claim MVP (M4) |
| `calendar_reminder` | Ingestion MVP (M2) |

## Conventions

- All IDs are ULIDs (`text`), not UUIDs — sortable and shorter to read in logs
- Timestamps: `timestamptz`, never `timestamp`
- Money: stored as `numeric(12,2)`, currency as ISO 4217 in a separate column
- Soft-delete: avoided; use `revoked_at` / `state` columns where lifecycle matters
- Indexes: every FK; every column used in `WHERE`; every JSONB path used in queries (`gin (payload jsonb_path_ops)`)
- Encryption: refresh tokens via `lib/crypto.js` (AES-256-GCM, key from `ENCRYPTION_KEY` env)

## Schema migrations

`db/migrations/NNNN_*.sql`. Run forward only via `npm run migrate`. `db/schema.sql` is the **declarative current state** for fresh installs; migrations are the **incremental path** for an existing database. Both must agree after every commit.