# Airtable schema (base `appDQruKQ7KdPL4Cd`)

Every field below exists in the base (created 2026-10-12; Users, Audit Log and the payment fields added 2026-11-01) and the base is seeded with the demo dataset
in `data/demo-seed.js` — RX Lab is Pharmacies `PHM-01` with its formulary and `Integration Type = RXLab`.
Fields marked **(new)** were introduced with the 503A integration + intake build. The app still tolerates
a field's absence (writes strip unknown fields and report "schema drift" in Admin → System). Field ids are
listed in `scripts/seed-airtable.js`. Field types are Airtable types.

## Brands
| Field | Type | Notes |
|---|---|---|
| Brand Name | text | |
| Access Code | text | `BRD-…`, portal login |
| Type | single select | Weight loss, Men's health, Longevity… |
| Status | single select | Prospect / Building / Live / Paused |
| Program Price | number | default program fee |
| Platform Fee (new) | number | CareCraft fee per order |
| Slug (new) | text | URL slug used by `/b/:slug` and `/intake/:slug` |
| Site Config (new) | long text | JSON produced by `/launch/` (overrides `sites/brands.js`) |
| Medical Group (new) | text | Name of the PC providing care for this brand |
| Preferred Pharmacy (new) | text | `PHM-…` |
| Launch States (new) | text | comma list |
| LegitScript Status (new) | single select | Not started / Applied / Certified |
| Contact Email (new) | email | |

## Patients
| Field | Type |
|---|---|
| Full Name, Access Code (`PT-…`), Brand Code, State, DOB, Status | existing |
| First Name, Last Name, Email, Phone, Sex (new) | text / email |
| Address Line 1, Address Line 2, City, ZIP, Delivery Instructions (new) | text |
| Height (in), Weight (lb) (new) | number |
| Allergies, Medications, Conditions (new) | long text |
| Pregnant or Nursing (new) | checkbox |
| ID Verified (new) | text — "No" / "Captured — pending review" / "Verified" |
| Consent Telehealth, Consent Privacy, Consent Compounded, Consent Marketing (new) | checkbox |
| Consent Signature, Consent Signed At, Consent IP, Consent User Agent (new) | text |
| Preferred Pharmacy (new) | text |

## Physicians
| Field | Type |
|---|---|
| Name, Access Code (`DOC-…`), States Licensed, Status | existing |
| NPI, DEA, License Number, Credentials, Phone, Email, Practice Address (new) | text |
| Medical Group, Supervising Physician (new) | text |
| Malpractice Expires, DEA Expires, Credentialed On (new) | date |
| Controlled OK (new) | checkbox — may prescribe scheduled drugs |
| Video Capable (new) | checkbox |

## Pharmacies
| Field | Type |
|---|---|
| Pharmacy Name, Access Code (`PHM-…`), State, Specialties, Status | existing |
| Ships To States (new) | text — comma list; dispatch blocked outside it |
| Integration Type (new) | single select — RXLab / API / Email / Fax / Manual (RXLab = RX Lab partner API, see docs/RXLAB-INTEGRATION.md) |
| Endpoint URL (url), API Key Env, Intake Email, Fax Number (new) | text — RX Lab: `https://api.rxlab.com`, `PHARMACY_API_KEY_PHM_01` |
| Billing Model (new) | single select — invoice_platform / invoice_brand / bill_patient |
| NPI, DEA, NCPDP, License Number (new) | text |
| Sterile (USP 797) (new) | checkbox |
| PCAB Accredited, LegitScript Certified (new) | checkbox |
| BAA Signed, PSA Signed (new) | date |
| Formulary (new) | long text — one formula per line `Name | strength | form | price` or JSON array |
| Escalation Contact (new) | text |

## Orders
| Field | Type |
|---|---|
| Order ID, Patient Code, Brand Code, Physician Code, Pharmacy Code, Program, Status, Intake Notes, Physician Notes, Rx Details, Tracking Number, Price, Med Cost, Platform Fee, Created, Updated | existing |
| Intake Data (new) | long text — JSON of the full intake (answers, flags, summary) |
| Flags (new) | long text — JSON array of `{code, level, label}` from the intake rules |
| Priority (new) | single select — standard / review / urgent |
| Encounter Type (new) | single select — async / video / phone |
| Approved At (new) | text (ISO) |
| Rx JSON (new) | long text — structured prescription from the formulary picker: `formularyCode` (RX Lab SKU), medication, strength, form, route, sig, quantity, quantityUnit, daysSupply, refills, schedule, justification, notesForPharmacist |
| Med Cost | currency — computed as quantity × RX Lab unit price when a formulary preparation is chosen |
| Clinical Justification (new) | long text |
| Fulfillment Type, Refill Number (new) | single select / number |
| Rx Sent At, Dispatch Status, Dispatch Mode, Dispatch Log, Rx Payload (new) | text / long text — Dispatch Status: Sent / Portal / Blocked / Failed |
| Pharmacy Order Ref, Pharmacy Ack At, Pharmacy Flag (new) | text |
| Carrier, Tracking URL, Shipped At, Delivered At, Lot, Beyond Use Date (new) | text |

| Payment Status (new) | single select — Unpaid / Pending / Paid / Refunded / Failed / Not Required |
| Payment Ref, Payment Provider, Paid At (new) | text — Stripe session / payment-intent id, provider, ISO timestamp |
| Amount Paid (new) | currency |
| Visit Link, Visit At (new) | url / text — video visit for real-time encounters |

## Messages
Message, Order ID, From Role, Sent At — unchanged.

## Leads (new table)
| Field | Type |
|---|---|
| Company, Contact Name, Email, Phone | text |
| Brand Name, Slug | text |
| Programs | text (comma list) |
| States | text |
| Audience | text — how they reach patients |
| Monthly Budget | number |
| Site Config | long text — JSON from the wizard |
| Readiness | long text — JSON of checklist answers |
| Status | single select — New / Contacted / Building / Live |
| Created | date |

## Users (new table `tblROYh4eBKMfs5JJ`)
Portal logins for every role — see [`USERS-AND-PERMISSIONS.md`](USERS-AND-PERMISSIONS.md). Never reachable through
`/api/airtable`; managed by `/api/auth/*` and `/api/users`.
| Field | Type | Notes |
|---|---|---|
| Email | email (primary) | unique, lower-case |
| Name | text | |
| Role | single select | admin / ops / brand / physician / pharmacy / patient |
| Status | single select | Invited / Active / Disabled |
| Linked Code | text | PT-/BRD-/DOC-/PHM- record the user acts as |
| Brand Codes | text | comma list scoping brand and ops users |
| Password Hash | text | `scrypt$N$r$p$salt$hash` — written by the server only |
| Permissions | long text | optional JSON `{"grant":[…],"deny":[…]}` |
| Token Hash, Token Purpose, Token Expires | text | SHA-256 of the outstanding invite/reset token |
| Failed Logins, Locked Until | number / text | lockout after 8 failures |
| Last Login, Last Login IP | text | |
| Session Version | number | bump to sign the user out everywhere |
| Created, Created By, Notes | date / text / long text | |

## Audit Log (new table `tblOUUTSnsqMzyl17`)
| Field | Type |
|---|---|
| Event (primary) | text — auth.login, orders.update, users.invite, proxy.denied, payments.paid … |
| At | text (ISO) |
| User, Role | text |
| Table, Record, Order ID | text |
| Detail | long text — field names / reason, never values |
| Outcome | single select — ok / denied / failed |
| IP, User Agent | text |

## Scopes on the PAT
`data.records:read`, `data.records:write`, and `schema.bases:read` (for the schema check in
Admin → System).