Do not allow more than one substitution to be assigned to a teacher in a specific period

[nemvince]

Write checks for this

[Dasa122]

@coderabbitai plan

[coderabbitai]

Coding Plan

Summary

• Implement a reusable helper function that queries existing substitutions for a teacher on a specific date, retrieves their associated lesson periods, and detects overlap with the incoming lesson periods
• Integrate this validation into both create and update handlers, positioned after existing validation checks but before any database mutations
• Follow established codebase patterns: batch queries with inArray, no SQL JOINs, pre-transaction validation, and HTTPException for errors with appropriate status codes

Implementation Steps

Add Teacher-Period Conflict Validation to Substitution Handlers

This phase introduces server-side enforcement to prevent a teacher from being double-booked as a substituter for the same period on the same date. The validation will be implemented as a reusable helper function and integrated into both the create and update handlers, following the existing pattern of pre-transaction validation checks.

Task 1: Create Conflict Detection Helper Function

Add a helper function to detect when a teacher is already assigned as a substituter for a given period on a given date.

• Add a new async function (e.g., checkTeacherSubstitutionConflict) in apps/chronos/src/routes/timetable/substitution.ts
• Function parameters: database instance, date, substituter ID, lesson IDs to be assigned, and an optional exclusion ID for updates
• Early return if substituter is null (cancelled lessons have no teacher conflict)
• Query the lesson table to retrieve period IDs for the incoming lesson IDs using inArray
• Query the substitution table to find existing substitutions matching the same date and substituter, excluding the current substitution ID when provided
• Query substitution_lesson_mtm to get lesson IDs linked to those existing substitutions
• Query the lesson table again to get period IDs for those linked lessons
• Compare period ID sets; if any overlap exists, throw an HTTPException with status 409 CONFLICT

Task 2: Integrate Conflict Check into Create Handler

Add the conflict validation to the createSubstitution handler before the transactional insert.

• In apps/chronos/src/routes/timetable/substitution.ts, locate the createSubstitution handler (around line 484)
• Insert a call to the conflict detection helper immediately after the existing lesson-existence check (after line 493) and before the db.transaction() call
• Pass the date, substituter, and lessonIds from the validated request body; no exclusion ID needed for creates

Task 3: Integrate Conflict Check into Update Handler

Add the conflict validation to the updateSubstitution handler, accounting for partial updates.

• In apps/chronos/src/routes/timetable/substitution.ts, locate the updateSubstitution handler (around line 584)
• Insert a call to the conflict detection helper after the existing lesson-existence check (after line 611) and before cancelPendingNotification
• Resolve effective values for the check: use the request body's date, substituter, and lessonIds when provided, otherwise fall back to the existing substitution's values fetched earlier in the handler
• Pass the current substitution's id as the exclusion parameter to avoid self-conflict detection
• Only perform the check if there is a non-null substituter (either new or existing)

🤖 Prompt for AI agents

This phase adds server-side teacher-period conflict validation to the
substitution handlers in `apps/chronos/src/routes/timetable/substitution.ts`.
The goal is to prevent a teacher from being assigned as a substituter for
multiple lessons in the same period on the same date, enforced in both create
and update flows.

**Task 1: Create `checkTeacherSubstitutionConflict` helper function**
- Add a new async function named `checkTeacherSubstitutionConflict` in
`apps/chronos/src/routes/timetable/substitution.ts`
- Parameters: database instance, `date`, substituter ID, lesson IDs to be
assigned, and an optional exclusion substitution ID (for updates)
- Early return immediately if `substituter` is null (no conflict possible for
cancelled lessons)
- Query the `lesson` table using `inArray` to retrieve period IDs for the
incoming lesson IDs
- Query the `substitution` table for existing records matching the same `date`
and `substituter`, excluding the record with the exclusion ID if provided
- Query `substitution_lesson_mtm` using `inArray` to get lesson IDs linked to
those existing substitutions
- Query the `lesson` table again using `inArray` to get period IDs for those
linked lessons
- Compare the two sets of period IDs; if any overlap is found, throw an
`HTTPException` with status `409 CONFLICT`

