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)

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:

python migrate_all_tables.py

After Git Pull

If new tables were added, run migrations again:

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

-- Connect to database
psql $DATABASE_URL

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

-- Check specific table
\d trans.scm_trips

Verify all tables

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

# 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:

-- 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:

# 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:

-- 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:

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:

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:

python migrate_all_tables.py

Best Practices

1. Always Backup First

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

2. Test on Staging First

# 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:

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:

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

Next Steps

After running migrations:

1. Verify tables:

   \dt trans.*
   

2. Start server:

   python -m uvicorn app.main:app --port 8003 --reload
   

3. Test APIs:

   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