​

SnapTrade Transaction Capture Implementation

​

Overview

​

Key Concepts

​

Activities vs Orders

• 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

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

​

Transaction 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

• 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

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

{
  "userId": "snaptrade_user_id",
  "userSecret": "user_secret",
  "accountId": "account_id",
  "clerkId": "clerk_user_id"
}

{
  "success": true,
  "total_activities": 1500,
  "inserted": 1450,
  "updated": 50,
  "skipped": 0,
  "request_id": "fetch_activities_123..."
}

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

​

Webhook Events

​

ACCOUNT_TRANSACTIONS_INITIAL_UPDATE

{
  "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

{
  "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

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

1. Create the database table:
   -- Run the SQL in create-snaptrade-transactions-table.sql
   
2. Trigger a webhook manually:
   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:
   SELECT COUNT(*) FROM snaptrade_transactions;
   SELECT * FROM snaptrade_transactions LIMIT 10;
   
4. Verify webhook logging:
   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:
   SELECT * FROM snaptrade_webhook_events 
   WHERE event_type LIKE '%TRANSACTION%'
   ORDER BY created_at DESC;
   
2. Check account sync status:
   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
• SnapTrade Account Activities Endpoint
• SnapTrade Webhook Events