Separate source identity from language variants

The schema has three core tables. sources records each feed and its language. articles stores the discovered URL, source, original title and body, category, summaries and evaluation fields. article_translations stores one row per article and language, keyed by (article_id, lang), with a translated title, body, summary, and optional vector.

This design avoids repeating source metadata in every translation while making it possible to retrieve the requested language directly. The original source version is also represented among the translation rows when that language is configured. For article details, the query uses COALESCE to fall back to the original fields if a requested translation row is absent; the list endpoint joins only existing translations.

Keep one article write atomic

insert_article opens a database transaction, inserts the parent article, then inserts each language row. If a translation insert fails, the transaction prevents a half-written article from appearing as complete. The article URL is unique, and translation rows have a composite primary key, so these constraints are the final defense against duplicate writes.

The schema is created by SQL files in db/init when the Postgres data volume is initialized. That is convenient for a fresh local stack, but these files are not a migration system: an existing pgdata volume will not automatically receive later schema changes. The project currently has no versioned database migration workflow.

Store a vector beside the text it represents

For each language, the embed node builds one string from the translated title, summary, and at most the first 6,000 body characters. The vector is stored on the matching translation row. The schema fixes the column at vector(1024) and creates an HNSW index with cosine distance, so semantic search can rank language-specific article records without moving text to a separate vector service.

The configured Mistral embedding model and stub provider both target 1,024 dimensions. The OpenAI option selects text-embedding-3-small, but its request does not ask for a reduced dimension while the database column remains 1,024. Unless the endpoint is configured to return 1,024 values elsewhere, that path is not a drop-in match. A real switch must align provider output, schema dimension, and all stored vectors.

embedding vector(1024),
PRIMARY KEY (article_id, lang)

CREATE INDEX idx_translations_embedding
ON article_translations
USING hnsw (embedding vector_cosine_ops);

The index is useful, but not a quality guarantee

The HNSW index is an approximate-nearest-neighbor structure: it trades some search exactness for faster candidate lookup as a collection grows. The repository orders by pgvector’s cosine-distance operator and converts distance to a similarity-like score. That score is not a probability that the result answers a question.

Vectors also depend on their model and preprocessing. The schema does not record an embedding model version, and there is no re-embedding migration in the code. Changing the embedding model while old rows remain can mix vectors from different spaces even if their dimensions happen to match. The next note follows how a query uses these stored vectors and how Wassla turns retrieved records into a cited answer.