feat(journal): weekly audit log with activity report and SQL dump

[koolamusic]

Summary

Automated weekly system journal that audits content activity and produces a structured report + full SQL dump. Becomes the human-readable project memory, cleared on a 4-week rolling window.

What the journal captures each week

• Content stats: new fragments, entries, wikis created/updated
• Highlights: newest items, most updated, highest interaction (edge count, edit count)
• Surface changes: what happened across `content | edits | entries | wikis` tables
• Full SQL dump: complete database snapshot at that point in time (not a diff)

Architecture

Trigger

• Cron job — scheduled background worker (BullMQ repeatable job)
• Runs weekly (e.g., Sunday 00:00 UTC)
• Can also be triggered manually via API endpoint for on-demand generation

Storage

• DB table: `journals` with columns:
  • `id` (primary key)
  • `week_start` / `week_end` (date range covered)
  • `report` (JSONB — structured activity summary)
  • `report_text` (TEXT — human-readable plain text version)
  • `sql_dump_path` (TEXT — path to the .sql file on disk or object storage)
  • `created_at`

SQL dump

• Full `pg_dump` of the entire database at journal creation time
• Stored as a .sql file (not in the DB itself — too large)
• Always the complete dump, never a diff from previous

Rolling window

• Journals older than 4 weeks are archived (not deleted)
• Archive = export to .txt + .sql files, then soft-delete from journals table
• Archived journals can be loaded into Claude for historical context ("give Claude access to April Journal folder")

Export formats

• TXT: Plain text narrative of the week's activity
• SQL: Full database dump at that point in time
• Both formats together form an archival unit that can reconstruct the full system state

API endpoints

1. `GET /journals` — list recent journals (within 4-week window)
2. `GET /journals/:id` — get specific journal with report
3. `GET /journals/:id/export` — download TXT + SQL archive
4. `POST /journals/generate` — trigger on-demand journal generation

Use cases

1. Human review: Weekly summary of what Robin captured and organized
2. AI context loading: Give Claude a journal folder to catch up on project history
3. Compliance/audit: Timestamped snapshots of system state
4. Local mapping: SQL dumps enable offline analysis and local tooling

Files to create

1. `core/src/db/schema.ts` — add `journals` table
2. `core/src/jobs/weekly-journal.ts` — journal generation worker
3. `core/src/routes/journals.ts` — API endpoints
4. `core/src/lib/journal-report.ts` — report generation logic (queries + formatting)

[arthuradinder]

I'd like to fully work on this. Maybe if you have extra information we can chat more via my email arthur.adinda@nairobiai.org or +254717229758