Dispatch Availability Engine

This document specifies the Dispatch Availability Engine — a compound read model that surfaces crew and vehicle availability for the dispatch board. It composes data from Backoffice (eligibility: status, qualifications, absences, inspections) and Operations (scheduling: assignments, duty logs) via cross-schema SQL views.

Contexts involved: Backoffice (data source) · Operations (data source) · UI layer (read model consumer) DDD pattern: CQRS read model across bounded contexts (DDD §7.2) Hosted in: backoffice schema (tenant isolation via Hasura permission layer — DDD §2.1)

---

SQL Views

backoffice.crew_availability

-- Application-layer read model (hosted in backoffice schema)
-- Tenant isolation enforced at Hasura permission layer (x-hasura-tenant-id),
-- not in this view definition — see DDD §2.1.
-- Scope: one tenant, one date range
CREATE VIEW backoffice.crew_availability AS
SELECT
  cm.id AS crew_member_id,
  cm.tenant_id,
  cm.first_name,
  cm.last_name,
  cm.role,
  cm.status,
  cm.phone,

  -- Eligibility: qualifications valid?
  NOT EXISTS (
    SELECT 1 FROM backoffice.crew_qualifications cq
    WHERE cq.crew_member_id = cm.id
      AND cq.status IN ('EXPIRED', 'REVOKED')
  ) AS qualifications_valid,

  -- Eligibility: any expiring-soon qualifications?
  EXISTS (
    SELECT 1 FROM backoffice.crew_qualifications cq
    WHERE cq.crew_member_id = cm.id
      AND cq.status = 'EXPIRING_SOON'
  ) AS has_expiring_qualifications,

  -- Eligibility: transmission restriction (structured enum, not free-text)
  EXISTS (
    SELECT 1 FROM backoffice.crew_qualifications cq
    WHERE cq.crew_member_id = cm.id
      AND cq.restriction_type = 'AUTOMATIC_ONLY'
  ) AS automatic_only,

  cm.created_at,
  cm.updated_at

FROM backoffice.crew_members cm
WHERE cm.status = 'ACTIVE';

Query time resolves parameterized checks (date-range-dependent) via Hasura computed fields or inline subqueries, not baked into the view definition:

• Absence overlap: NOT EXISTS (SELECT 1 FROM backoffice.crew_absences ca WHERE ca.crew_member_id = cm.id AND ca.status = 'APPROVED' AND daterange(ca.start_date, ca.end_date, '[]') && daterange($target_start, $target_end, '[]'))
• Assignment conflict: NOT EXISTS (SELECT 1 FROM operations.leg_assignments la JOIN operations.service_legs sl ON sl.id = la.service_leg_id WHERE la.crew_member_id = cm.id AND tsrange(sl.scheduled_start, sl.scheduled_end) && tsrange($target_start, $target_end))
• EU-561 rest time (simplified daily rest heuristic): (SELECT MAX(cdl.log_time) FROM operations.crew_duty_logs cdl WHERE cdl.crew_member_id = cm.id AND cdl.event_type = 'DRIVING') + INTERVAL '11 hours' < $target_start — Note: This checks the regular 11h daily rest only. Phase 2 will address full EU Regulation (EC) 561/2006 compliance (reduced daily rest 3×/week, weekly 45h rest, fortnightly compensation, continuous driving 4.5h limit).

backoffice.vehicle_availability

-- Tenant isolation enforced at Hasura permission layer — see DDD §2.1.
CREATE VIEW backoffice.vehicle_availability AS
SELECT
  v.id AS vehicle_id,
  v.tenant_id,
  v.license_plate,
  v.model,
  v.vehicle_class,
  v.status,
  v.transmission_type,
  v.capacity,
  v.current_mileage_km,

  -- Compliance: any blocking inspections?
  EXISTS (
    SELECT 1 FROM backoffice.vehicle_inspections vi
    WHERE vi.vehicle_id = v.id
      AND vi.blocks_dispatch = true
  ) AS dispatch_blocked,

  -- Warning: any overdue (non-blocking) inspections?
  EXISTS (
    SELECT 1 FROM backoffice.vehicle_inspections vi
    WHERE vi.vehicle_id = v.id
      AND vi.status = 'OVERDUE'
      AND vi.blocks_dispatch = false
  ) AS has_overdue_inspections

FROM backoffice.vehicles v
WHERE v.status = 'ACTIVE';

