feat: per-projection SQLite database for independent rebuild #162

New issue

Closed

opened 2026-03-01 17:49:21 +00:00 by ash · 0 comments

ash commented

2026-03-01 17:49:21 +00:00

Owner

What

Currently apps pass one *sql.DB to all projections. All tables live in one SQLite file. Changing any projection requires rebuilding ALL projections.

Target

Each sqlview.Config opens its own SQLite database file. Directory structure:

projections/
├── services.service_list.db
├── passes.pass_view.db
├── scanning.scan_log.db
└── ...

Why

Change one projection → delete one file → rebuild ONLY that projection
All other projections serve immediately from existing data
Zero-downtime deploys: only changed projections rebuild, rest instant
Cleaner isolation: one projection crash doesnt affect others

API Change

// Before: one shared DB
collector := projection.NewCollector()
pass.Register(bus, store, collector, sharedDB)  // all use same DB

// After: projection directory
collector := projection.NewCollector(projection.Config{
    Dir: "/opt/yoyopass/data/projections",
})
pass.Register(bus, store, collector)  // collector manages per-projection DBs

The Collector creates {Name}.db for each registered StateView. Setup creates the table in that DB. Checkpoint is either in the same DB or a separate checkpoint DB per projection.

Implementation

projection.Collector accepts a directory path
On Add(view): opens {dir}/{view.Name}.db, passes *sql.DB to the view
Setup() runs migrations per DB
Checkpoint colocated per projection DB (delete DB = reset checkpoint = full rebuild)
Backward compatible: if Dir not set, falls back to shared DB mode

Scope

eskit: projection.Collector changes
YoYoPass: internal/app/app.go passes directory instead of single DB
Deploy scripts: --rebuild-projection=name deletes specific DB file

Priority

High — blocks optimal zero-downtime deployment strategy.

## What Currently apps pass one `*sql.DB` to all projections. All tables live in one SQLite file. Changing any projection requires rebuilding ALL projections. ## Target Each `sqlview.Config` opens its own SQLite database file. Directory structure: ``` projections/ ├── services.service_list.db ├── passes.pass_view.db ├── scanning.scan_log.db └── ... ``` ## Why - Change one projection → delete one file → rebuild ONLY that projection - All other projections serve immediately from existing data - Zero-downtime deploys: only changed projections rebuild, rest instant - Cleaner isolation: one projection crash doesnt affect others ## API Change ```go // Before: one shared DB collector := projection.NewCollector() pass.Register(bus, store, collector, sharedDB) // all use same DB // After: projection directory collector := projection.NewCollector(projection.Config{ Dir: "/opt/yoyopass/data/projections", }) pass.Register(bus, store, collector) // collector manages per-projection DBs ``` The `Collector` creates `{Name}.db` for each registered StateView. Setup creates the table in that DB. Checkpoint is either in the same DB or a separate checkpoint DB per projection. ## Implementation 1. `projection.Collector` accepts a directory path 2. On `Add(view)`: opens `{dir}/{view.Name}.db`, passes `*sql.DB` to the view 3. `Setup()` runs migrations per DB 4. Checkpoint colocated per projection DB (delete DB = reset checkpoint = full rebuild) 5. Backward compatible: if `Dir` not set, falls back to shared DB mode ## Scope - eskit: `projection.Collector` changes - YoYoPass: `internal/app/app.go` passes directory instead of single DB - Deploy scripts: `--rebuild-projection=name` deletes specific DB file ## Priority High — blocks optimal zero-downtime deployment strategy.