Analytics: Daily Rollup for site_daily_stats

Task ID: f3ae01b2-00b7-4b8c-a977-dc6a02becaeb Layer: [Analytics] Priority: medium

Problem

site_pageviews grows unboundedly. Trend queries (e.g. "how many visits last 30 days?") had to full-scan that raw table. No pre-aggregated daily summaries existed.

Solution

Implement a rollup pipeline that reads site_pageviews for a given UTC date and writes pre-aggregated metrics into site_daily_stats.

Tables

Both tables already exist in PostgreSQL:

site_pageviews — raw event log (path, page_type, referrer, session_id, status_code, response_time_ms, is_bot, created_at)
site_daily_stats — pre-aggregated: (date, metric, value, breakdown_key) PK

Metrics computed per day

metric	breakdown_key	description
total_views	`	non-bot page views
unique_sessions		distinct session_ids
bounce_rate		fraction of single-page sessions
avg_pages_per_session		total_views / unique_sessions
avg_response_ms		mean response latency
error_rate		fraction of 5xx responses
views_by_page_type	<page_type>	per-type view count
top_referrer	<domain>	top-20 referrer domains by count

API endpoints added (api.py`)`

POST /api/admin/daily-rollup?date=YYYY-MM-DD — roll up one date (default: yesterday)

POST /api/admin/daily-rollup?date=backfill — roll up all dates present in site_pageviews

GET /api/site/stats?days=30 — return pre-aggregated stats for the last N days


Scheduling
The rollup is designed to be called daily (e.g. via Orchestra cron or a systemd timer). Typical invocation:
POST /api/admin/daily-rollup   # rolls up yesterday by default
Upsert semantics mean re-running is idempotent.
Work Log
2026-04-06

Confirmed site_pageviews and site_daily_stats tables already exist in PostgreSQL

Implemented compute_site_daily_stats(date_str, db) helper in api.py (near line 8135)

Added POST /api/admin/daily-rollup endpoint with backfill support

Added GET /api/site/stats` endpoint for fast trend queries
Verified Python syntax OK

File: f3ae01b2_analytics_daily_rollup_spec.md

Modified: 2026-05-01 20:13

Size: 2.1 KB

Analytics: Daily Rollup for site_daily_stats