Skip to content

Commit 73407ca

Browse files
dannykoppingdannykopping
committed
Generate dedupe hash at insertion time
Signed-off-by: Danny Kopping <danny@coder.com>
1 parent c58ff13 commit 73407ca

File tree

8 files changed

+163
-46
lines changed

8 files changed

+163
-46
lines changed

‎coderd/database/dbauthz/dbauthz.go

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -766,10 +766,6 @@ func (q *querier) customRoleEscalationCheck(ctx context.Context, actor rbac.Subj
766766
return nil
767767
}
768768

769-
func (q *querier) InsertNotificationMessage(ctx context.Context, arg database.InsertNotificationMessageParams) (database.NotificationMessage, error) {
770-
panic("not implemented")
771-
}
772-
773769
func (q *querier) AcquireLock(ctx context.Context, id int64) error {
774770
return q.db.AcquireLock(ctx, id)
775771
}

‎coderd/database/dbmetrics/dbmetrics.go

Lines changed: 1 addition & 8 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dump.sql

Lines changed: 33 additions & 3 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000213_notifications.up.sql

Lines changed: 39 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,11 @@ CREATE TYPE notification_message_status AS ENUM (
88
'unknown'
99
);
1010

11+
CREATE TYPE notification_receiver AS ENUM (
12+
'smtp',
13+
'webhook'
14+
);
15+
1116
CREATE TABLE notification_templates
1217
(
1318
id uuid NOT NULL,
@@ -22,29 +27,59 @@ CREATE TABLE notification_templates
2227

2328
COMMENT ON TABLE notification_templates IS 'Templates from which to create notification messages.';
2429

30+
-- Compute a hash from the template, receiver, input params, targets, and current hour; this will help prevent duplicate
31+
-- messages from being sent within the same hour.
32+
-- It is possible that a message could be sent at 12:59:59 and again at 13:00:00, but this should be good enough for now.
33+
-- This could have been a unique index, but we cannot immutably create an index on a timestamp with a timezone.
34+
CREATE OR REPLACE FUNCTION compute_dedupe_hash() RETURNS TRIGGER AS
35+
$$
36+
BEGIN
37+
NEW.dedupe_hash := CONCAT_WS(':',
38+
NEW.notification_template_id,
39+
NEW.receiver,
40+
NEW.input::text,
41+
ARRAY_TO_STRING(NEW.targets, ','),
42+
DATE_TRUNC('hour', NEW.created_at AT TIME ZONE 'UTC')::text
43+
);
44+
RETURN NEW;
45+
END;
46+
$$ LANGUAGE plpgsql;
47+
48+
COMMENT ON FUNCTION compute_dedupe_hash IS 'Computes a unique hash which will be used to prevent duplicate messages from being sent within the last hour';
49+
2550
CREATE TABLE notification_messages
2651
(
2752
id uuid NOT NULL,
2853
notification_template_id uuid NOT NULL,
54+
receiver notification_receiver NOT NULL,
2955
status notification_message_status NOT NULL DEFAULT 'pending'::notification_message_status,
3056
status_reason text,
3157
created_by text NOT NULL,
32-
input jsonb,
58+
input jsonb NOT NULL,
3359
attempt_count int,
34-
created_at timestamp with time zone NOT NULL,
60+
targets uuid[],
61+
created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
3562
updated_at timestamp with time zone,
3663
leased_until timestamp with time zone,
3764
next_retry_after timestamp with time zone,
3865
sent_at timestamp with time zone,
3966
failed_at timestamp with time zone,
40-
targets uuid[],
4167
dedupe_hash text NOT NULL,
4268
PRIMARY KEY (id),
43-
FOREIGN KEY (notification_template_id) REFERENCES notification_templates (id) ON DELETE CASCADE
69+
FOREIGN KEY (notification_template_id) REFERENCES notification_templates (id) ON DELETE CASCADE,
70+
UNIQUE (dedupe_hash)
4471
);
4572

73+
CREATE TRIGGER set_dedupe_hash
74+
BEFORE INSERT OR UPDATE
75+
ON notification_messages
76+
FOR EACH ROW
77+
EXECUTE FUNCTION compute_dedupe_hash();
78+
4679
CREATE INDEX idx_notification_messages_status ON notification_messages (status);
4780

81+
COMMENT ON COLUMN notification_messages.dedupe_hash IS 'Auto-generated at insertion time';
82+
4883
CREATE TABLE notification_preferences
4984
(
5085
id uuid NOT NULL,

‎coderd/database/models.go

Lines changed: 63 additions & 3 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go

Lines changed: 19 additions & 17 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)