Manual Accounts Ownership Logic Audit

Date: 2025-10-14
Status: ✅ CONSISTENT - All logic verified

Overview

This document audits all logic related to balance, total_balance, and ownership_percentage columns in the manual_accounts table.

Key Concepts

balance: The user’s actual portion (what they’re responsible for). This is the source of truth for calculations.
total_balance: The full balance before applying ownership percentage. Only populated when ownership_percentage < 100.
ownership_percentage: The percentage the user owns (e.g., 50.5%). Only populated when less than 100%.
100% ownership: Both total_balance and ownership_percentage are NULL.
Partial ownership: balance = total_balance × (ownership_percentage / 100)

Important Note

total_balance and ownership_percentage are NEW columns. Historical data will have NULL values, which correctly represents 100% ownership.

File-by-File Analysis

1. ✅ `/components/add-manual-account-dialog.tsx`

Lines 170-198: Account Creation Logic

const numericOwnershipPercentage = ownershipPercentage ? parseFloat(ownershipPercentage) : 100
const isPartialOwnership = accountType === 'loan' && numericOwnershipPercentage < 100
const numericTotalBalance = isPartialOwnership ? numericBalance : null
const finalOwnershipPercentage = isPartialOwnership ? numericOwnershipPercentage : null

const newAccount: NewManualAccount = {
  balance: numericBalance,  // User enters FULL balance, hook will recalculate
  ownership_percentage: finalOwnershipPercentage,  // NULL if 100%
  total_balance: numericTotalBalance,  // NULL if 100%
}

UI Display (Lines 413-424):

{ownershipPercentage && parseFloat(ownershipPercentage) < 100 && balance && (
  <p className='text-xs text-muted-foreground mt-1'>
    Your portion: ${(parseFloat(balance) * (parseFloat(ownershipPercentage) / 100)).toLocaleString()}
  </p>
)}

Status: ✅ CORRECT

User enters the FULL balance amount
Dialog sends full balance to backend
Hook recalculates user’s portion
UI shows preview of user’s portion

2. ✅ `/hooks/queries/use-manual-accounts.ts`

Add Account (Lines 165-175)

let adjustedBalance = newAccount.balance

if (newAccount.ownership_percentage && 
    newAccount.ownership_percentage < 100 && 
    newAccount.total_balance) {
  adjustedBalance = newAccount.total_balance * (newAccount.ownership_percentage / 100)
  console.log(`Calculating balance: ${newAccount.total_balance} × ${newAccount.ownership_percentage}% = ${adjustedBalance}`)
}

// Inserts with balance: adjustedBalance

Status: ✅ CORRECT

Calculates user’s portion from total_balance
If ownership is 100% (NULL), uses balance as-is

Update Account (Lines 228-262)

Handles three cases: Case 1: Removing ownership (both NULL)

const isRemovingOwnership = rest.ownership_percentage === null && rest.total_balance === null

Case 2: Setting/updating ownership < 100%

if (!isRemovingOwnership &&
    rest.ownership_percentage !== undefined && 
    rest.ownership_percentage !== null &&
    rest.ownership_percentage < 100 && 
    rest.total_balance) {
  adjustedBalance = rest.total_balance * (rest.ownership_percentage / 100)
}

Case 3: Updating balance when ownership exists

else if (!isRemovingOwnership && 
         rest.balance !== undefined && 
         rest.ownership_percentage === undefined) {
  // Fetch current ownership percentage
  const { data: currentAccount } = await supabase
    .from('manual_accounts')
    .select('ownership_percentage')
    .eq('id', id)
    .single()
  
  if (currentAccount?.ownership_percentage && currentAccount.ownership_percentage < 100) {
    // Recalculate total_balance: balance / (percentage / 100)
    adjustedTotalBalance = rest.balance / (currentAccount.ownership_percentage / 100)
  }
}

Status: ✅ CORRECT

Handles all three cases properly
Maintains consistency between balance and total_balance
Works with historical NULL values

