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

# SnapTrade Transaction Capture Implementation

## Overview

This document describes the implementation of transaction (activities) data capture from SnapTrade brokerage accounts. SnapTrade provides historical transaction data including buys, sells, dividends, deposits, withdrawals, fees, and more.

## Key Concepts

### Activities vs Orders

**Activities** (what we capture):

* Updated once daily
* May not have timestamps (only dates)
* Only contain completed transactions that resulted in position changes
* Often have years of history, sometimes entire account history
* Cached data that's refreshed daily

**Orders** (not currently captured):

* Real-time order data
* Includes pending and cancelled orders
* More detailed timing information

### Transaction Types

SnapTrade classifies transactions into common types:

**Most Common Types:**

* `BUY` - Asset bought (negative amount, positive units)
* `SELL` - Asset sold (positive amount, negative units)
* `DIVIDEND` - Dividend payout (positive amount)
* `CONTRIBUTION` - Cash contribution/deposit (positive amount)
* `WITHDRAWAL` - Cash withdrawal (negative amount)
* `REI` - Dividend reinvestment
* `STOCK_DIVIDEND` - Dividend paid in shares instead of cash
* `INTEREST` - Interest deposited
* `FEE` - Fee withdrawn (negative amount)
* `TAX` - Tax-related fee
* `OPTIONEXPIRATION` - Option expiration event
* `OPTIONASSIGNMENT` - Option assignment event
* `OPTIONEXERCISE` - Option exercise event
* `TRANSFER` - Transfer between accounts
* `SPLIT` - Stock split

**Important Fields:**

* `amount`: Positive = gaining cash, Negative = spending cash
* `units`: Positive = gaining shares, Negative = losing shares
* Example: BUY has positive units and negative amount

## Database Schema

### Table: `snaptrade_transactions`

```sql theme={null}
CREATE TABLE snaptrade_transactions (
  id UUID PRIMARY KEY,
  clerk_id TEXT NOT NULL,
  snaptrade_account_id TEXT NOT NULL,
  snaptrade_activity_id TEXT,
  
  -- Transaction details
  type TEXT NOT NULL,
  description TEXT,
  
  -- Symbol info (null for cash-only transactions)
  symbol TEXT,
  symbol_id TEXT,
  option_symbol TEXT,
  option_type TEXT,  -- PUT, CALL, etc. (for options)
  
  -- Institution info
  institution TEXT,  -- Institution where transaction occurred
  
  -- Financial details
  amount NUMERIC,
  units NUMERIC,
  price NUMERIC,
  currency TEXT,     -- Currency code (USD, CAD, etc.)
  currency_id TEXT,  -- SnapTrade's currency ID
  
  -- Fees and FX
  fee NUMERIC,
  fx_rate NUMERIC,
  
  -- Dates
  trade_date DATE,
  settlement_date DATE,
  
  -- Metadata
  raw_response JSONB,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  
  CONSTRAINT unique_snaptrade_activity 
    UNIQUE (snaptrade_account_id, snaptrade_activity_id)
);
```

### Indexes

* `idx_snaptrade_transactions_clerk_id`
* `idx_snaptrade_transactions_account_id`
* `idx_snaptrade_transactions_trade_date`
* `idx_snaptrade_transactions_type`
* `idx_snaptrade_transactions_symbol`

## API Endpoints

### POST `/api/snaptrade/fetch-activities`

Fetches and stores transaction data for a specific account.

**Request Body:**

```json theme={null}
{
  "userId": "snaptrade_user_id",
  "userSecret": "user_secret",
  "accountId": "account_id",
  "clerkId": "clerk_user_id"
}
```

**Response:**

```json theme={null}
{
  "success": true,
  "total_activities": 1500,
  "inserted": 1450,
  "updated": 50,
  "skipped": 0,
  "request_id": "fetch_activities_123..."
}
```

**Features:**

* Automatic pagination (fetches all pages)
* Upsert logic to handle duplicates
* Updates account sync status
* Comprehensive error handling

## Webhook Events

### ACCOUNT\_TRANSACTIONS\_INITIAL\_UPDATE

Triggered the first time SnapTrade collects transaction data from an account.

**Example Payload:**

```json theme={null}
{
  "webhookId": "aa7a7d5a-4a12-40df-a329-feedc738eee9",
  "clientId": "rmCZBUGAXP",
  "eventTimestamp": "2022-05-31T12:39:47.182403+00:00",
  "userId": "7jN22b2AWHMOqOeujjaiU5kKOM8iITt11ENcUMftx6LBg9GTn2",
  "eventType": "ACCOUNT_TRANSACTIONS_INITIAL_UPDATE",
  "webhookSecret": "gQNuZIBwPYUVAsXvCfOA",
  "accountId": "d9f55a48-5dc6-4100-acee-24e5dfd8ec26",
  "brokerageAuthorizationId": "d28affae-f918-4ecd-8128-e5438e861706"
}
```

### ACCOUNT\_TRANSACTIONS\_UPDATED

Triggered when account transactions are updated (daily refresh).

**Example Payload:**

