Alpaca Broker Reconciliation Idempotency

Alpaca — Reconciliation & Idempotency

This is the skill that separates a demo from production. Alpaca is an asynchronous, eventually-consistent system: writes settle later, events can be missed or replayed, some rails emit no events at all, and "executed" can still be reversed. Your job is to make your local database a faithful, self-healing mirror of Alpaca's state.

Read alpaca-broker-integration, alpaca-broker-sse-events, and the relevant domain skills first. This skill is the architecture that ties them together.

The core principle

Treat Alpaca as the source of truth and your DB as a cache that must converge to it. Every write is a request, not a fact. Every event is a hint, not a guarantee. Correctness comes from idempotent processing plus a reconciliation loop — never from assuming any single call or event succeeded exactly once.

1. Three layers of defense

Layer 1 — Idempotent writes      : never create a duplicate when you retry
Layer 2 — Idempotent event intake: never double-process a replayed/duplicate event
Layer 3 — Reconciliation sweep   : re-pull authoritative state and fix any drift

You need all three. Layer 1+2 keep you correct in the happy/retry case; Layer 3 catches everything that still slips through (downtime, bugs, missing events, corrections).

2. Layer 1 — Idempotent writes

Every money/order write must be safe to retry, because you can't tell a timeout apart from a success.

• Orders: set your own client_order_id (≤128 chars) derived from your transaction ID. On a lost response, look the order up via orders:by_client_order_id before retrying.
• Journals: send an Idempotency-Key header. Same key + same body returns the original journal; same key + different body → 422. (See alpaca-broker-journals.)
• Local-first ordering: write your intent row (with a generated key) before the network call, so a crash mid-call leaves a record you can reconcile — never an orphaned Alpaca object you can't find.
• Persist the returned Alpaca ID immediately (account_id, order_id, journal_id, transfer_id). It is your only correlation key for events and reconciliation.

3. Layer 2 — Idempotent event intake

Events are at-least-once: replay cursors, reconnects, and corrections all cause the same event to arrive more than once.

• Snapshot-first, keyed on event_id. Insert the raw event with upsert / skip-on-duplicate on event_id (a ULID). A duplicate becomes a no-op. This single unique constraint is your dedup boundary.
• Key business records on the Alpaca ID, not on a local autoincrement, with upsert semantics.
• Guard transitions by current status. Before acting, check the record isn't already terminal — so a duplicate "executed"/"filled" doesn't re-fire a payout or notification.
• Lock the row while mutating (SELECT … FOR UPDATE or your engine's equivalent) so concurrent events for one record serialize.
• Advance the cursor only after success (at-least-once, made safe by the above).

4. Layer 3 — Reconciliation / heal jobs

A scheduled job that re-pulls authoritative state from Alpaca and upserts it locally. This is what makes the system self-healing.

Canonical nightly heal (lesson):

1. For each active account, page through trade activities and non-trade activities (GET /v1/accounts/activities, paginated) for the last N days (e.g. 3) — a moving window that re-covers recent days so anything missed by SSE gets backfilled.
2. Page through journals (GET /v1/journals) and transfers for the same window.
3. Upsert each into your tables keyed on the Alpaca ID (insert-or-update). Re-running is safe and converges.
4. Bound concurrency (a small worker pool) and respect rate limits (alpaca-broker-rate-limits-resilience).

Why a window and not just "since last run": it absorbs corrections, late settlements, and any events dropped during a deploy — without rescanning all history every night.