topic-manager

name

topic-manager

description

Manage biblical topics in Raamattu Nyt. Use for creating/editing topics + Finnish translations, topic relations (related/opposite/broader/narrower), validating FI translations & pronunciations (Voikko/Omorfi), reviewing qa_status='unchecked', bulk translation updates, aliases/synonyms, fixing mistranslated FI topic names, and the taxonomy tree (parent_id hierarchy, roots, orphans, cycles, re-parenting after merges). ALSO the structured topic schema (`structured_schema`): type templates, claims + sources, the async generation queue (/ohjaamo/topics/generation), draft review, and the Matthew Henry claim-verification pass — plus the PLANNED verse schema generated from Matthew Henry (design lives in Obsidian, not yet in code). ALSO the source corpora behind topic content — which reference work feeds which column, where the local files live (`Docs/data/`), and how to expand ISBE coverage without re-scraping. Triggers: aihe, topic, teema, taksonomia, hierarkia, alias, merge/yhdistäminen, aiheskeema, jae-skeema, structured_schema, skeeman generointi, luonnoksen katselmointi, claim, väitteen vahvistus, Henry-vahvistus, generointijono, lähdedata, lähdeaineisto, korpus, ISBE, Easton, Smith, Nave, Torrey, unfoldingWord.

Topic Manager

Manage biblical topics: names, translations, relations, aliases, and QA validation.

Route the work before doing it

Topic work is massadataa — thousands of rows. The expensive mistake is pulling those rows into this session to do something a query or a script could have done. MCP results stay in context for the rest of the session, so a single careless SELECT makes every later step more expensive too.

Three cost classes. Pick one before starting:

Class	Examples	Do this
1. Deterministic	export/import CSV, enqueue-topics.ts, morphological spell-check, re-runnable audits	Run the script. Don't hand-roll the same logic in-session.
2. Mechanical bulk	coverage scans, duplicate/orphan detection, alias gaps, criteria-based qa_status updates, counting	Delegate to the topic-bulk agent (Agent(subagent_type: "topic-bulk", …)). It runs on a cheaper model and its rows never enter this context — you get a summary.
3. Semantic judgement	is this translation right, is this parent conceptually correct, does this source article match, claim verification, draft review	Do it here. This is the whole value of the skill; do not delegate or downgrade it.

Class 2 is the one that is habitually done wrong. "Which topics lack a Finnish name?" over 9 000 rows belongs in the agent, not here — and the context saving matters more than the model saving.

Between large batches of class-3 review, /clear. Judgement work needs a clean head, not the residue of the previous 2 000 rows.

Quick Reference

Read these context files first:

• Docs/context/db-schema-short.md - Table structures
• apps/raamattu-nyt/src/lib/topicEditorUtils.ts - TypeScript API utilities
• references/learnings.md - Gotchas (the level CHECK footgun, OSIS verification, topic search RPC behavior). Read before creating topics or debugging search.
• references/taxonomy.md - The category-root forest + cross-links. Read before any structuring/re-parenting/merge work. KEY INVARIANT: the topic tree is a forest of is_category_root roots (curated count, 17 as of 2026-06-19); every other topic descends from one → 0 orphans. A new topic MUST get a parent_id (a childless top-level insert is an orphan). Has the root list, the is_category_root/get_category_roots/get_topic_children mechanism behind the "Kategorioittain" views, and the invariant audits.
• references/structured-schema.md — aihe-skeema (structured_schema). Read before ANY work on schema authoring, the generation queue (/ohjaamo/topics/generation), draft review, or claim verification. Tietomalli, tyyppitemplaatit, jono + worker, Henry-vahvistuspassi, johdettu putken tila (käsittelemätön luonnos voittaa tallennetun) ja generointipromptin tunnetut viat.
• references/source-data.md — mistä raaka-aine tulee. Lue ennen kuin haet, scrapeat tai tuot lähdetekstiä: korpukset (Easton, Smith, ISBE, Nave, Henry) ovat jo levyllä Docs/data/:ssa, ja ISBE:n kattavuuden nosto on matchaus JSONL:stä — ei scrape, toisin kuin vanhat nootit väittävät.
• references/verse-schema-mh.md — jae-skeema Matthew Henrystä: SUUNNITELMA, ei vielä koodissa. Lyhyt pointteri; kanoninen kuvaus on .claude/skills/verse-topic-tagger/references/verse-schema.md (jae-skeema on jae-ensimmäistä työtä). Lue tämä ennen kuin muutat claim_source_checks-taulua, list_claim_issues-RPC:tä tai /ohjaamo/topics/claims-sivua — jae-skeema nojaa samaan koneistoon.

