| 1 | -- ─── watches ─────────────────────────────────────────────────────── |
| 2 | |
| 3 | -- name: GetWatch :one |
| 4 | SELECT user_id, repo_id, level, updated_at |
| 5 | FROM watches WHERE user_id = $1 AND repo_id = $2; |
| 6 | |
| 7 | -- name: UpsertWatch :exec |
| 8 | -- Always-write upsert. The AFTER trigger handles the watcher_count |
| 9 | -- delta on transition into / out of `ignore`. |
| 10 | INSERT INTO watches (user_id, repo_id, level) |
| 11 | VALUES ($1, $2, $3) |
| 12 | ON CONFLICT (user_id, repo_id) DO UPDATE |
| 13 | SET level = EXCLUDED.level, |
| 14 | updated_at = now(); |
| 15 | |
| 16 | -- name: InsertWatchIfAbsent :exec |
| 17 | -- Auto-watch flow: only insert if the user doesn't already have a |
| 18 | -- preference. ON CONFLICT DO NOTHING preserves the user's chosen |
| 19 | -- level when the trigger fires repeatedly. |
| 20 | INSERT INTO watches (user_id, repo_id, level) |
| 21 | VALUES ($1, $2, $3) |
| 22 | ON CONFLICT (user_id, repo_id) DO NOTHING; |
| 23 | |
| 24 | -- name: DeleteWatch :exec |
| 25 | -- Used when a user unsets their explicit preference (returning to the |
| 26 | -- implicit `participating` default). Trigger drops the watcher_count |
| 27 | -- when the prior level wasn't 'ignore'. |
| 28 | DELETE FROM watches WHERE user_id = $1 AND repo_id = $2; |
| 29 | |
| 30 | -- name: ListWatchersForRepo :many |
| 31 | -- Watchers list. `level <> 'ignore'` excludes users who have actively |
| 32 | -- muted the repo. Excludes suspended users from public surfaces. |
| 33 | SELECT w.user_id, w.level, w.updated_at, u.username, u.display_name |
| 34 | FROM watches w |
| 35 | JOIN users u ON u.id = w.user_id |
| 36 | WHERE w.repo_id = $1 |
| 37 | AND w.level <> 'ignore' |
| 38 | AND u.suspended_at IS NULL |
| 39 | ORDER BY w.updated_at DESC |
| 40 | LIMIT $2 OFFSET $3; |
| 41 | |
| 42 | -- name: CountWatchersForRepo :one |
| 43 | SELECT COUNT(*) FROM watches w |
| 44 | JOIN users u ON u.id = w.user_id |
| 45 | WHERE w.repo_id = $1 |
| 46 | AND w.level <> 'ignore' |
| 47 | AND u.suspended_at IS NULL; |
| 48 | |
| 49 | -- name: ListRepoWatchersByLevel :many |
| 50 | -- S29 notification-routing consumer: for fan-out, get every watcher |
| 51 | -- of a repo at the requested level (e.g. `level='all'` for new-issue |
| 52 | -- events). This is the cross-package read; expose the user_ids |
| 53 | -- without joining users — fan-out adds the user join itself. |
| 54 | SELECT user_id FROM watches |
| 55 | WHERE repo_id = $1 AND level = $2; |
| 56 |