| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Per-user, per-repo watch level. Absence of a row is the implicit |
| 4 | -- "participating" default — matches GitHub's semantics. We only |
| 5 | -- materialize a row when the user explicitly sets a level OR when an |
| 6 | -- auto-watch trigger fires (collaborator add → 'all'; first comment / |
| 7 | -- mention / assignment → 'participating'). |
| 8 | -- |
| 9 | -- watcher_count is the count of rows where level <> 'ignore'. The |
| 10 | -- spec's day-1 lean ("all non-ignore") matches GitHub. We do NOT add |
| 11 | -- collaborators with no row; the auto-watch path inserts a row for |
| 12 | -- them on collab add, so once that fires every collab is in the count. |
| 13 | |
| 14 | -- +goose Up |
| 15 | CREATE TYPE watch_level AS ENUM ('all', 'participating', 'ignore'); |
| 16 | |
| 17 | CREATE TABLE watches ( |
| 18 | user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 19 | repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 20 | level watch_level NOT NULL, |
| 21 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 22 | PRIMARY KEY (user_id, repo_id) |
| 23 | ); |
| 24 | |
| 25 | -- Watchers-of-a-repo, drives `/{owner}/{repo}/watchers`. |
| 26 | CREATE INDEX watches_repo_idx |
| 27 | ON watches (repo_id, updated_at DESC) |
| 28 | WHERE level <> 'ignore'; |
| 29 | |
| 30 | -- Watches-by-user (e.g. notification fan-out picks recipients). |
| 31 | CREATE INDEX watches_user_idx ON watches (user_id); |
| 32 | |
| 33 | -- +goose StatementBegin |
| 34 | CREATE OR REPLACE FUNCTION tg_watches_count() RETURNS trigger |
| 35 | LANGUAGE plpgsql AS $$ |
| 36 | DECLARE |
| 37 | delta int := 0; |
| 38 | target_repo_id bigint; |
| 39 | BEGIN |
| 40 | IF TG_OP = 'INSERT' THEN |
| 41 | target_repo_id := NEW.repo_id; |
| 42 | IF NEW.level <> 'ignore' THEN |
| 43 | delta := 1; |
| 44 | END IF; |
| 45 | ELSIF TG_OP = 'UPDATE' THEN |
| 46 | target_repo_id := NEW.repo_id; |
| 47 | IF OLD.level = 'ignore' AND NEW.level <> 'ignore' THEN |
| 48 | delta := 1; |
| 49 | ELSIF OLD.level <> 'ignore' AND NEW.level = 'ignore' THEN |
| 50 | delta := -1; |
| 51 | END IF; |
| 52 | ELSIF TG_OP = 'DELETE' THEN |
| 53 | target_repo_id := OLD.repo_id; |
| 54 | IF OLD.level <> 'ignore' THEN |
| 55 | delta := -1; |
| 56 | END IF; |
| 57 | END IF; |
| 58 | IF delta <> 0 THEN |
| 59 | UPDATE repos |
| 60 | SET watcher_count = GREATEST(watcher_count + delta, 0) |
| 61 | WHERE id = target_repo_id; |
| 62 | END IF; |
| 63 | RETURN COALESCE(NEW, OLD); |
| 64 | END; |
| 65 | $$; |
| 66 | -- +goose StatementEnd |
| 67 | |
| 68 | CREATE TRIGGER watches_count AFTER INSERT OR UPDATE OR DELETE ON watches |
| 69 | FOR EACH ROW EXECUTE FUNCTION tg_watches_count(); |
| 70 | |
| 71 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON watches |
| 72 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 73 | |
| 74 | -- +goose Down |
| 75 | DROP TRIGGER IF EXISTS set_updated_at ON watches; |
| 76 | DROP TRIGGER IF EXISTS watches_count ON watches; |
| 77 | DROP FUNCTION IF EXISTS tg_watches_count(); |
| 78 | DROP TABLE IF EXISTS watches; |
| 79 | DROP TYPE IF EXISTS watch_level; |
| 80 |