Money domain erd

Money Domain Entity Relationship Diagram

Overview

The Money domain provides comprehensive personal finance management capabilities including account tracking, transaction management, budgeting, and financial reporting. The schema follows Clean Architecture principles with proper separation of concerns and data integrity.

Entity Relationship Diagram

Table Descriptions

money_accounts

Stores user financial accounts (checking, savings, credit, etc.)

• Primary Key: id (UUID)
• Foreign Keys: user_id → profiles(id)
• Indexes: user_id, type, is_active, user_id+is_active
• Constraints:
  • type must be one of: checking, savings, credit, cash, investment, loan, other
  • color must be valid hex color (#RRGGBB)
• Triggers:
  • update_money_accounts_updated_at - Updates timestamp on modification
• Security: Row Level Security enabled, users can only access their own accounts

money_categories

Hierarchical categorization system for transactions

• Primary Key: id (UUID)
• Foreign Keys:
  • user_id → profiles(id) [nullable for system categories]
  • parent_id → money_categories(id) [self-referential]
• Indexes: user_id, type, parent_id, is_system
• Constraints:
  • type must be: income or expense
  • System categories have no user_id, user categories require user_id
• Special Features:
  • Pre-loaded system categories for common expense/income types
  • Supports hierarchical structure with parent-child relationships
• Security: System categories visible to all, user categories restricted by RLS

money_transactions

Core transaction records for all financial activities

• Primary Key: id (UUID)
• Foreign Keys:
  • user_id → profiles(id)
  • account_id → money_accounts(id)
  • to_account_id → money_accounts(id) [for transfers]
  • category_id → money_categories(id)
  • recurring_id → money_recurring_transactions(id)
• Indexes:
  • Composite: user_id+date, account_id+date, user_id+category_id+date
  • Partial: recent transactions (last 90 days), expenses only, income only
  • Full-text search on description, notes, tags
• Constraints:
  • type must be: income, expense, or transfer
  • Transfers require to_account_id, non-transfers must have null
  • Transfers cannot have categories
  • amount must be positive
• Triggers:
  • update_account_balance - Automatically updates account balances
  • validate_transfer_accounts - Ensures transfer accounts belong to same user
• Security: Full RLS enforcement, users only see their own transactions

money_recurring_transactions

Templates for automated recurring transactions

• Primary Key: id (UUID)
• Foreign Keys: user_id → profiles(id)
• Indexes: user_id, next_date, is_active
• Fields:
  • template (JSONB): Complete transaction template including amount, category, etc.
  • frequency: daily, weekly, biweekly, monthly, quarterly, yearly
  • frequency_interval: Multiplier for frequency (e.g., 2 for bi-weekly)
• Business Logic:
  • process_recurring_transactions() function creates transactions when due
• Security: RLS enabled, user-specific access only

money_budgets

Flexible budget tracking across multiple categories

• Primary Key: id (UUID)
• Foreign Keys: user_id → profiles(id)
• Indexes: user_id, is_active, start_date+end_date
• Fields:
  • category_ids[]: Array of category UUIDs to track
  • period: daily, weekly, monthly, yearly
  • alert_threshold: Decimal 0-1 for alert percentage (e.g., 0.8 = 80%)
• Triggers:
  • validate_budget_categories - Ensures user owns all referenced categories
• Security: RLS enabled with category ownership validation

Materialized Views

money_account_summary

Pre-calculated account balances and net worth per user

• Refreshed via refresh_money_materialized_views()
• Provides: total_accounts, active_accounts, liquid_assets, investments, net_worth

money_monthly_summary

Aggregated monthly spending by category

• Indexed by user_id+month, category_id+month
• Provides: transaction_count, total_amount, avg_amount per category/month

money_category_stats

Category usage statistics and totals

• Tracks transaction counts and amounts per category
• Useful for spending analysis and reports

money_budget_tracking

Real-time budget vs actual spending comparison

• Calculates spent_amount, remaining_amount, percentage_used
• Joins budgets with actual transactions

Key Business Functions

Financial Calculations

• calculate_account_balance(account_id) - Current balance with pending transactions
• calculate_category_spending(user_id, category_id, start_date, end_date)
• get_monthly_summary(user_id, start_date, end_date)
• get_cash_flow_report(user_id, start_date, end_date)

Budget Management

• get_budget_status(user_id) - Current status of all active budgets
• check_budget_alerts() - Identifies budgets exceeding thresholds

Recurring Transactions

• process_recurring_transactions() - Creates due transactions from templates

Security & Audit

• detect_suspicious_activity(user_id, days) - Fraud detection
• get_user_wealth(user_id) - Total net worth calculation
• Full audit trail with risk assessment

Security Architecture

Row Level Security (RLS)

• All tables have RLS enabled
• Users can only access their own data
• System categories visible to all authenticated users
• Transfer validation ensures both accounts belong to same user

Audit Trail

• All changes logged to audit_log table
• Risk level assessment for high-value transactions
• Fraud detection algorithms
• Suspicious activity monitoring

Data Encryption

• Account numbers encrypted using pgcrypto
• encrypt_account_number() / decrypt_account_number() functions
• Sensitive data never exposed in plain text

Performance Optimizations

Indexes Strategy

• User-scoped queries: All tables indexed on user_id
• Date-based queries: Composite indexes on user_id+date
• Partial indexes: Recent transactions, active accounts only
• Full-text search: GIN indexes on transaction search vectors
• Array columns: GIN indexes for tags and category arrays

Query Optimization

• Materialized views for expensive aggregations
• Optimized functions for common queries
• Strategic use of partial indexes for filtered queries
• CONCURRENTLY refreshed materialized views to avoid locks

Data Integrity Rules

1. Account Balance Consistency: Triggers automatically update balances on transaction changes
2. Transfer Validation: Both accounts must belong to same user
3. Category Hierarchy: Parent categories must exist before children
4. Budget Categories: All budget categories must be owned by user
5. Soft Delete Pattern: deleted_at timestamp preserves data for audit
6. Currency Consistency: All amounts stored with 2 decimal precision
7. Date Constraints: End dates must be after start dates where applicable

Sample Queries

Get User Net Worth

SELECT * FROM money_account_summary WHERE user_id = ?;

Monthly Spending by Category

SELECT 
  c.name,
  ms.month,
  ms.total_amount
FROM money_monthly_summary ms
JOIN money_categories c ON c.id = ms.category_id
WHERE ms.user_id = ? 
  AND ms.month >= CURRENT_DATE - INTERVAL '6 months'
ORDER BY ms.month DESC, ms.total_amount DESC;

Budget Status Check

SELECT * FROM money_budget_tracking 
WHERE user_id = ? 
  AND percentage_used >= 80
  AND CURRENT_DATE BETWEEN start_date AND COALESCE(end_date, CURRENT_DATE);

Search Transactions

SELECT * FROM search_money_transactions('coffee', user_id, 20, 0);

Migration History

1. 20250129_001_money_schema.sql - Core tables and relationships
2. 20250129_002_money_rls_policies.sql - Security policies
3. 20250129_003_money_indexes.sql - Performance optimizations
4. 20250129_004_money_functions.sql - Business logic
5. 20250129_005_money_audit.sql - Audit trail and fraud detection
6. 20250129_006_money_seed_data.sql - System categories and dev data

Rollback Strategy

Each migration has a corresponding rollback script in supabase/rollback/ directory, allowing safe reversal of changes in reverse order.

Future Enhancements

• Multi-currency support with exchange rates
• Investment portfolio tracking
• Tax category mapping
• Financial goal tracking
• Automated transaction import (bank APIs)
• Advanced reporting dashboards
• Machine learning for transaction categorization