Database Schema

Database Schema

The vector database runs on phlegethon (LXC 302), a PostgreSQL 18 instance with the pgvector extension.

Host: phlegethon.c0smere.net:5432 Database: obsidian_rag User: rag_indexer Extension: pgvector

Table: documents

Single table design — every chunk from every indexed file lives here.

CREATE TABLE documents (
    id          SERIAL PRIMARY KEY,
    file_path   TEXT NOT NULL,
    file_hash   TEXT NOT NULL,
    chunk_index INTEGER NOT NULL,
    heading     TEXT,
    content     TEXT NOT NULL,
    embedding   vector(768) NOT NULL,
    content_tsv TSVECTOR,
    metadata    JSONB DEFAULT '{}',
    updated_at  TIMESTAMP DEFAULT NOW(),
    UNIQUE (file_path, chunk_index)
);

Columns

Constraints

• UNIQUE (file_path, chunk_index) — Prevents duplicate chunks for the same file.

Table: note_links

Stores wikilink relationships between notes, extracted by the indexer.

CREATE TABLE note_links (
    id          SERIAL PRIMARY KEY,
    source_path TEXT NOT NULL,
    target_name TEXT NOT NULL,
    target_path TEXT,
    updated_at  TIMESTAMP DEFAULT NOW()
);

Columns

Links are file-to-file relationships, not chunk-level. The indexer extracts [[wikilinks]] from the full file text (after stripping code blocks), filters out attachments and code artifacts, then stores one row per unique source→target pair.

After all files are indexed, a resolution pass matches target_name against indexed file stems (case-insensitive) to populate target_path.

Indexes

documents_pkey

PRIMARY KEY, btree (id)

Standard auto-increment PK.

documents_embedding_idx

CREATE INDEX documents_embedding_idx
    ON documents USING hnsw (embedding vector_cosine_ops);

HNSW (Hierarchical Navigable Small World) index for approximate nearest neighbor vector search. Uses cosine distance, matching the <=> operator in search queries.

documents_content_tsv_idx

CREATE INDEX documents_content_tsv_idx
    ON documents USING gin(content_tsv);

GIN index for PostgreSQL full-text search. Enables the @@ operator for tsvector matching in keyword search mode.

documents_file_path_chunk_index_key

UNIQUE CONSTRAINT, btree (file_path, chunk_index)

Enforces uniqueness and accelerates get_note() queries which filter by file_path.

note_links_source_idx / note_links_target_path_idx

CREATE INDEX note_links_source_idx ON note_links(source_path);
CREATE INDEX note_links_target_path_idx ON note_links(target_path);

Support efficient graph traversal in both directions (outgoing from a note, incoming to a note).

Query Patterns

Vector Search (search_notes, mode=“vector”)

SELECT id, file_path, heading, content, chunk_index,
       1 - (embedding <=> $1::vector) AS score
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT $2

Keyword Search (search_notes, mode=“keyword”)

SELECT id, file_path, heading, content, chunk_index,
       ts_rank_cd(content_tsv, q) AS score
FROM documents,
     websearch_to_tsquery('english', $1) q
WHERE content_tsv @@ q
ORDER BY score DESC
LIMIT $2

Falls back to plainto_tsquery if websearch_to_tsquery returns no results (more lenient parsing).

Hybrid Search (search_notes, mode=“hybrid”)

Fetches 3x candidates from both vector and keyword, then applies Reciprocal Rank Fusion (RRF, k=60) to combine the ranked lists. Top results are expanded with neighboring chunks (chunk_index ± 1).

Graph Traversal (graph_search)

-- Outgoing links from a note
SELECT DISTINCT target_name, target_path
FROM note_links WHERE source_path = $1;

-- Incoming links to a note
SELECT source_path, target_name
FROM note_links WHERE target_path = $1;

BFS traversal up to 3 hops, both directions.

Neighbor Expansion

SELECT id, file_path, heading, content, chunk_index, 0.0 AS score
FROM documents
WHERE file_path = $1
  AND chunk_index IN ($2 - 1, $2 + 1)
  AND id != $3
ORDER BY chunk_index

Upsert Pattern (Indexer)

DELETE FROM documents WHERE file_path = $1;
INSERT INTO documents
    (file_path, file_hash, chunk_index, heading, content, embedding,
     content_tsv, metadata, updated_at)
VALUES ($1, $2, $3, $4, $5, $6::vector,
        to_tsvector('english', coalesce($4, '') || ' ' || $5),
        $7::jsonb, NOW());

The content_tsv is computed server-side via to_tsvector during INSERT, keeping the tsvector in sync with heading + content.

Current Stats

 total_chunks | total_files | total_links | resolved_links
--------------+-------------+-------------+----------------
          742 |         236 |         136 |             62

Setup

If recreating from scratch:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id          SERIAL PRIMARY KEY,
    file_path   TEXT NOT NULL,
    file_hash   TEXT NOT NULL,
    chunk_index INTEGER NOT NULL,
    heading     TEXT,
    content     TEXT NOT NULL,
    embedding   vector(768) NOT NULL,
    content_tsv TSVECTOR,
    metadata    JSONB DEFAULT '{}',
    updated_at  TIMESTAMP DEFAULT NOW(),
    UNIQUE (file_path, chunk_index)
);

CREATE INDEX documents_embedding_idx ON documents USING hnsw (embedding vector_cosine_ops);
CREATE INDEX documents_content_tsv_idx ON documents USING gin(content_tsv);

CREATE TABLE note_links (
    id          SERIAL PRIMARY KEY,
    source_path TEXT NOT NULL,
    target_name TEXT NOT NULL,
    target_path TEXT,
    updated_at  TIMESTAMP DEFAULT now()
);
CREATE INDEX note_links_source_idx ON note_links(source_path);
CREATE INDEX note_links_target_path_idx ON note_links(target_path);

CREATE USER rag_indexer WITH PASSWORD '<password>';
GRANT ALL ON ALL TABLES IN SCHEMA public TO rag_indexer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO rag_indexer;