Schema

Database Schema

Full schema documentation. See also db/migrate/ in the repo.

Food Knowledge Graph

food_sensitivity_categories

Column	Type	Notes
id	bigint	PK
name	string	”Histamine”, “FODMAPs”, etc.
slug	string	”histamine”, “fodmap”, etc. Unique index.
description	text

Seeded with 7 categories: histamine, fodmap, salicylate, oxalate, lectin, glutamate, capsaicin.

ingredients

Column	Type	Notes
id	bigint	PK
name	string	Raw name as received (e.g. “whole milk”)
canonical_name	string	Normalized form (e.g. “milk”)

canonical_name handles OFF’s inconsistent ingredient naming at ingest time.

foods

Column	Type	Notes
id	bigint	PK
name	string
open_food_facts_id	string	OFF product ID
barcode	string	EAN/UPC

food_category_memberships

Column	Type	Notes
food_id	bigint	FK → foods
food_sensitivity_category_id	bigint	FK → food_sensitivity_categories
severity	integer	enum: low=0, medium=1, high=2

Unique index on [food_id, food_sensitivity_category_id].

ingredient_food_mappings

Column	Type	Notes
ingredient_id	bigint	FK → ingredients
food_id	bigint	FK → foods

---

Users

users

Column	Type	Notes
id	bigint	PK
name	string
email	string	Unique index

user_suspect_foods

Column	Type	Notes
user_id	bigint	FK → users
food_id	bigint	FK → foods
added_at	timestamp

Unique index on [user_id, food_id].

---

Meal Plans

meal_plans

Column	Type	Notes
id	bigint	PK
user_id	bigint	FK → users
starts_on	date
ends_on	date

meal_plan_slots

Column	Type	Notes
id	bigint	PK
meal_plan_id	bigint	FK → meal_plans
scheduled_for	datetime
meal_id	bigint	FK → meals, nullable

meal_id is null when the slot exists but no recipe is assigned yet. Separates scheduling skeleton from recipe assignment.

washout_windows

Column	Type	Notes
id	bigint	PK
meal_plan_id	bigint	FK → meal_plans
sensitivity_category_id	bigint	FK → food_sensitivity_categories
start_date	date
end_date	date

Active washout = today falls between start_date and end_date. The hypothesis engine queries these before surfacing suggestions.

meals

Column	Type	Notes
id	bigint	PK
user_id	bigint	FK → users
eaten_at	datetime
planned	boolean	false = off-plan / “cheat”
notes	text

meal_ingredients

Column	Type	Notes
meal_id	bigint	FK → meals
ingredient_id	bigint	FK → ingredients
food_id	bigint	FK → foods, nullable

food_id present if ingredient came from barcode scan. Nullable for manual entry.

---

Symptom & Control Logging

symptom_types

Column	Type	Notes
id	bigint	PK
name	string	”Bloating”, “Headache”, etc.
slug	string	Unique
category	string	”gut” or “systemic”

Category enables correlation: histamine → systemic, FODMAPs → gut.

Seeded with: bloating, cramping, diarrhea, constipation, headache, migraine, fatigue, brain_fog, skin_rash, hives, nasal_congestion, joint_pain.

symptom_logs

Column	Type	Notes
id	bigint	PK
user_id	bigint	FK → users
symptom_type_id	bigint	FK → symptom_types
logged_at	datetime
score	integer	1–5
notes	text

daily_controls

Column	Type	Notes
id	bigint	PK
user_id	bigint	FK → users
date	date
sleep_hours	decimal
sleep_quality	integer	1–5
stress_level	integer	1–5
exercise_intensity	integer	enum: none=0, light=1, moderate=2, intense=3
notes	text

daily_control_flags

Column	Type	Notes
id	bigint	PK
daily_control_id	bigint	FK → daily_controls
flag_type	string	menstrual_phase, illness, antihistamine, nsaid, alcohol, etc.
value	string	e.g. “luteal”, “true”, “10mg”

Separate extensible table — no schema changes needed to add new confounder types.

---

Hypothesis Engine

hypothesis_suggestions

Column	Type	Notes
id	bigint	PK
user_id	bigint	FK → users
suggested_food_id	bigint	FK → foods
reason_category_id	bigint	FK → food_sensitivity_categories
status	integer	enum: pending=0, accepted=1, rejected=2

---

Integrations

connected_integrations

Column	Type	Notes
id	bigint	PK
user_id	bigint	FK → users
provider	string	apple_health, google_fit, cronometer, clue, etc.
access_token	string	Encrypted
refresh_token	string	Encrypted
scopes	string
last_synced_at	datetime

integration_sync_logs

Column	Type	Notes
id	bigint	PK
connected_integration_id	bigint	FK → connected_integrations
synced_at	datetime
records_imported	integer
status	string	success / error

---

Future: Lab Results

lab_results

Column	Type	Notes
id	bigint	PK
user_id	bigint	FK → users
collected_at	datetime
provider	string

lab_result_markers

Column	Type	Notes
id	bigint	PK
lab_result_id	bigint	FK → lab_results
marker_name	string	”DAO”, “plasma_histamine”, “urinary_oxalate”, etc.
value	decimal
unit	string
reference_range_low	decimal
reference_range_high	decimal
flag	string	normal / low / high
sensitivity_category_id	bigint	FK → food_sensitivity_categories, nullable

sensitivity_category_id links lab markers directly to the category graph. A low DAO result → histamine category → boosts confidence of histamine hypothesis suggestions.

This is where Overview and Overview data feeds in.