Reporting

The reporting system provides a single query endpoint for retrieving pre-aggregated analytics data. Rather than computing reports on-the-fly from raw tables, Siren maintains four pre-aggregated tables that are updated incrementally as events occur. These tables are derived from obligations and engagements. The query endpoint routes requests to the appropriate table based on the entity and dimensions requested.

Endpoints

All endpoints require authentication and are scoped to the current tenant (organization).

Query Reporting Data

POST /reporting/query

A unified query interface for all reporting data. The request specifies what entity to report on, which metrics to compute, how to group results (dimensions), and optional filters and date ranges.

The middleware chain authenticates the request (verifying JWT and permissions), then CollaboratorAliasResolverMiddleware resolves any collaborator aliases in filter values. If the authenticated user is a collaborator rather than an admin, AutoFilterByOwnerMiddleware injects a collaboratorId filter scoping results to their own data. Finally, ValidationMiddleware validates the required fields.

Request Body:

Parameter	Type	Required	Description
entity	string	Yes	The data entity to query. See Supported Entities below.
metrics	array	Yes	Array of metric expressions (e.g., ["sum:value"]).
dimensions	array	No	Array of dimension names for grouping results.
date_range	string or array	No	Date range specification. See Date Ranges below.
filters	array	No	Array of filter conditions. See Filter Syntax below.
order_by	array	No	Ordering specification.
limit	integer	No	Maximum number of result rows.

Example Response:

{
  "results": [
    {
      "dimensions": ["USD", "pending"],
      "metrics": [5000]
    },
    {
      "dimensions": ["USD", "approved"],
      "metrics": [12000]
    }
  ],
  "meta": {
    "entity": "obligations",
    "metrics": ["sum:value"],
    "dimensions": ["currency", "status"],
    "date_range": { "start": "2026-02-01", "end": "2026-02-28" },
    "total_rows": 2,
    "truncated": false
  }
}

Each result row contains a dimensions array (matching the requested dimension order) and a metrics array (matching the requested metric order). The meta object echoes the query parameters and includes the resolved date range.

Supported Entities

The entity field determines which pre-aggregated table is queried. The routing logic also considers the dimensions and metrics requested.

obligations (Balances)

Queried when entity is obligations and no time dimension is present. Routes to the collaborator balances table.

Returns the sum of obligation values grouped by currency and status.

Dimensions returned: [currency, status]

Example Request:

{
  "entity": "obligations",
  "metrics": ["sum:value"],
  "dimensions": ["currency", "status"],
  "filters": [["is", "collaboratorId", [42]]]
}

Example Response:

{
  "results": [
    { "dimensions": ["USD", "pending"], "metrics": [5000] },
    { "dimensions": ["USD", "approved"], "metrics": [12000] },
    { "dimensions": ["USD", "complete"], "metrics": [45000] }
  ]
}

Values are in cents (integer). Status values correspond to the obligation lifecycle: pending, approved, complete.

obligations (Earnings Over Time)

Queried when entity is obligations and a time dimension is present. Routes to the collaborator earnings periods table.

Returns earnings totals grouped by currency and time period.

Time dimensions:

Dimension	Period Type	Period Value Format
time:day	day	2026-04-06
time:month	month	2026-04

Dimensions returned: [currency, periodValue]

Example Request:

{
  "entity": "obligations",
  "metrics": ["sum:value"],
  "dimensions": ["currency", "time:month"],
  "date_range": "6mo",
  "filters": [["is", "collaboratorId", [42]]]
}

Example Response:

{
  "results": [
    { "dimensions": ["USD", "2025-11"], "metrics": [8000] },
    { "dimensions": ["USD", "2025-12"], "metrics": [12500] },
    { "dimensions": ["USD", "2026-01"], "metrics": [9200] }
  ],
  "meta": {
    "date_range": { "start": "2025-10-06", "end": "2026-04-06" }
  }
}

engagements (Activity Over Time)

Queried when entity is engagements. Routes to the collaborator activity periods table.

Returns engagement counts grouped by activity type and time period.

Dimensions returned: [activityType, periodValue]

Example Request:

{
  "entity": "engagements",
  "metrics": ["count"],
  "dimensions": ["activityType", "time:day"],
  "date_range": "30d",
  "filters": [["is", "collaboratorId", [42]]]
}

