> ## Documentation Index
> Fetch the complete documentation index at: https://docs.remind.tools/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

```mermaid theme={null}
erDiagram
    USERS ||--o{ MONEY_ACCOUNTS : owns
    USERS ||--o{ MONEY_CATEGORIES : creates
    USERS ||--o{ MONEY_TRANSACTIONS : records
    USERS ||--o{ MONEY_RECURRING_TRANSACTIONS : schedules
    USERS ||--o{ MONEY_BUDGETS : manages
    
    MONEY_ACCOUNTS ||--o{ MONEY_TRANSACTIONS : source
    MONEY_ACCOUNTS ||--o{ MONEY_TRANSACTIONS : destination
    
    MONEY_CATEGORIES ||--o{ MONEY_TRANSACTIONS : categorizes
    MONEY_CATEGORIES ||--o{ MONEY_CATEGORIES : parent
    MONEY_CATEGORIES ||--o{ MONEY_BUDGETS : tracks
    
    MONEY_RECURRING_TRANSACTIONS ||--o{ MONEY_TRANSACTIONS : generates
    
    MONEY_ACCOUNTS {
        uuid id PK
        uuid user_id FK
        varchar name
        varchar type
        decimal balance
        varchar currency
        varchar bank_name
        text account_number
        text description
        boolean is_active
        varchar color
        integer display_order
        jsonb metadata
        timestamp created_at
        timestamp updated_at
        timestamp deleted_at
    }
    
    MONEY_CATEGORIES {
        uuid id PK
        uuid user_id FK
        varchar name
        varchar type
        uuid parent_id FK
        varchar icon
        varchar color
        decimal budget_amount
        varchar budget_period
        boolean is_system
        integer display_order
        jsonb metadata
        timestamp created_at
        timestamp updated_at
        timestamp deleted_at
    }
    
    MONEY_TRANSACTIONS {
        uuid id PK
        uuid user_id FK
        uuid account_id FK
        uuid to_account_id FK
        varchar type
        uuid category_id FK
        decimal amount
        varchar currency
        varchar description
        date date
        text notes
        text[] tags
        text receipt_url
        boolean is_recurring
        uuid recurring_id FK
        jsonb metadata
        timestamp created_at
        timestamp updated_at
        timestamp deleted_at
    }
    
    MONEY_RECURRING_TRANSACTIONS {
        uuid id PK
        uuid user_id FK
        varchar name
        jsonb template
        varchar frequency
        integer frequency_interval
        date next_date
        date end_date
        date last_processed_date
        boolean is_active
        jsonb metadata
        timestamp created_at
        timestamp updated_at
    }
    
    MONEY_BUDGETS {
        uuid id PK
        uuid user_id FK
        varchar name
        uuid[] category_ids
        decimal amount
        varchar period
        date start_date
        date end_date
        decimal alert_threshold
        boolean is_active
        jsonb metadata
        timestamp created_at
        timestamp updated_at
    }
```

## 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

```sql theme={null}
SELECT * FROM money_account_summary WHERE user_id = ?;
```

### Monthly Spending by Category

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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