fix(dictionary): repair add_dictionary_vocabulary against the real schema #70

Merged

raisondetredev merged 1 commit from refs/pull/70/head into main

2026-07-02 01:18:41 +02:00

raisondetredev commented

2026-07-01 22:53:12 +02:00

(Migrated from codeberg.org)

Problem

The deployed add_dictionary_vocabulary (from 202606291840) is written against a schema that does not exist in this database: it references e.lemma, cards.source_id / normalized_expression / normalized_reading, the tables card_tags and card_review_states, and tags.color. Postgres validates plpgsql bodies only at call time, so the function was created cleanly but throws whenever it is actually called. count_dictionary_entries only reads real columns, so it kept working and masked the breakage — meaning the chips show counts but Greek and Japanese bulk-add fail at the point of insert.

Fix

Recreate the function from the last schema-correct body (20260607130000): payload-based vocabulary cards, FSRS inline on public.cards, content tags (pos ∪ common ∪ jlpt-n*) found/created in public.tags and joined via public.flashcard_tags. The only thing carried over from the divergent version is the part it got right — the hnc_lemma_words_<N> (500…5000) filter params and predicates, which reference the real, populated columns.

Helpers card_content_from_text / dictionary_tag_color re-declared idempotently → migration is self-contained.
Both the 4-arg and 14-arg overloads are dropped first, so a JLPT add (which omits the HNC args) can't resolve ambiguously between overloads.
No frequency ordering or band tag yet — that's a follow-up.

Note

This migration depends on the hnc_lemma_words_<N> columns (added by 202606291355_add_hnc_lemma_words_bulks.sql), which is currently untracked in the repo (applied to the live DB out-of-band). Against the live DB this migration applies cleanly; for a consistent migration tree, 202606291355 (and the wiktionary column migration) should be committed before this one. Happy to fold that in if wanted.

Apply via the Supabase SQL editor (or supabase db push); it is idempotent for the function definition.

## Problem The deployed `add_dictionary_vocabulary` (from `202606291840`) is written against a schema that does not exist in this database: it references `e.lemma`, `cards.source_id` / `normalized_expression` / `normalized_reading`, the tables `card_tags` and `card_review_states`, and `tags.color`. Postgres validates plpgsql bodies only at call time, so the function was created cleanly but **throws whenever it is actually called**. `count_dictionary_entries` only reads real columns, so it kept working and masked the breakage — meaning the chips show counts but **Greek and Japanese bulk-add fail at the point of insert**. ## Fix Recreate the function from the last schema-correct body (`20260607130000`): payload-based vocabulary cards, FSRS inline on `public.cards`, content tags (`pos ∪ common ∪ jlpt-n*`) found/created in `public.tags` and joined via `public.flashcard_tags`. The only thing carried over from the divergent version is the part it got right — the `hnc_lemma_words_<N>` (500…5000) filter params and predicates, which reference the real, populated columns. - Helpers `card_content_from_text` / `dictionary_tag_color` re-declared idempotently → migration is self-contained. - Both the 4-arg and 14-arg overloads are dropped first, so a JLPT add (which omits the HNC args) can't resolve ambiguously between overloads. - No frequency ordering or band tag yet — that's a follow-up. ## Note This migration depends on the `hnc_lemma_words_<N>` columns (added by `202606291355_add_hnc_lemma_words_bulks.sql`), which is currently **untracked** in the repo (applied to the live DB out-of-band). Against the live DB this migration applies cleanly; for a consistent migration tree, `202606291355` (and the wiktionary column migration) should be committed before this one. Happy to fold that in if wanted. Apply via the Supabase SQL editor (or `supabase db push`); it is idempotent for the function definition.