Rebuilding Projections

This example walks through rebuilding the user_concert_memory projection after fixing a bug in a derive expression.

Scenario

The user_concert_memory projection has a bug: venue_id is being stored as an empty string because the derive expression referenced event.venue (wrong field name) instead of event.venue_id.

The bug has been in production for two weeks. The projection table has incorrect data for all historical events.

Step 1: Identify the problem

import { createCausetClient } from '@causet/sdk-node';
 
const client = createCausetClient({
  apiUrl: process.env.CAUSET_API_URL ?? 'http://localhost:8085',
  platformSlug: 'jamlet',
  appSlug: 'concert-app',
  forkId: 'production',
  apiKey: process.env.CAUSET_API_KEY,
});
await client.init();
 
const { items } = await client.runQuery('my_concert_history', { user_id: 'user-1' });
// items[0].venue_id === "" — bug: derive used event.venue instead of event.venue_id
 
client.destroy();

# Check failure records — projection might be silently broken
psql $PROJECTIONS_DB_URL -c "
SELECT projection_name, error_message, count(*)
FROM projection_failures WHERE status = 'open'
GROUP BY 1, 2;
"
# No failures — it's a logic bug, not a handler error

Step 2: Fix the DSL

# Before (wrong):
derive:
  venue_id: event.venue    # ← wrong field name
 
# After (correct):
derive:
  venue_id: event.venue_id  # ← correct

Step 3: Compile and deploy

# Recompile
causet build compile --runtime concert-app --out build/concert-app-out
 
# Deploy new release via control plane
# Creates new IR version with the fix

Step 4: Truncate the projection table

The existing data is wrong. Truncate before rebuilding.

psql $PROJECTIONS_DB_URL -c "
SET search_path TO 'my_platform_concert_app_production';
TRUNCATE TABLE user_concert_memory;
"

Warning: This removes all data from the table until the rebuild catches up. Queries will return empty results during the rebuild. Consider using the zero-downtime pattern (new table + swap) for critical projections.

Step 5: Reset Kafka consumer group offset

# Stop projection worker first
docker stop causet-projection-worker
# (or ECS: set desired count to 0)
 
# Reset consumer group offset to beginning for the projection topic
kafka-consumer-groups.sh \
  --bootstrap-server localhost:9092 \
  --group causet-projection-worker \
  --reset-offsets \
  --topic causet.projection-events.v1 \
  --to-earliest \
  --execute

Step 6: Restart projection worker

docker start causet-projection-worker
# (or ECS: set desired count back to 1+)

Step 7: Monitor rebuild progress

# Watch consumer lag decrease to 0
watch -n 5 'kafka-consumer-groups.sh \
  --bootstrap-server localhost:9092 \
  --group causet-projection-worker \
  --describe | grep "causet.projection-events"'

Consumer lag starts at the total number of events in the topic and should drain to 0 as the rebuild completes.

Step 8: Verify correctness

import { createCausetClient } from '@causet/sdk-node';
 
const client = createCausetClient({
  apiUrl: process.env.CAUSET_API_URL ?? 'http://localhost:8085',
  platformSlug: 'jamlet',
  appSlug: 'concert-app',
  forkId: 'production',
  apiKey: process.env.CAUSET_API_KEY,
});
await client.init();
 
// After consumer lag = 0, verify the projection is correct
const { items } = await client.runQuery('my_concert_history', { user_id: 'user-1' });
// items should now have correct venue_id values
 
client.destroy();

Also verify row count — it should roughly match the number of CONCERT_MEMORY_CREATED events in the ledger:

# Ledger event count
psql $CAUSET_DB_URL -c "
SELECT count(*) FROM ledger_events
WHERE event_type = 'CONCERT_MEMORY_CREATED'
AND fork_id = 'production';
"
 
# Projection row count (by user)
psql $PROJECTIONS_DB_URL -c "
SET search_path TO 'my_platform_concert_app_production';
SELECT count(*) FROM user_concert_memory;
"

Zero-downtime alternative

For critical projections where zero query downtime is required:

Create a new projection with a different name:

projections:
  user_concert_memory_v2:    # new name
    # ... fixed derive expressions

Deploy and rebuild the new projection while the old one still serves traffic.

Once rebuilt, update queries to use the new table:

queries:
  my_concert_history:
    from: user_concert_memory_v2  # swap here

Deploy the query change.
Drop the old projection from the DSL after confirming the new one is correct.

Estimating rebuild time

Rebuild duration ≈ (total events in topic) × (avg UPSERT time per event)

For a topic with 5 million events at 500 events/second throughput:

Estimated rebuild: ~10,000 seconds (~2.8 hours)

Speed up by:

Scaling projection worker instances (more parallelism if multiple partitions)
Temporarily disabling Redis cache during rebuild
Running off-hours to reduce DB load

When to use full rebuild vs incremental

Situation	Approach
Logic bug in derive expression	Full rebuild (wrong data in all rows)
New column added	Partial rebuild or additive only (can add column with NULL, backfill separately)
New projection added	Full rebuild (no historical data yet)
Schema change (PK change)	Full rebuild required
Transient handler failure (DLQ)	Process DLQ only — no rebuild needed

Billing Events Roadmap