web-hosts/domains/coppertone.tech/backend/migrations/README.md

Database Migrations

This directory contains SQL migrations for the Copper Tone Technologies platform database.

Migration Files

Migrations are numbered sequentially and come in pairs:

• XXX_description.up.sql - Applies the migration
• XXX_description.down.sql - Rolls back the migration

Current Migrations

1. 001_create_users_and_identities - User authentication and multi-factor identity system

   • users table: Core user information
   • identities table: Multiple authentication methods (email/password, blockchain, DIDs)
   • user_roles table: Role-based access control (ADMIN, STAFF, CLIENT)

2. 002_create_projects_and_tasks - Work management system

   • projects table: Project tracking with IPFS metadata support
   • tasks table: Task management with assignments and time tracking
   • work_orders table: Work order documentation with IPFS support
   • task_comments table: Collaboration and task discussions

3. 003_create_invoices_and_payments - Payment and invoicing system

   • invoices table: Invoice management with blockchain transaction support
   • payments table: Multi-modal payments (credit card, crypto, blockchain tokens)
   • invoice_items table: Line items for invoices
   • Automatic invoice status updates based on payments

Running Migrations

Using podman-compose

Migrations are automatically applied when starting the stack:

podman-compose up --build

The db-init service will wait for PostgreSQL to be ready and then apply all migrations in order.

Manual Migration

To run migrations manually:

# From the backend directory
./scripts/init-db.sh

Environment Variables

• DB_HOST - Database host (default: db)
• DB_USER - Database user (default: user)
• DB_PASSWORD - Database password (default: password)
• DB_NAME - Database name (default: coppertone_db)
• MIGRATIONS_DIR - Path to migrations directory (default: /migrations)

Schema Features

Multi-Factor Authentication

The identity system supports multiple authentication methods per user:

• Email/password authentication
• Blockchain address authentication (with signature verification)
• Decentralized Identifiers (DIDs)
• Users can link multiple identities to a single account

IPFS Integration

Several tables include CID (Content Identifier) fields for storing references to data on IPFS:

• Project metadata
• Work order documents
• Invoice PDFs

Blockchain Support

• Identity verification via blockchain signatures
• Payment tracking with blockchain transaction hashes
• Support for multiple blockchain networks (Ethereum, Bitcoin, Polygon, etc.)

Automatic Triggers

• updated_at columns are automatically updated on row modifications
• Invoice status is automatically updated when payments are marked as completed

Adding New Migrations

1. Create a new migration pair with the next sequential number:

   touch migrations/004_your_description.up.sql
   touch migrations/004_your_description.down.sql
   

2. Write the SQL for applying the migration in the .up.sql file

3. Write the SQL for rolling back the migration in the .down.sql file

4. Rebuild and restart the db-init service:

   podman-compose up --build db-init
   

Migration Best Practices

• Always include both .up.sql and .down.sql files
• Use IF NOT EXISTS and IF EXISTS clauses to make migrations idempotent
• Include appropriate indexes for foreign keys and frequently queried columns
• Use database enums for status fields to maintain data integrity
• Add comments to complex SQL for clarity
• Test migrations on a development database before applying to production