Data Model

PostgreSQL (EU/Frankfurt). Read directly from backend/app/integrations/models.py and backend/alembic/versions/ on origin/main (July 2026) — 15 tables, all owned by users.id with ON DELETE CASCADE for GDPR erasure. This supersedes the older docs/architecture/diagrams/03-er-model.md ERD, which draws a different, unshipped shape (a profiles table with goals/body_params, a subscriptions table) — see the note at the bottom.

The nutrient-column convention (read this first)

Every nutrient-bearing table (meals, meal_memory, daily_summaries, period_summaries, user_targets) declares a <nutrient>_min/<nutrient>_max column pair for exactly the 32 keys in core/nutrients.py's NUTRIENT_KEYS — one canonical, ordered enumeration, no per-story subsets. A nutrient-drift guard test fails the build if any table is left behind when a nutrient is added. This is why the model is "one tier, columns only" (a rejected nutrition_extensions JSONB rare-extras tier was dropped 2026-07-04) — Statistics can chart, aggregate, or target any nutrient without a JSONB side channel.

energyproteincarbsfatfibersodiumcalciumironvitamin_cvitamin_db12potassiumphosphorusmagnesiumzinciodineseleniumvitamin_avitamin_evitamin_kb1b2b3b5b6b7folatesugarsaturated_fatmono_fatpoly_fatomega3

32 nutrients × 2 (min/max) = 64 numeric columns per table, listed once here and referenced as [NUTRIENT_KEYS] below instead of repeated 64 times per table.

Table	Column nullability	Why
`meals`, `meal_memory`	nullable	A still-estimating draft has no numbers yet
`daily_summaries`, `period_summaries`	NOT NULL, default 0	A day/week with only pending meals is `0/0` + a pending counter — never a gap
`user_targets`	mostly nullable; some sides always null by design	`sodium_min`, `sugar_min`, `saturated_fat_min`, `mono_fat_min` have no lower target zone; `fiber_max`, `potassium_max`, `mono_fat_max`, `omega3_max` have no upper cap

Every table with these columns also carries per-nutrient CHECK (x_min <= x_max) constraints (inverted-range guard), and meals/meal_memory add plausibility backstops (energy_max <= 8000, protein_max <= 500) so bad data can't reach the DB even if application code slips.

Tables

`users`

The identity anchor. Story 0.12 (timezone) + 7.1a (social identity). Every other owned table FKs to users.id.

Column	Type	Notes
`id`	uuid PK
`timezone`	string(64), null	IANA tz; captured lazily at first meal log/onboarding, not row creation
`auth_provider`	string(32), null	e.g. `google`. NULL for anonymous/demo users
`provider_subject`	string(255), null	Provider's stable subject id — the account key, paired with `auth_provider`, never `email`
`email`	string(320), null	Provider-verified display address only — not the identity key (can change; Apple relay alias)
`created_at`, `updated_at`	timestamptz

Constraints: (auth_provider IS NULL) = (provider_subject IS NULL) (registered has both, anonymous has neither) · UNIQUE(auth_provider, provider_subject) — makes claim-or-create race-safe and enforces "one Google identity = one emealia user."

`user_profiles`

Story 6.9. Currently owns only Today-nutrient visibility — not the full goals/body-params/dietary-preferences shape some concept docs describe; that richer profile is planned, not yet columns.

Column	Type	Notes
`user_id`	uuid PK, FK → users.id CASCADE
`locale`	string(12), null
`today_nutrients`	jsonb, NOT NULL default `[]`	`CHECK` array type + `CHECK` length ≤ 10 (the Today band personalization cap)
`created_at`, `updated_at`	timestamptz

`consents`

Story 0.13. Append-and-withdraw log, not a mutable flag.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK CASCADE
`type`	enum `consent_type`
`version`	string(32)	Which consent copy version was agreed to
`granted_at`	timestamptz
`revoked_at`	timestamptz, null

Constraints: revoked_at IS NULL OR revoked_at >= granted_at · partial unique index (user_id, type) WHERE revoked_at IS NULL — "does the user consent now?" always has exactly one answer.

`usage_accounts` trial model

One row per user's AI-estimation entitlement. This is the authoritative trial model — credit-based, not the clock-based model described in older docs.

