upgraded-sniffle

A document search backend for large collections of public PDF documents. Given a collection of PDFs, the system ingests them into a PostgreSQL + pgvector database and exposes a semantic search API.

---

Architecture

┌─────────────────┐        ┌──────────────────┐
│  ingestion_cli  │──────▶ │                  │
│  (one-shot)     │        │   PostgreSQL      │
└─────────────────┘        │   + pgvector      │
                           │                  │
┌─────────────────┐        │                  │
│   search_api    │──────▶ │                  │
│   (FastAPI)     │        └──────────────────┘
└─────────────────┘

Ingestion pipeline:

1. Read all PDF files from the input folder recursively
2. Compute MD5 checksum — skip or handle conflicts for already-ingested files
3. Extract text page by page with pdfplumber
4. Normalise whitespace; skip empty pages (e.g. scanned pages with no text layer)
5. Split each page into chunks at word boundaries, with fixed character overlap and a configurable maximum chunk size (CHUNK_MAX_SIZE)
6. Generate embeddings with a local sentence-transformers model (CPU)
7. Store document record + chunks + embeddings in PostgreSQL via pgvector

Search API:

• POST /search — vector similarity search, returns top-k chunks with metadata
• GET /healthz — liveness probe
• GET /db_healthz — readiness probe (connection, table, index)
• GET /documents/{id} — retrieve document metadata by ID
• GET /documents/by_checksum/{checksum} — look up document by MD5 checksum
• GET /documents/by_name/{name} — look up document by filename

Shared library (common): Both services share a common package (uv workspace member) containing database connection management (DBManager) and configuration (Settings). This avoids duplicating database logic across the two components.

---

Requirements

• Docker and Docker Compose

No other dependencies are required on the host.

---

Setup

1. Clone the repository

git clone https://github.com/iarspider/upgraded-sniffle.git
cd upgraded-sniffle

2. Configure environment

cp .env.example .env

Edit .env if needed. Defaults work out of the box.

Variable	Default	Description
API_PORT	8000	Port exposed by the search API on the host
POSTGRES_HOST	postgres	PostgreSQL hostname (service name in Docker Compose)
POSTGRES_DB	pdf_search	Database name
POSTGRES_USER	postgres	Admin user — owns the database
POSTGRES_PASSWORD	postgres	Admin user password
POSTGRES_INGESTION_USERNAME	ingestion	Ingestion user — write access to documents and chunks
POSTGRES_INGESTION_PASSWORD	ingestion	Ingestion user password
POSTGRES_WEB_USERNAME	web	Web user — read-only access to documents and chunks
POSTGRES_WEB_PASSWORD	web	Web user password
EMBEDDING_MODEL	sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2	HuggingFace model name used to generate embeddings
EMBEDDING_DIM	384	Embedding vector dimension — must match the model
OVERLAP_SIZE	100	Number of characters carried over from adjacent pages as overlap
CHUNK_MAX_SIZE	1000	Maximum chunk size in characters — chunks are split at word boundaries

3. Build images

docker compose build

---

Running the ingestion

Place your PDF files in a local folder, then run:

chmod +x scripts/ingest.sh
./scripts/ingest.sh /path/to/your/pdfs

The script mounts the folder read-only into the container and runs the ingestion pipeline. On first run the embedding model weights will be downloaded (~500 MB) — subsequent runs use the Docker volume cache.

Ingestion options

./scripts/ingest.sh [options] <path-to-pdf-folder> [options]

  --conflict, -c  skip|overwrite|add
                  What to do when a file with the same name but different
                  content was already ingested (default: skip).
                    skip      — keep the existing version
                    overwrite — delete the old record and re-ingest
                    add       — keep both versions

  --reset yesiamsure
                  Delete all documents and chunks before ingesting.

  --no-progress   Disable the progress bar (useful for non-interactive runs).

The path to the PDF folder is detected automatically by checking whether each argument is an existing directory — options can appear before or after the path.

Verify the ingestion results

Check that documents were loaded into the database:

docker compose exec postgres psql -U postgres -d pdf_search \
  -c "SELECT name, ingested_at FROM documents;"

Look up a specific document by name:

curl http://localhost:8000/documents/by_name/example.pdf

Check whether all PDFs in a folder have already been ingested by checksum (useful before a re-run to see what would be skipped):

