Domain model — JSON → Rust → Postgres port map
Why this document exists
The question shape is the product's IP. It must be ported 1:1. "Simplifying" it is a defect. This document is the checklist a reviewer uses to reject a lossy port.
Ground rules
1. questions.id stays a TEXT primary key. Values look like TB-000001 and already appear in exports and URLs. Never integer, never UUID.
2. Every field in the table below must exist in the Rust struct OR be explicitly listed under "Deliberately not ported" with a reason.
3. Nested arrays/objects are stored as JSONB and typed with serde_json::Value only where the shape is genuinely open; otherwise use a concrete #[derive(Serialize, Deserialize)] struct.
4. versions is an append-only array of full question snapshots. It is preserved verbatim. If it grows past a threshold, it moves to a question_versions table — the wire format does not change.
5. usage counters (timesUsed, lastUsed, testsUsedIn) must survive the port because they drive the "prefer never-used questions" selection priority.
6. Unknown fields are preserved, not dropped: deserialize into a struct with #[serde(flatten)] extra: Map<String, Value> (or a body JSONB column) so a future content import cannot silently lose data.
Top-level export envelope
| Key | JSON type | Postgres landing place | Notes |
|-----|-----------|------------------------|-------|
| schemaVersion | number | app_settings (key schema_version) | Single-row settings table |
| appVersion | string | app_settings (key app_version) | |
| exportedAt | string (ISO-8601) | app_settings (key exported_at) | |
| questions | array | questions table (1 row per question) | 2,343 rows |
| subjects | array | taxonomy table (type='subject') | 4 values in export; 3 map to LECPA subjects (FAR, Management Services, Taxation); "Theory of Accounts" is not one of the six and needs a mapping decision; AFAR, Auditing, RFBT absent |
| topics | array | taxonomy table (type='topic') | |
| chapters | array | taxonomy table (type='chapter') | |
| subtopics | array | taxonomy table (type='subtopic') | |
| tags | array | taxonomy table (type='tag') | |
| customTypes | array | custom_types (JSONB, reserved) | Empty today; reserved for future |
| collections | array | collections (JSONB, reserved) | Empty today; reserved for future |
| standards | array | standards (JSONB, reserved) | Empty today; reserved for future |
| tests | array | tests table | 15 rows |
| caseGroups | object (map) | case_groups table | 12 defined, 57 referenced |
| settings | object | app_settings (JSONB column) | Holds idCounter, theme, etc. |
*Taxonomy tables unified into one taxonomy table with type discriminator — avoids 5 near-identical tables for hierarchical data that shares the same shape. Because "Theory of Accounts" is not a LECPA subject, the taxonomy must allow a parent_subject mapping so those rows can be remapped without changing question text.*
Question fields
| JSON field | Type | Rust type | Postgres | Populated (n of 2343) | Port note |
|------------|------|-----------|----------|----------------------|-----------|
| type | string | String | TEXT | 2343 | Always "mcq"; keep for forward compat |
| text | string | String | TEXT | 2343 | Markdown with inline tables |
| choices | array | Vec<Choice> | JSONB | 2343 | See Nested shapes |
| correct | string | String | TEXT | 2343 | Choice id (e.g. "c3"), not an index. Grading compares answer id to this value. |
| explanation | string | String | TEXT | 2343 | 1,858 empty (79%) — keep empty string |
| tables | array | Vec<Table> | JSONB | 2 | Populated on 2 questions; schema present on all |
| subject | string | String | TEXT | 2343 | FK to taxonomy(type='subject') |
| topic | string | String | TEXT | 2343 | FK to taxonomy(type='topic') |
| chapter | string | Option<String> | TEXT NULL | 2197 | Optional; 146 null |
| subtopic | string | Option<String> | TEXT NULL | 2283 | Optional; 60 null |
| caseGroupId | string | Option<String> | TEXT NULL | 228 | Optional; 2115 null; 45 dangling refs |
| nature | string | Option<String> | TEXT NULL | 2119 | Enum: "", None, Problem, Theory |
| difficulty | string | String | TEXT | 2343 | Enum: Easy, Moderate, Difficult, Very Difficult |
| bloomLevel | string | String | TEXT | 2343 | Enum: "", Apply, Understand |
| status | string | String | TEXT | 2343 | Enum: draft, approved |
| archived | boolean | bool | BOOLEAN | 2343 | 8 true |
| favorite | boolean | bool | BOOLEAN | 2343 | |
| tags | array | Vec<String> | JSONB | 2343 | Tag names; FK to taxonomy(type='tag') |
| collections | array | Vec<String> | JSONB | 2343 | Empty on all; schema-only today |
| accountingArea | string | String | TEXT | 2343 | Enum: "", Financial Accounting |
| standard | string | String | TEXT | 0 | schema-only today |
| standardParagraph | string | String | TEXT | 0 | schema-only today |
| given | string | String | TEXT | 0 | schema-only today; empty string on all rows |
| required | string | String | TEXT | 0 | schema-only today; empty string on all rows |
| commonMistakes | string | String | TEXT | 0 | schema-only today; empty string on all rows |
| notes | string | Option<String> | TEXT NULL | 12 | Optional; present on 12 |
| journalEntries | array | Vec<serde_json::Value> | JSONB | 0 | schema-only today; open shape |
| tAccounts | array | Vec<serde_json::Value> | JSONB | 0 | schema-only today; open shape |
| schedules | array | Vec<serde_json::Value> | JSONB | 0 | schema-only today; open shape |
| usage | object | Usage | JSONB | 2343 | See Nested shapes |
| versions | array | Vec<QuestionSnapshot> | JSONB | 2343 | Present on all rows, non-empty on only 12 questions |
| id | string | String | TEXT PK | 2343 | Format TB-000001; never integer/UUID |
| dateAdded | string | DateTime<Utc> | TIMESTAMPTZ | 2343 | ISO-8601; lossy-parse fallback to text if unparseable |
| dateModified | string | DateTime<Utc> | TIMESTAMPTZ | 2343 | ISO-8601; lossy-parse fallback to text if unparseable |
| course | string | Option<String> | TEXT NULL | 12 | Optional |
| source | string | Option<String> | TEXT NULL | 12 | Optional |
| reference | string | Option<String> | TEXT NULL | 12 | Optional |
| author | string | Option<String> | TEXT NULL | 12 | Optional |
Nested shapes
Choice
{ "id": "A", "text": "Choice text with markdown" }
#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct Choice {
pub id: String,
pub text: String,
}
Table
{ "id": "TBL-64kcyuz", "position": "question", "headerRow": true, "rows": [["Age","Amount"],["0 - 60 days","6,000,000"]] }
#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct Table {
pub id: String,
pub position: String,
pub header_row: bool,
pub rows: Vec<Vec<String>>,
}
Usage
{ "timesUsed": 5, "lastUsed": "2026-10-15T08:30:00.000Z", "testsUsedIn": ["test-1", "test-3"] }
#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct Usage {
pub times_used: u32,
pub last_used: Option<DateTime<Utc>>,
pub tests_used_in: Vec<String>,
}
QuestionSnapshot
{ "type": "mcq", "text": "...", "choices": [...], "correct": "c3", "explanation": "...", "tables": [], "subject": "...", "topic": "...", "chapter": "...", "subtopic": "...", "caseGroupId": "...", "nature": "...", "difficulty": "...", "bloomLevel": "...", "status": "...", "archived": false, "favorite": false, "tags": [...], "collections": [], "accountingArea": "...", "standard": "...", "standardParagraph": "...", "given": "", "required": "", "commonMistakes": "", "notes": "...", "journalEntries": [], "tAccounts": [], "schedules": [], "usage": {...}, "versions": [], "id": "TB-000001", "dateAdded": "...", "dateModified": "...", "course": "...", "source": "...", "reference": "...", "author": "..." }
#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct QuestionSnapshot {
pub r#type: String,
pub text: String,
pub choices: Vec<Choice>,
pub correct: String,
pub explanation: String,
pub tables: Vec<Table>,
pub subject: String,
pub topic: String,
pub chapter: Option<String>,
pub subtopic: Option<String>,
pub case_group_id: Option<String>,
pub nature: Option<String>,
pub difficulty: String,
pub bloom_level: String,
pub status: String,
pub archived: bool,
pub favorite: bool,
pub tags: Vec<String>,
pub collections: Vec<String>,
pub accounting_area: String,
pub standard: String,
pub standard_paragraph: String,
pub given: String,
pub required: String,
pub common_mistakes: String,
pub notes: Option<String>,
pub journal_entries: Vec<serde_json::Value>,
pub t_accounts: Vec<serde_json::Value>,
pub schedules: Vec<serde_json::Value>,
pub usage: Usage,
pub versions: Vec<QuestionSnapshot>,
pub id: String,
pub date_added: DateTime<Utc>,
pub date_modified: DateTime<Utc>,
pub course: Option<String>,
pub source: Option<String>,
pub reference: Option<String>,
pub author: Option<String>,
}
Recursion risk in versions
versions holds full snapshots, and each snapshot has its own versions field. Deserialising a deeply
nested chain is unbounded recursion: serde_json will overflow the stack rather than error. Mitigation is
ordered — (a) the importer refuses a snapshot nested deeper than a configurable constant
MAX_VERSION_DEPTH (default 3), logging the question id;
(b) once versions exceeds 20 entries the row moves to the question_versions table and the column keeps
only the most recent snapshot. Wire format is unchanged by either measure.
Case groups
Observed shape: map id -> {title, scenario} with 12 defined entries for 57 distinct caseGroupId values on questions → 45 dangling ids with NO scenario text. scenario is the item-set stimulus text: without it the group cannot be rendered. See CONTENT-INVENTORY.md finding 1.
Rust shape:
#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct CaseGroup {
pub id: String,
pub title: String,
pub scenario: String,
}
Tests
Shape: {id, title, createdAt, versions:[String], questionCount, folder, sourceFolder}. Note that versions here is a list of version labels (observed label style is a single letter, e.g. ["A"]; 13 tests have 1 entry, 2 tests have 4 entries), not snapshots — it is a different concept from the question versions array and must not be conflated.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct Test {
pub id: String,
pub title: String,
pub created_at: DateTime<Utc>,
pub versions: Vec<String>,
pub question_count: usize,
pub folder: String,
pub source_folder: String,
}
Deliberately not ported
| Field | Reason |
|-------|--------|
| none | All fields accounted for; zero-populated fields kept for schema fidelity |
Review checklist
id is TEXT primary key (not integer, not UUID)usage counters (timesUsed, lastUsed, testsUsedIn) round-trip#[serde(flatten)] extra or body JSONBversions array stored as JSONB with recursive QuestionSnapshot typedateAdded/dateModified are TIMESTAMPTZ with documented lossy-parse fallbackcaseGroupId optional; 45 dangling refs documentedversions (labels) not conflated with question versions (snapshots)subject values normalised to the six LECPA subjects; "Theory of Accounts" rows have an explicit mapping decision