- Migration: partiell WHERE IS NOT NULL, dedup vorher, kein silent-catch
- Route: INSERT mit .catch(23505) → UPDATE statt ON CONFLICT (partial index inkompatibel)
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Tabelle user_achievements (Migration in db-migrate.js)
- src/lib/achievements.js: Definitionen + dedup-sichere Freischaltung
(ON CONFLICT DO NOTHING … RETURNING → nur Neues), Listing mit Status
- /auth/progress liefert unlocked_achievements (defensiv gekapselt)
- neue Route GET /auth/achievements
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
- Taxonomie um "Eigenschaften" (Adjektive) und "Verben & Handlungen"
ergänzt → Wortarten haben ein Zuhause statt Sonstiges.
- Klassifizierer geschärft: klare Wortart-/Themen-Regeln, "Sonstiges"
nur als letzter Ausweg; Sofort-Pfad nutzt jetzt Beispielsätze und
kleinere Batches (15) für deutlich genauere Treffer.
- ?reset=true: bestehende Zuordnungen verwerfen und neu klassifizieren.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Das ON-CONFLICT-Update griff bei bereits existierenden en/sv-Zeilen
nicht (Begrüßung blieb NULL). Stattdessen explizites, idempotentes
UPDATE für de/en/sv (Hallo/Hi/Hej).
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Feste ~20er-Taxonomie geseedet (de/en/sv, published; bestehende
Kategorien werden wiederverwendet) + Tabelle category_batches.
src/lib/classifyWords.js: findet in Pairs verwendete Wörter ohne
Kategorie und klassifiziert sie per Haiku gegen die feste Liste.
- Stundenjob über die Message Batches API (asynchron, ~50% günstiger):
submit/collect-Ticks, in index.js nach Boot + stündlich.
- Sofortiger synchroner One-Shot-Backfill (classifyWordsSync) für
Live-Test ohne 24h-Verzug.
Beides materialisiert pair_categories via derivePairCategories.
POST /api/categories/auto-assign (admin): ?sync=true = Sofort-Backfill,
sonst ein Batch-Tick. Entkoppelt von generate-words und Publish.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
languages.greeting (de/en/sv geseedet), neue pair_categories-Tabelle
(abgeleitet aus statement- und objektverknüpften Wörtern via
word_categories) inkl. Backfill für bereits veröffentlichte Pairs.
derivePairCategories() wird beim Publish (pairs + pipeline) aufgerufen.
/auth/me liefert language_target_greeting, /auth/stats liefert
categories[] mit Punkten je Kategorie fürs Profil.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
- Neue Tabelle user_daily_activity (Tagesverlauf) + Spalte daily_goal_ep
- POST /auth/progress schreibt Tagesaktivität mit
- GET /auth/me liefert daily_goal_ep
- Neuer GET /auth/stats: Tagesverlauf, Tagesziel, Totals, echte Skills je answer_type
- Neuer PUT /auth/goal zum Setzen des Tagesziels
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Die geseedete sv-Voice 'XXCqsM8I9KhqA7jLGj1U' existiert bei ElevenLabs
nicht — jede schwedische Audio-Generierung schlug mit voice_not_found
fehl (de/en haben eigene, gültige Account-Voices).
- Seed + Migration: sv → Premade 'Charlotte' (XB0fDUnXU5powFXDhCwa,
schwedischer Akzent, in jedem Account verfügbar); Bestandsdaten mit
der defekten ID werden beim Boot automatisch korrigiert
- voices.js: Fallback auf Premade 'Sarah' statt der toten ID
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
- pictures: reviewed-Status (Constraint + ALLOWED_STATUSES + Auto-Trigger beim Object-Linking)
- objects: STATUSES um reviewed erweitert; Auto-Trigger draft→reviewed wenn Pair verlinkt
- pairs/statements/questions: STATUSES um reviewed (Phase-1-Lücke)
- pairs: POST /:id/review kaskadiert Pair+Frage+Statements (verlangt alle 3 Sprachen)
- words: Auto requested→translated wenn alle titel_* gefüllt (POST+PATCH)
- audios computeUnits: nur vertonbar wenn ALLE 3 Sprachen pro Feld gefüllt
- claude: translate-text/translate-row/translate-missing mit Placeholder-Schutz
(⟦PHn:label⟧-Tokenisierung, Label übersetzt, UUID erhalten); translation-coverage
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
- tts_settings (voice/model/speed/... pro Sprache) + Seed de/en/sv; Route /api/tts-settings
- audios: Stimme/Parameter aus tts_settings; Coverage zählt jetzt auch draft/translated
- pairs: GET /publishability (Readiness, sortierbar nach 'am wenigsten fehlt'),
POST /:id/publish (kaskadiert question/statements→published, validiert Bild+Audio je Sprache)
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
- reviewed-Status für objects/questions/statements/pairs (Constraints)
- feed: nur fertige Inhalte (published + Bild + Audio-Gate), audio_url
- pairs: Publish-Gating (draft→published = 409)
- audios: source_table/source_id/source_field/language + Unique-Index;
generate-for, generate-batch, GET /coverage; voices.js (Voice je Sprache)
- auth: POST /auth/progress, /auth/me mit total_ep/streak/level;
users_public EP-Spalten + user_pair_progress.earned_points
- claude: POST /generate-words; words POST akzeptiert status
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Runs at startup (idempotent) — only touches rows that still contain bare
{{uuid}} placeholders. Looks up each UUID in words first, then objects,
and rewrites to {{label.w:uuid}} or {{label.o:uuid}} accordingly.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Add bbox_x/y/w/h FLOAT columns to object_pictures (0–1 percentage range)
- Include type ('word'|'object') and bbox in feed placeholder response
- Fix picture query to use DISTINCT ON instead of LIMIT 1
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- GET /auth/feed?lang=sv&limit=20 (JWT, end-user allowed)
- Resolves {{uuid}} placeholders to word labels in all languages
- Includes picture URLs, pos/neg words per statement
- Fix migration seed: use full unique index (non-partial) for ON CONFLICT
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- users_public gets user_id FK (1:1 link to auth user)
- Seed languages: en, sv alongside existing de
- POST /auth/register + /auth/login now include needsProfile flag
- New JWT-authed endpoints (end-user allowed):
GET /auth/languages public language list
GET /auth/check-username
GET /auth/me full profile join
POST /auth/profile one-time profile creation
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Convert TEXT[] back to TEXT (take first element of existing arrays)
- Valid values: yes_no, text, question, word
- API validation updated for single string
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- pictures: add objects_created (bool) + objects_created_at (auto timestamp)
GET /pictures supports ?objects_created=true/false filter
PATCH /pictures/:id allows setting objects_created
- db-migrate: seed German language, link to all existing users
- auth/login: include native_lang (from languages table) in response + JWT
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Fix broken rename migration array (sed had corrupted from values to _sv)
- Add languages table with status lifecycle and trilingual titles
- Add user_names table with unique lowercase index
- Add users_public table linking to user_names and languages (native/target)
- Wire all three new routes under /api/languages, /api/user-names, /api/users-public
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- is_blocked BOOLEAN (default true), INET type for IP validation
- Indexes on email/username/phone/ip for fast registration checks
- POST /api/blocklist/check — checks all fields in one request, returns 403 if blocked
- Auto-timestamps on block/unblock, email stored lowercase
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- objects: status enum, JSONB selections, notes, blocked_topic, auto-timestamps
- pairs placeholder table for future use
- Junction tables: object_words, object_pictures, object_pairs
- Full CRUD + link/unlink endpoints for all three relations
- README updated
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Fills out placeholder categories table with all fields
- Trilingual titles, status enum, difficulty level, auto-timestamps
- ALTER TABLE IF NOT EXISTS for safe migration on existing table
- /api/categories CRUD route, word_ids included in responses
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- words table with trilingual titles, status enum, difficulty level, timestamps
- word_pictures junction table (M2M words <-> pictures)
- categories placeholder table
- word_categories junction table (M2M words <-> categories)
- Auto-timestamps on status change (requested/published/blocked)
- Full CRUD + link/unlink endpoints for pictures and categories
- README updated with schema and endpoints
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