find /path/to/your/pdfs -iname '*.pdf' -exec sh -c \
  'echo "$1:"; curl -s http://localhost:8000/documents/by_checksum/$(md5sum "$1" | cut -d" " -f1)' _ {} \;

A 200 response means the file is already indexed; 404 means it will be ingested on the next run.

These endpoints are intended for diagnostics and demonstration purposes. They are not required by the assignment but make it easier to verify that ingestion, deduplication and persistence work correctly.

---

Starting the API

docker compose up search_api

The API is available at http://localhost:${API_PORT} (default: http://localhost:8000).

Interactive docs: http://localhost:8000/docs

---

API endpoints

POST /search

curl -X POST http://localhost:8000/search \
  -H "Content-Type: application/json" \
  -d '{
    "query": "Quelle est la position du document sur les politiques publiques ?",
    "top_k": 5
  }'

Optional min_score parameter filters out results below a cosine similarity threshold:

{ "query": "...", "top_k": 5, "min_score": 0.6 }

Example response:

{
  "query": "Quelle est la position du document sur les politiques publiques ?",
  "results": [
    {
      "document_name": "example.pdf",
      "page_number": 3,
      "chunk_index": 3,
      "similarity_score": 0.82,
      "chunk_text": "Contenu du passage correspondant..."
    }
  ]
}

GET /documents/by_checksum/{checksum}

Look up a document by its MD5 checksum. Returns 404 if not found. Useful for the ingestion client to check whether a file has already been processed before sending it.

curl http://localhost:8000/documents/by_checksum/d41d8cd98f00b204e9800998ecf8427e

GET /healthz / GET /db_healthz

curl http://localhost:8000/healthz
# {"status": "ok"}

curl http://localhost:8000/db_healthz
# {"status": "ok", "checks": {"connection": true, "table": true, "index": true}}
# Returns 503 if any check fails.

---

Rebuilding the index

Re-run the ingestion script. By default it skips already-ingested files (same checksum). To force a full rebuild:

./scripts/ingest.sh /path/to/your/pdfs --reset yesiamsure

---

Running the tests

# Unit tests only (no Docker required)
uv run pytest

# Integration tests only (requires Docker)
uv run pytest -m integration

# All tests
uv run pytest -m 'integration or not integration'

# Coverage report (unit + integration)
uv run coverage erase
uv run coverage run -m pytest
uv run coverage run --append -m pytest -m integration
uv run coverage report -m

Test structure

tests/
├── unit/
│   ├── test_normalize_whitespace.py   — whitespace normalisation edge cases
│   ├── test_extract_text.py           — PDF extraction with real generated PDFs (fpdf2)
│   ├── test_split_into_subchunks.py   — chunking logic and word boundary splitting
│   └── test_process_file.py           — conflict resolution logic (all DB calls mocked)
└── integration/                       — require Docker (testcontainers)
    ├── conftest.py                    — shared session-scoped fixtures (container, schema, seed data)
    ├── test_search_api.py             — search, document lookup and health endpoints
    ├── test_ingestion_db.py           — delete_document and lookup_document_metadata against real DB
    └── test_end2end.py                — full pipeline: ingest a PDF, verify via API

Unit tests run without any external dependencies. Integration tests spin up a temporary pgvector/pgvector:pg16 container via testcontainers — Docker must be available on the host.

---

Makefile targets

Target	Description
build	Build both Docker images (search_api and ingestion_cli)
run	Start the API in detached mode (docker compose up -d)
stop	Stop all services (docker compose down)
clean	Stop all services and remove volumes (docker compose down -v)
test	Run unit tests (uv run pytest)
itest	Run integration tests (uv run pytest -m integration)
coverage	Run unit + integration tests and generate an HTML coverage report
archive	Create a project.tar.bz2 archive of all tracked and untracked files

---

Design notes

PDF extraction

Text is extracted with pdfplumber. The documents in this exercise are predominantly text-based (not scanned), with no complex layout requirements — pdfplumber handles this well with a simple API.

More advanced tools such as docling offer layout analysis, OCR and better handling of multi-column pages and tables, but these capabilities are not needed here.

Scanned pages (no text layer) are detected automatically — page.extract_text() returns an empty string or None for pages without extractable text. Such pages are skipped with a warning after whitespace normalisation, and the document is still ingested from its remaining pages.

Extracted text may still contain artefacts such as headers, footers, page numbers and inconsistent whitespace. Whitespace is normalised; other artefacts are not cleaned aggressively; see Limitations.

