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

# Money Domain Database Implementation

## Overview

The Money domain database schema has been successfully implemented for the Remind Tools personal finance management application. This document provides a comprehensive overview of the implementation details, testing results, and usage guidelines.

## Implementation Status ✅

### Completed Components

#### 1. Database Schema (✅ Complete)

* **5 Core Tables**: `money_accounts`, `money_transactions`, `money_categories`, `money_budgets`, `money_recurring_transactions`
* **Support Tables**: `audit_log` for comprehensive audit trail
* **69 System Categories**: Pre-loaded expense (58) and income (11) categories
* **Full Soft Delete Support**: All tables include `deleted_at` timestamp

#### 2. Performance Optimization (✅ Complete)

* **60 Indexes**: Composite, partial, GIN, and full-text search indexes
* **4 Materialized Views**: Pre-computed aggregations for reporting
  * `money_account_summary`: User account balances and net worth
  * `money_monthly_summary`: Monthly spending patterns
  * `money_category_stats`: Category usage statistics
  * `money_budget_tracking`: Real-time budget vs actual spending

#### 3. Security (✅ Complete)

* **21 RLS Policies**: Complete user data isolation
* **Encryption Support**: PGCrypto functions for sensitive data
* **Audit Trail**: Comprehensive logging with risk assessment
* **Multi-tenant Ready**: User-based data segregation

#### 4. Business Logic (✅ Complete)

* **10 Database Functions**: Core financial calculations
* **28 Triggers**: Automatic balance updates and data integrity
* **Cascade Rules**: Proper foreign key relationships
* **Transaction Types**: Income, expense, and transfers

#### 5. Data Integrity (✅ Complete)

* **10 Foreign Key Constraints**: Referential integrity
* **56 Check Constraints**: Business rule enforcement
* **37 Default Values**: Sensible defaults for all columns

## Migration Files

The implementation consists of 6 migration files executed in sequence:

1. **20250129\_001\_money\_schema.sql** - Core table structure
2. **20250130\_001\_money\_rls\_policies.sql** - Row Level Security
3. **20250131\_001\_money\_indexes.sql** - Performance indexes
4. **20250201\_001\_money\_functions.sql** - Business logic functions
5. **20250202\_001\_money\_audit.sql** - Audit trail implementation
6. **20250203\_001\_money\_seed\_data.sql** - System categories and initial data

## Key Features

### 1. Multi-Currency Support

* All monetary columns support multiple currencies
* Exchange rate tracking capability
* Currency stored per account and transaction

### 2. Advanced Categorization

* Hierarchical categories with parent-child relationships
* System and user-defined categories
* Icon and color customization support

### 3. Budget Management

* Multiple budget periods (daily, weekly, monthly, quarterly, yearly)
* Category-based budget allocation
* Real-time tracking via materialized views
* Alert thresholds and notifications support

### 4. Recurring Transactions

* Flexible recurrence patterns
* Template-based transaction creation
* Automatic processing with `process_recurring_transactions()` function
* End date and occurrence limits

### 5. Account Types

* Checking, Savings, Cash
* Credit Cards
* Investment accounts
* Loans
* Custom account types

### 6. Transaction Features

* Income, Expense, and Transfer types
* Tag support for flexible categorization
* File attachments capability
* Location tracking with PostGIS
* Full-text search on descriptions and notes

## Testing Results

### Verification Tests Passed

```
✓ All 5 Money domain tables exist
✓ System categories loaded: 69 total (58 expense, 11 income)
✓ Created 60 indexes on Money domain tables
✓ All 4 materialized views created
✓ Created 10 Money domain functions
✓ Created 28 triggers on Money domain tables
✓ Created 21 RLS policies on Money domain tables
✓ Constraints: 10 foreign keys, 56 check constraints
✓ 37 columns have default values configured
```

### TypeScript Types

Generated TypeScript types are available at:

```
packages/shared/lib/src/types/database.types.ts
```

## Usage Examples

### Creating an Account

