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

# Database Overview

> Understanding FindU's Supabase architecture and production setup

# Database Overview

FindU uses Supabase as its backend platform, providing PostgreSQL database, authentication, real-time subscriptions, and file storage. This guide explains our database architecture and development practices.

## Architecture Overview

FindU operates with a single Supabase instance used by both development and production environments. We use careful data isolation practices to enable safe development.

```mermaid theme={null}
graph TB
    subgraph "Supabase Instance"
        A[Production Database] --> B[Live User Data<br/>50k+ students]
        A --> C[Test Data<br/>emails with 'test']
    end
    
    subgraph "Environments"
        D[Dev Environment<br/>dev branch] --> A
        E[Prod Environment<br/>main branch] --> A
    end
    
    F[Developers] --> C
    G[Real Users] --> B
```

<Warning>
  Both dev and production environments share the same Supabase database. Always use test accounts (emails containing 'test') for development work.
</Warning>

## Working with Production Data

Since we're using production directly, here are critical safety guidelines:

<CardGroup cols={2}>
  <Card title="Data Privacy" icon="shield">
    **Protecting User Information**

    When working with production:

    * Never expose personal information in logs
    * Use test accounts for development
    * Avoid bulk operations on real users
    * Respect user privacy at all times
  </Card>

  <Card title="Development Safety" icon="hard-hat">
    **Safe Development Practices**

    To minimize risks:

    * Always backup before schema changes
    * Test migrations locally first
    * Use read-only queries when possible
    * Create dedicated test data
  </Card>

  <Card title="Compliance" icon="scale-balanced">
    **Regulatory Compliance**

    Maintain compliance by:

    * Logging all data access
    * Following data retention policies
    * Respecting deletion requests
    * Maintaining audit trails
  </Card>

  <Card title="Testing Strategy" icon="flask">
    **Production Testing**

    Safe testing approaches:

    * Use accounts with 'test' in email
    * Create isolated test scenarios
    * Clean up test data after use
    * Monitor for unintended effects
  </Card>
</CardGroup>

## Environment Configuration

### Development Environment

* **Branch**: `dev`
* **Deployment**: Auto-deploy to Railway dev services
* **Database**: Same Supabase instance (use test data)
* **URLs**: `*-dev.railway.app`
* **Purpose**: Testing features before production

### Production Environment

* **Branch**: `main`
* **Deployment**: Auto-deploy to Railway production
* **Database**: Same Supabase instance (live data)
* **URLs**: `findu.app`, `*-production.railway.app`
* **Access**: Requires PR from dev branch

### Database Details

| Aspect          | Details                                     |
| --------------- | ------------------------------------------- |
| **Total Data**  | 50,000+ students, 6,500+ schools            |
| **Test Data**   | Emails containing 'test'                    |
| **Backups**     | Automated daily with point-in-time recovery |
| **RLS**         | Always enforced for security                |
| **Migrations**  | Apply via PR process                        |
| **Performance** | Scaled for production traffic               |
| **Monitoring**  | Real-time logs and alerts                   |

## Database Schema Structure

Our database is organized into several key areas:

### Core Tables

* **students** - Student profiles and preferences
* **schools** - College information and statistics
* **student\_school\_interactions** - Swipe history and matches
* **messages** & **conversations** - Chat system

### Partner System

* **partner\_organizations** - Universities, companies, nonprofits
* **partner\_entities** - Specific programs or departments
* **partner\_users** - Admissions staff accounts
* **partner\_affiliations** - Role assignments

### Supporting Tables

* **scholarships** - Financial aid opportunities
* **friendships** - Student social connections
* **notifications** - System notifications

## Safe Development Practices

### Working with Production

<Tabs>
  <Tab title="Read Operations">
    ```sql theme={null}
    -- Safe read-only queries
    SELECT * FROM students WHERE email LIKE '%test%';

    -- Aggregate data without PII
    SELECT COUNT(*) FROM students WHERE created_at > NOW() - INTERVAL '7 days';

    -- Check your changes
    SELECT * FROM your_table WHERE your_condition LIMIT 10;
    ```
  </Tab>

  <Tab title="Write Operations">
    ```sql theme={null}
    -- Always use transactions
    BEGIN;
    UPDATE students SET ... WHERE email LIKE '%test%';
    -- Verify changes before committing
    ROLLBACK; -- or COMMIT if correct

    -- Create test data
    INSERT INTO students (email, name) 
    VALUES ('test-feature@example.com', 'Test User');
    ```
  </Tab>
</Tabs>

<Warning>
  Always use WHERE clauses to limit scope. Never run UPDATE or DELETE without a WHERE clause in production!
</Warning>

## Data Flow

### Schema Changes (Migrations)

```mermaid theme={null}
sequenceDiagram
    participant Dev as Developer
    participant Local as Local DB
    participant Test as Local Testing
    participant PR as Pull Request
    participant ProdDB as Production DB
    
    Dev->>Local: Create migration
    Dev->>Test: Test with local database
    Dev->>PR: Create PR with migration
    PR->>ProdDB: Apply carefully after review
```

### Test Data Strategy

#### Identifying Test Data

```sql theme={null}
-- Test accounts have 'test' in email
SELECT * FROM students WHERE email LIKE '%test%';
SELECT * FROM partner_users WHERE email LIKE '%test%';

-- Some tables have explicit test flags
SELECT * FROM schools WHERE is_test = true;
```

#### Creating Test Data

```sql theme={null}
-- Always use 'test' in emails
INSERT INTO students (email, name) 
VALUES ('test.student@example.com', 'Test Student');

-- Mark test organizations
INSERT INTO partner_organizations (name, is_test) 
VALUES ('Test University', true);
```

#### Cleaning Test Data

```sql theme={null}
-- Clean up after development
DELETE FROM students WHERE email LIKE '%test%' 
  AND created_at < NOW() - INTERVAL '7 days';
```

## Security Considerations

### Access Control

* Service role keys for admin operations only
* Anon keys for client applications
* Personal access tokens for developers
* RLS policies always enforced

### Data Isolation

* Test data clearly marked
* No production data in logs
* Secure credential management
* Regular security audits

## Best Practices

<Steps>
  <Step title="Always Use Test Accounts">
    Create and use accounts with 'test' in the email for all development
  </Step>

  <Step title="Test Migrations Locally First">
    Run migrations against a local database before applying to production
  </Step>

  <Step title="Backup Before Major Changes">
    Use `supabase db dump` to create backups before schema modifications
  </Step>

  <Step title="Monitor After Changes">
    Watch logs and error rates after any production deployment
  </Step>

  <Step title="Document Everything">
    Keep detailed records of changes, test accounts, and data modifications
  </Step>
</Steps>

## Common Questions

<AccordionGroup>
  <Accordion title="How do I create safe test data?">
    Use INSERT statements with clearly marked test emails (containing 'test'). Add metadata to identify test records. Always document which test accounts you create.
  </Accordion>

  <Accordion title="How do I debug a production issue?">
    1. Use read-only queries to investigate
    2. Check Supabase logs and monitoring
    3. Create test data to reproduce the issue
    4. Fix using test accounts first
    5. Apply fix carefully with monitoring
  </Accordion>

  <Accordion title="What if I need to test destructive operations?">
    1. Create a complete backup first
    2. Test on isolated test data
    3. Use transactions with ROLLBACK
    4. Consider creating a separate test Supabase project
  </Accordion>
</AccordionGroup>

***

Next, learn about [creating and managing migrations](/database/migrations-guide) or [working with the schema](/database/schema-management).