18 SEPTEMBER 2026 · dbMigrations + capExpertAPI + capExpertApp · project view, tab 3

Scheduling Assistant — schema and rollout

Four new tables carry a real day-by-day schedule: which facilities a wave covers, which days each is visited, and who is on site for how many hours. Six business rules are enforced by the database itself, not by application code. The old roster table is retired. Nothing here is built yet — every constraint was proven on a live database first.

New tables: 4schedules · visits · visit members · blackout dates
Rules enforced in the DB: 6two uniques, one exclusion constraint, three triggers
Statements in the attack run: 3320 rejected for the right reason, 13 accepted
Tables retired: 1capture_project_assignments — 0 live rows

the short answer

A schedule is a wave of onsite work. A visit is one facility on one day. A member row is one person on that visit, with the hours they work there. Everything the brief asks for falls out of those three grains plus a blackout table: a facility sits in one schedule per project, one visit row per facility per day, a person appears once per facility-day, is never over eight hours a day across every project, and is never on a closed day.

The cap is a variable — a constant 8 for now, in the trigger function and in capture-scheduling.constants.ts — and moves to the system settings table after the final discussion. No table or column holds it.

How to read this site: this page is the plan. Tables in detail has every key and constraint; Columns only is the same tables with just the columns; Proven on the database is the attack run; The screens and the API is what the schema drives.

capture_projects
  └─ capture_schedules                 ← a wave of onsite work: window, lead, CS- code
       └─ capture_visits               ← one facility on one date, planned hours
            └─ capture_visit_members   ← one person on that visit, their hours
capture_projects
  └─ capture_project_blackout_dates    ← one row per closure

Baseline — three tableswhat exists

The feature owns exactly three tables today. Nothing else in the schema is part of it.

tablefate

tablecapture_projectsfateKept as is. id BIGINT serves every FK and the build-team URL (?project=1); project_code (CP-…, unique) serves the dashboard URL. The dead blackout_dates JSONB — never read or written — is dropped in the last phase.

tablecapture_project_sitesfateKept — these rows are the facilities. facility_account_id is nullable (a site is scheduled before it has an account), which is why the schedule references the site row, not the account. Gains one constraint, UNIQUE (id, capture_project_id), as an anchor for a composite FK.

tablecapture_project_assignmentsfateDropped in the last phase. One row, zero live; no FK, view, function or mobile reader depends on it. Its readers in the API and the front end are re-sourced first.

Both URL forms already resolve: the dashboard reads ?project=CP-8961C6T1 against project_code; build-team reads ?project=1 as the numeric id. No change.

Rules, and where each is enforcedR1–R6

Column detail lives on Tables in detail; the proof that each one holds is on Proven on the database.

ruleenforcement

ruleR1 · a project has many schedulesenforcementcapture_schedules.capture_project_id FK.

ruleR2 · a schedule covers many facilitiesenforcementEvery capture_visits row names its schedule and its facility; a schedule’s facilities are the distinct sites of its live visits.

ruleR3 · a facility is in one schedule per project; the same facility may appear in another projectenforcementEXCLUDE USING gist (capture_project_site_id WITH =, capture_schedule_id WITH <>) on capture_visits — no two live rows may name the same facility with different schedules. A site row belongs to one project, so this is “one schedule per project” by construction. Two composite FKs stop a visit mixing a schedule and a site from different projects.

ruleR4 · facility ↔ date is many-to-many inside a scheduleenforcementcapture_visits: one row per facility per day, UNIQUE (capture_project_site_id, visit_date) where live. Many days per facility and many facilities per day are free.

ruleR5a · a person appears once per facility-dayenforcementUNIQUE (capture_visit_id, user_id) where live, on capture_visit_members. No time of day is stored, so “two places at once” is not checked by time — R5b is the double-booking guard.

ruleR5b · a person’s hours that day, across every facility, schedule and project, stay within the capenforcementA deferred constraint trigger: a plain UNIQUE or EXCLUDE cannot sum. It sums the person’s live rows’ hours for the day — one table, no project filter, so the sum spans every project — and compares with the cap variable. An advisory lock on (person, day) serialises concurrent writers so two saves cannot both squeeze in.

ruleR6 · no visit on a blackout dateenforcementcapture_project_blackout_dates, one row per closure, plus two triggers: a visit may not land on a closed day or outside its schedule window, and a new or widened closure may not swallow an existing visit.

Decision log18 September, in review

Each with its reason, so it is not re-litigated.

decisioninstead of, and why

decisionTeam members are rows (capture_visit_members)whyInstead of an INTEGER[] of user ids. An array has no per-element FK, cannot carry hours, cannot be indexed by person, and R5 is unenforceable against it.

decisionBlackouts at project level, one row per closure, read at write timewhyInstead of copies per schedule or the intake’s single A7 range. Copies drift when closures change after schedules exist; A7 holds one range and a project may have several — “7–8 Oct and 10 Oct” is two rows.

decisionFacilities are capture_project_sites rowswhyInstead of accounts. facility_account_id is nullable — a site is scheduled before it has an account.

decisionFour tables, not five — the schedule ↔ facility pairing folded into capture_visitswhyInstead of a pairing table with a unique on the facility. R3 is enforced with a <> exclusion on the visit rows; one fewer join everywhere; nothing needed a per-(schedule, facility) attribute. Trade-off: a facility with no visit day yet is a selection on screen, not a stored row.

decisionMember rows carry hours only — no start_time / end_timewhyInstead of a time window per person with a tsrange exclusion for overlaps. Simpler rows and screens, no time pickers. Consequence accepted: an overlap in time is not detected; the daily cap is the only double-booking guard. If times return, a tsrange exclusion on this one table is the only addition.

