​

SnapTrade Integration Documentation (2025 Architecture)

​

Overview

Positions lifecycle: how stored positions are kept in sync with SnapTrade (soft-delete reconciliation, the trust gate, ghost cleanup) is documented separately in SNAPTRADE-POSITIONS-RECONCILIATION.md.

​

Table of Contents

1. Architecture Overview
2. 2025 QStash Daily Snapshot Architecture
3. Database Schema
4. API Routes & Backend Logic
5. Frontend Integration
6. Error Handling & Troubleshooting
7. Best Practices & Local Development
8. Example Flows & Code Snippets

​

Architecture Overview

​

High-Level Flow

1. User initiates connection from the Accounts page.
2. Backend generates a SnapTrade auth URL and redirects the user to SnapTrade’s portal.
3. User completes authentication with their broker (e.g., Fidelity, Schwab).
4. SnapTrade redirects back to our callback endpoint.
5. Backend processes the connection:
   • Fetches all connections and accounts for the user.
   • Inserts/updates records in snaptrade_institutions and snaptrade_accounts.
   • Immediately fetches and upserts balances and positions for each account (initial sync).
   • Sets sync_status for each account (syncing, success, error).
6. Frontend polls or subscribes to sync status and shows a “Syncing…” state until data is ready.
7. Ongoing updates: SnapTrade sends webhooks when account data changes. The backend processes these, but only if the account’s sync_status is success (to avoid race conditions).
8. Frontend auto-refreshes when sync completes or new data arrives.

​

Diagram

User → [Accounts Page] → Backend → SnapTrade Portal → Backend (callback) → DB (initial sync) → Frontend (syncs)
         ↑                        ↓
         ←←←←←←←←←←←←←←←←←←←←←←←←
         Webhooks (ongoing updates)

​

2025 QStash Daily Snapshot Architecture

​

Overview

​

How It Works

1. /api/cron/schedule-daily-snapshots (GET, called by Vercel Cron)
   • Authenticates with CRON_SECRET.
   • Fetches all non-test users.
   • Queues a QStash job for each user to /api/cron/process-daily-snapshot with { clerkId } in the body.
   • Adds a delay between jobs to avoid rate limits and backend overload.
2. /api/cron/process-daily-snapshot (POST, called by QStash)
   • Verifies QStash signature.
   • Runs the full daily snapshot logic for a single user, including:
     • Plaid, manual, crypto, property, and SnapTrade data
     • SnapTrade account, position, and balance daily snapshots
     • Net worth, cash, and market value calculations
     • Upserts to all relevant snapshot tables
     • Sends daily summary email if enabled

​

Benefits

• Scalability: Handles thousands of users without Vercel function timeouts.
• Reliability: Each user’s snapshot is isolated; failures/retries are per-user.
• Observability: Logs and error handling are per-user/job.
• No more monolithic cron timeouts!

​

Flow Diagram

Vercel Cron → /api/cron/schedule-daily-snapshots
    ↓
QStash (queues 1 job per user, with delay)
    ↓
/api/cron/process-daily-snapshot (per user)
    ↓
- Snapshots for Plaid, Manual, Crypto, SnapTrade, etc.
- SnapTrade account/position/balance daily snapshots
- Net worth, cash, market value updated
- Email summary sent (if enabled)

​

Code Snippet (Scheduling)

// schedule-daily-snapshots/route.ts
for (const user of users) {
  await qstash.publishJSON({
    url: destination,
    body: { clerkId: user.clerk_id },
    delay: delay,
    retries: 3,
  })
  delay += DELAY_BETWEEN
}

​

Note on SnapTrade Data

• SnapTrade account, position, and balance daily snapshots are now included in daily net worth and portfolio value calculations.
• The following tables are populated daily:
  • snaptrade_account_daily_snapshots
  • snaptrade_position_daily_snapshots
  • snaptrade_account_balance_daily_snapshots
• These are used for historical analytics, net worth, and reporting.

​

Deprecated: Old Monolithic Cron

• The old /api/cron/daily-snapshots route is now deprecated for daily snapshotting.
• Use the QStash fan-out pattern for all production snapshot jobs.

​

Database Schema

​

1. snaptrade_users

• Maps your app’s user (Clerk) to a SnapTrade user.
• Stores SnapTrade user ID and secret.

​

2. snaptrade_institutions

