docs/dev/supabase/DATABASE_SAFETY.md

Database Safety Guide

⚠️ CRITICAL: Database Management Rules

Tables Are Managed in Supabase

• ALL tables are created and managed in Supabase via docs/schema/schema.sql
• SQLAlchemy models ONLY map to existing tables - they do NOT create tables
• Alembic migrations are DISABLED for automatic execution

Why This Approach?

1. Safety - Prevents accidental table creation/modification in production
2. Control - All schema changes reviewed and applied manually in Supabase
3. Audit Trail - Supabase tracks all schema changes
4. Rollback - Easy to rollback changes in Supabase dashboard

How to Make Schema Changes

❌ NEVER DO THIS:

# DON'T call this in production code!
Base.metadata.create_all(bind=engine)

✅ CORRECT PROCESS:

1. Update schema.sql

   -- Add new column to users table
   ALTER TABLE users ADD COLUMN new_field TEXT;
   

2. Test in Supabase Dashboard

   • Go to SQL Editor
   • Run the ALTER statement
   • Verify it works

3. Update SQLAlchemy Model

   class User(BaseModel):
       __tablename__ = "users"
       new_field = Column(String(255), nullable=True)
   

4. Deploy Code

   • Models now map to updated table
   • No automatic migrations run

Alembic Configuration

Alembic is configured but NOT used for automatic migrations.

Current setup:

• alembic.ini - Points to environment variable (not hardcoded)
• alembic/versions/ - Empty (no migration files)
• Migrations must be run manually after review

If you need to use Alembic:

# 1. Create migration (review it first!)
alembic revision --autogenerate -m "description"

# 2. Review the generated migration file
# Check: alembic/versions/xxx_description.py

# 3. Test in development first
alembic upgrade head

# 4. If safe, run in production (manually)

Model Mapping Rules

1. Use __tablename__ - Always specify snake_case table name

   class User(BaseModel):
       __tablename__ = "users"  # ✅ Matches Supabase table
   

2. Match column names exactly - Use snake_case

   first_name = Column(String(100))  # ✅ Matches DB column
   firstName = Column(String(100))   # ❌ Won't work
   

3. Match data types - Use correct SQLAlchemy types

   # Supabase: UUID
   id = Column(UUID(as_uuid=True))  # ✅
   
   # Supabase: JSONB
   metadata = Column(JSONB, default={})  # ✅
   
   # Supabase: TIMESTAMP WITH TIME ZONE
   created_at = Column(DateTime(timezone=True))  # ✅
   

4. Don't define relationships yet - Keep models simple

   # ❌ Avoid for now (adds complexity)
   # tickets = relationship("Ticket", back_populates="user")
   
   # ✅ Use queries instead
   # tickets = db.query(Ticket).filter(Ticket.user_id == user.id).all()
   

Checking Table Structure

In Supabase Dashboard:

1. Go to Table Editor
2. Select table
3. View columns, types, constraints

In Code:

from sqlalchemy import inspect
inspector = inspect(engine)
columns = inspector.get_columns('users')
print(columns)

Current Table Status

All 41 tables exist in Supabase:

• ✅ users
• ✅ clients
• ✅ contractors
• ✅ projects
• ✅ tickets
• ✅ (36 more tables...)

See docs/schema/schema.sql for complete schema.

Safety Checklist

Before deploying code:

• No Base.metadata.create_all() calls in code
• No automatic Alembic migrations in startup
• Models use correct __tablename__
• Column names match Supabase (snake_case)
• Data types match Supabase schema
• Tested queries work with existing tables

Emergency: If Tables Get Created Accidentally

If code accidentally creates duplicate tables:

1. Stop the application immediately
2. Check Supabase logs - See what was created
3. Drop duplicate tables (if any)

   DROP TABLE IF EXISTS duplicate_table_name;
   

4. Fix the code - Remove table creation logic
5. Redeploy

Questions?

• Schema questions → Check docs/schema/schema.sql
• Model questions → Check src/app/models/
• Migration questions → Ask before running!