Defining Projections

Projections are declared in .causet files under the projections: key. The causet-compiler compiles them into the causet.projections.json IR artifact, which drives both DDL (table creation) and handler logic in the projection worker.

Full schema

projections:
  artist_leaderboard:
    source_events: [ArtistUpdated, CheckinRecorded]
    target:
      table: artist_leaderboard
      primary_key: [entity_id]
    fields:
      entity_id:        TEXT
      artist_name:      TEXT
      genre:            TEXT
      popularity_score: BIGINT
      deleted:          BOOLEAN
      updated_at:       BIGINT
    derive:
      entity_id:        event.artist_id
      artist_name:      event.info.artist_name
      popularity_score: event.ranking.popularity_score
      updated_at:       event.ts
    aggregates:
      CheckinRecorded:
        checkin_count: { op: add, by: 1, floor: 0 }
    mutations:
      ArtistUpdated:  { op: upsert }
      ArtistDeleted:  { op: delete }
    bucket:
      field: event.ts
      interval: "24h"
    indexes:
      - columns: [genre]
      - columns: [popularity_score]
        direction: desc

`source_events`

A list of event types that trigger updates to this projection. Only events whose type matches an entry in this list will be routed to this projection’s handler.

source_events: [ArtistUpdated, CheckinRecorded, ArtistDeleted]

You can have any number of source events. Each can have its own mutation operation and aggregate rules.

`target`

Declares the destination PostgreSQL table and the primary key.

target:
  table: artist_leaderboard
  primary_key: [entity_id]

table — the name of the PostgreSQL table. Created automatically from the IR at deploy time.

primary_key — one or more columns forming the primary key. Must match columns declared in fields. Use a composite PK when the projection represents a relationship (e.g., [user_id, artist_id]).

Warning: Changing the primary key after initial deploy requires a table drop and full rebuild. Choose your PK carefully.

`fields`

Declares every column in the target table with an explicit SQL type. This is required.

fields:
  entity_id:        TEXT
  artist_name:      TEXT
  genre:            TEXT
  popularity_score: BIGINT
  deleted:          BOOLEAN
  updated_at:       BIGINT

Supported SQL types

Type	Use for
`TEXT`	Strings, IDs, enum values
`BIGINT`	Timestamps (epoch ms), large integers, counters
`INT`	Small integers
`BOOLEAN`	Flags, soft-delete markers
`DECIMAL`	Monetary values, scores with decimals
`TIMESTAMP`	PostgreSQL timestamps (use `BIGINT` for epoch ms instead)

Warning: If you omit fields:, the compiler infers all columns as TEXT. This silently breaks BOOLEAN comparisons, BIGINT arithmetic, and any query using typed operators. Always declare fields: explicitly.

Reserved field names

The following names are reserved by the event envelope and cannot be used as payload field names or column names in projections:

type
ts
entity_id

Using any of these as a payload field name will produce a compiler error.

`derive`

Maps event payload fields to projection columns. Expressions reference the event payload via event. prefix.

derive:
  entity_id:        event.artist_id
  artist_name:      event.info.artist_name
  popularity_score: event.ranking.popularity_score
  updated_at:       event.ts

Derive expressions are evaluated per event at handler time. Nested field access uses dot notation. If a referenced field is absent in the event payload, the handler will fail — see Failure Handling.

You do not need to derive every column. Aggregate columns (managed by the aggregates block) are updated by the runtime, not by derive.

`mutations`

Declares the per-event-type operation to apply to the projection table.

mutations:
  ArtistUpdated:  { op: upsert }
  ArtistDeleted:  { op: delete }

Operation	Behavior
`upsert`	INSERT or UPDATE the row identified by the primary key
`delete`	DELETE the row identified by the primary key
`replace`	DELETE then INSERT (use when you need to clear aggregate columns on update)

If a source_event type has no explicit mutation declared, it defaults to upsert.

`aggregates`

Declares counter and sum operations per event type. These accumulate into columns across multiple events for the same primary key.

aggregates:
  CheckinRecorded:
    checkin_count: { op: add, by: 1, floor: 0 }
  LikeRecorded:
    like_count: { op: add, by: 1, floor: 0 }
  LikeRemoved:
    like_count: { op: add, by: -1, floor: 0 }

Aggregate operators

Op	Behavior
`add`	Adds `by` to the current column value
`set`	Sets the column to `by` unconditionally

floor — the minimum value the column will be clamped to. Use floor: 0 to prevent negative counts.

Aggregate columns must be declared in fields: with an appropriate numeric type (BIGINT or INT). Their initial value on first insert is 0 unless overridden.

`bucket`

Groups events into time windows, producing one row per entity per time bucket.

bucket:
  field: event.ts
  interval: "24h"

field — the event field to use as the bucket timestamp (usually event.ts).

interval — the window size. Supported values: "1h", "6h", "24h", "7d", "30d".

With bucketing, the primary key implicitly includes the bucket boundary, so each entity has one row per time window rather than a single row.

`indexes`

Declares secondary indexes to create on the projection table.

indexes:
  - columns: [genre]
  - columns: [popularity_score]
    direction: desc
  - columns: [artist_name, genre]

columns — one or more columns in the index.

direction — asc (default) or desc. Only meaningful for single-column indexes used in ordered queries.

Note: Index every column that appears in a where clause in your named queries. Missing indexes cause full-table scans, which degrade under load and produce slow queries.

Multiple event types

A single projection can respond differently to each source event type:

projections:
  user_concert_history:
    source_events: [TicketPurchased, EventCancelled, EventAttended]
    target:
      table: user_concert_history
      primary_key: [user_id, event_id]
    fields:
      user_id:      TEXT
      event_id:     TEXT
      event_name:   TEXT
      venue_name:   TEXT
      attended:     BOOLEAN
      cancelled:    BOOLEAN
      purchased_at: BIGINT
    derive:
      user_id:      event.user_id
      event_id:     event.event_id
      event_name:   event.event_name
      venue_name:   event.venue_name
      purchased_at: event.ts
    mutations:
      TicketPurchased: { op: upsert }
      EventCancelled:  { op: upsert }
      EventAttended:   { op: upsert }

Each event type can set different derived fields. Fields not referenced in derive for a given event type retain their current value (upsert only updates what the derive block provides).

Show attendance aggregate example

projections:
  show_attendance:
    source_events: [CheckinRecorded, CheckinRevoked]
    target:
      table: show_attendance
      primary_key: [show_id]
    fields:
      show_id:       TEXT
      venue_id:      TEXT
      checkin_count: BIGINT
    derive:
      show_id:   event.show_id
      venue_id:  event.venue_id
    aggregates:
      CheckinRecorded:
        checkin_count: { op: add, by: 1, floor: 0 }
      CheckinRevoked:
        checkin_count: { op: add, by: -1, floor: 0 }
    mutations:
      CheckinRecorded: { op: upsert }
      CheckinRevoked:  { op: upsert }
    indexes:
      - columns: [venue_id]

checkin_count increments on CheckinRecorded and decrements on CheckinRevoked, clamped to 0 by floor: 0. The venue_id index supports queries that filter by venue.

Concept Partitioning