Data model
The ten tables of the PostgreSQL schema, relationships, food_ref_id and RLS isolation.
The relational schema lives in PostgreSQL (Supabase) and was designed incrementally through thirteen structured migrations (001 to 013). Every table is protected by Row-Level Security policies. The later migrations (011–013) added the Premium subscription and Stripe columns and a unit field on meal items — see Backend & Supabase.
The tables
| Table | Purpose | Keys | Relationship |
|---|---|---|---|
users | Profiles, targets and settings | id (PK) | System base |
food_items | Catalog and nutrient density per 100 g | id (PK) | Referenced by meal_items |
meals | Chronological log of daily meals | id (PK), user_id (FK) | N → 1 with users |
meal_items | Foods in each meal and their weight | id (PK), meal_id (FK), food_id (FK) | N → 1 with meals |
pantry_items | Inventory, quantities and thresholds | id (PK), user_id (FK) | N → 1 with users |
diet_plans | AI-generated meal plans | id (PK), user_id (FK) | N → 1 with users |
weight_history | Weight time series | id (PK), user_id (FK) | N → 1 with users |
shopping_list | Shopping items (manual or predictive) | id (PK), user_id (FK) | N → 1 with users |
water_log | Daily hydration log | id (PK), user_id (FK) | N → 1 with users |
feedback | Bug reports and suggestions | id (PK), user_id (FK) | N → 1 with users |
How they relate
usersis the base — nearly every table links to it viauser_id.- Each
mealsgroups severalmeal_items; each item references afood_itemsthroughfood_id. pantry_itemsfeeds theshopping_listand gives context to thediet_plans.weight_historyandwater_logfeed the Progress tab.
Canonical food identifier
Integrity across modules (Pantry, Diet, Foods) rests on the food_ref_id field, whose
prefix makes the data's origin explicit:
off:<barcode> → Open Food Facts (international)
local:<slug> → national catalogSee Food sources.
RLS isolation
Every table is shielded by Row-Level Security. The expression evaluated on the server absolutely isolates access to each user's records:
user_id = auth.uid()This guarantees multitenant isolation at the database engine level — one user can never access another's data. See Security.
Where the data lives
| Layer | Role |
|---|---|
| Supabase (PostgreSQL) | Single source of truth. |
| Hive (local) | Instant cache and offline state. |
| External sources | Local PT database, FatSecret and Open Food Facts for foods. |