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

# Database Schema & Data Model

> The Neon Postgres schema behind /live: customers and orders are the editable source of truth, analytics are derived in queries, and migrations are idempotent and additive.

The `/live` OS runs on **Neon Postgres**. Schema lives in `src/lib/server/db-schema.ts` as a list of idempotent (`IF NOT EXISTS`) statements, applied via the gated `POST /api/admin/db-setup` endpoint.

## Core principle: derive, don't store

Customers and orders are the editable source of truth. **Analytics (LTV, totals, reorder cadence, revenue) are computed in queries**, never stored, so they stay correct as data is edited. `total === subtotal` is deliberate where there is no delivery-fee data; no numbers are fabricated.

## Tables

| Table               | Purpose          | Notes                                                                      |
| ------------------- | ---------------- | -------------------------------------------------------------------------- |
| `customers`         | Client roster    | `referred_by_id` self-FK powers the referral tree                          |
| `orders`            | Order headers    | `status`, `channel` (e.g. `sms-bot`), `uber_fee`, adapter refs             |
| `order_lines`       | Line items       | `product_id`, `qty`, `unit_price`, `reta_dose`, `reta_shots`               |
| `product_costs`     | Per-product COGS | Manually entered by Chris; **nullable**: margin views stay empty until set |
| `invoices`          | Clover invoices  | Written by the invoice adapter (stub or real)                              |
| `deliveries`        | Uber deliveries  | Written by the delivery adapter (stub or real)                             |
| `payroll_weeks`     | "Owed to me"     | Weekly summary tab                                                         |
| `revenue_goals`     | Targets          | Quick-stats tab                                                            |
| `audit_events`      | Change log       | Every assistant action, automation event, takeover transition              |
| `sms_consents`      | Opt-in audit     | Written by the public `/api/opt-in` portal endpoint                        |
| `bot_conversations` | Bot memory       | Archived SMS threads + FTS index for retrieval                             |

## Order status vocabulary

The `OrderStatus` union (`src/lib/types.ts`):

```ts theme={null}
requested → invoiced → paid → sent_to_clinic → ready → out_for_delivery → delivered
// plus: cancelled
```

<Warning>
  `pending_payment` is a **DB-only** status used for unpaid SMS-bot drafts. It is deliberately **not** in the `OrderStatus` type and is **excluded by the `live-data` loaders**, so a pending order is invisible to the orders list, revenue, LTV, and clinic-notify until payment commits it (`onInvoicePaid` → `paid`). This is the structural core of the payment gate.
</Warning>

"Awaiting fulfillment" (the dashboard triage card and `?status=awaiting`) is defined in `compute.ts` as `status === "requested" || status === "invoiced"`.

## Bot conversation memory

`bot_conversations` archives every SMS thread with a denormalized `search_text` and a GIN full-text index (`to_tsvector('english', ...)`). The bot retrieves relevant past conversations as in-context guidance, **retrieval memory, not model retraining**. `golden = true` marks human-curated exemplars that are always surfaced first; non-golden rows surface only on an FTS keyword match. No embeddings provider is used (pgvector + embeddings is the documented upgrade path).

## Migrations

All schema statements are **additive and idempotent**. Running `db-setup` against a populated DB is a no-op for existing rows. New finance tables seed **empty** (no fabricated COGS/payroll/goals). See [Admin Runbooks](/docs/architecture/runbooks) for the exact seeding procedure and its sharp edges.