Column	Type	Notes
`user_id`	uuid PK, FK CASCADE
`subscription_status`	enum, default `TRIALING`
`stripe_customer_id`, `stripe_subscription_id`	string, null, unique	NULL throughout the free trial — Stripe only enters once the user upgrades
`trial_started_at`	timestamptz, null
`trial_credits_granted`	int, default 0	Snapshotted at first reservation from `admin.toml [usage] trial_credits` (currently 30) — later config changes affect new trials only
`trial_credits_used`	int, default 0
`created_at`, `updated_at`	timestamptz

Constraints: trial_credits_used <= trial_credits_granted — this single CHECK is the trial gate at the DB level.

`usage_user_days` / `usage_global_days`

Cost-cap guardrails, independent of the trial. Per-user daily cap (admin default 20/day) and a hard global daily cap (admin default 13,333/day, story 1.16) that pauses the service calmly (no red, no numeric cap shown) rather than let a runaway loop burn budget.

Table	Key columns
`usage_user_days`	`id` PK, `user_id` FK, `local_date`, `ai_calls_reserved` — unique on `(user_id, local_date)`
`usage_global_days`	`utc_date` PK, `ai_calls_reserved`

`processed_webhook_events`

Story 8.2b. Stripe webhook idempotency — the primary key is the dedupe mechanism.

Column	Type
`stripe_event_id`	string(255) PK
`received_at`	timestamptz

`user_targets`

Story 6.8. One active target-zone row per user, output of the pure BuildTargets function — recalculated and upserted on every profile change, never on meal log or read. Frontend renders ideal-zone bars only from stored numbers, never computes on the fly.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK CASCADE, unique
`tier`	string(20)	`CHECK IN ('personalized','reference')` — reference = missing body params, substituted from versioned regional averages
`locale`	string(12)
`calculated_at`	timestamptz
`[NUTRIENT_KEYS]_min/_max`	numeric, mostly null	32 nutrients — see convention above

`daily_summaries`

Story 1.1b. The precomputed backing for Today's bands — RSS-aggregated (root-sum-square uncertainty) as meals are saved, so BuildDailyView reads one row instead of re-summing every meal.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK CASCADE
`local_date`	date	Unique on `(user_id, local_date)`
`[NUTRIENT_KEYS]_min/_max`	numeric, NOT NULL default 0
`pending_count`	int, default 0	A still-estimating meal adds 0 and bumps this — bands show "still estimating," never a gap
`updated_at`	timestamptz

`period_summaries`

