-- name: FetchNewMessageMetadata :one

-- This is used to build up the notification_message's JSON payload.

SELECT nt.name AS notification_name,

nt.id AS notification_template_id,

nt.actions AS actions,

nt.method AS custom_method,

u.id AS user_id,

u.email AS user_email,

COALESCE(NULLIF(u.name, ''), NULLIF(u.username, ''))::text AS user_name,

u.username AS user_username

FROM notification_templates nt,

users u

WHERE nt.id = @notification_template_id

AND u.id = @user_id;

-- name: EnqueueNotificationMessage :exec

INSERT INTO notification_messages (id, notification_template_id, user_id, method, payload, targets, created_by, created_at)

VALUES (@id,

@notification_template_id,

@user_id,

@method::notification_method,

@payload::jsonb,

@targets,

@created_by,

@created_at);

-- Acquires the lease for a given count of notification messages, to enable concurrent dequeuing and subsequent sending.

-- Only rows that aren't already leased (or ones which are leased but have exceeded their lease period) are returned.

--

-- A "lease" here refers to a notifier taking ownership of a notification_messages row. A lease survives for the duration

-- of CODER_NOTIFICATIONS_LEASE_PERIOD. Once a message is delivered, its status is updated and the lease expires (set to NULL).

-- If a message exceeds its lease, that implies the notifier did not shutdown cleanly, or the table update failed somehow,

-- and the row will then be eligible to be dequeued by another notifier.

--

-- SKIP LOCKED is used to jump over locked rows. This prevents multiple notifiers from acquiring the same messages.

-- See: https://www.postgresql.org/docs/9.5/sql-select.html#SQL-FOR-UPDATE-SHARE

--

-- name: AcquireNotificationMessages :many

WITH acquired AS (

UPDATE

notification_messages

SET queued_seconds = GREATEST(0, EXTRACT(EPOCH FROM (NOW() - updated_at)))::FLOAT,

updated_at = NOW(),

status = 'leased'::notification_message_status,

status_reason = 'Leased by notifier ' || sqlc.arg('notifier_id')::uuid,

leased_until = NOW() + CONCAT(sqlc.arg('lease_seconds')::int, ' seconds')::interval

WHERE id IN (SELECT nm.id

FROM notification_messages AS nm

WHERE (

(

-- message is in acquirable states

nm.status IN (

'pending'::notification_message_status,

'temporary_failure'::notification_message_status

)

)

-- or somehow the message was left in leased for longer than its lease period

OR (

nm.status = 'leased'::notification_message_status

AND nm.leased_until < NOW()

)

)

AND (

-- exclude all messages which have exceeded the max attempts; these will be purged later

nm.attempt_count IS NULL OR nm.attempt_count < sqlc.arg('max_attempt_count')::int

)

-- if set, do not retry until we've exceeded the wait time

AND (

CASE

WHEN nm.next_retry_after IS NOT NULL THEN nm.next_retry_after < NOW()

ELSE true

END

)

ORDER BY nm.created_at ASC

-- Ensure that multiple concurrent readers cannot retrieve the same rows

FOR UPDATE OF nm

SKIP LOCKED

LIMIT sqlc.arg('count'))

RETURNING *)

SELECT

-- message

nm.id,

nm.payload,

nm.method,

nm.attempt_count::int AS attempt_count,

nm.queued_seconds::float AS queued_seconds,

-- template

nt.id AS template_id,

nt.title_template,

nt.body_template,

-- preferences

(CASE WHEN np.disabled IS NULL THEN false ELSE np.disabled END)::bool AS disabled

FROM acquired nm

JOIN notification_templates nt ON nm.notification_template_id = nt.id

LEFT JOIN notification_preferences AS np

ON (np.user_id = nm.user_id AND np.notification_template_id = nm.notification_template_id);

-- name: BulkMarkNotificationMessagesFailed :execrows

UPDATE notification_messages

SET queued_seconds = 0,

updated_at = subquery.failed_at,

attempt_count = attempt_count + 1,

status = CASE

WHEN attempt_count + 1 < @max_attempts::int THEN subquery.status

ELSE 'permanent_failure'::notification_message_status END,

status_reason = subquery.status_reason,

leased_until = NULL,

next_retry_after = CASE

WHEN (attempt_count + 1 < @max_attempts::int)

THEN NOW() + CONCAT(@retry_interval::int, ' seconds')::interval END

FROM (SELECT UNNEST(@ids::uuid[]) AS id,

UNNEST(@failed_ats::timestamptz[]) AS failed_at,

UNNEST(@statuses::notification_message_status[]) AS status,

UNNEST(@status_reasons::text[]) AS status_reason) AS subquery

