| 1 | -- M5: global search over repositories, changes and issues. | |
| 2 | -- | |
| 3 | -- Spec §5 in-scope list ends at "Global search over repos, changes, and issues | |
| 4 | -- (not code)", so this indexes titles, descriptions and bodies — never blobs. | |
| 5 | -- | |
| 6 | -- Generated columns rather than triggers: the tsvector cannot drift from the | |
| 7 | -- row, and there is no ordering hazard between an update and its index. | |
| 8 | ||
| 9 | -- ─── repositories ──────────────────────────────────────────────────────────── | |
| 10 | ||
| 11 | ALTER TABLE repos ADD COLUMN search tsvector | |
| 12 | GENERATED ALWAYS AS ( | |
| 13 | setweight(to_tsvector('english', coalesce(name::text, '')), 'A') || | |
| 14 | setweight(to_tsvector('english', coalesce(description, '')), 'B') | |
| 15 | ) STORED; | |
| 16 | ||
| 17 | CREATE INDEX repos_search_idx ON repos USING gin (search); | |
| 18 | ||
| 19 | -- ─── changes ───────────────────────────────────────────────────────────────── | |
| 20 | ||
| 21 | ALTER TABLE changes ADD COLUMN search tsvector | |
| 22 | GENERATED ALWAYS AS ( | |
| 23 | setweight(to_tsvector('english', coalesce(title, '')), 'A') || | |
| 24 | setweight(to_tsvector('english', coalesce(description, '')), 'B') | |
| 25 | ) STORED; | |
| 26 | ||
| 27 | CREATE INDEX changes_search_idx ON changes USING gin (search); | |
| 28 | ||
| 29 | -- ─── issues ────────────────────────────────────────────────────────────────── | |
| 30 | ||
| 31 | ALTER TABLE issues ADD COLUMN search tsvector | |
| 32 | GENERATED ALWAYS AS ( | |
| 33 | setweight(to_tsvector('english', coalesce(title, '')), 'A') || | |
| 34 | setweight(to_tsvector('english', coalesce(body, '')), 'B') | |
| 35 | ) STORED; | |
| 36 | ||
| 37 | CREATE INDEX issues_search_idx ON issues USING gin (search); | |
| 38 | ||
| 39 | -- Cross-references between issues and changes, extracted from bodies and | |
| 40 | -- comments when they are written. Stored rather than re-scanned so a change's | |
| 41 | -- page can list the issues that mention it without a full-text query per view. | |
| 42 | CREATE TABLE cross_references ( | |
| 43 | id uuid PRIMARY KEY, | |
| 44 | repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE, | |
| 45 | -- What contains the reference. | |
| 46 | source_type text NOT NULL CHECK (source_type IN ('change', 'issue', 'comment')), | |
| 47 | source_id uuid NOT NULL, | |
| 48 | -- What it points at. | |
| 49 | target_type text NOT NULL CHECK (target_type IN ('change', 'issue')), | |
| 50 | target_id uuid NOT NULL, | |
| 51 | created_at timestamptz NOT NULL DEFAULT now(), | |
| 52 | UNIQUE (source_type, source_id, target_type, target_id) | |
| 53 | ); | |
| 54 | CREATE INDEX cross_references_target_idx ON cross_references (target_type, target_id); | |
| 55 | ||
| 56 | -- Labels need a default set per repository or the issue form has nothing to | |
| 57 | -- offer. Seeded for repositories that already exist; new ones are seeded in | |
| 58 | -- application code at creation. | |
| 59 | INSERT INTO labels (id, repo_id, name, color) | |
| 60 | SELECT gen_random_uuid(), r.id, l.name, l.color | |
| 61 | FROM repos r | |
| 62 | CROSS JOIN (VALUES | |
| 63 | ('bug', '#d06b6b'), | |
| 64 | ('enhancement', '#6ba9b8'), | |
| 65 | ('question', '#d9a441'), | |
| 66 | ('documentation', '#8b7fd4') | |
| 67 | ) AS l(name, color) | |
| 68 | ON CONFLICT (repo_id, name) DO NOTHING; |
68 lines · SQL