3. ✅ `/components/edit-manual-account-dialog.tsx`

Display Logic (Lines 148-155)

const displayBalance = fullAccountData.ownership_percentage && 
                       fullAccountData.ownership_percentage < 100 &&
                       fullAccountData.total_balance
  ? fullAccountData.total_balance  // Show FULL balance for editing
  : fullAccountData.balance || 0   // Show user's portion

Status: ✅ CORRECT

Shows total_balance (full amount) when ownership < 100%
Shows balance (user’s portion) when ownership = 100% (NULL)
Works correctly with historical NULL values

Submit Logic (Lines 301-325)

const isPartialOwnership = accountType === 'loan' && numericOwnershipPercentage < 100
const numericTotalBalance = isPartialOwnership ? numericBalance : null
const finalOwnershipPercentage = isPartialOwnership ? numericOwnershipPercentage : null

const updatedAccount = {
  balance: numericBalance,  // Hook will adjust
  ownership_percentage: finalOwnershipPercentage,
  total_balance: numericTotalBalance,
}

Status: ✅ CORRECT

Follows same pattern as add dialog
Sets to NULL for 100% ownership

4. ✅ `/lib/loan-payments.ts`

Process Payment (Lines 246-264)

// Fetch current account to check for ownership
const { data: accountData } = await supabase
  .from('manual_accounts')
  .select('ownership_percentage, total_balance')
  .eq('uuid', loan.uuid)
  .single()

let updateData: any = { balance: newBalance }
if (accountData?.ownership_percentage && accountData.ownership_percentage < 100) {
  // Calculate what total_balance should be: balance / (percentage / 100)
  updateData.total_balance = newBalance / (accountData.ownership_percentage / 100)
  console.log(`Updating total_balance proportionally: ${newBalance} / (${accountData.ownership_percentage}% / 100) = ${updateData.total_balance}`)
}

Status: ✅ CORRECT

When loan payment reduces balance, proportionally reduces total_balance
Formula: total_balance = balance / (percentage / 100) is correct
Works with historical NULL values (doesn’t update if NULL)

5. ✅ `/app/api/cron/process-daily-snapshot/route.ts`

Manual Account Processing (Lines 1036-1120)

// Line 1040: Uses balance directly
const accountBalance = account.balance || 0

// Lines 1118-1119: Stores ownership data in snapshot
ownership_percentage: account.ownership_percentage,
total_balance: account.total_balance,

Status: ✅ CORRECT

Uses balance (user’s portion) for all net worth calculations
Stores ownership fields for historical tracking
Works correctly with historical NULL values

6. ✅ `/app/api/manual-transactions/route.ts`

Create Transaction (Lines 77-143)

// Fetch account with ownership fields
.select('uuid, account_name, account_type, balance, ownership_percentage, total_balance')

// Update balance
const newBalance = account.account_type === 'credit' 
  ? account.balance + Number(amount)
  : account.balance - Number(amount)

const updateData: any = { 
  balance: newBalance,
  last_synced_at: new Date().toISOString()
}

// If account has ownership percentage, update total_balance proportionally
if (account.ownership_percentage && account.ownership_percentage < 100) {
  updateData.total_balance = newBalance / (account.ownership_percentage / 100)
  console.log(`Transaction updated balance with ownership: ${newBalance} / (${account.ownership_percentage}% / 100) = ${updateData.total_balance}`)
}

Status: ✅ CORRECT

Updates balance based on transaction
Proportionally updates total_balance if ownership < 100%
Works with historical NULL values

7. ✅ `/app/api/manual-transactions/[id]/route.ts`

Update Transaction (Lines 31, 66-95)

// Fetch with ownership fields (line 31)
.select('*, manual_accounts(account_type, balance, ownership_percentage, total_balance)')

// Update balance based on amount difference (lines 70-84)
const newBalance = account.account_type === 'credit'
  ? account.balance + amountDifference
  : account.balance - amountDifference

