AI knowledge base (pgvector)

CREATE EXTENSION IF NOT EXISTS vector;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE workspaces (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE sources (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    workspace_id uuid NOT NULL,
    kind text NOT NULL,
    uri text NOT NULL,
    last_synced_at timestamptz
);

CREATE TABLE documents (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    source_id uuid NOT NULL,
    title text NOT NULL,
    content_hash text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE chunks (
    id bigserial PRIMARY KEY,
    document_id uuid NOT NULL,
    position integer NOT NULL,
    content text NOT NULL,
    token_count integer NOT NULL,
    embedding vector(1536) NOT NULL
);
CREATE INDEX chunks_embedding_hnsw ON chunks USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
CREATE INDEX chunks_content_trgm ON chunks USING gin (content gin_trgm_ops);
CREATE INDEX chunks_document_idx ON chunks (document_id, position);

CREATE TABLE queries (
    id bigserial PRIMARY KEY,
    workspace_id uuid NOT NULL,
    text text NOT NULL,
    embedding vector(1536),
    asked_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE feedback (
    id bigserial PRIMARY KEY,
    query_id bigint NOT NULL,
    chunk_id bigint NOT NULL,
    helpful boolean NOT NULL
);

ALTER TABLE sources ADD FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE;

ALTER TABLE documents ADD FOREIGN KEY (source_id) REFERENCES sources (id) ON DELETE CASCADE;

ALTER TABLE chunks ADD FOREIGN KEY (document_id) REFERENCES documents (id) ON DELETE CASCADE;

ALTER TABLE queries ADD FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE;

ALTER TABLE feedback ADD FOREIGN KEY (query_id) REFERENCES queries (id) ON DELETE CASCADE;

ALTER TABLE feedback ADD FOREIGN KEY (chunk_id) REFERENCES chunks (id) ON DELETE CASCADE;