topic-manager

name

topic-manager

description

Expert assistant for managing biblical topics in the KR92 Bible Voice project. Use when (1) creating/editing topics and their Finnish translations, (2) managing topic relations (related, opposite, broader, narrower), (3) validating Finnish translations and pronunciations with Voikko/Omorfi, (4) reviewing topics marked with qa_status='unchecked', (5) bulk updating topic translations, (6) managing topic aliases and synonyms, (7) fixing incorrectly translated Finnish topic names, or (8) structuring the topic taxonomy — building/auditing the parent_id tree (hierarchy, broader/narrower, roots, orphans, cycles, depth), assigning a coherent root backbone, re-parenting after merges, and linking related/opposite topics. Trigger words — aihe, topic, teema, taksonomia, rakenne, puurakenne, hierarkia, parent, root, liitos, yhdistäminen/merge, relation, alias.

Topic Manager

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

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.

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
                               --   `refinement` alias — BOTH are shown; see taxonomy.md#labels.
                               --   (also desc_fi/desc_en, summary_fi/en, core_rank, etc.)
-- No `system_id`: topics are shared/global, NOT app-scoped — do not add one.

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. 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', 'fi', 'synonym')  -- alias_norm: lowercase, no diacritics
) AS 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

1. Wrong translation - Check with Voikko/Omorfi
2. Missing diacritics - ä, ö must be correct
3. Compound words - Finnish compounds may be written together
4. Capitalization - Finnish doesn't capitalize most terms

Voikko/Omorfi Integration

For Finnish morphological validation, use these tools:

Option 1: UralicNLP (Python)

# pip install uralicnlp
from uralicnlp import uralicApi

# Check if word is valid Finnish
def is_valid_finnish(word):
    analyses = uralicApi.analyze(word, "fin")
    return len(analyses) > 0

# Get lemma (base form)
def get_lemma(word):
    analyses = uralicApi.analyze(word, "fin")
    if analyses:
        return analyses[0][0]  # First analysis, lemma
    return word

Option 2: libvoikko (Python)

# pip install libvoikko
import libvoikko

voikko = libvoikko.Voikko("fi")

def is_valid_finnish(word):
    return voikko.spell(word)

def analyze_word(word):
    return voikko.analyze(word)

def get_suggestions(word):
    if not voikko.spell(word):
        return voikko.suggest(word)
    return []

Option 3: API-based (no local install)

// Use Edge Function to call external API
async function validateFinnish(word: string) {
  const response = await fetch(
    `https://api.kielitoimistonsanakirja.fi/api/search?word=${encodeURIComponent(word)}`
  );
  return response.ok;
}

Validation Script

See scripts/validate_finnish.py for batch validation of topic translations.

Bulk Operations

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.

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