Projections

Projections are read-only views that combine data from multiple aggregates into a single response. Your frontend needs an order with customer name, product details, and shipping info -- that's four aggregates. Without projections, you either make 4 API calls or build a custom endpoint. Projections solve this: define the shape you need in JSON, and the platform composes it for you.

When to use projections

Good for: - Dashboards combining multiple data sources - API responses needing denormalized data - Complex queries that would need multiple round-trips

Not for: - Simple single-aggregate reads (use GET directly) - Write operations (projections are read-only)

Defining projections

Projections are deployed via the admin API. Define them in JSON:

{
  "name": "order_with_details",
  "for_each": "order",
  "sources": {
    "order": "$key",
    "customer": {"type": "customer", "id": "$.order.customer_id"},
    "products": {"type": "product", "id": "$.order.line_items[*].product_id", "mode": "array"}
  },
  "include": {
    "pricing": "pricing_rules:global"
  },
  "select": {
    "order_id": "$.order.id",
    "total": "$.order.total",
    "customer_name": "$.customer.name",
    "tax_rate": "$.pricing.default_tax_rate",
    "items": {
      "$map": "$.order.line_items",
      "as": "$item",
      "to": {
        "product_id": "$.item.product_id",
        "product_name": {
          "$lookup": "$.products",
          "where": {"id": "$.item.product_id"},
          "select": "$.name"
        }
      }
    }
  }
}

Fields

Field	Required	Purpose
name	Yes	Projection identifier (lowercase, alphanumeric with underscores)
for_each	Yes	Trigger aggregate type -- one projection instance per aggregate of this type (bare streams only by default)
include_substreams	No	If true, also include tagged UUID variants (e.g., ledger:uuid:2025). Default: false.
sources	Yes	Aggregates derived from the trigger's state tree
include	No	Global singletons via type:global syntax
fan_out	No	Emit one row per element of a collection instead of one row per aggregate. See Fan-out projections.
select	Yes	Output shape with JSONPath expressions or typed column objects

These seven are the whole vocabulary. A definition carrying any other top-level field is rejected at deploy, naming the field — a definition that deployed and quietly ignored half of what it asked for would still rebuild the table and report success.

Sources

Sources define which aggregates to fetch. The first source can use "$key" shorthand to reference the trigger aggregate:

"sources": {
  "order": "$key",
  "customer": {"type": "customer", "id": "$.order.customer_id"}
}

Source options

Option	Default	Description
type	Required	Aggregate type to fetch
id	Required	ID expression ($key, literal, or JSONPath)
mode	"single"	"single" or "array" for fetching multiple

Array mode

Use mode: "array" when the ID path returns multiple values:

"products": {
  "type": "product",
  "id": "$.order.line_items[*].product_id",
  "mode": "array"
}

This fetches all products referenced by the order's line items.

Include

The include field is for global singleton aggregates that are not in the trigger's state tree:

"include": {
  "config": "settings:global",
  "pricing": "pricing_rules:global"
}

include is restricted to :global IDs to guide users toward proper patterns. For non-global aggregates, use sources with appropriate ID resolution.

Select expressions

The select field defines the output shape using JSONPath expressions.

Paths in select are not handler paths

This is the one thing to get right. A select path is evaluated as plain JSONPath against the resolved sources and include bindings. It is not the path language used by handlers, implications, and sagas, even though the two look identical and live in the same spec file.

The practical differences:

	Handlers / implications / sagas	Projection select
$.foo.bar	Resolved	Resolved
$.foo.bar? (optional suffix)	Optional — operation becomes a no-op when absent	Rejected at deploy time
@.* (state prefix)	Current aggregate state	Not available — name the source instead ($.order.status)
$.data.*, $.metadata.*	Event payload and metadata	Not available — projections read aggregate state, not events
Path resolves to nothing	Error, or no-op with ?	Column is null, row is still emitted

The ? optional suffix is a handler feature. It is not valid JSONPath, so a projection path ending in ? never resolves — the column would read null for every row, including rows where the field is present. Deploying such a path is rejected:

projection 'order_summary', column 'memo': '?' is not valid in a projection path.
Optional paths are a handler feature; see /docs/guides/projections#paths-in-select-are-not-handler-paths