```json theme={null}
{
  "webhookId": "4eb13e1c-5778-44c2-968d-813d81cb73a1",
  "clientId": "JELZRJPqui",
  "eventTimestamp": "2022-05-31T12:40:05.944481+00:00",
  "userId": "4XTxw0mZhffRCdrgPHBG1VqdklkVJdEz5Uor0hq29hwPYlLS7s",
  "eventType": "ACCOUNT_TRANSACTIONS_UPDATED",
  "webhookSecret": "DDAVBuPPGgqwADXgbcOE",
  "accountId": "6e30be12-4ab0-4ec0-8d4d-0dfdce509e36",
  "brokerageAuthorizationId": "8105b33e-3dd6-4189-b93e-31ccaa9d3b69"
}
```

## Webhook Handler Flow

When a transaction webhook is received:

1. **Validate webhook secret** - Authenticate the request
2. **Fetch account details** - Update balance and sync status
3. **Call fetch-activities endpoint** - Retrieve all transactions
4. **Store activities** - Upsert into `snaptrade_transactions` table
5. **Update account** - Mark `transactions_sync_completed = true`
6. **Record webhook event** - Log processing in `snaptrade_webhook_events`

## Implementation Files

### Created Files:

1. `/sql-queries/create-snaptrade-transactions-table.sql` - Database schema
2. `/app/api/snaptrade/fetch-activities/route.ts` - Activity fetching endpoint
3. `/docs/SNAPTRADE-TRANSACTIONS.md` - This documentation

### Modified Files:

1. `/app/api/snaptrade/webhook/route.ts`:
   * Added `handleTransactionsUpdate()` function
   * Updated event handlers for transaction webhooks
   * Refactored legacy `handleTradesSyncCompleted()` to use new handler

## Data Flow

```
SnapTrade Webhook Event
        ↓
handleTransactionsUpdate()
        ↓
1. Fetch account details (update balance)
        ↓
2. Call /api/snaptrade/fetch-activities
        ↓
3. SnapTrade API: getAccountActivities (paginated)
        ↓
4. Process each activity
        ↓
5. Upsert to snaptrade_transactions table
        ↓
6. Update account sync status
        ↓
7. Record webhook event
```

## Testing

To test the implementation:

1. **Create the database table:**
   ```sql theme={null}
   -- Run the SQL in create-snaptrade-transactions-table.sql
   ```

2. **Trigger a webhook manually:**
   ```bash theme={null}
   curl -X POST http://localhost:3000/api/snaptrade/webhook \
     -H "Content-Type: application/json" \
     -d '{
       "webhookSecret": "your_secret",
       "eventType": "ACCOUNT_TRANSACTIONS_INITIAL_UPDATE",
       "userId": "user_id",
       "accountId": "account_id",
       "brokerageAuthorizationId": "auth_id"
     }'
   ```

3. **Check the database:**
   ```sql theme={null}
   SELECT COUNT(*) FROM snaptrade_transactions;
   SELECT * FROM snaptrade_transactions LIMIT 10;
   ```

4. **Verify webhook logging:**
   ```sql theme={null}
   SELECT * FROM snaptrade_webhook_events 
   WHERE event_type IN (
     'ACCOUNT_TRANSACTIONS_INITIAL_UPDATE',
     'ACCOUNT_TRANSACTIONS_UPDATED'
   )
   ORDER BY created_at DESC;
   ```

## Performance Considerations

### Pagination

* SnapTrade API returns max 1000 transactions per page
* Our implementation fetches all pages automatically
* For accounts with 10+ years of history, expect multiple pages

### Data Freshness

* Transaction data is cached and refreshed once daily by SnapTrade
* Not suitable for real-time transaction monitoring
* Perfect for historical analysis and tax reporting

### Upsert Strategy

* Uses `(snaptrade_account_id, snaptrade_activity_id)` as unique key
* Prevents duplicates while allowing updates to existing records
* Safe to run multiple times without data duplication

## Future Enhancements

1. **UI Integration**
   * Display transaction history in frontend
   * Filter by type, date range, symbol
   * Export to CSV for tax purposes

2. **Analytics**
   * Calculate cost basis
   * Track realized gains/losses
   * Generate tax reports

3. **Reconciliation**
   * Compare SnapTrade transactions with holdings
   * Detect discrepancies
   * Alert on unusual activity

4. **Performance Optimization**
   * Incremental updates (only fetch recent transactions)
   * Background job for initial sync of large histories
   * Caching frequently accessed data

## Troubleshooting

### Transactions not appearing

1. Check webhook logs:
   ```sql theme={null}
   SELECT * FROM snaptrade_webhook_events 
   WHERE event_type LIKE '%TRANSACTION%'
   ORDER BY created_at DESC;
   ```

2. Check account sync status:
   ```sql theme={null}
   SELECT 
     snaptrade_account_id,
     transactions_sync_completed,
     transactions_last_sync,
     last_sync_result
   FROM snaptrade_accounts;
   ```

3. Check for errors in fetch-activities:
   * Look for 425 errors (sync not ready)
   * Verify user credentials are valid
   * Check SnapTrade API status

### Duplicate transactions

* Should not occur due to unique constraint
* If duplicates appear, check `snaptrade_activity_id` values
* Some brokers may not provide consistent activity IDs

### Missing transaction data

* SnapTrade data availability varies by broker
* Some older transactions may not have complete data
* Check `raw_response` field for actual data received

## References

* [SnapTrade API Documentation](https://docs.snaptrade.com/)
* [SnapTrade Account Activities Endpoint](https://api.snaptrade.com/api/v1/accounts/\{accountId}/activities)
* [SnapTrade Webhook Events](https://docs.snaptrade.com/webhooks)


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