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

# Database Documentation

> Database schemas, models, and implementation guides

# Database Documentation

Comprehensive database documentation for Remind Tools, covering schemas, relationships, and implementation details.

## Overview

Remind Tools uses **Supabase** (PostgreSQL) as the primary database, providing:

* Relational data storage
* Real-time subscriptions
* Row-level security
* Database functions and triggers

## Database Structure

<CardGroup cols={2}>
  <Card title="Money Domain ERD" icon="diagram-project" href="/database/money-domain-erd">
    Entity relationship diagram for the Money app domain
  </Card>

  <Card title="Money Implementation" icon="code" href="/database/money-domain-implementation">
    Implementation details and SQL schemas
  </Card>
</CardGroup>

## Core Schemas

### Money Domain

The Money app uses a comprehensive schema for financial management:

<Tabs>
  <Tab title="Core Tables">
    * `users` - User profiles and settings
    * `accounts` - Financial accounts
    * `transactions` - Transaction records
    * `categories` - Transaction categories
    * `budgets` - Budget definitions
    * `goals` - Financial goals
  </Tab>

  <Tab title="Supporting Tables">
    * `currencies` - Currency definitions
    * `exchange_rates` - Historical rates
    * `recurring_transactions` - Scheduled transactions
    * `attachments` - Receipt storage
    * `tags` - Transaction tagging
  </Tab>
</Tabs>

### Trips Domain

The Trips app schema for travel planning:

<Tabs>
  <Tab title="Core Tables">
    * `trips` - Trip definitions
    * `itineraries` - Day-by-day plans
    * `activities` - Trip activities
    * `accommodations` - Lodging details
    * `transportation` - Travel bookings
  </Tab>

  <Tab title="Supporting Tables">
    * `destinations` - Location data
    * `poi` - Points of interest
    * `collaborators` - Shared trips
    * `expenses` - Trip expenses
    * `documents` - Travel documents
  </Tab>
</Tabs>

## Database Features

### Row-Level Security (RLS)

All tables implement RLS policies:

```sql theme={null}
-- Example: Users can only see their own data
CREATE POLICY "Users can view own data" ON transactions
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own data" ON transactions
  FOR INSERT WITH CHECK (auth.uid() = user_id);
```

### Real-time Subscriptions

Enable real-time updates for collaborative features:

```dart theme={null}
final subscription = supabase
  .from('transactions')
  .stream(primaryKey: ['id'])
  .eq('user_id', userId)
  .listen((data) {
    // Handle real-time updates
  });
```

### Database Functions

Custom PostgreSQL functions for complex operations:

```sql theme={null}
-- Calculate account balance
CREATE FUNCTION calculate_balance(account_id UUID)
RETURNS DECIMAL AS $$
  SELECT COALESCE(SUM(
    CASE 
      WHEN type = 'income' THEN amount
      ELSE -amount
    END
  ), 0)
  FROM transactions
  WHERE account_id = $1;
$$ LANGUAGE SQL;
```

## Migration Strategy

Database migrations are managed through:

1. **Version Control**: All migrations in `supabase/migrations/`
2. **Sequential Execution**: Numbered migration files
3. **Rollback Support**: Down migrations for reversibility
4. **Testing**: Migration testing in staging environment

## Performance Optimization

### Indexing Strategy

```sql theme={null}
-- Composite indexes for common queries
CREATE INDEX idx_transactions_user_date 
  ON transactions(user_id, transaction_date DESC);

-- Partial indexes for filtered queries
CREATE INDEX idx_active_budgets 
  ON budgets(user_id) 
  WHERE is_active = true;
```

### Query Optimization

* Use database views for complex queries
* Implement materialized views for reporting
* Leverage database functions for calculations
* Use proper indexing strategies

## Backup & Recovery

* **Automated Backups**: Daily automated backups
* **Point-in-time Recovery**: Up to 7 days
* **Manual Snapshots**: Before major migrations
* **Export Tools**: Regular data exports

## Security Considerations

1. **Encryption at Rest**: All data encrypted
2. **SSL/TLS**: Encrypted connections
3. **Access Control**: Role-based permissions
4. **Audit Logging**: Track all modifications
5. **Data Masking**: Sensitive data protection