ticket_watchers: ticket_id type mismatch (int vs varchar) and missing FK to tickets #32

New Issue

2026-08-31T21:29:41-04:00

jared commented

2026-08-31 21:29:41 -04:00

Severity: High (data-integrity risk)

ticket_watchers (migrations/000_baseline.sql, ~lines 245-251) stores ticket_id as int(11), while every other satellite table (ticket_comments, ticket_attachments, ticket_dependencies, custom_field_values) stores it as varchar(9)/varchar(10) matching tickets.ticket_id. There's also no FK constraint from ticket_watchers.ticket_id to tickets.ticket_id at all — every other satellite table has one.

api/watch_ticket.php (~lines 37-49) and helpers/NotificationHelper.php (~lines 197-200) cast/bind ticket IDs as ints here, which only works today because ticket IDs happen to be numeric-only 9-digit strings that fit in int(11). If the ID format ever gains a non-numeric character (e.g. a prefix), this silently truncates/corrupts IDs, and orphaned watcher rows can never be caught by referential integrity since there's no FK.

Fix: Migrate ticket_watchers.ticket_id to varchar(9) matching tickets.ticket_id, and add the missing FK constraint (with ON DELETE CASCADE, matching the intent of a per-ticket subscription row).

**Severity:** High (data-integrity risk) `ticket_watchers` (migrations/000_baseline.sql, ~lines 245-251) stores `ticket_id` as `int(11)`, while every other satellite table (`ticket_comments`, `ticket_attachments`, `ticket_dependencies`, `custom_field_values`) stores it as `varchar(9)`/`varchar(10)` matching `tickets.ticket_id`. There's also no FK constraint from `ticket_watchers.ticket_id` to `tickets.ticket_id` at all — every other satellite table has one. `api/watch_ticket.php` (~lines 37-49) and `helpers/NotificationHelper.php` (~lines 197-200) cast/bind ticket IDs as ints here, which only works today because ticket IDs happen to be numeric-only 9-digit strings that fit in `int(11)`. If the ID format ever gains a non-numeric character (e.g. a prefix), this silently truncates/corrupts IDs, and orphaned watcher rows can never be caught by referential integrity since there's no FK. **Fix:** Migrate `ticket_watchers.ticket_id` to `varchar(9)` matching `tickets.ticket_id`, and add the missing FK constraint (with `ON DELETE CASCADE`, matching the intent of a per-ticket subscription row).

jared added the data-integrity priority/high labels 2026-09-08 10:15:43 -04:00

jared referenced this issue from a commit

2026-09-08 21:28:47 -04:00

Fix ticket_watchers.ticket_id type mismatch and missing FK (#32)

jared referenced this issue from a commit

2026-09-08 21:33:34 -04:00

Merge development into main: high-priority security/reliability batch (#27, #28, #30, #32)

jared commented

2026-09-08 21:33:43 -04:00