Core Engine Schema — core_engine
The scheduling core of RABS lives in the core_engine Postgres schema. It replaced the earlier parallel scheduling systems (workshed, loom, program_templates) with a single clean schema of 19 tables, a dedicated instance API at /api/v1/engine, and a fully-wired program creation wizard.
The previous docs in this section (Workshed + Loom, Program Wizard, Payment/Python/History Ribbon) remain as historical context — the thinking they describe evolved into the design documented here.
Conceptual Model
Six concepts underpin the entire engine:
| Concept | Meaning |
|---|---|
| Sources | The filing cabinets. Participant files, staff files, vehicle files, venue files. Without these, nothing operational can be built. Live in core_source. |
| Programs | Blueprints. They describe what normally happens. Not the future and not the past — the current intended shape of the repeating thing. |
| Intents | The calendar. A date-specific modification that does not rewrite the blueprint: a participant cancelled this Tuesday only, a bus unavailable for two weeks, an alternate pickup address, a billing override. |
| Instances | The living thing. A program on a specific date after matching intents have been applied. Mutable until the day starts or the instance is locked. |
| The Active Window | Destructible and rebuildable. Programs + intents + settings reproduce the future. The window can be 4 weeks or 16 weeks — future instances are projections, not truth. |
| Locked Instances | 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. |
Key principle: 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. Locked rows must denormalise the values that mattered at the time.
Schema Tables (19)
| Table | Purpose |
|---|---|
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.
Migration Scripts
| File | What it did |
|---|---|
20260420_core_engine_create.sql | Built the 19 tables in one transaction. Triggers, constraints, indexes. |
20260420_core_engine_patch.sql | 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. |
20260421_seed_billing_rates.sql | Replaced 11 POC junk rows in finance.billing_rates with 73 real NDIS-style codes. |
Instance API — /api/v1/engine
The engine route file (backend/routes_v1p/engine.js) is the operations-side counterpart to the wizard. The wizard builds the blueprint; the engine serves the live and locked instances.
Window
GET /api/v1/engine/window
Reads admin.app_settings for loom_window_fortnights (default 4 = 56 days) and returns the date list the engine considers active.
Instances
GET /api/v1/engine/instances
GET /api/v1/engine/instances/:id
PATCH /api/v1/engine/instances/:id
Attendance
GET /api/v1/engine/instances/:id/attendance
POST /api/v1/engine/instances/:id/attendance
PATCH /api/v1/engine/instances/:id/attendance/:aid
Staff
GET /api/v1/engine/instances/:id/staff
POST /api/v1/engine/instances/:id/staff
PATCH /api/v1/engine/instances/:id/staff/:sid
Vehicles
GET /api/v1/engine/instances/:id/vehicles
POST /api/v1/engine/instances/:id/vehicles
PATCH /api/v1/engine/instances/:id/vehicles/:vid
Routes & Timeslots & Billables
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
Locking
POST /api/v1/engine/instances/:id/lock
POST /api/v1/engine/instances/lock-date
When an instance locks, names, addresses, rates, and totals are denormalised onto the instance and its children. Future template edits or rate changes can never rewrite the past.
Artifacts & Tags
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
Backend Route Files
Twelve backend route files were rewritten to point at core_engine:
| File | Role |
|---|---|
templates.js (1671 lines) | Wizard backend — programs, slots, participants, billing, staff, vehicles, requirements, finalize. |
engine.js | Instance operations API (23 endpoints). |
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. |
What stayed in core_ops: shift notes, shift cover claims, shift density cache, vehicle/venue documents and inspections, and the Type 2 session tables.
Program Creation Wizard
The wizard at page_prog_create walks the user through six steps:
- Details — program name, type, venue, schedule days
- Time Slots — pickup, activity, meal, other, dropoff with click-to-edit and adjacent give/take
- Participants — selection with photos, address preferences, participant fees
- Billing — real billing rates from 73 seeded codes, group ratios auto-calculate from 1:1 base rate, NF2F sibling lines
- Staffing — staff placeholders (auto / manual / open)
- Vehicles — vehicle placeholders (auto / manual / personal car)
- Finalize — calls
util_syncRethreadandutil_generateBillingto project instances and billing lines into the active window
Key wizard features
- Real billing rates — 73 seeded NDIS-style codes in dropdowns, group ratios auto-calculate
- NF2F billing lines — each line has an NF2F button creating a sibling line tagged
NF2F, with manual hours and rate - Participant photos — served via extension-free route at
/api/v1/participants/:id/photo(probes for .png, .webp, .jpg, .jpeg) - Participant fees —
participant_feecolumn with checkbox and amount input - Click-to-edit time slots — editing a slot pushes against neighbours instead of overlapping
- Draft management — drafts can be deleted, POST returns full program row
What's Next
| Area | Status |
|---|---|
| Lock endpoint denormalisation testing | Endpoint exists, end-to-end snapshotting needs verification |
syncRethread child completeness | Needs to confirm all instance children are generated (attendance, shifts, vehicles, billables, routes) |
| Intent-application engine | intentions.js is CRUD-only — intents need side effects (cancel, swap venue, override times/billing) |
| Live operations frontend | page_workspace does not yet exist — the active workspace where today's instances are visible |
| Settings page rename | page_loom-settings → system-org settings page for threshold knobs |
| Template change history | Changelog for program blueprint changes |
Quick Reference
| Item | Location |
|---|---|
| Schema | core_engine.* (19 tables, DB2) |
| Wizard backend | backend/routes_v1p/templates.js |
| Instance API | backend/routes_v1p/engine.js → /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 |
| Blog post | From Loom to Engine |