popagent

Latest change 6726bbcb1e8a68b297a0e59825c4d913d4b9aab8 - Run autonomous improvements continuously by Ólafur Búi Ólafsson

BEGIN;

DO $lock$
BEGIN
  PERFORM pg_advisory_xact_lock(hashtextextended('popagent-evolution-schema-v1', 0));
END
$lock$;

CREATE TABLE IF NOT EXISTS popagent_data_migrations (
  id TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS popagent_autonomy_settings (
  singleton BOOLEAN PRIMARY KEY CHECK (singleton),
  enabled BOOLEAN NOT NULL,
  reflection_interval_ms INTEGER NOT NULL CHECK (reflection_interval_ms BETWEEN 60000 AND 86400000),
  batch_size INTEGER NOT NULL CHECK (batch_size BETWEEN 1 AND 100),
  max_attempts INTEGER NOT NULL CHECK (max_attempts BETWEEN 1 AND 10),
  auto_apply_strategies BOOLEAN NOT NULL,
  auto_create_skills BOOLEAN NOT NULL,
  self_update_enabled BOOLEAN NOT NULL,
  self_update_cron TEXT NOT NULL CHECK (char_length(btrim(self_update_cron)) BETWEEN 1 AND 100),
  idle_improvement_enabled BOOLEAN NOT NULL DEFAULT FALSE,
  idle_deployment_enabled BOOLEAN NOT NULL DEFAULT FALSE,
  idle_workspace_ids JSONB NOT NULL DEFAULT '[]'::jsonb CHECK (jsonb_typeof(idle_workspace_ids) = 'array'),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS popagent_evolution_signals (
  id TEXT PRIMARY KEY,
  dedupe_key TEXT NOT NULL UNIQUE CHECK (char_length(dedupe_key) = 64),
  session_id TEXT,
  turn_id TEXT,
  trace_id TEXT,
  agent_id TEXT NOT NULL REFERENCES popagent_agents(id),
  workspace_id TEXT,
  kind TEXT NOT NULL CHECK (kind IN ('turn-success', 'turn-failure')),
  summary TEXT NOT NULL CHECK (char_length(summary) BETWEEN 1 AND 4000),
  status TEXT NOT NULL CHECK (status IN ('pending', 'processing', 'applied', 'ignored', 'dead-letter')),
  attempts INTEGER NOT NULL DEFAULT 0 CHECK (attempts BETWEEN 0 AND 10),
  next_attempt_at TIMESTAMPTZ,
  processed_at TIMESTAMPTZ,
  error TEXT CHECK (error IS NULL OR char_length(error) <= 2000),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS popagent_evolution_signals_claim_idx
  ON popagent_evolution_signals (next_attempt_at, created_at, id)
  WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS popagent_evolution_signals_recent_idx
  ON popagent_evolution_signals (created_at DESC, id DESC);

CREATE TABLE IF NOT EXISTS popagent_learned_overlays (
  agent_id TEXT PRIMARY KEY REFERENCES popagent_agents(id),
  content TEXT NOT NULL CHECK (char_length(btrim(content)) BETWEEN 1 AND 12000),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE popagent_agent_skills
  ADD COLUMN IF NOT EXISTS evolution_managed BOOLEAN NOT NULL DEFAULT FALSE;

CREATE TABLE IF NOT EXISTS popagent_evolution_revisions (
  id TEXT PRIMARY KEY,
  agent_id TEXT NOT NULL REFERENCES popagent_agents(id),
  target_type TEXT NOT NULL CHECK (target_type IN ('overlay', 'skill')),
  target_key TEXT NOT NULL CHECK (char_length(btrim(target_key)) BETWEEN 1 AND 100),
  before_content TEXT,
  after_content TEXT,
  rationale TEXT NOT NULL CHECK (char_length(btrim(rationale)) BETWEEN 1 AND 2000),
  evidence_ids JSONB NOT NULL CHECK (jsonb_typeof(evidence_ids) = 'array'),
  status TEXT NOT NULL CHECK (status IN ('applied', 'reverted')),
  reverts_revision_id TEXT REFERENCES popagent_evolution_revisions(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CHECK (before_content IS NOT NULL OR after_content IS NOT NULL)
);

CREATE INDEX IF NOT EXISTS popagent_evolution_revisions_recent_idx
  ON popagent_evolution_revisions (created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS popagent_evolution_revisions_target_idx
  ON popagent_evolution_revisions (agent_id, target_type, target_key, created_at DESC);

CREATE OR REPLACE FUNCTION popagent_reject_evolution_revision_mutation()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
BEGIN
  RAISE EXCEPTION 'popagent_evolution_revisions is append-only';
END
$function$;

DROP TRIGGER IF EXISTS popagent_evolution_revisions_append_only
  ON popagent_evolution_revisions;
CREATE TRIGGER popagent_evolution_revisions_append_only
  BEFORE UPDATE OR DELETE ON popagent_evolution_revisions
  FOR EACH ROW EXECUTE FUNCTION popagent_reject_evolution_revision_mutation();

DO $migration$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM popagent_data_migrations
    WHERE id = '2026-08-14-evolution-v1'
  ) THEN
    INSERT INTO popagent_autonomy_settings (
      singleton,
      enabled,
      reflection_interval_ms,
      batch_size,
      max_attempts,
      auto_apply_strategies,
      auto_create_skills,
      self_update_enabled,
      self_update_cron
    ) VALUES (
      TRUE,
      TRUE,
      3600000,
      20,
      3,
      TRUE,
      TRUE,
      TRUE,
      '0 3 * * *'
    )
    ON CONFLICT (singleton) DO NOTHING;

    INSERT INTO popagent_data_migrations (id)
    VALUES ('2026-08-14-evolution-v1')
    ON CONFLICT (id) DO NOTHING;
  END IF;
END
$migration$;

DO $revision_lineage$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM popagent_data_migrations
    WHERE id = '2026-08-14-evolution-revert-lineage-v2'
  ) THEN
    CREATE UNIQUE INDEX IF NOT EXISTS popagent_evolution_revisions_reverts_unique_idx
      ON popagent_evolution_revisions (reverts_revision_id)
      WHERE reverts_revision_id IS NOT NULL;

    INSERT INTO popagent_data_migrations (id)
    VALUES ('2026-08-14-evolution-revert-lineage-v2');
  END IF;
END
$revision_lineage$;

DO $idle_improvement$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM popagent_data_migrations
    WHERE id = '2026-08-15-idle-improvement-v1'
  ) THEN
    ALTER TABLE popagent_autonomy_settings
      ADD COLUMN IF NOT EXISTS idle_improvement_enabled BOOLEAN NOT NULL DEFAULT FALSE,
      ADD COLUMN IF NOT EXISTS idle_deployment_enabled BOOLEAN NOT NULL DEFAULT FALSE,
      ADD COLUMN IF NOT EXISTS idle_workspace_ids JSONB NOT NULL DEFAULT '[]'::jsonb
        CHECK (jsonb_typeof(idle_workspace_ids) = 'array');
    INSERT INTO popagent_data_migrations (id)
    VALUES ('2026-08-15-idle-improvement-v1');
  END IF;
END
$idle_improvement$;

COMMIT;