gaetan/knowledgify

Implement: Database layer — SQLite schema, card upsert, and hash-based sync #15

New issue

Open

opened 2026-08-20 15:31:35 +02:00 by gaetan · 0 comments

gaetan commented

2026-08-20 15:31:35 +02:00

Owner

Wayfinder map: #1
Dependencies: #13 (project scaffolding), #14 (content loading)

Description

Build the database layer: SQLite schema, card upsert operations, and hash-based content sync to detect changes and reset progress when content changes.

Tasks

Create internal/database/ package
Use modernc.org/sqlite for SQLite driver
Create tables:
- cards table: id (TEXT PK), type (TEXT), question (TEXT), answer (TEXT), explanation (TEXT), category (TEXT), tags (TEXT), content_hash (TEXT), created_at (DATETIME), updated_at (DATETIME)
- progress table: card_id (TEXT PK/FK), ease_factor (REAL, default 2.5), interval (INTEGER, default 0), repetitions (INTEGER, default 0), next_review (DATETIME), last_review (DATETIME), status (TEXT: "new"/"learning"/"review")
Card upsert: insert or update cards by ID
Hash-based sync:
- Compute SHA256 of card content (question + answer + explanation)
- Store hash in cards.content_hash
- On sync, detect changed hashes → reset progress for that card
- Detect removed cards → optionally archive or delete
KNOWLEDGIFY_DB env var for DB path (from config package)
Seed function: load content from content package, upsert into DB
Unit tests for schema creation, upsert, and hash detection

Acceptance criteria

SQLite database is created with correct schema
Cards can be upserted by ID
Content hash changes are detected and progress is reset
Seed function loads content and populates the database
Unit tests pass

**Wayfinder map:** #1 **Dependencies:** #13 (project scaffolding), #14 (content loading) ## Description Build the database layer: SQLite schema, card upsert operations, and hash-based content sync to detect changes and reset progress when content changes. ## Tasks - [ ] Create `internal/database/` package - [ ] Use `modernc.org/sqlite` for SQLite driver - [ ] Create tables: - `cards` table: id (TEXT PK), type (TEXT), question (TEXT), answer (TEXT), explanation (TEXT), category (TEXT), tags (TEXT), content_hash (TEXT), created_at (DATETIME), updated_at (DATETIME) - `progress` table: card_id (TEXT PK/FK), ease_factor (REAL, default 2.5), interval (INTEGER, default 0), repetitions (INTEGER, default 0), next_review (DATETIME), last_review (DATETIME), status (TEXT: "new"/"learning"/"review") - [ ] Card upsert: insert or update cards by ID - [ ] Hash-based sync: - Compute SHA256 of card content (question + answer + explanation) - Store hash in cards.content_hash - On sync, detect changed hashes → reset progress for that card - Detect removed cards → optionally archive or delete - [ ] `KNOWLEDGIFY_DB` env var for DB path (from config package) - [ ] Seed function: load content from content package, upsert into DB - [ ] Unit tests for schema creation, upsert, and hash detection ## Acceptance criteria - SQLite database is created with correct schema - Cards can be upserted by ID - Content hash changes are detected and progress is reset - Seed function loads content and populates the database - Unit tests pass

gaetan referenced this issue

2026-08-20 15:31:53 +02:00

Implement: SM-2 review engine — scheduling, due cards, and rating #16

gaetan referenced this issue

2026-08-20 15:32:07 +02:00

Implement: HTTP handlers and routing — dashboard, categories, and review endpoints #17

gaetan referenced this issue

2026-08-20 15:32:44 +02:00

Implement: Sample content (~50 cards) and end-to-end verification #19