feat(db): implement Postgres backend (Phase 3)

[ajianaz]

Phase 3 — Postgres works end-to-end

Goal

Full PostgresBackend impl of Database trait. TrapFall runs on Postgres with feature parity to SQLite.

Tasks

• Create crates/trapfall-db/src/postgres.rs
• Write Postgres migration files (mirror SQLite schema with dialect adjustments)
  • ? → $1, $2, ... parameter binding
  • datetime('now') → NOW()
  • AUTOINCREMENT → SERIAL / BIGSERIAL
  • INSERT OR IGNORE → ON CONFLICT DO NOTHING
  • INSERT OR REPLACE → ON CONFLICT DO UPDATE
  • RANDOMBLOB() → gen_random_bytes()
  • LIKE → ILIKE for case-insensitive
  • sqlite_trigram → pg_trgm extension
• Impl all ~60 methods in PostgresBackend (mirror SqliteBackend logic)
• Add #[cfg(feature = "postgres")] guards
• Use compile-time query branching: #[cfg(feature = "sqlite")] vs #[cfg(feature = "postgres")] (Option B from plan)
• Setup Postgres in docker-compose for dev testing

Migration files structure

crates/trapfall-db/migrations/
├── sqlite/
│   ├── 001_initial.sql
│   ├── 002_alert_rules.sql
│   └── 003_drop_api_keys.sql
└── postgres/
    ├── 001_initial.sql
    ├── 002_alert_rules.sql
    └── 003_drop_api_keys.sql

Acceptance Criteria

• PostgresBackend implements all Database trait methods
• All integration tests pass on Postgres
• Docker compose with Postgres backend works end-to-end
• Ingest → digest → store pipeline works on Postgres
• Login/auth flow works on Postgres
• Search works on Postgres (with pg_trgm)

Effort

~2-3 days

Depends on

#166 (Phase 1), #167 (Phase 2)

Future enhancements (out of scope)

• JSONB for events.data (Phase: postgres-native)
• tsvector for full-text search (Phase: postgres-native)
• LISTEN/NOTIFY for WebSocket hub (Phase: postgres-native)
• pg_partman for time-based partitioning (Phase: postgres-native)