File size: 5,515 Bytes
096dfff | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | -- ============================================================================
-- 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
);
|