gaetan/knowledgify
1
0
Code Issues 8 Projects Packages Wiki Activity Actions

Decision: Data storage — SQLite schema and content loading strategy #10

New issue
Closed
opened 2026-08-20 14:35:31 +02:00 by gaetan · 1 comment
gaetan commented 2026-08-20 14:35:31 +02:00
Owner
Copy link

Part of #1

Question

What does the SQLite database schema look like, and how does content loading work?

The content model and user model are settled, so we can now design the storage layer.

Cards table:

CREATE TABLE cards (
  id INTEGER PRIMARY KEY,
  type TEXT NOT NULL CHECK(type IN ('qa', 'vf')),
  question TEXT NOT NULL,
  answer TEXT NOT NULL,
  explanation TEXT,
  category TEXT NOT NULL,
  tags TEXT,
  content_path TEXT NOT NULL,
  content_id INTEGER NOT NULL
);

Progress table:

CREATE TABLE progress (
  card_id INTEGER PRIMARY KEY,
  next_review DATETIME NOT NULL,
  interval REAL NOT NULL DEFAULT 0,
  ease_factor REAL NOT NULL DEFAULT 2.5,
  repetitions INTEGER NOT NULL DEFAULT 0,
  last_review DATETIME,
  FOREIGN KEY (card_id) REFERENCES cards(id)
);

Key design questions:

  • Should cards be stored in the DB or only in JSON? The content model says JSON is source of truth. Options:

    • Store cards in DB (loaded from JSON on startup). Simpler queries, but requires sync logic.
    • Store only progress in DB, read cards from JSON on demand. Simpler sync, but queries are slower.
    • Hybrid: cards in DB for fast queries, JSON as source of truth for updates.

  • How to handle card updates? If a card's question/answer changes in JSON, does the progress reset? Or is it preserved?

  • Should we store card hashes? To detect when JSON content changes without re-parsing everything.

  • Database file location? Same directory as the binary? User's home directory? Configurable?

This decision shapes the data access layer and the content loading pipeline.

Dependencies

  • Depends on: #2 (content model), #6 (user model)
Part of #1 ## Question What does the SQLite database schema look like, and how does content loading work? The content model and user model are settled, so we can now design the storage layer. Cards table: ```sql CREATE TABLE cards ( id INTEGER PRIMARY KEY, type TEXT NOT NULL CHECK(type IN ('qa', 'vf')), question TEXT NOT NULL, answer TEXT NOT NULL, explanation TEXT, category TEXT NOT NULL, tags TEXT, content_path TEXT NOT NULL, content_id INTEGER NOT NULL ); ``` Progress table: ```sql CREATE TABLE progress ( card_id INTEGER PRIMARY KEY, next_review DATETIME NOT NULL, interval REAL NOT NULL DEFAULT 0, ease_factor REAL NOT NULL DEFAULT 2.5, repetitions INTEGER NOT NULL DEFAULT 0, last_review DATETIME, FOREIGN KEY (card_id) REFERENCES cards(id) ); ``` Key design questions: - **Should cards be stored in the DB or only in JSON?** The content model says JSON is source of truth. Options: - Store cards in DB (loaded from JSON on startup). Simpler queries, but requires sync logic. - Store only progress in DB, read cards from JSON on demand. Simpler sync, but queries are slower. - Hybrid: cards in DB for fast queries, JSON as source of truth for updates. - **How to handle card updates?** If a card's question/answer changes in JSON, does the progress reset? Or is it preserved? - **Should we store card hashes?** To detect when JSON content changes without re-parsing everything. - **Database file location?** Same directory as the binary? User's home directory? Configurable? This decision shapes the data access layer and the content loading pipeline. ## Dependencies - Depends on: #2 (content model), #6 (user model)
gaetan commented 2026-08-20 14:47:02 +02:00
Author
Owner
Copy link

Resolution

Data storage decided:

Cards in DB: Yes. JSON files are parsed and upserted into SQLite on startup. All queries (due cards, category stats, search) use the DB.

Sync strategy: Hash-based. Store SHA256 hash of card content (question+answer+explanation) in DB. On startup, compare hashes. If changed → delete progress row (reset). If unchanged → keep progress.

