From Loom to Engine: The Scheduling Core Gets a Clean Schema

The scheduling brain of RABS just had its biggest renovation since the project began. The metaphor names everyone learned -- workshed, loom, ribbon -- have done their job and are now retired. In their place sits core_engine: a born-clean Postgres schema with 19 tables, a dedicated instance API at /api/v1/engine, a fully-wired program creation wizard, and a working philosophy for how the past becomes truth. Twelve backend route files were rewritten to point at the new schema. Seventy-three real billing rates replaced the POC junk rows. The wizard now handles NF2F lines, participant photos, participant fees, click-to-edit time slots, and full draft management. None of this is theoretical anymore.

Why the rename, and why now

The old schema had grown three parallel scheduling systems on top of each other. There was a programs tree from the original POC, a program_templates tree from a later iteration, and a tgl_loom_* tree from the loom POC. Routes were calling tables that no longer existed. Some columns had been renamed in one place and left alone in another. A single endpoint could touch four different generations of the same idea.

The first plan was to rename tables in place inside core_ops and prune the dead branches. A column-by-column gap analysis was even produced (20260419_GAP_ANALYSIS.md) showing every meaningful column from every dropped table mapped onto a surviving target. But once that document existed, the conclusion changed: if you have to write hundreds of lines of careful rename SQL to make a schema readable, you should probably just write the schema you want.

So the schema was rewritten from scratch under a new name, core_engine. core_ops stays put as operational support -- shift notes, shift density, vehicle records, venue records, document tracking, Type 2 session storage. The scheduling engine moves out of the family home and gets its own place.

What core_engine actually contains

Nineteen tables, one transaction, no legacy baggage. The naming is direct now -- no metaphor prefixes -- and reads top to bottom in the order things happen.

Table	What it is
programs	The blueprint. The repeating shape of a scheduled event.
program_slots	Time slots inside a program (pickup, activity, meal, dropoff).
program_participants	Who normally attends, with address preferences.
program_participant_billing	Default billing lines per participant.
program_staff	Staff placeholders (auto / manual / open).
program_vehicles	Vehicle placeholders (auto / manual / personal car).
program_requirements	Computed staffing/vehicle minimums.
program_activities	Activities attached to a program.
intents	Date-specific modifications. The calendar.
instances	A program on a date after intents apply. The living thing.
instance_timeslots	Cards on the live timeline for one instance.
instance_attendance	Participant rows for one instance.
instance_shifts	Staff shifts for one instance.
instance_staff_checkin	Day-of staff check-ins.
instance_vehicle_dispatch	Day-of vehicle dispatch.
instance_billables	Billing lines generated for one instance.
instance_routes	Transport route cards.
instance_artifacts	Pinned shift notes, incident reports, attachments.
instance_tags	Semantic tags with vector embeddings for search.

External foreign keys reach out to exactly four schemas: core_source (participants, staff, vehicles, venues), finance (billing rates), accounts (users), and admin (app settings). Nothing else. The engine is islanded by design.

The three migration scripts that did the work

File	What it did
20260420_core_engine_create.sql	Built the 19 tables in one transaction. Triggers, constraints, indexes.
20260420_core_engine_patch.sql	TablePlus review fixes -- schema-qualified trigger functions, NOT NULL booleans, relaxed split-shift uniqueness, CHECK constraint hardening.
20260420_core_ops_prune.sql	Dropped 30+ dead tables from core_ops. The 20 surviving tables are operational support only.

A separate seed (20260421_seed_billing_rates.sql) replaced 11 POC junk rows in finance.billing_rates with 73 real NDIS-style codes. The wizard's old auto-calc-from-1:1 behaviour was preserved -- enter a base rate, group ratios calculate themselves, single_rate codes stay 1:1 only.

The conceptual model that survived

The metaphors are gone from the table names but the thinking they encoded is what made the redesign work. It's worth writing down in plain language because the engine is built around it.