Any select path that fails to parse as JSONPath is rejected the same way. Before this check existed, a malformed path silently produced a permanently-null column.

To project a field that only some events set, default it in the handler and then overwrite it, so the field always exists:

[
  { "set": { "target": "memo", "value": "" } },
  { "set": { "target": "memo", "value": "$.data.memo?" } }
]

Then use a plain path in the projection: "memo": "$.order.memo".

Missing paths produce null, not a missing row

A column whose path doesn't resolve is null. The row is still emitted and the other columns are unaffected.

This makes schema evolution cheap: adding a column for a field only newer events set is safe and needs no backfill. Aggregates that predate the field read null; everything else is unaffected. Conversely, it means a null column is not evidence that a row failed — see Consistency before concluding a projection is broken.

Basic paths

Reference any source or include binding:

"select": {
  "customer_name": "$.customer.name",
  "order_total": "$.order.total"
}

$map transform

Map over arrays to transform each element:

"items": {
  "$map": "$.order.line_items",
  "as": "$item",
  "to": {
    "name": "$.item.name",
    "qty": "$.item.quantity"
  }
}

as names the binding with a $ prefix ("$item"), but paths that read it are ordinary JSONPath against the bindings map — "$.item.name", with the dot. The dotless "$item.name" is rejected at deploy time, because it is not a path and would otherwise be stored as the literal text "$item.name".

$lookup transform

Find and extract from arrays:

"product_name": {
  "$lookup": "$.products",
  "where": {"id": "$.item.product_id"},
  "select": "$.name"
}

where values are resolved the same way, so a binding reference needs the dot form. A literal comparison value is written plainly: "where": {"role": "lead"}.

Both $lookup and typed path entries accept an optional "type" annotation for SQL materialization:

"price": {
  "$lookup": "$.products",
  "where": {"id": "$.item.product_id"},
  "select": "$.price",
  "type": "real"
}

Fan-out projections

By default a projection emits exactly one row per for_each aggregate. fan_out emits one row per element of a collection on that aggregate instead — the standard read model for a many-to-many relationship.

The case it exists for: an org aggregate holds members as a map of user_id → role. The membership rules belong on the org, because deciding whether an actor may change a member's role means comparing ranks across the whole set — a single-aggregate invariant. But the common query runs the other way: which orgs is this user in? Without fan-out that's a full scan of every org.

{
  "name": "org_members",
  "for_each": "org",
  "sources": { "org": "$key" },
  "fan_out": { "path": "$.org.members", "as": "$m" },
  "select": {
    "org_id":   { "path": "$id",        "type": "text" },
    "org_name": { "path": "$.org.name", "type": "text" },
    "user_id":  { "path": "$.m.key",   "type": "text" },
    "role":     { "path": "$.m.value", "type": "text" }
  }
}

SELECT org_id, org_name, role FROM org_members
WHERE user_id = ?1 AND role IS NOT NULL

One query over one narrow table, instead of folding every org to find the ones a user belongs to. Note that user_id itself is not indexed — see What is indexed.

Fan-out fields

Field	Required	Purpose
path	Yes	JSONPath to the collection. Must resolve to an object or an array.
as	Yes	Binding name for the current element, $-prefixed.

The binding exposes two fields, whether the collection is a map or an array:

Binding	Map source	Array source
$.m.key	The map key	The array index, as a string ("0", "1", …)
$.m.value	The value at that key	The element

Use $.m.value.field to reach into an element that is itself an object. Paths outside the binding ($.org.name, $key) resolve normally and repeat on every row.

Note the dot: as names the binding "$m", but paths that read it are ordinary JSONPath against the bindings — $.m.key, not $m.key. The dotless form is rejected at deploy.

Aggregate identity in select

Three bare paths are available alongside ordinary JSONPath, matching the vocabulary handlers and sagas use:

Path	Resolves to	Example
$key	The full aggregate key	org:a1b2c3…
$id	The bare aggregate ID — the same value as the automatic _key_id column	a1b2c3…
$fan_key	The current fan-out element's key; null without fan_out	alice

