Database Troubleshooting

This guide helps you diagnose and fix common database issues in the FindU ecosystem. Each section includes symptoms, causes, and step-by-step solutions.

Connection Issues

Cannot Connect to Database

Symptoms
Diagnosis
Solutions

FATAL: password authentication failed
could not connect to server
timeout expired
SASL authentication failed

# Check current environment
./findu env status

# Verify connection string
echo $DATABASE_URL

# Test connection
psql $DATABASE_URL -c "SELECT 1"

# Check Supabase project status
supabase status

Wrong environment:
```
./findu env switch dev  # or prod
```

Expired token:

# Get new token from https://app.supabase.com/account/tokens
echo "SUPABASE_ACCESS_TOKEN=new_token" >> .env.local

Network issues:

# Check if Supabase is accessible
curl -I https://app.supabase.com

# Try direct connection
psql postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres

Update MCP config:
```
./findu update mcp
```

SSL Connection Required

-- Error: SSL connection is required

-- Solution 1: Add SSL mode to connection string
postgresql://user:pass@host:5432/db?sslmode=require

-- Solution 2: Set environment variable
export PGSSLMODE=require

Migration Issues

Migration Already Exists

Never modify a migration that has been deployed. Create a new migration to fix issues.

Check migration status

supabase migration list

If local only

# Remove the local migration file
rm supabase/migrations/[timestamp]_migration_name.sql

# Create new one with different name
supabase migration new fixed_migration_name

If already deployed

# Create a fix migration
supabase migration new fix_previous_migration

# Add your fixes to the new migration

Migration Fails to Apply

-- Error: relation "table_name" already exists

-- Fix: Use IF NOT EXISTS
CREATE TABLE IF NOT EXISTS public.table_name (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid()
);

-- For columns
ALTER TABLE public.table_name 
  ADD COLUMN IF NOT EXISTS new_column text;

-- For constraints
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint 
    WHERE conname = 'constraint_name'
  ) THEN
    ALTER TABLE public.table_name
      ADD CONSTRAINT constraint_name CHECK (column > 0);
  END IF;
END $$;

Migration Order Issues

# Symptoms: Foreign key constraints fail

# Solution 1: Check migration order
ls -la supabase/migrations/

# Solution 2: Combine related migrations
cat supabase/migrations/*create_tables* > supabase/migrations/[timestamp]_create_all_tables.sql

# Solution 3: Defer constraints
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- Your migration SQL here
COMMIT;

RLS (Row Level Security) Issues

All Queries Return Empty

Check if RLS is enabled

SELECT 
  schemaname, 
  tablename, 
  rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' 
  AND tablename = 'your_table';

Check policies exist

SELECT 
  pol.polname as policy_name,
  pol.polcmd as command,
  pol.polroles as roles,
  CASE 
    WHEN pol.polpermissive THEN 'PERMISSIVE'
    ELSE 'RESTRICTIVE'
  END as type
FROM pg_policies pol
WHERE pol.schemaname = 'public'
  AND pol.tablename = 'your_table';

Test as specific user

-- Set user context
SET request.jwt.claim.sub TO 'user-uuid-here';

-- Test query
SELECT * FROM public.your_table;

-- Reset context
RESET request.jwt.claim.sub;

Debug policy

-- Create debug policy
CREATE POLICY "debug_policy" ON public.your_table
  FOR SELECT
  USING (
    RAISE NOTICE 'User ID: %, Row ID: %', auth.uid(), id
    RETURNING true
  );

Policy Not Working as Expected

-- Common RLS debugging queries

-- Check current user
SELECT 
  auth.uid() as user_id,
  auth.role() as role,
  auth.email() as email;

-- Test policy condition directly
SELECT 
  s.*,
  (EXISTS (
    SELECT 1 FROM public.profiles p 
    WHERE p.user_id = auth.uid() 
      AND p.student_id = s.id
  )) as policy_passes
FROM public.students s;

-- Bypass RLS temporarily (dev only!)
ALTER TABLE public.your_table DISABLE ROW LEVEL SECURITY;
-- Don't forget to re-enable!
ALTER TABLE public.your_table ENABLE ROW LEVEL SECURITY;

Performance Issues

Slow Queries

Identify Slow Queries
Common Fixes
Query Optimization

-- Enable query timing
\timing on

-- Get query plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT * FROM public.student_school_interactions
WHERE student_id = 'uuid'
  AND liked = true
ORDER BY interaction_date DESC;

-- Find missing indexes
SELECT 
  schemaname,
  tablename,
  attname,
  n_distinct,
  most_common_vals
FROM pg_stats
WHERE tablename = 'student_school_interactions'
  AND attname IN ('student_id', 'liked');

-- Add missing indexes
CREATE INDEX CONCURRENTLY idx_interactions_student_liked 
ON public.student_school_interactions(student_id, liked)
WHERE liked = true;

-- Update table statistics
ANALYZE public.student_school_interactions;

-- Rewrite query to use indexes
-- Bad: Functions on indexed columns
SELECT * FROM students 
WHERE LOWER(email) = '[email protected]';

-- Good: Index on expression
CREATE INDEX idx_students_email_lower 
ON students(LOWER(email));

-- Use EXISTS instead of IN for large datasets
-- Bad
SELECT * FROM students 
WHERE id IN (
  SELECT student_id FROM interactions WHERE liked = true
);

-- Good
SELECT * FROM students s
WHERE EXISTS (
  SELECT 1 FROM interactions i 
  WHERE i.student_id = s.id AND i.liked = true
);

-- Limit early in CTEs
WITH recent_students AS (
  SELECT * FROM students 
  WHERE created_at > NOW() - INTERVAL '7 days'
  LIMIT 1000  -- Limit here
)
SELECT * FROM recent_students
ORDER BY gpa DESC
LIMIT 10;  -- Not just here

Database Size Issues

-- Check table sizes
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
  pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as indexes_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Find bloated indexes
SELECT 
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_tup_read > 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Clean up
VACUUM ANALYZE; -- Gentle cleanup
VACUUM FULL; -- Aggressive, locks tables
REINDEX CONCURRENTLY INDEX index_name; -- Rebuild index

Data Integrity Issues

Constraint Violations

-- Error: violates foreign key constraint

-- Find orphaned records
SELECT ssi.*
FROM public.student_school_interactions ssi
LEFT JOIN public.students s ON ssi.student_id = s.id
WHERE s.id IS NULL;

-- Fix: Delete orphans or add missing parents
DELETE FROM public.student_school_interactions
WHERE student_id NOT IN (
  SELECT id FROM public.students
);

-- Prevent future issues
ALTER TABLE public.student_school_interactions
  DROP CONSTRAINT fk_student,
  ADD CONSTRAINT fk_student 
    FOREIGN KEY (student_id) 
    REFERENCES public.students(id) 
    ON DELETE CASCADE;

Data Inconsistencies

-- Find and fix common inconsistencies

-- Mismatched counts
WITH counts AS (
  SELECT 
    c.id,
    c.message_count,
    COUNT(m.id) as actual_count
  FROM public.conversations c
  LEFT JOIN public.messages m ON c.id = m.conversation_id
  GROUP BY c.id, c.message_count
)
UPDATE public.conversations
SET message_count = counts.actual_count
FROM counts
WHERE conversations.id = counts.id
  AND counts.message_count != counts.actual_count;

-- Orphaned records
DELETE FROM public.conversation_participants
WHERE conversation_id NOT IN (
  SELECT id FROM public.conversations
);

-- Circular references
WITH RECURSIVE hierarchy AS (
  SELECT id, parent_id, 1 as level
  FROM public.categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.parent_id, h.level + 1
  FROM public.categories c
  JOIN hierarchy h ON c.parent_id = h.id
  WHERE h.level < 10  -- Prevent infinite recursion
)
SELECT * FROM hierarchy
ORDER BY level, id;

Supabase-Specific Issues

Storage Issues

-- Check storage usage
SELECT 
  auth.uid() as user_id,
  bucket_id,
  COUNT(*) as file_count,
  pg_size_pretty(SUM(COALESCE(metadata->>'size', '0')::bigint)) as total_size
FROM storage.objects
GROUP BY auth.uid(), bucket_id;

-- Clean up orphaned files
DELETE FROM storage.objects
WHERE owner NOT IN (
  SELECT id FROM auth.users
);

Auth Issues

-- Check auth configuration
SELECT * FROM auth.users WHERE email = '[email protected]';

-- Verify user metadata
SELECT 
  id,
  email,
  raw_user_meta_data,
  created_at,
  last_sign_in_at
FROM auth.users
ORDER BY created_at DESC
LIMIT 10;

-- Fix missing profiles
INSERT INTO public.profiles (id, role)
SELECT 
  id as id,
  'student' as role
FROM auth.users
WHERE id NOT IN (
  SELECT id FROM public.profiles
);

Emergency Procedures

Database Locked

-- Find blocking queries
SELECT 
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity 
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Kill blocking query (use carefully!)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = [blocking_pid];

Rollback Recent Changes

-- Create restore point (before risky operation)
SELECT pg_create_restore_point('before_risky_change');

-- Use transactions
BEGIN;
-- Your risky operations
SAVEPOINT my_savepoint;
-- More operations
-- If something goes wrong:
ROLLBACK TO my_savepoint;
-- Or if all good:
COMMIT;

-- Restore from backup (contact Supabase support for production)

Prevention Best Practices

Getting Help

Check Supabase Status

Visit status.supabase.com

Review Logs

# In Supabase Dashboard
# Settings → Logs → Database logs

Contact Support

Development issues: Team discussion
Production emergencies: Supabase support
Include error messages and query examples

Return to database overview or learn about migrations.

Getting Started

Developer Guide

Web App

iOS App

Matching Algorithm

Database

AI Development

Database Troubleshooting

Database Troubleshooting

Connection Issues

Cannot Connect to Database

SSL Connection Required

Migration Issues

Migration Already Exists

Migration Fails to Apply

Migration Order Issues

RLS (Row Level Security) Issues

All Queries Return Empty

Policy Not Working as Expected

Performance Issues

Slow Queries

Database Size Issues

Data Integrity Issues

Constraint Violations

Data Inconsistencies

Supabase-Specific Issues

Storage Issues

Auth Issues

Emergency Procedures

Database Locked

Rollback Recent Changes

Prevention Best Practices

Getting Help

Getting Started

Developer Guide

Web App

iOS App

Matching Algorithm

Database

AI Development

​Database Troubleshooting

​Connection Issues

​Cannot Connect to Database

​SSL Connection Required

​Migration Issues

​Migration Already Exists

​Migration Fails to Apply

​Migration Order Issues

​RLS (Row Level Security) Issues

​All Queries Return Empty

​Policy Not Working as Expected

​Performance Issues

​Slow Queries

​Database Size Issues

​Data Integrity Issues

​Constraint Violations

​Data Inconsistencies

​Supabase-Specific Issues

​Storage Issues

​Auth Issues

​Emergency Procedures

​Database Locked

​Rollback Recent Changes

​Prevention Best Practices

​Getting Help

Database Troubleshooting

Connection Issues

Cannot Connect to Database

SSL Connection Required

Migration Issues

Migration Already Exists

Migration Fails to Apply

Migration Order Issues

RLS (Row Level Security) Issues

All Queries Return Empty

Policy Not Working as Expected

Performance Issues

Slow Queries

Database Size Issues

Data Integrity Issues

Constraint Violations

Data Inconsistencies

Supabase-Specific Issues

Storage Issues

Auth Issues

Emergency Procedures

Database Locked

Rollback Recent Changes

Prevention Best Practices

Getting Help