> ## Documentation Index > Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt > Use this file to discover all available pages before exploring further. # SNAPTRADE POSITIONS RECONCILIATION # SnapTrade Positions — Soft-Delete Reconciliation **Last Updated:** 2026-07-08 **Introduced by:** PR #2407 (`fix/snaptrade-positions-soft-delete`) **Related Docs:** `snaptrade-integration.md` (overall architecture), `SNAPTRADE-ACCOUNTS-TABLE.md` (accounts schema), `SNAPTRADE-TRANSACTIONS.md` (activities feed) This document explains how we keep our stored copy of a user's brokerage positions (`snaptrade_account_positions`) in sync with what SnapTrade actually reports — and why that is harder than it sounds. *** ## Table of Contents 1. [The Problem](#the-problem) 2. [The Core Insight: Absence Is the Signal](#the-core-insight-absence-is-the-signal) 3. [History: Why Hard Delete + Count Guards Failed](#history-why-hard-delete--count-guards-failed) 4. [The Soft-Delete Model](#the-soft-delete-model) 5. [The Trust Gate](#the-trust-gate) 6. [Write Sites (Where Reconciliation Runs)](#write-sites-where-reconciliation-runs) 7. [Readers (Everything Must Filter `status = 'active'`)](#readers-everything-must-filter-status--active) 8. [Telemetry & Monitoring](#telemetry--monitoring) 9. [The Backfill Script](#the-backfill-script) 10. [Gotchas & Lessons Learned](#gotchas--lessons-learned) 11. [Deliberate Non-Goals & Follow-Ups](#deliberate-non-goals--follow-ups) *** ## The Problem We persist a copy of each SnapTrade account's positions so the dashboard, AI chat, net-worth functions, and daily snapshots don't have to call SnapTrade on every read. Any persisted copy can drift from reality, and positions drift in one specific, nasty way: **when a user sells or transfers out a position, SnapTrade simply stops returning it.** There is no "sold" record in the positions feed, no zero-unit row — the position just disappears from the response. If our sync doesn't actively remove rows that stopped being reported, they live in our DB forever as **ghost positions**: holdings the user no longer owns, still counted in their investments breakdown. Before this system shipped, production had accumulated **\~\$55.8M of ghosts across 8,139 rows / 716 accounts**. Note the blast radius: the headline net worth mostly *telescopes to the broker's reported account total*, so ghosts corrupt the holdings **breakdown** (what you own) more than the **total** (what it's worth). Both matter — the breakdown feeds the AI, the PFS export, and every "what do I hold?" answer. ## The Core Insight: Absence Is the Signal `getUserAccountPositions` returns the account's *current* holdings. So: > A symbol we have stored, that a **healthy** response no longer contains, > has left the account. The word doing all the work is *healthy*. A broker outage, a partial sync, a parse failure, or SnapTrade's "empty but HTTP 200" failure mode all look identical to "the user sold everything." The whole design revolves around distinguishing **trusted absence** (act on it) from **untrusted absence** (skip and wait). ## History: Why Hard Delete + Count Guards Failed The original implementation **hard-DELETEd** rows missing from the response. Because a wrong delete was irreversible, the sweep was wrapped in count-based safety guards: * `no_response_with_existing` — refuse to delete if the response was empty but we had existing rows (a real full liquidation could never be recorded). * `below_50pct_threshold` — refuse if the response contained fewer than 50% of the rows we had (a real consolidation/partial sale wave could never be recorded). The guards existed only because deletion was unrecoverable, and they blocked legitimate cleanup far more often than they caught outages — that's where the \$55.8M of ghosts came from. The fix was not smarter guards; it was making removal **reversible** so we could afford to trust the response. ## The Soft-Delete Model ### Schema (migration `20260708_01`) `snaptrade_account_positions` gained three columns: | Column | Meaning | | - | - | | `status` | `'active'` (currently held per the last trusted sync) or `'removed'` (soft-deleted). `NOT NULL DEFAULT 'active'`. | | `status_changed_at` | Stamped on status transitions (see gotcha below re: active rows). Cursor for a future hard-purge of long-removed rows. | | `removal_reason` | The **mechanism** that removed the row: `'reconciliation'` \| `'zero_quantity'` \| `'backfill'`. Not the business cause (sold vs transferred) — that's a follow-up fed by the transactions feed. | Two CHECK constraints enforce the contract at the DB layer: `status` must be one of the two values, and an `active` row carries no `removal_reason` while a `removed` row must carry a known reason **and** a `status_changed_at`. A composite index `(account_id, status)` serves the hot read path. `UNIQUE(account_id, symbol)` is unchanged and remains the upsert conflict target — a symbol has exactly one row that flips between states. ### Lifecycle ``` reported by trusted sync ┌────────────────────────────────────────┐ ▼ │ ┌────────┐ absent from trusted sync ┌─────────┐ │ active │ ────────────────────────────▶│ removed │ └────────┘ (reconcile UPDATE) └─────────┘ ▲ │ └────────────────────────────────────────┘ symbol reappears → upsert reactivates (status='active', removal_reason=null) ``` * **Reactivation is automatic and ordering-safe**: every sync upserts reported symbols with `status: 'active', removal_reason: null` *before* the reconcile pass runs, so a previously-removed symbol that comes back is revived by the same upsert that refreshes its price. * **A wrong removal self-heals**: if an outage ever slips past the trust gate and wipes an account, the next healthy sync reports the symbols again and the upsert flips them all back. This reversibility is what justified deleting the count guards. ### The decision + write, in one place The logic is deliberately split into pure, unit-tested helpers in [`lib/snaptrade-positions-sweep.ts`](../../lib/snaptrade-positions-sweep.ts): * `decidePositionsReconcile({reportedSymbols, syncTrusted})` → either `{action:'skip', reason:'sync_untrusted'}` or `{action:'reconcile', symbolsToKeep}` (the deduped reported set). An **empty `symbolsToKeep` is a valid reconcile** — a trusted empty response means full liquidation, the formerly-forbidden case. * `applyPositionsReconcile(client, accountId, symbolsToKeep, nowIso)` — the shared soft-delete writer used by all four routes. One UPDATE: ``` UPDATE snaptrade_account_positions SET status='removed', status_changed_at=now, removal_reason='reconciliation' WHERE account_id = $1 AND status = 'active' AND symbol NOT IN (symbolsToKeep...) -- omitted when keep list is empty ``` The `.eq('status','active')` predicate makes the write idempotent and keeps `status_changed_at` honest (stamped only on the actual active→removed flip). * `buildPostgrestInList` / `escapeSymbolForPostgrestInValue` — PostgREST-safe NOT-IN construction (supabase-js has `.in()` but no `.notIn()`, so the negated list is built manually with proper escaping). Symbols explicitly reported with `units <= 0` are excluded from `reportedSymbols`, so they fall outside `symbolsToKeep` and are removed by the same pass — there is no separate zero-quantity delete anymore. ## The Trust Gate `isSyncTrustedForReconcile(input)` is the **only** remaining gate, replacing the count guards. Reconciliation runs iff ALL of: | Signal | Meaning | Protects against | | - | - | - | | `accountDetailsFetchOk` | Account-details fetch succeeded | Broker/connection-level failure | | `positionsIsArray` | Positions fetch succeeded AND returned an actual array | Fetch/parse failure masquerading as "no positions" | | `!notReady425` | No 425 "initial sync not ready" seen | Reconciling against a half-populated first sync | | `!connectionBroken` | Connection not broken / needs\_reauth | Stale data from a dead connection | | `holdingsSyncComplete` | The **holdings portion** of the sync is complete | Partial holdings data | | `!positionsUpsertHadError` | No positions write (or row-build) failure this sync | Removing symbols we failed to persist/parse | **`holdingsSyncComplete` is intentionally the holdings-specific signal** (`sync_status.holdings.initial_sync_completed`), NOT the account-level `sync_status`. The account-level status goes `'partial'` whenever *transactions* are incomplete, and some brokers (notably **Empower**) sit at `'partial'` forever for transaction reasons while their holdings sync perfectly. Gating on the overall status would permanently block ghost cleanup for those accounts. Per-route mapping of the signals: | Route | `holdingsSyncComplete` source | Notes | | - | - | - | | `process-snaptrade-holdings-sync` | `accountDetailsResponse.data.sync_status.holdings.initial_sync_completed === true` | Persistent 425/broken-connection failures throw before this point | | `webhook` (NEW\_ACCOUNT\_AVAILABLE) | Same real signal from `getUserAccountDetails` | `notReady425` = the positions-fetch 425 flag | | `process-connection` (both paths) | `account.sync_status?.holdings?.initial_sync_completed === true` off the `listUserAccounts` Account object | The Account object carries `sync_status` (verified against prod: 500/500 recent stored payloads include the flag). New connections whose initial sync hasn't finished simply skip reconciliation here — nothing stale to clean; webhook/holdings-sync reconcile once holdings complete | A missing/malformed nested `symbol` on a position (seen on some corporate actions/delistings) also sets `positionsUpsertHadError` at every site — a symbol we cannot parse must not be treated as "no longer held," so the sync skips reconciliation instead of soft-removing the stored row. ## Write Sites (Where Reconciliation Runs) Four sites, all wrapped in `withSnaptradePositionsLock` (serializes holdings-class writers per account — SnapTrade fires `NEW_ACCOUNT_AVAILABLE` within seconds of the OAuth redirect, so `process-connection` and the webhook race on fresh connections): 1. [`app/api/internal/process-snaptrade-holdings-sync/route.ts`](../../app/api/internal/process-snaptrade-holdings-sync/route.ts) — the QStash-fanned holdings sync (chunked batched upserts). A failed reconcile marks the sync `'partial'` via `hadPersistenceErrors`. 2. [`app/api/snaptrade/webhook/route.ts`](../../app/api/snaptrade/webhook/route.ts) — `NEW_ACCOUNT_AVAILABLE` handler. 3. [`app/api/snaptrade/process-connection/route.ts`](../../app/api/snaptrade/process-connection/route.ts) — existing-account path. 4. Same file — new-account path. Each site follows the same shape: build/upsert reported rows (with `status:'active'`) → compute the trust inputs → `decidePositionsReconcile` → `applyPositionsReconcile` → telemetry. ## Readers (Everything Must Filter `status = 'active'`) Removed rows keep their `units`/`price` values (we only flip `status`), so **any reader that forgets the filter silently resurrects ghosts**. The failure mode is over-counting — visible and reversible, but wrong. Current coverage: **TypeScript:** * `hooks/queries/use-accounts.ts` — dashboard positions fetch * `lib/financial-data-cache.ts` — 4 read sites (`getFinancialMetrics`, `getAccountDetails` crypto, `getInvestmentDetails`, `getCryptoHoldingsDetails`) * `app/api/cron/process-daily-snapshot/route.ts` — snapshots record held positions only; disappearance is encoded by omission across days * `app/api/holdings/[id]/route.ts` — the cash-override PATCH only targets active rows (404s otherwise, via `maybeSingle`) **SQL functions (migration `20260708_02`):** * `get_user_net_worth_for_clerk`, `get_user_net_worth_export` — re-emitted with `AND sp.status = 'active'` in `agg_snaptrade` * `get_total_assets`, `get_user_asset_distribution` — patched in place (see [Gotchas](#gotchas--lessons-learned)) * `audit_usd_drift` and `refresh_*_usd_*` are **intentionally unfiltered** — maintenance passes should see every row **Views (skills repo, PR skills#154):** `account_details`, `crypto_holding_details`, `public_holding_details` **AI prompts:** both system prompts (`app/api/claude/config/system-prompt.ts`, chat config) instruct generated SQL to filter `snaptrade_account_positions.status = 'active'` *in addition to* the parent `snaptrade_accounts.is_active` — otherwise AI/tool SQL resurrects ghosts in chat answers. A regression test (`lib/__tests__/snaptrade-reader-status-filter.test.ts`) greps the known reader files for the filter. ## Telemetry & Monitoring In-app, sync-path emission via [`lib/snaptrade-reconcile-telemetry.ts`](../../lib/snaptrade-reconcile-telemetry.ts) (there is deliberately **no monitoring cron** — alerts fire the moment the event happens, not up to an hour later): * **`captureReconcileFailure(source, accountId, error)`** — Sentry **error** when a reconcile UPDATE fails (stale rows stay active; the sync is also marked `'partial'` in the holdings-sync route). * **`captureMassRemoval(source, accountId, removedCount, keptCount)`** — Sentry **warning** when a trusted sync soft-removes an *entire* account. Self-gates on `keptCount === 0 && removedCount >= 3` so routine small liquidations stay out of the alert stream. Tagged `snaptrade_full_liquidation:true`. Both are try/catch-wrapped — telemetry must never break a sync — and send only aggregate context (source, counts, account id), never symbol lists or values. **Alerting contract:** a *single* full-liquidation warning is usually a real user event (they emptied the account). A **cluster in a short window is the "SnapTrade returned empty-with-200 during an outage" signal** — configure the Sentry frequency alert on `area:snaptrade_reconcile + snaptrade_full_liquidation:true` to page on the cluster, not the singleton. Expect a one-time burst on release day as the backlog of ghost accounts gets its first trusted sync. ### The evidence log — `snaptrade_reconcile_log` Sentry only fires on *full* wipes; a **trusted-but-partial payload** that trims an account without emptying it (observed live 2026-07-10: two Robinhood accounts dropped from steady \~50/\~36-position payloads to 16/22 with no corroborating sells) fires nothing, and `snaptrade_account_positions` keeps only the current state plus the *last* transition per symbol — an oscillating payload leaves no trail. `snaptrade_reconcile_log` ([`lib/snaptrade-reconcile-log.ts`](../../lib/snaptrade-reconcile-log.ts)) closes that gap: **one row per reconcile decision at every write site** with the reported/zero-quantity counts, the removed symbols, and the *reactivated* symbols (previously removed symbols the payload brought back — the signature of a transient glitch). Best-effort writes; never blocks a sync. Uses: 1. **SnapTrade support cases** — "your API returned 50, 50, 50, then 16 (these 34 symbols missing), then 50 again," with timestamps and symbol lists. 2. **Partial-payload detection** — `removed_count > 0` followed shortly by `reactivated_count > 0` on the same account. Schema note: the per-symbol arrays exist (rather than counts alone, which would suffice for the two uses above) so that removal/reactivation events stay joinable with the daily snapshots and the transactions feed. Don't "optimize" them away — counts can't be re-expanded into symbols after the fact. Retention: automated — the weekly `/api/cron/cleanup-snaptrade-reconcile-log` cron (Mondays 04:30 UTC) deletes rows with `synced_at` older than 90 days (≈1 MB/day of inserts — bounded by design, unlike the append-only audit tables on the disk-pressure list). ## The Backfill Script [`scripts/backfill-snaptrade-stale-positions.ts`](../../scripts/backfill-snaptrade-stale-positions.ts) proactively sweeps existing ghosts instead of waiting for each account's next organic sync. Per account: fetch fresh positions from SnapTrade, run the same `decidePositionsReconcile` decision, and apply the same soft-delete UPDATE with `removal_reason: 'backfill'`. Supports `--dry-run` (SELECT-only preview of what would flip), `--clerk-id` / `--account-id` targeting, and concurrency/rate-limit tuning. ## Gotchas & Lessons Learned 1. **Never re-emit a SQL function from a stale snapshot.** The original version of migration 02 re-emitted the two net-worth functions from a June-25 copy of the live definitions — which would have silently **reverted** the cash-sweep dedup and FX-over-cash changes applied to prod in the meantime. Function re-emits in ever-new migration files never git-conflict, so concurrent redefinitions slip through review. Before re-emitting, diff your base against live `pg_get_functiondef()`. When the repo has no current source for a function, prefer the **patch-in-place** pattern used in `20260708_02` for `get_total_assets` / `get_user_asset_distribution`: read `pg_get_functiondef()` at apply time, assert the patch anchor appears the exact expected number of times, `replace()`, `EXECUTE` — the migration fails loudly on drift instead of clobbering someone else's change. 2. **Migrations are hand-applied before the code release.** There is no CI auto-apply. Code that writes/reads `status` must not deploy before `20260708_01` exists in prod. 3. **`status_changed_at` on active rows means "last confirmed active."** Every upsert stamps it, so for active rows it is refreshed each sync rather than recording the last transition. For **removed** rows the transition semantics hold exactly (only the reconcile UPDATE touches them), which is what the future hard-purge cursor needs. 4. **`removal_reason = 'zero_quantity'` is reserved but currently unwritten.** Zero-quantity symbols are removed by the same NOT-IN pass as absent symbols and record `'reconciliation'`. The value is admitted by the CHECK for a future writer that wants the distinction. 5. **A row-build parse failure must trip the trust gate.** If a position throws during row construction — or arrives with a missing/malformed nested `symbol` (`position.symbol?.symbol?.symbol` resolves empty, seen on some corporate actions/delistings) — its symbol is missing from `reportedSymbols`, and the reconcile would remove it as if the broker dropped it. All routes set `positionsUpsertHadError = true` in both the per-row catch and the missing-symbol skip so the sync skips reconciliation instead. 6. **The residual accepted risk:** `initial_sync_completed` stays `true` forever once set, so an upstream outage that returns an empty array with HTTP 200 and healthy account details *is* trusted and wipes the account. This is the deliberate trade-off of dropping the count guards; the mitigations are reversibility (next healthy sync restores everything) and the full-liquidation Sentry warning (clusters = outage). 7. **Empower accounts sit at `sync_status='partial'` forever** (transactions never complete). That's why the trust gate keys on the holdings-specific completion signal — see [The Trust Gate](#the-trust-gate). ## Deliberate Non-Goals & Follow-Ups * **Current state only, not history.** `UNIQUE(account_id, symbol)` + soft-delete gives *current state plus the last removal*. It cannot represent sold → re-bought → sold-again history; a true positions **event/audit table** is a separate follow-up (needed for robust "when did I sell X?" chat answers). * **`removal_reason` enrichment (planned PR):** when a symbol is removed, look up recent SnapTrade transactions for `account_id + symbol` — a matching SELL means `'sold'`, none means `'transferred'`. Enriches the reason; never gates removal. * **\$0-account reconstruction (CFO-6245) — SHIPPED separately:** rebuilding a null broker total from summed positions lives in `lib/snaptrade-derived-balance.ts` (strict-null trigger, cash-equivalent exclusion, `balance_is_derived` labeling on `snaptrade_accounts`). It reuses this system's trust gate and reads only `status='active'` rows — see the derived-balance section of `snaptrade-integration.md`. * **Hard purge of long-removed rows:** `status_changed_at` is the cursor; nothing is purged today. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.