Query your data

POST /v1/query answers a typed question about your workspace's data: revenue per month, the products viewed most this week, your best buyers among subscribers. You describe what you want in JSON; Postforge compiles it to one SQL query, scoped to your workspace, runs it read-only and answers with the rows, the definitions it used and the SQL it ran.

There is no raw SQL interface. A query in this language uses the same definitions every time (an order counts as an order the way it does everywhere in Postforge), never adds two currencies together and can't reach another workspace's data.

What you can ask aboutCopy Anchor Link

Every object in the catalog: profile, customer, order, line_item, event, product, product_variant, coupon, coupon_code and store. The catalog lists each object's fields and says which can be filtered and which can be grouped. Read it first.

Profiles have more:

• customFields.<key>: a custom field, by its key.
• traits.<key>: a computed trait. The built-in ones are ordersCount, firstOrderAt, lastOrderAt, and the amounts totalSpent and averageOrderValue, which name their currency (traits.totalSpent:EUR). Your own computed traits are named by their id (traits.trait_Ab12Cd34).

Events have properties.<path> for anything in their payload (properties.product_id).

A list of recordsCopy Anchor Link

{
  "object": "order",
  "fields": ["number", "placedAt", "total"],
  "where": [{ "field": "financialStatus", "test": { "operator": "is", "value": "paid" } }],
  "sort": [{ "by": "placedAt", "dir": "desc" }],
  "limit": 50
}

Amounts come in minor units with their currency beside them (total and totalCurrency). Filters use the same tests as segments: text (is, contains, is_one_of…), numbers (gte, between…), dates (in_last_days, before…), yes/no and lists.

Only some people's recordsCopy Anchor Link

profiles takes conditions in the language of segments: only records that belong to people who meet them. It works for profiles and for everything that belongs to a person (customers, orders, line items, events, coupon codes).

{
  "object": "order",
  "fields": ["number", "total"],
  "profiles": { "type": "segment_membership", "segmentId": "seg_RZcSpS7k", "value": true }
}

Reports: groups and aggregatesCopy Anchor Link

Group by fields, and dates by day, week, month or year (in UTC). Aggregate with count, sum, avg, min, max and distinct_count.

{
  "object": "order",
  "groupBy": ["placedAt:month"],
  "aggregates": [
    { "fn": "count", "as": "orders" },
    { "fn": "sum", "field": "total", "as": "revenue" }
  ],
  "sort": [{ "by": "placedAt:month" }]
}

{
  "rows": [
    { "placedAt:month": "2026-09-01T00:00:00.000Z", "orders": 412, "revenue": 2104500, "currency": "EUR" },
    { "placedAt:month": "2026-09-01T00:00:00.000Z", "orders": 37, "revenue": 198000, "currency": "USD" }
  ],
  "status": "complete",
  "definitions": [],
  "sql": { "text": "select …", "params": ["…"] }
}

Money is always grouped by its currency: a sum over orders in euros and dollars gives one row per currency, never one number.

The answerCopy Anchor Link

• rows: the fields, or the groups and aggregates, by name.
• status: complete when these are all the rows, truncated when there were more than limit (at most 1,000), not_run for a dry run.
• definitions: the segments, computed traits, custom fields and lists the query used.
• sql: the SQL it compiled to, with its parameters. It's there to read; nothing runs it but Postforge.

Set dryRun: true to get the SQL without running the query.

Content in a localeCopy Anchor Link

A product's and a variant's content (title, price, tags…) is kept per locale and never falls back to another one. A query that reads it names the locale:

{ "object": "product", "fields": ["title", "stock"], "locale": "nl-NL" }

LimitsCopy Anchor Link

• A query runs for at most 60 seconds. One that runs out of time answers 422 query_too_slow: narrow it with a shorter window, more filters or fewer groups.
• At most 1,000 rows, 50 fields, 20 filters, 5 groups and 10 aggregates.
• It runs read-only, as a database role that only sees your workspace's rows: a guard behind the compiler, which scopes every query already.
• Your key needs the read scope.