Database Schema

topical_topics (bible_schema)

id UUID PRIMARY KEY
name_en TEXT NOT NULL          -- English topic name
name_fi TEXT                   -- Finnish translation
slug TEXT (nullable, unique)   -- URL slug (English)
slug_fi TEXT                   -- Finnish URL slug
parent_id UUID                 -- Broader topic (hierarchy)
qa_status qa_status_t          -- 'unchecked' | 'ok' | 'needs_review' | 'rejected' (default 'unchecked')
is_core BOOLEAN                -- Important/featured topic
is_category_root BOOLEAN       -- ⚠ One of the top categories (the ONLY roots; curated
                               --   count, 17 as of 2026-06-19). Drives the "Kategorioittain"
                               --   views. Roots == category_roots; anything else at root = orphan.
is_biblical BOOLEAN            -- Biblical vs modern term (default true)
level TEXT NOT NULL            -- ⚠ CHECK: 'core' | 'standard' | 'community' ONLY.
                               --   Column DEFAULT 'subtopic' VIOLATES this check → ALWAYS
                               --   set level explicitly on INSERT ('standard' for a normal
                               --   new topic). Relying on the default = insert fails.
category TEXT                  -- NULL or: theology|person|place|event|object|practice|concept|other
confidence_score NUMERIC       -- NULL or 0..1
semantic_field TEXT            -- Semantic category
semantic_field_fi TEXT         -- Finnish semantic field
semantic_field_en TEXT         -- English semantic field
usage_context TEXT             -- How term is used
nuance_fi / nuance_en TEXT     -- Short lay "gloss" shown under the name (e.g. "Keneen voin
                               --   turvautua"). One per lang; NOT searchable. Sibling to the
                               
                               

topical_relations (bible_schema)

source_topic_id UUID           -- Source topic
target_topic_id UUID           -- Target topic
relation_type TEXT             -- 'related', 'opposite', 'synonym'
is_bidirectional BOOLEAN       -- TRUE for opposite/synonym

topical_aliases (bible_schema)

topic_id UUID
alias TEXT                     -- Display text
alias_norm TEXT                -- Normalized (lowercase, no diacritics)
lang TEXT                      -- 'fi' or 'en'
alias_type TEXT                -- 'synonym', 'abbrev', 'variant', 'old_term', 'misspelling',
                               --   'refinement' (teol. discipline label, e.g. Jeesus →
                               --   "Kristologia"; searchable + shown inline; see taxonomy.md#labels)
qa_status qa_status_enum       -- 'unchecked', 'ok', 'needs_review'
source TEXT                    -- 'manual', 'ai', 'import'

topical_references (bible_schema)

topic_id UUID
osis_start TEXT                -- Start verse (e.g., 'Gen.1.1')
osis_end TEXT                  -- End verse (for ranges)
relevance_score INTEGER        -- 1-5 relevance

Common Operations

Create New Topic (+ attach verses)

1. Check for duplicates first: searchSimilarTopics or SELECT ... WHERE name_en ~* '<term>' OR name_fi ~* '<term>'.
2. Verify OSIS codes exist before inserting references (resolution happens at query time via book_aliases; bad OSIS = silently never displayed): SELECT osis FROM bible_schema.verse_keys WHERE osis IN ('John.20.19', ...); Book-code convention matches verse_keys (e.g. John, 2Thess, 1Cor, Acts, Titus).
3. Name it NOMINALLY — name_fi is a display label, not a search string. A noun or noun phrase (pelottomuus), never an imperative, infinitive or adjective (älä pelkää, rentoutua, rohkea). Put every phrasing a user might type into topical_aliases (alias_type='variant') instead — that is what makes it findable. When renaming an existing topic: alias first, then rename, and treat a name collision as a duplicate finding rather than a reason to pick a second-choice name. Full policy + traps: taxonomy.md#nameform.
4. Insert topic + references + aliases in one data-modifying CTE. ⚠ Set level='standard' explicitly (default 'subtopic' fails the check constraint), and ⚠ set parent_id to an existing more-general topic — a NULL parent creates an orphan and breaks the category-root forest invariant (see taxonomy.md):

WITH new_topic AS (
  INSERT INTO bible_schema.topical_topics
    (name_en, name_fi, slug, slug_fi, is_core, is_biblical, qa_status, level, parent_id)
  VALUES ('appearances of Jesus', 'Jeesuksen ilmestyminen', 'appearances-of-jesus',
          'jeesuksen-ilmestyminen', false, true, 'ok'::bible_schema.qa_status_t, 'standard',
          (SELECT id FROM bible_schema.topical_topics WHERE slug = 'jesus'))  -- nearest general parent
  RETURNING id
),
refs AS (
  INSERT INTO bible_schema.topical_references (topic_id, osis_start, osis_end, relevance_score)
  SELECT nt.id, v.osis_start, v.osis_end, v.rel
  FROM new_topic nt CROSS JOIN (VALUES
    ('John.20.19', NULL::text, 5), ('1Cor.15.5', '1Cor.15.8', 5)  -- range via osis_end
  ) AS v(osis_start, osis_end, rel)
  RETURNING 1
)
INSERT INTO bible_schema.topical_aliases (topic_id, alias, alias_norm, lang, alias_type, source, qa_status)
SELECT nt.id, a.alias, a.alias_norm, a.lang, a.alias_type, 'manual', 'ok'::bible_schema.qa_status_t
FROM new_topic nt CROSS JOIN (VALUES
  ('Jeesuksen ilmestykset', 'jeesuksen ilmestykset', , )  
)  a(alias, alias_norm, lang, alias_type);

• relevance_score is 1–5 (default 1). osis_end only for ranges.
• This is content data, not schema → use execute_sql (no migration). Effect is immediate.
• Topic-page (/aihe/<slug>) shows ALL references; the verses-tab "Aiheeseen liittyvät" augmentation is gated — see references/learnings.md.

Find Topics Needing Review

-- Topics with unchecked qa_status
SELECT id, name_en, name_fi, qa_status
FROM bible_schema.topical_topics
WHERE qa_status = 'unchecked'
ORDER BY is_core DESC, name_en
LIMIT 50;

-- Topics without Finnish translation
SELECT id, name_en, slug
FROM bible_schema.topical_topics
WHERE name_fi IS NULL
ORDER BY is_core DESC, name_en;

Update Topic Translation

UPDATE bible_schema.topical_topics
SET
  name_fi = 'suomenkielinen nimi',
  slug_fi = 'suomenkielinen-slug',
  qa_status = 'ok'
WHERE id = 'uuid-here';

Create Topic Relation

-- Related topic (one-way)
INSERT INTO bible_schema.topical_relations
  (source_topic_id, target_topic_id, relation_type, is_bidirectional)
VALUES ('source-uuid', 'target-uuid', 'related', false);

-- Opposite (bidirectional)
INSERT INTO bible_schema.topical_relations
  (source_topic_id, target_topic_id, relation_type, is_bidirectional)
VALUES ('source-uuid', 'target-uuid', 'opposite', true);

Set Topic Hierarchy

-- Set parent (broader term)
UPDATE bible_schema.topical_topics
SET parent_id = (SELECT id FROM bible_schema.topical_topics WHERE slug = 'parent-slug')
WHERE slug = 'child-slug';

Add Topic Alias

INSERT INTO bible_schema.topical_aliases
  (topic_id, alias, alias_norm, lang, alias_type, source, qa_status)
VALUES (
  'topic-uuid',
  'Vaihtoehtoinen nimi',
  'vaihtoehtoinen nimi',
  'fi',
  'synonym',
  'manual',
  'ok'
);

Topic Taxonomy (tree base + cross-links)

Full methodology, root backbone, and audit queries: references/taxonomy.md. Read it before any structuring / re-parenting / post-merge work.

The user-facing aihepuu (Haku → Aiheet preview + topic page, component TopicRelationsTree via the get_topic_preview RPC) is built from exactly three structures — category is NOT used and is rendered nowhere (dormant):

1. parent_id (hierarchy) — the whole tree: parent (id=parent_id) + children (parent_id=this, ≤5) + siblings (shared parent_id, ≤10, derived for free). Strict is-a / part-of; one parent; ≤4 deep; no cycles.
2. topical_relations (related → "nearby", opposite → antonyms). Synonyms are NOT relations → use topical_aliases / mergeTopicAsSynonym.
3. topical_aliases — synonyms (inline list).

Decide the field: "X is a kind of Y" → parent_id; "X relates to Y" → related; "X is another name for Y" → alias/merge.

The forest invariant (load-bearing). The tree is a clean forest: is_category_root roots (curated count, 17 as of 2026-06-19), and EVERY other topic descends from one → 0 orphans (measured 2026-06-19: 17 roots, 17 category_roots, 0 non-category roots). Maintain it:

• A new topic ALWAYS gets a parent_id (the Create-Topic CTE sets it). A NULL-parent insert = orphan = bug.
• Roots count must equal is_category_root count (the count itself is curated by the owner — don't "fix" a deliberately added root). Re-run the invariant audit (taxonomy.md #1/#2) after every re-parent/merge batch.
• is_category_root (boolean) is the LIVE category mechanism → get_category_roots()
  • get_topic_children(parent, p_core_only) drive the "Kategorioittain" views (topic page + cinema-os). The category TEXT enum is a separate, dormant field — don't confuse them.

Next step (in progress): some roots are too flat (ihminen ~604 direct kids, usko ~494) → add a top sub-topic tier (category → ~5–15 top sub-topics → leaves), keeping the invariant. See taxonomy.md "Next step". Content data → execute_sql (no migration), reviewed batches.

Finnish Translation Validation

QA Status Values

• unchecked - Not reviewed yet (default for imports)
• ok - Verified correct
• needs_review - Flagged for human review
• rejected - Reviewed and rejected (4 values total in bible_schema.qa_status_t)

Common Finnish Issues

Two different problems — one is machine-checkable, the other is not:

Problem	Detectable by Voikko/Omorfi?
Missing diacritics (ä, ö)	✅ yes
Broken inflection ("paimens", "kutsueassaan")	✅ yes
Compound written apart / together	✅ mostly
Capitalization (Finnish doesn't capitalize most terms)	✅ rule-based
Wrong meaning (codependency → "koodiriippuvuus")	❌ NO

⚠️ Morphology ≠ meaning. "koodiriippuvuus" is perfectly well-formed Finnish — Voikko accepts it happily. It is also completely wrong (codependency = läheisriippuvuus). A literal machine translation of an English compound almost always passes a spell-checker, which is exactly why this class of error survived a qa_status='ok' pass. Meaning is class 3: read the English name_en, ask what the concept actually is, and judge. No tool does this for you.

When fixing a mistranslation, correct name_fi and slug_fi; leave the English slug alone (it's the lookup key).

Voikko/Omorfi Integration — use the script, don't rewrite it

scripts/validate_finnish.py (in this skill) already implements batch validation with a libvoikko → UralicNLP fallback. Run it. Do not re-derive the same three snippets in-session — that was the old content of this section and it caused the work to be redone by hand every time.

Installed and working as of 2026-08-05 (libvoikko 4.3.3 + venv). Run it via the venv interpreter — a bare python3 will NOT find the binding:

cd .claude/skills/topic-manager/scripts
./.venv/bin/python validate_finnish.py --word "armo"
./.venv/bin/python validate_finnish.py --csv exported_topics.csv
./.venv/bin/python validate_finnish.py --csv exported_topics.csv --json
./.venv/bin/python validate_finnish.py --csv schema_fields.csv --prose   # virkkeille

--prose on pakollinen structured_schema-teksteille. Oletustila on tehty 1–3 sanan nimille eikä riisu välimerkkejä → virkkeillä 14 osumaa joista 0 todellista virhettä. --prose riisuu reunavälimerkit, ohittaa jaeviitteet ja luokittaa tuntemattoman isolla alkavan sanan erisnimeksi; paimens/tyolla/ xyzqwerty löytyvät silti.

⚠️ There is NO database mode. The script never connects to Supabase. An earlier docstring advertised --all and a bare "check unchecked topics" run; neither was ever implemented (--all was accepted and silently did nothing). Both are now removed — --all errors out. The real workflow is two-step:

1. Export with SQL (class 2 → topic-bulk agent can do this):

   SELECT slug, name_en, name_fi FROM bible_schema.topical_topics
   WHERE name_fi IS NOT NULL AND qa_status = 'unchecked';
   

2. Feed the CSV to --csv. Header must include slug, name_en, name_fi.

Verified output on a 6-row fixture: paimens → INVALID (suggests paimen), kutsueassaan → INVALID (suggests kutsuessaan), xyzqwerty → not recognized. And koodiriippuvuus → VALID — the boundary above, demonstrated: this script checks form, never meaning.

Setup notes if it ever breaks: PEP 668 blocks pip install on system Python, so the binding lives in scripts/.venv/ (gitignored, recreate with python3 -m venv .venv && ./.venv/bin/pip install libvoikko). The system library comes from brew install libvoikko, which bundles the Finnish dictionary.

Bulk Operations

Class 2 — delegate these. Everything in this section runs over the whole topic table. Hand the query to Agent(subagent_type: "topic-bulk", …) with the criteria; it returns counts + a bounded sample instead of thousands of rows. Run them inline only when you already know the result set is tiny (single topic, single slug).

Export for Translation

-- Export untranslated topics to CSV format
SELECT
  slug,
  name_en,
  COALESCE(name_fi, '') as name_fi,
  COALESCE(slug_fi, '') as slug_fi,
  is_core
FROM bible_schema.topical_topics
WHERE name_fi IS NULL OR qa_status = 'unchecked'
ORDER BY is_core DESC, name_en;

Import Translations

-- Create temp table
CREATE TEMP TABLE topic_import (
  slug TEXT,
  name_fi TEXT,
  slug_fi TEXT
);

-- Import (via \copy or Supabase)
-- Update topics
UPDATE bible_schema.topical_topics t
SET
  name_fi = i.name_fi,
  slug_fi = i.slug_fi,
  qa_status = 'ok'
FROM topic_import i
WHERE t.slug = i.slug;

Mark Topics as Reviewed

-- Mark single topic
UPDATE bible_schema.topical_topics
SET qa_status = 'ok'
WHERE id = 'uuid';

-- Batch mark by criteria
UPDATE bible_schema.topical_topics
SET qa_status = 'ok'
WHERE qa_status = 'unchecked'
AND name_fi IS NOT NULL
AND LENGTH(name_fi) > 2;

Relation Types

Type	Direction	Use Case
related	One-way	Topic A relates to B (the bulk: ~1316 rows)
opposite	Bidirectional (is_bidirectional=true)	Antonyms (good ↔ evil)
synonym	—	Do NOT use a relation row. Same concept → topical_aliases (alias_type='synonym') or mergeTopicAsSynonym. 0 synonym relations exist by design.
broader	Uses parent_id	Hierarchy (parent topic) — see taxonomy.md
narrower	Reverse of parent_id	Hierarchy (child topics) — see taxonomy.md

QA Workflow

1. Query unchecked topics - Start with high-value (is_core=true)
2. Validate Finnish - Use Voikko/UralicNLP to check spelling
3. Review translation - Ensure theological accuracy
4. Check relations - Verify related/opposite links make sense
5. Mark as reviewed - Set qa_status='ok'

Aihe-cinema (topic cinema mode)

A topic now has a fullscreen snap-scroll walkthrough, opened from the /aihe/<slug> page header "Cinema" button (mirror of the Q&A cinema). The cinema UI/flow itself is owned by cinema-voice-architect — but topic data quality directly drives what the cinema shows, so it matters here. The card order and its data source:

Card	Source field/table	Curation lever
Selitys + AI-yhteenveto	selectTopicDescription() hierarchy: unfolding_desc → summary_fi → desc_fi → semantic_field_fi → semantic_field, then summary_fi/summary_en	Fill summary_fi/unfolding_desc → richer first card
Info-palaset	topic_info_blocks (published, !is_hidden)	Attach + publish info blocks
Strong's	fetchStrongsMatches(name_en) — strongs_lexicon ilike on name, ≤5, NOT a junction	Accurate name_en → better lexicon hits
Jakeet	top 10 topical_references by relevance_score (5/card × max 2)	High relevance_score on the best verses surfaces them first
Omat korostukset	user highlights tag-matched to topic name	(user data)
Yhteenveto	anchor questions (get_anchor_questions_for_topics) + questions sharing the topic's info blocks	Good name_fi/name_en keywords + attached info blocks

Practical implication: to make a topic's cinema strong, ensure summary_fi/unfolding_desc is present, the most central verses carry the highest relevance_score (only the top 10 show), and relevant info blocks are attached and published. Code pointers (for context, not editing here): apps/raamattu-nyt/src/hooks/cinema/useTopicCinemaSteps.ts, apps/raamattu-nyt/src/components/cinema/topic/, manifest cinema.cinema_variants.topic.

Aihe-skeema ja jae-skeema (sisältöskeemat)

Kaksi eri asiaa; älä sekoita niitä toisiinsa eikä DB-migraatioihin ("schema"):

	Aihe-skeema	Jae-skeema
Tila	Tuotannossa	Suunnitelma — ei koodia
Sijainti	topical_topics.structured_schema (jsonb)	ei vielä taulua
Yksikkö	aihe	Matthew Henryn kommentaarijakso (EI jae)
Lähde	Easton/Smith/ISBE/unfolding + aiheen jakeet	Henryn jaksot + outline-kohdat
Omistaja	topic-manager (tämä skill)	verse-topic-tagger
Ohje	references/structured-schema.md	.claude/skills/verse-topic-tagger/references/verse-schema.md (pointteri: references/verse-schema-mh.md)

Generointiputki lyhyesti (yksityiskohdat referenssissä):

kandidaatit → enqueue (prompti rakennetaan clientissä) → worker (cron 1 min)
   → assembleJobDraft + Zod → KATSELMOINTI → tallennus → Henry-vahvistuspassi

Kolme sääntöä, jotka rikotaan helposti:

1. Mitään ei tallenneta automaattisesti. Luonnos menee aina ihmisen katselmoitavaksi (onDraft), ja verdictit sovelletaan erillisellä askeleella.
2. Putken tila on johdettu, ei tallennettu — ja käsittelemätön luonnos voittaa tallennetun skeeman. Älä lisää viidettä statussaraketta.
3. Section/field-avaimet ovat claim-tarkistusten identiteetti. Uudelleennimeäminen ilman migraatiota katkaisee claim_source_checks-viittaukset.

Integration with Other Skills

Task	Delegate To
Schema changes	supabase-migration-writer
Admin UI changes	admin-panel-builder
Aihe-cinema UI/flow behavior	cinema-voice-architect
Bulk CSV operations	translation-sync
AI-assisted translations	ai-prompt-manager
Finding topic code	code-wizard

TypeScript API

Key functions in apps/raamattu-nyt/src/lib/topicEditorUtils.ts:

• fetchTopicById(id) - Get topic details
• updateTopic(id, updates, token) - Update topic
• createAlias(topicId, alias, lang, type, token) - Add alias
• createRelation(source, target, type, token) - Create relation
• setTopicParent(topicId, parentId, token) - Set hierarchy
• searchSimilarTopics(query, excludeId) - Find duplicates
• mergeTopicAsSynonym(primary, duplicate, token) - Merge topics

  ⚠ Before ANY merge, run the pre-merge content diff. The merge checklist covers FK tables; the dup row's own columns (above all structured_schema, plus curation_level/qa_status/summary_*/desc_*/ nuance*/*_desc) are dropped silently with no error and no trace. Hard stop if the dup holds a structured_schema the survivor lacks. See taxonomy.md → Merge a duplicate and learnings.md → "…half the content lives in COLUMNS". Note mergeTopicAsSynonym is lossy by design — it does not carry columns over.