(

user_id uuidREFERENCES usersON DELETE CASCADENOT NULL,

notification_template_id uuidREFERENCES notification_templatesON DELETE CASCADENOT NULL,

disabled boolNOT NULL DEFAULT FALSE,

created_atTIMESTAMP WITH TIME ZONENOT NULL DEFAULTCURRENT_TIMESTAMP,

updated_atTIMESTAMP WITH TIME ZONENOT NULL DEFAULTCURRENT_TIMESTAMP

user_id uuidREFERENCES usersON DELETE CASCADENOT NULL,

notification_template_id uuidREFERENCES notification_templatesON DELETE CASCADENOT NULL,

disabled boolNOT NULL DEFAULT FALSE,

created_atTIMESTAMP WITH TIME ZONENOT NULL DEFAULTCURRENT_TIMESTAMP,

updated_atTIMESTAMP WITH TIME ZONENOT NULL DEFAULTCURRENT_TIMESTAMP,

PRIMARY KEY (user_id, notification_template_id)

);

ALTERTABLE notification_preferences

ADDCONSTRAINT unique_user_notification_template UNIQUE (user_id, notification_template_id);

ADDCONSTRAINT unique_user_notification_template UNIQUE (user_id, notification_template_id);

CREATETYPEnotification_template_kindAS ENUM (

);

);

ALTERTABLE notification_templates

ADD COLUMN method notification_method,

ADD COLUMN kind notification_template_kind DEFAULT'system'::notification_template_kindNOT NULL;

ADD COLUMN method notification_method,

ADD COLUMN kind notification_template_kind DEFAULT'system'::notification_template_kindNOT NULL;

COMMENT ON COLUMN notification_templates.method IS'NULL defers to the deployment-level method';

ALTERTYPE notification_message_status ADD VALUE IF NOT EXISTS'inhibited';

CREATE OR REPLACEFUNCTIONinhibit_enqueue_if_disabled()

RETURNS TRIGGERAS

RETURNS TRIGGERAS

$$

IF EXISTS (SELECT1

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;

IF EXISTS (SELECT1

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;

BEFORE INSERT

ON notification_messages

FOR EACH ROW

BEFORE INSERT

ON notification_messages

FOR EACH ROW

EXECUTE FUNCTION inhibit_enqueue_if_disabled();

INSERT INTO users(id, email, username, hashed_password, created_at, updated_at, status, rbac_roles, deleted)

VALUES ('fc1511ef-4fcf-4a3b-98a1-8df64160e35a','githubuser@coder.com','githubuser','\x',

'2022-11-02 13:05:21.445455+02','2022-11-02 13:05:21.445455+02','active','{}', false)ON CONFLICT DO NOTHING;

INSERT INTO notification_templates (id, name, title_template, body_template,"group")

VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11','A','title','body','Group 1')ON CONFLICT DO NOTHING;

INSERT INTO notification_preferences (user_id, notification_template_id, disabled, created_at, updated_at)

VALUES ('a0061a8e-7db7-4585-838c-3116a003dd21','a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', FALSE,'2024-07-15 10:30:00+00','2024-07-15 10:30:00+00');