TwigaGuide · DevelopersTwigaGuide · DevelopersHow Twiga Works
Nine tables in Postgres, defined with SQLModel. The conversation, the curriculum and the vector index all live in the same database.
Tables are declared in app/database/models.py and queried through app/database/db.py. The runtime uses an async driver; Alembic uses a synchronous one against the same URL.
| Table | What it holds |
|---|---|
users | Teachers, keyed by WhatsApp ID. Carries the state machine (blocked, in review, approved, onboarding, active, inactive), the onboarding step, school, region and last activity. |
subjects | One row per subject in use, from a fixed list of thirty. |
classes | A subject paired with a grade level, for example Geography and Form 2. Unique on that pair. |
teachers_classes | Which teacher teaches which class. |
resources | A textbook or curriculum document. |
classes_resources | Which documents belong to which class. |
chunks | The retrieval unit. A passage of a document plus its embedding, page number and section. |
messages | Every conversation turn, including tool calls and tool results. |
generated_exams | Exams the model has produced, stored as JSON for redelivery. |
subject ──1:N── class ──N:M── user (via teachers_classes)
│
└──N:M── resource ──1:N── chunk
user ──1:N── message
user ──1:N── generated_examDeletes cascade throughout, with one exception noted at the bottom of this page.
A single exchange can write several rows. The assistant message that requests a tool, one row per tool result, and the final answer are all stored.
The column that decides what a teacher actually saw is is_present_in_conversation. The raw model output is stored with that flag off and a rendered copy is stored with it on, so citations and exam markers are expanded in what is displayed but the original is kept. Any interface reading this table should filter on that flag.
source_chunk_ids records which textbook passages backed an answer, which is what makes citations possible.
Embeddings are stored in the chunks table using pgvector, as a 1024 dimension column with an HNSW index using cosine distance. Search is an ordinary query ordered by cosine distance, so there is no separate vector database to run.
The embedding dimension is hard-coded
The column is fixed at 1024, which matches the multilingual and BAAI sample data. The repository also ships an OpenAI sample file at 1536 dimensions, and it will not load.
To change embedding model you have to edit the column definition in app/database/models.py, write a migration to alter the column, and re-embed every chunk. No such migration exists yet.
States, roles, subjects, grade levels and chunk types are all Postgres enum types rather than plain strings. This is worth knowing before you change one, because Alembic does not detect new enum values automatically. See Database & Migrations.
generated_exams.class_id is indexed but has no foreign key, so deleting a class leaves orphaned exam rows.users.updated_at is not nullable and has no database default. It is populated by the ORM, so raw SQL inserts must set it.Section model exists in the file but is commented out. It is not part of the schema.chunks_tmp_reembed table that Alembic does not know about and a reset does not drop.