# Lila Data Pipeline

> How vocabulary data is generated and gets into PostgreSQL.
> Last updated: 2026-08-01 · Branch: `refactor/gemini-only-pipeline`

**Authoritative detail lives in two companion docs:**

| Doc                                              | What's in it                                                                   |
| ------------------------------------------------ | ------------------------------------------------------------------------------ |
| [pipeline/design-doc.md](pipeline/design-doc.md) | Schema design, difficulty model, query patterns, Gemini JSON contract, indexes |
| [pipeline/roadmap.md](pipeline/roadmap.md)       | Phase-by-phase plan with task checklists and acceptance criteria               |

This file is the orientation layer: what the pipeline is, what exists on disk today, and what is not built yet.

The previous local-LLM pipeline (llama.cpp, adapter pattern, 10-model evaluation, CEFR voter ensemble, Kaikki gender lookup) has been removed from the codebase. Its documentation is preserved under `archive/` and describes `utils/` and `config/` modules that **no longer exist**:

- [archive/data-pipeline-local-llm.md](archive/data-pipeline-local-llm.md) — the old pipeline stages and file layout
- [archive/llm-setup-local.md](archive/llm-setup-local.md) — llama.cpp / cloud provider configuration
- [archive/model-strategy-cefr-voters.md](archive/model-strategy-cefr-voters.md) — the multi-model voter architecture for sense-disambiguated CEFR assignment

---

## What changed, and why

The old pipeline ran small local models and needed a deterministic Kaikki Wiktionary lookup to patch grammatical gender, because local models hallucinated it. The rewrite drops local inference entirely in favour of the Gemini API: one provider, no adapter layer, gender produced directly by the model and enforced by validation instead of by a second data source.

The data model changed with it. The live `vocabulary_entries` / `entry_translations` tables (one row per word sense, populated from Kaikki) are replaced by `words` → `senses` → `translations`, where translations hang off a **sense**, not off a flat entry. That is the whole point of the rewrite: a quiz question can now be tied to one specific meaning of a word.

---

## Flow

```
source-data/{lang}/{pos}   frequency wordlists, one word per line, UTF-8
        │
        ▼
Gemini API                 batches of 20 words, one language at a time
        │
        ▼
validation                 per-entry; invalid entries → rejection log, not the DB
        │
        ▼
db/staging.db              SQLite staging (words, senses, translations)
        │
        ▼
import script              SQLite → PostgreSQL via Drizzle, transaction per batch
        │
        ▼
PostgreSQL (dev :5432, then prod)
```

Each language is processed independently so definitions and examples are written **in that language** — a German word gets a German definition, not a translation of an English one. Only the translations cross language boundaries.

The app always reads from PostgreSQL. SQLite exists purely as a staging file so re-runs, prompt tweaks, and spot-checks never touch a real database.

---

## What exists on disk today

| Path                                     | State                                                                     |
| ---------------------------------------- | ------------------------------------------------------------------------- |
| `data-pipeline/source-data/{lang}/{pos}` | ✅ Noun lists for `de`, `en`, `es`, `fr`, `it`                            |
| `data-pipeline/prompt`                   | ✅ The Gemini system prompt (plain UTF-8 text, not a module)              |
| `data-pipeline/db/schema.sql`            | ✅ SQLite staging schema                                                  |
| `data-pipeline/db/staging.db`            | ✅ Created, tables present, **0 rows** — gitignored                       |
| `data-pipeline/pipeline.ts`              | 🚧 Design pseudocode in comments. No executable pipeline code yet.        |
| Validation module                        | ❌ Not written (rules specced in design-doc §6.4)                         |
| SQLite → PostgreSQL import script        | ❌ Not written                                                            |
| `data-pipeline/kaikki-source-files/`     | ⚠️ Leftover JSONL dumps from the old pipeline; nothing reads them anymore |
| `data-pipeline/worddata/english/nouns/`  | ⚠️ Empty leftover output directory from the old per-word-JSON design      |

Directory naming follows the language/POS codes used in `packages/shared/src/constants.ts` (`de/noun`, not `german/nouns`) so no name mapping is needed anywhere in the pipeline.

Note that `data-pipeline/vitest.config.ts` looks for tests in `tests/**/*.test.ts` — that directory does not exist yet.

---

## Staging schema

