`projections` — Materialized Read Models

Projections subscribe to events and maintain PostgreSQL tables. They are the read side of Causet.

projections:
  user_concert_stats:
    description: "Per-user concert attendance stats"
    source_events:
      - SHOW_CHECK_IN_CREATED
    target:
      table: user_concert_stats
      primary_key: [user_id]
    fields:                              # always declare explicitly
      user_id:        TEXT
      shows_attended: BIGINT
      venues_visited: BIGINT
      last_checkin_at: BIGINT
    derive:
      user_id:        event.user_id
      last_checkin_at: event.ts
    mutations:
      SHOW_CHECK_IN_CREATED:
        op: upsert
        set:
          shows_attended: { aggregate: count }
          venues_visited: { aggregate: count_distinct, field: event.venue_id }
    indexes:
      - columns: [user_id]
      - columns: [last_checkin_at]
        direction: desc

Projection fields

Field	Required	Description
`source_events`	yes	List of event types that update this projection
`target.table`	yes	PostgreSQL table name
`target.primary_key`	yes	List of column names forming the PK
`fields`	strongly recommended	Explicit SQL column types
`derive`	yes	Expression map for column values
`mutations`	yes*	Per-event operation (`upsert`, `delete`, `replace`)
`aggregates`	yes*	Counter-style increments (`add`, `sub`, `set`)
`indexes`	no	Additional indexes
`bucket`	no	Time-window bucketing
`resolver`	no	Named entity lookups in derive expressions

*At least one of mutations or aggregates must be present.

SQL field types

TEXT · VARCHAR · STRING · INT · INTEGER · SMALLINT · BIGINT · DOUBLE · FLOAT · NUMERIC · DECIMAL · BOOLEAN · BOOL · TIMESTAMP · TIMESTAMPTZ · DATE · UUID · JSONB · JSON · GEOGRAPHY_POINT · GEOGRAPHY

🚫

Column type inference gotcha

If you omit fields:, the compiler infers every column type from the first literal it sees in derive:. A value like false infers TEXT, which breaks WHERE read = false queries (Postgres casts 'false' → integer → error).

Always declare fields: with explicit SQL types for any column that is boolean, bigint, or timestamp.

Mutation ops

Op	Behavior
`upsert`	INSERT … ON CONFLICT UPDATE (default)
`delete`	DELETE row matching primary key
`replace`	DELETE then INSERT

mutations:
  ARTIST_FOLLOWED:   { op: upsert }
  ARTIST_UNFOLLOWED: { op: delete }

Aggregate ops

Counter-style mutations using aggregates:

aggregates:
  SHOW_CHECK_IN_CREATED:
    shows_attended: { op: add, by: 1, floor: 0 }
    unique_venue_count:
      op: add
      by: 1
      floor: 0

Aggregate op	Description
`add`	Increment the column
`sub`	Decrement the column
`set`	Set to a specific value

floor: N sets the minimum value after the operation.

Alternatively, use inline aggregate syntax in mutations.set:

mutations:
  SHOW_CHECK_IN_CREATED:
    op: upsert
    set:
      shows_attended: { aggregate: count }
      unique_venues:  { aggregate: count_distinct, field: event.venue_id }

Derive expressions

Expression	Context	Description
`event.<field>`	derive	Event payload field
`event.ts`	derive	Event timestamp (epoch ms)
`event.entity_id`	derive	Routing entity ID
`event.payload.<field>`	derive	Backwards-compat alias for `event.<field>`
`shard(event.artist_id, 16)`	derive	Shard ID (0–15) for hot-aggregate sharding
`lookup(entity_type, id_expr).<field>`	derive	Cross-entity lookup

Bucketed projections

Use bucket: for time-window metrics:

projections:
  hourly_checkin_counts:
    source_events: [SHOW_CHECK_IN_CREATED]
    target:
      table: hourly_checkin_counts
      primary_key: [venue_id, bucket_start]
    fields:
      venue_id:     TEXT
      bucket_start: BIGINT
      checkin_count: BIGINT
    derive:
      venue_id:  event.venue_id
    aggregates:
      SHOW_CHECK_IN_CREATED:
        checkin_count: { op: add, by: 1, floor: 0 }
    bucket:
      field:    event.ts
      interval: "1h"          # 1h | 24h | 1d | 5m

Pair with a query that uses gte_window to filter by time window.

Indexes

indexes:
  - columns: [user_id]
  - columns: [last_checkin_at]
    direction: desc
  - columns: [location]
    type: GIST              # GIST for GEOGRAPHY_POINT columns
  - columns: [tags]
    type: GIN               # GIN for TEXT search

Entity resolver

Use resolver: to look up another entity’s state in derive: expressions:

projections:
  enriched_checkins:
    source_events: [SHOW_CHECK_IN_CREATED]
    resolver:
      show:
        entity: show
        key: event.show_id
    derive:
      title:   "show.title"
      user_id: event.user_id

actions queries

projections — Materialized Read Models