Choose the capture boundary
Begin with the business objects required by the consuming workflow. Identify their source tables, stable keys, ownership and permitted fields. Log based capture can observe committed changes without repeatedly scanning every row, but it still consumes source resources and depends on database configuration. A connector must receive only the privileges it needs.
Separate a row update from a business fact. A single invoice transition may update several tables in one transaction. Publishing each row as an independent decision trigger can expose incomplete context. Preserve transaction information where available, then assemble an explicit business event at a documented boundary.
Coordinate the snapshot and change stream
An initial snapshot establishes existing state while a change stream describes subsequent work. Define how the connector obtains a consistent snapshot, records its source position and reconciles changes occurring during the scan. The exact mechanism depends on the connector and database; a timestamp alone is usually insufficient proof of a gap free handoff.
Test a large table, concurrent writes and an interrupted snapshot before approving the approach. Record which tables completed, the checkpoint format and the restart behavior. Snapshot records should be distinguishable from later changes when downstream processing gives those records different operational meaning.
Keep source positions separate from business identity
A source position tells the capture process where to resume. An entity key tells a consumer which business object changed. An event identifier lets it detect repeated delivery. Preserve all three concepts rather than using a database log position as a universal business identifier.
Checkpoint only after the chosen durable handoff succeeds. PostgreSQL documents that a logical slot can emit recent changes again after a crash. Consumers therefore need duplicate handling even when the connector appears to advance monotonically. Treat offsets as opaque unless the database documentation defines a safe comparison.
{
"entity": "invoice",
"entityId": "inv_1042",
"operation": "update",
"sourcePosition": "opaque-checkpoint",
"transactionId": "tx_81"
}Receiving contract · invoice inv_1042
- First delivery
change_81Receipt and invoice projection committed together.- Replay after restart
change_81The durable receipt identifies a repeat; the effect is not applied again.
Define a stable identity for each change and how long to retain it. A transaction ID or resume token alone may not uniquely identify an event.
Make deletes and schema changes visible
Define whether a delete removes a projection, expires a relationship or creates a retained audit record. A missing row in a later snapshot is not always equivalent to an explicit delete event. Some databases require replica identity configuration to include the key or previous values needed downstream.
Plan column additions, renames and type changes as contract changes. Avoid forwarding unrestricted source records into every destination. A transformation boundary can remove sensitive fields and version a stable event shape while keeping source metadata available for diagnosis.
Recover from an interrupted capture
Monitor capture lag, retained logs, snapshot progress and the age of the last durable checkpoint. PostgreSQL replication slots can retain required WAL when a consumer stops, which makes storage growth an operational concern. Establish an alert and a reviewed recovery procedure before that pressure becomes urgent.
Exercise connector restarts, unavailable destinations and an expired source log position. If a new snapshot becomes necessary, define how consumers handle duplicate records and reconcile the recovered data with the source.