gitfor/platform

-- Directory lookups for rows that moved into per-repository (RepoState) and per-account (AccountState) Durable
-- Objects: a request that only knows an id or a token hash finds the object that holds the row here.
CREATE TABLE scope_locators (
  kind TEXT NOT NULL,
  key TEXT NOT NULL,
  scope_id TEXT NOT NULL,
  PRIMARY KEY (kind, key)
);

CREATE INDEX scope_locators_scope ON scope_locators(scope_id);

-- Repository-scoped ids.
INSERT OR IGNORE INTO scope_locators SELECT 'issue', id, repo_id FROM issues;
INSERT OR IGNORE INTO scope_locators SELECT 'comment', c.id, i.repo_id FROM comments c JOIN issues i ON i.id = c.issue_id;
INSERT OR IGNORE INTO scope_locators SELECT 'review', r.id, i.repo_id FROM reviews r JOIN issues i ON i.id = r.issue_id;
INSERT OR IGNORE INTO scope_locators
  SELECT 'review_comment', rc.id, i.repo_id FROM review_comments rc JOIN issues i ON i.id = rc.issue_id;
INSERT OR IGNORE INTO scope_locators SELECT 'discussion', id, repo_id FROM discussions;
INSERT OR IGNORE INTO scope_locators
  SELECT 'discussion_comment', dc.id, d.repo_id FROM discussion_comments dc JOIN discussions d ON d.id = dc.discussion_id;
INSERT OR IGNORE INTO scope_locators SELECT 'release', id, repo_id FROM releases;
INSERT OR IGNORE INTO scope_locators
  SELECT 'release_asset', ra.id, r.repo_id FROM release_assets ra JOIN releases r ON r.id = ra.release_id;
INSERT OR IGNORE INTO scope_locators SELECT 'workflow_run', id, repo_id FROM workflow_runs;
INSERT OR IGNORE INTO scope_locators SELECT 'workflow_job', id, repo_id FROM workflow_jobs;
INSERT OR IGNORE INTO scope_locators SELECT 'job_token', token_hash, repo_id FROM job_tokens;
INSERT OR IGNORE INTO scope_locators SELECT 'deploy_token', token_hash, repo_id FROM deploy_tokens;
INSERT OR IGNORE INTO scope_locators SELECT 'webhook', id, repo_id FROM webhooks;
INSERT OR IGNORE INTO scope_locators SELECT 'attachment', id, repo_id FROM attachments WHERE repo_id IS NOT NULL;
INSERT OR IGNORE INTO scope_locators SELECT 'security_advisory', id, repo_id FROM security_advisories;
-- Sites without a repository (upload scratch sites, claimed or not) live in their own RepoState keyed 'site:<id>'.
INSERT OR IGNORE INTO scope_locators SELECT 'site', id, COALESCE(repo_id, 'site:' || id) FROM sites;
INSERT OR IGNORE INTO scope_locators SELECT 'site_claim', claim_token_hash, COALESCE(repo_id, 'site:' || id) FROM sites
  WHERE claim_token_hash IS NOT NULL;
INSERT OR IGNORE INTO scope_locators
  SELECT 'site_deployment', sd.id, COALESCE(s.repo_id, 'site:' || s.id) FROM site_deployments sd JOIN sites s ON s.id = sd.site_id;
-- Host routing: both the label host and any custom domain resolve to the site id.
INSERT OR IGNORE INTO scope_locators SELECT 'site_host', lower(label), id FROM sites WHERE label IS NOT NULL;
INSERT OR IGNORE INTO scope_locators SELECT 'site_host', lower(custom_domain), id FROM sites WHERE custom_domain IS NOT NULL;
INSERT OR IGNORE INTO scope_locators SELECT 'site_host', lower(sd.label), sd.site_id FROM site_deployments sd WHERE sd.label IS NOT NULL;