Story 9.1. One row per user per Mon–Sun week — the average logged day (mean of each nutrient's daily bounds), for Statistics. Not yet exposed via an API route.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK CASCADE
`period_start`, `period_end`	date	`period_start` = the week's Monday; unique on `(user_id, period_start)` → idempotent recompute
`days_counted`	int, default 0
`[NUTRIENT_KEYS]_min/_max`	numeric, default 0
`updated_at`	timestamptz

`meals` core table

Story 1.2a onward. One logged meal — the user's own words plus the AI's honest nutrient ranges. Created as a draft by LogMeal, filled by the ai_jobs worker, RSS-aggregated into daily_summaries on Save.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK CASCADE
`logged_at`	timestamptz
`local_date`	date
`raw_text`	text	The user's own words — covered by the 90-day pseudonymization job
`client_queue_id`	uuid, null	Idempotency for offline auto-commit on reconnect; unique on `(user_id, client_queue_id)`
`confidence_level`	enum
`revision_count`, `version`, `reestimate_count`	int, default 0	each `>= 0`
`title`	string(`TITLE_MAX_CHARS`=60), null	AI-generated; the length constant is shared with the prompt snippet + response validator
`ai_tag`	string(`TAG_MAX_CHARS`=24), null
`meal_type`	enum, NOT NULL default `other`	breakfast·brunch·lunch·dinner·snack·beverage·other — user-selected, never AI-derived
`status`	enum, default `draft`	draft→logged lifecycle; a draft is excluded from summaries/history until Save
`assumptions`	jsonb, default `[]`	List of `{text_en, text_de, amount, dominating}`
`meal_size`	text, null
`portion_size`	string(100), null
`products`	jsonb, default `[]`	Portion/product breakdown (Epic 18.1)
`[NUTRIENT_KEYS]_min/_max`	numeric, nullable	NULL until the AI estimate lands

Plausibility backstops: energy_min >= 0 AND energy_max <= 8000, protein_min >= 0 AND protein_max <= 500.

`meal_memory`

Stories 5.4/5.6. A confirmed dish remembered in the user's own words — repeat logging with zero AI calls once a dish crosses 3 confirmations. Same nutrient columns as meals (a reuse is a direct copy, one modeling paradigm for Statistics). Not a promise that the AI "learns" — an implementation detail for repeat logging and bounded recommendation context.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK CASCADE
`canonical_text`	text	Unique per `(user_id, canonical_text)`; PII — covered by the 90-day pseudonymization job too
`keywords`	text, default ''	For the "from history" keyword match
`times_confirmed`	int, default 0
`title`, `ai_tag`, `meal_size`, `confidence_level`, `assumptions`	same shape as `meals`	estimate snapshot
`created_at`, `updated_at`, `last_confirmed_at`	timestamptz
`[NUTRIENT_KEYS]_min/_max`	numeric, nullable

`ai_jobs`

Story 1.15a. One async estimation job per meal. Transition logic lives in core/ai_job.py; this row only persists state.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK CASCADE
`meal_id`	uuid FK → meals.id CASCADE
`status`	enum, default `pending`	`AiJobStatus` — the same vocabulary the polling contract exposes
`attempt_count`	int, default 0
`next_attempt_at`	timestamptz, null	Indexed together with `status` for the worker's poll query
`input_snapshot`	jsonb, NOT NULL	Keeps the worker idempotent + auditable
`output_snapshot`	jsonb, null
`created_at`, `updated_at`	timestamptz

`daily_context_tags`

Story 2.9. Optional Day Context tags — a day with no context has no rows. For later Statistics aggregation, not interpreted medically.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK CASCADE
`local_date`	date
`tag`	enum `day_context_tag`	Unique on `(user_id, local_date, tag)`
`created_at`	timestamptz

`activity_context`

Story 2.10. Optional movement context — a calm informational turnover range only. Never feeds Today bands, meal budgets, streaks, or reminders. Epic 18 (activity as first-class context) is explicitly draft/do-not-implement — this table is the entire shipped surface.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK CASCADE
`local_date`	date	Unique on `(user_id, local_date)`
`raw_text`	text
`turnover_min`, `turnover_max`	numeric	`CHECK turnover_min <= turnover_max`, `>= 0`
`created_at`	timestamptz

Relationships (summary)

users 1──* meals 1──* ai_jobs
users 1──1 user_profiles
users 1──1 user_targets
users 1──1 usage_accounts
users 1──* usage_user_days
users 1──* consents
users 1──* meal_memory
users 1──* daily_summaries
users 1──* period_summaries
users 1──* daily_context_tags
users 1──* activity_context
usage_global_days                (no user FK — global row per UTC date)
processed_webhook_events         (no user FK — Stripe event id is the whole row)

Every FK is ON DELETE CASCADE from users.id — a GDPR erasure of the user row removes every owned table's data in one transaction, with two exceptions requiring an explicit job: the 90-day pseudonymization of meals.raw_text/meal_memory.canonical_text runs on a schedule, independent of deletion.

Roadmap — planned tables not yet built

Table	Purpose	Story
`sessions`	Access+refresh session split, rotation/revocation	7.4a/7.4b
`daily_suggestions`	Persisting coaching output (currently generated on the fly, not stored)	4.5

Conflict with `docs/architecture/diagrams/03-er-model.md`

Stale reference diagram

The Mermaid ERD in the emealia-app repo's docs/architecture/diagrams/03-er-model.md was authored early (story 0.12/0.13 era) and describes a planned shape that diverged as the schema actually shipped: it shows a profiles table with goals/body_params/dietary_preferences columns (the real user_profiles only has today_nutrients + locale so far), a subscriptions table with trial_ends_at (the real table is usage_accounts with credit columns, no such table exists), and is missing period_summaries, usage_user_days/usage_global_days, processed_webhook_events, and activity_context entirely. This page (data-model.html) reflects the actually-shipped schema and should be treated as authoritative; the emealia-app diagram needs a refresh (flagged in the doc-agent report, not fixed here per this agent's edit scope).

Data Model

The nutrient-column convention (read this first)

Tables

users

user_profiles

consents

usage_accounts trial model

usage_user_days / usage_global_days

processed_webhook_events

user_targets

daily_summaries

period_summaries

meals core table

meal_memory

ai_jobs

daily_context_tags

activity_context