-- =============================================================================
-- brain-schema-starter.sql
-- A starter schema for a shared "brain": one knowledge table + one task queue.
-- =============================================================================
--
-- The idea: give every agent (and every human) a single source of truth they
-- read first and write to last. Two objects do the work:
--
--   1. brain        a namespaced key/value knowledge table
--   2. task_queue   one queue with strict, explicit statuses
--
-- The doctrine this schema enforces:
--   "If it is not in the brain, assume it did not happen."
--
-- Design choices worth keeping:
--   - The key is a namespace, not a filename. The prefix before the first
--     underscore is the domain. The namespace is your API.
--   - Rows are typed by `kind`. Keep "what happened" (log) separate from
--     "what is true now" (state) separate from "the contract" (spec/protocol).
--   - Retirement is a boolean plus a view, not a delete. History stays whole.
--   - The queue has exactly four terminal statuses, and one canonical query
--     defines "open". In a shared-memory system, the query is the contract.
--
-- This file targets PostgreSQL. Full-text search is built in. Semantic search
-- via embeddings is optional and shown in a commented block at the end so the
-- file runs cleanly without the vector extension installed.
--
-- Copy, paste, run. Then delete the parts you do not need.
-- =============================================================================


-- -----------------------------------------------------------------------------
-- 1. THE BRAIN: namespaced knowledge, typed by intent
-- -----------------------------------------------------------------------------

-- `kind` is a small, closed taxonomy. Keeping it small is the point: it forces
-- a decision about what each row is for, instead of one undifferentiated blob.
CREATE TYPE brain_kind AS ENUM (
  'log',        -- append-only history: what happened, when, by whom
  'state',      -- the current value of something that changes (mutable, few)
  'spec',       -- how something is supposed to work
  'protocol',   -- a rule of procedure
  'reference',  -- durable facts, glossaries, lookups
  'skill',      -- an operating manual an agent loads at boot
  'doc',        -- a documentation page meant to be rendered for humans
  'artifact',   -- a produced output worth keeping
  'map',        -- an index or table of contents over other rows
  'research'    -- investigation notes, findings in progress
);

CREATE TABLE brain (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  -- The address of a row. Flat string, but reads like a path.
  -- The prefix before the first underscore is the domain, e.g.
  --   SKILL_INGEST, DOC_GETTING_STARTED, INBOX_OWNER, STATE_PIPELINE.
  -- Find a domain with: WHERE file_key LIKE 'DOC_%'
  -- This is the whole magic trick: the namespace is the API.
  file_key      text NOT NULL UNIQUE,

  title         text,
  summary       text,                          -- one-line gist for fast scanning
  content       text,                          -- markdown / prose / SQL / anything

  kind          brain_kind NOT NULL,
  category      text,                          -- free-form secondary grouping

  -- Retirement is a flag, not a delete. Broad reads go through brain_active
  -- (below) so a reversed decision never resurfaces.
  archived      boolean NOT NULL DEFAULT false,

  -- Full-text recall. Generated, so it is always in sync with the row.
  search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')),   'A') ||
    setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(content, '')), 'C')
  ) STORED,

  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  last_read_at  timestamptz                    -- touch on read to spot dead rows
);

-- Fast prefix scans on the namespace (LIKE 'DOC_%').
-- text_pattern_ops makes the index usable for left-anchored LIKE.
CREATE INDEX brain_file_key_prefix_idx ON brain (file_key text_pattern_ops);

-- Filter by type cheaply (all the live logs, all the specs, ...).
CREATE INDEX brain_kind_idx ON brain (kind) WHERE archived = false;

-- Full-text search index.
CREATE INDEX brain_search_idx ON brain USING gin (search_vector);

-- Keep updated_at honest.
CREATE OR REPLACE FUNCTION brain_touch_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER brain_set_updated_at
  BEFORE UPDATE ON brain
  FOR EACH ROW
  EXECUTE FUNCTION brain_touch_updated_at();

