JSONB Analytics Snapshot Pattern

This skill provides the architectural pattern for using JSONB to store pre-aggregated analytics data, rather than relying on on-the-fly RDBMS calculations or complex star-schema table architectures.

The Core Concept

When building dashboards or analytics views, data is often grouped by multiple dimensions (e.g., status, type, template, date). If history is immutable (i.e. "what happened has happened"), recalculating these aggregations on every page load using GROUP BY and multiple JOINs is inefficient.

The JSONB Analytics Snapshot Pattern solves this by calculating the aggregations once for a specific time period (e.g., Daily, Weekly) and storing the complete structured result in a single JSONB column.

RDBMS vs JSONB for Aggregations

Why not create normalized RDBMS tables for the aggregated results (e.g., analytics_overview, analytics_by_status, analytics_by_template)?

1. Schema Flexibility: Dashboards change frequently. If you want to add a new chart (e.g., "Performance by User Role"), an RDBMS approach requires a database migration, new tables/columns, and ORM updates. With JSONB, you simply add a new key to the JSON payload.
2. Read Performance: A dashboard typically needs all these aggregations at once. Fetching from 5 different RDBMS aggregate tables requires 5 queries or complex joins. Fetching a single JSONB row provides the entire localized payload in one fast read.
3. Data Shape Match: The JSONB structure can exactly match the API response DTO mapped to the frontend charts, avoiding mapping boilerplate.