Aggregation Query Builder

List saved/named query definitions for an organization

{
  "data": [
    {
      "id": "qry-001",
      "org_id": "org-bufdir-42",
      "query_type": "activity_count",
      "period_id": "rp-2024-q4",
      "activity_type": "peer_support_group",
      "created_at": "2025-01-04T10:00:00Z"
    },
    {
      "id": "qry-002",
      "org_id": "org-bufdir-42",
      "query_type": "participant_sum",
      "period_id": "rp-2024-q4",
      "activity_type": null,
      "created_at": "2025-01-04T10:05:00Z"
    }
  ],
  "pagination": {
    "page": 1,
    "limit": 20,
    "total": 8
  }
}

Get a specific query definition and its last result

{
  "id": "qry-001",
  "org_id": "org-bufdir-42",
  "query_type": "activity_count",
  "period_id": "rp-2024-q4",
  "activity_type": "peer_support_group",
  "sql_preview": "SELECT COUNT(*) FROM activities WHERE org_id = 'org-bufdir-42' AND activity_type = 'peer_support_group' AND period_id = 'rp-2024-q4'",
  "last_result": {
    "count": 82
  },
  "last_executed_at": "2025-01-05T08:01:00Z"
}

Build and execute an aggregation query

{
  "org_id": "org-bufdir-42",
  "period_id": "rp-2025-q1",
  "query_type": "hours_delivered",
  "activity_type": null
}

{
  "id": "qry-019",
  "org_id": "org-bufdir-42",
  "period_id": "rp-2025-q1",
  "query_type": "hours_delivered",
  "result": {
    "total_hours": 1840.5
  },
  "executed_at": "2025-03-26T09:00:00Z",
  "duration_ms": 185
}

Re-execute an existing query with updated parameters

{
  "period_id": "rp-2025-q1",
  "activity_type": "family_workshop"
}

{
  "id": "qry-001",
  "org_id": "org-bufdir-42",
  "query_type": "activity_count",
  "period_id": "rp-2025-q1",
  "activity_type": "family_workshop",
  "result": {
    "count": 27
  },
  "executed_at": "2025-03-26T09:15:00Z"
}

Delete a saved query definition

{
  "deleted": true,
  "id": "qry-001"
}

Build and return a geographic breakdown query result

{
  "org_id": "org-bufdir-42",
  "period_id": "rp-2025-q1",
  "query_type": "geographic_breakdown",
  "result": [
    {
      "region": "Oslo",
      "participant_count": 204
    },
    {
      "region": "Viken",
      "participant_count": 312
    }
  ],
  "executed_at": "2025-03-26T09:01:00Z"
}

{
  "contract_summary": {
    "total_contracts": 410,
    "total_endpoints": 2416,
    "api_styles_used": [
      "rest"
    ]
  },
  "generated_at": "2026-03-26T06:55:53.316Z"
}