[BUG] Reservation update/create crashes with unhandled FOREIGN KEY constraint (500) when place_id/day_id/end_day_id/assignment_id doesn't exist

Pre-flight checklist

• I have searched existing issues and this bug has not been reported yet (related but distinct: #522, see below)
• I am running the latest available version of TREK
• I have read the Troubleshooting guide and my issue is not covered there

TREK version

4.2.1

Describe the bug

PUT /api/trips/:tripId/reservations/:id (and POST .../reservations) can crash with an unhandled SqliteError: FOREIGN KEY constraint failed → raw 500, instead of the clean 400 Not part of this trip: ... that ReservationsController.rejectForeignReferences / ReservationsService.referencesOutsideTrip is meant to produce.

referencesOutsideTrip (server/src/nest/reservations/reservations.service.ts) only rejects an id when it exists but belongs to a different trip:

const elsewhere = (table, id) => {
  const row = this.db.get(`SELECT trip_id FROM ${table} WHERE id = ?`, id);
  return !!row && String(row.trip_id) !== String(tripId);
};

An id that resolves to nothing at all is not treated as an offender, so it sails through this guard. That's the intended, documented behaviour for accommodation_id (fixed in #522 — day_accommodations cascades away on delete, and healing to null keeps the booking editable). But the same lenient elsewhere() helper is also used for day_id, end_day_id, place_id, and the assignment_id check — and unlike accommodation_id, these four are real foreign keys in schema.ts:

day_id INTEGER REFERENCES days(id) ON DELETE SET NULL,
end_day_id INTEGER REFERENCES days(id) ON DELETE SET NULL,
place_id INTEGER REFERENCES places(id) ON DELETE SET NULL,
assignment_id INTEGER REFERENCES day_assignments(id) ON DELETE SET NULL,

(and the same problem exists for create_accommodation.place_id / start_day_id / end_day_id, which back an INSERT INTO day_accommodations with NOT NULL REFERENCES columns.)

So a request body carrying a place_id (etc.) that never existed for this trip — not "deleted", just absent/wrong — passes the guard, reaches the raw UPDATE/INSERT, and SQLite's own FK enforcement throws. The exception isn't caught, so it surfaces as a bare 500 with no actionable message.

Steps to reproduce

1. Create a trip.
2. Send a reservation create/update request whose place_id (or day_id / end_day_id / assignment_id) does not correspond to any existing row at all — e.g. a stale id from a client-side race (place lookup/save not yet committed when the reservation save fires), or simply a malformed/garbage id.
3. Observe 500 Internal Server Error instead of a validation error.

In our case it happened organically through normal use: create a trip → search a place via the Google Places integration (autocomplete → place details → photos) → save it onto a reservation. Something in that sequence produced a place_id that didn't exist yet when the reservation PUT landed.

Expected behavior

Same contract rejectForeignReferences already promises for the "wrong trip" case: a clean 400 { error: "Not part of this trip: place_id" } (or similar), never a raw 500/unhandled SqliteError.

Deployment method

Docker Compose

Host OS

macOS 26 (Apple Silicon)

Accessing TREK from

Desktop browser

Browser (if applicable)

Chrome 141

Relevant logs or error output

Unhandled error: SqliteError: FOREIGN KEY constraint failed
    at DatabaseService.run (/app/server/dist/nest/database/database.service.js:45:39)
    at ReservationsService.updateInTx (/app/server/dist/nest/reservations/reservations.service.js:598:17)
    at /app/server/dist/nest/reservations/reservations.service.js:527:47
    at /app/server/dist/nest/database/database.service.js:49:44
    at sqliteTransaction (/app/node_modules/better-sqlite3/lib/methods/transaction.js:65:24)
    at DatabaseService.transaction (/app/server/dist/nest/database/database.service.js:49:58)
    at ReservationsService.update (/app/server/dist/nest/reservations/reservations.service.js:527:24)
    at ReservationsController.update (/app/server/dist/nest/reservations/reservations.controller.js:72:73)
    at /app/node_modules/@nestjs/core/router/router-execution-context.js:38:29
    at process.processTicksAndRejections (node:internal/process/task_queues:104:5) {
  code: 'SQLITE_CONSTRAINT_FOREIGNKEY'
}
[ERROR] 2026-09-13T07:33:29 PUT /api/trips/1/reservations/1 500 17ms

Additional context

Related: #522, which fixed the same crash for accommodation_id specifically (dangling reference after cascade-delete → healed to null). That fix is correct and should stay as-is — accommodation_id genuinely has no FK, so "doesn't exist" is a legitimate, non-offending state for it. This report is about the four actual foreign keys (day_id, end_day_id, place_id, assignment_id, plus the create_accommodation.* trio) that share the same referencesOutsideTrip guard but were left with the same "missing id passes through" gap that #522 fixed only for accommodation_id.

I have a small, tested fix ready locally (extends the guard so a missing id is also an offender for these specific fields, leaving accommodation_id's behavior untouched; added a regression test RESV-SCOPE-006 covering all five fields). Happy to open a PR against dev if that's welcome — will follow the Discord-first contribution flow per CONTRIBUTING.md rather than dropping a PR unannounced.