-- Pull request aliases (`pr-<N>--<site>`) and optional immutable commit previews (`<sha7>--<site>`), so the KV
-- pointers they own can be dropped when the pull request closes or the site goes away.
CREATE TABLE site_pr_aliases (
  site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  label TEXT NOT NULL,
  pr_number INTEGER NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('pr', 'commit')),
  deployment_id TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (site_id, label)
);

CREATE INDEX site_pr_aliases_pull ON site_pr_aliases(site_id, pr_number, kind, created_at);

ALTER TABLE sites ADD COLUMN commit_previews INTEGER NOT NULL DEFAULT 0;