Skip to content

Analytics: activity endpoint (GET /api/v1/analytics/activity) #76

Description

@nathanschmid08

Summary

Implement the activity endpoint: what has been happening in the application over time, derived from the usage_events log.

Motivation

Where the usage endpoint answers "what did this cost", activity answers "what has been going on" — how many completions ran, when agents and artifacts were created, which agents are actually being used. It is the data behind a dashboard or an activity timeline.

It reads the same table as the usage endpoint but asks a different question, which is why the two are separate endpoints and separate issues — they can be built in parallel by different people.

Endpoint

  • Method: GET
  • Route: /api/v1/analytics/activity
  • Request body: none

Query parameters

  • from and to — time range bounds over occurred_at
  • interval — time bucket granularity (hour, day, week, month)
  • filters: eventType, agentId, providerId, modelId

Response

200 with event counts over time, broken down by event type. The exact shape is part of this issue's scope.

Status codes

  • 200 — success, including an empty result when there is no data
  • 400 — invalid query parameters, unknown interval, or an inverted time range

Scope

  • Implement the handler in apps/api/src/modules/analytics/analytics.controller.ts and the query in analytics.service.ts.
  • Design the response contract, which the API design does not define. Decide and document:
    • whether this returns a time series of counts, a recent-events feed, or both. The name suggests a time series and the usage_events table is built for one; a feed of recent individual events is also plausible and much simpler. Pick one, and say why — this is the main design decision in the issue.
    • how counts are broken down by event_type within each bucket
    • whether overall totals accompany the series
    • whether zero-activity buckets appear. For a chart they must, or the timeline is misleading — which means generating the buckets rather than returning only what the query found.
  • Aggregate in SQL with COUNT and GROUP BY.
  • Support the time range with a documented default. "Last 30 days" is a sensible default for an activity view; unbounded is not.
  • Whitelist the interval values and map them to bucketing expressions. Never interpolate a client-supplied value into SQL.
  • Enforce a maximum bucket count, so hourly granularity over a year does not return nine thousand rows.

Out of Scope

  • The usage endpoint — a separate issue over the same table.
  • Recording usage events — a separate issue.
  • Token and cost aggregation. Those belong to the usage endpoint; this one counts events. A little overlap in the underlying query is fine, but do not duplicate the cost reporting.
  • Real-time or live-updating activity. This is a query, not a stream.
  • Charting. This returns data; the frontend draws.
  • A general-purpose event search API.
  • Retention or pruning of old events.

Technical Notes

  • idx_usage_events_occurred_at supports the time range filter, which is the main access path here. There is no index on event_type, and this endpoint groups by it — so a large table means a scan. Acceptable at the scale of a local application; if it proves otherwise, open a separate area:database issue rather than adding the index here.
  • event_type is unconstrained free text in the schema, with chat_completion, agent_created and artifact_created given as examples. The recording service issue defines the vocabulary as constants. Do not hard-code a different list here — if the vocabulary has landed, use it; if not, derive the types present from the data (SELECT DISTINCT) rather than inventing a parallel list that will drift.
  • All four foreign keys are ON DELETE SET NULL, so events survive the deletion of what they refer to. Filtering by agentId will therefore miss events from agents that have since been deleted — which is correct, but means a per-agent activity view silently loses history. Worth documenting, and worth deciding whether an "unattributed" bucket is surfaced.
  • occurred_at is a TEXT ISO-8601 timestamp. SQLite's date functions can bucket it; verify the format the recording service writes actually parses, and that lexicographic range comparison behaves as expected.
  • Bucketing has a timezone question: a "day" is not the same set of events in UTC as in the user's local time. Decide whether bucketing is UTC or client-supplied, and be consistent with whatever the usage endpoint chose — a dashboard showing both side by side with different day boundaries would be confusing.
  • Generating zero-activity buckets means producing the bucket series in the application and left-joining the counts onto it, or generating it in SQL with a recursive CTE. The first is simpler and easier to read.

Acceptance Criteria

  • The endpoint is implemented at the route above.
  • Event counts are aggregated over time buckets, broken down by event type.
  • The time series versus feed decision is implemented and justified.
  • Filtering by time range, event type, agent, provider and model works.
  • An unknown interval returns 400, not a SQL error.
  • An inverted time range returns 400.
  • Aggregation happens in SQL.
  • The zero-activity bucket decision is implemented and documented.
  • Event types come from the shared vocabulary or from the data, not from a hard-coded parallel list.
  • Timezone handling is documented and consistent with the usage endpoint.
  • A maximum bucket count is enforced.
  • An empty table returns 200 with an empty or zero-filled result, not an error.
  • The response contract is documented.
  • All SQL uses bound parameters.
  • npm run lint, npm run build and npm test pass in apps/api.

Testing

  • Seed a known set of events across several buckets and assert exact counts — hand-verifiable fixtures make this endpoint easy to trust.
  • Each interval granularity produces correct bucket boundaries.
  • Events falling exactly on a bucket boundary land in the expected bucket.
  • Zero-activity buckets behave as documented.
  • Each filter, and combinations.
  • Events with null references are handled per the documented decision.
  • An unknown interval returns 400; an injection attempt through it is rejected.
  • Inverted time range returns 400.
  • Empty table returns an empty result rather than an error.
  • A request exceeding the bucket limit is handled as documented.

Contribution Notes

  • Relevant files: apps/api/src/modules/analytics/analytics.controller.ts, analytics.service.ts, data/analytics/usage_events.sql, data/analytics/index.sql.
  • Independent of the usage recording service — seed usage_events rows directly in tests. That keeps this endpoint buildable today and the tests deterministic.
  • Independent of the usage endpoint, though they share a controller file. Coordinate only on the timezone decision, which should match.
  • The response contract decision is worth proposing in the PR before implementing — "activity" could reasonably mean two quite different things, and it is better to agree first.

Dependencies

Metadata

Metadata

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions