schema.sql

-- Knowledge Base PostgreSQL Schema
-- Requires: pgvector extension

-- Enable vector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Main documents table
CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  source_path TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  category VARCHAR(100),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Summaries table with vector embeddings
CREATE TABLE summaries (
  id BIGSERIAL PRIMARY KEY,
  document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  tokens INT,
  embedding VECTOR(1536),
  model_name TEXT NOT NULL,
  generation_timestamp TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_documents_category ON documents(category);
CREATE INDEX idx_documents_updated ON documents(updated_at);
CREATE INDEX idx_documents_metadata ON documents USING gin(metadata);

CREATE INDEX summaries_content_gin ON summaries USING gin(to_tsvector('english', content));
CREATE INDEX summaries_embedding_idx ON summaries USING ivfflat(embedding vector_l2_ops) WITH (lists = 100);