Export/import progress (JSON backup)

[atbrace]

Allow users to export progress to JSON and import it back. Useful for backup/restore and migrating between machines.

[claude]

Issue Enrichment Report

Type

Feature — new capability not currently in the codebase. The infra label seems misapplied; this is user-facing functionality. No type label exists; recommend adding feature.

Scope & Alignment

In scope. Export/import progress is explicitly listed as a planned feature in the README (README.md:403). Fits local-first design — it enables data portability between local installs without any cloud dependency.

Related Issues

• Duplicates: none found
• Overlaps with: Spaced repetition for question scheduling #32 (Spaced repetition) — both touch topic_progress and question_attempts tables; spaced repetition schema additions may affect the import payload shape if implemented first
• Blocked by: none
• Blocks: none

Acceptance Criteria

No criteria defined. Proposed:

• GET /api/export returns a valid JSON file download containing all rows from topic_progress, question_attempts, assessment_sessions, weak_areas, and study_sessions
• Exported JSON includes a schema_version field (e.g. 1) for forward-compatibility
• POST /api/import with a valid export file restores all progress data and returns HTTP 200
• Importing into a clean database produces the same mastery scores as the original install
• Import rejects payloads missing schema_version or with unrecognized versions (HTTP 400)
• Export/import UI is accessible from the Progress page (download button + file upload)
• tutor_conversations and experiment_deployments are excluded from the export (not user-progress data)

Code Surface Area

Files likely touched:

• src/app/api/export/route.ts — new GET endpoint; queries all 5 progress tables and streams JSON response with Content-Disposition: attachment
• src/app/api/import/route.ts — new POST endpoint; validates payload, wraps upserts in a transaction
• src/lib/db/migrations/003_*.sql — optional, only if import needs a staging table or version tracking
• src/app/[exam]/progress/page.tsx — add export download button and file upload trigger
• src/components/progress/ — new ExportImport client component for file picker and feedback UI

Effort estimate: medium

Research Findings

The existing /api/progress GET route (sync better-sqlite3 pattern) is a good template for the export endpoint. The /api/assess POST route shows the transaction-wrapped multi-table write pattern to follow for import. Key implementation notes:

• All 5 tables have exam_id as a natural partition key — exporting per-exam or all-exams are both feasible; all-exams is simpler.
• Import should use INSERT OR REPLACE (SQLite upsert) to be idempotent — safe to re-import without duplication.
• File download from Next.js App Router: return new Response(JSON.stringify(data), { headers: { 'Content-Disposition': 'attachment; filename="progress-backup.json"', 'Content-Type': 'application/json' } }).
• File upload on the client: standard <input type="file" accept=".json"> + FileReader.readAsText(), then POST to import endpoint.
• No new dependencies required.

Priority Assessment

P3-low is appropriate. This is a utility/backup feature that doesn't affect core study functionality, and it's listed under "Planned" (not "In Progress") in the README.

---

Automated enrichment by Claude — verify findings before implementation