Calorie tracker data model

Purpose

This model is designed for a trustworthy, low-friction calorie and nutrient tracker. It supports packaged foods, whole foods, household recipes, common dishes, saved meals, quantity ranges, barcode/search logging, repeat logging, and AI-assisted drafts without allowing estimates to masquerade as exact data.

The database is PostgreSQL hosted by Supabase. Flutter may read and write user-owned CRUD data through Row Level Security (RLS). Privileged imports, AI processing, and computed operations run through trusted server code.

Design rules

1. A historical log never changes when catalog data changes. Every confirmed log stores a nutrient snapshot.
2. Precision is explicit. Estimated quantities and nutrients carry minimum, expected, and maximum values.
3. Published nutrition records are immutable. Corrections create a new item version.
4. Meals, recipes, and foods are distinct. Foods and recipes are loggable nutrition items; saved meals are reusable collections of those items.
5. Public and user-owned data share behavior, not permissions. Ownership and visibility are explicit on every relevant row.
6. All external data is traceable. Imported records retain source, source identifier, version, and timestamps.
7. AI creates drafts only. A user confirms or edits a draft before it becomes a log.
8. Canonical units are boring. Mass uses grams, volume uses millilitres, energy uses kcal, and each nutrient has one canonical unit.
9. User-local dates are stable. Logs retain the IANA timezone and local calendar date that applied when consumed.
10. Retries are safe. Client-created records use UUIDs and idempotency keys.

Domain boundaries

• Identity and preferences: profile, locale, units, onboarding answers, notification settings.
• Goals: body measurements, active goal, and effective-dated calorie/macro targets.
• Nutrition catalog: food/recipe records, versions, nutrients, servings, barcodes, aliases, and provenance.
• Recipe composition: ingredients, cooking method, yield, and retention assumptions.
• Saved meals: reusable user-defined combinations.
• Logging: meal events, logged items, nutrient snapshots, and corrections.
• Assisted logging: voice/photo/text drafts and candidates.
• Personalization: favorites, derived patterns, suggestions, and notification outcomes.

Core tables

All primary keys are UUIDs unless noted. Every mutable table has created_at and updated_at. User-deletable synced tables also have deleted_at as a tombstone.

Identity and onboarding

profiles

One row per auth.users row.

• user_id — primary key and foreign key to auth.users.id
• display_name
• birth_date — optional; do not store a changing integer age
• country_code
• locale — BCP 47 language tag
• timezone — IANA timezone name
• mass_unit — kg or lb
• height_unit — cm, ft_in
• energy_unit — initially kcal
• onboarding_completed_at

user_preferences

• user_id — primary key
• primary_focus — calorie tracking, macro tracking, meal planning, exercise, or habits
• meal_tracking_enabled
• meal_planning_frequency
• dietary_preferences — validated JSON array until product choices stabilize
• allergens — validated JSON array
• notification_settings — validated JSON with independently controllable reminder types

Use join tables instead of JSON when a value becomes searchable or gains business behavior.

user_goal_selections

Supports up to three onboarding goals without embedding an array in profiles.

• user_id
• goal_code
• priority
• unique (user_id, goal_code) and (user_id, priority)
• constraint limiting priority to 1–3

Goals and measurements

body_measurements

Append-only measurement history.

• user_id
• measured_at
• weight_kg
• optional body_fat_percent, waist_cm
• source — manual, Apple Health, device, import

nutrition_goals

Effective-dated user intent.

• user_id
• goal_type — maintain, lose, gain
• target_weight_kg
• weekly_change_kg
• starts_on, ends_on
• status — draft, active, completed, cancelled

Only one active goal may overlap a given date.

daily_targets

Versioned targets so historical reports use the target that was active that day.

• user_id
• effective_from, effective_to
• energy_kcal
• protein_g, carbohydrate_g, fat_g
• optional fiber and micronutrient targets
• calculation_method — formula identifier or manual
• calculation_inputs — validated structured snapshot
• review_status — generated, user-adjusted, professionally-reviewed

Generated targets must pass safety limits before activation. The calculation service records the formula version and never silently changes an active target. A user may have no target row at all; this is observe mode, not a zero-calorie target.

Data provenance

data_sources

• code — USDA, OFF, NIN, user, FitGenie
• name, homepage_url
• license_name, license_url, attribution_text
• redistribution_allowed

import_runs

Server-only operational record.

• source_id
• source_release
• status
• started_at, finished_at
• counts for read, inserted, updated, rejected
• error_summary

Rejected rows go to protected import diagnostics, not into the catalog.

Nutrient dictionary

nutrients

