Spaces:
Sleeping
Sleeping
File size: 4,978 Bytes
32eb084 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 | """
Migration script to create tasks table and indexes.
Run this script to set up the database schema for task tracking.
"""
import asyncio
import asyncpg
import os
from dotenv import load_dotenv
# Load environment variables
load_dotenv()
async def run_migration():
"""Create tasks table and indexes"""
# Get database connection details
db_host = os.getenv("DB_HOST", "localhost")
db_port = int(os.getenv("DB_PORT", "5432"))
db_name = os.getenv("DB_NAME", "cuatrolabs")
db_user = os.getenv("DB_USER", "postgres")
db_password = os.getenv("DB_PASSWORD", "")
print("=" * 80)
print("TASKS TABLE MIGRATION")
print("=" * 80)
print(f"Host: {db_host}")
print(f"Port: {db_port}")
print(f"Database: {db_name}")
print(f"User: {db_user}")
print("=" * 80)
try:
# Connect to database
print("\n[1/3] Connecting to PostgreSQL...")
conn = await asyncpg.connect(
host=db_host,
port=db_port,
database=db_name,
user=db_user,
password=db_password
)
print("✅ Connected successfully")
# Create tasks table
print("\n[3/4] Creating trans.scm_tasks table...")
create_table_sql = """
CREATE TABLE IF NOT EXISTS trans.scm_tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL,
assigned_to UUID NOT NULL,
title TEXT NOT NULL,
description TEXT,
status TEXT DEFAULT 'not_started',
latitude DOUBLE PRECISION,
longitude DOUBLE PRECISION,
address TEXT,
scheduled_at TIMESTAMP,
started_at BIGINT,
completed_at BIGINT,
created_at TIMESTAMP DEFAULT now(),
updated_at TIMESTAMP DEFAULT now(),
CONSTRAINT chk_status CHECK (status IN ('not_started', 'in_progress', 'completed'))
)
"""
await conn.execute(create_table_sql)
print("✅ Table 'trans.scm_tasks' created/verified")
# Create indexes
print("\n[4/4] Creating indexes...")
# Index for assigned_to + scheduled_at lookups
index_sql = """
CREATE INDEX IF NOT EXISTS idx_scm_tasks_assigned_date
ON trans.scm_tasks (assigned_to, scheduled_at)
"""
await conn.execute(index_sql)
print("✅ Index 'idx_scm_tasks_assigned_date' created")
# Index for merchant + status lookups
index_merchant_sql = """
CREATE INDEX IF NOT EXISTS idx_scm_tasks_merchant_status
ON trans.scm_tasks (merchant_id, status)
"""
await conn.execute(index_merchant_sql)
print("✅ Index 'idx_scm_tasks_merchant_status' created")
# Index for status + scheduled_at
index_status_sql = """
CREATE INDEX IF NOT EXISTS idx_scm_tasks_status_scheduled
ON trans.scm_tasks (status, scheduled_at)
"""
await conn.execute(index_status_sql)
print("✅ Index 'idx_scm_tasks_status_scheduled' created")
# Verify table structure
print("\n[VERIFICATION] Checking table structure...")
columns = await conn.fetch("""
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'trans' AND table_name = 'scm_tasks'
ORDER BY ordinal_position
""")
print("\nTable Structure:")
print("-" * 80)
for col in columns:
nullable = "NULL" if col['is_nullable'] == 'YES' else "NOT NULL"
default = f"DEFAULT {col['column_default']}" if col['column_default'] else ""
print(f" {col['column_name']:<20} {col['data_type']:<20} {nullable:<10} {default}")
print("-" * 80)
# Check indexes
indexes = await conn.fetch("""
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'trans' AND tablename = 'scm_tasks'
""")
print("\nIndexes:")
print("-" * 80)
for index in indexes:
print(f" {index['indexname']}")
print("-" * 80)
# Close connection
await conn.close()
print("\n" + "=" * 80)
print("✅ MIGRATION COMPLETED SUCCESSFULLY")
print("=" * 80)
print("\nNext steps:")
print("1. Start the Tracker microservice")
print("2. Test the tasks endpoint: GET /tracker/tasks/today")
print("3. Check the API documentation: http://localhost:8003/docs")
print("=" * 80)
except Exception as e:
print(f"\n❌ Migration failed: {e}")
raise
if __name__ == "__main__":
asyncio.run(run_migration())
|