Spoke Plus Database Structure

Last generated: 2026-03-05T11:58:02.303Z

Lemma Editor persistence contract (stabilized)

A edição de lemma deve preservar os seguintes armazenamentos:

• lemmas: core (lemma, editorial_status, numeric_value, spelled_form, image_url, audio_url).
• content_item_taxonomies: seleção múltipla para part_of_speech, grammar_topics, semantic_domains, register, frequency_band, difficulty (via PUT /admin/content-bank/lemmas/:id/taxonomies).
• lemma_pronunciations: IPA (ipa) por lemma.
• sense_translations / lemma_translations: traduções multilíngues sem substituir o lemma base em inglês.
• lemma_forms: morfologia (plural forms, verb forms, comparatives, superlatives).
• lexical_relations: relações semânticas (collocations, synonyms, antonyms, links).
• lemma_assets: assets editoriais (asset_type = audio/image, asset_url).

Estrutura esperada de lemma_assets:

• id
• lemma_id
• asset_type
• asset_url
• created_at

UNIVERSAL TAXONOMY SYSTEM

O schema do Spoke Plus adota o Universal Taxonomy Engine como fonte única de classificação.

Tabelas centrais:

• taxonomy_categories: catálogo das categorias de classificação.
• taxonomy_values: valores permitidos por categoria.

Regra de arquitetura:

• nunca persistir classificação de domínio como texto livre quando houver taxonomia equivalente;
• sempre preferir colunas *_id referenciando taxonomy_values.id;
• manter compatibilidade temporária com colunas legadas textuais apenas durante migração.

Uso esperado por domínio:

• vocabulário: pos_id, lemma_type_id, cefr_level_id;
• sentidos/traduções/tokens/sentenças: IDs taxonômicos de POS, CEFR, registros e qualidade;
• conteúdo pedagógico (exercises/lessons/themes/assets): IDs taxonômicos de tipo, dificuldade e tema;
• analytics/AI/admin: classificação por IDs taxonômicos para garantir auditabilidade e consistência.

Governança:

• validação obrigatória em backend via validateTaxonomy(category_slug, value_id);
• auditoria periódica via endpoint administrativo /admin/taxonomy/audit;
• hardcoded strings classificatórias (ex.: "verb", "A1", "easy") são proibidas em novos fluxos.

Detected from sql/ and migrations/.

admin_users

• source: sql/0001_user_provision.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• user_id: uuid primary key
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

ai_action_logs

• source: sql/0014_ai_action_logs.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• created_at: timestamptz not null default now()
• admin_user_id: uuid not null
• action_type: text not null
• execution_mode: text not null default 'suggest'
• tool_name: text
• request_payload: jsonb not null default '{}'::jsonb
• response_payload: jsonb not null default '{}'::jsonb
• status: text not null default 'completed'
• undo_supported: boolean not null default false
• undone_at: timestamptz
• relations:
• (none parsed)

ai_conversation_summary

• source: sql/0019_ai_conversation_summary.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• admin_user_id: uuid primary key
• summary: text not null default ''
• source_message_count: integer not null default 0
• updated_at: timestamptz not null default now()
• relations:
• (none parsed)

ai_conversations

• source: sql/0022_ai_conversations_hardening.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key
• admin_user_id: uuid not null
• title: text
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• (none parsed)

ai_prompt_versions

• source: sql/0018_global_linguistic_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• prompt_key: text not null
• version: text not null
• provider: text null
• body: text not null
• metadata: jsonb not null default '{}'::jsonb
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

app_pages

• source: sql/0027_course_content_and_app_cms.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• project_id: uuid not null references public.app_projects(id) on delete cascade
• page_key: text not null
• layout: jsonb not null default '{}'::jsonb
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• app_pages.project_id -> app_projects

app_projects

• source: sql/0027_course_content_and_app_cms.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• name: text not null
• domain: text not null unique
• is_active: boolean not null default true
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• (none parsed)

app_themes

• source: sql/0027_course_content_and_app_cms.sql
• purpose: Stores project theme tokens for UI theming and configuration.
• fields:
• id: uuid primary key default gen_random_uuid()
• project_id: uuid not null references public.app_projects(id) on delete cascade
• tokens: jsonb not null default '{}'::jsonb
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• app_themes.project_id -> app_projects

content_generation_jobs

• source: sql/0018_global_linguistic_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• status: text not null default 'queued' check (status in ('queued', 'processing', 'ai_ready', 'ready', 'published', 'failed'))
• theme: text null
• pos: text null
• count: integer not null default 1
• target_languages: text[] not null default '{en}'
• payload: jsonb not null default '{}'::jsonb
• result: jsonb not null default '{}'::jsonb
• created_by: uuid null
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• (none parsed)

