| 1 | -- ─── notifications ───────────────────────────────────────────────── |
| 2 | |
| 3 | -- name: UpsertNotificationByThread :one |
| 4 | -- Coalesce-or-insert: if a row exists for (recipient, thread), bump |
| 5 | -- last_event_at + last_actor + reason and re-flip unread=true so the |
| 6 | -- inbox surfaces it again. Otherwise insert a fresh row. |
| 7 | -- |
| 8 | -- Returns the resulting row (whether it was created or updated) |
| 9 | -- so the caller can chain an email-enqueue without a re-read. |
| 10 | INSERT INTO notifications ( |
| 11 | recipient_user_id, kind, reason, repo_id, |
| 12 | thread_kind, thread_id, source_event_id, |
| 13 | last_event_at, last_actor_user_id |
| 14 | ) VALUES ( |
| 15 | $1, $2, $3, $4, $5, $6, $7, now(), $8 |
| 16 | ) |
| 17 | ON CONFLICT (recipient_user_id, thread_kind, thread_id) WHERE thread_id IS NOT NULL |
| 18 | DO UPDATE SET |
| 19 | kind = EXCLUDED.kind, |
| 20 | reason = EXCLUDED.reason, |
| 21 | source_event_id = EXCLUDED.source_event_id, |
| 22 | last_event_at = now(), |
| 23 | last_actor_user_id = EXCLUDED.last_actor_user_id, |
| 24 | unread = true, |
| 25 | updated_at = now() |
| 26 | RETURNING id, recipient_user_id, kind, reason, repo_id, |
| 27 | thread_kind, thread_id, source_event_id, unread, |
| 28 | last_event_at, last_actor_user_id, summary, created_at, updated_at; |
| 29 | |
| 30 | -- name: InsertThreadlessNotification :one |
| 31 | -- For events with no thread (e.g. repo-admin lifecycle: archived). |
| 32 | -- These don't coalesce; each fires its own row. Used sparingly. |
| 33 | INSERT INTO notifications ( |
| 34 | recipient_user_id, kind, reason, repo_id, |
| 35 | source_event_id, last_actor_user_id |
| 36 | ) VALUES ($1, $2, $3, $4, $5, $6) |
| 37 | RETURNING id, recipient_user_id, kind, reason, repo_id, |
| 38 | thread_kind, thread_id, source_event_id, unread, |
| 39 | last_event_at, last_actor_user_id, summary, created_at, updated_at; |
| 40 | |
| 41 | -- name: ListNotificationsForRecipient :many |
| 42 | -- Inbox view, recency-sorted. `onlyUnread` toggles the inbox |
| 43 | -- filter ("Unread" tab vs "All"). |
| 44 | SELECT n.id, n.recipient_user_id, n.kind, n.reason, n.repo_id, |
| 45 | n.thread_kind, n.thread_id, n.source_event_id, n.unread, |
| 46 | n.last_event_at, n.last_actor_user_id, n.summary, |
| 47 | n.created_at, n.updated_at, |
| 48 | coalesce(u.username, '') AS actor_username, |
| 49 | coalesce(r.name, '') AS repo_name, |
| 50 | coalesce(ru.username, '') AS repo_owner_username, |
| 51 | coalesce(i.number, 0) AS thread_number, |
| 52 | coalesce(i.title, '') AS thread_title |
| 53 | FROM notifications n |
| 54 | LEFT JOIN users u ON u.id = n.last_actor_user_id |
| 55 | LEFT JOIN repos r ON r.id = n.repo_id |
| 56 | LEFT JOIN users ru ON ru.id = r.owner_user_id |
| 57 | LEFT JOIN issues i ON i.id = n.thread_id |
| 58 | WHERE n.recipient_user_id = $1 |
| 59 | AND ($2::boolean = false OR n.unread = true) |
| 60 | ORDER BY n.last_event_at DESC |
| 61 | LIMIT $3 OFFSET $4; |
| 62 | |
| 63 | -- name: CountUnreadForRecipient :one |
| 64 | SELECT count(*) FROM notifications |
| 65 | WHERE recipient_user_id = $1 AND unread = true; |
| 66 | |
| 67 | -- name: CountNotificationsForRecipient :one |
| 68 | SELECT count(*) FROM notifications |
| 69 | WHERE recipient_user_id = $1 |
| 70 | AND ($2::boolean = false OR unread = true); |
| 71 | |
| 72 | -- name: SetNotificationRead :exec |
| 73 | UPDATE notifications SET unread = false, updated_at = now() |
| 74 | WHERE id = $1 AND recipient_user_id = $2; |
| 75 | |
| 76 | -- name: SetNotificationUnread :exec |
| 77 | UPDATE notifications SET unread = true, updated_at = now() |
| 78 | WHERE id = $1 AND recipient_user_id = $2; |
| 79 | |
| 80 | -- name: MarkAllReadForRecipient :exec |
| 81 | -- Bounded sweep: a single call doesn't try to update millions of |
| 82 | -- rows. Caller paginates via repeated calls when count > batch. |
| 83 | UPDATE notifications SET unread = false, updated_at = now() |
| 84 | WHERE recipient_user_id = $1 AND unread = true; |
| 85 | |
| 86 | -- name: GetNotification :one |
| 87 | SELECT id, recipient_user_id, kind, reason, repo_id, |
| 88 | thread_kind, thread_id, source_event_id, unread, |
| 89 | last_event_at, last_actor_user_id, summary, created_at, updated_at |
| 90 | FROM notifications WHERE id = $1; |
| 91 |