**Task 2: Integrate into `createSubstitution` handler**
- Locate the `createSubstitution` handler (around line 484)
- After the existing lesson-existence check (after line 493) and before the
`db.transaction()` call, insert a call to `checkTeacherSubstitutionConflict`
- Pass `date`, `substituter`, and `lessonIds` from the validated request body;
do not pass an exclusion ID

**Task 3: Integrate into `updateSubstitution` handler**
- Locate the `updateSubstitution` handler (around line 584)
- After the existing lesson-existence check (after line 611) and before
`cancelPendingNotification`, insert a call to `checkTeacherSubstitutionConflict`
- Resolve effective values: use the request body's `date`, `substituter`, and
`lessonIds` if provided, otherwise fall back to the existing substitution's
values already fetched in the handler
- Pass the current substitution's `id` as the exclusion parameter to prevent
self-conflict detection
- Only invoke the check when the effective substituter is non-null

Research

The project is a monorepo with apps/chronos (Hono/Bun backend API) and apps/iris (React frontend). Substitutions are managed via the substitution table (with id, date, substituter FK to teacher, and comment) and a substitution_lesson_mtm join table linking substitutions to lessons. Each lesson has a periodId foreign key to the period table, which contains the ordinal period number. Currently, the getSubstitutionCandidates endpoint provides advisory conflict checking for the UI, but the actual createSubstitution and updateSubstitution handlers perform no server-side enforcement of teacher-period conflicts for existing substitutions.

---

🚀 Next Steps

🤖 All AI agent prompts combined

This phase adds server-side teacher-period conflict validation to the
substitution handlers in `apps/chronos/src/routes/timetable/substitution.ts`.
The goal is to prevent a teacher from being assigned as a substituter for
multiple lessons in the same period on the same date, enforced in both create
and update flows.

**Task 1: Create `checkTeacherSubstitutionConflict` helper function**
- Add a new async function named `checkTeacherSubstitutionConflict` in
`apps/chronos/src/routes/timetable/substitution.ts`
- Parameters: database instance, `date`, substituter ID, lesson IDs to be
assigned, and an optional exclusion substitution ID (for updates)
- Early return immediately if `substituter` is null (no conflict possible for
cancelled lessons)
- Query the `lesson` table using `inArray` to retrieve period IDs for the
incoming lesson IDs
- Query the `substitution` table for existing records matching the same `date`
and `substituter`, excluding the record with the exclusion ID if provided
- Query `substitution_lesson_mtm` using `inArray` to get lesson IDs linked to
those existing substitutions
- Query the `lesson` table again using `inArray` to get period IDs for those
linked lessons
- Compare the two sets of period IDs; if any overlap is found, throw an
`HTTPException` with status `409 CONFLICT`

**Task 2: Integrate into `createSubstitution` handler**
- Locate the `createSubstitution` handler (around line 484)
- After the existing lesson-existence check (after line 493) and before the
`db.transaction()` call, insert a call to `checkTeacherSubstitutionConflict`
- Pass `date`, `substituter`, and `lessonIds` from the validated request body;
do not pass an exclusion ID

**Task 3: Integrate into `updateSubstitution` handler**
- Locate the `updateSubstitution` handler (around line 584)
- After the existing lesson-existence check (after line 611) and before
`cancelPendingNotification`, insert a call to `checkTeacherSubstitutionConflict`
- Resolve effective values: use the request body's `date`, `substituter`, and
`lessonIds` if provided, otherwise fall back to the existing substitution's
values already fetched in the handler
- Pass the current substitution's `id` as the exclusion parameter to prevent
self-conflict detection
- Only invoke the check when the effective substituter is non-null

💡 Iterate on the plan with: `@coderabbitai` <feedback>

Example Feedback
- `@coderabbitai` You can skip phase 3. Add a simple unit test case for phase 2.
- `@coderabbitai` For design choice 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!