• code — stable internal code such as energy_kcal, protein_g, vitamin_c_mg
• name
• canonical_unit
• display_order
• is_core
• optional USDA/NIN identifiers

Canonical units never change after use. Source-specific units are converted at ingestion boundaries.

Nutrition items

nutrition_items

Represents an atomic food, common dish, or recipe.

• owner_user_id — null for public catalog items
• kind — food, common_dish, recipe
• visibility — public, private, household
• name, optional brand_name
• description
• default_locale
• current_version_id
• status — draft, active, archived

RLS treats owner_user_id IS NULL as catalog data, not globally writable data.

nutrition_item_versions

Published versions are append-only.

• nutrition_item_id
• version_number
• basis_amount — normally 100
• basis_unit — g or ml
• density_g_per_ml — nullable
• yield_grams_min, yield_grams_expected, yield_grams_max — recipes only
• confidence — verified, label, calculated, estimated
• source_id, source_record_id, source_release
• source_updated_at, imported_at
• published_at, superseded_at
• unique (nutrition_item_id, version_number)

nutrition_values

One row per nutrient and item version.

• item_version_id
• nutrient_id
• amount_min, amount_expected, amount_max
• unique (item_version_id, nutrient_id)

Amounts are per the version's basis. Constraints enforce 0 <= min <= expected <= max. Exact label values use the same number for all three fields.

serving_options

• item_version_id
• label — cup, katori, roti, slice, package
• quantity_value — usually 1
• quantity_unit — count, g, ml, cup, tbsp, etc.
• grams_min, grams_expected, grams_max
• is_default
• source — label, measured, household, estimated

A serving must either resolve to grams or directly resolve to a known item basis. UI labels are localized separately from stable unit codes.

barcodes

• barcode_type — EAN-8, EAN-13, UPC-A, etc.
• normalized_value
• item_version_id
• market_country_code
• source_id

Index normalized barcode lookup. Do not assume a barcode identifies the same formulation in every market.

item_aliases

• nutrition_item_id
• locale
• alias
• alias_type — synonym, transliteration, regional name, brand

Search combines names, aliases, brands, recency, user ownership, and source quality. PostgreSQL full-text search plus trigram indexes are sufficient initially.

Recipes and cooking

recipe_details

One-to-one with a recipe item version.

• recipe_version_id
• servings_expected
• preparation_method
• cooking_method_code
• instructions
• calculation_version

recipe_ingredients

• recipe_version_id
• position
• ingredient_item_version_id
• quantity_value, quantity_unit
• grams_min, grams_expected, grams_max
• retention_profile_id — nullable
• notes

A database/service validation prevents recipe cycles. Recalculating a recipe creates a new recipe version rather than rewriting a published version.

retention_profiles and retention_factors

• Profile identifies source, release, food group, cooking method, and assumptions.
• Factor identifies nutrient and retained fraction.
• Fractions are validated and versioned.

Retention math must be covered by fixture-based tests and reviewed before it becomes user-facing. It is an estimate, so calculated recipe outputs retain ranges and provenance.

Saved meals

meal_templates

• user_id
• name
• optional default_meal_slot
• last_used_at

meal_template_items

• meal_template_id
• position
• item_version_id
• serving_option_id — nullable
• quantity
• default gram range snapshot

Logging a template creates fresh log rows in one transaction. It does not point reports directly at mutable template rows.

Confirmed logs

meal_logs

Header for one eating event.

• user_id
• consumed_at — timestamp with timezone
• consumed_timezone — IANA name at entry time
• local_date
• meal_slot — breakfast, lunch, dinner, snack, other
• entry_method — search, barcode, repeat, template, voice, photo, manual
• note
• client_idempotency_key
• status — draft, active, deleted
• unique (user_id, client_idempotency_key) when present

Clients may assemble items and nutrient snapshots only while a meal is a draft. finalize_meal_log validates ownership, requires at least one item and nutrient snapshots for every item, then activates the meal. Active or deleted history rejects direct child inserts and all direct mutation.

logged_items

• meal_log_id
• source_item_version_id — nullable only for an unreconciled manual item
• source_serving_option_id — nullable
• display_name_snapshot
• quantity, quantity_unit
• grams_min, grams_expected, grams_max
• confidence
• position

logged_nutrients

Immutable nutrition snapshot used for totals and reports.

• logged_item_id
• nutrient_id
• amount_min, amount_expected, amount_max
• unique (logged_item_id, nutrient_id)

Daily totals are derived from logged_nutrients. A cache may be introduced only after measurement shows aggregation is too slow; snapshot rows remain the source of truth.

log_change_events

Append-only audit trail for corrections and deletion.

• meal_log_id
• user_id
• event_type
• occurred_at
• before/after references or a validated minimal diff