const updateData: any = { 
  balance: newBalance,
  last_synced_at: new Date().toISOString()
}

if (account.ownership_percentage && account.ownership_percentage < 100) {
  updateData.total_balance = newBalance / (account.ownership_percentage / 100)
}

Delete Transaction (Lines 117-158)

// Reverse balance change (lines 144-158)
const newBalance = account.account_type === 'credit'
  ? account.balance - transaction.amount
  : account.balance + transaction.amount

const updateData: any = { 
  balance: newBalance,
  last_synced_at: new Date().toISOString()
}

if (account.ownership_percentage && account.ownership_percentage < 100) {
  updateData.total_balance = newBalance / (account.ownership_percentage / 100)
}

Status: ✅ CORRECT

Edit: Updates balance by difference, adjusts total_balance proportionally
Delete: Reverses balance change, adjusts total_balance proportionally
Works with historical NULL values

8. ✅ `/app/api/manual-transactions/batch/route.ts`

Batch Import (Lines 46-158)

// Fetch account with ownership fields (line 48)
.select('uuid, account_type, balance, ownership_percentage, total_balance')

// Update balance for all transactions (lines 126-147)
let newBalance: number
if (account.account_type === 'credit') {
  newBalance = account.balance + totalAmountChange
} else {
  newBalance = account.balance - totalAmountChange
}

const updateData: any = { 
  balance: newBalance,
  last_synced_at: new Date().toISOString()
}

if (account.ownership_percentage && account.ownership_percentage < 100) {
  updateData.total_balance = newBalance / (account.ownership_percentage / 100)
  console.log(`Batch import updated balance with ownership: ${newBalance} / (${account.ownership_percentage}% / 100) = ${updateData.total_balance}`)
}

Status: ✅ CORRECT

Accumulates all transaction amounts
Updates balance and total_balance proportionally
Works with historical NULL values

9. ✅ `/app/liabilities/page.tsx`

Account Filtering (Lines 177-184)

const liabilityAccounts: LiabilityAccount[] = accounts
  .filter((account) => {
    const balance = account.balance_current || 0
    return (
      (account.type === 'credit' || account.type === 'loan') &&
      balance > 0
    )
  })

Status: ✅ CORRECT

Uses balance_current for Plaid accounts (not manual accounts)
Manual accounts are handled by useAccounts hook which provides proper balances

10. ✅ `/lib/financial-data-cache.ts`

Manual Accounts Query (Line 294)

supabase
  .from('manual_accounts')
  .select('account_name, account_type, account_subtype, balance, created_at, last_synced_at, institution_name, original_amount, interest_rate, payment_amount, payment_frequency, payment_due_day, term_months, origination_date, maturity_date, ownership_percentage, total_balance')

Balance Usage (Lines 352-360)

if (manualAccounts) {
  manualAccounts.forEach((account) => {
    if (account.account_type === 'investment' || account.account_type === 'retirement') {
      manualAccountsValue += account.balance || 0
    } else if (account.account_type === 'cash') {
      cashOnHand += account.balance || 0
    } else if (account.account_type === 'loan') {
      totalDebts += account.balance || 0
    }
  })
}

Status: ✅ CORRECT

Fetches ownership fields for context
Uses balance (user’s portion) for all calculations
Works correctly with historical NULL values

11. ✅ `/app/api/chat/pdf-export-tool.ts` (Financial Statement Generation)

Manual Investment Accounts (Lines 317-328)

let value = inv.balance_current !== undefined ? inv.balance_current :
            inv.balance !== undefined ? inv.balance : 0;

// For manual accounts with partial ownership, show the full balance
let ownershipInfo = '';
if (inv.source === 'manual' && inv.ownership_percentage && 
    inv.ownership_percentage < 100 && inv.total_balance) {
  value = inv.total_balance;  // Show full balance
  ownershipInfo = ` (${inv.ownership_percentage}% ownership)`;
}