Example Response:

{
  "results": [
    { "dimensions": ["site_visit", "2026-03-15"], "metrics": [23] },
    { "dimensions": ["site_visit", "2026-03-16"], "metrics": [18] },
    { "dimensions": ["coupon_use", "2026-03-15"], "metrics": [3] }
  ]
}

obligations (Percentile Standing)

Queried when any metric starts with percentile:. Routes to the collaborator standings table.

Returns pre-computed percentile rankings. Currently defaults to monthly period.

Dimensions returned: [] (empty)

Example Request:

{
  "entity": "obligations",
  "metrics": ["percentile:earnings"],
  "filters": [["is", "collaboratorId", [42]]]
}

Example Response:

{
  "results": [
    { "dimensions": [], "metrics": [85] }
  ]
}

A percentile of 85 means the collaborator earns more than 85% of all collaborators.

Date Ranges

The date_range parameter supports multiple formats:

Preset Strings

Preset	Description
7d	Last 7 days.
30d	Last 30 days.
{N}d	Last N days.
month or this-month	Current calendar month.
last-month	Previous calendar month.
6mo	Last 6 months.
{N}mo	Last N months.
year or this-year	Current calendar year.
last-year	Previous calendar year.
previous:{preset}	The period before the named preset (e.g., previous:month).

Explicit Range

An array of two ISO 8601 date strings:

["2026-01-01", "2026-03-31"]

Comparison Mode

An object with current and previous keys (the endpoint resolves only the current range):

{
  "current": "month",
  "previous": "previous:month"
}

Filter Syntax

Filters are arrays of condition tuples: [operator, field, values].

is Operator

Matches exact values. With a single value, generates an = clause; with multiple values, generates an IN clause.

[["is", "collaboratorId", [42]]]

[["is", "currency", ["USD", "EUR"]]]

Allowed Filter Fields by Table

Not all filter fields apply to every pre-aggregated table. Filters referencing columns that do not exist on the target table are silently ignored.

Table	Allowed Filter Columns
Balances	collaboratorId (all columns accepted)
Earnings Periods	collaboratorId, currency, periodType, periodValue
Activity Periods	collaboratorId, activityType, periodType, periodValue
Standings	collaboratorId, currency, periodType, periodValue

Pre-Aggregated Tables

The reporting system maintains four tables, updated incrementally by event listeners:

Table	Updated By	Trigger Events
collaborator_balances	UpdateBalanceOnObligationChange	Obligation created/updated
collaborator_earnings_periods	UpdateEarningsPeriodOnObligationChange	Obligation created/updated
collaborator_activity_periods	UpdateActivityPeriodOnEngagementCreated	Engagement created
collaborator_standings	Batch computation	Periodic recalculation

This architecture ensures that reporting queries are fast (simple SELECT on pre-aggregated data) at the cost of slightly delayed data (updated asynchronously as events fire).

Access Control

The AutoFilterByOwnerMiddleware automatically restricts results based on the authenticated user’s role:

• Admin users: See data for all collaborators. Can filter by any collaboratorId.
• Collaborator users: Automatically filtered to their own data. The middleware injects a collaboratorId filter matching their collaborator record, overriding any client-supplied filter.

This means the same endpoint serves both the admin dashboard (seeing all collaborators) and the collaborator portal (seeing only their own data).

Relationship to Other Resources

• Obligations (upstream). The balances, earnings periods, and standings tables are all derived from obligation records. When an obligation is created or updated, event listeners incrementally update the corresponding pre-aggregated rows. Querying entity: "obligations" reads from these derived tables, not from the obligations table directly.
• Engagements (upstream). The activity periods table is derived from engagement records. Each time an engagement is created, UpdateActivityPeriodOnEngagementCreated increments the count for that collaborator, activity type, and time period.
• Pre-aggregated tables. Reporting never queries raw domain tables. The four pre-aggregated tables (collaborator_balances, collaborator_earnings_periods, collaborator_activity_periods, collaborator_standings) act as materialized views, trading write-time computation for read-time speed. This means reporting data may lag slightly behind the source records, but queries remain fast regardless of data volume.