content_metadata

• source: sql/0006_content_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• content_id: uuid not null references public.legacy_content_entries(id) on delete cascade
• difficulty_score: numeric(5,2)
• frequency_rank: integer
• semantic_group: text
• notes: text
• relations:
• content_metadata.content_id -> legacy_content_entries

course_content_items

• source: sql/0027_course_content_and_app_cms.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• course_id: uuid not null references public.courses(id) on delete cascade
• entity_type: text not null check (entity_type in ('lemma','sentence','grammar_card','grammar_concept'))
• entity_id: uuid not null
• unit_id: uuid null references public.units(id) on delete set null
• lesson_id: uuid null references public.lessons(id) on delete set null
• introduced_unit_number: integer null
• tags_override: jsonb null
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• course_content_items.course_id -> courses
• course_content_items.unit_id -> units
• course_content_items.lesson_id -> lessons

curricular_nodes

• source: sql/0018_global_linguistic_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• node_type: text not null check (node_type in ('course', 'unit', 'skill', 'lesson'))
• parent_id: uuid null references public.curricular_nodes(id) on delete cascade
• title: text not null
• metadata: jsonb not null default '{}'::jsonb
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• curricular_nodes.parent_id -> curricular_nodes

docs_index

• source: sql/0039_docs_index.sql
• purpose: Stores full-text searchable documentation pages.
• fields:
• id: uuid primary key default gen_random_uuid()
• slug: text not null
• title: text not null
• content: text not null
• commit_hash: text not null
• updated_at: timestamptz not null default now()
• relations:
• (none parsed)

grammar_card_assets

• source: sql/0012_grammar_content_bank.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• card_id: uuid not null references public.grammar_cards(id) on delete cascade
• type: text not null
• lang: text
• asset_url: text not null
• provider: text
• preset: text
• cache_key: text
• created_at: timestamptz not null default now()
• relations:
• grammar_card_assets.card_id -> grammar_cards

grammar_card_examples

• source: sql/0012_grammar_content_bank.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• card_id: uuid not null references public.grammar_cards(id) on delete cascade
• sentence_id: uuid not null references public.sentences(id) on delete restrict
• order_index: integer not null default 0
• highlight_token_index: integer
• note: text
• relations:
• grammar_card_examples.card_id -> grammar_cards
• grammar_card_examples.sentence_id -> sentences

grammar_cards

• source: sql/0012_grammar_content_bank.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• concept_id: uuid not null references public.grammar_concepts(id) on delete restrict
• title: text not null
• explanation_native: text
• native_lang: text not null
• explanation_target: text
• target_lang: text
• status: text not null default 'draft' check (status in ('draft', 'ready', 'published'))
• curriculum_tags: jsonb
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• grammar_cards.concept_id -> grammar_concepts

grammar_concepts

• source: sql/0012_grammar_content_bank.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• code: text
• title: text
• description: text
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

legacy_content_entries

• source: sql/0006_content_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• type: text not null check (type in ('lemma', 'sentence', 'phrase', 'dialogue'))
• source_lang: text not null
• target_lang: text not null
• source_text: text not null
• target_text: text
• tags: text[] not null default '{}'
• level: text not null check (level in ('A1', 'A2', 'B1', 'B2', 'C1', 'C2'))
• image_url: text
• audio_url: text
• image_style: text
• audio_style: text
• ai_generated: boolean not null default false
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

lesson_content_map

• source: sql/0018_global_linguistic_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• lesson_node_id: uuid not null references public.curricular_nodes(id) on delete cascade
• entity_type: text not null check (entity_type in ('lemma', 'sentence', 'word_form'))
• entity_id: uuid not null
• usage_status: text not null check (usage_status in ('used', 'planned'))
• created_at: timestamptz not null default now()
• relations:
• lesson_content_map.lesson_node_id -> curricular_nodes

lessons

• source: sql/0006_content_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• skill_id: uuid not null references public.skills(id) on delete cascade
• type: text not null check (type in ('lesson', 'practice', 'test', 'review'))
• order_index: integer not null default 1
• created_at: timestamptz not null default now()
• relations:
• lessons.skill_id -> skills

morphology_rules

• source: sql/0010_morphological_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• language: text not null
• pos: text not null
• rule_name: text
• pattern: jsonb
• transformation: jsonb
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

pos_colors

• source: sql/0007_content_bank_consolidation.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• pos: text primary key
• color_hex: text
• label: text
• relations:
• (none parsed)

project_course_bindings

