Hugging Face's logo

Spaces:
LoveLogicAI
/
kanbot-backend
Paused

App Files Community
1
kanbot-backend / sql /001_schema_quoted.sql
Jeremy Jones
Self-contained Postgres + web server Dockerfile, entrypoint, and quoted schema
bd7e398
Raw History Blame Contribute Delete
4.2 kB
-- KanBot v0 schema — column names quoted.
-- `column` is a reserved keyword in PostgreSQL, so every reference to the
-- cards.column / trigger NEW.column / OLD.column must be double-quoted.
-- This file is the version used by the HF Space Docker image.
-- Idempotent: IF NOT EXISTS / DROP TRIGGER IF EXISTS / DO $$ ... EXCEPTION.
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;