Card update behavior: If a card's content changes in JSON, its progress is automatically reset (hash mismatch). The user starts fresh with the new card.

DB file location: Configurable via KNOWLEDGIFY_DB env var. Default: ./knowledgify.db.

SQLite driver: modernc.org/sqlite (pure Go, no CGO required).

Final DB schema:

CREATE TABLE cards (
  id INTEGER PRIMARY KEY,
  type TEXT NOT NULL CHECK(type IN ('qa', 'vf')),
  question TEXT NOT NULL,
  answer TEXT NOT NULL,
  explanation TEXT,
  category TEXT NOT NULL,
  tags TEXT,
  content_hash TEXT NOT NULL,
  content_path TEXT NOT NULL,
  content_id INTEGER NOT NULL
);

CREATE TABLE progress (
  card_id INTEGER PRIMARY KEY,
  next_review DATETIME NOT NULL,
  interval REAL NOT NULL DEFAULT 0,
  ease_factor REAL NOT NULL DEFAULT 2.5,
  repetitions INTEGER NOT NULL DEFAULT 0,
  last_review DATETIME,
  FOREIGN KEY (card_id) REFERENCES cards(id)
);

CREATE INDEX idx_cards_category ON cards(category);
CREATE INDEX idx_cards_content_hash ON cards(content_hash);
CREATE INDEX idx_progress_next_review ON progress(next_review);

Startup flow:

  1. Open SQLite DB
  2. Parse all JSON files in content/
  3. For each card: compute hash, upsert into cards table
  4. For each DB card: if hash changed since last run, delete progress row
  5. Query progress for cards with next_review <= now → due cards
## Resolution Data storage decided: **Cards in DB**: Yes. JSON files are parsed and upserted into SQLite on startup. All queries (due cards, category stats, search) use the DB. **Sync strategy**: Hash-based. Store SHA256 hash of card content (question+answer+explanation) in DB. On startup, compare hashes. If changed → delete progress row (reset). If unchanged → keep progress. **Card update behavior**: If a card's content changes in JSON, its progress is automatically reset (hash mismatch). The user starts fresh with the new card. **DB file location**: Configurable via `KNOWLEDGIFY_DB` env var. Default: `./knowledgify.db`. **SQLite driver**: `modernc.org/sqlite` (pure Go, no CGO required). **Final DB schema**: ```sql CREATE TABLE cards ( id INTEGER PRIMARY KEY, type TEXT NOT NULL CHECK(type IN ('qa', 'vf')), question TEXT NOT NULL, answer TEXT NOT NULL, explanation TEXT, category TEXT NOT NULL, tags TEXT, content_hash TEXT NOT NULL, content_path TEXT NOT NULL, content_id INTEGER NOT NULL ); CREATE TABLE progress ( card_id INTEGER PRIMARY KEY, next_review DATETIME NOT NULL, interval REAL NOT NULL DEFAULT 0, ease_factor REAL NOT NULL DEFAULT 2.5, repetitions INTEGER NOT NULL DEFAULT 0, last_review DATETIME, FOREIGN KEY (card_id) REFERENCES cards(id) ); CREATE INDEX idx_cards_category ON cards(category); CREATE INDEX idx_cards_content_hash ON cards(content_hash); CREATE INDEX idx_progress_next_review ON progress(next_review); ``` **Startup flow**: 1. Open SQLite DB 2. Parse all JSON files in `content/` 3. For each card: compute hash, upsert into cards table 4. For each DB card: if hash changed since last run, delete progress row 5. Query progress for cards with `next_review <= now` → due cards
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
No results found.
No results found.
Labels
Clear labels   
wayfinder:grilling

   
wayfinder:map

   
wayfinder:prototype

   
wayfinder:prototype

   
wayfinder:research

   
wayfinder:research

   
wayfinder:task

   
wayfinder:task
No labels
wayfinder:grilling
wayfinder:map
wayfinder:prototype
wayfinder:prototype
wayfinder:research
wayfinder:research
wayfinder:task
wayfinder:task
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Depends on
#2 Decision: Content model — structure of culture générale cards and categories
gaetan/knowledgify
#6 Decision: User model — single user vs multi-user with auth
gaetan/knowledgify
Reference
gaetan/knowledgify#10
No description provided.