18 SEPTEMBER 2026 · local PostgreSQL 16.15 · one transaction, rolled back

Every rule, attacked

The schema was applied to a live copy of the database, seeded on real project and site rows, and then given every input the rules forbid — and several they allow. Thirty-three statements; every rejection came from the constraint or trigger that owns the rule, and the transaction ended in ROLLBACK.

Statements
3320 must reject, 13 must succeed
Rejections
20each from its own constraint, no wrong-reason passes
Left behind
0tables, extension and fixtures all gone after rollback

How it was runrepeatable

cd capExpertApp/docs
psql -h localhost -d capClone -X -f capture-scheduling-assistant-validate.sql

The script begins with BEGIN, includes the DDL, sets SET CONSTRAINTS ALL IMMEDIATE so the deferred cap trigger fires per statement, seeds fixtures on project 1 and project 3, runs each attack inside a savepoint, and ends with ROLLBACK and a check that nothing persisted. Both scripts live beside the plan in capExpertApp/docs/.

R1 – R2 · schedules and facilities

caseresult
caseTwo schedules on project 1, one on project 3resultAccepted. Codes CS-… issued by the trigger.
caseSchedule 9001 visits site 1 on two days and sites B and C on one dayresultAccepted — three facilities in one wave, one of them on two dates, three of them on the same date.

R3 · one schedule per facility per project

caseresult
caseSite 1 visited under a second schedule of project 1resultRejected — capture_visits_site_one_schedule: “(1, 9002) conflicts with existing key (1, 9001)”.
caseThe same facility account, as a site row of project 3, under project 3’s scheduleresultAccepted — a different site row, so a different project’s facility.
caseAn unvisited project-1 site under a project-3 schedule, claiming project 3resultRejected by the site composite FK — “(9905, 3) is not present in capture_project_sites”.
caseSame, claiming project 1resultRejected by the schedule composite FK — “(9003, 1) is not present in capture_schedules”.
caseSoft-delete all of site 1’s visits, then visit it under the other scheduleresultAccepted — the facility is released.

R4 · one visit row per facility per day

caseresult
caseA second visit row for site 1 on 10 FebruaryresultRejected — capture_visits_site_date_unique.

R5 · the daily caphours only

caseresult
casePerson A: 2 h at facility 1 + 6 h at facility 2 on the same day (= 8)resultAccepted.
casePerson A: + 1 h at facility 3 (2 + 6 + 1 = 9)resultRejected — “user 1 is booked 9.00 hours on 2026-02-10, over the 8-hour daily cap”.
casePerson A: the same extra hour, but on a project-3 visitresultRejected — the sum spans every project.
casePerson A: the 2 h row updated to 3 h (3 + 6 = 9)resultRejected — the trigger fires on UPDATE OF hours too.
casePerson A: a second row on the same visitresultRejected — capture_visit_members_visit_user_unique: one row per person per facility-day.
casePerson B: one 8 h row on project 3resultAccepted.
casePerson B: + 1 h on project 1 (9 > 8)resultRejected across projects.
caseA single 9 h rowresultRejected — over the cap on its own.
casehours = 0 and hours = 25resultRejected — capture_visit_members_hours_check, the physical bound.
caseSoft-delete Person A’s 6 h row, then + 1 h at facility 3 (2 + 1)resultAccepted — the cap ignores deleted rows.

R6 · blackouts

caseresult
caseA visit on a closed day (16 February)resultRejected — “visit on 2026-02-16 falls in a blackout period for project 1”.
caseA new closure covering a day that already has a visitresultRejected — “blackout 2026-02-09..2026-02-10 covers an existing visit of project 1”.
caseSeveral closures on one project — rows 07–08 Oct and 10 Oct: visits on the 7th, 8th and 10thresultRejected, all three, each by its own row. The day rail’s query expands the rows to 2026-10-07 2026-10-08 2026-10-10.
case… visits on the 6th and the 9th, the gapsresultAccepted.
case… widen the 10 Oct row to 09–10 Oct after the 9th has a visitresultRejected by the blackout guard.
case… soft-delete the 10 Oct row, then visit the 10thresultAccepted — the day reopens.
caseA visit outside its schedule’s window (1 March against 2–20 February)resultRejected — “visit on 2026-03-01 is outside its schedule window”.

Integrity and release

caseresult
caseA member row claiming a different date from its visitresultRejected by the composite FK — the row cannot lie about its day, which is what the cap keys on.
caseMove a visit to another dateresultIts member rows follow (ON UPDATE CASCADE) and are re-checked.
caseFacilities per schedule, and remaining hours per person on a dateresultOne query each: DISTINCT site over a schedule’s live visits; 8 − SUM(hours) per person and day.
after the rollback

The final statement checks the database: the four tables are gone, btree_gist is not installed, and the fixture rows added to capture_project_sites no longer exist. All true. The database was as it was before the run.