> ## 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 ACCOUNTS TABLE

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

```mermaid theme={null}
User Connects → SnapTrade API → process-connection/route.ts
                                        ↓
                    Read account type from API: "401K"
                                        ↓
                    Map via SNAPTRADE_TO_PLAID_MAPPING
                                        ↓
                    Insert into snaptrade_accounts:
                    {
                      source_account_type: "401K"        (original)
                      source_type: "investment"           (mapped, locked)
                      source_subtype: "401k"              (mapped, locked)
                      type: "investment"                  (editable)
                      subtype: "401k"                     (editable)
                      account_type: "investment"          (auto-synced)
                    }
```

### **2. User Changes Type (UI Override)**

```mermaid theme={null}
User clicks Edit → Selects "Depository" + "Checking" → update-type/route.ts
                                                              ↓
                                        Update snaptrade_accounts:
                                        {
                                          type: "depository"          (changed)
                                          subtype: "checking"         (changed)
                                          account_type: "depository"  (auto-synced)
                                          
                                          source_account_type: "401K"     (unchanged!)
                                          source_type: "investment"        (unchanged!)
                                          source_subtype: "401k"           (unchanged!)
                                        }
```

### **3. Webhook Updates (Balances/Positions)**

```mermaid theme={null}
SnapTrade Webhook → webhook/route.ts → Update snaptrade_accounts
                                              ↓
                            Updates ONLY these fields:
                            - balance
                            - currency
                            - sync_status
                            - holdings_sync_completed
                            - transactions_sync_completed
                            - raw_response
                            
                            Does NOT touch type columns! ✅
```

### **4. Daily Snapshot Processing**

```mermaid theme={null}
Cron Job → process-daily-snapshot/route.ts
                    ↓
    Reads snaptrade_accounts (including type/subtype)
                    ↓
    Calls getEffectiveAccountType(account)
                    ↓
    Routes to correct calculation:
    - effectiveType === 'investment' → totalMarketValue
    - effectiveType === 'depository' → totalCash
    - effectiveType === 'loan'       → totalDebts
```

***

## Example Data Flow

### **Scenario: Fidelity 401(k) Account**

#### **Step 1: Initial Connection**

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

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

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

```javascript theme={null}
// 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:**

1. Always use `type`/`subtype` for current effective categorization
2. Use `source_*` columns for "Reset to Default" functionality
3. Use `getEffectiveAccountType()` for calculations
4. Keep `account_type` in sync with `type` for backward compatibility

### ❌ **DON'T:**

1. Never modify `source_account_type`, `source_type`, or `source_subtype`
2. Never use `account_type` directly - use `type` instead
3. Don't assume all SnapTrade accounts are investments
4. 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`

***

## Related Tables

* **`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)


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