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:
```python
# DON'T call this in production code!
Base.metadata.create_all(bind=engine)
```

#### ✅ CORRECT PROCESS:

1. **Update schema.sql**
   ```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**
   ```python
   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:**
```bash
# 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
   ```python
   class User(BaseModel):
       __tablename__ = "users"  # ✅ Matches Supabase table
   ```

2. **Match column names exactly** - Use snake_case
   ```python
   first_name = Column(String(100))  # ✅ Matches DB column
   firstName = Column(String(100))   # ❌ Won't work
   ```

3. **Match data types** - Use correct SQLAlchemy types
   ```python
   # 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
   ```python
   # ❌ 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:**
```python
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)
   ```sql
   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!