Database & Storage

// apps/api/src/services/plaidService.ts:29
const accessTokenStore: Map<string, string> = new Map()

export function storeAccessToken(userId: string, accessToken: string): void {
  accessTokenStore.set(userId, accessToken)
}

export function getAccessToken(userId: string): string | undefined {
  return accessTokenStore.get(userId)
}

-- Users table
CREATE TABLE users (
  user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Plaid connections (encrypted tokens)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE plaid_items (
  item_id VARCHAR(255) PRIMARY KEY,
  user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
  access_token_encrypted BYTEA NOT NULL,  -- Encrypted with AES-256
  institution_id VARCHAR(255),
  institution_name VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW(),
  last_synced TIMESTAMP
);

-- Financial profiles
CREATE TABLE financial_profiles (
  user_id UUID PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
  liquid_assets DECIMAL(12, 2),
  credit_debt DECIMAL(12, 2),
  loan_debt DECIMAL(12, 2),
  monthly_income DECIMAL(12, 2),
  monthly_spending DECIMAL(12, 2),
  spending_by_category JSONB,
  spending_volatility DECIMAL(5, 4),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Goals and simulation results
CREATE TABLE goals (
  goal_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
  goal_text TEXT,
  goal_type VARCHAR(50),
  target_amount DECIMAL(12, 2),
  timeline_months INTEGER,
  success_probability DECIMAL(5, 4),
  median_outcome DECIMAL(12, 2),
  simulation_results JSONB,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Plaid accounts
CREATE TABLE plaid_accounts (
  account_id VARCHAR(255) PRIMARY KEY,
  user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
  item_id VARCHAR(255) REFERENCES plaid_items(item_id) ON DELETE CASCADE,
  account_type VARCHAR(50),
  account_subtype VARCHAR(50),
  name VARCHAR(255),
  balance DECIMAL(12, 2),
  account_data JSONB,
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_plaid_accounts_user ON plaid_accounts(user_id);
CREATE INDEX idx_goals_user ON goals(user_id);

// users collection
{
  _id: ObjectId,
  email: String,
  createdAt: Date,
  updatedAt: Date
}

// plaidItems collection
{
  _id: ObjectId,
  userId: ObjectId,
  itemId: String,
  accessTokenEncrypted: String,  // Must be encrypted
  institutionId: String,
  institutionName: String,
  createdAt: Date,
  lastSynced: Date
}

// financialProfiles collection
{
  _id: ObjectId,
  userId: ObjectId,
  liquidAssets: Number,
  creditDebt: Number,
  loanDebt: Number,
  monthlyIncome: Number,
  monthlySpending: Number,
  spendingByCategory: {
    "Food and Drink": 450.00,
    "Travel": 200.00,
    ...
  },
  spendingVolatility: Number,
  updatedAt: Date
}

// goals collection
{
  _id: ObjectId,
  userId: ObjectId,
  goalText: String,
  goalType: String,
  targetAmount: Number,
  timelineMonths: Number,
  successProbability: Number,
  medianOutcome: Number,
  simulationResults: {
    percentiles: { p10: ..., p25: ..., p50: ..., p75: ..., p90: ... },
    assumptions: { ... },
    ...
  },
  createdAt: Date,
  updatedAt: Date
}

// accounts collection
{
  _id: ObjectId,
  userId: ObjectId,
  itemId: String,
  accountId: String,  // Plaid account ID
  accountType: String,
  accountSubtype: String,
  name: String,
  balance: Number,
  accountData: {  // Full account details
    // Type-specific fields
  },
  updatedAt: Date
}

import { createClient } from 'redis'

const redis = createClient({ url: process.env.REDIS_URL })

// Cache Plaid account data (expires in 1 hour)
await redis.setEx(
  `plaid:accounts:${userId}`,
  3600,
  JSON.stringify(accounts)
)

// Retrieve cached data
const cached = await redis.get(`plaid:accounts:${userId}`)
if (cached) return JSON.parse(cached)

npm install pg
npm install --save-dev @types/pg

npm install mongodb

// apps/api/src/services/databaseService.ts
import { Pool } from 'pg'
import crypto from 'crypto'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : undefined,
})

// Encryption helpers
const ENCRYPTION_KEY = process.env.ENCRYPTION_KEY // Must be 32 bytes
const ALGORITHM = 'aes-256-cbc'

function encrypt(text: string): { encrypted: string; iv: string } {
  const iv = crypto.randomBytes(16)
  const cipher = crypto.createCipheriv(ALGORITHM, Buffer.from(ENCRYPTION_KEY, 'hex'), iv)
  let encrypted = cipher.update(text)
  encrypted = Buffer.concat([encrypted, cipher.final()])
  return { encrypted: encrypted.toString('hex'), iv: iv.toString('hex') }
}

function decrypt(encrypted: string, iv: string): string {
  const decipher = crypto.createDecipheriv(
    ALGORITHM,
    Buffer.from(ENCRYPTION_KEY, 'hex'),
    Buffer.from(iv, 'hex')
  )
  let decrypted = decipher.update(Buffer.from(encrypted, 'hex'))
  decrypted = Buffer.concat([decrypted, decipher.final()])
  return decrypted.toString()
}

// Store Plaid access token (encrypted)
export async function storeAccessToken(
  userId: string,
  itemId: string,
  accessToken: string,
  institutionId: string,
  institutionName: string
): Promise<void> {
  const { encrypted, iv } = encrypt(accessToken)
  
  await pool.query(
    `INSERT INTO plaid_items (item_id, user_id, access_token_encrypted, access_token_iv, institution_id, institution_name)
     VALUES ($1, $2, $3, $4, $5, $6)
     ON CONFLICT (item_id) DO UPDATE
     SET access_token_encrypted = $3, access_token_iv = $4, last_synced = NOW()`,
    [itemId, userId, encrypted, iv, institutionId, institutionName]
  )
}

// Retrieve Plaid access token (decrypted)
export async function getAccessToken(userId: string): Promise<string | null> {
  const result = await pool.query(
    `SELECT access_token_encrypted, access_token_iv FROM plaid_items WHERE user_id = $1 LIMIT 1`,
    [userId]
  )
  
  if (result.rows.length === 0) return null
  
  const { access_token_encrypted, access_token_iv } = result.rows[0]
  return decrypt(access_token_encrypted, access_token_iv)
}

// Store financial profile
export async function storeFinancialProfile(
  userId: string,
  profile: FinancialProfile
): Promise<void> {
  await pool.query(
    `INSERT INTO financial_profiles (user_id, liquid_assets, credit_debt, loan_debt, monthly_income, monthly_spending, spending_by_category, spending_volatility)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
     ON CONFLICT (user_id) DO UPDATE
     SET liquid_assets = $2, credit_debt = $3, loan_debt = $4, monthly_income = $5, monthly_spending = $6, spending_by_category = $7, spending_volatility = $8, updated_at = NOW()`,
    [
      userId,
      profile.liquidAssets,
      profile.creditDebt,
      profile.loanDebt,
      profile.monthlyIncome,
      profile.monthlySpending,
      JSON.stringify(profile.spendingByCategory),
      profile.spendingVolatility,
    ]
  )
}

// Save simulation result
export async function saveSimulationResult(
  userId: string,
  goal: Goal,
  results: SimulationResults
): Promise<string> {
  const result = await pool.query(
    `INSERT INTO goals (user_id, goal_text, goal_type, target_amount, timeline_months, success_probability, median_outcome, simulation_results)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
     RETURNING goal_id`,
    [
      userId,
      goal.goalText,
      goal.goalType,
      goal.targetAmount,
      goal.timelineMonths,
      results.successProbability,
      results.medianOutcome,
      JSON.stringify(results),
    ]
  )
  
  return result.rows[0].goal_id
}

# Database connection
DATABASE_URL=postgresql://user:password@host:5432/drift

# Encryption key (generate with: openssl rand -hex 32)
ENCRYPTION_KEY=your_64_character_hex_key_here

// Before (in-memory)
import { storeAccessToken } from './plaidService'
storeAccessToken(userId, accessToken)

// After (database)
import { storeAccessToken } from './databaseService'
storeAccessToken(userId, itemId, accessToken, institutionId, institutionName)

// Export user data (GDPR right to access)
router.get('/api/user/export', async (req, res) => {
  const userData = await exportUserData(req.userId)
  res.json(userData)
})

// Delete user data (GDPR right to erasure)
router.delete('/api/user/account', async (req, res) => {
  await deleteUserData(req.userId)
  res.json({ success: true })
})

-- Speed up user lookups
CREATE INDEX idx_plaid_items_user ON plaid_items(user_id);
CREATE INDEX idx_goals_user ON goals(user_id);
CREATE INDEX idx_accounts_user ON plaid_accounts(user_id);

-- Speed up recent goals query
CREATE INDEX idx_goals_created ON goals(user_id, created_at DESC);

// Use connection pooling for PostgreSQL
const pool = new Pool({
  max: 20,                    // Maximum connections
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 2000,
})

// Cache frequently accessed data in Redis
const CACHE_TTL = 3600 // 1 hour

export async function getFinancialProfile(userId: string) {
  // Check cache first
  const cached = await redis.get(`profile:${userId}`)
  if (cached) return JSON.parse(cached)
  
  // Fetch from database
  const profile = await db.query('SELECT * FROM financial_profiles WHERE user_id = $1', [userId])
  
  // Cache result
  await redis.setEx(`profile:${userId}`, CACHE_TTL, JSON.stringify(profile))
  
  return profile
}