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

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

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