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

Money Domain ERD

Entity relationship diagram for the Money app domain

Money Implementation

Implementation details and SQL schemas

Core Schemas

Money Domain

The Money app uses a comprehensive schema for financial management:

Core Tables

• users - User profiles and settings
• accounts - Financial accounts
• transactions - Transaction records
• categories - Transaction categories
• budgets - Budget definitions
• goals - Financial goals

Supporting Tables

• currencies - Currency definitions
• exchange_rates - Historical rates
• recurring_transactions - Scheduled transactions
• attachments - Receipt storage
• tags - Transaction tagging

Trips Domain

The Trips app schema for travel planning:

Core Tables

• trips - Trip definitions
• itineraries - Day-by-day plans
• activities - Trip activities
• accommodations - Lodging details
• transportation - Travel bookings

Supporting Tables

• destinations - Location data
• poi - Points of interest
• collaborators - Shared trips
• expenses - Trip expenses
• documents - Travel documents

Database Features

Row-Level Security (RLS)

All tables implement RLS policies:

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

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:

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

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