MIGRATIONS_GUIDE.md

# Database Migrations Guide

## Overview

Database migrations are **manual scripts** that create and update database tables. They must be run **once during initial setup** or when deploying updates.

## Quick Start

### Run All Migrations (Recommended)

```bash
python migrate_all_tables.py
```

This will create all 6 tables:
1. `trans.scm_attendance` - Check-in/out records
2. `trans.scm_tasks` - Task assignments
3. `trans.scm_task_attachments` - Task photos/signatures
4. `trans.scm_location_points` - GPS tracking (TimescaleDB hypertable)
5. `trans.scm_geofences` - Work zone definitions
6. `trans.scm_trips` - Business trip tracking

## Tables Created

### 1. Attendance Table
- **Table:** `trans.scm_attendance`
- **Purpose:** Employee check-in/check-out records
- **Indexes:** 2 (employee_work_date, merchant_date)

### 2. Tasks Table
- **Table:** `trans.scm_tasks`
- **Purpose:** Task assignments and tracking
- **Indexes:** 1 (assigned_to, scheduled_at)

### 3. Task Attachments Table
- **Table:** `trans.scm_task_attachments`
- **Purpose:** Task photos and signatures
- **Indexes:** 1 (task_id)
- **Foreign Key:** References scm_tasks(id)

### 4. Location Points Table (TimescaleDB)
- **Table:** `trans.scm_location_points`
- **Purpose:** GPS location tracking
- **Type:** TimescaleDB hypertable (if extension available)
- **Indexes:** 2 (user_time, merchant_time)
- **Partitioning:** 1-day chunks

### 5. Geofences Table
- **Table:** `trans.scm_geofences`
- **Purpose:** Work zone definitions
- **Indexes:** 2 (merchant, merchant_active)
- **Constraints:** 3 (radius, latitude, longitude)

### 6. Trips Table
- **Table:** `trans.scm_trips`
- **Purpose:** Business trip tracking
- **Indexes:** 3 (user_status, merchant_date, status)
- **Constraints:** 6 (status, coordinates, distance)

## Migration Behavior

### Idempotent Operations
All migrations use `CREATE TABLE IF NOT EXISTS` and `CREATE INDEX IF NOT EXISTS`, making them safe to run multiple times.

### What Happens
1. Creates `trans` schema if not exists
2. Creates each table if not exists
3. Creates indexes if not exist
4. Adds constraints (silently skips if exist)
5. Converts location table to hypertable (if TimescaleDB available)

## When to Run Migrations

### Initial Setup
Run migrations once when setting up the application for the first time:

```bash
python migrate_all_tables.py
```

### After Git Pull
If new tables were added, run migrations again:

```bash
git pull
python migrate_all_tables.py
```

### Production Deployment
1. Backup database first
2. Run migrations on staging
3. Test thoroughly
4. Run on production
5. Verify tables exist

## Checking Migration Status

### Check if tables exist

```sql
-- Connect to database
psql $DATABASE_URL

-- List all tables in trans schema
\dt trans.*

-- Check specific table
\d trans.scm_trips
```

### Verify all tables

```sql
SELECT 
    schemaname,
    tablename 
FROM pg_tables 
WHERE schemaname = 'trans'
ORDER BY tablename;
```

Expected output:
- scm_attendance
- scm_geofences
- scm_location_points
- scm_task_attachments
- scm_tasks
- scm_trips

## Troubleshooting

### Error: "Could not connect to database"

**Check:**
1. `.env` file exists with correct credentials
2. Database is accessible
3. Network connection is working

```bash
# Test connection
psql "postgresql://$DB_USER:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DB_NAME?sslmode=require"
```

### Error: "TimescaleDB extension not found"

**Solution:**
The location table will be created as a regular PostgreSQL table. To enable TimescaleDB:

```sql
-- Connect to database
psql <connection_string>

-- Enable TimescaleDB
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

-- Re-run migrations
python migrate_all_tables.py
```

### Error: "Permission denied"

**Solution:**
Your database user needs these permissions:
- CREATE SCHEMA
- CREATE TABLE
- CREATE INDEX
- INSERT, SELECT, UPDATE, DELETE

