feat: Add PostgreSQL / TimescaleDB for persistent event storage

[notyorch]

Overview

Currently, events and alerts are stored ephemerally (likely SQLite under /app/data). This means all detection history is lost on container restart, making it impossible to run historical queries, trend analysis, or compliance reports.

Problem

• No durable event store → no alert history after restart
• SQLite doesn't scale under concurrent writes from collector + manager
• TimescaleDB would enable time-series queries natively (e.g. "anomalies in last 7 days")

Proposed Solution

Replace the current ephemeral data volume with PostgreSQL (or TimescaleDB as a drop-in extension) as the primary event store.

Changes Required

• Add tup-postgres service to docker-compose.yml with a named volume
• Add tup-postgres healthcheck before tup-manager starts
• Update tup-manager to connect via DATABASE_URL env var
• Add DB migration system (e.g. Alembic for Python, or raw SQL scripts in /scripts/migrations/)
• Update .env.example with POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB
• Enable TimescaleDB extension for the events table (optional but recommended)

Schema Suggestion

CREATE TABLE events (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  timestamp   TIMESTAMPTZ NOT NULL,
  model_id    TEXT,
  event_type  TEXT,
  severity    TEXT,
  payload     JSONB,
  detected_by TEXT
);
-- If using TimescaleDB:
SELECT create_hypertable('events', 'timestamp');

Acceptance Criteria

• docker compose up spins up Postgres alongside other services
• Events survive a full docker compose restart
• Manager health endpoint returns DB connection status
• README updated with DB configuration section

Priority

🔴 High — blocking for any real production or demo use case