-- The view every broad read should use. Retired rows simply do not exist here.
CREATE VIEW brain_active AS
  SELECT * FROM brain WHERE archived = false;


-- -----------------------------------------------------------------------------
-- 2. THE TASK QUEUE: one queue, strict statuses, one canonical "open" query
-- -----------------------------------------------------------------------------

-- Eight statuses. Four are terminal. Getting this exactly right is the whole
-- game: an "open tasks" query that misses a terminal state will hand agents
-- dead work to rediscover forever.
--
--   Non-terminal: queued, claimed, in_progress, blocked
--   Terminal:     done, failed, cancelled, superseded
CREATE TYPE task_status AS ENUM (
  'queued',      -- waiting to be picked up
  'claimed',     -- an agent has reserved it but not started
  'in_progress', -- actively being worked
  'blocked',     -- cannot proceed; waiting on something (see blocked_reason)
  'done',        -- TERMINAL: completed successfully
  'failed',      -- TERMINAL: gave up after errors
  'cancelled',   -- TERMINAL: called off on purpose
  'superseded'   -- TERMINAL: replaced by another task (see superseded_by)
);

CREATE TABLE task_queue (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  title          text NOT NULL,
  detail         text,                         -- the full instruction / payload
  kind           text,                         -- your own job type, e.g. 'scrape'

  status         task_status NOT NULL DEFAULT 'queued',
  priority       int NOT NULL DEFAULT 100,     -- lower runs first

  -- Who owns it right now, and since when. NULL when nobody holds it.
  claimed_by     text,
  claimed_at     timestamptz,

  blocked_reason text,                         -- required reading when blocked
  result         text,                         -- output / error / outcome note
  superseded_by  bigint REFERENCES task_queue (id),  -- set when status='superseded'

  -- Optional scheduling: do not surface before this time.
  run_after      timestamptz,

  attempts       int NOT NULL DEFAULT 0,
  max_attempts   int NOT NULL DEFAULT 3,

  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);

-- Index that matches the canonical "open" query below.
CREATE INDEX task_queue_open_idx
  ON task_queue (priority, created_at)
  WHERE status IN ('queued', 'claimed', 'in_progress', 'blocked');

CREATE TRIGGER task_queue_set_updated_at
  BEFORE UPDATE ON task_queue
  FOR EACH ROW
  EXECUTE FUNCTION brain_touch_updated_at();

-- THE CANONICAL "OPEN TASKS" QUERY, frozen as a view so everyone copies the
-- same definition. "Open" means: not in any terminal state, and ready to run.
-- Edit this in exactly one place if the rules ever change.
CREATE VIEW task_queue_open AS
  SELECT *
  FROM task_queue
  WHERE status NOT IN ('done', 'failed', 'cancelled', 'superseded')
    AND (run_after IS NULL OR run_after <= now())
  ORDER BY priority ASC, created_at ASC;


-- -----------------------------------------------------------------------------
-- 3. CLAIMING WORK SAFELY: one task to one worker, no double pickup
-- -----------------------------------------------------------------------------

-- SKIP LOCKED lets many workers pull from the same queue without colliding.
-- Each call hands back at most one task and marks it claimed atomically.
CREATE OR REPLACE FUNCTION claim_next_task(worker text)
RETURNS task_queue AS $$
DECLARE
  t task_queue;
BEGIN
  SELECT *
    INTO t
    FROM task_queue
   WHERE status = 'queued'
     AND (run_after IS NULL OR run_after <= now())
   ORDER BY priority ASC, created_at ASC
   FOR UPDATE SKIP LOCKED
   LIMIT 1;

  IF NOT FOUND THEN
    RETURN NULL;
  END IF;

  UPDATE task_queue
     SET status     = 'claimed',
         claimed_by = worker,
         claimed_at = now(),
         attempts   = attempts + 1
   WHERE id = t.id
   RETURNING * INTO t;

  RETURN t;
END;
$$ LANGUAGE plpgsql;


