​

Tax Treatment Feature Implementation

​

Overview

​

Changes Made

​

1. Database Schema (sql-queries/add-tax-treatment-support.sql)

• Added tax_treatment column to accounts, manual_accounts, and snaptrade_accounts tables
• Added CHECK constraint to ensure values are: 'taxable', 'tax_deferred', or 'tax_free'
• Backfilled existing accounts with smart defaults based on account type/subtype
• Verification queries included at bottom of file

​

2. Utility Library (lib/tax-treatment-utils.ts)

• getDefaultTaxTreatment() - Determines smart default based on account type/subtype
• getTaxTreatmentLabel() - Human-readable labels for UI display
• getTaxTreatmentDescription() - Descriptions for UI tooltips/help text

• Tax Free: Roth IRA, Roth 401k, HSA, 529A
• Tax Deferred: Traditional IRA, 401k, 403b, SEP IRA, Pension
• Taxable: Brokerage, Checking, Savings, Money Market
• Null: Loans and Credit (not applicable)

​

3. TypeScript Interfaces Updated

• hooks/queries/use-accounts.ts - Added tax_treatment to Account interface
• hooks/queries/use-manual-accounts.ts - Added to all manual account interfaces
• Updated account mapping in fetchAccounts() to include tax_treatment field

​

4. UI Components

• Created new shadcn-style RadioGroup component using Radix UI

• Added tax treatment selector (radio buttons)
• Only shows for non-loan/credit accounts
• Pre-selects smart default on account load
• Saves tax_treatment on update

​

Installation Required

npm install @radix-ui/react-radio-group

​

Next Steps

​

1. Run Database Migration

-- Execute the SQL file in Supabase SQL Editor
-- File: sql-queries/add-tax-treatment-support.sql

​

2. Install Dependencies

npm install @radix-ui/react-radio-group

​

3. Add Tax Treatment to Other Account Edit Dialogs

​

4. Add Tax Treatment to Account Creation Flows

• Update manual account creation dialogs
• Add to SnapTrade connection flow (if applicable)
• Consider adding to Plaid connection confirmation

​

5. LLM Integration

When providing financial advice, consider the tax treatment of accounts:
- Taxable accounts: Subject to capital gains and income taxes
- Tax-deferred accounts: Taxes deferred until withdrawal (traditional IRA, 401k)
- Tax-free accounts: Qualified withdrawals are tax-free (Roth IRA, HSA)

Use this information for:
- Tax-efficient withdrawal strategies
- Asset location recommendations
- Tax loss harvesting opportunities
- Required Minimum Distribution (RMD) planning

​

6. Tax Estimate Feature (Future)

• Estimated tax on liquidation
• Optimal withdrawal order calculator
• Asset location analyzer (which assets belong in which account types)
• Tax projection for retirement planning

​

Usage Examples

​

Get Default Tax Treatment

import { getDefaultTaxTreatment } from '@/lib/tax-treatment-utils';

const treatment = getDefaultTaxTreatment({
  type: 'retirement',
  subtype: 'roth_ira'
});
// Returns: 'tax_free'

​

Get Human-Readable Labels

import { getTaxTreatmentLabel, getTaxTreatmentDescription } from '@/lib/tax-treatment-utils';

const label = getTaxTreatmentLabel('tax_deferred');
// Returns: 'Tax Deferred'

const description = getTaxTreatmentDescription('tax_deferred');
// Returns: 'Taxes deferred until withdrawal'

​

Calculate Liquidation Tax Estimate

import { calculateLiquidationTaxEstimate, getTaxTreatmentSummary } from '@/lib/tax-estimate-utils';

// From financial metrics
const estimate = calculateLiquidationTaxEstimate({
  taxableAssetsValue: financialMetrics.taxableAssetsValue,
  taxDeferredAssetsValue: financialMetrics.taxDeferredAssetsValue,
  taxFreeAssetsValue: financialMetrics.taxFreeAssetsValue,
  filingStatus: 'married_joint', // or 'single', 'married_separate', 'head_of_household'
});

// Get formatted summary for LLM
const summary = getTaxTreatmentSummary(estimate);

// Access detailed breakdown
console.log(`Total tax on liquidation: $${estimate.totalEstimatedTax}`);
console.log(`Net after tax: $${estimate.netAfterTax}`);
console.log(`Effective tax rate: ${estimate.effectiveTaxRate}%`);

​

Use in Financial Data Cache

const metrics = await getFinancialMetrics(clerkId, 'chat');

// Access tax treatment values
console.log(metrics.taxableAssetsValue);      // Total in taxable accounts
console.log(metrics.taxDeferredAssetsValue);  // Total in tax-deferred accounts
console.log(metrics.taxFreeAssetsValue);      // Total in tax-free accounts

​

Benefits

1. Tax Estimates: Calculate potential tax liability on account liquidation
2. Better LLM Context: Silvia can provide tax-aware advice and recommendations
3. Financial Planning: Proper asset location and tax-efficient strategies
4. Withdrawal Optimization: Determine optimal order to withdraw from accounts
5. User Education: Help users understand tax implications of their accounts

​

Files Modified/Created

​

Created:

• sql-queries/add-tax-treatment-support.sql - Database migration
• lib/tax-treatment-utils.ts - Smart default logic and utilities
• lib/tax-estimate-utils.ts - Tax estimate calculations
• components/ui/radio-group.tsx - RadioGroup component
• docs/TAX-TREATMENT-IMPLEMENTATION.md - This documentation

​

Modified:

• hooks/queries/use-accounts.ts - Added tax_treatment to Account interface and queries
• hooks/queries/use-manual-accounts.ts - Added tax_treatment to manual account interfaces
• components/edit-manual-account-dialog.tsx - Added tax treatment UI selector with PostHog tracking
• app/accounts/page.tsx - Added tax treatment UI selector with PostHog tracking, optimized save logic
• app/api/accounts/update-tax-treatment/route.ts - API endpoint to save tax_treatment (updates updated_at timestamp)
• app/api/accounts/update-type/route.ts - Updated to set updated_at timestamp on type/subtype changes
• app/api/connect-bank/route.ts - Set tax_treatment defaults on Plaid account creation
• app/api/relink/route.ts - Removed duplicate tax_treatment logic (uses migration defaults instead)
• app/api/snaptrade/process-connection/route.ts - Set tax_treatment defaults on SnapTrade account creation
• app/api/cron/process-daily-snapshot/route.ts - Save tax_treatment in daily snapshots for historical tracking
• lib/financial-data-cache.ts - Added tax_treatment to all queries, refactored getUnrealizedGainsTaxDetails() to use tax_treatment column (removed 30 lines of hardcoded logic)
• hooks/queries/use-unrealized-gains.ts - Refactored to use centralized getDefaultTaxTreatment() utility (removed 70 lines of duplicate logic)
• components/tax-estimate-card.tsx - Updated dialog description to reflect tax_treatment usage

​

Testing Checklist

• Install @radix-ui/react-radio-group dependency
• Run database migration
• Verify existing accounts have tax_treatment backfilled
• Test editing manual account and updating tax treatment
• Verify smart defaults are working correctly
• Test that loan/credit accounts don’t show tax treatment selector
• Test Plaid accounts include tax_treatment in queries
• Test SnapTrade accounts include tax_treatment in queries