| 1 | -- ─── notification_threads ────────────────────────────────────────── |
| 2 | |
| 3 | -- name: GetNotificationThread :one |
| 4 | SELECT recipient_user_id, thread_kind, thread_id, subscribed, reason, updated_at |
| 5 | FROM notification_threads |
| 6 | WHERE recipient_user_id = $1 AND thread_kind = $2 AND thread_id = $3; |
| 7 | |
| 8 | -- name: UpsertNotificationThread :exec |
| 9 | -- Always-write upsert. Used by Subscribe / Unsubscribe / Ignore |
| 10 | -- handlers and by the auto-subscription rules in the fan-out |
| 11 | -- worker. |
| 12 | INSERT INTO notification_threads (recipient_user_id, thread_kind, thread_id, subscribed, reason) |
| 13 | VALUES ($1, $2, $3, $4, $5) |
| 14 | ON CONFLICT (recipient_user_id, thread_kind, thread_id) |
| 15 | DO UPDATE SET subscribed = EXCLUDED.subscribed, |
| 16 | reason = EXCLUDED.reason, |
| 17 | updated_at = now(); |
| 18 | |
| 19 | -- name: InsertNotificationThreadIfAbsent :exec |
| 20 | -- Auto-subscription path: only insert if the user has no explicit |
| 21 | -- preference yet. Preserves user choices (e.g. an explicit |
| 22 | -- `subscribed=false` from clicking "Unsubscribe"). |
| 23 | INSERT INTO notification_threads (recipient_user_id, thread_kind, thread_id, subscribed, reason) |
| 24 | VALUES ($1, $2, $3, $4, $5) |
| 25 | ON CONFLICT (recipient_user_id, thread_kind, thread_id) DO NOTHING; |
| 26 | |
| 27 | -- name: DeleteNotificationThread :exec |
| 28 | DELETE FROM notification_threads |
| 29 | WHERE recipient_user_id = $1 AND thread_kind = $2 AND thread_id = $3; |
| 30 | |
| 31 | -- name: ListSubscribersForThread :many |
| 32 | -- Fan-out helper: returns recipients who explicitly subscribed to a |
| 33 | -- thread. The fan-out worker unions this with the per-repo `watches` |
| 34 | -- result + author/assignee/reviewer rules. |
| 35 | SELECT recipient_user_id, reason |
| 36 | FROM notification_threads |
| 37 | WHERE thread_kind = $1 AND thread_id = $2 AND subscribed = true; |
| 38 |