decisionThe cap is global and a variable — CONSTANT 8 in the trigger, CAPTURE_DAILY_HOURS_CAP in the APIwhyInstead of a daily_hours_cap column on capture_projects, or the shared settings table now. The rule is about a person’s day across all projects, so a per-project value is ambiguous; settings is not part of this feature yet. The later move is one CREATE OR REPLACE FUNCTION migration plus one read of the Setting entity — no schema change.

decisionThe cap is per calendar day, by constructionwhyInstead of special handling for overnight shifts. Hours belong to a visit and a visit is one calendar day; an overnight shift is hours on two visits.

decisioncapture_project_assignments is droppedwhyInstead of reusing it as the roster. Membership becomes “holds at least one live member row”; the lead is capture_schedules.lead_user_id per wave plus the existing project_lead_id; throughput_snapshot goes — the resolver already prefers the measured rate, then the profile value, then the default.

decisionSchedules carry schedule_capex_id (CS-XXXXXXXX); no namewhyInstead of the hashed fake CAP-xxxx the UI derives today, and an optional label. Issued by the same generate_capex_unique_id trigger as CP- codes, never reused; the code identifies a wave.

Dropping the roster tablewhat has to move first

capture_project_assignments is the roster’s only home today, so four things are re-sourced before it goes.

todaybecomes

today“On the team” means a roster row, dated or notbecomesA person is on a project when they hold at least one live member row. The existing PUT team keeps working as a shim: an undated entry is expanded into one row per schedulable day of the schedule window, minus blackouts, with the visit’s hours.

todayrole = lead on a roster rowbecomescapture_schedules.lead_user_id per wave, plus the existing capture_projects.project_lead_id. The role column and its enum go.

todaythroughput_snapshot frozen at hire timebecomesDropped. The throughput resolver already prefers the measured sticker rate, then the profile value, then the default; the snapshot has no reader.

todayEight service methods, one card projection and one dashboard filter read the rosterbecomesRe-sourced from visits and members. The single-project, application-level overlap check is deleted — the database enforces the cap, across projects.

Rollout — five phasesexpand → migrate → verify → contract

Each phase is its own commit in its own repository. Nothing destructive happens before phase 5.

1 · Expand — dbMigrations. One migration, one transaction: CREATE EXTENSION IF NOT EXISTS btree_gist, the unique on capture_project_sites, the four tables with their indexes and the exclusion constraint, the CS- trigger, the three guard functions and triggers. Canonical copies of the functions go in triggers-and-functions/. Blackouts are back-filled from the intake’s A7 answer where valid. The migration asserts the roster table has zero live rows and stops if not. Purely additive — the running API is unaffected. Gate: applies and reverts on the local database; the attack script’s data section passes.

2 · Backend — capExpertAPI. Four entities; the scheduling service re-sourced; a new schedule service and controller routes for schedules, availability and blackout dates; the card projection and dashboard filter moved; new messages; CAPTURE_DAILY_HOURS_CAP = 8 in the constants file, returned by the availability route. The PUT team shim keeps the old front end working. Gates: npm run typecheck, npm run lint:layering, npm run quality.

3 · Frontend — capExpertApp. Regenerate the swagger client (and restart ng serve). Build-team posts visits and member hours instead of collapsing days to windows, gets a per-person hours field and a remaining-hours chip. The roster table lists real schedules with CS- codes; Edit and Delete become real. The preview shows real hours. Gate: the development build at the 5 GB heap.

4 · Verify. The attack script’s data section against the migrated database; an API smoke that creates a schedule, hits a blackout day, an over-cap hour on the same and on another project, and a reused facility, and gets the right 400 for each, then deletes the schedule and sees the facility freed; the front-end build green and the chip matching the cap − SUM(hours) query.

5 · contract — separately authorised, after phase 4 passes on the target environment

DROP TABLE capture_project_assignments and ALTER TABLE capture_projects DROP COLUMN blackout_dates, guarded by a RAISE if any live roster row exists. The down-migration recreates both, but the data is not recoverable — acceptable because there are zero live rows today; re-check on every environment first. Then delete the entity and stop. No further cleanup in the same change.

Trapsthe ones that type-check

The cap trigger is deferred. Its error surfaces at COMMIT, so the service maps errors around transaction.commit(), not only around the insert. Test scripts must SET CONSTRAINTS ALL IMMEDIATE or commit.

The advisory-lock pattern assumes READ COMMITTED — Sequelize’s default. Under REPEATABLE READ a concurrent writer’s committed rows would be invisible to the sum.

The <> exclusion needs btree_gist. Without it: “data type integer has no default operator class for access method gist”. The extension is trusted on PostgreSQL 13+, so no superuser, but the migrating role needs CREATE on the database.

Deleting a schedule must soft-delete its visits and member rows in the same transaction. A live visit left behind keeps the facility locked to the dead schedule through the <> exclusion.

Two copies of the cap variable until the settings move — the trigger function and the TypeScript constant. Change both in one PR; the verification script pins the database value by attempting the ninth hour.

Moving a visit re-fires the cap check on its member rows through ON UPDATE CASCADE — intended; the move fails if anyone would go over the cap on the new day.

bulkCreate never uses ignoreDuplicates. A swallowed unique violation is a silent duplicate row; let it throw and map it.

Swagger regeneration rewrites src/swagger (gitignored). Restart ng serve; the mid-rewrite errors are not real.

Out of scopenamed so it stays out

Mobile — no reader of these tables. Per-facility blackouts, per-person caps, and time of day on member rows (an overlap check by time) — each a contained follow-up noted above. Range-based booking with daterange — considered and rejected: a range implicitly claims weekends and closed days, and per-day hours do not fit it.