Database Schema and Migrations 🗄️

Master PostgreSQL and Supabase database design for CrittrHavens. A comprehensive guide to schema, migrations, and data management.

Database Schema Overview

The foundation of reptile care data.

Core Schema Design

Entity Relationships:

-- Simplified ERD
profiles (1) ──┬── havens (∞)
               ├── subscriptions (1)
               └── settings (1)

havens (1) ────┬── habitats (∞)
               ├── crittrs (∞)
               └── tasks (∞)

crittrs (1) ───┬── logs (∞)
               ├── photos (∞)
               └── measurements (∞)

inventory (1) ─┬── items (∞)
               └── alerts (∞)

Table Structure

Primary Tables:

-- User profiles table
CREATE TABLE profiles (
  user_id UUID PRIMARY KEY REFERENCES auth.users(id),
  display_name TEXT,
  email TEXT UNIQUE,
  subscription_status TEXT DEFAULT 'free',
  subscription_id TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  is_active BOOLEAN DEFAULT true,
  notification_preferences JSONB DEFAULT '{}',
  privacy_settings JSONB DEFAULT '{}'
);

-- Havens (workspaces) table
CREATE TABLE havens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES profiles(user_id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  background_image TEXT,
  slot_count INTEGER DEFAULT 6,
  layout_type TEXT DEFAULT 'grid',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Habitats table with 4-layer visual system
CREATE TABLE habitats (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  haven_id UUID REFERENCES havens(id) ON DELETE CASCADE,
  user_id UUID REFERENCES profiles(user_id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  habitat_type TEXT,
  visual JSONB DEFAULT '{"habitat":"01","background":"01","foreground":"01","glassReflection":"01","shape":"Wide"}'::jsonb,
  primary_substrate TEXT,
  is_bioactive BOOLEAN DEFAULT false,
  slot_position INTEGER,
  cleaning_frequency_hours INTEGER,
  feeding_frequency_hours INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Crittrs (pets) table
CREATE TABLE crittrs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  haven_id UUID REFERENCES havens(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  species TEXT NOT NULL,
  morph TEXT,
  sex TEXT CHECK (sex IN ('male', 'female', 'unknown')),
  birth_date DATE,
  acquisition_date DATE,
  photos JSONB[] DEFAULT '{}',
  is_favorite BOOLEAN DEFAULT false,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Care logs table
CREATE TABLE logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  crittr_id UUID REFERENCES crittrs(id) ON DELETE CASCADE,
  user_id UUID REFERENCES profiles(user_id),
  log_type TEXT NOT NULL,
  content TEXT,
  measurements JSONB,
  images JSONB[],
  log_date TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Data Types

PostgreSQL Types Used:

• UUID: Unique identifiers for all primary keys
• TEXT: Variable-length strings
• TIMESTAMPTZ: Timestamp with timezone
• DATE: Date without time
• INTEGER: Whole numbers
• BOOLEAN: True/false values
• JSONB: Binary JSON for flexible data
• JSONB[]: Array of JSON objects

4-Layer Habitat Visual System

Visual Field Structure:

{
  "habitat": "01",        // H layer (01-99)
  "background": "03",     // BG layer (01-99)
  "foreground": "02",     // FG layer (01-99)
  "glassReflection": "01", // GR layer (01-99)
  "shape": "Wide"         // Wide | ExtraWide | Square | Tall
}

Layer Types:

• Habitat (H): Main habitat structure layer
• Background (BG): Background scenery layer
• Foreground (FG): Foreground decorative elements
• Glass Reflection (GR): Glass surface reflections

Image Naming Convention:

<TypeShorthand>_<Number>_<Shape>.png
Examples:
- H_01_Wide.png
- BG_03_Square.png
- FG_02_ExtraWide.png
- GR_05_Tall.png

Table Relationships

Understanding foreign keys and references.

Foreign Key Constraints

Relationship Definitions:

-- Cascade deletes
ALTER TABLE havens
  ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id)
  REFERENCES profiles(user_id)
  ON DELETE CASCADE;

-- Restrict deletes
ALTER TABLE tasks
  ADD CONSTRAINT fk_crittr
  FOREIGN KEY (crittr_id)
  REFERENCES crittrs(id)
  ON DELETE RESTRICT;

-- Set null on delete
ALTER TABLE logs
  ADD CONSTRAINT fk_task
  FOREIGN KEY (task_id)
  REFERENCES tasks(id)
  ON DELETE SET NULL;

Join Patterns

Common Queries:

-- Get all crittrs with their havens
SELECT 
  c.*,
  h.name as haven_name,
  h.layout_type
FROM crittrs c
JOIN havens h ON c.haven_id = h.id
WHERE h.user_id = auth.uid();

-- Get logs with crittr details
SELECT 
  l.*,
  c.name as crittr_name,
  c.species
FROM logs l
JOIN crittrs c ON l.crittr_id = c.id
WHERE l.log_date >= NOW() - INTERVAL '30 days';

-- Complex join for full data
SELECT 
  h.name as haven,
  c.name as crittr,
  COUNT(l.id) as log_count,
  MAX(l.log_date) as last_log
FROM havens h
LEFT JOIN crittrs c ON h.id = c.haven_id
LEFT JOIN logs l ON c.id = l.crittr_id
GROUP BY h.id, c.id
ORDER BY h.name, c.name;

RLS Policies

Row Level Security for data protection.

Understanding RLS

Security Concepts:

-- Enable RLS on table
ALTER TABLE havens ENABLE ROW LEVEL SECURITY;

-- Basic policy structure
CREATE POLICY policy_name
  ON table_name
  FOR operation
  TO role
  USING (condition)
  WITH CHECK (condition);

Common Policies

User Data Isolation:

-- Users can only see their own havens
CREATE POLICY "Users view own havens"
  ON havens
  FOR SELECT
  USING (auth.uid() = user_id);

-- Users can insert their own havens
CREATE POLICY "Users create own havens"
  ON havens
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Users can update their own havens
CREATE POLICY "Users update own havens"
  ON havens
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Users can delete their own havens
CREATE POLICY "Users delete own havens"
  ON havens
  FOR DELETE
  USING (auth.uid() = user_id);

Nested Policies

Access Through Relationships:

-- Access crittrs through haven ownership
CREATE POLICY "Users manage crittrs in their havens"
  ON crittrs
  FOR ALL
  USING (
    haven_id IN (
      SELECT id FROM havens
      WHERE user_id = auth.uid()
    )
  );

-- Access logs through crittr ownership
CREATE POLICY "Users view logs for their crittrs"
  ON logs
  FOR SELECT
  USING (
    crittr_id IN (
      SELECT c.id FROM crittrs c
      JOIN havens h ON c.haven_id = h.id
      WHERE h.user_id = auth.uid()
    )
  );

Subscription-Based Policies

Feature Access Control:

-- Limit free users to 2 havens
CREATE POLICY "Free users limited havens"
  ON havens
  FOR INSERT
  WITH CHECK (
    (
      SELECT COUNT(*) FROM havens
      WHERE user_id = auth.uid()
    ) < 2
    OR
    EXISTS (
      SELECT 1 FROM profiles
      WHERE user_id = auth.uid()
      AND subscription_status IN ('care', 'care_plus')
    )
  );

Migration File Structure

Organizing database changes.

Migration Naming

Convention:

supabase/migrations/
├── 20240101000000_initial_schema.sql
├── 20240102000000_add_profiles.sql
├── 20240103000000_add_havens.sql
├── 20240104000000_add_crittrs.sql
├── 20240105000000_add_logs.sql
├── 20240106000000_add_rls_policies.sql
├── 20240107000000_add_indexes.sql
└── 20240108000000_seed_data.sql

Migration Template

Standard Structure:

-- Migration: Add inventory system
-- Author: developer@example.com
-- Date: 2024-01-15

-- Create inventory table
CREATE TABLE IF NOT EXISTS inventory (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES profiles(user_id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  quantity INTEGER DEFAULT 0,
  min_quantity INTEGER DEFAULT 1,
  unit TEXT,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add indexes
CREATE INDEX idx_inventory_user_id ON inventory(user_id);
CREATE INDEX idx_inventory_category ON inventory(category);

-- Add RLS policies
ALTER TABLE inventory ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users manage own inventory"
  ON inventory
  FOR ALL
  USING (auth.uid() = user_id);

-- Add trigger for updated_at
CREATE TRIGGER update_inventory_updated_at
  BEFORE UPDATE ON inventory
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Creating New Migrations

Step-by-step migration creation.

Using Supabase CLI

Generate Migration:

# Create new migration file
supabase migration new add_feeding_schedule

# This creates: 
# supabase/migrations/[timestamp]_add_feeding_schedule.sql

Writing Migrations

Best Practices:

-- 1. Use IF NOT EXISTS for safety
CREATE TABLE IF NOT EXISTS feeding_schedules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

-- 2. Make migrations idempotent
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'crittrs'
    AND column_name = 'feeding_day'
  ) THEN
    ALTER TABLE crittrs ADD COLUMN feeding_day TEXT;
  END IF;
END $$;

-- 3. Include rollback comments
-- ROLLBACK: DROP TABLE feeding_schedules;

-- 4. Update existing data carefully
UPDATE crittrs
SET feeding_day = 'monday'
WHERE feeding_day IS NULL
  AND species = 'Ball Python';

Complex Migrations

Multi-Step Migration:

-- Step 1: Add new column with default
ALTER TABLE crittrs
  ADD COLUMN status TEXT DEFAULT 'active';

-- Step 2: Migrate existing data
UPDATE crittrs
SET status = CASE
  WHEN last_fed < NOW() - INTERVAL '30 days' THEN 'inactive'
  WHEN is_breeding THEN 'breeding'
  ELSE 'active'
END;

-- Step 3: Add constraint
ALTER TABLE crittrs
  ADD CONSTRAINT chk_status
  CHECK (status IN ('active', 'inactive', 'breeding', 'quarantine'));

-- Step 4: Remove default
ALTER TABLE crittrs
  ALTER COLUMN status DROP DEFAULT;

4-Layer Visual Migration Example:

-- Migration: Convert habitat visual from string to JSONB
-- Step 1: Add temporary column
ALTER TABLE habitats ADD COLUMN visual_layers JSONB;

-- Step 2: Migrate existing data
UPDATE habitats 
SET visual_layers = 
  CASE 
    WHEN visual = 'frog-habby-1' THEN 
      jsonb_build_object(
        'habitat', '01',
        'background', '01', 
        'foreground', '01',
        'glassReflection', '01',
        'shape', 'Wide'
      )
    WHEN visual = 'jungle-climb' THEN 
      jsonb_build_object(
        'habitat', '02',
        'background', '02',
        'foreground', '02', 
        'glassReflection', '01',
        'shape', 'Wide'
      )
    ELSE jsonb_build_object(
      'habitat', '01',
      'background', '01',
      'foreground', '01',
      'glassReflection', '01',
      'shape', 'Wide'
    )
  END;

-- Step 3: Drop old column and rename new
ALTER TABLE habitats DROP COLUMN visual;
ALTER TABLE habitats RENAME COLUMN visual_layers TO visual;

-- Step 4: Add constraints
ALTER TABLE habitats
ADD CONSTRAINT visual_layers_required_fields CHECK (
  visual ? 'habitat' AND 
  visual ? 'background' AND 
  visual ? 'foreground' AND 
  visual ? 'glassReflection' AND 
  visual ? 'shape'
);

ALTER TABLE habitats
ADD CONSTRAINT visual_layers_valid_shape CHECK (
  visual->>'shape' IN ('Wide', 'ExtraWide', 'Square', 'Tall')
);

Testing Migrations Locally

Verify changes before production.

Local Database Setup

Start Local Supabase:

# Start local development database
supabase start

# Database will be available at:
# postgresql://postgres:postgres@localhost:54321/postgres

Running Migrations

Apply Migrations:

# Run all pending migrations
supabase db push

# Reset database and rerun all migrations
supabase db reset

# Check migration status
supabase migration list

Testing Queries

Verify Changes:

-- Connect to local database
psql postgresql://postgres:postgres@localhost:54321/postgres

-- Test new tables
\dt

-- Test policies
SET ROLE authenticated;
SET request.jwt.claim.sub = 'user-uuid';
SELECT * FROM havens;

Rollback Strategies

Handling migration failures.

Rollback Patterns

Safe Rollback:

-- Forward migration
CREATE TABLE new_table (...);

-- Rollback migration
DROP TABLE IF EXISTS new_table;

Data-Preserving Rollback:

-- Forward: Rename column
ALTER TABLE crittrs
  RENAME COLUMN old_name TO new_name;

-- Rollback: Rename back
ALTER TABLE crittrs
  RENAME COLUMN new_name TO old_name;

Backup Before Migration

Safety First:

# Backup production database
supabase db dump -f backup.sql

# Restore if needed
psql $DATABASE_URL < backup.sql

Data Seeding

Populate development data.

Seed Files

Development Seeds:

-- supabase/seed.sql

-- Insert test user
INSERT INTO auth.users (id, email)
VALUES ('test-user-id', 'test@example.com');

-- Insert test profile
INSERT INTO profiles (user_id, display_name, subscription_status)
VALUES ('test-user-id', 'Test User', 'care_plus');

-- Insert test havens
INSERT INTO havens (user_id, name, slot_count)
VALUES 
  ('test-user-id', 'Pythons', 6),
  ('test-user-id', 'Geckos', 4);

-- Insert test crittrs
INSERT INTO crittrs (haven_id, name, species, morph)
SELECT 
  h.id,
  'Sunny',
  'Ball Python',
  'Banana'
FROM havens h
WHERE h.name = 'Pythons'
LIMIT 1;

Running Seeds

Seed Commands:

# Run seed file
psql $DATABASE_URL < supabase/seed.sql

# Or use Supabase CLI
supabase db seed

Database Performance

Optimization techniques.

Indexes

Strategic Indexing:

-- Primary key indexes (automatic)
-- Foreign key indexes (recommended)
CREATE INDEX idx_crittrs_haven_id ON crittrs(haven_id);
CREATE INDEX idx_logs_crittr_id ON logs(crittr_id);

-- Query-specific indexes
CREATE INDEX idx_logs_date_type 
  ON logs(log_date DESC, log_type);

-- Partial indexes for common filters
CREATE INDEX idx_active_crittrs
  ON crittrs(haven_id)
  WHERE is_archived = false;

-- JSON indexes
CREATE INDEX idx_logs_measurements
  ON logs USING GIN (measurements);

Query Optimization

Efficient Queries:

-- Use EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM logs
WHERE crittr_id = 'uuid'
  AND log_date > NOW() - INTERVAL '30 days';

-- Batch operations
INSERT INTO logs (crittr_id, log_type, content)
VALUES
  ('uuid1', 'feeding', 'Fed 1 rat'),
  ('uuid2', 'feeding', 'Fed 2 mice'),
  ('uuid3', 'feeding', 'Fed 1 rabbit')
ON CONFLICT DO NOTHING;

-- Use CTEs for complex queries
WITH recent_logs AS (
  SELECT crittr_id, COUNT(*) as log_count
  FROM logs
  WHERE log_date > NOW() - INTERVAL '7 days'
  GROUP BY crittr_id
)
SELECT c.*, rl.log_count
FROM crittrs c
LEFT JOIN recent_logs rl ON c.id = rl.crittr_id;

Maintenance

Regular Tasks:

-- Update statistics
ANALYZE;

-- Vacuum dead rows
VACUUM;

-- Reindex if needed
REINDEX TABLE logs;

Common SQL Patterns

Reusable query templates.

Aggregation Queries

-- Count by category
SELECT 
  species,
  COUNT(*) as count,
  AVG(weight) as avg_weight
FROM crittrs
GROUP BY species
ORDER BY count DESC;

-- Time-based aggregation
SELECT 
  DATE_TRUNC('week', log_date) as week,
  COUNT(*) as logs_per_week
FROM logs
GROUP BY week
ORDER BY week DESC;

Window Functions

-- Rank crittrs by log count
SELECT 
  name,
  COUNT(*) OVER (PARTITION BY haven_id) as haven_total,
  ROW_NUMBER() OVER (
    PARTITION BY haven_id 
    ORDER BY created_at
  ) as crittr_number
FROM crittrs;

JSON Operations

-- Query JSONB data
SELECT 
  id,
  measurements->>'weight' as weight,
  measurements->>'length' as length
FROM logs
WHERE measurements @> '{"type": "measurement"}';

-- Update JSONB
UPDATE crittrs
SET photos = photos || '[{"url": "new-photo.jpg"}]'::jsonb
WHERE id = 'uuid';

-- Query habitat visual layers
SELECT 
  id,
  name,
  visual->>'habitat' as habitat_layer,
  visual->>'background' as background_layer,
  visual->>'shape' as shape
FROM habitats
WHERE visual->>'shape' = 'Wide';

-- Update specific visual layer
UPDATE habitats
SET visual = jsonb_set(
  visual,
  '{background}',
  '"05"'
)
WHERE id = 'habitat-uuid';

-- Find habitats with specific layer combinations
SELECT * FROM habitats
WHERE visual @> '{"habitat": "01", "shape": "Square"}';

Migration Best Practices

Guidelines for success.

Do's

• ✅ Test migrations locally first
• ✅ Make migrations idempotent
• ✅ Include descriptive comments
• ✅ Keep migrations small and focused
• ✅ Version control all migrations
• ✅ Backup before production migrations

Don'ts

• ❌ Modify existing migration files
• ❌ Drop columns without data backup
• ❌ Use SELECT * in views
• ❌ Forget to add indexes
• ❌ Skip RLS policies
• ❌ Ignore rollback strategy

Troubleshooting

Common database issues.

Migration Failures

# Check migration status
supabase migration list

# View error logs
supabase db logs

# Reset local database
supabase db reset

Performance Issues

-- Find slow queries
SELECT 
  query,
  calls,
  mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Check table sizes
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Next Steps

Continue learning database management.

• Supabase Integration
• Performance Guide
• Testing Strategy
• Error Handling

---

Building a solid foundation for tracking every scale, every feeding, every shed. 🐍