Anything else beginning with $ followed by a letter or underscore is a mistyped binding reference and is rejected — write $.m.key, not $m.key.

Row identity

A fan-out projection's rows are identified by (_key_id, _fan_key), not by _key_id alone. Both are present as columns:

Column	Contents
_key_id	The for_each aggregate ID — repeated across every row from that aggregate
_fan_key	The map key or array index for this row

_fan_key is only present on fan-out projections. Everything that assumes one row per _key_id has to account for this:

• GET /_projections/:name/:id returns a JSON array of rows for a fan-out projection, not a single object. The array is ordered by _fan_key, and is empty when the aggregate's collection is empty.
• CSV export emits _key_id, _fan_key, then the select columns alphabetically.
• SQL queries need WHERE _key_id = ?1 AND _fan_key = ?2 to address a single row.

Removal and tombstones

When an aggregate changes, every row for that _key_id is deleted and rebuilt from the current collection. Elements that disappeared from the collection therefore disappear from the table. There is no incremental path that could leave a stale row behind.

Entries whose value is null are still emitted as rows, with null in the columns that read from them. This is deliberate: the common map-patch idiom keeps a removed key with a null value rather than deleting it, so the history stays legible, and skipping those rows would make the two representations of "removed" behave differently. Filter them in SQL:

WHERE role IS NOT NULL

If you want them gone from the table entirely, remove the key from the aggregate (remove_at) rather than nulling it.

Empty collections produce no rows

An aggregate whose collection is empty, missing, or not a collection contributes zero rows. A fan-out projection therefore cannot double as "the list of all orgs" — an org with no members is absent from org_members entirely. Keep a separate non-fan-out projection when you need the aggregate list.

Limits

• One fan_out per projection. Nested fan-out (a row per element of a collection inside a collection) is not supported; use $map to nest the inner collection into a json column.
• fan_out and $map are unrelated. $map transforms a collection into a JSON value inside one column of one row; fan_out changes how many rows the projection has.
• The 100,000-row CSV export limit and the 10,000-row SQL query cap count fanned-out rows, not aggregates.

API endpoints

Admin (console API, JWT authentication)

POST   /api/instances/:id/projections        # Deploy definition
GET    /api/instances/:id/projections        # List all
DELETE /api/instances/:id/projections/:name  # Remove

The deploy body wraps the definition under a projection key:

{
  "name": "order_summary",
  "environment": "prod",
  "projection": {
    "for_each": "order",
    "sources": {"order": "$key"},
    "select": {"total": "$.order.total"}
  }
}

The dashboard upload expects the same format — use the same JSON file you'd use with curl.

Data (API key authentication)

GET /_projections/:name/:id              # Read cached
GET /_projections/:name/:id?synchronous  # Compute fresh

Use ?synchronous when you need guaranteed fresh data. Otherwise, projections automatically refresh when any source aggregate changes — see Consistency.

For a fan-out projection, GET /_projections/:name/:id returns an array of rows ordered by _fan_key rather than a single object.

Example: User dashboard

{
  "name": "user_dashboard",
  "for_each": "user",
  "sources": {
    "user": "$key",
    "org": {"type": "organization", "id": "$.user.org_id"},
    "recent_orders": {"type": "order", "id": "$.user.recent_order_ids", "mode": "array"}
  },
  "include": {
    "features": "feature_flags:global"
  },
  "select": {
    "user_name": "$.user.name",
    "org_name": "$.org.name",
    "orders": "$.recent_orders",
    "dark_mode_enabled": "$.features.dark_mode"
  }
}

Request:

curl https://myapp.j17.dev/_projections/user_dashboard/abc123 \
  -H "Authorization: Bearer $API_KEY"

Response:

{
  "ok": true,
  "data": {
    "user_name": "Alice",
    "org_name": "Acme Corp",
    "orders": [],
    "dark_mode_enabled": true
  },
  "cached_at": 1706745600
}

Deployment example

# Deploy a projection (console API, JWT)
curl -X POST "https://console.j17.dev/api/instances/$INSTANCE_ID/projections" \
  -H "Authorization: Bearer $JWT" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "order_summary",
    "environment": "prod",
    "projection": {
      "for_each": "order",
      "sources": {
        "order": "$key",
        "customer": {"type": "customer", "id": "$.order.customer_id"}
      },
      "select": {
        "id": "$.order.id",
        "total": "$.order.total",
        "customer_name": "$.customer.name"
      }
    }
  }'

