​

Personal Information Tool - Financial Advisor Intake

​

Overview

​

Key Features

• Implicit Information Capture: The AI automatically detects and stores personal information mentioned during natural conversation
• No Permission Required: Information is stored silently without asking for permission, similar to how a human advisor would remember details
• Separate Database Table: Personal information is stored in a dedicated user_personal_info table with full audit trail
• Comprehensive Data Model: Covers demographics, residence, employment, financial profile, and tax information
• Type-Safe Schema: Uses PostgreSQL CHECK constraints to enforce data integrity
• Row-Level Security: Each user can only access their own personal information

​

Database Schema

​

Table: user_personal_info

​

Basic Demographics

• date_of_birth (DATE) - User’s birth date for age-based financial planning
• marital_status (ENUM) - single, married, divorced, widowed, domestic_partnership
• number_of_dependents (INTEGER) - Number of financial dependents

​

Residence Information

• primary_residence_street (TEXT)
• primary_residence_city (TEXT)
• primary_residence_state (TEXT)
• primary_residence_zip (TEXT)
• primary_residence_country (TEXT) - Defaults to ‘United States’
• home_ownership_status (ENUM) - own, rent, other

​

Employment & Income

• employment_status (ENUM) - employed_full_time, employed_part_time, self_employed, retired, unemployed, student
• occupation (TEXT)
• employer_name (TEXT)
• annual_gross_income (NUMERIC)
• expected_retirement_age (INTEGER)

​

Financial Profile

• risk_tolerance (ENUM) - conservative, moderate, aggressive, very_aggressive
• investment_experience (ENUM) - beginner, intermediate, advanced, expert
• investment_time_horizon (ENUM) - short_term, medium_term, long_term
• primary_financial_goals (JSONB) - Array of goals

​

Tax & Legal

• citizenship_status (ENUM) - us_citizen, permanent_resident, visa_holder, other
• tax_filing_status (ENUM) - single, married_filing_jointly, married_filing_separately, head_of_household, qualifying_widow
• state_of_tax_residency (TEXT)

​

Audit Trail

• created_at (TIMESTAMPTZ) - When record was created
• updated_at (TIMESTAMPTZ) - When record was last updated (auto-updated via trigger)
• is_completed (BOOLEAN) - Whether intake form is complete

​

AI Tools

​

getPersonalInfo()

{
  success: boolean
  hasData: boolean
  data?: {
    // All personal info fields
  }
  message?: string
  error?: string
}

​

updatePersonalInfo()

// Update marital status
updatePersonalInfo({ marital_status: 'married' })

// Update multiple fields
updatePersonalInfo({
  primary_residence_city: 'San Francisco',
  primary_residence_state: 'California',
  occupation: 'Software Engineer',
  annual_gross_income: 150000
})

{
  success: boolean
  message?: string
  updatedFields?: string[]
  error?: string
}

​

System Prompt Integration

1. Capture information naturally when users mention personal details
2. Store information silently without announcing or asking permission
3. Never make it feel like a form - let information emerge organically
4. Remember details like a human advisor would

​

Examples of Implicit Capture

​

Implementation Details

​

File Locations

1. SQL Schema: docs/user-personal-info-schema.sql
2. Tool Implementation: app/api/chat/tools.ts (lines for getPersonalInfo and updatePersonalInfo)
3. System Prompt: app/api/chat/system-prompt.ts (Personal Information Management section)

​

Security Features

1. Row-Level Security (RLS): Users can only access their own records
2. Foreign Key Constraint: Links to all_users table via clerk_id
3. Cascade Delete: Automatically removes personal info when user account is deleted
4. CHECK Constraints: Validates enum values at database level
5. Audit Trail: Automatic timestamps via PostgreSQL trigger

​

Data Validation

• All enum fields have CHECK constraints in the database
• Zod validation in the AI tools layer
• Age constraints (e.g., retirement age between 0-120)
• Income validation (must be non-negative)
• Dependent count validation (must be non-negative)

​

Usage Patterns

​

For Financial Planning

• Age-based recommendations: Retirement planning based on current age and target retirement age
• Family-aware guidance: Considers dependents for tax and savings strategies
• Location-specific advice: State tax implications, cost of living adjustments
• Income-appropriate suggestions: Investment recommendations scaled to income level
• Risk-aligned portfolios: Asset allocation matching stated risk tolerance

​

For Conversational Context

• “Based on your goal to retire at 60…”
• “Given your conservative risk tolerance…”
• “Considering your family of four…”
• “As a high-income earner in California…”

​

Privacy Considerations

1. User Consent: Implied through using the financial advisory service
2. Data Minimization: Only captures information relevant to financial planning
3. Access Control: Strong RLS policies prevent unauthorized access
4. Encryption: Data encrypted at rest (Supabase default)
5. Deletion: Automatically deleted when user closes account

​

Future Enhancements

1. Explicit Intake Form: Optional UI for users to review/edit stored information
2. Completeness Indicator: Show users what information has been captured
3. Privacy Dashboard: Let users see and manage stored personal information
4. Export Capability: Allow users to export their personal data
5. Additional Fields: Healthcare status, estate planning details, beneficiaries
6. Historical Tracking: Version history of personal information changes

​

Testing Checklist

• Run SQL migration to create user_personal_info table
• Verify RLS policies are active and working
• Test getPersonalInfo() with no existing data
• Test updatePersonalInfo() with new user (INSERT path)
• Test updatePersonalInfo() with existing user (UPDATE path)
• Test partial updates (only some fields)
• Test with invalid enum values (should be rejected)
• Test with invalid numeric values (should be rejected)
• Verify timestamp auto-updates work
• Test cascade delete when user account is deleted
• Verify users cannot access other users’ data

​

Example Conversation Flow

User: "Hi! I'm 35 and married with two kids. We live in San Francisco."

AI: [Silently calls updatePersonalInfo with:]
{
  date_of_birth: "1989-10-20", // Calculated from age
  marital_status: "married",
  number_of_dependents: 2,
  primary_residence_city: "San Francisco",
  primary_residence_state: "California"
}

AI Response: "Hi! It's great to meet you. As a family of four in San 
Francisco, you're navigating one of the most expensive housing markets 
in the country. Let's talk about your financial goals..."

[Later in conversation]

User: "How much should I be saving for my kids' college?"

AI: [Has context from stored info: 2 dependents, San Francisco resident]
"With two children and living in California, here's what you should 
consider for college savings..."

​

Deployment Steps

1. Deploy SQL Migration:
   # Run the migration in Supabase SQL Editor or via migration tool
   cat docs/user-personal-info-schema.sql | supabase db execute
   
2. Deploy Code Changes:
   • app/api/chat/tools.ts - Tool implementations
   • app/api/chat/system-prompt.ts - AI instructions
3. Verify Deployment:
   • Test with a sample conversation
   • Check database to confirm data is being stored
   • Verify RLS policies are protecting data
4. Monitor:
   • Watch for errors in tool execution
   • Monitor personal info capture rate
   • Check for any privacy concerns from users

​

Support & Maintenance

• Questions: Contact the engineering team
• Bug Reports: Use the standard bug reporting process
• Feature Requests: Submit through product management