### Migration Fails Midway

**Solution:**
1. Check error message
2. Fix the issue
3. Re-run the migration (it's idempotent)

## Manual Migration (Advanced)

If you need to run migrations manually:

```bash
# Connect to database
psql $DATABASE_URL

# Copy SQL from migrate_all_tables.py and run
```

## Rollback (CAUTION: Destroys Data)

To drop all tables and start fresh:

```sql
-- Drop all tables
DROP TABLE IF EXISTS trans.scm_task_attachments CASCADE;
DROP TABLE IF EXISTS trans.scm_tasks CASCADE;
DROP TABLE IF EXISTS trans.scm_attendance CASCADE;
DROP TABLE IF EXISTS trans.scm_location_points CASCADE;
DROP TABLE IF EXISTS trans.scm_geofences CASCADE;
DROP TABLE IF EXISTS trans.scm_trips CASCADE;

-- Drop schema
DROP SCHEMA IF EXISTS trans CASCADE;

-- Re-run migrations
python migrate_all_tables.py
```

## Adding New Tables

To add a new table to the migration system:

1. **Add function to `migrate_all_tables.py`:**

```python
async def create_new_table(conn):
    """Create new_table"""
    await conn.execute("""
        CREATE TABLE IF NOT EXISTS trans.new_table (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            -- your columns here
        )
    """)
    
    await conn.execute("""
        CREATE INDEX IF NOT EXISTS idx_new_table_field
        ON trans.new_table (field_name)
    """)
```

2. **Add to `_run_migrations()` function:**

```python
async def _run_migrations(conn):
    await create_attendance_table(conn)
    await create_tasks_table(conn)
    await create_task_attachments_table(conn)
    await create_location_table(conn)
    await create_geofences_table(conn)
    await create_trips_table(conn)
    await create_new_table(conn)  # Add here
```

3. **Run migrations:**

```bash
python migrate_all_tables.py
```

## Best Practices

### 1. Always Backup First

```bash
# PostgreSQL backup
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME > backup_$(date +%Y%m%d).sql
```

### 2. Test on Staging First

```bash
# Run on staging
DB_HOST=staging.example.com python migrate_all_tables.py

# Test thoroughly
python test_trips_api.py
python test_location_api.py
python test_geofences_api.py
```

### 3. Use Idempotent Operations

Always use:
- `CREATE TABLE IF NOT EXISTS`
- `CREATE INDEX IF NOT EXISTS`
- Try-catch for constraints

### 4. Document Changes

Update this guide when adding new tables or making schema changes.

## Environment Variables

Migrations use these environment variables from `.env`:

```env
DB_USER=trans_owner
DB_PASSWORD=your_password
DB_HOST=your-host.aws.neon.tech
DB_PORT=5432
DB_NAME=cuatrolabs
```

## FAQ

### Q: Do migrations run automatically on server start?

**A:** No, migrations are manual. You must run them explicitly using `python migrate_all_tables.py`.

### Q: Can I run migrations multiple times?

**A:** Yes, migrations are idempotent. They check if tables exist before creating them.

### Q: What if I only want to create one table?

**A:** You can modify `migrate_all_tables.py` to comment out the tables you don't need, or copy the specific function and run it separately.

### Q: How do I verify migrations completed successfully?

**A:** Check the output for "✅ SUCCESS" message, or query the database:

```sql
SELECT COUNT(*) FROM pg_tables WHERE schemaname = 'trans';
-- Should return 6
```

## Next Steps

After running migrations:

1. **Verify tables:**
   ```sql
   \dt trans.*
   ```

2. **Start server:**
   ```bash
   python -m uvicorn app.main:app --port 8003 --reload
   ```

3. **Test APIs:**
   ```bash
   python test_trips_api.py
   python test_location_api.py
   python test_geofences_api.py
   ```

4. **View API docs:**
   http://localhost:8003/docs

## Support

- **Migration script:** `migrate_all_tables.py`
- **Documentation:** This file
- **Database access:** Use psql or pgAdmin
- **Logs:** Check console output during migration