# Read a projection (API key)
curl https://myapp.j17.dev/_projections/order_summary/ord_12345 \
  -H "Authorization: Bearer $API_KEY"

Consistency

Projections are asynchronous. A background refresher notices that a source aggregate changed and re-materializes the affected rows shortly afterwards. A query issued immediately after a write may not see it yet.

This matters more than it sounds, because a row that hasn't materialised yet looks exactly like a row that was dropped. That ambiguity is the most common way people reach a wrong conclusion about projection behaviour — a short result gets read as "my path is broken" or "unresolvable paths drop rows" when it was only lag. When testing a projection, retry before concluding anything.

Two escapes when you need a guarantee:

• GET /_projections/:name/:id?synchronous recomputes that projection from live aggregate state and returns the fresh result.
• Read the aggregate directly with GET /:type/:id?synchronous=true when you need read-after-write on a single aggregate.

SQL queries against materialized tables are always served from the materialized data — there is no synchronous variant.

Don't gate an action on a projection

Reading a projection to decide whether to allow something can fail in either direction. A newly granted permission may not have materialized yet, incorrectly refusing access. A revoked permission may still appear in the table, incorrectly allowing it.

A projection describes a past state, not necessarily the current one. For authorization and other mutable conditions, verify the authoritative aggregate and enforce the condition when committing the action; a separate read alone does not prevent a concurrent change. Only an explicitly monotonic fact — one that cannot become false — can safely use a projected positive as a shortcut. Treat a projected negative for such a fact as unproven and confirm it against the aggregate before refusing.

Tests must exercise projection lag. Mocks that refresh projections inline can hide this behavior, but local tests using asynchronous materialization can reproduce it. Test both newly granted and revoked access while deliberately holding the projection behind the authoritative state.

Changing a definition rebuilds the table

Deploying a changed definition for an existing projection drops its table and recreates it, then re-materializes rows as the refresher works through the aggregates. Until it catches up, the table is empty or partial.

That is normal — a projection is a derived, disposable view, and rebuilding is the expected way to change one. But it means a definition change is not a zero-downtime operation for readers of that table. For a projection on a hot path, deploy the new shape under a new name, let it populate, then switch reads and delete the old one.

Adding, removing, or retyping a column all count as definition changes. So does changing sources, fan_out, or include.

Performance

Projections automatically cache and refresh when source data changes. No manual invalidation required.

Guidelines: - Keep to 3-4 sources max - Use array mode sparingly (each ID is a separate fetch) - For high-traffic projections, use the cached endpoint (default) rather than ?synchronous

What is indexed

A projection table gets exactly one index, and it is not configurable:

Projection	Index
Normal	_key_id — primary key
Fan-out	(_key_id, _fan_key) — composite primary key

Every other column is unindexed. A WHERE on one is a full scan of the table, which holds one row per aggregate — or per fanned-out element — not one per event. That is usually fine for operational queries, and it is the wrong shape for a hot single-row lookup by token, email or handle: those scan the whole table on every call. If a lookup like that is on your request path, cache the result rather than repeating the query.

_fan_key on its own is not indexed either. It is the second column of the composite, so WHERE _fan_key = ?1 without _key_id cannot use the index.

There is currently no way to declare additional indexes, and a definition that tries — an "indexes" key, or any other field outside the seven the vocabulary allows — is rejected at deploy rather than accepted and ignored.

Limitations

• No joins across instances
• No aggregation across all aggregates (use analytics export)
• Max 10 sources per projection
• One index per table, not configurable — see What is indexed
• include is restricted to :global IDs

Projections, and the read models you build yourself

Classical event sourcing builds read models by subscribing to the event stream and folding every event into a database you operate yourself. Built-in projections are that idea with the plumbing removed: declare the shape, and the platform maintains the table next to your data and answers SQL against it.