• source: sql/0027_course_content_and_app_cms.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• project_id: uuid not null references public.app_projects(id) on delete cascade
• course_id: uuid not null references public.courses(id) on delete cascade
• created_at: timestamptz not null default now()
• relations:
• project_course_bindings.project_id -> app_projects
• project_course_bindings.course_id -> courses

semantic_relations

• source: sql/0010_morphological_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• lemma_id: uuid not null
• related_lemma_id: uuid not null
• relation_type: text not null
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

sentence_tokens

• source: sql/0007_content_bank_consolidation.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• sentence_id: uuid not null
• lemma_id: uuid not null
• position: integer not null
• role: text
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

sentence_lemmas

• source: sql/0014_sentences_admin_v2.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• sentence_id: uuid not null references public.sentences(id) on delete cascade
• lemma_id: uuid not null references public.vocabulary(id) on delete cascade
• created_at: timestamptz not null default now()
• relations:
• sentence_lemmas.sentence_id -> sentences
• sentence_lemmas.lemma_id -> vocabulary

sentences

• source: sql/0007_content_bank_consolidation.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• text: text not null
• lang: text not null
• cefr_level: text
• introduced_chapter: integer
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

sql_audit_log

• source: sql/0023_spok_sql_guardrails.sql
• purpose: Stores observability, diagnostics, or audit data.
• fields:
• id: bigserial primary key
• executed_at: timestamptz not null default now()
• executed_by: text null
• rpc_name: text not null
• sql_text: text not null
• relations:
• (none parsed)

sql_mutation_allowlist

• source: sql/0023_spok_sql_guardrails.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• schema_name: text not null
• table_name: text not null
• is_enabled: boolean not null default true
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• (none parsed)

system_errors

• source: sql/0035_system_errors_registry.sql
• purpose: Stores observability, diagnostics, or audit data.
• fields:
• id: uuid primary key default gen_random_uuid()
• service: text
• path: text
• method: text
• error_code: text
• message: text
• meta: jsonb
• created_at: timestamptz default now()
• relations:
• (none parsed)

system_events

• source: sql/0037_system_events.sql
• purpose: Stores observability, diagnostics, or audit data.
• fields:
• id: uuid primary key default gen_random_uuid()
• type: text not null check (type in ('error', 'warning', 'info', 'perf', 'security'))
• source: text not null
• message: text not null
• metadata: jsonb not null default '{}'::jsonb
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

theme_links

• source: sql/0018_global_linguistic_engine.sql
• purpose: Stores project theme tokens for UI theming and configuration.
• fields:
• id: uuid primary key default gen_random_uuid()
• theme_id: uuid not null references public.themes(id) on delete cascade
• entity_type: text not null check (entity_type in ('unit', 'skill', 'lemma', 'sentence'))
• entity_id: uuid not null
• created_at: timestamptz not null default now()
• relations:
• theme_links.theme_id -> themes

themes

• source: sql/0018_global_linguistic_engine.sql
• purpose: Stores project theme tokens for UI theming and configuration.
• fields:
• id: uuid primary key default gen_random_uuid()
• parent_theme_id: uuid null references public.themes(id) on delete set null
• slug: text not null unique
• title: text not null
• created_at: timestamptz not null default now()
• relations:
• themes.parent_theme_id -> themes

translation_pairs

• source: sql/0018_global_linguistic_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• source_lemma_id: uuid not null references public.vocabulary(id) on delete cascade
• target_lemma_id: uuid not null references public.vocabulary(id) on delete cascade
• source_lang: text not null
• target_lang: text not null
• quality_score: numeric null
• status: text not null default 'ai_ready' check (status in ('draft', 'processing_ai', 'ai_ready', 'ready', 'published'))
• metadata: jsonb not null default '{}'::jsonb
• created_at: timestamptz not null default now()
• relations:
• translation_pairs.source_lemma_id -> vocabulary
• translation_pairs.target_lemma_id -> vocabulary

tts_assets

• source: sql/0013_tts_assets.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• entity_type: text not null
• entity_id: uuid not null
• lang: text not null
• voice_preset: text not null
• provider: text not null
• text_hash: text not null
• text_snapshot: text not null
• asset_url: text not null
• duration_ms: integer
• status: text not null default 'ready'
• error_message: text
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• (none parsed)

universal_concepts

• source: sql/0018_global_linguistic_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• semantic_core: text not null
• global_tags: jsonb not null default '[]'::jsonb
• image_asset_id: uuid null
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• (none parsed)

user_course_enrollments

• source: sql/0004_enrollment.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• user_id: uuid not null
• course_id: uuid not null references public.courses(id) on delete cascade
• enrolled_at: timestamptz not null default now()
• relations:
• user_course_enrollments.course_id -> courses