-- -----------------------------------------------------------------------------
-- 4. STARTER ROWS: the doctrine, a couple of skills, and the first state
-- -----------------------------------------------------------------------------

-- The first rule of the system, stored inside the system.
INSERT INTO brain (file_key, title, kind, summary, content) VALUES
('PROTOCOL_BRAIN_FIRST',
 'Read the brain first',
 'protocol',
 'Every session begins with a read of the brain.',
 'Before doing anything, read your skill row and the open task queue. '
 'If it is not in the brain, assume it did not happen. '
 'Write what you did back to the brain before you finish.');

-- An example operating manual an agent loads at boot.
INSERT INTO brain (file_key, title, kind, category, summary, content) VALUES
('SKILL_EXAMPLE',
 'Example skill: how to run the example mode',
 'skill',
 'operations',
 'What this mode does and how to do it correctly.',
 '1. Read STATE_SYSTEM for the current state of the world.'  || E'\n' ||
 '2. Claim a task with claim_next_task(your_name).'          || E'\n' ||
 '3. Do the work. Append a log row describing what happened.'|| E'\n' ||
 '4. Mark the task done, failed, or blocked. Never leave it claimed.');

-- The present tense: one mutable state row.
INSERT INTO brain (file_key, title, kind, summary, content) VALUES
('STATE_SYSTEM',
 'Current state of the world',
 'state',
 'The one row that says what is true right now.',
 'status: bootstrapping');

-- A seed task so the queue is not empty on first run.
INSERT INTO task_queue (title, detail, kind, priority) VALUES
('Confirm the brain is wired up',
 'Read PROTOCOL_BRAIN_FIRST, then mark this task done.',
 'setup',
 1);


-- -----------------------------------------------------------------------------
-- 5. EVERYDAY QUERIES (the ones you will actually run)
-- -----------------------------------------------------------------------------

-- An agent boots:
--   SELECT content FROM brain_active WHERE file_key = 'SKILL_EXAMPLE';

-- Read the present tense:
--   SELECT content FROM brain_active WHERE file_key = 'STATE_SYSTEM';

-- List one domain by namespace prefix:
--   SELECT file_key, title FROM brain_active WHERE file_key LIKE 'DOC_%';

-- Full-text recall when you know the term:
--   SELECT file_key, title
--   FROM brain_active
--   WHERE search_vector @@ websearch_to_tsquery('english', 'rate limit')
--   ORDER BY ts_rank(search_vector,
--                    websearch_to_tsquery('english', 'rate limit')) DESC;

-- Pull the next task to work:
--   SELECT * FROM claim_next_task('worker-1');

-- See everything still open (one definition, copied nowhere):
--   SELECT * FROM task_queue_open;

-- Finish a task:
--   UPDATE task_queue SET status='done',   result='ok'        WHERE id = 1;
--   UPDATE task_queue SET status='blocked', blocked_reason='…' WHERE id = 1;

-- Retire knowledge without losing history:
--   UPDATE brain SET archived = true WHERE file_key = 'STATE_OLD_THING';


-- =============================================================================
-- 6. OPTIONAL: semantic recall via embeddings
-- =============================================================================
-- Uncomment if you want vector search alongside full-text. Requires the vector
-- extension. Everything above works without it: lose the embeddings and
-- full-text still works; lose both and you still have file_key and kind, which
-- is a perfectly good filesystem. There is no single fragile component.
--
-- CREATE EXTENSION IF NOT EXISTS vector;
--
-- ALTER TABLE brain ADD COLUMN content_embedding vector(1536);  -- match your model
--
-- CREATE INDEX brain_embedding_idx
--   ON brain USING hnsw (content_embedding vector_cosine_ops);
--
-- -- Populate content_embedding from your embedding model on insert/update,
-- -- then search semantically when you know the idea but not the words:
-- --   SELECT file_key, title
-- --   FROM brain_active
-- --   WHERE content_embedding IS NOT NULL
-- --   ORDER BY content_embedding <=> :query_embedding
-- --   LIMIT 10;
-- =============================================================================
