Projection Best Practices

Always declare explicit fields: with SQL types

This is the single most common source of projection bugs. If fields: is omitted, the compiler infers everything as TEXT. Boolean comparisons break silently. Numeric aggregates produce string results. Queries using typed operators return incorrect results or fail.

# Bad — everything inferred as TEXT
fields: ~
 
# Good — explicit types for every column
fields:
  entity_id:        TEXT
  artist_name:      TEXT
  popularity_score: BIGINT
  deleted:          BOOLEAN
  updated_at:       BIGINT

If you discover that a column was inferred as TEXT but should be BIGINT, fix the fields: declaration and rebuild the projection.

Choose primary keys carefully

The primary key cannot be changed without dropping the table and rebuilding. Treat it as immutable.

Rules for choosing a good primary key:

• Always use entity_id as part of the PK for entity-scoped projections
• Use composite PKs for relationship projections: [user_id, artist_id], [show_id, user_id]
• Use a time bucket field as part of the PK for time-windowed projections: [entity_id, bucket_start]
• Never use mutable fields as part of the PK — artist names change, entity IDs don’t

If you find yourself needing to change the primary key, treat it as a schema migration: create a new projection with the correct PK, backfill from events, swap queries to the new projection, drop the old one.

Keep projections focused: one projection per query shape

A projection should reflect the shape of one specific query or a small family of closely related queries. Projections that try to serve every possible query become wide tables with too many columns, complex derive expressions, and conflicting mutation behaviors.

Prefer multiple narrow projections over one wide projection:

# Prefer this — focused, one query shape each
projections:
  artist_leaderboard:        # for ranked artist lists
    ...
  artist_search:             # for search-by-name queries
    ...
  artist_genre_counts:       # for genre aggregate queries
    ...

Wide projections also amplify the blast radius of a failure. If one projection fails, only the queries that depend on it are affected.

Index every column in a where clause

Every column that appears in a where condition in any named query must have a corresponding index in the projection. Without it, the query executes as a full-table scan.

projections:
  artist_leaderboard:
    ...
    indexes:
      - columns: [genre]                   # for: where genre = ?
      - columns: [popularity_score]        # for: where popularity_score >= ?
        direction: desc
      - columns: [updated_at]             # for: where updated_at > ?
 
queries:
  artists_by_genre:
    where:
      - { column: genre, op: eq, param: genre }              # covered by genre index
      - { column: popularity_score, op: gte, param: min }    # covered by popularity_score index

Note: Indexes add write overhead per UPSERT. Don’t index columns that are never filtered on. Index exactly what your queries need.

Never ignore projection failures silently

Treat projection failures as incidents. Any status: open failure in a production projection deserves investigation. Set up:

1. Alerting — Any new entry in causet.projection-dlq.v1 triggers a PagerDuty alert
2. Health checks — Monitor /actuator/health and alert on DEGRADED or DOWN
3. Metrics — Alert on projection_failures_total > 0 in a 5-minute window
4. Dashboard — Per-projection failure count and last successful event timestamp visible at all times

The fastest way to erode trust in a data platform is to let projection failures accumulate silently until users report incorrect data.

Validate before every deploy

Make deployment gates part of your deploy pipeline. causet build validate catches undeclared projection fields:, bad source_events, and other DSL-level mismatches at compile time — run it, and don’t deploy if it fails:

# In your deploy script
causet build validate --runtime . || {
  echo "Projection validation failed. Aborting deploy."
  exit 1
}
 
causet doctor

causet build validate catches undeclared projection fields:, bad source_events, and other DSL-level mismatches before they reach production; causet doctor confirms your CLI, context, and runtime/query/cloud connectivity are healthy before you deploy.

Alert on projection lag

A sudden increase in lag without a corresponding increase in event volume indicates a projection is failing or the worker is overloaded. Investigate immediately.

Document rebuild procedures for critical projections

For every projection that powers a user-facing query, write and maintain a rebuild runbook:

## Rebuilding artist_leaderboard
 
1. Truncate: `TRUNCATE TABLE {schema}.artist_leaderboard;`
2. Reset offset: `kafka-consumer-groups.sh ... --to-earliest`
3. Restart worker: `kubectl rollout restart deployment/causet-projection-worker`
4. Monitor lag: `kafka-consumer-groups.sh ... --describe`
5. Expected rebuild time: ~12 minutes (at 2026-06-25 event volume)
6. Verify: query artist_by_id for known artist; assert popularity_score matches ledger

Document expected rebuild time based on current event volume. Re-measure after major volume changes.

Schema evolution: additive changes only

Projection schemas evolve. Follow these rules:

The core rule: only additive changes. Removal and renaming require a new projection, a migration of query definitions, and a controlled transition.

Test against multiple forks in CI

Multi-tenant projections can behave differently per fork if fork-specific data is involved. Your CI integration tests should:

1. Use an isolated fork per CI run (e.g., fork: ci-{git-sha})
2. Clean up forks after tests complete
3. Verify that tenant isolation holds: a row written in fork A should not be visible in fork B

def test_tenant_isolation():
    fork_a = deploy_test_fork("fork-a")
    fork_b = deploy_test_fork("fork-b")
 
    submit_intent(fork_a, {"type": "UpdateArtist", "entity_id": "artist_1", ...})
    wait_for_lag_zero(fork=fork_a)
 
    # artist_1 must not appear in fork_b
    result = query(fork=fork_b, query="artist_by_id", params={"entity_id": "artist_1"})
    assert len(result) == 0