verb_conjugations

• source: sql/0016_verb_conjugation.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• sense_id: uuid not null references public.senses(id) on delete cascade
• tense_key: text not null references public.verb_tenses(tense_key)
• person_key: text null
• form: text not null
• is_irregular: boolean not null default false
• metadata: jsonb null
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• verb_conjugations.sense_id -> senses
• verb_conjugations.tense_key -> verb_tenses

verb_tenses

• source: sql/0016_verb_conjugation.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• tense_key: text primary key
• label: text not null
• group_key: text not null
• order_index: int not null
• relations:
• (none parsed)

vocabulary

• source: sql/0018_global_linguistic_engine.sql (extends sql/0007_content_bank_consolidation.sql)
• purpose: Stores vocabulary entities used by learning and content-bank workflows with a status-only editorial workflow.
• fields:
• id: uuid primary key default gen_random_uuid()
• lemma: text not null
• pos: text
• status: text not null default 'draft' check (status in ('draft', 'processing_ai', 'ai_ready', 'ready', 'published'))
• cefr_level: text
• frequency_rank: integer
• introduced_chapter: integer
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

vocabulary_components

• source: sql/0032_vocabulary_chunks.sql
• purpose: Stores vocabulary entities used by learning and content-bank workflows.
• fields:
• id: uuid primary key default gen_random_uuid()
• chunk_id: uuid not null references public.vocabulary(id) on delete cascade
• lemma_id: uuid not null references public.vocabulary(id) on delete restrict
• position: integer not null
• created_at: timestamptz not null default now()
• relations:
• vocabulary_components.chunk_id -> vocabulary
• vocabulary_components.lemma_id -> vocabulary

vocabulary_relations

• source: sql/0018_global_linguistic_engine.sql
• purpose: Stores vocabulary entities used by learning and content-bank workflows.
• fields:
• id: uuid primary key default gen_random_uuid()
• source_lemma_id: uuid not null references public.vocabulary(id) on delete cascade
• target_lemma_id: uuid not null references public.vocabulary(id) on delete cascade
• relation_type: text not null
• metadata: jsonb not null default '{}'::jsonb
• created_at: timestamptz not null default now()
• relations:
• vocabulary_relations.source_lemma_id -> vocabulary
• vocabulary_relations.target_lemma_id -> vocabulary

vocabulary_themes

• source: sql/0034_fix_themes_migration.sql
• purpose: Stores vocabulary entities used by learning and content-bank workflows.
• fields:
• lemma_id: uuid not null references public.vocabulary(id) on delete cascade
• theme_id: uuid not null references public.themes(id) on delete cascade
• created_at: timestamptz not null default now()
• relations:
• vocabulary_themes.lemma_id -> vocabulary
• vocabulary_themes.theme_id -> themes

lemma_assets

• source: sql/0007_content_bank_consolidation.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• lemma_id: uuid not null
• asset_type: text not null
• asset_url: text not null
• provider: text
• style: jsonb
• voice: jsonb
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

lemma_forms

• source: sql/0010_morphological_engine.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• lemma_id: uuid not null
• relations:
• (none parsed)

lemma_grammar

• source: sql/0007_content_bank_consolidation.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• lemma_id: uuid not null
• concept_id: uuid not null
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

word_revisions

• source: sql/0017_word_revision_audit.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• entity_type: text not null
• entity_id: uuid not null
• changed_by: uuid null
• diff: jsonb not null default '{}'::jsonb
• created_at: timestamptz not null default now()
• relations:
• (none parsed)

senses

• source: sql/0015_senses.sql
• purpose: Stores lexical senses linked to vocabulary entries.
• fields:
• id: uuid primary key default gen_random_uuid()
• lemma_id: uuid not null references public.vocabulary(id) on delete cascade
• pos: text not null
• cefr_level: text null
• frequency_rank: int null
• is_primary: boolean not null default false
• status: text not null default 'draft' check (status in ('draft', 'enriched', 'reviewed', 'published'))
• ai_generated: boolean not null default false
• enrichment_version: int not null default 1
• last_enriched_at: timestamptz null
• created_at: timestamptz not null default now()
• updated_at: timestamptz not null default now()
• relations:
• senses.lemma_id -> vocabulary

sense_translations

• source: sql/0007_content_bank_consolidation.sql
• purpose: Application domain table for Spoke Plus features.
• fields:
• id: uuid primary key default gen_random_uuid()
• lemma_id: uuid not null
• lang: text not null
• text: text not null
• is_primary: boolean not null default false
• created_at: timestamptz not null default now()
• relations:
• (none parsed)