Appearance
Commander Tenant Rollout
task: #840074
owner: trunks + multiple commanders
tenants: zoro · allmight · calendar · brook · sanji · squidward
pattern: bulma
designed: 2026-06-01
status: design complete — implementation deferred to start_date 2026-07-15
Pattern Contract (Bulma + Simba baseline)
Every tenant in this rollout must satisfy the endpoint-doctrine §10 self-describe checklist. The football/bulma tenants established this shape. New tenants follow it without deviation.
LayerArtefactLocation convention
L1 StoragePostgres schema (idempotent DDL)`projects/infrastructure/2026-05-hinata-infrastructure/install-scripts/{tenant}-schema-postgres.sql`
L1 StorageInstall shell script`projects/infrastructure/2026-05-hinata-infrastructure/install-scripts/jimmy-vps-add-{tenant}-tenant.sh`
L2 QueryFastAPI router`projects/infrastructure/2026-05-hinata-infrastructure/install-scripts/{tenant}-router-fastapi.py` (canonical edit copy; embedded into sh as heredoc)
L2 QueryCLI wrapper`~/Sandpit/hinata/scripts/{tenant}-q.py` — subcommands read same Postgres via hinata-collector API
L2 QueryWorker proxy route`applications/hinata-studio/api/worker/index.ts` — add `/api/{tenant}/*` passthrough
L3 SnapshotSnapshot YAML shape doc`the-government/information_reference/reference_snapshots/{tenant}.yaml`
L3 SnapshotSnapshot rebuilder LaunchAgent`~/Sandpit/hinata/scripts/update-{tenant}-snapshot.py`
L4 ConsumerBot tool-call rulesinline BOT_RULES table in `hinata-bot-poller.py` — one rule block per Commander
SchemaAPI schema YAML`the-government/information_reference/reference_api-schemas/{tenant}.yaml`
Every router must expose /grammar (GET, accepted period/bucket/category values) and /schema (GET, returns schema YAML as JSON). Auth: x-hinata-key on all write endpoints; reads public. Response envelope: {"data": ..., "meta": {count, as_of, filters, endpoint, elapsed_ms, schema_version}}.
Rollout Sequence
One tenant per PR. Not bundled. Order chosen by data-readiness (what upstream data exists today) and dependency graph (AllMight reads Zoro signals; Calendar has no Commander dependency).
OrderTenantPillarBlockerEst.
1
zoro
FOUNDATION
Hevy API or Excel session input pipeline live — raw/ subfolder must have data to serve. Unblocked if we seed from existing session files.
~3h
2
allmight
FOUNDATION
Zoro tenant live (AllMight reads fitness signal). Zoot-log format defined. Snorlax sleep signal format defined (for burnout flag feed).
~3h
3
calendar
cross-cutting
hinata-cal.py already produces the event feed. No new upstream required — Calendar tenant queries the existing Google Calendar data already powering the Studio tab.
~3h
4
brook
FIRE
brook_ingest.py and brook_daily_scout.py already exist. ingested/ folder already has JSON entries. Trakt/MAL optional — first version reads local ingested/ only.
~3h
5
sanji
FIRE
No upstream data yet (macro-database.csv not seeded). First version stores logs from manual meal-log turns; USDA lookup is Phase 2.
~3h
6
squidward
FORGE
musicmastery tenant is doctrine-compliant and live. Squidward extends it with practice session log + chord-chart save events. No new Postgres schema needed — extends musicmastery schema.
~2h (extend, not new)
Per-Commander Schema Designs
zoro — Fitness (gym sessions + pilates attendance)
L1 storage: Postgres on jimmy-vps. Cross-device, multiple consumers (Studio + bot + Zoro agent).
Schema: zoro Postgres schema
CREATE SCHEMA IF NOT EXISTS zoro;
-- One row per gym session. Source: Excel session export (YYYY-MM-DD.md in raw/).
-- session_id is the date string e.g. '2026-06-01'; stable across re-imports.
CREATE TABLE IF NOT EXISTS zoro.sessions (
session_id TEXT PRIMARY KEY, -- YYYY-MM-DD
session_date DATE NOT NULL,
session_type TEXT, -- 'gym' | 'pilates' | 'cardio'
duration_min INTEGER,
energy_rating INTEGER, -- 1-5 subjective
notes TEXT,
imported_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- One row per exercise per session. Primary fact table.
CREATE TABLE IF NOT EXISTS zoro.lifts (
id BIGSERIAL PRIMARY KEY,
session_id TEXT NOT NULL REFERENCES zoro.sessions(session_id) ON DELETE CASCADE,
exercise TEXT NOT NULL, -- normalised name e.g. 'deadlift'
set_number INTEGER NOT NULL,
reps INTEGER,
weight_kg DOUBLE PRECISION,
rpe INTEGER, -- rate of perceived exertion 1-10
is_pr BOOLEAN DEFAULT FALSE, -- flagged by import script
imported_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_zoro_lifts_session ON zoro.lifts (session_id);
CREATE INDEX IF NOT EXISTS idx_zoro_lifts_exercise ON zoro.lifts (exercise);
-- Per-exercise PR registry. Updated by import script on each sync.
CREATE TABLE IF NOT EXISTS zoro.prs (
exercise TEXT PRIMARY KEY,
best_weight_kg DOUBLE PRECISION,
best_reps INTEGER,
achieved_at DATE,
session_id TEXT
);
-- Pilates attendance (rolled in from pilates-researcher; allMight reads the recovery side).
CREATE TABLE IF NOT EXISTS zoro.pilates_sessions (
session_id TEXT PRIMARY KEY, -- YYYY-MM-DD-HH (class slot)
session_date DATE NOT NULL,
class_name TEXT,
attended BOOLEAN NOT NULL DEFAULT TRUE,
notes TEXT,
imported_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS zoro.data_meta (
key TEXT PRIMARY KEY,
value TEXT,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);FastAPI endpoints: /zoro/*
MethodPathAuthPurpose
POST`/zoro/sessions/ingest`requiredBulk upsert sessions + lifts + PR update. Called by `sync-zoro-sessions.py`.
GET`/zoro/sessions`publicList sessions — params: `period`, `type`, `limit`.
GET`/zoro/sessions/{session_id}`publicSingle session with all lifts.
GET`/zoro/lifts`publicFiltered lift log — params: `exercise`, `period`, `limit`.
GET`/zoro/prs`publicFull PR registry or filtered by exercise.
GET`/zoro/prs/{exercise}`publicSingle exercise PR history.
GET`/zoro/pilates`publicPilates attendance — params: `period`.
GET`/zoro/aggregates`publicVolume + frequency summary for period. Returns: total_sessions, total_sets, avg_duration_min, exercises_hit.
GET`/zoro/grammar`publicSelf-describe: accepted period values + exercise vocabulary.
GET`/zoro/schema`publicSelf-describe: endpoint contract as JSON.
GET`/zoro/meta`publicFreshness + counts from data_meta.
Bot integration rules (BOT_RULES addition)
{"bot": "zoro", "pattern": r"\b(PR|personal record|deadlift|bench|squat|session|workout|lifts?)\b",
"cli": ["zoro-q.py", "session", "--last"]},
{"bot": "zoro", "pattern": r"\b(PRs?|records?)\b",
"cli": ["zoro-q.py", "prs"]},
{"bot": "zoro", "pattern": r"\bpilates\b",
"cli": ["zoro-q.py", "pilates", "--period", "30d"]},CLI subcommands: zoro-q.py
session · session --last · lifts --exercise [name] · prs · pilates · aggregates --period
Event kinds added
fitness.session_logged · fitness.pr_set · pilates.attended — all already in event-kinds.md; producer wraps in try/except per doctrine.
allmight — Health (smoking streak + burnout signals + health metrics)
L1 storage: Postgres on jimmy-vps. Cross-device. Bot reads it live for every burnout-flag query.
Key constraint: the allmight schema is the aggregation point for the burnout-risk weekly flag. Three upstream feeds: Zoro sessions (fitness/energy), Snorlax sleep signal, and the zoot log (smoking events). The schema must be joinable to those. However — AllMight does NOT duplicate data from Zoro. It materialises daily summaries that the burnout flag reads, not raw lifts.
Schema: allmight Postgres schema
CREATE SCHEMA IF NOT EXISTS allmight;
-- Smoking event log. One row per zoot ping from update-zoot-state.py.
CREATE TABLE IF NOT EXISTS allmight.smoking_log (
id BIGSERIAL PRIMARY KEY,
logged_at TIMESTAMP WITH TIME ZONE NOT NULL,
is_relapse BOOLEAN DEFAULT FALSE,
note TEXT,
source TEXT DEFAULT 'telegram' -- 'telegram' | 'manual' | 'nudge'
);
CREATE INDEX IF NOT EXISTS idx_allmight_smoking_at ON allmight.smoking_log (logged_at DESC);
-- Derived daily health summary — materialised by update-allmight-snapshot.py.
-- This is what the burnout flag reads, not raw zoot pings.
CREATE TABLE IF NOT EXISTS allmight.daily_summary (
summary_date DATE PRIMARY KEY,
zoot_count INTEGER DEFAULT 0,
streak_day INTEGER, -- days since last relapse
sleep_hours DOUBLE PRECISION, -- from Snorlax feed
sleep_quality INTEGER, -- 1-5; from Snorlax
gym_attended BOOLEAN DEFAULT FALSE, -- from zoro.sessions join
pilates_attended BOOLEAN DEFAULT FALSE, -- from zoro.pilates_sessions join
energy_rating INTEGER, -- 1-5 from zoro.sessions or manual
work_output_est INTEGER, -- proxy: session count + token burn
burnout_flag TEXT, -- 'green' | 'amber' | 'red' | null
burnout_rationale TEXT,
built_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_allmight_daily_date ON allmight.daily_summary (summary_date DESC);
-- Streak registry — current + historical streaks.
CREATE TABLE IF NOT EXISTS allmight.streaks (
id BIGSERIAL PRIMARY KEY,
streak_type TEXT NOT NULL, -- 'quit_smoking' | 'gym' | 'pilates'
started_at DATE NOT NULL,
ended_at DATE, -- null = current streak
length_days INTEGER GENERATED ALWAYS AS (
COALESCE(ended_at, CURRENT_DATE) - started_at
) STORED,
pb_at_close BOOLEAN DEFAULT FALSE
);
CREATE TABLE IF NOT EXISTS allmight.data_meta (
key TEXT PRIMARY KEY,
value TEXT,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);FastAPI endpoints: /allmight/*
MethodPathAuthPurpose
POST`/allmight/smoking/log`requiredLog a zoot ping. Called by `update-zoot-state.py`.
GET`/allmight/smoking/streak`publicCurrent streak + history.
GET`/allmight/smoking/log`publicFiltered zoot log — params: `period`, `bucket`.
GET`/allmight/burnout/flag`publicLatest RAG burnout flag + rationale from daily_summary.
GET`/allmight/burnout/history`publicWeekly burnout flag history — params: `period`.
GET`/allmight/daily`publicDaily summary rows — params: `period`.
GET`/allmight/streaks`publicActive + historical streaks by type.
POST`/allmight/daily/rebuild`requiredTrigger daily_summary rebuild from upstream joins (Zoro + Snorlax). Called by snapshot rebuilder LaunchAgent.
GET`/allmight/grammar`publicSelf-describe: period vocabulary + streak_type values.
GET`/allmight/schema`publicSelf-describe: endpoint contract.
GET`/allmight/meta`publicFreshness + counts.
Bot integration rules
{"bot": "allmight", "pattern": r"\b(smoking|zoot|streak|quit|smoke)\b",
"cli": ["allmight-q.py", "smoking-streak"]},
{"bot": "allmight", "pattern": r"\b(burnout|tired|overwhelmed|health|energy)\b",
"cli": ["allmight-q.py", "burnout-flag"]},CLI subcommands: allmight-q.py
smoking-streak · smoking-log --period · burnout-flag · burnout-history · daily --period
Technical note — GENERATED ALWAYS AS STORED
The length_days computed column uses a Postgres ≥12 generated column. Confirm jimmy-vps Postgres version before shipping. Fallback: drop the GENERATED clause and compute in application layer instead.
Event kinds
smoking.zoot_logged · smoking.streak_reset · smoking.streak_pb — already in event-kinds.md.
calendar — Event query + opportunity scoring
L1 storage: Postgres on jimmy-vps. hinata-cal.py already feeds Apple Calendar events into a local JSONL; this tenant stores that as queryable rows and adds the opportunity-cost score layer currently computed client-side in opportunityScore.ts.
Key design decision: the Calendar tenant is query infrastructure for all Commanders, not just a UI concern. Sanji reads "what's for lunch near this location", Simba reads "when does X subscription renew on the calendar", AllMight reads pilates attendance. Centralising the query layer here prevents every Commander from scraping iCal independently.
Schema: calendar Postgres schema
CREATE SCHEMA IF NOT EXISTS calendar;
-- Canonical event store. Synced from Apple Calendar via hinata-cal.py eod-sync.
-- uid is Apple Calendar's UID — stable across edits.
CREATE TABLE IF NOT EXISTS calendar.events (
uid TEXT PRIMARY KEY,
title TEXT NOT NULL,
start_at TIMESTAMP WITH TIME ZONE NOT NULL,
end_at TIMESTAMP WITH TIME ZONE,
all_day BOOLEAN DEFAULT FALSE,
calendar_name TEXT, -- e.g. 'Work' | 'Personal' | 'Habits'
location TEXT,
tags TEXT[], -- array of string tags (Postgres native)
status TEXT DEFAULT 'confirmed', -- 'confirmed' | 'tentative' | 'cancelled'
recurrence TEXT, -- RRULE string if recurring
opportunity_score TEXT, -- 'star' | 'high' | 'neutral' | 'risk' | null
auto_accepted BOOLEAN DEFAULT FALSE, -- true if auto-accept rule fired
accept_rule TEXT, -- which rule fired e.g. 'birthday'
raw_ical TEXT, -- original iCal entry for audit
synced_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cal_events_start ON calendar.events (start_at);
CREATE INDEX IF NOT EXISTS idx_cal_events_cal ON calendar.events (calendar_name);
CREATE INDEX IF NOT EXISTS idx_cal_events_tags ON calendar.events USING GIN (tags);
CREATE INDEX IF NOT EXISTS idx_cal_events_score ON calendar.events (opportunity_score);
-- Pilates class attendance link (written by AllMight/Zoro ingest; Calendar tenant mirrors).
-- Avoids a JOIN to zoro schema at query time for common "when is next pilates?" queries.
CREATE TABLE IF NOT EXISTS calendar.pilates_links (
uid TEXT REFERENCES calendar.events(uid) ON DELETE CASCADE,
attended BOOLEAN,
session_id TEXT, -- FK to zoro.pilates_sessions if attended
PRIMARY KEY (uid)
);
CREATE TABLE IF NOT EXISTS calendar.data_meta (
key TEXT PRIMARY KEY,
value TEXT,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);FastAPI endpoints: /calendar/*
MethodPathAuthPurpose
POST`/calendar/events/sync`requiredBulk upsert from hinata-cal.py eod sync. Idempotent on uid.
GET`/calendar/events`publicFiltered event list — params: `period`, `calendar`, `tag`, `score`, `status`, `limit`.
GET`/calendar/events/{uid}`publicSingle event.
GET`/calendar/upcoming`publicNext N events sorted by start_at — params: `days` (default 7), `limit`.
GET`/calendar/inbox`publicEvents in pending/tentative state requiring action (mirrors Calendar Inbox UI).
GET`/calendar/opportunity`publicEvents scored star/high for the next 7d — the opportunity-cost view.
GET`/calendar/pilates`publicPilates class schedule + attendance status — params: `period`.
GET`/calendar/aggregates`publicEvent count by calendar_name + score distribution for period.
GET`/calendar/grammar`publicSelf-describe: period, score, calendar_name vocabularies.
GET`/calendar/schema`publicSelf-describe: endpoint contract.
GET`/calendar/meta`publicFreshness + counts.
Bot integration rules
{"bot": "allmight", "pattern": r"\b(pilates|class|next session)\b",
"cli": ["calendar-q.py", "pilates", "--days", "14"]},Note: Calendar has no dedicated bot — it is a utility tenant queried by other Commanders (AllMight via pilates, Simba via renewals-due, Sanji via meal planning slots). No separate calendar bot is expected.
CLI subcommands: calendar-q.py
upcoming --days · inbox · opportunity --days · pilates --period · events --tag --period
GIN index note
tags TEXT[] uses a GIN index for array contains queries (WHERE tags @> ARRAY['pilates']). This is the correct Postgres index type for array columns. Confirm btree_gin extension is available on jimmy-vps if needed; fallback is to use a join table instead of TEXT[].
Event kinds
calendar.event_created · calendar.event_deleted · calendar.day_synced — already in event-kinds.md.
brook — Entertainment (watchlist + ingested metadata + taste profile)
L1 storage: Postgres on jimmy-vps. brook_ingest.py already writes JSON files to the brook ingested folder (originally at colonel/goku-fire/brook-entertainment/ingested/, now in the-government/information_reference/ or Sandpit) — the schema replaces the flat JSON folder as the queryable surface. Flat files remain as the ingest buffer; a sync script posts them to the API.
Note on existing scripts: brook_ingest.py and brook_daily_scout.py are already written (seen in /opt/jimmy-brain-ops/scripts/). They write to flat JSON. The tenant does not replace this ingest pipeline — it adds a sync step that reads the flat files and upserts to Postgres.
Schema: brook Postgres schema
CREATE SCHEMA IF NOT EXISTS brook;
-- Media item store. Covers films, TV, anime, games, YouTube.
CREATE TABLE IF NOT EXISTS brook.items (
item_id TEXT PRIMARY KEY, -- e.g. YouTube video_id, IMDB tt-id, MAL id
source TEXT NOT NULL, -- 'youtube' | 'imdb' | 'trakt' | 'mal' | 'steam' | 'manual'
media_type TEXT NOT NULL, -- 'film' | 'tv' | 'anime' | 'game' | 'youtube'
title TEXT NOT NULL,
channel TEXT, -- YouTube channel / studio / developer
url TEXT,
description TEXT,
tags TEXT[],
vibe_tags TEXT[], -- auto-tagged from seed vibes (investigative, tension, etc.)
view_count BIGINT,
like_count BIGINT,
ingested_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
synced_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_brook_items_source ON brook.items (source);
CREATE INDEX IF NOT EXISTS idx_brook_items_type ON brook.items (media_type);
CREATE INDEX IF NOT EXISTS idx_brook_items_vibes ON brook.items USING GIN (vibe_tags);
-- Watch/play log — Michael's consumption history.
CREATE TABLE IF NOT EXISTS brook.activity (
id BIGSERIAL PRIMARY KEY,
item_id TEXT REFERENCES brook.items(item_id),
activity_type TEXT NOT NULL, -- 'watched' | 'playing' | 'dropped' | 'queued'
logged_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
rating INTEGER, -- 1-5, optional
feedback_type TEXT, -- 'liked' | 'skipped' | 'training'
note TEXT
);
CREATE INDEX IF NOT EXISTS idx_brook_activity_item ON brook.activity (item_id);
CREATE INDEX IF NOT EXISTS idx_brook_activity_type ON brook.activity (activity_type, logged_at DESC);
-- Watchlist / backlog.
CREATE TABLE IF NOT EXISTS brook.watchlist (
item_id TEXT REFERENCES brook.items(item_id),
added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
priority INTEGER DEFAULT 5, -- 1 (highest) to 10
source_note TEXT, -- why it was added
PRIMARY KEY (item_id)
);
CREATE TABLE IF NOT EXISTS brook.data_meta (
key TEXT PRIMARY KEY,
value TEXT,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);FastAPI endpoints: /brook/*
MethodPathAuthPurpose
POST`/brook/items/ingest`requiredBulk upsert items from sync-brook-ingested.py. Idempotent on item_id.
POST`/brook/activity`requiredLog a watch/play/skip event.
GET`/brook/items`publicFiltered item library — params: `source`, `media_type`, `vibe`, `limit`.
GET`/brook/watchlist`publicBacklog ordered by priority.
GET`/brook/activity`publicActivity history — params: `period`, `activity_type`, `limit`.
GET`/brook/recommend`publicTop 3 watchlist items by vibe match. Vibe derived from AllMight burnout score (if available) or defaults. Not an ML endpoint — deterministic priority sort filtered by vibe.
GET`/brook/grammar`publicSelf-describe: source, media_type, vibe_tag vocabularies.
GET`/brook/schema`publicSelf-describe: endpoint contract.
GET`/brook/meta`publicFreshness + counts.
Bot integration rules
{"bot": "brook", "pattern": r"\b(watch|watching|watchlist|recommend|film|show|anime|game)\b",
"cli": ["brook-q.py", "recommend"]},
{"bot": "brook", "pattern": r"\b(last watch|recent|history)\b",
"cli": ["brook-q.py", "activity", "--period", "7d"]},CLI subcommands: brook-q.py
recommend · watchlist · activity --period · items --vibe
Event kinds
brook.item_ingested · brook.activity_logged — new; add to event-kinds.md under a brook domain before shipping the producer.
sanji — Nutrition (meal log + macro tracking)
L1 storage: Postgres on jimmy-vps. No upstream data yet — Phase 1 stores manual meal-log turns captured by the bot. USDA FoodData Central lookup and receipt OCR are Phase 2 extensions to the same schema.
Design anchor: the macro-database.csv referenced in sanji context.md becomes the sanji.items table. The receipt pipeline writes to sanji.items; the meal log references items by item_id. This means nutrition lookup is always a join, not a repeated text lookup.
Schema: sanji Postgres schema
CREATE SCHEMA IF NOT EXISTS sanji;
-- Food item / ingredient dictionary — the macro-database.csv as Postgres.
-- item_id is stable across edits; name is the human-readable label.
CREATE TABLE IF NOT EXISTS sanji.items (
item_id TEXT PRIMARY KEY, -- slugified name e.g. 'chicken_breast_100g'
name TEXT NOT NULL,
brand TEXT,
calories_kcal DOUBLE PRECISION,
protein_g DOUBLE PRECISION,
carbs_g DOUBLE PRECISION,
fat_g DOUBLE PRECISION,
fibre_g DOUBLE PRECISION,
-- Micronutrients (partial; Phase 2 extends)
iron_mg DOUBLE PRECISION,
b12_ug DOUBLE PRECISION,
vitamin_d_ug DOUBLE PRECISION,
magnesium_mg DOUBLE PRECISION,
zinc_mg DOUBLE PRECISION,
vitamin_c_mg DOUBLE PRECISION,
source TEXT, -- 'usda' | 'nutritionix' | 'label_photo' | 'manual'
usda_fdc_id TEXT, -- FoodData Central ID if sourced from USDA
last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sanji_items_name ON sanji.items (name);
-- Meal log — one row per logged meal.
CREATE TABLE IF NOT EXISTS sanji.meals (
meal_id TEXT PRIMARY KEY, -- YYYY-MM-DD-{meal_type} e.g. 2026-06-01-dinner
meal_date DATE NOT NULL,
meal_type TEXT NOT NULL, -- 'breakfast' | 'lunch' | 'dinner' | 'snack'
location TEXT, -- 'home' | 'restaurant name' | null
quality_rating INTEGER, -- 1-5 subjective
note TEXT,
logged_by TEXT DEFAULT 'bot', -- 'bot' | 'manual' | 'receipt'
logged_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sanji_meals_date ON sanji.meals (meal_date DESC);
-- Meal components — what was in each meal, with quantity.
CREATE TABLE IF NOT EXISTS sanji.meal_items (
id BIGSERIAL PRIMARY KEY,
meal_id TEXT NOT NULL REFERENCES sanji.meals(meal_id) ON DELETE CASCADE,
item_id TEXT REFERENCES sanji.items(item_id),
free_text TEXT, -- fallback when item not in dictionary yet
quantity_g DOUBLE PRECISION,
quantity_unit TEXT DEFAULT 'g' -- 'g' | 'ml' | 'unit' | 'portion'
);
CREATE INDEX IF NOT EXISTS idx_sanji_meal_items_meal ON sanji.meal_items (meal_id);
-- Daily macro summary — materialised by snapshot rebuilder.
CREATE TABLE IF NOT EXISTS sanji.daily_macros (
summary_date DATE PRIMARY KEY,
calories_kcal DOUBLE PRECISION,
protein_g DOUBLE PRECISION,
carbs_g DOUBLE PRECISION,
fat_g DOUBLE PRECISION,
meals_logged INTEGER,
completeness TEXT, -- 'full' | 'partial' | 'estimated'
built_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS sanji.data_meta (
key TEXT PRIMARY KEY,
value TEXT,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);FastAPI endpoints: /sanji/*
MethodPathAuthPurpose
POST`/sanji/meals/log`requiredLog a meal with optional items list.
POST`/sanji/items/upsert`requiredAdd/update food items (macro-database sync).
GET`/sanji/meals`publicMeal history — params: `period`, `meal_type`, `limit`.
GET`/sanji/meals/{meal_id}`publicSingle meal with item breakdown.
GET`/sanji/macros`publicDaily macro summaries — params: `period`, `bucket`.
GET`/sanji/macros/today`publicToday's running macro total.
GET`/sanji/items`publicFood item dictionary — params: `limit`.
GET`/sanji/items/{item_id}`publicSingle food item macro profile.
GET`/sanji/aggregates`publicAverage macros per day for period + meal_type distribution.
GET`/sanji/grammar`publicSelf-describe: period, meal_type, source vocabularies.
GET`/sanji/schema`publicSelf-describe: endpoint contract.
GET`/sanji/meta`publicFreshness + counts.
Bot integration rules
{"bot": "sanji", "pattern": r"\b(eat|ate|meal|food|macros?|protein|calories?|cook|cooked)\b",
"cli": ["sanji-q.py", "macros", "--period", "today"]},
{"bot": "sanji", "pattern": r"\b(last meal|what did I eat)\b",
"cli": ["sanji-q.py", "meals", "--period", "today"]},CLI subcommands: sanji-q.py
macros --period · macros today · meals --period · items
Event kinds
sanji.meal_logged · sanji.macro_built — new domain; add to event-kinds.md before shipping.
squidward — Music practice (extends musicmastery tenant)
Key decision — extend, not new: the musicmastery schema and tenant are already doctrine-compliant and live. Squidward does not get a separate Postgres schema — it gets a practice extension added to the musicmastery schema. This avoids duplicating the chord-chart and flashcard tables that already exist.
What musicmastery already has: musicmastery.events (per-answer event log from Studio), /musicmastery/stats, /musicmastery/global-speed. Those are the drilling/flashcard layer.
What Squidward adds: practice session logs (what pieces, how long, what broke), and chord-chart save events (which chords were built in the Studio chord chart tool). These are separate concerns from drilling stats.
Schema additions to musicmastery schema
-- Added to existing musicmastery schema via ALTER / CREATE IF NOT EXISTS.
-- No new schema created.
-- Practice session log. Squidward owns this table.
CREATE TABLE IF NOT EXISTS musicmastery.practice_sessions (
session_id TEXT PRIMARY KEY, -- YYYY-MM-DD-HHmm
session_date DATE NOT NULL,
duration_min INTEGER NOT NULL,
pieces_worked TEXT[], -- array of piece names e.g. ['Autumn Leaves', 'Giant Steps']
technical_focus TEXT, -- e.g. 'tritone subs' | 'chord inversions'
energy_rating INTEGER, -- 1-5
what_worked TEXT,
what_broke TEXT,
next_target TEXT,
logged_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mm_practice_date ON musicmastery.practice_sessions (session_date DESC);
-- Chord chart save events from the Studio chord-chart tool.
CREATE TABLE IF NOT EXISTS musicmastery.chart_events (
id BIGSERIAL PRIMARY KEY,
event_type TEXT NOT NULL, -- 'saved' | 'exported_html' | 'exported_pdf'
chord_name TEXT,
voicing TEXT,
mode TEXT,
session_id TEXT,
ts TIMESTAMP WITH TIME ZONE NOT NULL,
inserted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);FastAPI endpoints added to /musicmastery/*
MethodPathAuthPurpose
POST`/musicmastery/practice/log`requiredLog a practice session from Squidward bot / practice-log command.
GET`/musicmastery/practice`publicPractice session history — params: `period`, `limit`.
GET`/musicmastery/practice/aggregates`publicTotal minutes + session count + most-worked pieces for period.
POST`/musicmastery/charts/log`requiredLog chord chart save event from Studio UI.
GET`/musicmastery/charts`publicChord chart history — params: `period`, `mode`.
Existing endpoints (/stats, /global-speed) are unchanged. The extension is additive.
Bot integration rules
{"bot": "squidward", "pattern": r"\b(practice|practiced|piano|chord|piece|session)\b",
"cli": ["squidward-q.py", "practice", "--last"]}, {"bot": "squidward", "pattern": r"\b(minutes|time|how long)\b", "cli": ["squidward-q.py", "practice-aggregates", "--period", "7d"]},
#### CLI subcommands: `squidward-q.py`
`practice --last` · `practice --period` · `practice-aggregates --period` · `charts --period`
Note: `squidward-q.py` is a thin wrapper; it calls the musicmastery FastAPI routes. Not a duplicate of the existing musicmastery CLI if one exists.
#### Event kinds
`music.practice_logged` · `chart.saved` · `chart.exported_html` · `chart.exported_pdf` — all already in event-kinds.md.
## Cross-Cutting Implementation Notes
### Install script pattern (identical for all 5 new tenants)
Each `jimmy-vps-add-{tenant}-tenant.sh` follows the football tenant's 4-step pattern exactly:
1. Apply schema (idempotent DDL with IF NOT EXISTS + ALTER ADD COLUMN IF NOT EXISTS guards)
2. Write router file to `${SERVICE_HOME}/app/routers/{tenant}.py`
3. Wire router into main.py (idempotent grep guard before each sed)
4. Restart hinata-collector service + verify status
The canonical edit copy of each router lives in the vault install-scripts folder. The sh script embeds it as a heredoc. If the router needs updating: edit the vault copy, re-run the install script. This is the existing precedent — do not deviate.
### Worker proxy additions
Each tenant needs a route added to `applications/hinata-studio/api/worker/index.ts`. Pattern already exists for `/api/events` and `/api/bulma`. Add one passthrough block per tenant. The Worker holds `HINATA_COLLECTOR_KEY` as a wrangler secret — do not expose it in the browser bundle.
### Snapshot rebuilder cadence
CommanderCadenceRationale
zorodaily (post-midnight)Session data is written once per gym day; next-day rebuild is sufficient
allmighthourlySmoking log can fire any time; burnout flag should reflect today's zoots
calendarevery 30 minEvent changes (invites, rescheduling) need near-real-time reflection in inbox view
brookdailyWatchlist and activity log are low-churn
sanjidailyMacro log is written at meal time; daily summary is adequate
squidwarddaily (post-midnight)Practice sessions are once-per-day maximum
| | | |
| --- | --- | --- |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
### Technical debt flags
* **Inline BOT_RULES in hinata-bot-poller.py:** task #840082 exists to migrate to a dynamic routing.md parser. The rules added here follow the existing inline pattern — they are documented technical debt, not new debt.
* **AllMight GENERATED ALWAYS AS STORED:** only available in Postgres 12+. Check `SELECT version();` on jimmy-vps before shipping. If Postgres 11: drop the computed column, derive length_days in the router.
* **Sanji item_id as slugified name:** names with special characters (accents, spaces) will need consistent slugification. Establish the slug function once in a shared util — do not reinvent per route.
* **Calendar tags TEXT[]:** GIN index requires the tags column to use Postgres native array type. If future columns need the same pattern, consider whether a separate tags join table is more maintainable at scale. For now the array approach is correct at expected volume.
* **Squidward's practice_sessions pieces_worked TEXT[]:** same GIN index consideration applies. At practice-session volume (once/day), this is fine indefinitely.
### OWASP check at system boundaries
* All write endpoints require `x-hinata-key`. The football/events precedent is correct; do not relax this for any new tenant.
* SQL injection: all routes must use parameterised queries (`psycopg %s` placeholders). Never interpolate user input into SQL strings. The football router is the reference — use the same pattern.
* Pydantic validation on all request bodies. No unvalidated JSON ingestion.
* Sanji meals endpoint receives free-text fields (food descriptions). Ensure these are stored verbatim, not evaluated. Max length on `free_text` and `note` fields should be enforced at the Pydantic model level (e.g. `Field(max_length=500)`).
* AllMight smoking log: smoking-related payload follows event-kinds.md privacy rule — count of zoots is OK, do not log location or brand names as structured fields.
## Doctrine Compliance Matrix (endpoint-doctrine §10)
Checklist item
zoro
allmight
calendar
brook
sanji
squidward
api-schemas/{tenant}.yamldesign onlydesign onlydesign onlydesign onlydesign onlydesign only
/grammar GETplannedplannedplannedplannedplannedplanned
/schema GETplannedplannedplannedplannedplannedplanned
update-{tenant}-snapshot.py LaunchAgentplannedplannedplannedplannedplannedplanned
snapshots/{tenant}.yaml shape docplannedplannedplannedplannedplannedplanned
emit_event() in producersplannedplannedalready in hinata-cal.pyplannedplannedchart.saved already emitted
routing.md bot rulesplannedplannedN/A (no dedicated bot)plannedplannedplanned
One Studio consumer migratedZoro Studio tabAllMight Studio tabCalendarView.tsxSanji tab (new)new tabextends MusicMastery tab
| | | | | | | |
| --- | --- | --- | --- | --- | --- | --- |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
All items are "design only" at this stage — this document is the design artefact. Implementation begins at start_date 2026-07-15 per tasks.json. Each column becomes "live" when the corresponding tenant PR lands.
## Explicitly Deferred
ItemWhy deferredWhere tracked
Zoro: Hevy API fetch (direct API)Hevy API access not confirmed. Excel/CSV input path is sufficient for Phase 1.federation/colonel_saitama-foundation_zoro-fitness_context.md — Health & Biometric Pipeline
AllMight: Snorlax sleep signal wireSnorlax sleep signal format not yet defined. AllMight daily_summary.sleep_hours can be NULL until the feed is wired.allmight context.md — data-plumbing gap note
Brook: Trakt.tv / MAL / Steam OAuthPhase 1 reads local ingested/ folder only. External API wiring is a separate task.the-government/information_reference/reference_commander-api-integration — Wave 2
Sanji: USDA FoodData Central lookupPhase 1 is manual entry only. USDA integration requires API key and lookup logic.sanji context.md — Phase 2
Sanji: receipt OCR pipelineRequires Jimmy Neutron Phase 2 image pipeline. Blocked on snapshot rebuilder pattern proven.sanji context.md — macro logging from labels
All tenants: ChatGPT-style tool_call/tool_result API roleshinata-bot-poller.py uses inline rule table, not proper Anthropic tool_use format. Tracked separately.projects/brain/commander-bots.html — "What is deferred"
Calendar: opportunity-score ML modelCurrent opportunityScore.ts is a rule-based scorer. ML upgrade is #840072, gated on data volume.#840072
| | | |
| --- | --- | --- |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
◆ hinata · projects/infrastructure/commander-tenant-rollout.html · task #840074 design · trunks 2026-06-01