spectrum

Spectrum is a self-hosted product analytics warehouse designed as an open-source alternative to commercial platforms like Amplitude and Mixpanel. The project demonstrates how a careful PostgreSQL-only architecture can handle event ingestion, sessionization, funnel analysis, cohort retention, revenue attribution, and A/B testing without requiring external message brokers or big-data frameworks. The system ingests raw clickstream events through a FastAPI endpoint, processes them via an idempotent ELT pipeline using `FOR UPDATE SKIP LOCKED` batch claims, and computes analytical constructs purely in SQL—including 30-minute session boundaries via window functions and weekly conversion funnels across account-level dimensions. Materialized views serve pre-aggregated metrics for Grafana dashboards while row-level security isolates tenant data. Key architectural patterns include a three-schema design separating staging, warehouse, and analytics layers; monthly range-partitioned fact tables with partition pruning; BRIN indexes on chronological event timestamps; a dead-letter queue for malformed payloads; and concurrent-safe pipeline execution that tolerates simultaneous runner instances without duplicate processing. The project ships with Alembic migrations, Prometheus metrics exposure, integration tests that assert exact funnel rates and retention isolation, and a benchmark script demonstrating partition-pruning speedups. Deployment relies on Podman containers orchestrating PostgreSQL, the API server, and Grafana, with a single `make demo` command provisioning everything, generating simulated user activity, running the transformation pipeline, and launching the visualization layer.