lorentz
b4f8ccac8b
- migrations/085: ALTER TABLE notification_channels ADD COLUMN owner_user_id TEXT REFERENCES user(id) ON DELETE CASCADE - migrations/085: partial unique index notification_channels_owner_user_id_channel_type_uniq WHERE owner_user_id IS NOT NULL (UPSERT race defense) - migrations/086: CREATE TABLE notify_event_keys (key PK, display_label, description, sort_order, is_active) with seed row - migrations/086: CREATE TABLE user_event_subscriptions composite PK (user_id, event_key, channel_type) opt-out model - lib/types/pipeline.ts: NotificationChannel gains owner_user_id: string | null - lib/types/pipeline.ts: exports NotifyEventKey and UserEventSubscription interfaces
31 lines
1.7 KiB
SQL
31 lines
1.7 KiB
SQL
-- =============================================================================
|
|
-- 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.';
|