| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- S32 — Repo topics. One row per (repo, topic). Topic is a citext |
| 4 | -- so case-insensitive uniqueness comes free, and the shape is |
| 5 | -- compatible with case-insensitive search later. Caps: |
| 6 | -- - 20 topics per repo (enforced in the orchestrator) |
| 7 | -- - 50 chars per topic (CHECK below) |
| 8 | -- Topic shape (lowercase alphanumeric + hyphen, can't lead/trail with |
| 9 | -- hyphen) is enforced in the Go validator since regex CHECKs in |
| 10 | -- Postgres are noisier than the Go-side equivalent. |
| 11 | |
| 12 | -- +goose Up |
| 13 | CREATE TABLE repo_topics ( |
| 14 | repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 15 | topic citext NOT NULL, |
| 16 | created_at timestamptz NOT NULL DEFAULT now(), |
| 17 | |
| 18 | PRIMARY KEY (repo_id, topic), |
| 19 | CONSTRAINT repo_topics_topic_length CHECK (char_length(topic::text) BETWEEN 1 AND 50) |
| 20 | ); |
| 21 | |
| 22 | -- Index for "repos with topic X" lookups (post-MVP topic-filter on |
| 23 | -- explore page). Cheap and indexes are eventually consistent with |
| 24 | -- the workload. |
| 25 | CREATE INDEX repo_topics_topic_idx ON repo_topics (topic); |
| 26 | |
| 27 | -- +goose Down |
| 28 | DROP TABLE IF EXISTS repo_topics; |
| 29 |