The normal edit transaction validates ownership, updates the current user-visible log, and appends an event. Audit events are deleted with their owning account so they cannot block the promised account-deletion path.

Assisted logging drafts

logging_drafts

• user_id
• input_type — voice, text, photo
• status — processing, needs_review, confirmed, failed, expired
• storage_object_path — private bucket path, nullable
• transcript — nullable and governed by retention policy
• parser_strategy — standard, enhanced, photo
• parser_version
• nullable provider, model, model_version for provider-backed strategies
• original_locale, detected_locale
• expires_at
• error_code, user_safe_error

draft_items

• logging_draft_id
• candidate item/version reference
• recognized text
• proposed quantity and gram range
• confidence
• match reason
• position

Confirmation uses a server transaction to create meal_logs, logged_items, and logged_nutrients. Provider output is validated before it reaches these tables. Raw model responses are not trusted domain records. NLP drafts identify food and quantity; deterministic nutrition calculations produce calorie and nutrient snapshots.

user_entitlements

Server-managed capabilities keep subscription decisions out of Flutter authorization logic.

• user_id
• capability — standard text parse, enhanced text parse, voice, photo
• status — active, expired, revoked
• source — subscription provider, promotion, support override
• effective_from, effective_to
• source_reference
• unique active capability per user

Feature flags can disable a provider or enhanced parsing globally. Standard text parsing remains functional when enhanced parsing is removed, replaced, unavailable, or no longer entitled.

See docs/free-text-meal-parsing.md for the parser contract and failure policy.

Personalization

user_favorites

• user_id, nutrition_item_id
• unique pair

meal_patterns

Derived, replaceable data—not authoritative history.

• user_id
• day-of-week and meal slot
• candidate template or item
• support count, confidence
• computed_at, expires_at

suggestion_events

• user_id, pattern reference
• channel — in_app, push
• shown_at, acted_at, dismissed_at
• outcome

This allows recommendation usefulness and notification fatigue to be measured.

Important transactions and RPCs

Use database functions or trusted Node endpoints where multiple writes must succeed together:

1. finalize_meal_log(meal_log_id)
2. confirm_logging_draft(draft_id, edits, idempotency_key)
3. log_meal_template(template_id, consumed_at, quantity_overrides, idempotency_key)
4. repeat_meal_log(source_meal_log_id, consumed_at, quantity_overrides, idempotency_key)
5. publish_recipe_version(recipe_id, calculation_inputs)
6. activate_daily_target(target_id)

Every function checks auth.uid()/server identity, validates all ranges and references, and returns a complete immutable result rather than requiring follow-up mutation.

RLS policy matrix

• Public catalog items and published versions: authenticated read; trusted importer write.
• Private and household items: owner/household read; owner write.
• Profiles, goals, measurements, templates, logs, favorites, drafts, and suggestions: owning user only.
• Import runs, provider diagnostics, retention administration: server roles only.
• Storage photos/audio: private bucket, owner path policy, short-lived signed URLs.
• Form/media catalog: public or signed read; trusted publisher write.

RLS tests must prove both allowed access and cross-user denial for every table and RPC. The Supabase service-role key never ships in Flutter.

Integrity and indexing

Required constraints and indexes include:

• Range ordering for quantities and nutrients.
• Positive serving quantities and recipe yields.
• Unique current version and one default serving per item version.
• Foreign keys that prevent deleting referenced published versions.
• User/date index on meal_logs (user_id, local_date, consumed_at).
• Barcode index on normalized value and market.
• Trigram/full-text indexes for item names, aliases, and brands.
• Template and draft ownership indexes.
• Partial indexes excluding tombstoned rows.

Macro-derived energy (4p + 4c + 9f) is a quality warning, not a hard constraint, because fiber, alcohol, organic acids, and source labeling rules create legitimate differences.

Validation and test fixtures

Before UI work depends on the model, automated tests cover:

• Exact packaged-food serving conversion.
• Gram, volume, count, fractional serving, katori, and roti quantities.
• Recipe scaling, cooked yield, and nutrient retention ranges.
• Copying a meal while changing only selected quantities.
• Catalog version updates leaving historical logs unchanged.
• Day boundaries and daylight-saving transitions.
• Duplicate retries producing one log.
• Cross-user RLS denial.
• Draft confirmation and provider failure recovery.
• Account deletion removing or anonymizing all private data according to policy.

Explicit non-goals for the first tracker release

• Social feeds and creator marketplace.
• Commerce.
• Medical diagnosis or treatment recommendations.
• Automatic logging without user confirmation.
• Premature daily-summary tables, event buses, or microservices.