Postgres read models (query side)

Chronacta remains the source of truth. JSON projections under data/projections/ stay for lightweight ops. Postgres is an external read model owned by the query/UI team.

Ownership

Artifact	Owner	Location
Domain events	Domain/backend	Schema Registry + publishers
Postgres table schema	Read model team	YAML read-model schema
DDL / DML SQL	Generator from YAML	migrations/*.sql + runtime statements
Event → column mapping	Read model team	source: fields in YAML
JSON projections	Ops / Chronacta CLI	data/projections/

Chronacta does not create Postgres DDL at runtime and does not invent table schemas.

Flow

schema.yaml  →  chronacta sqlgen generate ddl  →  migrations/*.sql  →  apply
             →  runtime DML from YAML          →  postgres projector → UPSERT
Chronacta durable subscription ───────────────→  postgres projector → ACK after commit

YAML schema

See examples/postgres-projector/schema.yaml. Columns declare source paths such as:

• event.event_id
• event.event_type
• event.stream_id
• event.global_position
• event.created_at
• event.data / event.data.<field>
• event.metadata.<key>

SQL generator

./bin/chronacta sqlgen generate ddl \
  -schema examples/postgres-projector/schema.yaml \
  -out examples/postgres-projector/migrations

./bin/chronacta sqlgen generate dml \
  -schema examples/postgres-projector/schema.yaml \
  -out examples/postgres-projector/generated

Review generated DDL before applying. Runtime does not run CREATE TABLE.

Projector

Use chronacta-connector with handler go:PostgresReadModel and Postgres settings in the job config (see examples/postgres-projector/connector.yaml).

Delivery semantics:

1. Pull event from durable subscription
2. BEGIN
3. Insert into processed_events (ON CONFLICT DO NOTHING)
4. If inserted: execute generated UPSERTs + checkpoint
5. COMMIT then ACK
6. On Postgres error: do not ACK (Nack / retry)

At-least-once; consumers must tolerate redelivery via event_id idempotency.

Rebuild / schema evolution

1. Edit schema.yaml
2. Regenerate DDL (sqlgen generate ddl) — v1 prefers new migration or full rebuild, not auto-ALTER
3. Stop projector
4. Apply migration
5. On breaking change: TRUNCATE domain tables (+ optional processed_events), reset subscription checkpoint / replay
6. Start projector and wait for lag to clear

JSON Chronacta projections are unchanged by this process.

Health and metrics

Projector logs event_id, stream_id, global_position, event_type, table name. Do not log payloads or secrets by default.

Operational checks:

• Chronacta reachable
• Postgres ping
• Subscription lag
• Duplicate skips vs upsert errors

Related

• Projections (JSON ops)
• Connector
• Durable subscriptions