# Analytics Architecture

# Stats Storage

Every time a video's statistics are fetched (from any flow — start tracking, nightly refresh, blogger batch), a new row is inserted into video_statistics:

-- video_statistics schema
video_id          UUID    -- FK → videos.id
external_video_id TEXT    -- Platform video ID
views             BIGINT
likes             BIGINT
comments          BIGINT
reposts           BIGINT
saves             BIGINT
created_at        TIMESTAMPTZ

This is an append-only table — each fetch creates a new snapshot, not an update.

# Materialized Views

Two materialized views compute incremental analytics from the raw snapshots:

# `mv_video_daily_increments`

Computes the delta between consecutive video_statistics rows within a day:

views_delta, likes_delta, comments_delta, reposts_delta
Grouped by external_video_id + date

# `mv_video_weekly_increments`

Aggregates daily increments into weekly summaries.

# `videos_with_statistics_fast`

A fast read view that joins videos with the latest video_statistics row per video. Used heavily by the dashboard list and Google Sheets export.

# Data Flow

graph TB
    A[RapidAPI fetch] -->|queue-processor| B[video_statistics INSERT]
    B --> C[mv_video_daily_increments]
    C --> D[mv_video_weekly_increments]
    B --> E[videos_with_statistics_fast]

    F[Dashboard API] -->|reads| E
    F -->|reads| C
    F -->|reads| D

    G[Google Sheets export] -->|reads| E

# Refresh Schedule

Materialized views are refreshed periodically by the materialized-views-refresh worker (or equivalent scheduled function). The refresh is triggered after significant write batches to keep data current without refreshing on every insert.

# Totals vs Increments

The system supports both modes:

Mode	Description	Source
Totals	Absolute counts at last fetch	`video_statistics` latest row
Daily increments	Growth per day	`mv_video_daily_increments`
Weekly increments	Growth per week	`mv_video_weekly_increments`

This distinction matters for campaign analytics — a video added mid-campaign shows only the increment from when tracking started, not historical totals.