// Display: "Account Name (50% ownership) - $100,000"

Manual Loan Accounts (Lines 902-912)

let balance = account.balance || 0;
let ownershipInfo = '';

// For partial ownership, show the full balance
if (account.ownership_percentage && account.ownership_percentage < 100 && account.total_balance) {
  balance = account.total_balance;  // Show full balance
  ownershipInfo = ` (${account.ownership_percentage}% ownership)`;
}

totalLoans += Math.abs(balance);
// Display: "Mortgage (50% ownership) - $400,000"

Manual Credit Cards (Lines 817-827)

let balance = account.balance || 0;
let ownershipInfo = '';

// For partial ownership, show the full balance
if (account.ownership_percentage && account.ownership_percentage < 100 && account.total_balance) {
  balance = account.total_balance;  // Show full balance
  ownershipInfo = ` (${account.ownership_percentage}% ownership)`;
}

totalCreditCards += Math.abs(balance);
// Display: "Credit Card (50% ownership) - $10,000"

Cash Accounts (Lines 263-276)

let balance = account.balance_current !== undefined ? account.balance_current : 
              account.balance !== undefined ? account.balance : 0;

// For manual accounts with partial ownership, show the full balance
let ownershipInfo = '';
if (account.source === 'manual' && account.ownership_percentage && 
    account.ownership_percentage < 100 && account.total_balance) {
  balance = account.total_balance;  // Show full balance
  ownershipInfo = ` (${account.ownership_percentage}% ownership)`;
}

// Display: "Joint Checking (25% ownership) - $100,000"

Status: ✅ CORRECT & CONSISTENT

Displays: total_balance when ownership_percentage < 100%
Shows: Ownership percentage in account name (e.g., “50% ownership”)
Consistent with: Add/Edit dialogs where users enter/view full balance
Purpose: Financial statements show full liability/asset amounts (what banks want)
Net Worth: Still calculated correctly using balance in getFinancialMetrics
Works with historical NULL values (100% ownership - no annotation shown)

Summary

✅ All Logic is Consistent

Key Findings:

Balance Column: Always contains the user’s actual portion across all files
Total Balance: Only populated when ownership < 100%, NULL means 100%
Ownership Percentage: Only populated when < 100%, NULL means 100%
Historical Data: NULL values correctly represent 100% ownership everywhere
Calculations: All balance updates proportionally update total_balance when ownership < 100%

Formula Consistency

When setting ownership:

balance = total_balance × (ownership_percentage / 100)

When updating balance:

total_balance = balance / (ownership_percentage / 100)

Both formulas are algebraically equivalent and consistently applied across all files.

Recommendations

✅ Current Implementation is Solid

The current implementation correctly:

Uses balance as the source of truth for calculations
Maintains total_balance for reference/display when ownership < 100%
Treats NULL values as 100% ownership
Proportionally updates both fields when balance changes

Optional: Migration Script for Display

If you want to backfill total_balance for display purposes on old records where ownership was intended to be partial:

-- Only if needed - current NULL values correctly represent 100% ownership
-- This would only be needed if historical records should have had partial ownership
UPDATE manual_accounts
SET 
  total_balance = balance,
  ownership_percentage = 100
WHERE 
  ownership_percentage IS NULL 
  AND total_balance IS NULL
  AND account_type = 'loan';

Note: This is NOT necessary - NULL correctly means 100% ownership.

Conclusion

Status: ✅ VERIFIED - All logic is consistent and correct. All files properly handle:

New accounts with partial ownership
Historical accounts with NULL values (100% ownership)
Balance updates (transactions, payments, edits)
Display logic (showing full balance vs user’s portion)
Net worth calculations (always using user’s portion)

Recent Updates (2025-10-14)

Fixed: /app/api/chat/pdf-export-tool.ts to display total_balance with ownership percentage when ownership_percentage < 100% Result: Financial statement PDFs now show:

