db/schema.sql

-- ============================================================================
-- IT Knowledge Base Pipeline — Postgres schema
-- Implements Storage Layer (C) from the design doc: 1 database instead of
-- Postgres + Qdrant + Elasticsearch + Neo4j + Redis/Celery.
-- ============================================================================

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgcrypto;   -- gen_random_uuid()

-- ----------------------------------------------------------------------------
-- A3.5 — section content hashes (change detection between ingest runs)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS section_hashes (
    source_id     TEXT NOT NULL,
    section_id    TEXT NOT NULL,
    content_hash  TEXT NOT NULL,
    updated_at    TIMESTAMP DEFAULT now(),
    PRIMARY KEY (source_id, section_id)
);

-- ----------------------------------------------------------------------------
-- A4 — job queue, replaces Celery + Redis
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS section_jobs (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id      UUID NOT NULL,
    source_id     TEXT NOT NULL,
    section_id    TEXT NOT NULL,
    section_text  TEXT NOT NULL,
    status        TEXT NOT NULL DEFAULT 'pending',  -- pending|processing|done|manual_review|failed
    attempt       INT NOT NULL DEFAULT 0,
    max_attempts  INT NOT NULL DEFAULT 3,
    llm_tier      TEXT NOT NULL DEFAULT 'light',     -- light|strong
    next_run_at   TIMESTAMP NOT NULL DEFAULT now(),
    last_error    TEXT,
    created_at    TIMESTAMP DEFAULT now(),
    updated_at    TIMESTAMP DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_section_jobs_poll
    ON section_jobs (status, next_run_at);

-- ----------------------------------------------------------------------------
-- Knowledge atoms == verified elements after A4 (+ dedup B1 + embeddings B2)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS knowledge_atoms (
    id                TEXT PRIMARY KEY,           -- stable element id
    source_id         TEXT NOT NULL,
    section_id        TEXT NOT NULL,
    chapter           TEXT,
    page_start        INT,
    label             TEXT,                       -- FACT|DEFINITION|CONSTRAINT|EXAMPLE|WARNING|OTHER
    raw               TEXT NOT NULL,               -- verbatim substring from source (A4 verification)
    normalized        TEXT,                        -- LLM-normalized/paraphrased form
    confidence        REAL,
    contradicts_flag  BOOLEAN DEFAULT FALSE,
    embedding         vector(768),
    search_vector     tsvector,
    created_at        TIMESTAMP DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_atoms_embedding
    ON knowledge_atoms USING hnsw (embedding vector_cosine_ops);
CREATE INDEX IF NOT EXISTS idx_atoms_search
    ON knowledge_atoms USING gin (search_vector);
CREATE INDEX IF NOT EXISTS idx_atoms_source_section
    ON knowledge_atoms (source_id, section_id);

CREATE OR REPLACE FUNCTION knowledge_atoms_search_vector_trigger() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', coalesce(NEW.normalized, NEW.raw, ''));
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_atoms_search_vector ON knowledge_atoms;
CREATE TRIGGER trg_atoms_search_vector
BEFORE INSERT OR UPDATE ON knowledge_atoms
FOR EACH ROW EXECUTE FUNCTION knowledge_atoms_search_vector_trigger();

-- ----------------------------------------------------------------------------
-- B2 — Knowledge Graph (replaces Neo4j)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS kg_nodes (
    id    TEXT PRIMARY KEY,
    label TEXT,
    type  TEXT   -- entity|atom
);

CREATE TABLE IF NOT EXISTS kg_edges (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_id    TEXT REFERENCES kg_nodes(id),
    target_id    TEXT REFERENCES kg_nodes(id),
    relation     TEXT NOT NULL,       -- DEFINES|EXTENDS|CONTRADICTS|...
    evidence_raw TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_edges_source ON kg_edges(source_id);
CREATE INDEX IF NOT EXISTS idx_edges_target ON kg_edges(target_id);

-- ----------------------------------------------------------------------------
-- B2 — chunk records (parent/child, late chunking)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS chunk_records (
    id         TEXT PRIMARY KEY,
    type       TEXT NOT NULL,          -- parent|child
    content    TEXT NOT NULL,
    parent_id  TEXT REFERENCES chunk_records(id),
    embedding  vector(768),
    metadata   JSONB
);
CREATE INDEX IF NOT EXISTS idx_chunk_embedding
    ON chunk_records USING hnsw (embedding vector_cosine_ops);

-- ----------------------------------------------------------------------------
-- C — batch monitoring (replaces Sentry + Flower)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS batch_runs (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    started_at              TIMESTAMP DEFAULT now(),
    finished_at             TIMESTAMP,
    total_documents         INT DEFAULT 0,
    total_sections          INT DEFAULT 0,
    failed_sections         INT DEFAULT 0,
    manual_review_sections  INT DEFAULT 0
);