SnapTrade Accounts Table Documentation

Table: `snaptrade_accounts`

Complete documentation for the SnapTrade accounts table structure, data flow, and column usage.

Column Structure

Identity & Relationships

Column	Type	Description	Mutable?	Set By
`id`	bigint	Primary key	❌	Database
`clerk_id`	text	User ID from Clerk auth	❌	process-connection
`snaptrade_user_id`	text	SnapTrade user ID	❌	process-connection
`snaptrade_connection_id`	text	SnapTrade connection/authorization ID	❌	process-connection
`snaptrade_account_id`	text	Unique account ID from SnapTrade	❌	process-connection

Account Information

Column	Type	Description	Mutable?	Set By
`broker_name`	text	Institution name (e.g., “Fidelity”)	✅	webhook updates
`account_name`	text	Account display name	✅	webhook updates
`account_number`	text	Masked account number	✅	webhook updates
`custom_name`	text	User-defined custom name	✅	User via UI
`is_active`	boolean	Account is active/connected	✅	webhook/refresh
`is_hidden`	boolean	User hid account from view	✅	User via UI

Account Type & Categorization ⭐ NEW SYSTEM

Column	Type	Description	Mutable?	Set By	Example
`account_type`	text	Current effective type (for calculations)	✅	Auto-synced from `type`	`"investment"`
`type`	text	User’s current type choice	✅	User override OR default	`"investment"`
`subtype`	text	User’s current subtype choice	✅	User override OR default	`"401k"`
`source_account_type`	text	Original SnapTrade type (immutable)	❌	process-connection	`"401K"`
`source_type`	text	Original mapped Plaid type (immutable)	❌	process-connection	`"investment"`
`source_subtype`	text	Original mapped Plaid subtype (immutable)	❌	process-connection	`"401k"`

Type Column Relationships:

SnapTrade API          →  source_account_type (immutable)
      ↓ (mapped via SNAPTRADE_TO_PLAID_MAPPING)
Initial Plaid-style    →  source_type / source_subtype (immutable)
      ↓ (user can override)
Current User Choice    →  type / subtype (editable)
      ↓ (auto-synced for backward compatibility)
Calculation Field      →  account_type (mirrors type)

Financial Data

Column	Type	Description	Mutable?	Set By
`balance`	numeric	Total account value (cash + positions)	✅	webhook updates
`currency`	text	Primary currency (usually “USD”)	✅	webhook updates
`raw_response`	jsonb	Full API response from SnapTrade	✅	webhook updates

Sync Status

Column	Type	Description	Mutable?	Set By
`last_synced_at`	timestamp	Last successful sync time	✅	webhook/refresh
`sync_status`	text	Current sync state	✅	webhook/refresh
`last_sync_result`	text	Human-readable sync result	✅	webhook
`holdings_sync_completed`	boolean	Holdings data is complete	✅	webhook
`holdings_last_sync`	timestamp	Last holdings sync time	✅	webhook
`transactions_sync_completed`	boolean	Transaction history is complete	✅	webhook
`transactions_last_sync`	timestamp	Last transaction sync time	✅	webhook
`transactions_first_date`	date	Earliest transaction date available	✅	webhook

Metadata

Column	Type	Description	Mutable?	Set By
`created_at`	timestamp	Account record creation time	❌	process-connection
`updated_at`	timestamp	Last update time	✅	Any update

Data Flow

1. Account Creation (First Connection)

2. User Changes Type (UI Override)

3. Webhook Updates (Balances/Positions)

4. Daily Snapshot Processing

Example Data Flow

Scenario: Fidelity 401(k) Account

Step 1: Initial Connection

// SnapTrade API returns:
{
  id: "snap_123",
  name: "Fidelity 401(k)",
  type: "401K",  // SnapTrade format
  balance: { total: { amount: 50000 } }
}

// Stored in DB:
{
  snaptrade_account_id: "snap_123",
  account_name: "Fidelity 401(k)",
  source_account_type: "401K",      // Original from SnapTrade
  source_type: "investment",         // Mapped
  source_subtype: "401k",            // Mapped
  type: "investment",                // Current (can be changed)
  subtype: "401k",                   // Current (can be changed)
  account_type: "investment",        // For calculations (mirrors type)
  balance: 50000
}

Step 2: User Changes to “Checking” (Wrong, but allowed)

// User overrides via UI
// update-type/route.ts updates:
{
  type: "depository",           // Changed by user
  subtype: "checking",          // Changed by user
  account_type: "depository",   // Auto-synced
  
  source_account_type: "401K",      // Still original
  source_type: "investment",         // Still original
  source_subtype: "401k"             // Still original
}

Step 3: Daily Snapshot Calculation

