-- Commit aliases are owned per pull request: the same commit previewed in two pull requests keeps one row each.
CREATE TABLE site_pr_aliases_new (
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, pr_number)
);
INSERT INTO site_pr_aliases_new SELECT site_id, label, pr_number, kind, deployment_id, created_at FROM site_pr_aliases;
DROP TABLE site_pr_aliases;
ALTER TABLE site_pr_aliases_new RENAME TO site_pr_aliases;
CREATE INDEX site_pr_aliases_pull ON site_pr_aliases(site_id, pr_number, kind, created_at);
CREATE INDEX site_pr_aliases_label ON site_pr_aliases(label);
-- When a custom domain was first fully verified; the unverified-claim sweep never touches a domain that has been.
ALTER TABLE sites ADD COLUMN domain_verified_at INTEGER;
ALTER TABLE pages_sites ADD COLUMN domain_verified_at INTEGER;
-- Domains active today count as verified; pending claims stay unverified so they can still expire or be taken over.
UPDATE sites SET domain_verified_at = COALESCE(domain_checked_at, updated_at)
WHERE custom_domain IS NOT NULL AND domain_status LIKE 'active%';
UPDATE pages_sites SET domain_verified_at = updated_at WHERE custom_domain IS NOT NULL AND domain_status LIKE 'active%';
-- Leases for cron jobs that must not overlap (object GC); taken with a conditional UPDATE.
CREATE TABLE cron_locks (
name TEXT PRIMARY KEY,
holder TEXT,
expires_at INTEGER NOT NULL DEFAULT 0
);