> For the complete documentation index, see [llms.txt](https://t420.gitbook.io/t420/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://t420.gitbook.io/t420/shadow-training-schema-plan.md). # shadow-training-schema-plan ## FleetOS Shadow Training Schema + Migration Plan > **Status: EXECUTED** — Implemented as a single migration (`029_shadow_training_layer.sql`) rather than 5 separate migrations. All tables, enums, indexes, and RLS created. Services in `services/shadow/` are wired and operational. Retained as historical context for the schema design rationale. ### Objective Turn the shadow training layer into something Claude can actually build in Supabase/Postgres. This document defines: * the initial Postgres/Supabase schema * enum strategy * table structure * indexes * row-level security direction * migration order * backfill approach * v1 build scope vs later scope This is **not** the final forever schema. It is the first practical version that captures decision-quality training data without overbuilding an ML platform on day one. *** ### Design Goals The schema must support: * raw workflow event capture * decision point tracking * candidate action tracking * selected action tracking * outcome tracking * state snapshots * model-ready training examples The schema must also support **shadow mode**: * observe only * silent recommendation capture * compare model recommendation vs human action * link downstream outcomes back to decisions *** ## Part 1 — Schema Strategy ### Principles #### 1. Keep operational tables separate from shadow-learning tables Do not overload core `service_requests` and related tables with agent-training concerns. #### 2. Link shadow objects to operational objects Use `work_order_id` / `request_id` references so the shadow layer can observe real coordination work. #### 3. Support nullable references for gradual rollout Some early records may not have complete upstream linkage. #### 4. Use JSONB where flexibility is useful, but not everywhere Use JSONB for snapshots and payloads. Use typed columns for the fields you know you will filter or aggregate on. #### 5. Optimize for learning and audit This is not just telemetry. We need reliable joins between: * context * choice * actual action * outcome *** ## Part 2 — Enum Definitions Use Postgres enums for the most stable controlled vocabularies. ### 1. actor\_type * `fleet` * `supplier` * `system` * `agent` ### 2. channel\_type * `api` * `ui` * `whatsapp` * `email` * `webform` * `agent` ### 3. decision\_type * `missing_intake_info` * `supplier_selection` * `quote_evaluation` * `approval_required` * `technician_assignment` * `bay_assignment` * `part_requirement_prediction` * `follow_up_needed` * `delay_escalation` * `vehicle_delivery_request` ### 4. action\_type * `request_missing_info` * `route_to_supplier` * `submit_quote` * `approve_quote` * `decline_quote` * `assign_technician` * `assign_bay` * `add_part_requirement` * `update_part_status` * `send_follow_up` * `escalate_case` * `request_vehicle_delivery` * `mark_ready_for_pickup` * `complete_work_order` * `cancel_work_order` * `other` ### 5. outcome\_type * `quote_approved` * `quote_rejected` * `job_completed` * `deadline_met` * `budget_variance` * `response_time_minutes` * `completion_time_minutes` * `reassignment_required` * `escalation_occurred` * `customer_complaint` * `other` ### 6. shadow\_mode * `observe_only` * `silent_recommendation` * `suggestion_visible` * `guardrailed_autonomy` *** ## Part 3 — Core Tables ### 1. coordination\_sessions Represents the lifecycle of a coordination case. #### Purpose Group all shadow-learning artifacts around one work order / coordination case. #### Columns * `coordination_session_id uuid primary key default gen_random_uuid()` * `work_order_id uuid null` * `service_request_id uuid null` * `fleet_company_id uuid null` * `supplier_company_id uuid null` * `shadow_mode shadow_mode not null default 'observe_only'` * `opened_at timestamptz not null default now()` * `closed_at timestamptz null` * `final_outcome text null` * `success_score numeric(5,2) null` * `created_at timestamptz not null default now()` * `updated_at timestamptz not null default now()` #### Notes * `service_request_id` should link to existing FleetOS request object if that remains the core work object. * `work_order_id` can be introduced later if Phase 1 API creates a more explicit work-order abstraction. #### Indexes * index on `service_request_id` * index on `work_order_id` * index on `fleet_company_id` * index on `supplier_company_id` * index on `opened_at desc` *** ### 2. workflow\_events Raw event stream for the coordination session. #### Columns * `event_id uuid primary key default gen_random_uuid()` * `coordination_session_id uuid not null references coordination_sessions(coordination_session_id) on delete cascade` * `work_order_id uuid null` * `service_request_id uuid null` * `event_type text not null` * `actor_type actor_type not null` * `actor_id uuid null` * `actor_name text null` * `channel channel_type not null` * `payload jsonb not null default '{}'::jsonb` * `created_at timestamptz not null default now()` #### Indexes * index on `coordination_session_id, created_at` * index on `service_request_id, created_at` * index on `event_type` * gin index on `payload` *** ### 3. state\_snapshots Frozen structured snapshot of the case at a decision moment. #### Columns * `state_snapshot_id uuid primary key default gen_random_uuid()` * `coordination_session_id uuid not null references coordination_sessions(coordination_session_id) on delete cascade` * `work_order_id uuid null` * `service_request_id uuid null` * `snapshot_version integer not null default 1` * `snapshot_json jsonb not null` * `created_at timestamptz not null default now()` #### What snapshot\_json should contain in v1 * priority / urgency * vehicle identifier * location * category * deadline / required\_by * budget auth * current quote state * technician state * bay state * part state * elapsed times * missing fields array * last inbound / outbound channel #### Indexes * index on `coordination_session_id, created_at` * index on `service_request_id, created_at` * gin index on `snapshot_json` *** ### 4. decision\_points The most important table. #### Columns * `decision_point_id uuid primary key default gen_random_uuid()` * `coordination_session_id uuid not null references coordination_sessions(coordination_session_id) on delete cascade` * `work_order_id uuid null` * `service_request_id uuid null` * `decision_type decision_type not null` * `state_snapshot_id uuid null references state_snapshots(state_snapshot_id) on delete set null` * `trigger_event_id uuid null references workflow_events(event_id) on delete set null` * `opened_at timestamptz not null default now()` * `resolved_at timestamptz null` * `resolved_by_actor_type actor_type null` * `resolved_by_actor_id uuid null` * `resolution_summary text null` * `created_at timestamptz not null default now()` #### Indexes * index on `coordination_session_id, opened_at` * index on `service_request_id, opened_at` * index on `decision_type` * index on `resolved_at` *** ### 5. candidate\_actions Stores the actions that were available or proposed at a decision point. #### Columns * `candidate_action_id uuid primary key default gen_random_uuid()` * `decision_point_id uuid not null references decision_points(decision_point_id) on delete cascade` * `action_type action_type not null` * `action_payload jsonb not null default '{}'::jsonb` * `proposed_by actor_type not null` * `confidence_score numeric(5,4) null` * `rank integer null` * `was_model_generated boolean not null default false` * `created_at timestamptz not null default now()` #### Indexes * index on `decision_point_id, rank` * index on `decision_point_id, proposed_by` * gin index on `action_payload` *** ### 6. selected\_actions Stores what was actually chosen. #### Columns * `selected_action_id uuid primary key default gen_random_uuid()` * `decision_point_id uuid not null references decision_points(decision_point_id) on delete cascade` * `candidate_action_id uuid null references candidate_actions(candidate_action_id) on delete set null` * `selected_action_type action_type not null` * `selected_action_payload jsonb not null default '{}'::jsonb` * `selected_by_actor_type actor_type not null` * `selected_by_actor_id uuid null` * `selected_at timestamptz not null default now()` * `selection_reason text null` * `was_human_override boolean not null default false` #### Indexes * unique index on `decision_point_id` where one decision gets one selected action in v1 * index on `selected_by_actor_type` * gin index on `selected_action_payload` *** ### 7. outcome\_records Links downstream outcomes back to the coordination session and decisions. #### Columns * `outcome_record_id uuid primary key default gen_random_uuid()` * `coordination_session_id uuid not null references coordination_sessions(coordination_session_id) on delete cascade` * `work_order_id uuid null` * `service_request_id uuid null` * `outcome_type outcome_type not null` * `outcome_value jsonb not null default '{}'::jsonb` * `measured_at timestamptz not null default now()` * `created_at timestamptz not null default now()` #### Indexes * index on `coordination_session_id, measured_at` * index on `service_request_id, measured_at` * index on `outcome_type` * gin index on `outcome_value` *** ### 8. outcome\_record\_decision\_points Join table because one outcome can relate to multiple decision points. #### Columns * `outcome_record_id uuid not null references outcome_records(outcome_record_id) on delete cascade` * `decision_point_id uuid not null references decision_points(decision_point_id) on delete cascade` * primary key (`outcome_record_id`, `decision_point_id`) #### Indexes * index on `decision_point_id` *** ### 9. training\_examples Model-ready examples derived from real cases. #### Columns * `training_example_id uuid primary key default gen_random_uuid()` * `decision_point_id uuid not null references decision_points(decision_point_id) on delete cascade` * `input_snapshot_id uuid null references state_snapshots(state_snapshot_id) on delete set null` * `target_action_type action_type null` * `target_action_payload jsonb null` * `human_action_type action_type null` * `human_action_payload jsonb null` * `model_action_type action_type null` * `model_action_payload jsonb null` * `agreement_score numeric(5,4) null` * `outcome_score numeric(5,4) null` * `dataset_version text null` * `created_at timestamptz not null default now()` #### Indexes * unique index on `decision_point_id` * index on `dataset_version` * index on `outcome_score` *** ## Part 4 — Recommended Foreign Key Strategy ### Existing FleetOS tables to reference where possible Likely existing tables: * `service_requests` * `companies` * `contacts` * maybe future `work_orders` #### Initial recommendation Use nullable foreign keys for: * `service_request_id` * `fleet_company_id` * `supplier_company_id` * actor IDs when they are true UUIDs Do not block the shadow layer because one operational record is messy. *** ## Part 5 — RLS Direction ### Recommendation for v1 Keep shadow-learning tables server-written only at first. #### Why This layer is internal and sensitive. It should not be directly writable by normal client-side sessions. ### v1 policy direction * deny direct public writes * allow service-role writes * allow controlled internal reads for admins / internal dashboards only #### Suggested approach For each new table: * enable RLS * create no broad public insert/update policy * create restricted select policy only for internal admin roles if needed * otherwise use Supabase service role from backend functions / Edge Functions / server routes This is the safest starting point. *** ## Part 6 — Migration Order ### Migration 1 — enums Create enums: * `actor_type` * `channel_type` * `decision_type` * `action_type` * `outcome_type` * `shadow_mode` ### Migration 2 — base tables Create: * `coordination_sessions` * `workflow_events` * `state_snapshots` * `decision_points` ### Migration 3 — choice tables Create: * `candidate_actions` * `selected_actions` ### Migration 4 — outcome tables Create: * `outcome_records` * `outcome_record_decision_points` ### Migration 5 — training example table Create: * `training_examples` ### Migration 6 — indexes + RLS Add indexes and RLS policies after table creation. ### Migration 7 — helper functions Optional but useful: * `create_coordination_session_for_request(request_id uuid)` * `log_workflow_event(...)` * `open_decision_point(...)` * `resolve_decision_point(...)` * `create_training_example_for_decision(...)` *** ## Part 7 — Backfill Strategy ### Goal Do not wait for perfect future instrumentation. Backfill some value from existing service request history. ### Backfill what is realistic #### 1. coordination\_sessions Create one session per existing `service_request`. #### 2. workflow\_events Backfill from known timestamps on requests: * created\_at → `work_order_created` * acknowledged\_at → `quote_submitted` or `supplier_responded` * accepted\_at → `quote_approved` * started\_at → `work_started` * completed\_at → `work_completed` * declined\_at → `quote_declined` * cancelled\_at → `work_cancelled` * ready\_for\_pickup\_at → `ready_for_pickup` #### 3. decision\_points Only backfill obvious inferred decisions in v1: * `quote_evaluation` when quote exists * `approval_required` when quote + budget auth exists * `technician_assignment` when technician assigned * `bay_assignment` when bay assigned #### 4. state\_snapshots Backfill one coarse snapshot per inferred decision point. #### 5. training\_examples Do **not** aggressively backfill this at first. Generate from new live traffic after instrumentation is in place. *** ## Part 8 — v1 Scope vs Later Scope ### v1 build now Build now: * enums * tables listed above * server-only writes * instrumentation hooks in request lifecycle * basic session creation * basic event logging * basic decision point opening for 3–5 decision types * basic selected action capture * basic outcome capture ### Later Add later: * model-generated candidate actions * agreement scoring automation * recommendation ranking dashboards * dataset versioning workflows * training export pipelines * autonomy controls per action type *** ## Part 9 — Concrete v1 Instrumentation Targets Instrument these request lifecycle points first: ### 1. Request created * create coordination session * create workflow event * optionally open `missing_intake_info` decision if key fields absent ### 2. Quote submitted / supplier responded * create workflow event * open `quote_evaluation` decision point * snapshot state ### 3. Quote approved or declined * create workflow event * resolve `quote_evaluation` * create outcome record ### 4. Technician assigned * create workflow event * open and resolve `technician_assignment` ### 5. Bay assigned * create workflow event * open and resolve `bay_assignment` ### 6. Part added / updated * create workflow event * open `part_requirement_prediction` only if system suggested before human action ### 7. Work completed * create workflow event * create outcomes for: * `job_completed` * `completion_time_minutes` * `deadline_met` if deadline existed * `budget_variance` if quote/final cost available *** ## Part 10 — Suggested File Outputs For Claude Claude should produce: ### SQL migrations * `supabase/migrations/xxxx_shadow_training_enums.sql` * `supabase/migrations/xxxx_shadow_training_core.sql` * `supabase/migrations/xxxx_shadow_training_choices.sql` * `supabase/migrations/xxxx_shadow_training_outcomes.sql` * `supabase/migrations/xxxx_shadow_training_rls.sql` ### Type generation updates * generated Supabase types updated after migrations ### Server-side services Suggested files: * `services/shadow/CoordinationSessionService.ts` * `services/shadow/WorkflowEventService.ts` * `services/shadow/DecisionPointService.ts` * `services/shadow/OutcomeService.ts` * `services/shadow/TrainingExampleService.ts` ### Integration points Add hooks in the existing request workflow service so shadow data is created as the operational workflow moves. *** ## Part 11 — Success Criteria This schema plan is successful if Claude can implement a v1 where: * new service workflows automatically create coordination sessions * major workflow steps generate structured workflow events * major decisions are captured as decision points * human choices are captured as selected actions * outcomes are linked back to decisions * FleetOS starts accumulating training-quality coordination data * all of this happens without unsafe early autonomy --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://t420.gitbook.io/t420/shadow-training-schema-plan.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.