app/database/README.md

Database Design

Purpose

The database directory owns PostgreSQL access, migrations, relational schema, transactions, repositories, and pgvector integration.

PostgreSQL is the canonical local data store. It runs locally through Docker Compose and stores documents, ordered structural chunks, extracted assets, embedding vectors, conversations, and /ask traces.

Files

connection.py

Creates PostgreSQL engine/session factories and verifies connectivity. It should own low-level connection lifecycle and keep driver details away from services and API handlers.

migrations.py

Applies schema migrations idempotently and tracks database version upgrades.

schema.sql

Documents the production schema. Core entities should include documents, chunks, chunk_assets, chunk_embeddings, conversation_turns, and ask_traces.

Recommended chunk identity:

chunks
- id UUID PRIMARY KEY
- document_id UUID NOT NULL
- sequence_id INTEGER NOT NULL
- parent_sequence_id INTEGER NULL
- chunk_type TEXT NOT NULL
- heading_path TEXT[]
- markdown TEXT NOT NULL
- plain_text TEXT NOT NULL
- page_start INTEGER
- page_end INTEGER
- bbox_json JSONB
- token_count INTEGER
- created_at TIMESTAMPTZ NOT NULL

UNIQUE(document_id, sequence_id)
INDEX(document_id, sequence_id)

Recommended embedding shape:

chunk_embeddings
- chunk_id UUID PRIMARY KEY REFERENCES chunks(id)
- embedding vector(VECTOR_DIMENSION) NOT NULL
- embedding_model TEXT NOT NULL
- created_at TIMESTAMPTZ NOT NULL

pgvector.py

Encapsulates every pgvector operation, including vector index assumptions, embedding insertion, nearest-neighbor search, score normalization, and conversion from vector results back to chunk IDs.

transactions.py

Provides transaction helpers for ingestion. Document metadata, ordered chunks, assets, and embedding job records should either commit together as a coherent state or fail without exposing partial documents.

repositories/documents.py

Owns CRUD operations for document metadata and ingestion status.

repositories/chunks.py

Owns chunk persistence and sequential retrieval. Critical lookups include chunk by ID, chunk by document_id + sequence_id, next chunk, previous chunk, and sequence windows.

repositories/embeddings.py

Owns embedding metadata persistence and delegates vector operations to database.pgvector.

repositories/assets.py

Stores metadata for extracted images, figures, tables, page snapshots, and layout artifacts.

repositories/conversations.py

Stores chat turns, selected routing context, router reasons, citations, and model metadata.

Data Model Rules

chunks.sequence_id is the source of truth for physical document order.

Vector search may return semantically relevant chunks, but it must not mutate or redefine the stored sequence order.

Sequential reveal should use relational queries such as:

WHERE document_id = :document_id
  AND sequence_id > :current_sequence_id
ORDER BY sequence_id ASC
LIMIT 1

Global retrieval should use pgvector similarity against chunk_embeddings.embedding, then join back to chunks for text, sequence IDs, page spans, and citations.

Data Dependencies

database is used by extraction through services.ingestion to persist ordered chunks, embeddings to store vectors, services to retrieve documents and chunks, chat.global_context for pgvector retrieval, and chat.local_context for current chunk lookup.

database should not call MinerU, Ollama, SearXNG, or FastAPI routers.