Full balance (what banks want to see)
Ownership percentage clearly noted (e.g., “50% ownership”)
Consistent with Add/Edit dialog behavior

Example:

Joint mortgage: Shows “ $400,000 (50% ownership)" instead of "$ 200,000”
Net worth still correctly uses $200,000 (user’s portion) via getFinancialMetrics

​Manual Accounts Ownership Logic Audit

​Overview

​Key Concepts

​Important Note

​File-by-File Analysis

​1. ✅ /components/add-manual-account-dialog.tsx

​2. ✅ /hooks/queries/use-manual-accounts.ts

​Add Account (Lines 165-175)

​Update Account (Lines 228-262)

​3. ✅ /components/edit-manual-account-dialog.tsx

​Display Logic (Lines 148-155)

​Submit Logic (Lines 301-325)

​4. ✅ /lib/loan-payments.ts

​Process Payment (Lines 246-264)

​5. ✅ /app/api/cron/process-daily-snapshot/route.ts

​Manual Account Processing (Lines 1036-1120)

​6. ✅ /app/api/manual-transactions/route.ts

​Create Transaction (Lines 77-143)

​7. ✅ /app/api/manual-transactions/[id]/route.ts

​Update Transaction (Lines 31, 66-95)

​Delete Transaction (Lines 117-158)

​8. ✅ /app/api/manual-transactions/batch/route.ts

​Batch Import (Lines 46-158)

​9. ✅ /app/liabilities/page.tsx

​Account Filtering (Lines 177-184)

​10. ✅ /lib/financial-data-cache.ts

​Manual Accounts Query (Line 294)

​Balance Usage (Lines 352-360)

​11. ✅ /app/api/chat/pdf-export-tool.ts (Financial Statement Generation)

​Manual Investment Accounts (Lines 317-328)

​Manual Loan Accounts (Lines 902-912)

​Manual Credit Cards (Lines 817-827)

​Cash Accounts (Lines 263-276)

​Summary

​✅ All Logic is Consistent

​Formula Consistency

​Recommendations

​✅ Current Implementation is Solid

​Optional: Migration Script for Display

​Conclusion

​Recent Updates (2025-10-14)

Manual Accounts Ownership Logic Audit

Overview

Key Concepts

Important Note

File-by-File Analysis

1. ✅ `/components/add-manual-account-dialog.tsx`

2. ✅ `/hooks/queries/use-manual-accounts.ts`

Add Account (Lines 165-175)

Update Account (Lines 228-262)

3. ✅ `/components/edit-manual-account-dialog.tsx`

Display Logic (Lines 148-155)

Submit Logic (Lines 301-325)

4. ✅ `/lib/loan-payments.ts`

Process Payment (Lines 246-264)

5. ✅ `/app/api/cron/process-daily-snapshot/route.ts`

Manual Account Processing (Lines 1036-1120)

6. ✅ `/app/api/manual-transactions/route.ts`

Create Transaction (Lines 77-143)

7. ✅ `/app/api/manual-transactions/[id]/route.ts`

Update Transaction (Lines 31, 66-95)

Delete Transaction (Lines 117-158)

8. ✅ `/app/api/manual-transactions/batch/route.ts`

Batch Import (Lines 46-158)

9. ✅ `/app/liabilities/page.tsx`

Account Filtering (Lines 177-184)

10. ✅ `/lib/financial-data-cache.ts`

Manual Accounts Query (Line 294)

Balance Usage (Lines 352-360)

11. ✅ `/app/api/chat/pdf-export-tool.ts` (Financial Statement Generation)

Manual Investment Accounts (Lines 317-328)

Manual Loan Accounts (Lines 902-912)

Manual Credit Cards (Lines 817-827)

Cash Accounts (Lines 263-276)

Summary

✅ All Logic is Consistent

Formula Consistency

Recommendations

✅ Current Implementation is Solid

Optional: Migration Script for Display

Conclusion

Recent Updates (2025-10-14)