Asher’s progress tracker
How the tracker stores his data
The app records, it does not judge. Every intervention, whether prescription, supplement, homeopathic, dietary or therapeutic, is stored in the same table with the same fields. Nothing is ranked or given an evidence score. Analysis comes only from what is logged.
- Tables
- 33
- Derived views
- 24
- Functions
- 9
- Database
- Postgres on Supabase
Measures
measures7 fieldsWhat each logged measure means, so views never guess which end of a scale is good. Shared reference data, not per child.
| Field | Type | Details |
|---|---|---|
| key | text | key |
| label | text | required |
| source | text | required daily_scoredaily_countdaily_skillweekly daily_skill: 0–10 skill ratings shown under Skills on Today |
| direction | text | required lower_betterhigher_betterneutral |
| scale_min | number | |
| scale_max | number | |
| sort_order | whole number |
Core
children11 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| user_id | id | required default signed-in user |
| name | text | required |
| dob | date | required |
| timezone | text | required default 'Europe/London'Day boundary for meals and logs. Rolling loads and "which day was this meal" are computed in this zone. |
| illness_tail_days | whole number | required default 14Analysis settings. Kept here rather than hardcoded so they can be retuned and every derived view follows, including for past data. |
| adherence_threshold | number | required default 0.8 |
| observed_min_n | whole number | required default 8days eaten AND not eaten |
| observed_lag_days | whole number | required default 10 = same day, 1 = next day |
| observed_points_per_score | number | required default 1.0How many points of OCD difference (eaten vs not) equal one point on the 0–3 histamine scale, so observed scores sit on the seed’s scale. |
| created_at | date and time | default now |
Linked from interventions.child_id, protocol_entries.child_id, protocol_documents.child_id, daily_logs.child_id, changes.child_id, weekly_ratings.child_id, foods.child_id, meals.child_id, saved_meals.child_id, thresholds.child_id, food_trials.child_id, illness_episodes.child_id, illness_protocol_steps.child_id, observations.child_id, patterns.child_id, media.child_id, progress_events.child_id, library_items.child_id, atec_scores.child_id, growth.child_id, test_results.child_id, monthly_reviews.child_id
interventions7 fieldsEvery substance, therapy or dietary rule that can be "on" or "off".
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| name | text | required "Fish oil", "Rifaximin", "Ferrum phos" |
| category | text | supplementmedicationhomeopathicessential_oildiettherapydevice |
| unit | text | ml, mg, drops, capsules, sessions |
| notes | text | |
| archived | yes / no | required default false |
Linked from protocol_entries.intervention_id, changes.intervention_id, illness_protocol_steps.intervention_id, dose_events.intervention_id, library_interventions.intervention_id
Protocol state
protocol_entries9 fieldsThe active protocol on any date is every row where started_on <= date and (ended_on is null or ended_on >= date). Nothing is re-entered daily.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| intervention_id | id | required links to interventions |
| dose | number | |
| frequency | text | "2x daily", "nightly", "as needed" |
| route | text | oraltopicalinhaledrectal |
| started_on | date | required |
| ended_on | date | null = currently active |
| notes | text |
check (ended_on is null or ended_on >= started_on)exclude using gist (intervention_id with =, daterange(started_on, ended_on, '[]') with &&)
Linked from daily_protocol_log.protocol_entry_id, changes.ended_entry_id, changes.started_entry_id
protocol_documents8 fieldsThe protocol as issued by the practitioner, stored alongside the structured version so the two can be reconciled. AI extraction proposes protocol_entries; nothing is written until the user confirms each row.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| issued_on | date | |
| source | text | practitioner or origin |
| storage_path | text | required PDF, photo or scan |
| notes | text | |
| reconciled | yes / no | required default false |
| created_at | date and time | default now |
Daily log
daily_logs40 fieldsOne row per day. Everything optional except log_date, so a partial day is still saved. Moved ahead of adherence, which references it.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| log_date | date | required |
| agitation | whole number | range 1–10DAILY SCORES, 1-10. Direction lives in the measures table so 1 and 10 never swap meaning, and views can flip difficulty measures. |
| anxiety | whole number | range 1–10 |
| ocd | whole number | range 1–10 |
| stress | whole number | range 1–10 |
| hyperactivity | whole number | range 1–10 |
| face_tension | whole number | range 1–10 |
| mood | whole number | range 1–10 |
| behaviour | whole number | range 1–10 |
| speech | whole number | range 1–10 |
| sugar_intake | whole number | range 0–3Tracked independently of histamine load, not folded into it. |
| sugar_notes | text | what it was: "biscuit, half a juice" |
| sleep_hours | number | OBJECTIVE COUNTS AND DURATIONS. Facts, not judgements; never converted to a score. Sleep fields describe the night ending on the morning of log_date. Poo count is derived from stool_events, not stored. |
| night_wakings | whole number | default 0 |
| night_awake_hours | number | |
| wee_accidents | whole number | default 0 |
| wee_accident_grades | list of text | required default '{}' lightheavyvery_heavyOne grade per accident; wee_accidents is their count. Days logged before grading existed have a count and no grades. |
| meltdowns | whole number | default 0 |
| upside_down_count | whole number | default 0times he goes upside down |
| wrist_to_body | whole number | default 0SELF-DIRECTED AND OUTWARD-DIRECTED BEHAVIOURS, counted separately. Self-injury and aggression often move independently; a blended count hides which changed. hitting own body with wrist |
| wrist_to_head | whole number | default 0hitting own head with wrist |
| head_to_floor | whole number | default 0head down/hanging on floor |
| head_butt_person | whole number | default 0head banging into a person |
| scratching_person | whole number | default 0 |
| behaviour_notes | text | |
| spontaneous_words | whole number | |
| table_sitting_mins | whole number | longest sit to eat |
| lesson_sitting_mins | whole number | longest sit for a lesson |
| play | whole number | range 0–10Skills, 0–10 (0 = not at all, 10 = excellent) |
| pretend_play | whole number | range 0–10 |
| park_play | whole number | range 0–10 |
| interaction | whole number | range 0–10 |
| daily_speech_clarity | whole number | range 0–10 |
| neurotypical | whole number | range 0–10 |
| confounders | list of text | required default '{}'Things that could explain a bad day other than food or protocol: fever, nappy_rash, travel, teething, poor_sleep, visitors… The app offers a fixed list so the same thing is always spelled the same way. |
| food_notes | text | |
| day_notes | text | |
| created_at | date and time | default now |
unique (child_id, log_date)check (sleep_hours >= 0 and night_wakings >= 0 and night_awake_hours >= 0 and wee_accidents >= 0 and meltdowns >= 0 and upside_down_count >= 0 and wrist_to_body >= 0 and wrist_to_head >= 0 and head_to_floor >= 0 and head_butt_person >= 0 and scratching_person >= 0 and spontaneous_words >= 0 and table_sitting_mins >= 0 and lesson_sitting_mins >= 0)
Linked from daily_protocol_log.daily_log_id, meals.daily_log_id, stool_events.daily_log_id, media.daily_log_id
daily_protocol_log6 fieldsWhat he was prescribed and what he actually received are different facts. Only deviations are stored; a day with a daily_logs row and no entry here means every active item was given as prescribed. A day with no daily_logs row is unknown, not adherent.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| daily_log_id | id | required links to daily_logs (deleted with it) |
| protocol_entry_id | id | required links to protocol_entries |
| status | text | required default 'as_prescribed' as_prescribedpartialmisseddose_changedrefused |
| actual_dose | number | only if it differed |
| notes | text |
unique (daily_log_id, protocol_entry_id)
Single-variable change windows
changes15 fieldsA change opens a window of window_days days starting on changed_on (window_end is the first day after it). A second change whose window overlaps an existing one is refused unless the user overrides, which sets overlapped = true. Enforced by trigger in 20261025000300_functions.sql.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| intervention_id | id | required links to interventions |
| ended_entry_id | id | links to protocol_entries The protocol rows this change ended and started, so changes and protocol_entries cannot drift apart. Written together by record_change(). |
| started_entry_id | id | links to protocol_entries |
| change_type | text | required addedremovedincreaseddecreased |
| from_dose | number | |
| to_dose | number | |
| changed_on | date | required |
| window_days | whole number | required default 5 check window_days > 0 |
| window_end | date | computed |
| verdict | text | required default 'pending' toleratednot_toleratedunclearpending |
| verdict_notes | text | |
| overlapped | yes / no | required default false |
| closed_at | date and time |
Linked from event_triggers.change_id
weekly_ratings11 fieldsCapacities that change over weeks, not days.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| week_start | date | required must be a Monday Monday |
| posture | whole number | range 1–101-10, capacity measures: 1 = poor, 10 = excellent |
| strength | whole number | range 1–10 |
| motor_skills | whole number | range 1–10 |
| cognition | whole number | range 1–10 |
| speech_clarity | whole number | range 1–10 |
| understanding | whole number | range 1–10 |
| notes | text | |
| submitted_at | date and time |
unique (child_id, week_start)
Food and meals
foods16 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| name | text | required |
| food_group | text | proteinvegetablefruitcarbohydratefatother |
| flagged | yes / no | required default falseon the known-reaction list |
| status | text | required default 'allowed' allowedexcludedtrial_queuedin_trialnever_tried |
| excluded_on | date | |
| excluded_reason | text | |
| exclusion_confounded | yes / no | Exclusion decided during illness or stacked changes: a weak verdict. |
| histamine_seed | whole number | range 0–3Seed value from published lists. Deliberately rough; superseded by observed_score once the user approves a proposal. |
| perishable | yes / no | required default falseStorage time meaningfully raises histamine: fish, meat, cooked protein. |
| oxalate_seed | whole number | range 0–3 |
| sugar_seed | whole number | range 0–3 |
| observed_score | number | range 0–3From this child’s own data, on the same 0–3 scale as the seed. Proposed by v_observed_scores, written only after user approval. |
| observed_n | whole number | required default 0 |
| notes | text |
Linked from meal_items.food_id, saved_meal_items.food_id, food_trials.food_id, event_triggers.food_id
meals10 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| daily_log_id | id | links to daily_logs (deleted with it) |
| eaten_at | date and time | required |
| meal_type | text | breakfastlunchdinnersnackother |
| photo_path | text | private bucket |
| location | text | homeschoolother_houseout |
| notes | text | |
| ai_extracted | yes / no | required default false |
| user_confirmed | yes / no | required default falsemust be true before scoring |
Linked from meal_items.meal_id
meal_items6 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| meal_id | id | required links to meals (deleted with it) |
| food_id | id | required links to foods |
| portion | text | required default 'normal' tracesmallnormallarge |
| freshness | text | required default 'fresh' freshleftover_24hleftover_over_24hfrozen_then_reheatedApplies to perishable foods only; histamine accumulates in the fridge. |
| notes | text |
Linked from event_triggers.meal_item_id
saved_meals7 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| name | text | required "chicken, rice and pear" |
| photo_path | text | |
| notes | text | |
| times_used | whole number | required default 0 |
| last_used_on | date |
Linked from saved_meal_items.saved_meal_id
saved_meal_items5 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| saved_meal_id | id | required links to saved_meals (deleted with it) |
| food_id | id | required links to foods |
| portion | text | required default 'normal' tracesmallnormallarge |
| freshness | text | required default 'fresh' freshleftover_24hleftover_over_24hfrozen_then_reheated |
thresholds9 fieldsUser-editable, so flags reflect his current tolerance.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| measure | text | required histaminesugaroxalate |
| amber_at | number | required |
| red_at | number | required |
| single_item_red | number | any single item at or above this |
| rolling_48h_amber | number | Today + yesterday. Kept separate from the daily thresholds because a two-day sum is not comparable to a one-day limit. The amber level gates food trials. |
| rolling_48h_red | number | |
| updated_at | date and time | default now |
unique (child_id, measure)check (red_at >= amber_at)check (rolling_48h_red >= rolling_48h_amber)
food_trials15 fieldsStructurally the same as a protocol change: one variable, a clean window, a verdict. clean_start and gate_notes are set by trigger at insert, from the gate conditions, not trusted from the client.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| food_id | id | required links to foods |
| trial_type | text | required retestnew_food |
| started_on | date | required |
| portion | text | required default 'trace' tracesmallnormallarge |
| freshness | text | required default 'fresh' freshleftover_24hleftover_over_24hfrozen_then_reheated |
| clean_start | yes / no | |
| gate_notes | text | |
| window_days | whole number | required default 3 check window_days > 0 |
| window_end | date | computed |
| verdict | text | required default 'pending' toleratednot_toleratedunclearpending |
| verdict_notes | text | |
| next_portion | text | tracesmallnormallarge A tolerated trace does not clear normal portions; escalation is a separate trial. |
| closed_at | date and time |
stool_events6 fieldsCount alone is not informative; type is. Daily poo count is derived here.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| daily_log_id | id | required links to daily_logs (deleted with it) |
| occurred_at | date and time | |
| stool_type | text | required normalpelletylooseexplosive_greenhardmucus |
| accident | yes / no | required default false |
| notes | text |
Illness episodes
illness_episodes10 fieldsIllness days are not stored on daily_logs. v_illness_days derives them (episode plus children.illness_tail_days), so changing the tail updates history too.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| started_at | date and time | required "first sneeze" button |
| first_symptom | text | default 'sneeze' |
| ended_on | date | null = open |
| peak_severity | whole number | range 0–3 |
| fever_max | number | |
| recovery_notes | text | |
| baseline_regained_on | date | may lag ended_on |
| regained_fully | yes / no |
Linked from dose_events.episode_id, media.episode_id, event_triggers.episode_id
illness_protocol_steps9 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| intervention_id | id | required links to interventions |
| schedule | text | required human-readable |
| interval_mins | whole number | for timed prompting |
| max_doses | whole number | null = until episode closed |
| then_schedule | text | e.g. "3x daily after 6 doses" |
| start_offset_mins | whole number | required default 0 |
| sort_order | whole number |
dose_events7 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| episode_id | id | required links to illness_episodes (deleted with it) |
| intervention_id | id | required links to interventions |
| due_at | date and time | |
| given_at | date and time | null and past due = missed |
| skipped | yes / no | required default false |
| notes | text |
Observations
observations10 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| noticed_on | date | required |
| title | text | required "one knee down, other on chair" |
| description | text | |
| tags | list of text | required default '{}' posturestimmotorbehaviourphysicalspeechsocial |
| status | text | required default 'ongoing' ongoingresolvedincreasedintermittent |
| resolved_on | date | |
| follow_up_due | date | +3 months, re-asked at review |
| created_at | date and time | default now |
Linked from media.observation_id
patterns9 fieldsPatterns the parent has noticed ("fewer than 2 poos a day → more agitation"). Kept as her own words; check_kind links a pattern to a built-in comparison against logged data, shown with its n.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| noticed_on | date | required default current_date |
| title | text | required |
| description | text | |
| examples | text | dates and what happened |
| status | text | required default 'watching' watchingseems_truenot_borne_out |
| check_kind | text | poo_countfull_moon_histamine |
| created_at | date and time | default now |
Media
media10 fieldsMoved ahead of progress_events, which references it.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| storage_path | text | required private bucket |
| kind | text | photovideodocument |
| captured_at | date and time | |
| caption | text | |
| tags | list of text | required default '{}' |
| daily_log_id | id | links to daily_logs (cleared if deleted) |
| observation_id | id | links to observations (cleared if deleted) |
| episode_id | id | links to illness_episodes (cleared if deleted) |
Linked from progress_events.media_id
Gains and setbacks
progress_events15 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| event_type | text | required gainsetbackongoing ongoing = still there and not changing ("still the same") |
| occurred_on | date | required when it started |
| noticed_on | date | when it was logged, if later |
| date_approx | yes / no | required default false |
| title | text | required "said ‘hi mama’ unprompted" |
| description | text | |
| domain | text | required speechcognitionsocialmotorplayregulationbehaviourself_caresleepphysical domain is the first of domains, kept for older plugin versions. |
| domains | list of text | required default '{}' speechcognitionsocialmotorplayregulationbehaviourself_caresleepphysical |
| magnitude | whole number | range 1–3 |
| status | text | required default 'current' currentrecoveredpartial |
| recovered_on | date | |
| media_id | id | links to media (cleared if deleted) |
| created_at | date and time | default now |
Linked from event_triggers.event_id
event_triggers11 fieldsA suspected cause is a link to the actual record, not typed text, so it can be counted. Nothing is attributed automatically.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| event_id | id | required links to progress_events (deleted with it) |
| trigger_kind | text | required illnessfoodchangeexposureconfounderunknown |
| episode_id | id | links to illness_episodes |
| meal_item_id | id | links to meal_items |
| change_id | id | links to changes |
| food_id | id | links to foods |
| confounder | text | fever, nappy_rash, travel… |
| source | text | required default 'user' usersuggested_accepted |
| confidence | whole number | range 1–3 |
| notes | text |
check (case trigger_kind when 'illness' then episode_id is not null and num_nonnulls(meal_item_id, change_id, food_id, confounder) = 0 when 'food' then num_nonnulls(meal_item_id, food_id) = 1 and num_nonnulls(episode_id, change_id, confounder) = 0 when 'change' then change_id is not null and num_nonnulls(episode_id, meal_item_id, food_id, confounder) = 0 when 'confounder' then confounder is not null and num_nonnulls(episode_id, meal_item_id, change_id, food_id) = 0 when 'exposure' then num_nonnulls(meal_item_id, food_id) <= 1 and num_nonnulls(episode_id, change_id, confounder) = 0 when 'unknown' then num_nonnulls(episode_id, meal_item_id, change_id, food_id, confounder) = 0 end)
Library
library_items9 fieldsReference only. Not scored, ranked or rated, and never used as context for AI analysis.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| title | text | required |
| category | text | required essential_oilprotocollaserhomeopathicdietsupplementtherapyother |
| tags | list of text | required default '{}' |
| source | text | |
| body | text | |
| created_at | date and time | default now |
| updated_at | date and time | default now |
Linked from library_files.library_item_id, library_interventions.library_item_id
library_files4 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| library_item_id | id | required links to library_items (deleted with it) |
| storage_path | text | required |
| caption | text |
library_interventions2 fields
| Field | Type | Details |
|---|---|---|
| library_item_id | id | links to library_items (deleted with it) |
| intervention_id | id | links to interventions |
primary key (library_item_id, intervention_id)
Measures and records
atec_scores9 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| scored_on | date | required |
| speech | whole number | |
| sociability | whole number | |
| sensory | whole number | |
| health | whole number | |
| total | whole number | computed Null unless all four are present. A missing subscale counted as zero would read as improvement, since lower ATEC is better. |
| notes | text |
growth5 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| measured_on | date | required |
| weight_kg | number | check weight_kg > 0 |
| height_cm | number | check height_cm > 0 |
test_results8 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| test_type | text | geneticstoolbloodurineimagingassessmentreport |
| lab | text | |
| collected_on | date | |
| summary | text | user’s own plain-language note |
| storage_path | text | |
| created_at | date and time | default now |
Linked from gene_variants.test_result_id
gene_variants6 fields
| Field | Type | Details |
|---|---|---|
| id | id | key |
| test_result_id | id | required links to test_results (deleted with it) |
| gene | text | |
| variant | text | |
| genotype | text | |
| notes | text |
Monthly review
monthly_reviews8 fieldsPrefill, observation creation from new_or_odd, and progress_event creation from new gains/worse text are app logic, done once on submit.
| Field | Type | Details |
|---|---|---|
| id | id | key |
| child_id | id | required links to children (deleted with it) |
| month | date | required first of the month |
| gains | text | |
| worse | text | |
| unchanged | text | |
| new_or_odd | text | |
| submitted_at | date and time |
unique (child_id, month)
Derived views
Nothing analytical is stored. Each view is worked out from the tables when it is read, so changing a setting (a multiplier, the illness recovery tail, the adherence threshold) updates past results too. Illness days are left out of before-and-after comparisons.
v_illness_days
Each day inside an illness episode or its recovery tail. An open episode runs to today; its tail starts once it is closed.
Reads illness_episodes children
v_daily_measures_long
Every daily score and counter as one row per day per measure, with its direction and scale, so other views can treat measures generically.
v_meal_item_loads
Every meal item with its computed loads. item_load = coalesce(observed_score, histamine_seed) * portion_multiplier * freshness_multiplier Freshness only applies to perishable foods, and only to histamine; sugar and oxalate use their seed times portion.
Reads meal_items meals children foods daily_logs
v_daily_exposure
Daily exposure. Both the total and the largest single item are kept: a day can be harmful by accumulation or by one large exposure. The 48-hour figures are today plus yesterday. Sugar, histamine and oxalate are never combined.
Reads v_meal_item_loads
v_event_trigger_labels
Readable label for each setback trigger, resolving links to names.
v_daily_with_protocol
Each logged day joined to the interventions active that day, with what was actually given. Any day’s real regimen is reconstructable from this.
Reads daily_logs protocol_entries interventions daily_protocol_log
v_adherence
Adherence per intervention per month, over logged days only. Per change window adherence is in v_change_outcomes.
Reads v_daily_with_protocol
v_change_outcomes
Every change: each measure’s mean over the 7 days before versus the window, with adherence and confounding flags. One row per change per measure (a change with no logged days still gets one row, measure null).
Reads changes v_illness_days interventions children v_daily_with_protocol v_daily_measures_long
v_trial_outcomes
Every food trial: verdict, whether the start was clean, and each measure in the window versus the 7 days before.
Reads changes v_illness_days food_trials foods v_daily_measures_long
v_retest_candidates
Excluded foods ranked for retesting: confounded exclusions first, then those made before the most recent protocol change, then oldest first.
v_food_next_day
For any food, mean next-day measures after days it was eaten versus days it was not. Co-occurrence only; n is shown. "Not eaten" days require confirmed meals that day, so an unlogged day is never counted as a "no".
v_observed_scores
Proposed observed_score per food, from OCD on days after it was eaten versus not (lag set by children.observed_lag_days). The difference is mapped onto the 0–3 seed scale via children.observed_points_per_score. A proposal only: the app writes foods.observed_score after approval.
Reads v_meal_item_loads v_daily_measures_long foods children
v_exposure_lag
Each exposure measure correlated with each daily measure at 0, 1 and 2 day lags (0, 24 and 48 hours). Sugar and oxalate run independently.
v_load_contributors
For the 5 highest histamine days each month, which items drove the number, ranked, with each item’s share of the day.
v_variety
Distinct foods eaten per month, overall and per food group. Flags three consecutive months each lower than the one before.
Reads v_meal_item_loads
v_best_worst_days
Composite per day (difficulty measures flipped so higher is always better), top and bottom 5 per month, each with what was given, what was eaten and the confounders ticked. Needs at least 5 of 10 scores.
Reads v_daily_measures_long v_daily_with_protocol v_meal_item_loads daily_logs
v_monthly_rollup
Per measure per month: total (meaningful for counts), daily mean, and change against the previous logged month. Daily means are compared, so a month with fewer logged days is not penalised.
Reads v_daily_measures_long
v_monthly_extras
The monthly figures that are not a single measure: stool types, zero-waking nights, accident-free days and the longest run of them. A run is broken by an accident or by an unlogged day.
Reads stool_events daily_logs
v_score_trends
Each daily measure with its 7-day rolling mean, raw value alongside.
Reads v_daily_measures_long
v_weekly_development
The six weekly ratings over time, with protocol changes and illness days in each week for plotting alongside.
v_growth_trend
Weight and height with change since the previous measurement. Flags a plateau or drop across two consecutive measurements.
Reads growth
v_progress_by_month
Gains and setbacks by month, with attributed triggers and recovery.
v_trigger_frequency
How often each trigger appears against setbacks, with mean magnitude and mean days to recovery. Rows with is_kind_total = true are totals per trigger kind; the rest are per specific food, change or confounder.
v_illness_impact
Per episode: length, dose adherence, days to baseline, and the previous episode’s length for trend.
Rules the database enforces
One change at a time
A protocol change opens a window, 5 days by default. A second change whose window overlaps is refused unless overridden, and an overridden change is marked as overlapping. Both changes then show as confounded in the analysis.
Clean food trials
A trial starts clean only when: no illness is open or ended in the last 14 days, no change window is open, no other trial is open, the 48-hour histamine load is below its 48-hour amber threshold, and the 3 days before were logged. A trial that fails still runs, marked not clean, with the reasons saved.
Meal flags name their cause
Red for a day total, a single large item, a 48-hour total at or above its threshold, or a food on the known-reaction list. Amber for a day total at or above amber, or a high-histamine food at a normal or large portion. Histamine, sugar and oxalate are checked separately and never combined.
Adherence is a fact
What was prescribed and what was given are recorded separately. Only missed or changed doses need entering. A change window under 80% adherence is marked unreliable.
Private by default
Every table only shows the signed-in owner’s rows. Photos and PDFs sit in a private storage area, served by signed links only.
Nothing written automatically
Doses read from a protocol document by AI, foods read from a meal photo, and food scores proposed from his own data are all shown for confirmation before anything is saved.
Functions
set_updated_atruns automatically- Keeps the updated time on library items and thresholds current.
check_change_windowruns automatically- Runs on every new change. Refuses a change whose window overlaps another change unless the override is passed, and then marks it as overlapping.
record_change- Records a protocol change in one step: ends the current entry, starts the new one and saves the change. If the change is refused, nothing is saved.
edit_change- Corrects a recorded change after the fact: its start date and how many days to watch. The protocol moves with it.
food_trial_gate_failures- Lists which start conditions a food trial would fail on a given date, so the app can show them before the trial begins.
set_food_trial_gateruns automatically- Runs on every new food trial and sets whether the start was clean. The app cannot set this itself.
evaluate_meal- Checks a meal against the thresholds. Returns one line per reason, each naming the cause, the number, and whether the score came from his own data or a published list.
export_child_json- Everything for one child as a single JSON file, saved locally once a day so the data is never trapped in the app.
owns_child- Used by the access rules: true only when the signed-in user owns that child profile.
How it was tested
The migrations were run on Postgres 17 against eight weeks of made-up data: 53 days of logs with one gap, five protocol changes, two meals a day, an illness episode, food trials and setbacks with linked causes.
- All 32 tables, 24 views and 8 functions install with no errors.
- Every view returns the expected rows, with values checked by hand.
- An overlapping change is refused and leaves the protocol untouched. With the override it is saved and marked.
- A window with 3 of 5 doses missed shows 40% adherence and is marked unreliable.
- A trial saved as clean by the app is corrected to the real gate result.
- A second account sees none of the first account’s data and cannot add to or change it.