47 lines
2.3 KiB
MySQL
47 lines
2.3 KiB
MySQL
|
-- =============================================================================
|
||
|
|
-- Notify event taxonomy + per-user subscriptions (Phase 9 — SUB-01, SUB-02)
|
||
|
|
-- =============================================================================
|
||
|
|
-- notify_event_keys: humanization layer for the event keys that appear in
|
||
|
|
-- pipeline_steps.config->'route_to_user'->>'event_key'. Admin-managed at
|
||
|
|
-- /admin/workflow/event-keys. NOT a gate — keys not in this table are
|
||
|
|
-- still routable; they just render with the raw key as their label.
|
||
|
|
--
|
||
|
|
-- user_event_subscriptions: opt-out matrix per (user, event_key, channel_type).
|
||
|
|
-- Row absence = enabled (D-15 default-enabled / opt-out).
|
||
|
|
-- =============================================================================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS notify_event_keys (
|
||
|
|
key TEXT PRIMARY KEY,
|
||
|
|
display_label TEXT NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_notify_event_keys_active
|
||
|
|
ON notify_event_keys(is_active, sort_order, key);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS user_event_subscriptions (
|
||
|
|
user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
||
|
|
event_key TEXT NOT NULL,
|
||
|
|
channel_type VARCHAR(20) NOT NULL,
|
||
|
|
enabled BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
PRIMARY KEY (user_id, event_key, channel_type)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_event_subs_user
|
||
|
|
ON user_event_subscriptions(user_id);
|
||
|
|
|
||
|
|
COMMENT ON TABLE notify_event_keys IS
|
||
|
|
'Humanization layer for event keys used in notify-step route_to_user blocks. Admins curate display_label/description/sort. Not a gate — unknown keys are still routable.';
|
||
|
|
COMMENT ON TABLE user_event_subscriptions IS
|
||
|
|
'Opt-out matrix. Row absence = enabled. Composite PK enforces one row per (user, event_key, channel_type).';
|
||
|
|
|
||
|
|
-- Seed an example row so the admin UI is not empty on first run.
|
||
|
|
INSERT INTO notify_event_keys (key, display_label, description, sort_order, is_active)
|
||
|
|
VALUES ('ticket_assigned_to_me', 'Ticket assigned to me', 'Fires when an Autotask ticket is assigned to your resource.', 10, true)
|
||
|
|
ON CONFLICT (key) DO NOTHING;
|