You can still do it the classical way, and nothing in j17 gets in the way. Materialize into Postgres, ClickHouse, Elasticsearch, DuckDB, a search index, a graph database — whatever your stack already runs. Two ways to feed it:

• Push — listeners POST matching events to your endpoint as they're written, signed and retried.
• Pull — GET /:type/:id/events pages forward from a cursor you keep, so a loop or a cron job can catch up from wherever it left off and replay from the beginning whenever you change the shape.

Events are the durable record, and a read-scoped API key reaches all of them. Projections are a convenience layered on top, not a gate in front of your data.

Built-in projections are, like Tick handlers, an 85% solution: they cover the common shapes without a second system to run, they live on the same node as the events so reads are fast and no network hop is involved, and they refresh themselves. When your read model fits, that's a lot of infrastructure you never have to think about. When it doesn't, drop to the stream and build exactly what you need — that isn't an escape hatch bolted on the side, it's the same thing every event-sourced system does.

Where the built-in ends

The boundary is worth knowing up front. A projection is derived from current aggregate state, not from the event stream — it's a function of the aggregate, which is itself the fold of the events. It can compute anything recoverable from current state, and nothing that isn't.

So path-dependent facts have no home in a projection unless the aggregate already carries them:

Question	Built-in projection	Your own consumer
"Which orgs is this user in?"	Yes — fan-out	—
"What is this order's total?"	Yes	—
"How many times has this been rejected?"	Only with a counter on the aggregate	Natural — count the events
"How long did this sit in pending?"	Only with a timestamp on the aggregate	Natural — read the transitions
"What did this look like in March?"	No	Fold the stream to that point
Full-text search, graph traversal, OLAP	No	The right tool for each

Reconstructing a historical view means replaying events in any event-sourced system — that part isn't a j17 limitation. The narrower difference is that a built-in projection can't accumulate history as it goes, so a fact you want continuously queryable either lives on the aggregate or lives in a read model you fold yourself.

Built-in projections are also not for analytics. For heavy cross-aggregate queries over large datasets, pipe events into a warehouse.

CSV Export

Download all rows of a projection as CSV:

curl https://myapp.j17.dev/_projections/order_summary/export.csv \
  -H "Authorization: Bearer $API_KEY" \
  -o orders.csv

Columns: _key_id (aggregate ID), then _fan_key on fan-out projections, then select keys alphabetically. Nested values are JSON-serialized. A trailer comment reports row counts for completeness verification.

• 100,000 row limit
• Subject to instance read rate limits
• Stale-cache rows omitted (reported in trailer)

SQL Queries

Projection data is automatically materialized into queryable tables. Query them with read-only SQL:

curl -X POST https://myapp.j17.dev/_projections/query \
  -H "Authorization: Bearer $API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT name, email FROM user_summary WHERE status = ?1 ORDER BY name LIMIT 50",
    "params": ["active"]
  }'

{
  "ok": true,
  "columns": ["name", "email"],
  "rows": [["Alice", "alice@example.com"], ["Bob", "bob@example.com"]],
  "types": ["text", "text"],
  "truncated": false,
  "row_limit": 10000
}

Each row is a positional array aligned with columns (and types). columns and types are always present — including when the query matches no rows — so you can read a projection's shape from an empty result.

Truncation

Results are capped at row_limit rows. truncated is true when the query had more rows to give than were returned.

Check it before treating a result as complete. A query that returns exactly row_limit rows looks identical to one that returned everything, so without this flag a clipped result reads as a total — a SUM over a truncated set is simply a wrong number, with nothing in the response to say so.

The cap applies to the rows the query produces, so it also bounds a query whose own LIMIT is larger, absent, or bound to a negative value. For bulk reads use the CSV export, which streams.

Column types

Every projection table has two metadata columns plus your select columns:

Column	SQLite type	types value	Notes
_key_id	TEXT	"text"	The aggregate ID. Unique per row except on fan-out projections, where it repeats. When include_substreams is true, tagged variants appear as separate rows with the full ID including the tag (e.g., abc123...:2025).
_fan_key	TEXT	"text"	Fan-out projections only. The map key or array index this row came from. Together with _key_id it identifies the row.
_updated_at	INTEGER	"integer"	Unix seconds when the row was last upserted — returned as a JSON number
unannotated select columns	TEXT	"text"	Default for plain string paths
annotated select columns	per declaration	declared type	See type annotations below

