Write slim IPA and pronunciation URL back on vocab miss - #21

Summary

On an exact-word miss of phonetic_question, a successful dictionary result that has both a non-blank IPA and a pronunciation URL is inserted as a slim row. The next lookup is a local hit and does not call web-parser.

Plan: https://app.notion.com/p/40acda9bbfa68178b872ccf065025df1

Timmy locks (2026-11-03)

1. Write-back only. picsFullPaths / PIC_FILE_NAME hide stays setPicsFullPaths({""}). Picture cleanup is a follow-up.
2. True DB miss only. Sparse bank rows (hit && !enriched) are returned as-is and are never filled.
3. Unique WORD, same client key. No new normalization. The client already sends the vocab string unchanged (VocabPracticeService.getQuestion → encodeURIComponent(word)), and PhoneticQuestionDAO looks that string up with WORD = :word.

Behavior

• Hook: PhoneticQuestionService.buildQuestionByWebAPI, which runs only after getQuestionFromDB misses.
• Persist only when enriched() is true (both IPA and PRONOUNCED_LINK). Exact-word guard in fillQuestionByDictionaryResult is unchanged.
• RANK is 0, below every VocabDifficulty range, so generatePractice / getRandomQuestionWithinRank never returns a write-back.
• Insert-if-absent re-checks the word, then relies on unique WORD for a concurrent double-miss. A duplicate-key failure is logged and the pronunciation is still returned.
• Meanings, InterpretationService, and Replicate are untouched. Stale audio URLs are stored as-is.

Unique WORD

Applied directly on the UAT database at 8:23pm HKT on 3 Nov 2026, after a re-check found 0 duplicate WORD groups (4,061 rows, no null or blank WORD):

ALTER TABLE phonetic_question ADD CONSTRAINT UK_PHONETIC_QUESTION_WORD UNIQUE (WORD);

No rows were deleted. The existing non-unique index PHONETIC_QUESTION_WORD is unchanged. The entity still declares the same constraint so H2 tests reject a second insert. The app does not run schema changes on startup.

Prod was not updated. SSH to the prod host is not available from this machine. Collation on UAT is utf8mb3_unicode_ci, so the unique key matches the existing case-insensitive lookup.

Tests

./gradlew test --tests com.esl.service.PhoneticQuestionWriteBackTest --tests com.esl.service.PhoneticQuestionServiceTest is green:

• miss → row with IPA + PRONOUNCED_LINK only, RANK = 0; second request is a DB hit and does not call queryDictionary
• empty / IPA-only / URL-only / base-form mismatch → no row
• generatePractice for every VocabDifficulty never returns the rank-0 word
• sparse existing row returned unchanged
• concurrent double-miss and a second insert cannot create two WORDs
• picsFullPaths stays {""} for both a write-back and an existing bank row (apple)

Not done: no esl-ionic changes, no web-parser changes, no app deploy. Prod database was not changed.