How Session Statistics Analytics Are Calculated in AgentsView: SQLite vs Go Implementation

AgentsView calculates session statistics analytics through a dual-path architecture that uses optimized SQLite queries for simple filters and a pure-Go aggregation engine for complex time-of-day or model-specific filtering, returning comprehensive metrics including totals, averages, medians, percentiles, and project concentration ratios.

The analytics API in the kenn-io/agentsview repository gathers session-level statistics through the internal/db/analytics.go file. The core entry point (*DB).GetAnalyticsSummary processes an AnalyticsFilter to return high-level metrics across matching sessions, implementing both a high-performance SQL path and a flexible Go-based fallback.

Analytics Filter Construction and Session Pre-filtering

The calculation begins with AnalyticsFilter, a struct that encapsulates all query parameters including date ranges, machine IDs, projects, agents, models, timezones, and hour-of-day or day-of-week constraints.

The filter system uses two primary helpers:

• buildWhere and buildWhereWithDate generate the SQL WHERE clause and argument slice for the base query (internal/db/analytics.go#L10-L28).
• When time-of-day filters are active, filteredSessionIDs (or filteredSessionIDsModel for model-specific queries) pre-filters sessions that satisfy hour and day constraints before aggregation begins (internal/db/analytics.go#L88-L100).

This pre-filtering ensures that subsequent calculations only operate on relevant session rows, regardless of whether the SQLite or Go path is taken.

Dual-Path Calculation: SQLite Fast-Path vs Go Fallback

GetAnalyticsSummary implements a performance optimization that attempts a pure-SQLite query first, falling back to Go only when necessary (internal/db/analytics.go#L70-L76).

The SQLite fast-path activates when:

• The filter can be expressed entirely in SQLite via f.canUseSQLiteTimeSQL().
• No model-specific filtering is required.

If either condition fails, the function switches to getAnalyticsSummaryGo (internal/db/analytics.go#L121-L124), which loads matching sessions into memory and performs aggregation using Go standard library functions.

SQLite Aggregation Query Deep Dive

When conditions permit, the SQLite path executes a sophisticated Common Table Expression (CTE) query that computes all metrics in a single database round-trip.

The query structure consists of three CTEs:

1. filtered: Selects relevant sessions with timezone-adjusted local_date calculations.
2. ranked: Uses window functions (ROW_NUMBER and COUNT) to establish ordinal positions for percentile calculations.
3. project_totals: Aggregates message counts per project to identify the most active project and calculate concentration metrics.

WITH filtered AS (
    SELECT id, project, agent, message_count,
           total_output_tokens, has_total_output_tokens,
           <dateExpr> AS local_date
    FROM sessions
    WHERE <where>
),
ranked AS (
    SELECT message_count,
           ROW_NUMBER() OVER (ORDER BY message_count ASC) AS rn,
           COUNT(*) OVER () AS n
    FROM filtered
),
project_totals AS (
    SELECT project, SUM(message_count) AS messages
    FROM filtered
    GROUP BY project
)
SELECT
    COUNT(*)                         AS total_sessions,
    COALESCE(SUM(message_count),0)   AS total_messages,
    COALESCE(SUM(CASE WHEN has_total_output_tokens
                       THEN total_output_tokens ELSE 0 END),0) AS total_output_tokens,
    COALESCE(SUM(CASE WHEN has_total_output_tokens THEN 1 ELSE 0 END),0) AS token_reporting_sessions,
    COUNT(DISTINCT project)          AS active_projects,
    COUNT(DISTINCT local_date)       AS active_days,
    COALESCE(ROUND(AVG(message_count),1),0) AS avg_messages,
    -- median via two-row window
    COALESCE((
        SELECT CAST(AVG(message_count) AS INTEGER)
        FROM ranked
        WHERE rn IN (CAST(((n+1)/2) AS INTEGER),
                     CAST(((n+2)/2) AS INTEGER))
    ),0)                             AS median_messages,
    -- 90th percentile
    COALESCE((
        SELECT message_count
        FROM ranked
        WHERE rn = MIN(CAST(n*0.9 AS INTEGER)+1, n)
        LIMIT 1
    ),0)                             AS p90_messages,
    COALESCE((
        SELECT project
        FROM project_totals
        ORDER BY messages DESC, project ASC
        LIMIT 1
    ),'')                           AS most_active,
    COALESCE(ROUND((
        SELECT SUM(messages)
        FROM (SELECT messages FROM project_totals ORDER BY messages DESC LIMIT 3)
    ) * 1.0 / NULLIF(SUM(message_count),0), 3),0) AS concentration
FROM filtered;

Key calculation methods in the SQL path:

• Median: Averages the middle one or two rows from the ranked window using rn IN ((n+1)/2, (n+2)/2).
• P90: Selects the row at index ⌈0.9·n⌉ using WHERE rn = MIN(CAST(n*0.9 AS INTEGER)+1, n).
• Most active project: Orders project_totals by message count descending, then alphabetically by project name, taking LIMIT 1.
• Concentration: Calculates the proportion of messages contained in the top-3 projects by summing their counts and dividing by total messages, rounded to three decimal places.

Results are scanned directly into an AnalyticsSummary struct (internal/db/analytics.go#L43-L66).

Go Fallback Implementation for Complex Filters

When the SQLite path is unavailable (typically due to model filters or complex time-of-day constraints), getAnalyticsSummaryGo executes a four-stage aggregation process:

1. Session loading: Queries the database for sessions matching the generic filter (internal/db/analytics.go#L138-L143).
2. Model filtering: If a model filter exists, retrieves per-session message statistics via getAnalyticsFilteredMessageStats (internal/db/analytics.go#L144-L155).
3. Data construction: Iterates through rows to build a slice of sessionRow structs containing raw session data (internal/db/analytics.go#L151-L170).
4. Aggregation: Accumulates totals, per-project message counts, calendar day sets, and per-agent aggregates in memory (internal/db/analytics.go#L226-L244).

Derived metric calculations in Go:

• AvgMessages: Computes totalMessages / totalSessions as a float64, rounded to one decimal place (internal/db/analytics.go#L263-L267).
• MedianMessages: Sorts the message count slice and selects the middle value (or average of two middle values) (internal/db/analytics.go#L268-L274).
• P90Messages: Indexes into the sorted slice at position int(0.9 * n) (internal/db/analytics.go#L275-L279).
• MostActive: Iterates the projects map to find the highest message count, using lexical comparison for tie-breaking (internal/db/analytics.go#L281-L288).
• Concentration: Sums the top-3 project counts and divides by total messages, rounded to three decimals (internal/db/analytics.go#L290-L304).

The Go path also collects distinct model names from the selected sessions (internal/db/analytics.go#L245-L258), populating the optional models field in the result.

AnalyticsSummary Result Structure and API Response

The calculation returns an AnalyticsSummary struct (defined at internal/db/analytics.go#L44-L64) containing the following fields:

• total_sessions: Count of sessions matching the filter.
• total_messages: Sum of message_count across all matched sessions.
• total_output_tokens: Aggregated token usage from sessions reporting token data.
• token_reporting_sessions: Count of sessions that exposed token usage metrics.
• active_projects: Number of unique project identifiers.
• active_days: Distinct calendar days (timezone-adjusted) with activity.
• avg_messages: Mean messages per session (rounded to 0.1).
• median_messages: Median message count per session.
• p90_messages: 90th percentile of messages per session.
• most_active: Project identifier with highest message volume.
• concentration: Fraction of total messages contained in the top-3 projects (0.0-1.0).
• agents: Per-agent breakdown of session and message totals.
• models: Distinct model names used (optional, populated only in Go path).

The HTTP handler in internal/server/analytics.go exposes these statistics via the /api/analytics/summary endpoint. It parses query parameters into an AnalyticsFilter, invokes db.GetAnalyticsSummary, and returns the JSON payload. The frontend consumes these types through frontend/src/lib/api/types/analytics.ts:

// TypeScript client example
const response = await fetch('/api/analytics/summary?from=2024-01-01&to=2024-12-31');
const summary: AnalyticsSummary = await response.json();

console.log('Average messages per session:', summary.avg_messages);
console.log('Top project:', summary.most_active);
console.log('Concentration ratio:', summary.concentration);

Summary

• AgentsView implements session statistics analytics through a dual-path system in internal/db/analytics.go, optimizing for performance while maintaining correctness across all filter combinations.
• The SQLite fast-path computes totals, averages, medians, percentiles, and project concentration ratios using window functions and CTEs when filters are simple and model-agnostic.
• The Go fallback handles complex filters (model-specific or time-of-day) by loading sessions into memory and calculating statistics using sorted slices and map aggregations.
• Key metrics include median_messages (calculated via window functions or sorted indexing), p90_messages (90th percentile), concentration (top-3 project share), and most_active (winner-take-all project identification).
• The API surface at /api/analytics/summary bridges the database layer with the frontend TypeScript definitions, providing a type-safe contract for analytics consumption.

Frequently Asked Questions

What triggers the Go fallback instead of the SQLite fast-path?

The Go fallback activates when f.canUseSQLiteTimeSQL() returns false or when a model filter is present in the AnalyticsFilter. Complex time-of-day filters and model-specific queries require logic that cannot be expressed efficiently in SQLite window functions, forcing the system to load matching sessions into memory and perform aggregation using Go's standard library.

How does AgentsView calculate the median and 90th percentile for session messages?

In the SQLite path, the median uses a ranked CTE with ROW_NUMBER() to identify the middle row (or average of two middle rows for even counts), while the P90 selects the row at index ⌈0.9·n⌉. In the Go path, the code sorts the message count slice and indexes directly into the sorted array at positions n/2 for median and int(0.9*n) for P90.

What is the "concentration" metric in session statistics analytics?

Concentration measures message distribution inequality across projects, defined as the fraction of total messages contained within the top-3 most active projects. The value ranges from 0.0 to 1.0, with higher values indicating that activity is concentrated in fewer projects. The metric is calculated by summing the message counts of the top-3 projects and dividing by total_messages, rounded to three decimal places.

How are time-of-day filters handled when calculating session statistics?

Time-of-day filters first execute filteredSessionIDs (or filteredSessionIDsModel when model filtering is active) to pre-select session IDs that match the hour and day-of-week constraints (internal/db/analytics.go#L88-L100). These IDs are then used to constrain the main aggregation query. If the filter complexity prevents SQLite optimization, the Go path loads only these pre-filtered sessions before performing in-memory calculations.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}