derivation-stateful-logic

derivation-stateful-logic

Estuary derivation that maintains internal SQLite tables to record state, and whose lambdas query and update that state to decide what to emit.

Prereq: read derivation-basics first — in particular the stateless-vs-stateful distinction and the role of migrations. This skill is the "stateful via SQLite tables" path.

Docs:

• https://docs.estuary.dev/getting-started/tutorials/derivations_acmebank/ — the canonical stateful tutorial (deposits, withdrawals, transfer approval) with the exact pattern this skill uses
• https://docs.estuary.dev/concepts/derivations/#internal-state — the concept page on internal task state

When to use this over alternatives

• Balance / account state: debit sender if funds available, reject otherwise
• Inventory: deduct stock, mark backordered if insufficient
• Approval workflows: accumulate approvers, emit "approved" once threshold met
• Deduplication with history: filter events by ID seen-before (beyond what collection-key uniqueness gives)
• Any business logic that depends on prior events for the same key

Reach for other skills when:

• Pure aggregations (sum/count/min/max) that reductions can express → derivation-aggregate-metrics
• Merging fields across sources → derivation-join-collections
• Time-bounded sliding state (last 24h) → derivation-windowing (which also uses SQLite internal state, but with readDelay)
• Stateless per-doc transforms → derivation-filter-transform

How it works (one paragraph)

You declare SQLite migrations in the derive.using.sqlite.migrations list — SQL that runs once at startup to create your state tables. Your lambda then queries and updates those tables as it processes each source document, and can SELECT a derived output row or not, depending on state. Each task shard owns its own SQLite database; events are routed to shards via a keyed shuffle, so all events for the same entity (e.g. the same account) reach the same shard and see the same state.