CREATE TABLE policy_snapshots (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  source_agent_id TEXT,
  source_generation INTEGER NOT NULL,
  hash TEXT NOT NULL,
  content_hash TEXT NOT NULL UNIQUE,
  payload_json TEXT NOT NULL CHECK(length(CAST(payload_json AS BLOB)) <= 8388608),
  payload_bytes INTEGER NOT NULL CHECK(payload_bytes >= 0 AND payload_bytes <= 8388608),
  created_at TEXT NOT NULL
);
CREATE TABLE policy_rollouts (
  id TEXT PRIMARY KEY,
  snapshot_id TEXT NOT NULL REFERENCES policy_snapshots(id),
  state TEXT NOT NULL CHECK(state IN ('canary','confirmed','rolled_back')),
  version INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE policy_rollout_targets (
  rollout_id TEXT NOT NULL REFERENCES policy_rollouts(id) ON DELETE CASCADE,
  agent_id TEXT NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
  previous_snapshot_id TEXT NOT NULL REFERENCES policy_snapshots(id),
  baseline_generation INTEGER NOT NULL,
  previous_delivered_hash TEXT,
  previous_delivered_revision INTEGER,
  previous_applied_hash TEXT,
  previous_applied_revision INTEGER,
  is_canary INTEGER NOT NULL CHECK(is_canary IN (0,1)),
  phase TEXT NOT NULL CHECK(phase IN ('pending','candidate','rollback','released')),
  desired_revision INTEGER,
  activated_at TEXT,
  PRIMARY KEY(rollout_id,agent_id)
);
CREATE TABLE policy_pins (
  agent_id TEXT PRIMARY KEY REFERENCES agents(id) ON DELETE CASCADE,
  snapshot_id TEXT NOT NULL REFERENCES policy_snapshots(id),
  rollout_id TEXT NOT NULL REFERENCES policy_rollouts(id)
);
CREATE INDEX idx_policy_rollout_targets_agent ON policy_rollout_targets(agent_id);
CREATE INDEX idx_policy_rollouts_created ON policy_rollouts(created_at,id);
CREATE INDEX idx_policy_snapshots_created ON policy_snapshots(created_at,id);