// process-daily-snapshot reads:
const effectiveType = getEffectiveAccountType(account)
// Returns: "depository" (respects user's choice)

// Calculation:
if (effectiveType === 'depository') {
  totalCash += account.cash_balance  // $50k goes to cash! ✅
}
// Investment calculation skipped

Step 4: User Realizes Mistake, Resets

// User clicks "Reset to Default" in UI
// update-type/route.ts copies source back to current:
{
  type: "investment",           // Reset from source_type
  subtype: "401k",              // Reset from source_subtype
  account_type: "investment",   // Auto-synced
  
  source_account_type: "401K",      // Still original
  source_type: "investment",         // Still original
  source_subtype: "401k"             // Still original
}

Important Rules

✅ DO:

Always use type/subtype for current effective categorization
Use source_* columns for “Reset to Default” functionality
Use getEffectiveAccountType() for calculations
Keep account_type in sync with type for backward compatibility

❌ DON’T:

Never modify source_account_type, source_type, or source_subtype
Never use account_type directly - use type instead
Don’t assume all SnapTrade accounts are investments
Don’t update type columns in webhook handler

Migration Notes

Before Migration:

Single column: account_type = "401K" (SnapTrade format)
No subtype information
No way to override or reset

After Migration:

Six columns for complete tracking
User can override type/subtype
“Reset to Default” functionality
Backward compatibility maintained via account_type auto-sync

Migration Script: sql-queries/add-account-type-override-support.sql

snaptrade_account_balances - Currency balances (USD, CAD, etc.)
snaptrade_account_positions - Holdings/securities positions
snaptrade_account_daily_snapshots - Historical snapshots (includes type/subtype for tracking changes)
snaptrade_institutions - Broker/institution information

Code References

Creates accounts:

app/api/snaptrade/process-connection/route.ts

Updates types:

app/api/accounts/update-type/route.ts

Updates balances (doesn’t touch types):

app/api/snaptrade/webhook/route.ts

Daily calculations:

app/api/cron/process-daily-snapshot/route.ts

Frontend display:

hooks/queries/use-accounts.ts

Type mapping:

lib/account-type-mappings.ts (SNAPTRADE_TO_PLAID_MAPPING)

Product Guide

Account

Help

SNAPTRADE ACCOUNTS TABLE

SnapTrade Accounts Table Documentation

Table: `snaptrade_accounts`

Column Structure

Identity & Relationships

Account Information

Account Type & Categorization ⭐ NEW SYSTEM

Type Column Relationships:

Financial Data

Sync Status

Metadata

Data Flow

1. Account Creation (First Connection)

2. User Changes Type (UI Override)

3. Webhook Updates (Balances/Positions)

4. Daily Snapshot Processing

Example Data Flow

Scenario: Fidelity 401(k) Account

Step 1: Initial Connection

Step 2: User Changes to “Checking” (Wrong, but allowed)

Step 3: Daily Snapshot Calculation

Step 4: User Realizes Mistake, Resets

Important Rules

✅ DO:

❌ DON’T:

Migration Notes

Code References

​SnapTrade Accounts Table Documentation

​Table: snaptrade_accounts

​Column Structure

​Identity & Relationships

​Account Information

​Account Type & Categorization ⭐ NEW SYSTEM

​Type Column Relationships:

​Financial Data

​Sync Status

​Metadata

​Data Flow

​1. Account Creation (First Connection)

​2. User Changes Type (UI Override)

​3. Webhook Updates (Balances/Positions)

​4. Daily Snapshot Processing

​Example Data Flow

​Scenario: Fidelity 401(k) Account

​Step 1: Initial Connection

​Step 2: User Changes to “Checking” (Wrong, but allowed)

​Step 3: Daily Snapshot Calculation

​Step 4: User Realizes Mistake, Resets

​Important Rules

​✅ DO:

​❌ DON’T:

​Migration Notes

​Related Tables

​Code References

SnapTrade Accounts Table Documentation

Table: `snaptrade_accounts`

Column Structure

Identity & Relationships

Account Information

Account Type & Categorization ⭐ NEW SYSTEM

Type Column Relationships:

Financial Data

Sync Status

Metadata

Data Flow

1. Account Creation (First Connection)

2. User Changes Type (UI Override)

3. Webhook Updates (Balances/Positions)

4. Daily Snapshot Processing

Example Data Flow

Scenario: Fidelity 401(k) Account

Step 1: Initial Connection

Step 2: User Changes to “Checking” (Wrong, but allowed)

Step 3: Daily Snapshot Calculation

Step 4: User Realizes Mistake, Resets

Important Rules

✅ DO:

❌ DON’T:

Migration Notes

Related Tables

Code References