Spaces:

LoveLogicAI
/

kanbot-backend

Paused

Upload folder using huggingface_hub

587a933 verified about 1 month ago

4.18 kB

-- KanBot v0 schema.
-- The board is the message bus AND the state machine. A column change on a card
-- fires a NOTIFY that the Conductor (LISTEN) picks up and dispatches on.
--
-- Idempotency: transitions are logged in board_events keyed on
-- (card_id, from_col, to_col, attempt); a UNIQUE index makes replays no-ops.

BEGIN;

-- --------------------------------------------------------------------------- --
-- enums mirror core/contract.py exactly. Keep them in sync.
-- --------------------------------------------------------------------------- --
DO $$ BEGIN
  CREATE TYPE kb_column AS ENUM
    ('backlog','ready','in-flight','review','blocked','done');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE kb_role AS ENUM ('planner','executor','critic','human');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- --------------------------------------------------------------------------- --
-- cards = the atoms. thread holds inter-agent comms as an ordered JSONB array.
-- --------------------------------------------------------------------------- --
CREATE TABLE IF NOT EXISTS cards (
  id                   TEXT PRIMARY KEY,             -- CARD-42
  title                TEXT NOT NULL,
  column               kb_column NOT NULL DEFAULT 'backlog',
  agent_role           kb_role  NOT NULL DEFAULT 'executor',
  spec                 TEXT NOT NULL DEFAULT '',
  acceptance_criteria  JSONB NOT NULL DEFAULT '[]'::jsonb,
  assigned_worker      TEXT,
  parent               TEXT REFERENCES cards(id) ON DELETE SET NULL,
  children             JSONB NOT NULL DEFAULT '[]'::jsonb,
  thread               JSONB NOT NULL DEFAULT '[]'::jsonb,
  lease_expires_at     TIMESTAMPTZ,                  -- drives the reaper
  attempt              INT  NOT NULL DEFAULT 0,
  requires_human       BOOLEAN NOT NULL DEFAULT FALSE,
  tags                 JSONB NOT NULL DEFAULT '[]'::jsonb,  -- ["heavy"] -> cloud
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_cards_column ON cards(column);
CREATE INDEX IF NOT EXISTS idx_cards_lease  ON cards(lease_expires_at)
  WHERE column = 'in-flight';

-- --------------------------------------------------------------------------- --
-- board_events = the append-only transition log. Also the idempotency ledger.
-- --------------------------------------------------------------------------- --
CREATE TABLE IF NOT EXISTS board_events (
  id         BIGSERIAL PRIMARY KEY,
  card_id    TEXT NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
  from_col   kb_column,
  to_col     kb_column NOT NULL,
  attempt    INT NOT NULL DEFAULT 0,
  actor      TEXT NOT NULL,       -- worker id / "human:remy" / "system:reaper"
  reason     TEXT,
  ts         TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Replays of the same transition are no-ops (ON CONFLICT DO NOTHING upstream).
CREATE UNIQUE INDEX IF NOT EXISTS uq_transition
  ON board_events(card_id, from_col, to_col, attempt);

-- --------------------------------------------------------------------------- --
-- NOTIFY trigger: any column change publishes a JSON payload on 'kb_events'.
-- The Conductor LISTENs on this channel and dispatches an agent per event.
-- --------------------------------------------------------------------------- --
CREATE OR REPLACE FUNCTION notify_card_transition() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'UPDATE' AND NEW.column IS DISTINCT FROM OLD.column)
     OR TG_OP = 'INSERT' THEN
    PERFORM pg_notify(
      'kb_events',
      json_build_object(
        'card_id',   NEW.id,
        'from',      CASE WHEN TG_OP='INSERT' THEN NULL ELSE OLD.column END,
        'to',        NEW.column,
        'role',      NEW.agent_role,
        'attempt',   NEW.attempt,
        'tags',      NEW.tags,
        'ts',        extract(epoch from now())
      )::text
    );
  END IF;
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_card_transition ON cards;
CREATE TRIGGER trg_card_transition
  BEFORE INSERT OR UPDATE ON cards
  FOR EACH ROW EXECUTE FUNCTION notify_card_transition();

COMMIT;

	-- KanBot v0 schema.
	-- The board is the message bus AND the state machine. A column change on a card
	-- fires a NOTIFY that the Conductor (LISTEN) picks up and dispatches on.
	--
	-- Idempotency: transitions are logged in board_events keyed on
	-- (card_id, from_col, to_col, attempt); a UNIQUE index makes replays no-ops.

	BEGIN;

	-- --------------------------------------------------------------------------- --
	-- enums mirror core/contract.py exactly. Keep them in sync.
	-- --------------------------------------------------------------------------- --
	DO $$ BEGIN
	CREATE TYPE kb_column AS ENUM
	('backlog','ready','in-flight','review','blocked','done');
	EXCEPTION WHEN duplicate_object THEN NULL; END $$;

	DO $$ BEGIN
	CREATE TYPE kb_role AS ENUM ('planner','executor','critic','human');
	EXCEPTION WHEN duplicate_object THEN NULL; END $$;

	-- --------------------------------------------------------------------------- --
	-- cards = the atoms. thread holds inter-agent comms as an ordered JSONB array.
	-- --------------------------------------------------------------------------- --
	CREATE TABLE IF NOT EXISTS cards (
	id TEXT PRIMARY KEY, -- CARD-42
	title TEXT NOT NULL,
	column kb_column NOT NULL DEFAULT 'backlog',
	agent_role kb_role NOT NULL DEFAULT 'executor',
	spec TEXT NOT NULL DEFAULT '',
	acceptance_criteria JSONB NOT NULL DEFAULT '[]'::jsonb,
	assigned_worker TEXT,
	parent TEXT REFERENCES cards(id) ON DELETE SET NULL,
	children JSONB NOT NULL DEFAULT '[]'::jsonb,
	thread JSONB NOT NULL DEFAULT '[]'::jsonb,
	lease_expires_at TIMESTAMPTZ, -- drives the reaper
	attempt INT NOT NULL DEFAULT 0,
	requires_human BOOLEAN NOT NULL DEFAULT FALSE,
	tags JSONB NOT NULL DEFAULT '[]'::jsonb, -- ["heavy"] -> cloud
	created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
	);

	CREATE INDEX IF NOT EXISTS idx_cards_column ON cards(column);
	CREATE INDEX IF NOT EXISTS idx_cards_lease ON cards(lease_expires_at)
	WHERE column = 'in-flight';

	-- --------------------------------------------------------------------------- --
	-- board_events = the append-only transition log. Also the idempotency ledger.
	-- --------------------------------------------------------------------------- --
	CREATE TABLE IF NOT EXISTS board_events (
	id BIGSERIAL PRIMARY KEY,
	card_id TEXT NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
	from_col kb_column,
	to_col kb_column NOT NULL,
	attempt INT NOT NULL DEFAULT 0,
	actor TEXT NOT NULL, -- worker id / "human:remy" / "system:reaper"
	reason TEXT,
	ts TIMESTAMPTZ NOT NULL DEFAULT now()
	);

	-- Replays of the same transition are no-ops (ON CONFLICT DO NOTHING upstream).
	CREATE UNIQUE INDEX IF NOT EXISTS uq_transition
	ON board_events(card_id, from_col, to_col, attempt);

	-- --------------------------------------------------------------------------- --
	-- NOTIFY trigger: any column change publishes a JSON payload on 'kb_events'.
	-- The Conductor LISTENs on this channel and dispatches an agent per event.
	-- --------------------------------------------------------------------------- --
	CREATE OR REPLACE FUNCTION notify_card_transition() RETURNS trigger AS $$
	BEGIN
	IF (TG_OP = 'UPDATE' AND NEW.column IS DISTINCT FROM OLD.column)
	OR TG_OP = 'INSERT' THEN
	PERFORM pg_notify(
	'kb_events',
	json_build_object(
	'card_id', NEW.id,
	'from', CASE WHEN TG_OP='INSERT' THEN NULL ELSE OLD.column END,
	'to', NEW.column,
	'role', NEW.agent_role,
	'attempt', NEW.attempt,
	'tags', NEW.tags,
	'ts', extract(epoch from now())
	)::text
	);
	END IF;
	NEW.updated_at := now();
	RETURN NEW;
	END;
	$$ LANGUAGE plpgsql;

	DROP TRIGGER IF EXISTS trg_card_transition ON cards;
	CREATE TRIGGER trg_card_transition
	BEFORE INSERT OR UPDATE ON cards
	FOR EACH ROW EXECUTE FUNCTION notify_card_transition();

	COMMIT;