Incremental ingestion

Each PDF file is identified by its MD5 checksum before ingestion. The ingestion CLI queries the database by checksum and by filename to detect four situations:

• Same checksum, same name: already ingested — skip.
• Same checksum, different name: duplicate content — skip with a warning.
• Same name, different checksum: file has changed — behaviour controlled by --conflict.
• No match: ingest normally.

Document records and their chunks are stored in separate tables (documents and chunks). Chunks reference their document via a foreign key with ON DELETE CASCADE, so deleting a document (on overwrite) automatically removes all associated chunks.

Vector storage

pgvector was chosen over file-based stores (FAISS, Annoy) because:

• no separate serialisation/deserialisation step between ingestion and the API
• metadata and vectors live in the same database, making filtered search straightforward to add later
• consistent with a production PostgreSQL setup

Chunk metadata is stored as individual columns rather than a JSON blob because the schema is fixed and known upfront. Typed columns are cheaper to index and query. In a production system handling richer or variable metadata — file origin, document date, municipality, topic tags, or fields extracted from the PDF itself — a hybrid approach would make sense: keep structured fields used for filtering as columns, and store the rest in a JSONB column. PostgreSQL supports GIN indexes on JSONB, so filtered vector search over arbitrary metadata remains efficient without a schema migration every time a new metadata field is added.

Similarity search uses cosine distance with an IVFFlat index.

Database roles

Three PostgreSQL roles are used:

• admin (POSTGRES_USER) — owns the database, runs schema migrations
• ingestion — can insert and delete from documents and chunks
• web — read-only access to documents and chunks

This limits the impact of a compromised service.

Embedding model

sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2 (384 dimensions) — multilingual, runs on CPU, reasonable quality/speed trade-off.

---

Limitations and possible improvements

Current limitations

• Chunking is page-level: text is split page by page with overlap. Splits respect word boundaries but not sentence or paragraph boundaries, so a chunk may cut mid-sentence. Very long pages may produce multiple chunks.
• Scanned documents are not supported: pages without a text layer are skipped. OCR is not performed.
• No text cleaning: headers, footers and page numbers extracted from PDFs are included in chunks and may dilute embedding quality.
• No authentication: the API has no auth layer.

Search quality may be poor when

• A query requires synthesising information spread across multiple non-adjacent passages.
• Documents contain dense legal or administrative boilerplate that dominates embeddings.
• Queries use vocabulary or phrasing very different from the document text — paraphrase-trained models can help bridge the gap, but semantic similarity is not guaranteed across all query styles.

Assumptions

• PDF files are downloaded locally before ingestion; the system does not fetch them.
• Documents are primarily in French; the embedding model handles French well.
• The dataset is small enough for exact IVFFlat search; approximate indexing is not required.

What I would improve with more time

• OCR support: integrate docling or tesseract to handle scanned pages.
• Better PDF extraction: switch to docling for documents with complex layouts (tables, multi-column pages).
• Smarter chunking: sentence-boundary-aware splitting using spaCy or nltk would better preserve semantic units.
• Text cleaning: strip repeated headers/footers, normalise whitespace more aggressively.
• Hybrid search: combine vector similarity with BM25 full-text search (PostgreSQL tsvector) for better recall on keyword-heavy queries.
• Reranking: add a cross-encoder reranker as a second stage for improved precision.
• Query expansion: generate alternative phrasings of the query before searching.

Production considerations

This project is intentionally minimal. A production-grade version would require significant additional work across several areas:

Observability: structured logging, distributed tracing and metrics are absent. A production system would need full instrumentation — OpenTelemetry for tracing, Prometheus + Grafana for metrics, and a centralised log aggregation solution (ELK, Loki). Without this, diagnosing latency issues or ingestion failures at scale is very difficult.

Search quality evaluation: there is no way to measure whether the system is returning good results. A production system would require a golden dataset — a curated set of queries with known relevant documents — to benchmark retrieval quality (recall@k, MRR, NDCG) and detect regressions when the model or chunking strategy changes.

Scalability:

• Replace IVFFlat with HNSW for better recall at scale.
• Async ingestion with a task queue (Celery, ARQ) for large document sets.
• Connection pooling (PgBouncer) for the API under load.

Reliability:

• Add authentication (API key or OAuth2).
• CI/CD pipeline with linting, type checking and integration tests.
• SQLAlchemy Core (without ORM) for more complex queries and safer parameter handling.