32 lines
1.7 KiB
MySQL
32 lines
1.7 KiB
MySQL
|
-- =============================================================================
|
||
|
|
-- Personal notification channels (Phase 9 — CHAN-01)
|
||
|
|
-- =============================================================================
|
||
|
|
-- Adds owner_user_id to notification_channels so a row can be either:
|
||
|
|
-- - Global (owner_user_id IS NULL) — existing rows, admin-managed
|
||
|
|
-- - Personal (owner_user_id = "user".id) — created via /api/me/channels
|
||
|
|
--
|
||
|
|
-- Cascade delete: removing a Better Auth user removes their personal channels.
|
||
|
|
--
|
||
|
|
-- Defense-in-depth: a partial unique index enforces one-personal-channel-per-
|
||
|
|
-- type-per-user at the DB layer (D-02 / CHAN-02). The API-layer WITH-CTE UPSERT
|
||
|
|
-- in Plan 02 still has a small race window for concurrent saves; this index
|
||
|
|
-- closes that gap. The WHERE clause keeps existing global rows (NULL owner)
|
||
|
|
-- exempt — multiple global rows of the same type can still coexist.
|
||
|
|
-- =============================================================================
|
||
|
|
|
||
|
|
ALTER TABLE notification_channels
|
||
|
|
ADD COLUMN IF NOT EXISTS owner_user_id TEXT
|
||
|
|
REFERENCES "user"(id) ON DELETE CASCADE;
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_notification_channels_owner
|
||
|
|
ON notification_channels(owner_user_id, channel_type)
|
||
|
|
WHERE owner_user_id IS NOT NULL;
|
||
|
|
|
||
|
|
-- One personal channel per (owner, type). Globals (NULL owner) unaffected.
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS notification_channels_owner_user_id_channel_type_uniq
|
||
|
|
ON notification_channels (owner_user_id, channel_type)
|
||
|
|
WHERE owner_user_id IS NOT NULL;
|
||
|
|
|
||
|
|
COMMENT ON COLUMN notification_channels.owner_user_id IS
|
||
|
|
'Personal channel owner. NULL = global channel (admin-managed). NOT NULL = personal channel reachable only by the owner and admins.';
|