docs/persistence_schema.md

# Persistence Schema

This document describes the first persistence slice for the RAG backend. The
schema exists independently of the live FastAPI pipeline: ingestion, retrieval,
answer generation, and citation generation are not writing to these tables yet.

## Database Target

Intended production persistence uses a managed MySQL database supplied through
the `DATABASE_URL` environment variable, for example with a
`mysql+pymysql://...` SQLAlchemy URL.

Managed MySQL requires TLS. Set `DB_CA_CERT` to the local CA certificate path so
SQLAlchemy/PyMySQL can connect with certificate verification enabled. The CA
certificate file is local deployment material and must not be committed.

For Hugging Face Docker Space deployment, do not commit the CA certificate.
Encode the CA certificate locally and store the base64 string as the
`DB_CA_CERT_B64` Space secret. On startup, the app decodes that secret into a
temporary CA file and uses it for SQLAlchemy SSL verification.

Required Hugging Face Space secrets for the deployed app are:

- `DATABASE_URL`
- `DB_CA_CERT_B64`
- `GROQ_API_KEY`
- `API_TOKEN`

Deployment to the Hugging Face Space is manual through the `hf` git remote;
there is no GitHub auto-sync workflow in this repo.

SQLite is only the local/development fallback for smoke testing and quick schema
checks. If `DATABASE_URL` is not set, the persistence layer defaults to
`sqlite:///./rag_persistence.db`.

Set `DATABASE_URL` through environment variables in deployment. For local
development it may be placed in `.env`, but `.env` must never be committed
because it can contain database credentials.

## User Identity

The schema includes `user_id` on every user-owned table before OAuth exists. For
local development, `persistence.user_context.get_current_user_id()` returns the
stable placeholder `local-dev-user`.

Adding `user_id` now prevents a later migration where historical documents,
chunks, queries, and citations would need to be backfilled or repartitioned by
owner. When OAuth is added, it should replace only `get_current_user_id()` with a
real authenticated identity lookup; the schema stays unchanged.

## Why Eval Tables Are Excluded

Evaluation reports, benchmark questions, metrics, and experiment artifacts are
not part of the application persistence model. They remain file-based under
`eval/` and generated reports remain ignored under `eval/results/`.

Keeping eval data out of this schema avoids mixing production user history with
benchmark-only records and keeps this slice focused on app-level document and
query history.

## Why Embeddings And FAISS Blobs Are Excluded

This slice stores source text and retrieval metadata, not vector artifacts.
Embeddings and FAISS indexes are intentionally excluded because:

- they can be regenerated from chunks and the recorded embedding config,
- they are large compared with the relational metadata,
- their binary format and compatibility depend on model/index implementation,
- cache invalidation is already tied to embedding model and input format.

Future persistence can add a dedicated vector store or artifact cache if needed.

## Dedup Behavior

Document deduplication uses `(user_id, source_hash)`, but `source_hash` is
computed differently by ingestion path:

- Upload ingestion uses the SHA-256 hash of the uploaded PDF bytes.
- URL ingestion uses a hash of the existing document cache key, because the URL
  loader does not retain raw PDF bytes after chunking.

Known limitation: the same PDF ingested once by upload and once by URL will not
deduplicate across those paths because the two source hashes differ. This is an
accepted tradeoff for now to avoid refactoring the URL loader and cache flow.

## Tables

### `users`

Stores the application user identity.

- `id` primary key
- optional profile/auth fields: `email`, `display_name`, `auth_provider`
- timestamps: `created_at`, `updated_at`

### `documents`

Stores one ingested PDF per user and the retrieval configuration used for it.

- `user_id` references `users.id`
- input metadata: `source_type`, `filename`, `source_url`, `source_hash`,
  `cache_key`
- lifecycle fields: `status`, `error_message`
- retrieval config: `embedding_model`, `embedding_format`, `retrieval_mode`,
  `reranker_model`, `k_initial`, `k_final`
- timestamps: `created_at`, `updated_at`

Index: `documents(user_id, created_at)`.

### `chunks`

Stores page-aware text chunks for a document.

- `user_id` references `users.id`
- `document_id` references `documents.id`
- chunk identity: `chunk_id`, `chunk_index`
- location/content: `page_number`, `text`, `text_hash`, `char_count`
- timestamp: `created_at`

Indexes:

- `chunks(user_id, document_id)`
- unique `chunks(document_id, chunk_id)`

### `queries`

Stores one question/answer result against a persisted document.

- `user_id` references `users.id`
- `document_id` references `documents.id`
- answer fields: `question`, `answer`, `status`, `is_abstained`
- optional structured verification payload: `claim_verifications_json`
- retrieval config: `embedding_model`, `retrieval_mode`, `reranker_model`,
  `k_initial`, `k_final`
- optional timing: `latency_ms`
- timestamp: `created_at`

Index: `queries(user_id, document_id, created_at)`.

### `citations`

Stores source chunks returned for a query.

- `user_id` references `users.id`
- `query_id` references `queries.id`
- `document_id` references `documents.id`
- optional chunk relation: `chunk_db_id`
- response-facing source fields: `chunk_id`, `rank`, `page_number`, `excerpt`
- optional scores: `retrieval_score`, `reranker_score`
- timestamp: `created_at`

Indexes:

- `citations(user_id, query_id)`
- `citations(query_id, rank)`

## Relationships

- A user has many documents, chunks, queries, and citations.
- A document has many chunks, queries, and citations.
- A query has many citations.
- A citation may point to a stored chunk through `chunk_db_id`; it also stores
  response-facing `chunk_id` and excerpt so citations remain readable even if a
  chunk relationship is unavailable.