Add notification preferences migrations

Signed-off-by: Danny Kopping <danny@coder.com>
coder · dannykopping · Aug 5, 2024 · Jul 25, 2024 · Jul 23, 2024 · Jul 23, 2024
commit 3ac40be09fbf1d424d92d68d4f2838d70d35e71f
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000229_notification_preferences.down.sql b/coderd/database/migrations/000229_notification_preferences.down.sql
@@ -0,0 +1,7 @@
+DROP TABLE IF EXISTS notification_preferences;
+
+ALTER TABLE notification_templates
+    DROP COLUMN IF EXISTS method;
+
+DROP TRIGGER IF EXISTS inhibit_enqueue_if_disabled_trigger ON notification_messages;
+DROP FUNCTION IF EXISTS inhibit_enqueue_if_disabled;
diff --git a/coderd/database/migrations/000229_notification_preferences.up.sql b/coderd/database/migrations/000229_notification_preferences.up.sql
@@ -0,0 +1,45 @@
+CREATE TABLE notification_preferences
+(
+    user_id                  uuid REFERENCES users ON DELETE CASCADE                  NOT NULL,
+    notification_template_id uuid REFERENCES notification_templates ON DELETE CASCADE NOT NULL,
+    disabled                 bool                                                     NOT NULL DEFAULT FALSE,
+    created_at               TIMESTAMP WITH TIME ZONE                                 NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at               TIMESTAMP WITH TIME ZONE                                 NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
+-- Ensure we cannot insert multiple entries for the same user/template combination
+ALTER TABLE notification_preferences
+    ADD CONSTRAINT unique_user_notification_template UNIQUE (user_id, notification_template_id);
+
+-- Allow per-template notification method (enterprise only)
+ALTER TABLE notification_templates
+    ADD COLUMN method notification_method;
+COMMENT ON COLUMN notification_templates.method IS 'NULL defers to the deployment-level method';
+
+-- No equivalent in down migration because ENUM values cannot be deleted
+ALTER TYPE notification_message_status ADD VALUE IF NOT EXISTS 'inhibited';
+
+-- Function to prevent enqueuing notifications unnecessarily
+CREATE OR REPLACE FUNCTION inhibit_enqueue_if_disabled()
+    RETURNS TRIGGER AS
+$$
+BEGIN
+    -- Fail the insertion if the user has disabled this notification
+    IF EXISTS (SELECT 1
+               FROM notification_preferences
+               WHERE disabled = TRUE
+                 AND user_id = NEW.user_id
+                 AND notification_template_id = NEW.notification_template_id) THEN
+        RAISE EXCEPTION 'cannot enqueue message: user has disabled this notification';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Trigger to execute above function on insertion
+CREATE TRIGGER inhibit_enqueue_if_disabled_trigger
+    BEFORE INSERT
+    ON notification_messages
+    FOR EACH ROW
+EXECUTE FUNCTION inhibit_enqueue_if_disabled();