Brokerage and SnapTrade

Lesson 11 · about 15 minutes · how a user's real brokerage account becomes rows in the snaptrade schema, and why the webhook and the brokerage function are two different trust boundaries.

This is the only part of the backend that holds a per-user secret for a third party. Every design choice here, the vault, the two functions, the fire-and-forget webhook, the P0002 error codes, follows from one fact: SnapTrade gives us raw BUY/SELL legs and a userSecret, and nothing else. We reconstruct everything the app shows.

Two functions, two trust boundaries

FunctionWho calls itAuth gateWhy separate
brokerageThe app (user routes) and the webhook function (internal routes)User routes: AuthMiddleware JWT. Internal routes: validateServiceRoleHolds the SnapTrade SDK and the vault decrypt. brokerage/index.ts:37-63
snaptrade-webhookSnapTrade's serversHMAC-SHA256 over the canonical JSON body, Signature headerNo JWT exists on a vendor push; the secret is the shared SNAPTRADE_CONSUMER_KEY. snaptrade-webhook/security/signature.ts:72-88

Note the router in brokerage/index.ts checks the internal endpoints before wrapping the rest in AuthMiddleware. An internal route reached with a user JWT still fails, because each internal handler calls validateServiceRole itself. brokerage/handlers/refreshBalances.ts:34

Connect flow: user to stored accounts

1POST /brokerage/register with consent_given_at. Calls SnapTrade to create the user, then stores the returned userSecret in Vault through public.create_secret and writes snaptrade.users with only the vault id. The plaintext secret never lands in a table. brokerage/README.md · migrations/20260310205722
2POST /brokerage/connect. getAuthContext validates the JWT with a service client, then getSnapTradeCredential reads user_secret_id and decrypts it. loginUser returns a portal URL that expires in five minutes. brokerage/utils/auth.ts:27-54 · brokerage/handlers/connect.ts:33-47
3The app opens the portal in a WebView. The user logs into their broker on SnapTrade's site. We never see broker credentials.
4POST /brokerage/connection-created with the portal's authorizationId. The handler re-validates it against listBrokerageAuthorizations rather than trusting the client, then upserts brokerage_connections and brokerage_accounts, seeds account_balances, sets Profiles.has_brokerage_connected, and writes an audit row. brokerage/handlers/connectionCreated.ts
5SnapTrade also sends CONNECTION_ADDED to the webhook, which upserts the same connection row. Both paths are idempotent on authorization_id, so order does not matter. snaptrade-webhook/processors/connection.ts
Why a vault id instead of the secret in a column? snaptrade.users is readable by service_role only, so a column would already be hidden from users. The vault adds encryption at rest and a single rotation point, and it means a leaked database dump does not leak brokerage access. The cost is one extra RPC on every SnapTrade call, which is why every handler goes through getSnapTradeCredential rather than caching.

Webhook flow: SnapTrade to derived data

1Verify, then record. The signature is checked over a canonical form that reproduces Python's json.dumps(sort_keys=True), compared in constant time. A payload older than 24 hours is refused; the comment explains why the vendor's 5-minute sample was wrong for their own retry schedule. Every delivery is then upserted into webhook_deliveries on webhook_id, which is the real replay guard. signature.ts:23 · snaptrade-webhook/index.ts:56-79
2Foreign-user guard. Our SnapTrade customer account is shared with another app, so valid-signature events arrive for users that are not ours. isWsbUserId is a UUID shape check, not a table lookup: malformed ids are provably foreign and get a 200; well-formed unknown ids keep erroring loudly. snaptrade-webhook/lib/userId.ts · index.ts:85-93
3Dispatch by event type. Connection events are awaited and marked handled. Holdings and transaction events are fire-and-forget: EdgeRuntime.waitUntil lets the function return 200 immediately while supabase.functions.invoke calls the internal brokerage/* routes. Those rows stay handled=false on purpose, because the outcome is unobservable from here. index.ts:103-143 · processors/holdings.ts:38-42
4Internal refresh handlers. refresh-balances upserts account_balances and appends account_balance_history (a trigger freezes the USD value). refresh-positions and refresh-holdings snapshot positions. sync-activities pulls the full activity history and hands it to snaptrade.process_activities. brokerage/handlers/refreshBalances.ts:79-100 · syncActivities.ts
5FIFO matching in SQL. process_activities is SECURITY DEFINER, service-role only, and idempotent. It walks BUY/SELL legs in time order, matches sells against open_lots, writes realized_trades, and since migration 20260812192023 also writes the trade cards in feed_items in the same transaction. Unattributable sells (transferred-in shares) are skipped, never guessed. migrations/20260624181133_create_winrate_tracking.sql:106

The schema in one table

TableWritten byRead by
snaptrade.usersregisterservice_role only; RLS enabled with no user policy
brokerage_connections, brokerage_accountsconnection-created, webhookowner via RLS auth.uid() = user_id. A trigger truncates account_number_masked to 4 chars on write. migrations/20260305194442:135-158
account_balances, account_balance_history, account_positions_snapshot, account_holdings_snapshot, account_trade_countsinternal refresh handlersowner via RLS, read directly over PostgREST. There is no read endpoint; the schema is in [api].schemas.
open_lots, realized_tradesprocess_activitiesget_user_winrate and the leaderboard functions
private.connection_stalenessa cron calling run_connection_staleness_checkyou, when a connection stops delivering. Flags accounts with no sync in 36h and attempts capped recovery. migrations/20260904121500
Why never log a raw SnapTrade error? The SDK puts userSecret in the request URL as a query parameter, and its error object copies that URL into url, message and toJSON(). One console.error(err) writes a user's brokerage credential to the function logs. describeSnapTradeError exists so every catch block has a safe default, and the router's last-resort catch uses it too. brokerage/clients/snaptradeClient.ts:48-80 · index.ts:90-95

Check yourself

Do this in the repo

  1. Run the pure unit tests that need no stack, and read what each one guards: 
    cd src && deno test --allow-all supabase/tests/functions/snaptrade-webhook-signature-test.ts supabase/tests/functions/brokerage-snaptrade-error-redaction-test.ts
  2. Run the FIFO matcher's pgTAP suite and open one failing-case assertion in snaptrade-shorts.test.sql: 
    cd src && npx supabase test db 2>&1 | grep -i 'snaptrade'
  3. Read brokerage/index.ts and list which endpoints a user JWT can reach. Then check the README table agrees.

Primary source

SnapTrade: Webhooks for the signature scheme and retry schedule, then the in-repo docs/WINRATE_TRACKING.md for why P&L is reconstructed rather than fetched.