Query your events#

Use SQL to ask questions about one project's delivered events. The HTTP API, CLI, and MCP share the same project isolation and query limits. You write the SQL, or ask your agent to write it.

Before starting, connect your tools. You need events:read on the project. Listing projects also requires management:read.

Ask an agent#

With MCP connected, ask:

Find the project I want to analyze. Inspect its SQL schema and observed event names. Count each event by UTC day, and explain which data and identity fields you used.

The agent discovers operation contracts with porcupine_docs, then calls these operations through porcupine_call:

1. list-projects to resolve a name to a project ID.
2. get-event-schema to read columns, supported functions, and limits.
3. get-event-catalog to inspect observed event names and property keys.
4. query-events to execute SQL for that project.

The catalog samples delivered events. It helps discover what is present, but does not describe every historical property or explain your application's business meaning. The discovery reference describes its bounds.

Discover data from the CLI#

Set PORCUPINE_URL and sign in, then use the returned project ID:

porcupine list-projects
export PROJECT_ID=YOUR_PROJECT_ID
porcupine get-event-schema --id "$PROJECT_ID"
porcupine get-event-catalog --id "$PROJECT_ID"

Inspect an event name from the catalog to find its properties:

porcupine get-event-catalog --id "$PROJECT_ID" --event article_published

Replace article_published with a name from your project. The schema and catalog contain the field names an agent needs to build a query.

Run a query#

This query counts each event by UTC day:

porcupine query-events --id "$PROJECT_ID" --body '{"sql":"SELECT toDate(event_time) AS day, event, count() AS total FROM events GROUP BY day, event ORDER BY day, event LIMIT 100"}'

Rows appear under result.data. ClickHouse returns 64-bit integers, including counts, as strings to preserve precision. An empty array means the query found no delivered rows.

For a filtered query, save the following as query.json:

{
    "sql": "SELECT JSONExtractString(properties, 'category') AS category, count() AS total FROM events WHERE event={name:String} GROUP BY category ORDER BY total DESC LIMIT 100",
    "parameters": {"name": "article_published"}
}

Run it with:

porcupine query-events --id "$PROJECT_ID" --body @query.json

Replace the event name and property key with values observed in your project. Typed parameters keep values separate from SQL. The SQL reference lists supported syntax, parameter types, and fixed execution limits.

For the same request over HTTP, send query.json to POST /api/v1/projects/PROJECT_ID/query with an API-audience bearer credential. Through MCP, call query-events with path: {"id": "PROJECT_ID"} and the JSON as body. See the MCP request examples.

Check a recently sent event#

Queries use ClickHouse's result cache by default, with a 60-second TTL. Add "use_cache": false to the request body to bypass cache reads and writes. Fresh queries still read only events already delivered to ClickHouse.

The response's delivery.pending and delivery.pending_mutations report work awaiting projection. After sending an event, allow that work to drain before checking its count. See query consistency and cache behavior.

Count people or export more data#

Use person_id for the currently resolved identity and distinct_id for the actor recorded at capture. Daily cookieless identifiers cannot measure a person's return across days. Read identity and sessions before writing a retention or unique-user query.

For a large result, create a one-off export. To deliver results on a schedule, configure an S3 pipeline.