Query time resolves assignment conflict: NOT EXISTS (SELECT 1 FROM operations.leg_assignments la JOIN operations.service_legs sl ON sl.id = la.service_leg_id WHERE la.vehicle_id = v.id AND tsrange(sl.scheduled_start, sl.scheduled_end) && tsrange($target_start, $target_end))

---

GraphQL Query Contracts

The dispatch board consumes the availability data via Hasura.

crewAvailability

Field	Value
Auth	MANAGER or DISPATCHER role (or DISPATCH capability)
Input	{ tenant_id: UUID (from JWT), target_start: Timestamp, target_end: Timestamp, role_filter?: DRIVER | GUIDE | DRIVER_GUIDE }
Output	[{ crew_member_id, first_name, last_name, role, qualifications_valid, has_expiring_qualifications, automatic_only, is_on_leave, has_assignment_conflict, rest_time_sufficient, availability_status: AVAILABLE | WARNING | BLOCKED }]
Mechanism	Hasura computed fields on crew_members or tracked SQL view with parameterized where clause

vehicleAvailability

Field	Value
Auth	MANAGER or DISPATCHER role (or DISPATCH capability)
Input	{ tenant_id: UUID (from JWT), target_start: Timestamp, target_end: Timestamp, vehicle_class_filter?: VehicleClass, min_capacity?: Int }
Output	[{ vehicle_id, license_plate, model, vehicle_class, capacity, transmission_type, dispatch_blocked, has_overdue_inspections, has_assignment_conflict, availability_status: AVAILABLE | WARNING | BLOCKED }]

---

Conflict Detection Rules

The dispatch board applies a three-tier conflict system:

Tier	Meaning	UI	Override
🔴 BLOCKED	Cannot assign. Hard constraint.	Red row, assignment button disabled	Not overridable
🟡 WARNING	Can assign with risk. Soft constraint.	Yellow row, assignment button enabled with confirmation dialog	Dispatcher must confirm + provide reason (logged to change_events)
🟢 AVAILABLE	No conflicts.	Normal row	N/A

Crew Conflict Rules

Rule	Check	Tier	Rationale
Inactive/Terminated	crew_members.status ≠ ACTIVE	🔴	Not in the view at all
Qualification expired/revoked	Any crew_qualifications.status IN (EXPIRED, REVOKED) for required types	🔴	Legal compliance — cannot drive without valid LICENSE_D/MODULE_95
On approved leave	crew_absences overlaps target date range with status = APPROVED	🔴	Contractual obligation
Assignment conflict	Existing leg_assignment overlaps target time window	🔴	Physical impossibility — one person, one location
Insufficient rest time (EU-561)	Last DRIVING log + 11h > target_start (daily rest)	🔴	Legal compliance — €2,000+ fine per violation. Phase 1: simplified daily rest heuristic only.
Qualification expiring soon	Any crew_qualifications.status = EXPIRING_SOON	🟡	Valid today but may expire during or before the trip
Transmission restriction	crew_qualifications.restriction_type = 'AUTOMATIC_ONLY' AND target vehicle transmission_type = MANUAL	🔴	Cannot operate the assigned vehicle
Pending absence request	crew_absences overlaps target date range with status = REQUESTED	🟡	Leave not yet approved but may be

Vehicle Conflict Rules

Rule	Check	Tier	Rationale
Non-active	vehicles.status ≠ ACTIVE	🔴	Not in the view at all
Dispatch-blocking inspection	vehicle_inspections.blocks_dispatch = true	🔴	Overdue HU/SP — unroadworthy
Assignment conflict	Existing leg_assignment overlaps target time window	🔴	One vehicle, one route
Overdue non-blocking inspection	vehicle_inspections.status = OVERDUE AND blocks_dispatch = false	🟡	Non-safety inspection overdue (e.g., exhaust test)
Capacity mismatch	vehicles.capacity < required_pax	🟡	Possible overbooking

---

Performance Considerations

Dimension	Expected Scale	Strategy
Crew per tenant	5–50 (SMB)	Live joins sufficient; no materialized view needed
Vehicles per tenant	3–30 (SMB)	Live joins sufficient
Legs per month	50–500	Index on (tenant_id, scheduled_start, scheduled_end) on service_legs
Concurrent dispatchers	1–3 per tenant	No caching layer needed; Hasura subscriptions for real-time updates
Duty logs per crew/month	~60 (2/day)	Index on (crew_member_id, log_time DESC)

Indexing strategy:

• backoffice.crew_absences: GiST index on daterange(start_date, end_date) with crew_member_id and status — required for efficient && (overlap) operator evaluation on range types. B-tree cannot accelerate range overlap queries.
• backoffice.crew_qualifications: (crew_member_id, status) — covers the expiry check
• backoffice.vehicle_inspections: (vehicle_id, blocks_dispatch) — covers the dispatch block check
• operations.leg_assignments: (crew_member_id), (vehicle_id) — covers the conflict check
• operations.crew_duty_logs: (crew_member_id, log_time DESC) — covers the rest time check

---

Edge States

#	Edge Case	Resolution	Enforcement
E1	Concurrent assignment: two dispatchers assign the same crew member	Optimistic concurrency via unique constraint on (crew_member_id, service_leg_id) on leg_assignments. Second INSERT fails with 409 CONFLICT. Frontend receives error → refreshes availability view (Hasura subscription auto-updates).	Database — unique constraint. Frontend — error handling + retry UX.
E2	Absence approved while crew member has active future assignments	CrewAbsenceApproved event triggers a conflict scan in Operations. If overlapping leg_assignments exist: (1) the system flags affected assignments with a conflict status, (2) dispatcher receives a notification listing affected legs, (3) dispatcher reassigns manually. No auto-reassignment in Phase 1.	Backend — Hasura Event Trigger on crew_absences.status → APPROVED. NestJS handler scans leg_assignments for overlap. Communications emits manager notification.
E3	Stale availability data in multi-tab or multi-dispatcher scenarios	Hasura GraphQL subscriptions push real-time updates to the dispatch board. Creating, updating, or deleting a leg_assignment triggers an automatic re-evaluation of the availability view. No manual refresh needed. For edge cases where the system delays a subscription (< 1s typical): the INSERT constraint (E1) prevents double-assignment.	Frontend — Hasura subscriptions. Backend — DB constraints as safety net.
E4	EU-561 rest time: tachograph data missing or delayed	If no crew_duty_log exists for a crew member in the last 24h, the system shows REST_TIME_UNKNOWN warning (🟡), not BLOCKED. The system warns dispatchers that the system cannot verify compliance, but they can still proceed. Log entry: "EU-561 compliance check skipped: no telemetry data available for crew_member_id={id} in last 24h". Phase 1: simplified 11h daily rest heuristic only.	Application layer — NestJS availability resolver returns REST_TIME_UNKNOWN status. Frontend shows warning badge.
E5	Subcontracted legs (ADR-007) and availability	Subcontracted leg_assignments (where supplier_id IS NOT NULL) do not consume internal crew/vehicle availability. The system excludes them from the assignment conflict checks since crew_member_id and vehicle_id are both NULL. The availability views correctly handle this via the existing nullability.	Schema — nullable FKs per ADR-007. Query — WHERE la.crew_member_id = cm.id naturally excludes null rows.
E6	Crew member status changes while user has the dispatch board open	CrewMemberStatusChanged and VehicleStatusChanged domain events (defined in event-catalog.md) trigger Hasura subscriptions. The availability view filters on status = 'ACTIVE', so a status change instantly removes/adds the resource from the dispatcher's view.	Backend — Hasura Event Trigger emits domain event. Frontend — subscription-driven reactivity.
E7	Date range spanning midnight / multi-day tours	The availability check uses tsrange / daterange overlap operators, which correctly handle multi-day spans. A crew member on a 5-day tour remains blocked for all 5 days. Absences use daterange (date-level granularity), assignments use tsrange (timestamp-level). No special handling needed.	SQL — PostgreSQL range operators.

---

Schema Cross-References

Table	Schema Doc	Detail
crew_members	schema-backoffice.md	status (ACTIVE filter), role (DRIVER/GUIDE/DRIVER_GUIDE)
crew_qualifications	schema-backoffice.md	status enum, restriction_type enum, qualification catalog
crew_absences	schema-backoffice.md	start_date/end_date range, status (APPROVED blocks)
vehicles	schema-backoffice.md	status, vehicle_class, capacity, transmission_type
vehicle_inspections	schema-backoffice.md	blocks_dispatch flag
leg_assignments	schema-operations.md	Assignment conflict check, UNIQUE(crew_member_id, service_leg_id)
crew_duty_logs	schema-operations.md	EU-561 rest time calculation
service_legs	schema-operations.md	scheduled_start/scheduled_end for time window overlap

Phase 2 backlog:

• Full EU-561/2006 compliance (weekly/fortnightly rest, reduced rest allowances, continuous driving limits)