```sql theme={null}
INSERT INTO money_accounts (user_id, name, type, currency, balance)
VALUES (auth.uid(), 'Main Checking', 'checking', 'USD', 1000.00);
```

### Recording a Transaction

```sql theme={null}
INSERT INTO money_transactions (
  user_id, account_id, type, amount, 
  category_id, description, date
)
VALUES (
  auth.uid(), 
  'account-uuid', 
  'expense', 
  50.00,
  'category-uuid', 
  'Grocery shopping', 
  CURRENT_DATE
);
```

### Creating a Budget

```sql theme={null}
INSERT INTO money_budgets (
  user_id, name, amount, period, 
  category_ids, start_date
)
VALUES (
  auth.uid(), 
  'Monthly Food Budget', 
  500.00, 
  'monthly',
  ARRAY['food-category-uuid'],
  DATE_TRUNC('month', CURRENT_DATE)
);
```

### Getting Budget Status

```sql theme={null}
SELECT * FROM get_budget_status('budget-uuid');
```

### Calculating Category Spending

```sql theme={null}
SELECT calculate_category_spending(
  'category-uuid',
  CURRENT_DATE - INTERVAL '30 days',
  CURRENT_DATE
);
```

## Rollback Scripts

Rollback scripts are available in `supabase/rollback/` for safe migration reversal:

* Each migration has a corresponding rollback script
* Execute in reverse order (006 → 001)
* Preserves data where possible

## Local Development

### Starting Supabase

```bash theme={null}
supabase start
```

### Accessing Services

* **API**: [http://localhost:54321](http://localhost:54321)
* **Studio**: [http://localhost:54323](http://localhost:54323)
* **Database**: localhost:54322

### Running Tests

```bash theme={null}
# Verify database structure
docker exec -i supabase_db_remind_tools psql -U postgres -d postgres < test/supabase/money_functions_test.sql

# Run Flutter integration tests
flutter test test/integration/money_database_test.dart
```

## Production Deployment

### Prerequisites

1. Supabase project created
2. Database access configured
3. Environment variables set

### Deployment Steps

```bash theme={null}
# Link to Supabase project
supabase link --project-ref <project-id>

# Push migrations to production
supabase db push

# Verify deployment
supabase db diff
```

## Security Considerations

1. **RLS Policies**: Always enabled in production
2. **API Keys**: Use service role key only server-side
3. **Sensitive Data**: Consider encryption for account numbers
4. **Audit Trail**: Regularly review for suspicious activity
5. **Backups**: Configure automated backups

## Performance Optimization

### Materialized View Refresh

Set up periodic refresh for materialized views:

```sql theme={null}
-- Manual refresh
SELECT refresh_money_materialized_views();

-- Automated refresh (requires pg_cron)
SELECT cron.schedule(
  'refresh-money-views', 
  '0 */2 * * *', 
  'SELECT public.refresh_money_materialized_views()'
);
```

### Index Maintenance

Monitor and maintain indexes:

```sql theme={null}
-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND tablename LIKE 'money_%'
ORDER BY idx_scan;
```

## Future Enhancements

### Planned Features

* [ ] Investment tracking with portfolio management
* [ ] Bill reminders and payment scheduling
* [ ] Financial goals and savings targets
* [ ] Advanced reporting and analytics
* [ ] CSV/QIF import/export
* [ ] Bank account synchronization
* [ ] Tax category mapping
* [ ] Multi-user family accounts

### API Integration Points

* Plaid for bank connections
* Exchange rate APIs for currency conversion
* Document storage for receipts
* Notification services for alerts

## Support and Maintenance

### Monitoring

* Set up alerts for failed transactions
* Monitor materialized view refresh status
* Track database performance metrics

### Regular Maintenance

* Weekly: Refresh materialized views
* Monthly: Analyze query performance
* Quarterly: Review and optimize indexes
* Annually: Archive old transactions

## Conclusion

The Money domain database implementation is complete and production-ready. All core features have been implemented with a focus on performance, security, and maintainability. The schema supports future expansion while maintaining backward compatibility.