Skip to main content

​
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.
Both dev and production environments share the same Supabase database. Always use test accounts (emails containing ‘test’) for development work.

​
Working with Production Data

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

Data Privacy

Protecting User InformationWhen 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

Development Safety

Safe Development PracticesTo minimize risks:
  • Always backup before schema changes
  • Test migrations locally first
  • Use read-only queries when possible
  • Create dedicated test data

Compliance

Regulatory ComplianceMaintain compliance by:
  • Logging all data access
  • Following data retention policies
  • Respecting deletion requests
  • Maintaining audit trails

Testing Strategy

Production TestingSafe testing approaches:
  • Use accounts with ‘test’ in email
  • Create isolated test scenarios
  • Clean up test data after use
  • Monitor for unintended effects

​
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

AspectDetails
Total Data50,000+ students, 6,500+ schools
Test DataEmails containing ‘test’
BackupsAutomated daily with point-in-time recovery
RLSAlways enforced for security
MigrationsApply via PR process
PerformanceScaled for production traffic
MonitoringReal-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

  • Read Operations
  • Write Operations
-- 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;
Always use WHERE clauses to limit scope. Never run UPDATE or DELETE without a WHERE clause in production!

​
Data Flow

​
Schema Changes (Migrations)

​
Test Data Strategy

​
Identifying Test Data

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

-- Always use 'test' in emails
INSERT INTO students (email, name) 
VALUES ('[email protected]', 'Test Student');

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

​
Cleaning Test Data

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

1

Always Use Test Accounts

Create and use accounts with ‘test’ in the email for all development
2

Test Migrations Locally First

Run migrations against a local database before applying to production
3

Backup Before Major Changes

Use supabase db dump to create backups before schema modifications
4

Monitor After Changes

Watch logs and error rates after any production deployment
5

Document Everything

Keep detailed records of changes, test accounts, and data modifications

​
Common Questions

Use INSERT statements with clearly marked test emails (containing ‘test’). Add metadata to identify test records. Always document which test accounts you create.
  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
  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
Next, learn about creating and managing migrations or working with the schema.