| 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