RFC: Multi-DB Sync Architecture for Team Mode

[BYK]

Summary

Design and implement the multi-DB sync architecture that enables team knowledge sharing via Turso.

Architecture Decisions (Confirmed)

Question	Decision
DB topology	Personal DB + Team DB — each user has a personal DB (local SQLite backed up to Turso cloud), each team has a separate team DB (Turso cloud)
Promotion flow	Both — explicit user action + curator auto-suggest with admin approval queue
Schema divergence	Same schema — team-specific columns exist in both but are nullable/unused in personal DB
Cross-project scope	Stays personal — cross_project=1 means "across MY projects." Team sharing is orthogonal
Turso integration	Gateway handles sync — core stays pure local SQLite. Gateway manages Turso connections and sync scheduling
Backward compat	CLI login flow — lore team join sets up Turso credentials. Without it, fully local
Migration path	Offer bulk import — on team join, user can bulk-promote existing entries to team DB with approval
Team DB provisioning	CLI + web dashboard
Team entry ranking	Personal entries preferred — personal entries get a score boost over team entries in forSession()
.lore.md in team mode	Option D — .lore.md (committed) = team entries, .lore.local.md (gitignored) = personal entries. Local-only mode unchanged.

Key Technical Finding

Turso partial sync is NOT row-level isolation. Query bootstrap controls initial page download, but lazy page fetching means any local query can pull any row from the remote DB. Fine-grained permissions are table-level only, no RLS.

Conclusion: Security is enforced via database-per-user/team separation, not column-based visibility filtering.

Implementation Phases

Phase 0: Schema prep ✅ (PR #468)

• Migration v28: attribution, promotion, approval columns on knowledge table
• team_knowledge and team_config tables (empty scaffolding)
• Updated types, CRUD functions, 7 new tests

Phase 1: Personal cloud backup

• lore team join CLI command (stores Turso credentials in team_config)
• Gateway push/pull for personal DB cloud backup
• No team features yet — just cloud persistence

Phase 2: Team knowledge pull

• Team DB created by team admin (CLI or web dashboard)
• Gateway pulls approved team entries into team_knowledge table
• forSession() merges personal + team entries (personal preferred)
• .lore.md / .lore.local.md split for team mode

Phase 3: Promotion workflow

• Explicit promotion via lore team promote <id> / curator auto-suggest
• Gateway pushes nominated entries to team DB with approval_status = "pending"
• Team admin approval (CLI or dashboard)

Phase 4: Full sync + polish

• Conflict resolution, entry updates syncing back
• Bulk import on team join
• Dashboard for team knowledge management
• Analytics, stale entry detection

Monetization Framing

Free tier: .lore.md per repo via git (static, manual, per-repo)
Paid team tier: Cross-repo team knowledge base, auto-curation, approval workflows, real-time sync, analytics, onboarding acceleration, knowledge lifecycle management

Open Questions

1. Auth token lifecycle — short-lived JWTs from our auth service vs. long-lived platform tokens?
2. Offline team access — how stale is acceptable for team_knowledge cache?
3. Entry dedup across personal/team — merge, reject, or let admin decide?