18 SEPTEMBER 2026 · dbMigrations · scheduling assistant only
The four tables, column by column
Every column with what it holds, every key and why it is there. The runnable DDL lives beside the plan in capExpertApp/docs/; this page is that text, explained.
The shapethree grains and a calendar
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, with their hours
capture_projects
└─ capture_project_blackout_dates ← one row per closureThree layers because there are three grains. A wave is one thing per project. A facility worked on a day has planned hours and is a fact independent of who turns up. A person on that day works some number of hours there, and their hours across every facility and project that day are capped. The old pairing table between schedules and visits was folded into the visit row — see R3 below for what replaces its unique.
The two existing tableschanges only
id BIGINT is the FK target; project_code VARCHAR(20) UNIQUE is the CP- code the dashboard URL uses. blackout_dates JSONB is dead and is dropped in the contraction phase.UNIQUE (id, capture_project_id). id is already the primary key, so this is always satisfiable; it exists only so capture_visits can reference (site, project) as a pair and thereby prove the visit’s facility is in the visit’s project.Audit columnson all four
created_by INTEGER NOT NULL FK → users(id)
updated_by INTEGER NULL FK → users(id)
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
deleted_at TIMESTAMPTZ NULL -- soft delete. Every unique and exclusion rule is partial on
-- this, so remove-then-add creates a fresh row beside the old one1 · capture_schedulesR1
A wave of onsite work inside a project. Many per project.
id BIGSERIAL PK
schedule_capex_id VARCHAR(20) NOT NULL, UNIQUE -- 'CS-XXXXXXXX', set by the generate_capex_unique_id trigger;
-- the ID column in the UI. Not partial: a code is never reissued
capture_project_id BIGINT NOT NULL FK → capture_projects(id)
lead_user_id INTEGER NULL FK → users(id) -- lead capturer for this wave (the crown)
start_date DATE NOT NULL -- the window; every visit must fall inside
end_date DATE NOT NULL -- CHECK (end_date >= start_date)
status VARCHAR(20) NOT NULL DEFAULT 'draft' -- CHECK: draft | published | in_progress | completed | canceled
+ audit columns
UNIQUE (id, capture_project_id) -- anchor for the composite FK from visits
INDEX (capture_project_id) WHERE deleted_at IS NULL -- a project's schedules2 · capture_visitsR2 · R3 · R4
One facility on one date, inside one schedule. Many dates per facility, many facilities per date.
id BIGSERIAL PK
capture_schedule_id BIGINT NOT NULL -- the wave this visit belongs to
capture_project_id BIGINT NOT NULL -- the project of both, proven by the two FKs below
capture_project_site_id BIGINT NOT NULL -- the facility
visit_date DATE NOT NULL -- the calendar day of the onsite visit
hours NUMERIC(4,2) NOT NULL DEFAULT 8 -- PLANNED coverage for the facility that day; CHECK 0 < h <= 24.
-- Staffing is the members' hours; the UI shows "staffed 6 of 8 h"
notes VARCHAR(255) NULL -- e.g. "escort required after 15:00"
+ audit columns
FOREIGN KEY (capture_schedule_id, capture_project_id) → capture_schedules (id, capture_project_id)
FOREIGN KEY (capture_project_site_id, capture_project_id) → capture_project_sites (id, capture_project_id)
-- a visit cannot mix a schedule and a facility from different projects
EXCLUDE USING gist (capture_project_site_id WITH =,
capture_schedule_id WITH <>) WHERE (deleted_at IS NULL)
-- R3: every live visit of a facility names the same schedule.
-- A schedule's facilities = DISTINCT site over its live visits
UNIQUE (capture_project_site_id, visit_date) WHERE deleted_at IS NULL
-- R4: one visit row per facility per day
UNIQUE (id, visit_date) -- anchor so member rows cannot lie about their date
INDEX (capture_schedule_id) WHERE deleted_at IS NULL
INDEX (capture_project_id, visit_date) WHERE deleted_at IS NULL
TRIGGER capture_visits_guard BEFORE INSERT OR UPDATE OF visit_date, capture_schedule_id
-- R6: not on a blackout day; and inside the schedule windowWhy <>. A facility has many visit rows, so a plain unique on the facility is impossible; the pairing table used to carry that unique. An exclusion constraint with the inequality operator says “no two rows may share a facility while differing in schedule” — which is exactly R3, on the visit rows themselves. btree_gist provides <> for exclusion. When all of a facility’s visits are soft-deleted, the facility is released to another schedule; that is why deleting a schedule must soft-delete its visits too.
3 · capture_visit_membersR5
One person on one visit, with the hours they work there that day. One row per person per facility-day.
id BIGSERIAL PK
capture_visit_id BIGINT NOT NULL -- the visit (facility-day)
work_date DATE NOT NULL -- = the visit's date, pinned by the FK below
user_id INTEGER NOT NULL FK → users(id)
hours NUMERIC(4,2) NOT NULL -- hours this person works at that facility that day, typed in;
-- CHECK (hours > 0 AND hours <= 24) is only a physical bound
+ audit columns
FOREIGN KEY (capture_visit_id, work_date) → capture_visits (id, visit_date) ON UPDATE CASCADE
-- a row cannot claim another date; moving the visit moves its rows
UNIQUE (capture_visit_id, user_id) WHERE deleted_at IS NULL
-- one row per person per facility-day
CONSTRAINT TRIGGER capture_visit_members_daily_cap AFTER INSERT OR UPDATE OF hours, user_id, work_date, deleted_at
DEFERRABLE INITIALLY DEFERRED
-- R5: SUM(hours) for (user_id, work_date) over live rows <= the cap variable (8)
INDEX (user_id, work_date) WHERE deleted_at IS NULL -- "what is X doing on this date"Member rows carry hours only; start_time and end_time were dropped. “Two places at once” is therefore not checked by time — the daily cap is the double-booking guard: a person’s hours across every facility, schedule and project that day cannot exceed the cap. If times return, a tsrange exclusion on this one table is the only addition.
4 · capture_project_blackout_datesR6
Project-level closures. One row per closure; a project has as many rows as it needs.
id BIGSERIAL PK
capture_project_id BIGINT NOT NULL FK → capture_projects(id)
start_date DATE NOT NULL -- inclusive; a single day is start_date = end_date
end_date DATE NOT NULL -- CHECK (end_date >= start_date)
notes VARCHAR(255) NULL -- e.g. "inventory count", "holiday"
+ audit columns
INDEX (capture_project_id, start_date, end_date) WHERE deleted_at IS NULL
TRIGGER capture_blackout_dates_guard BEFORE INSERT OR UPDATE OF start_date, end_date, deleted_at
-- a new or widened closure cannot cover an existing live visit“Closed 7–8 October and 10 October” is two rows: (2026-10-07, 2026-10-08) and (2026-10-10, 2026-10-10). The guards check a date against all of a project’s live rows; the UI shows one chip per row and strikes every covered day out of the day rail. Overlapping rows are allowed and harmless.
The three triggerswhat each rejects
BEFORE INSERT/UPDATE on capture_visitsrejectsA
visit_date inside any live blackout of the visit’s project, and a visit_date outside its schedule’s start_date–end_date. Both raise check_violation with a message naming the date.BEFORE INSERT/UPDATE on capture_project_blackout_datesrejectsA live closure whose range covers an existing live visit of the same project — so a blackout can never be added underneath scheduled work.
constraint trigger, AFTER INSERT/UPDATE, deferredrejectsA day on which the person’s live rows sum to more than the cap — whether by a new row, or by raising
hours on an existing one. Takes pg_advisory_xact_lock(user_id, day) first so two concurrent saves cannot both pass. The cap is a variable: CONSTANT 8 inside the function until it moves to system settings — that move touches only this function.The extensionbtree_gist
CREATE EXTENSION IF NOT EXISTS btree_gist; — needed for capture_schedule_id WITH <> in the R3 exclusion constraint. Trusted on PostgreSQL 13+, so the database owner can create it without superuser, but the migrating role needs CREATE on the database. Not installed on the local database today; the migration creates it as its first statement, so a missing privilege fails fast rather than half-way.