​

SnapTrade Positions — Soft-Delete Reconciliation

​

Table of Contents

1. The Problem
2. The Core Insight: Absence Is the Signal
3. History: Why Hard Delete + Count Guards Failed
4. The Soft-Delete Model
5. The Trust Gate
6. Write Sites (Where Reconciliation Runs)
7. Readers (Everything Must Filter status = 'active')
8. Telemetry & Monitoring
9. The Backfill Script
10. Gotchas & Lessons Learned
11. Deliberate Non-Goals & Follow-Ups

​

The Problem

​

The Core Insight: Absence Is the Signal

A symbol we have stored, that a healthy response no longer contains, has left the account.

​

History: Why Hard Delete + Count Guards Failed

• 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 Soft-Delete Model

​

Schema (migration 20260708_01)

​

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

• 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).

​

The Trust Gate

​

Write Sites (Where Reconciliation Runs)

1. 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 — NEW_ACCOUNT_AVAILABLE handler.
3. app/api/snaptrade/process-connection/route.ts — existing-account path.
4. Same file — new-account path.

​

Readers (Everything Must Filter status = 'active')

• 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)

• 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)
• audit_usd_drift and refresh_*_usd_* are intentionally unfiltered — maintenance passes should see every row

​

Telemetry & Monitoring

• 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.

​

The evidence log — snaptrade_reconcile_log

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.

​

The Backfill Script

​

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.

​

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.