Projections

A projection is a PostgreSQL table maintained by the projection worker. It is populated by consuming events from Kafka and UPSERTing rows according to your declared derive expressions. Projections are your read models — the tables your queries run against.

Why projections exist

The write path (runtime + ledger) is optimized for correctness: per-entity ordering, deterministic rules, durable writes. It is not optimized for read patterns.

Projections bridge the gap. For every query shape you need, you declare a projection that materializes the data into the right structure. The projection worker maintains these tables asynchronously as events arrive.

Defining a projection

projections:
  artist_leaderboard:
    source_events: [ARTIST_FOLLOWED, USER_CHECKED_IN, ARTIST_UNFOLLOWED]
    target:
      table: artist_leaderboard
      primary_key: [artist_id]
    fields:                    # ALWAYS declare explicitly — see gotcha below
      artist_id:        TEXT
      artist_name:      TEXT
      genre:            TEXT
      follower_count:   BIGINT
      checkin_count:    BIGINT
      updated_at:       BIGINT
    derive:
      artist_id:    event.artist_id
      artist_name:  event.info.artist_name
      genre:        event.info.genre
      updated_at:   event.ts
    aggregates:
      ARTIST_FOLLOWED:
        follower_count: { op: add, by: 1, floor: 0 }
      ARTIST_UNFOLLOWED:
        follower_count: { op: sub, by: 1, floor: 0 }
      USER_CHECKED_IN:
        checkin_count: { op: add, by: 1, floor: 0 }
    mutations:
      ARTIST_FOLLOWED:   { op: upsert }
      ARTIST_UNFOLLOWED: { op: upsert }
      USER_CHECKED_IN:   { op: upsert }
    indexes:
      - columns: [genre]
      - columns: [follower_count]
        direction: desc

Fields reference

`source_events`

List of event types that trigger updates to this projection.

source_events: [ARTIST_FOLLOWED, USER_CHECKED_IN, ARTIST_UNFOLLOWED]

If an event arrives that is not in this list, it is ignored by this projection.

`target`

Target table name and primary key.

target:
  table: artist_leaderboard
  primary_key: [artist_id]          # composite: [user_id, artist_id]

Primary key choice is critical — changing it requires a full projection rebuild.

`fields`

Always declare fields: explicitly with SQL types.

fields:
  artist_id:        TEXT
  follower_count:   BIGINT          # NOT int — use BIGINT for counts
  is_verified:      BOOLEAN         # NOT boolean-as-string
  updated_at:       BIGINT          # NOT text — use BIGINT for timestamps

Warning: If you omit the fields: block, the compiler infers all column types from the first literal seen in derive:. A derive like is_verified: false without an explicit fields: block will infer is_verified: TEXT — then queries with { eq: false } will cast 'false' to integer and fail. Always declare fields: with explicit SQL types.

`derive`

Expression map from event fields to column values.

derive:
  artist_id:   event.artist_id     # payload field
  updated_at:  event.ts            # envelope timestamp (reserved)
  shard_id:    shard(event.artist_id, 16)  # built-in function

`mutations`

Per-event-type operation.

Op	Description
`upsert`	INSERT or UPDATE based on primary key (default)
`delete`	DELETE row matching primary key
`replace`	DELETE then INSERT (replace entire row)

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

`aggregates`

Increment/decrement counters or sums per event type.

aggregates:
  USER_CHECKED_IN:
    checkin_count: { op: add, by: 1, floor: 0 }
  TICKET_REFUNDED:
    revenue: { op: sub, by: event.amount, floor: 0 }

Aggregate ops: add, sub, set. floor sets the minimum value after the op.

`bucket`

Group events into time windows.

bucket:
  field: event.ts
  interval: "1h"    # 24h | 1h | 5m | 1d

Use with shard() for high-throughput aggregates when a single projection row receives very high write volume.

`indexes`

Explicit index declarations. Always index every column used in WHERE clauses.

indexes:
  - columns: [genre]
  - columns: [follower_count]
    direction: desc
  - columns: [user_id, artist_id]    # composite index

Eventual consistency

Projection tables lag behind writes. After a client submits an intent, the event flow is:

Intent → runtime → ledger (sync, milliseconds)
Ledger → Kafka publish (async, after response)
Kafka → projection worker (async, milliseconds to seconds)
Projection worker → UPSERT (async)

For most applications, the lag is imperceptible. Under high load it can grow to seconds. Monitor causet_projection_lag_seconds.

Idempotency

Projection UPSERTs are idempotent by design — the primary key handles duplicate processing. If the same event is processed twice, the row is simply upserted to the same values.

This is why Causet uses at-least-once delivery. Exactly-once delivery is not needed when handlers are idempotent.

Multi-tenant isolation

Each fork gets its own PostgreSQL schema:

{platformId}_{applicationId}_{forkId}

The projection worker sets SET LOCAL search_path = '{tenant_schema}' per transaction. There are no shared projection tables between tenants.

Failure handling

When a projection handler throws, the failure is captured with full context and retried. After max retries, the event goes to the DLQ. Failures are never silently discarded.

See Projection Failure Handling for the complete model.

Schema is IR-driven

Projection table DDL is applied from causet.projections.json at deploy time. There is no Flyway for projection tables.

Adding a column: add to fields: + derive: → recompile → deploy release → DDL applied.

Removing a column: columns are NOT automatically removed on deploy. Manual DDL required after ensuring no queries reference the column.

Changing primary key: requires a full projection rebuild (truncate + replay).

Common mistakes

Omitting fields: block. Results in all columns inferred as TEXT. Boolean and bigint queries will fail.

Forgetting to declare indexes. Every column in a WHERE clause should have an index. Missing indexes cause full table scans on high-volume projection tables.

Using reserved field names in event payloads. type, ts, and entity_id are always resolved from the event envelope, not the payload. Never name a payload field with these names.

Making the primary key too narrow. A primary key of just [user_id] means only one row per user. If you need one row per user per artist, the PK must be [user_id, artist_id].

Aggregate queries without group_by. A count query without group_by is silently dropped. Always pair aggregate queries with a matching group_by key.

Production notes

Design projection tables for your query shapes. Each query pattern may need its own projection.
Monitor projection lag. Growing lag means the worker is falling behind and read models are becoming stale.
Run causet build validate before every production deploy to catch DSL-level projection mismatches early.
Document the rebuild procedure for every critical projection before going to production.

Next steps

Defining Projections — syntax reference
Schema Versioning — safe vs breaking schema changes
Queries — named reads over projection tables
Best Practices — production-grade projection design

Examples Defining Projections