Per-site unique event slug (currently uq_event_slug is global)

[Salem874]

ELI5

When migration 008 created tblEvents, the eventSlug column got a global UNIQUE index. Migration 019 fixed this for categories, types, themes, series, and attendance service types — but missed tblEvents itself. Two sites on the same install can't both have a sabbath-service event; the second one to try will hit a duplicate-key error.

Detailed proposal

• New migration 030_event_slug_per_site.sql that:
  • ALTER TABLE tblEvents DROP INDEX uq_event_slug, ADD UNIQUE KEY uq_event_slug_site (eventSlug, siteID);
  • Pre-flight: detect existing collisions (same eventSlug across different siteID) and suffix -2, -3, … on the older rows so the index can build cleanly.
  • INSERT INTO tblMigrations for tracking, per the established 019 pattern.
• No application-code change required — every tblEvents slug lookup already joins on siteID (Events app is fully site-scoped via Site::current() and AppRegistry per-site enablement). Verify with grep -rn "eventSlug" web/_apps/calendar web/_core before merging.
• DEV_NOTES.md gets a 2-line entry under "Schema corrections" noting that 019 missed tblEvents and 030 completes the per-site uniqueness model. CHANGELOG bug-fix line under 1.2.x patch band.
• Related tables already site-scoped via 019: tblEventCategories, tblEventTypes, tblEventThemes, tblEventSeries, tblAttendanceServiceTypes. tblEventRecurrence and tblEventRsvps have no slug column — unaffected.

Pros / Cons

Pros

• Completes the per-site uniqueness model 019 started — consistency win.
• Prevents an inevitable production incident the next time a second church (ChurchMS sub-brand per Site::branding) installs and creates sabbath-service.
• Single migration file, zero PHP changes — low blast radius.
• Easy to verify with a smoke test (insert same slug into two siteIDs).

Cons

• Requires collision detection + auto-rename for any installs that already collided (unlikely today but must be handled to keep migration idempotent).
• Slug suffixing changes public URLs for any renamed events — needs a brief operator note.
• Larger composite index marginally bigger on disk (negligible).

How necessary?

Medium — latent bug today, certain to bite on the first multi-site/ChurchMS install that picks a common slug.

Acceptance criteria

• New migration 030_event_slug_per_site.sql drops uq_event_slug and adds uq_event_slug_site (eventSlug, siteID).
• Migration auto-suffixes any existing cross-site slug collisions before the new index is added.
• Insert smoke test: same eventSlug accepted across two different siteID rows; rejected within the same siteID.
• No application-code grep hits where eventSlug is queried without a siteID filter (or all such call-sites updated).
• DEV_NOTES.md + CHANGELOG.md updated; migration tracked in tblMigrations.
• Tested on dev install with both WebMS Intra and ChurchMS brand presets.

Estimated effort

3-4 hours (migration + collision handler + grep audit + smoke test + docs).

---

Filed during The Events Calendar competitive analysis on 2026-06-16; decision pending.