-- Runs are numbered per repository (1, 2, 3 …), not by the hub-wide row id.

ALTER TABLE flow_runs ADD COLUMN number INTEGER NOT NULL DEFAULT 0;

UPDATE flow_runs SET number = (

    SELECT COUNT(*) FROM flow_runs r2 WHERE r2.repo_id = flow_runs.repo_id AND r2.id <= flow_runs.id

);

CREATE UNIQUE INDEX flow_runs_number ON flow_runs (repo_id, number);

-- Checks link to their run's page by number now.

UPDATE checks SET url = substr(checks.url, 1, length(checks.url) - length(CAST(r.id AS TEXT))) || r.number

FROM flow_runs r

WHERE checks.repo_id = r.repo_id AND checks.name LIKE 'flow/%' AND checks.url LIKE '%/flow/' || r.id;