`data-pipeline/db/schema.sql` mirrors the PostgreSQL schema with two SQLite concessions: IDs are `TEXT` (`crypto.randomUUID()`), and `definitions` / `examples` are JSON-encoded strings because SQLite has no array type. The import script parses them back into PostgreSQL `TEXT[]`.

```
words        id, headword, language_code, pos          UNIQUE(headword, language_code, pos)
senses       id, word_id→words, sense_index,           UNIQUE(word_id, sense_index)
             difficulty, definitions, examples
translations id, sense_id→senses, target_language_code, UNIQUE(sense_id, target_language_code, translation)
             translation, gender, difficulty
```

`difficulty` is `easy | medium | hard` on both `senses` and `translations`, and they mean different things — sense difficulty is "is this _meaning_ appropriate for the level", translation difficulty is "is this _word_ an acceptable answer". design-doc §4 explains how queries use sense difficulty as a ceiling and translation difficulty as the target.

---

## The prompt

`data-pipeline/prompt` is the current working prompt, checked in as a plain text file and edited by hand. It is currently pinned to a concrete sample run (Spanish nouns, 20 words inlined) rather than templated — source language, POS, target languages, and the word batch will need to become substitutions when `pipeline.ts` is implemented.

What it enforces, beyond the JSON shape in design-doc §6.3:

- Raw JSON only — no markdown fences, comments, or trailing commas; one object per input word, in input order.
- Definitions and examples in the **source** language.
- Gender required for `de` (m/f/n) and `it`/`es`/`fr` (m/f); always `null` for `en`.
- German translation nouns capitalized; Romance-language nouns lowercase unless proper nouns.
- Base dictionary form, no articles or determiners.
- 1–3 senses per word, most words 1; skip rare, archaic, and technical senses.
- Up to 2 translations per target language per sense, only genuine synonyms or difficulty variants.
- A translation's difficulty may never be lower than its sense's difficulty.
- A word that isn't a valid noun in that language comes back with `"senses": []`.

⚠️ **Known inconsistencies in the current prompt file** — it was adapted from the English version and some hardcoded values were not updated: rules 2 and 3 still say `language` must be `"en"` and there is a stray "valid English noun" in rule 31, while the header correctly says Spanish. Rule 15 lists target languages `de, it, es, fr` while the header says `en, it, de, fr`. Fix these when templating the prompt.

Validation is the safety net, not the prompt — every entry is checked before it reaches SQLite, and rejects go to a log for review rather than silently disappearing. Target reject rate is under 10%.

---

## Running it

```bash
docker compose up -d pipeline-database      # dedicated PostgreSQL on :5433
pnpm --filter @lila/pipeline pipeline:run   # tsx --env-file .env pipeline.ts (currently a no-op)
pnpm --filter @lila/pipeline test
```

The pipeline reads `.env` from the repo root: `GEMINI_API_KEY`, plus `PIPELINE_POSTGRES_USER` / `PIPELINE_POSTGRES_PASSWORD` / `PIPELINE_POSTGRES_DB` / `PIPELINE_DATABASE_URL`. The pipeline database is deliberately separate from the app database (`:5432`) so pipeline work can never damage dev data.

---

## Phase status

Full breakdown in [pipeline/roadmap.md](pipeline/roadmap.md).

| Phase                                             | State                                                          |
| ------------------------------------------------- | -------------------------------------------------------------- |
| 1 — Drizzle schema (words/senses/translations)    | ✅ Complete                                                    |
| 2 — Preparation (wordlists, DBs, prompt)          | ✅ Complete                                                    |
| 3 — Build the pipeline → SQLite                   | 🔄 **Current.** Validation + `pipeline.ts` + first run         |
| 4 — Migration & SQLite → PostgreSQL import        | ⬜ Not started                                                 |
| 5 — App integration (`getGameTerms`, distractors) | ⬜ Not started                                                 |
| 6 — Production deploy                             | ⬜ Not started                                                 |
| 7 — Extend to verbs, adjectives, adverbs          | ⬜ Not started — new wordlists + prompt only, no schema change |

Target for Phase 3: ~1000 nouns × 5 languages in staging, reject rate under 10%, 50 entries spot-checked by hand.

Phase 5 is where this becomes visible in the app: `packages/db/src/models/termModel.ts` still queries `vocabulary_entries`/`entry_translations` and must be rewritten against the sense-based schema. Until then, production runs on the old data.