Query Best Practices

Design queries for projection shapes

Each query should map to a projection (or join of projections) you declared for that read pattern. If a query needs a column that no projection provides, add a projection first — don’t work around missing data in application code.

Always set a limit

Unbounded queries on large tables will time out. Use limit on every production query. Pass limit as an input param when callers need pagination.

Acknowledge eventual consistency

Projection tables lag behind the ledger. If a client submits an action and immediately queries a projection, the result may not reflect the write.

Options:

• Return enough data from the action response that the client doesn’t need an immediate query
• Include updated_at / event.ts derived timestamps so clients can detect staleness
• Use the entity read API when strong consistency is required

Common mistakes

Using fields: - count: col instead of aggregate:. Inline aggregate specs in fields: are silently dropped. Always use group_by + aggregate:.

Missing group_by on aggregate queries. Every aggregate query needs group_by or results will be wrong.

Not using coalesce_zero on count queries. A count for a user with no records returns an empty result set, not { count: 0 }.

Querying immediately after a write. Design for eventual consistency or use entity reads for strong consistency.

Caching freshness-sensitive data. Inventory, balances, and per-user status queries usually should not use cache_ttl_seconds.

Next steps

• Examples — reference query catalog
• Projections — shape tables for your queries
• Intents — the write path that feeds projections