gitfor/platform

-- actions: squashed from 0700_actions.sql, 1000_runner_daemon.sql, 1001_runner_attestation_cache.sql.

-- Trigger index of the default branch's workflows, refreshed on push, so non-push events and
-- schedules can be matched without reading git.
CREATE TABLE workflow_triggers (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  workflow_path TEXT NOT NULL,
  workflow_name TEXT NOT NULL,
  events TEXT NOT NULL,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (repo_id, workflow_path)
);

CREATE TABLE workflow_schedules (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  workflow_path TEXT NOT NULL,
  cron TEXT NOT NULL,
  next_run_at INTEGER NOT NULL,
  last_run_at INTEGER,
  UNIQUE (repo_id, workflow_path, cron)
);

CREATE INDEX workflow_schedules_due ON workflow_schedules(next_run_at);

CREATE TABLE action_caches (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  ref TEXT NOT NULL,
  key TEXT NOT NULL,
  version TEXT NOT NULL,
  size INTEGER NOT NULL,
  r2_key TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  last_used_at INTEGER NOT NULL,
  content_sha256 TEXT,
  UNIQUE (repo_id, ref, key, version)
);

CREATE INDEX action_caches_lru ON action_caches(repo_id, last_used_at);