refactor(db): extract Database trait abstraction layer (Phase 1)

[ajianaz]

Phase 1 — Foundation: isolate SQLite-specific code behind generic trait

Goal

Create trapfall-db crate with a Database trait. Move all 60+ raw SQL queries from store.rs + auth.rs into SqliteBackend impl. Zero behavior change.

Tasks

• Create crates/trapfall-db/ with lib.rs, schema.rs, sqlite.rs
• Define Database trait with all store methods (projects, issues, events, auth, alerts, lifecycle)
• Move entity structs (Project, Issue, Event, User, Session, AlertRule, AuthAttempt) to schema.rs
• Implement SqliteBackend — copy existing SQLite queries from store.rs + auth.rs
• Update AppState to hold Box<dyn Database> instead of SqlitePool
• Update all consumers: server.rs, digest.rs, alert.rs, retention.rs, ws.rs, metrics.rs, search.rs, mcp.rs
• Run full test suite — all tests pass, zero behavior change

Acceptance Criteria

• trapfall-db crate compiles and passes tests
• trapfalld uses Box<dyn Database> everywhere
• No SqlitePool reference outside sqlite.rs
• All existing tests pass

Effort

~1-2 days

Depends on

None — this is the foundation for all other phases.

[ajianaz]

Status: In Progress 🚧

Phase 1 implementation complete. PR: #186

Done

• New crate trapfall-db with Database trait (40+ methods)
• SqliteBackend impl — all 60+ SQL queries extracted from store.rs/auth.rs
• Store → thin facade over Arc<dyn Database>
• Store::pool() escape hatch retained (search/mcp/metrics — Phase 3)
• 84/84 tests pass — zero behavior change
• Net -489 lines (SQL deduplicated)

Notes

• Chose "narrow" scope: trait covers Store methods only. trapfall-search, trapfall-mcp, retention, metrics still use raw SqlitePool via Store::pool(). Phase 3 (feat(db): implement Postgres backend (Phase 3) #168) will migrate them when Postgres forces it.
• Store::with_backend(Arc<dyn Database>) ready for Phase 2 connection factory (feat(db): connection factory + env config + feature flags (Phase 2) #167).

Awaiting review/merge.