`migrations/009_animal_agent.sql`

55 lines
-- Animal Agent — autonomous builder. Tracks its own task queue + run history
-- so it can resume cleanly across restarts and Steve can audit what it did.

BEGIN;

CREATE TABLE IF NOT EXISTS agent_tasks (
  id            BIGSERIAL PRIMARY KEY,
  kind          TEXT NOT NULL,                -- 'audit_websites' | 'gen_mockups' | 'enrich_emails' | 'ingest_osm_state' | 'ingest_reddit' | 'wikimedia_breeds' | 'codex_debate' | 'build_subsite' | 'github_data'
  payload_json  JSONB NOT NULL DEFAULT '{}',  -- per-kind args
  priority      INTEGER NOT NULL DEFAULT 5,   -- 1=highest, 9=lowest
  status        TEXT NOT NULL DEFAULT 'queued'
                CHECK (status IN ('queued','running','completed','failed','skipped','cancelled')),
  attempts      INTEGER NOT NULL DEFAULT 0,
  max_attempts  INTEGER NOT NULL DEFAULT 3,
  scheduled_for TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  started_at    TIMESTAMPTZ,
  finished_at   TIMESTAMPTZ,
  result_json   JSONB,
  error         TEXT,
  cron_label    TEXT,                          -- which schedule rule queued this
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_agent_tasks_status ON agent_tasks(status, scheduled_for);
CREATE INDEX IF NOT EXISTS idx_agent_tasks_kind   ON agent_tasks(kind);
CREATE INDEX IF NOT EXISTS idx_agent_tasks_cron   ON agent_tasks(cron_label, created_at DESC);

CREATE TABLE IF NOT EXISTS agent_runs (
  id           BIGSERIAL PRIMARY KEY,
  started_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  finished_at  TIMESTAMPTZ,
  ok_count     INTEGER NOT NULL DEFAULT 0,
  fail_count   INTEGER NOT NULL DEFAULT 0,
  notes        TEXT
);

-- Tracks generated subsites (the "Vet Site Upgrade" delivery — one full
-- static site per business that opted in). Paths land under public/sites/.
CREATE TABLE IF NOT EXISTS subsites (
  id              BIGSERIAL PRIMARY KEY,
  business_id     BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  slug            TEXT NOT NULL,             -- url path: public/sites/{slug}/index.html
  template_label  TEXT,                      -- which design system spec was rendered
  pages           TEXT[],                    -- e.g. ['index','services','team','contact']
  hostname        TEXT,                      -- 'happypaws.vetloft.com' once domain is wired
  status          TEXT NOT NULL DEFAULT 'generated'
                  CHECK (status IN ('generated','published','outdated','removed')),
  generated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  published_at    TIMESTAMPTZ,
  UNIQUE (business_id, slug)
);
CREATE INDEX IF NOT EXISTS idx_subsites_biz    ON subsites(business_id);
CREATE INDEX IF NOT EXISTS idx_subsites_status ON subsites(status);

COMMIT;