Type annotations

By default all user columns are TEXT. To use a native SQLite type, replace the plain path string with an object:

"select": {
  "name":      "$.user.name",
  "age":       {"path": "$.user.age",    "type": "integer"},
  "score":     {"path": "$.user.score",  "type": "real"},
  "active":    {"path": "$.user.active", "type": "boolean"},
  "tags":      {"path": "$.user.tags",   "type": "json"}
}

Valid types:

Type	SQLite affinity	types value	Notes
text	TEXT	"text"	Default. to_string() coercion.
integer	INTEGER	"integer"	Numeric comparisons and ORDER BY work correctly. Values that can't parse as integer are stored NULL.
real	REAL	"real"	Same as integer for floats.
boolean	INTEGER	"boolean"	Stored as 0/1. Truthy: true, "true", 1. Falsy: false, "false", 0. Anything else → NULL.
json	TEXT	"json"	Maps and arrays JSON-encoded. Signals the client to JSON.parse(). $map columns default to this type.

The types array in the query response is parallel to columns — one entry per selected column. Use it to decode the columns your client needs:

{
  "ok": true,
  "columns": ["name", "tags", "age"],
  "rows": [
    ["Alice", "[\"admin\",\"billing\"]", 32],
    ["Bob",   "[\"viewer\"]",            28]
  ],
  "types": ["text", "json", "integer"]
}

tags arrives as the string "[\"admin\",\"billing\"]" — parse it with JSON.parse() / Jason.decode!(). age arrives as a JSON number (no parsing needed). An unannotated date field arrives as whatever string the source event used (typically ISO 8601).

Range queries on text columns

Text columns sort lexicographically. That's chronological for two common shapes:

• ISO 8601 strings ("2026-05-04T13:22:11Z") sort chronologically by string comparison: WHERE started_at >= '2026-05-01T00:00:00Z'
• Fixed-width Unix seconds ("1714521600") sort numerically because all values have the same digit count for any timestamp from 2001 to 2286

If you control the event shape, prefer ISO 8601 — it's the default JSON Schema format: "date-time" representation and stays human-readable in query results.

The _updated_at column is INTEGER, so it takes numeric comparison directly: WHERE _updated_at >= 1714521600.

Other restrictions

SELECT only, no semicolons, a hard 10,000-row cap (not a default — your own larger LIMIT does not raise it), 5-second timeout. Subject to instance read rate limits.

Not for OLAP: For analytical queries across large datasets, pipe events into ClickHouse, DuckDB, or BigQuery. SQL queries cover operational needs — active users, orders by status, recent activity.

Substreams and tagged UUIDs

By default, for_each only matches bare aggregate streams — ledger:uuid, not ledger:uuid:2025. Set "include_substreams": true to include tagged variants:

{
  "name": "ledger_summary",
  "for_each": "ledger",
  "include_substreams": true,
  "sources": { "ledger": "$key" },
  "select": { "total": "$.ledger.total", "period": "$.ledger.period" }
}

Each tagged variant becomes its own row. Filter by period in SQL: WHERE _key_id LIKE '%:2025'.

When to use include_substreams: the events in the substream share the same aggregate type, the same schema, and the same handlers as the base stream. A yearly ledger partition (ledger:uuid:2026) that carries forward balances and posts entries is the right use case.

When not to use include_substreams: if you're tagging to store related-but-different data under a user's ID — a webhook log, an audit trail, an inbox — those events have different schemas and likely no meaningful handlers. That's not a substream, that's a separate aggregate type. Use a deterministic v5 UUID instead:

// Stable ID derived from the user — no colons, genuinely separate type
const emailLogId = uuidv5(userId, EMAIL_LOG_NAMESPACE)
await j17.post(`email_log/${emailLogId}/webhook_received`, { ... })

This keeps the user projection clean and gives the email_log type its own spec, schema, and handlers.

See also

• Spec reference - Projection definition syntax
• JSONPath reference - The handler path language, and how it differs from select paths
• Caching guide - How caching interacts with projections