> ## 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 integration

# SnapTrade Integration Documentation (2025 Architecture)

## Overview

This document explains the SnapTrade integration in the Portfolio Tracker app. It is designed for reliability, scalability, and a great user experience. It covers the full flow: from user connection, through initial sync, to ongoing updates via webhooks. It also explains how sync status is tracked and how the frontend stays in sync with backend data.

> **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`](./SNAPTRADE-POSITIONS-RECONCILIATION.md).

***

## Table of Contents

1. [Architecture Overview](#architecture-overview)
2. [2025 QStash Daily Snapshot Architecture](#2025-qstash-daily-snapshot-architecture)
3. [Database Schema](#database-schema)
4. [API Routes & Backend Logic](#api-routes--backend-logic)
5. [Frontend Integration](#frontend-integration)
6. [Error Handling & Troubleshooting](#error-handling--troubleshooting)
7. [Best Practices & Local Development](#best-practices--local-development)
8. [Example Flows & Code Snippets](#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

To support scalability and reliability for daily net worth and account snapshots (including SnapTrade data), the app now uses a QStash-based fan-out pattern for daily snapshotting. This replaces the old monolithic cron job, which is now deprecated for daily snapshotting.

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

```typescript theme={null}
// 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)

```typescript theme={null}
// 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

```typescript theme={null}
// 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

```tsx theme={null}
// 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

This architecture ensures:

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

If you are a new engineer, read through the code and this doc, and you'll be able to confidently work on or extend the SnapTrade integration!


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.