db/README.md

E-commerce Order Tables - Database Migrations

Overview

This directory contains SQL migration scripts for creating and managing e-commerce order tables in the trans schema.

Tables

1. trans.sales_orders - Main order table
2. trans.sales_order_items - Order line items
3. trans.sales_order_addresses - Shipping and billing addresses

Migration Files

001_create_ecommerce_order_tables.sql

Creates all three order tables from scratch with UUID support.

Use when:

• Setting up a new database
• Tables don't exist yet
• Starting fresh

Features:

• UUID primary keys
• Proper foreign key constraints
• Indexes for performance
• Automatic updated_at trigger
• Comprehensive field set

002_alter_existing_order_tables_to_uuid.sql

Drops and recreates existing tables with UUID support.

Use when:

• Tables already exist with VARCHAR(26) IDs
• Need to migrate from ULID to UUID
• Existing data can be lost

⚠️ WARNING: This will drop existing tables and data!

001_rollback_ecommerce_order_tables.sql

Drops all order tables.

Use when:

• Need to completely remove order tables
• Rolling back a migration
• Starting over

⚠️ WARNING: This will permanently delete all order data!

Running Migrations

Option 1: Using Python Script (Recommended)

# Navigate to db directory
cd cuatrolabs-ecomm-ms/db

# Create new tables
python run_migration.py 001_create_ecommerce_order_tables.sql

# Alter existing tables (with confirmation)
python run_migration.py 002_alter_existing_order_tables_to_uuid.sql

# Rollback (with confirmation)
python run_migration.py 001_rollback_ecommerce_order_tables.sql

Option 2: Using psql

# Set connection string
export DATABASE_URL="postgresql://trans_owner:BookMyService7@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech/cuatrolabs?sslmode=require"

# Run migration
psql $DATABASE_URL -f migrations/001_create_ecommerce_order_tables.sql

# Or with explicit connection
psql -h ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech \
     -U trans_owner \
     -d cuatrolabs \
     -f migrations/001_create_ecommerce_order_tables.sql

Option 3: Using DBeaver or pgAdmin

1. Connect to your database
2. Open the SQL script file
3. Execute the script

Verification

After running a migration, verify the tables were created:

-- List tables
SELECT schemaname, tablename 
FROM pg_tables 
WHERE schemaname = 'trans' 
    AND tablename LIKE 'sales_order%'
ORDER BY tablename;

-- Check table structure
\d trans.sales_orders
\d trans.sales_order_items
\d trans.sales_order_addresses

-- Verify UUID columns
SELECT 
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE table_schema = 'trans'
    AND table_name IN ('sales_orders', 'sales_order_items', 'sales_order_addresses')
    AND column_name LIKE '%_id'
ORDER BY table_name, ordinal_position;

Table Schema

trans.sales_orders

Column	Type	Description
sales_order_id	UUID	Primary key
order_number	VARCHAR(50)	Unique order number (e.g., ORD-20260207-0001)
merchant_id	UUID	Merchant ID
customer_id	VARCHAR(100)	Customer ID from auth service
order_date	TIMESTAMP	Order creation date
status	VARCHAR(50)	Order status
subtotal	NUMERIC(15,2)	Subtotal before tax
total_tax	NUMERIC(15,2)	Total tax amount
grand_total	NUMERIC(15,2)	Final total
payment_status	VARCHAR(50)	Payment status
fulfillment_status	VARCHAR(50)	Fulfillment status
...	...	Additional fields

trans.sales_order_items

Column	Type	Description
id	UUID	Primary key
sales_order_id	UUID	Foreign key to sales_orders
product_id	VARCHAR(100)	Product catalogue ID
product_name	VARCHAR(255)	Product name
quantity	NUMERIC(15,3)	Quantity ordered
unit_price	NUMERIC(15,2)	Unit price
line_total	NUMERIC(15,2)	Line total
...	...	Additional fields

trans.sales_order_addresses

Column	Type	Description
id	UUID	Primary key
sales_order_id	UUID	Foreign key to sales_orders
address_type	VARCHAR(50)	shipping or billing
line1	VARCHAR(255)	Address line 1
city	VARCHAR(100)	City
state	VARCHAR(100)	State
postal_code	VARCHAR(20)	Postal code
country	VARCHAR(100)	Country

Environment Variables

The Python migration script uses these environment variables:

DB_HOST=ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
DB_PORT=5432
DB_NAME=cuatrolabs
DB_USER=trans_owner
DB_PASSWORD=BookMyService7
DB_SSLMODE=require

These can be set in your .env file or exported in your shell.

Troubleshooting

Connection Failed

• Verify database credentials
• Check network connectivity
• Ensure SSL mode is correct
• Verify database exists

Permission Denied

• Ensure user has CREATE TABLE permissions
• Check schema permissions
• Verify user can create functions and triggers

Table Already Exists

• Use 002_alter_existing_order_tables_to_uuid.sql to recreate
• Or use 001_rollback_ecommerce_order_tables.sql first

Foreign Key Violations

• Ensure parent tables exist before child tables
• Check foreign key references are correct
• Verify CASCADE options are appropriate

Best Practices

1. Backup First: Always backup your database before running migrations
2. Test in Dev: Test migrations in development environment first
3. Review SQL: Review the SQL scripts before executing
4. Verify Results: Always verify tables were created correctly
5. Document Changes: Keep track of which migrations have been run

Next Steps

After running the migration:

1. Verify tables were created successfully
2. Test the order API endpoints
3. Create sample orders to test functionality
4. Set up monitoring and logging
5. Configure backups for order data

Support

For issues or questions:

• Check the verification queries above
• Review error messages carefully
• Ensure all prerequisites are met
• Check database logs for detailed errors