• Sources are the filing cabinets. Participant files, staff files, vehicle files, venue files. Without these, nothing operational can be built.
• Programs are blueprints. They describe what normally happens. They are not the future and not the past -- they are the current intended shape of the repeating thing.
• Intents are the calendar. A date-specific modification that does not rewrite the blueprint: a participant cancelled this Tuesday only, a bus is unavailable for two weeks, an alternate pickup address for one day, a birthday reminder, a billing override.
• Instances are the living thing. A program on a specific date after the matching intents have been applied. Mutable until the day starts or the instance is locked.
• The active window is destructible and rebuildable. Programs plus intents plus settings should reproduce the future. The window can be 4 weeks or 16 weeks -- it doesn't matter, because future instances aren't truth, they're projections.
• Locked instances are historical truth. Once locked, they don't change because templates change. They store snapshots of names, addresses, rates, and totals as they were on the day.

The most important principle that fell out of all this: a foreign key is not history. If a locked billable line still references the live finance.billing_rates row, then changing a rate changes the past. Unacceptable. Locked rows must denormalise the values that mattered at the time. That decision shapes the entire instance_billables schema and the lock endpoint behaviour.

Backend routes -- twelve files, one schema

Every route file that touched the old scheduling tables was rewritten to point at core_engine. The auditor counted around 156 stale references across the twelve files. After the migration:

File	Status
templates.js (1671 lines)	Wizard backend -- programs, slots, participants, billing, staff, vehicles, requirements, finalize.
util_syncRethread.js	Active-window instance generation.
util_generateBilling.js	Billing line generation when instances are projected.
finance.js	Payment lifecycle, invoice tracking.
dashboard.js	Live operations dashboard queries.
programs.js	Program list endpoints.
activities.js	Program activities CRUD.
intentions.js	Intent CRUD (processing logic still to come).
loom.js	Legacy loom routes, patched but slated for replacement by engine.js.
master-schedule.js	Calendar-style schedule queries.
roster.js	Staff roster queries.
system.js	System-level queries that touched scheduling.

What stayed in core_ops: shift notes, shift cover claims, shift density cache, vehicle/venue documents and inspections, and the Type 2 session tables. None of that is part of the scheduling engine.

The new instance API: engine.js

The big new piece is a fresh route file that the old structure didn't have: backend/routes_v1p/engine.js. Mounted at /api/v1/engine, it is the operations-side counterpart to the wizard. The wizard builds the blueprint; the engine serves the live and locked instances that result.

Every endpoint listed below is wired today:

GET    /api/v1/engine/window
GET    /api/v1/engine/instances
GET    /api/v1/engine/instances/:id
PATCH  /api/v1/engine/instances/:id

GET    /api/v1/engine/instances/:id/attendance
POST   /api/v1/engine/instances/:id/attendance
PATCH  /api/v1/engine/instances/:id/attendance/:aid

GET    /api/v1/engine/instances/:id/staff
POST   /api/v1/engine/instances/:id/staff
PATCH  /api/v1/engine/instances/:id/staff/:sid

GET    /api/v1/engine/instances/:id/vehicles
POST   /api/v1/engine/instances/:id/vehicles
PATCH  /api/v1/engine/instances/:id/vehicles/:vid

GET    /api/v1/engine/instances/:id/routes
POST   /api/v1/engine/instances/:id/routes

GET    /api/v1/engine/instances/:id/timeslots
GET    /api/v1/engine/instances/:id/billables

POST   /api/v1/engine/instances/:id/lock
POST   /api/v1/engine/instances/lock-date

GET    /api/v1/engine/instances/:id/artifacts
POST   /api/v1/engine/instances/:id/artifacts
GET    /api/v1/engine/instances/:id/tags
POST   /api/v1/engine/instances/:id/tags

The window endpoint reads admin.app_settings for loom_window_fortnights (default 4 fortnights = 56 days) and returns the date list the engine considers active. The lock endpoints are where the snapshotting philosophy gets enforced -- when an instance locks, names, addresses, rates, and totals are denormalised onto the instance and its children, so future template edits or rate changes can never rewrite the past.

The program creation wizard

The wizard at page_prog_create started as a backdoor POC and is now a serious operational tool. It walks the user through six steps -- details, time slots, participants, staffing, vehicles, finalize -- and on finalize it calls util_syncRethread and util_generateBilling to project instances and billing lines forward into the active window.

What landed in this round of polish:

• Real billing rates. The 73 seeded codes show up in the dropdowns. Group ratios auto-calculate from the 1:1 base rate the way the original POC behaved.
• NF2F billing lines. Each billing line has an NF2F button that creates a sibling line tagged NF2F, with manual hours and a manual rate (currency-formatted with a $ prefix and two-decimal semantics). The line snapshots into instance_billables like any other.
• Participant photos. Served via an extension-free backend route at /api/v1/participants/:id/photo that probes for .png, .webp, .jpg, and .jpeg and streams whichever exists. No more 404s from mismatched file extensions, and <img> tags work without sending Authorization headers because the cookie auth is in place.
• Participant fees. New participant_fee column with a UI that includes a "charge a fee" checkbox and an amount input. Captured per participant on the program.
• Click-to-edit time slots with adjacent give/take. Editing a slot's start or end time pushes against its neighbours instead of overlapping them. Slot types now match the DB constraint exactly: pickup, activity, meal, other, dropoff. Slot labels are first-class.
• Full draft management. Drafts can be deleted from the modal. The POST that creates a draft now returns the full program row instead of a stub, so the wizard doesn't have to refetch.
• Page-size fixes. Participants, staff, vehicles, and venues all fetch enough rows to actually populate the dropdowns. The wizard was previously hiding records behind default pagination.

There is one tiny outstanding wizard refinement: moving the participant fee $ sign so it sits to the left of the entered amount, after the checkbox. That's a five-minute UI tweak, not a schema concern.

What's wired today

• core_engine schema live on DB2 with 19 tables, constraints, indexes, and triggers.
• core_ops pruned to 20 operational support tables. No more parallel scheduling trees.
• 12 backend route files migrated to core_engine. No stale core_ops references in scheduling routes.
• /api/v1/engine exposes 23 endpoints covering instance CRUD, attendance, staff check-ins, vehicle dispatch, routes, timeslots, billables, locking, artifacts, and tags.
• Program creation wizard end-to-end: draft → details → slots → participants → billing (with NF2F) → staff → vehicles → finalize → instance projection.
• Billing rates seeded with 73 real codes, group-ratio auto-calc preserved.
• Participant photos via extension-free route. Cookie auth means <img> tags just work.
• Participant fees captured per-program-participant.

What's next

The wizard is the build surface. The engine is the operational surface. The next phase is making the operational surface visible to humans.

• Verify the lock endpoint denormalises everything the design demands -- program name, venue name and address, participant names, staff names, vehicle names and registrations, billing code text, unit price, hours, ratio label, total amount. The endpoint exists; the snapshotting needs end-to-end testing.
• Confirm syncRethread produces every required instance child when a program is finalised -- not just instances and timeslots, but attendance, shifts, vehicle dispatch, billables, and routes.
• Build the intent-application engine. intentions.js is currently CRUD-only. Intents need side effects: cancel an instance, swap a venue, override times, add or remove a participant for the day, swap staff, override billing for a date.
• Build the live operations frontend. The active workspace where today's instances are visible, attendance is logged, staff check in, vehicles dispatch. page_workspace does not yet exist. page_schedule and page_prog_settings are stubs that need to consume /api/v1/engine.
• Rename page_loom-settings to a system-org settings page. The threshold knobs (vehicle trigger, WPU, active window size) belong in a system settings layer, not on the templates themselves.
• Template change history. Programs are blueprints, but their intentional changes need a changelog so admins can scrub through time and see when a participant was added or a slot was resized. Locked instances keep their snapshot regardless.

The recovered transcript that survived the previous session ended on a UI nit about a dollar sign. The architectural work is done. The schema is clean. The engine has an API. The wizard is exceptional. The next agent that picks this up gets to build the part everyone actually sees.

Quick Reference

Concept	Location
Schema	core_engine.* (19 tables, DB2)
Wizard backend	backend/routes_v1p/templates.js
Instance API	backend/routes_v1p/engine.js (mounted at /api/v1/engine)
Sync engine	backend/routes_v1p/util_syncRethread.js
Billing engine	backend/routes_v1p/util_generateBilling.js
Wizard frontend	admin/src/js/pages/page_prog_create.js
Migrations	admin/tasks/sql/sql-current/20260420_core_engine_*.sql
Build status	admin/tasks/tasks_future/workshed-loom/BUILD_STATUS.md
Recovered handoff	admin/tasks/tasks_active/loom-to-engine/rabs_recovered_session_pack/

-- Reginald