WHERE notification_messages.id = subquery.id;

-- name: BulkMarkNotificationMessagesSent :execrows

UPDATE notification_messages

SET queued_seconds = 0,

updated_at = new_values.sent_at,

attempt_count = attempt_count + 1,

status = 'sent'::notification_message_status,

status_reason = NULL,

leased_until = NULL,

next_retry_after = NULL

FROM (SELECT UNNEST(@ids::uuid[]) AS id,

UNNEST(@sent_ats::timestamptz[]) AS sent_at)

AS new_values

WHERE notification_messages.id = new_values.id;

-- Delete all notification messages which have not been updated for over a week.

-- name: DeleteOldNotificationMessages :exec

DELETE

FROM notification_messages

WHERE id IN

(SELECT id

FROM notification_messages AS nested

WHERE nested.updated_at < NOW() - INTERVAL '7 days');

-- name: GetNotificationMessagesByStatus :many

SELECT *

FROM notification_messages

WHERE status = @status

LIMIT sqlc.arg('limit')::int;

-- name: GetUserNotificationPreferences :many

SELECT *

FROM notification_preferences

WHERE user_id = @user_id::uuid;

-- name: UpdateUserNotificationPreferences :execrows

INSERT

INTO notification_preferences (user_id, notification_template_id, disabled)

SELECT @user_id::uuid, new_values.notification_template_id, new_values.disabled

FROM (SELECT UNNEST(@notification_template_ids::uuid[]) AS notification_template_id,

UNNEST(@disableds::bool[]) AS disabled) AS new_values

ON CONFLICT (user_id, notification_template_id) DO UPDATE

SET disabled = EXCLUDED.disabled,

updated_at = CURRENT_TIMESTAMP;

-- name: UpdateNotificationTemplateMethodByID :one

UPDATE notification_templates

SET method = sqlc.narg('method')::notification_method

WHERE id = @id::uuid

RETURNING *;

-- name: GetNotificationTemplateByID :one

SELECT *

FROM notification_templates

WHERE id = @id::uuid;

-- name: GetNotificationTemplatesByKind :many

SELECT *

FROM notification_templates

WHERE kind = @kind::notification_template_kind

ORDER BY name ASC;

-- name: GetNotificationReportGeneratorLogByTemplate :one

-- Fetch the notification report generator log indicating recent activity.

SELECT

*

FROM

notification_report_generator_logs

WHERE

notification_template_id = @template_id::uuid;

-- name: UpsertNotificationReportGeneratorLog :exec

-- Insert or update notification report generator logs with recent activity.

INSERT INTO notification_report_generator_logs (notification_template_id, last_generated_at) VALUES (@notification_template_id, @last_generated_at)

ON CONFLICT (notification_template_id) DO UPDATE set last_generated_at = EXCLUDED.last_generated_at

WHERE notification_report_generator_logs.notification_template_id = EXCLUDED.notification_template_id;

-- name: GetWebpushSubscriptionsByUserID :many

SELECT *

FROM webpush_subscriptions

WHERE user_id = @user_id::uuid;

-- name: InsertWebpushSubscription :one

-- Inserts or updates a webpush subscription. The (user_id, endpoint) pair

-- is unique; re-subscribing the same endpoint replaces the keys instead of

-- inserting a duplicate row. This is the recovery path after a PWA reinstall

-- on iOS, where the browser may keep the same endpoint with rotated keys.

INSERT INTO webpush_subscriptions (user_id, created_at, endpoint, endpoint_p256dh_key, endpoint_auth_key)

VALUES ($1, $2, $3, $4, $5)

ON CONFLICT (user_id, endpoint) DO UPDATE

SET endpoint_p256dh_key = EXCLUDED.endpoint_p256dh_key,

endpoint_auth_key = EXCLUDED.endpoint_auth_key,

created_at = EXCLUDED.created_at

RETURNING *;

-- name: DeleteWebpushSubscriptions :exec

DELETE FROM webpush_subscriptions

WHERE id = ANY(@ids::uuid[]);

-- name: DeleteWebpushSubscriptionByUserIDAndEndpoint :exec

DELETE FROM webpush_subscriptions

WHERE user_id = @user_id AND endpoint = @endpoint;

-- name: DeleteAllWebpushSubscriptions :exec

-- Deletes all existing webpush subscriptions.

-- This should be called when the VAPID keypair is regenerated, as the old

-- keypair will no longer be valid and all existing subscriptions will need to

-- be recreated.

TRUNCATE TABLE webpush_subscriptions;