Database Development

This section covers all aspects of database development for the Family Shapes platform, including schema design, migrations, queries, and best practices.

Table of Contents

1. Database Architecture
2. Migration Workflow
3. Schema Design
4. Row Level Security
5. Query Optimization
6. Type Generation
7. Testing Database Changes

Database Architecture

Family Shapes uses Supabase (PostgreSQL) as the database with a multi-tenant architecture. The database is designed to support three distinct user types:

1. Family Users: Individual users and family communities
2. SaaS Users: Cryobanks and fertility clinics
3. Donor Users: Donors with privacy controls

Key tables include:

• persons: People depicted in family trees
• family_trees: Family tree definitions
• organizations: Multi-tenant organizations
• connections: Relationships between people
• donors: Donor-specific information

For schema and migration details, see Database Migration Guidelines.

Migration Workflow

All database changes must be made through migrations in the supabase/migrations/ directory. The migration workflow is as follows:

Important: Always use Makefile targets to create migration files. Do not manually create or rename SQL files. Using make ensures timestamped, sequential ordering and helps prevent drift.

# Ensure environment is set up
# Create .env file at repo root with:
SUPABASE_PROJECT_REF=nhkufibfwskdpzdjwirr
SUPABASE_DB_PASSWORD=your-remote-db-password

# Reset local database to match migrations
make db/reset

# Verify no drift exists
make db/check  # Should show: ✅ No drift.

# After making local schema changes (use Makefile to enforce naming)
make db/diff name=0002_short_description

# Verify migration
make db/reset
make db/check

# Regenerate TypeScript types
make db/types

# Only after PR approval and CI passing
make db/push

IMPORTANT: Before creating any database migrations, always check the Project Priorities and ensure your changes align with the current phase and immediate priorities in the ROADMAP.md.

For detailed migration guidelines, see Database Migration Guidelines.

Schema Design

When designing database schema, follow these principles:

1. Normalization: Follow normalization principles to reduce redundancy
2. Naming Conventions: Use snake_case for table and column names
3. Primary Keys: Use UUIDs for primary keys
4. Foreign Keys: Always define foreign key constraints
5. Timestamps: Include created_at and updated_at columns
6. Soft Deletes: Use deleted_at for soft deletes where appropriate
7. Indexing: Create indexes for frequently queried columns

For detailed schema design guidelines, see Database Migration Guidelines.

Row Level Security

All tables must have appropriate Row Level Security (RLS) policies to ensure data isolation between tenants. Example RLS policy:

-- Example RLS policy for organization-scoped data
CREATE POLICY "Users can view their organization's data"
ON table_name
FOR SELECT
USING (
  auth.uid() IN (
    SELECT user_id FROM organization_members
    WHERE organization_id = table_name.organization_id
  )
);

For detailed RLS guidelines, see Database Migration Guidelines.

Query Optimization

Optimize database queries by:

1. Using Indexes: Create indexes for frequently queried columns
2. Limiting Results: Use LIMIT and OFFSET for pagination
3. Joining Efficiently: Use appropriate join types
4. Using Views: Create views for complex queries
5. Caching: Use query caching where appropriate

For detailed query optimization guidelines, see Database Migration Guidelines.

Type Generation

After making schema changes, regenerate TypeScript types:

make db/types

This will update the types in src/types/database.ts.

Testing Database Changes

Test database changes by:

1. Local Testing: Test changes locally before creating a migration
2. Migration Testing: Test migrations by resetting the local database
3. Query Testing: Test queries with representative data
4. Performance Testing: Test query performance with large datasets

For detailed testing guidelines, see Database Migration Guidelines.