• Represents a connection between a user and a brokerage (e.g., Fidelity).
• Key columns: clerk_id, snaptrade_user_id, snaptrade_connection_id, broker_name, status, last_synced_at.
• snaptrade_connection_id is always set to the real SnapTrade connection ID (never null).

​

3. snaptrade_accounts

• Represents individual accounts under a connection (e.g., a brokerage account, IRA, etc).
• Key columns: clerk_id, snaptrade_user_id, snaptrade_connection_id, snaptrade_account_id, account_name, account_type, sync_status, last_synced_at, last_sync_result.
• sync_status tracks the state: syncing, success, error.
• Derived balance (CFO-6245): some brokers (e.g. Empower 401k) return a NULL balance.total while positions sync fine. When that happens on a trusted sync, the holdings sync derives balance_usd from active non-cash-equivalent positions + USD cash and labels it via balance_is_derived / balance_derived_at / balance_derivation_source (positions_sum | cash_sum). A null total is never written as $0, and a real broker total (including a genuine 0) clears the label. Derived balances should be presented as approximate ("≈$X”); a both-null balance renders as unavailable (”—”). Complete-picture rule: a derivation is only written when BOTH halves are known this sync — the active positions read succeeded AND fresh cash is available (computed from this sync’s balance response, never a stale re-read). If either is unknown (positions readback failed/capped, or the balance fetch failed), the derivation is skipped and the prior value/label are PRESERVED — a positions-only or cash-only figure would silently understate the full-account total. Likewise if a later sync is untrusted/fails; the stale balance_derived_at is the staleness signal. Support note: per-account displays show ”—” for unknown balances, but the SQL net-worth aggregates COALESCE unknown to 0 — so “my account shows — but my net worth didn’t drop” is expected behavior, not a bug (an unknown account simply contributes nothing to the total).

​

4. snaptrade_account_balances

• Stores multi-currency balances for each account.
• Key columns: account_id (references snaptrade_accounts), currency_code, balance, available_balance, last_updated.

​

5. snaptrade_account_positions

• Stores holdings/positions for each account.
• Key columns: account_id, symbol, units, price, open_pnl, average_purchase_price, last_updated.

​

6. snaptrade_webhook_events

• Logs all incoming webhooks and their processing status.
• Useful for debugging and replaying missed events.

​

API Routes & Backend Logic

​

1. /api/snaptrade (Connection Initialization)

• Generates a SnapTrade auth URL for the user.
• Handles local vs. production URLs (uses ngrok for local dev).
• Redirects user to SnapTrade’s portal.

​

2. /api/snaptrade/callback (OAuth Callback)

• Handles the redirect from SnapTrade after user authentication.
• Verifies parameters and redirects user to the accounts page.
• (Most of the heavy lifting is done in the next step.)

​

3. /api/snaptrade/process-connection (Initial Sync)

• Core of the integration.
• Fetches all connections and accounts for the user from SnapTrade.
• Upserts connections into snaptrade_institutions (using the real SnapTrade connection ID).
• Upserts accounts into snaptrade_accounts.
• For each account:
  • Sets sync_status to syncing at the start.
  • Fetches balances and upserts into snaptrade_account_balances.
  • Fetches positions and upserts into snaptrade_account_positions.
  • On success, sets sync_status to success and updates last_synced_at.
  • On error, sets sync_status to error and logs the error in last_sync_result.
• Returns a summary to the frontend.

​

4. /api/snaptrade/webhook (Ongoing Sync)

• Handles webhooks from SnapTrade (e.g., holdings updated, transactions, etc).
• Before processing, checks the account’s sync_status:
  • If not success (e.g., still syncing), skips processing and logs the event for retry.
  • If success, fetches latest balances/positions and upserts as above.
• Logs all events in snaptrade_webhook_events.

​

5. /api/snaptrade/refresh (Manual/On-Demand Refresh)

• (Optional) Allows the user to manually trigger a refresh of their SnapTrade data.

​

Frontend Integration

​

1. User Experience

• User clicks “Connect SnapTrade” on the Accounts page.
• Sees a “Syncing…” state while initial sync is in progress.
• Accounts, balances, and holdings appear as soon as sync completes.
• If an error occurs, user sees a clear error and can retry.
• Ongoing updates (via webhooks) are reflected in real time or on page refresh.

​

2. Polling/Realtime

• The frontend polls or subscribes to the sync_status of each SnapTrade account.
• When sync_status changes to success, the UI auto-refreshes.
• If sync_status is error, the UI shows an error and offers a retry.

​

3. Manual Refresh

• User can trigger a manual refresh (calls /api/snaptrade/refresh).

​

Error Handling & Troubleshooting

• All errors are logged in the backend and in snaptrade_webhook_events.
• Sync status is always updated (syncing, success, error) so the frontend can show the right state.
• Common issues:
  • If accounts or balances are missing, check logs and the sync_status column.
  • If webhooks are skipped, check if initial sync is still in progress.
  • If snaptrade_connection_id is null, ensure you are always using connection.id from SnapTrade.
• See the troubleshooting section at the end of this doc for more tips.

​

Best Practices & Local Development

• Use ngrok for local development so SnapTrade can reach your local server.
• Set NEXT_PUBLIC_NGROK_URL in your .env.local to your ngrok URL.
• Always use the real SnapTrade connection ID (connection.id) for snaptrade_connection_id.
• Use upsert with onConflict for all balance and position inserts to avoid duplicates.
• Keep sync logic idempotent and robust to retries.
• Use clear, user-friendly error messages in the frontend.
• Log all webhook events for debugging and replay.

​

Example Flows & Code Snippets

​

1. Initial Sync (process-connection)

// For each account after upsert:
try {
  // Fetch balances
  const balanceResponse =
    await snapTradeClient.accountInformation.getUserAccountBalance({
      userId,
      userSecret,
      accountId,
    })
  if (balanceResponse?.data && Array.isArray(balanceResponse.data)) {
    for (const currencyBalance of balanceResponse.data) {
      await supabase.from('snaptrade_account_balances').upsert(
        {
          account_id: accountId,
          currency_code: currencyBalance.currency?.code,
          balance: currencyBalance.cash || 0,
          available_balance:
            currencyBalance.buying_power || currencyBalance.cash || 0,
          last_updated: new Date().toISOString(),
          raw_response: JSON.stringify(currencyBalance),
        },
        { onConflict: 'account_id,currency_code' }
      )
    }
  }
  // Fetch positions
  const positionsResponse =
    await snapTradeClient.accountInformation.getUserAccountPositions({
      userId,
      userSecret,
      accountId,
    })
  if (positionsResponse?.data && Array.isArray(positionsResponse.data)) {
    for (const position of positionsResponse.data) {
      await supabase.from('snaptrade_account_positions').upsert(
        {
          account_id: accountId,
          symbol: position.symbol?.symbol?.symbol || '',
          units: position.units || 0,
          price: position.price || 0,
          last_updated: new Date().toISOString(),
          raw_response: JSON.stringify(position),
        },
        { onConflict: 'account_id,symbol' }
      )
    }
  }
  // Set sync_status to 'success'
  await supabase
    .from('snaptrade_accounts')
    .update({
      sync_status: 'success',
      last_synced_at: new Date().toISOString(),
      last_sync_result: null,
    })
    .eq('clerk_id', clerkId)
    .eq('snaptrade_account_id', accountId)
} catch (err) {
  // Set sync_status to 'error'
  await supabase
    .from('snaptrade_accounts')
    .update({
      sync_status: 'error',
      last_sync_result: String(err),
    })
    .eq('clerk_id', clerkId)
    .eq('snaptrade_account_id', accountId)
}

​

2. Webhook Processing

// Before processing, check sync_status
const { data: accountStatusData } = await supabase
  .from('snaptrade_accounts')
  .select('sync_status')
  .eq('snaptrade_account_id', accountId)
  .single();
if (accountStatusData?.sync_status !== 'success') {
  // Skip processing, log event for retry
  await supabase.from('snaptrade_webhook_events').insert({
    ...
    process_notes: `Skipped due to sync_status=${accountStatusData.sync_status}`
  });
  return;
}
// Otherwise, process balances/positions as above

​

3. Frontend Polling Example

// Poll or subscribe to sync_status
const { data: accounts } = useAccounts()
const isSyncing = accounts.some((acc) => acc.sync_status === 'syncing')
if (isSyncing) {
  // Show spinner or "Syncing..."
} else {
  // Show accounts and balances
}

​

Conclusion

• Users see their full account data immediately after connecting.
• Ongoing updates are reliable and race-condition free.
• Errors are visible and actionable for both users and engineers.
• The system is robust, scalable, and easy to extend.