# Database Migrations

This directory contains SQL migrations that enforce ledger correctness boundaries.

## Migration Order

Run migrations in this order:

1. `001_ledger_idempotency.sql` - Add unique constraint for idempotency
2. `002_dual_ledger_outbox.sql` - Create outbox table
3. `003_outbox_state_machine.sql` - Enforce state machine constraints
4. `004_balance_constraints.sql` - Enforce balance integrity (apply after data cleanup)
5. `005_post_ledger_entry.sql` - Create atomic posting function
6. `006_sal_positions_fees.sql` - SAL extension: positions (asset x chain), fees, reconciliation snapshots

## Running Migrations

### Option 1: Direct SQL execution

```bash
# Set your database connection
export DATABASE_URL="postgresql://user:password@host:port/database"

# Run migrations in order
psql $DATABASE_URL -f db/migrations/001_ledger_idempotency.sql
psql $DATABASE_URL -f db/migrations/002_dual_ledger_outbox.sql
psql $DATABASE_URL -f db/migrations/003_outbox_state_machine.sql
psql $DATABASE_URL -f db/migrations/004_balance_constraints.sql
psql $DATABASE_URL -f db/migrations/005_post_ledger_entry.sql
psql $DATABASE_URL -f db/migrations/006_sal_positions_fees.sql
```

### Option 2: Prisma migrate (if using Prisma migrations)

These SQL files can be added to a Prisma migration:

```bash
npx prisma migrate dev --name add_ledger_correctness_boundaries
```

Then copy the SQL into the generated migration file.

## Important Notes

### Column Naming

These migrations assume **snake_case** column names in the database (Prisma default).

If your database uses camelCase, adjust the SQL accordingly:
- `ledger_id` → `ledgerId`
- `debit_account_id` → `debitAccountId`
- etc.

### Balance Constraints

The balance constraints in `004_balance_constraints.sql` will fail if you have existing inconsistent data. 

**Before applying:**
1. Audit existing balances
2. Fix any inconsistencies
3. Then apply the constraints

### Testing

After applying migrations, verify:

```sql
-- Check idempotency constraint exists
SELECT constraint_name 
FROM information_schema.table_constraints 
WHERE table_name = 'ledger_entries' 
  AND constraint_name LIKE '%reference%';

-- Check outbox table exists
SELECT COUNT(*) FROM dual_ledger_outbox;

-- Test posting function
SELECT * FROM post_ledger_entry(
  'Test'::TEXT,
  'account1'::TEXT,
  'account2'::TEXT,
  100::NUMERIC,
  'USD'::TEXT,
  'fiat'::TEXT,
  'Type_A'::TEXT,
  'test-ref-123'::TEXT,
  NULL::NUMERIC,
  NULL::JSONB
);
```

## Rollback

These migrations are designed to be additive. To rollback:

1. Drop the function: `DROP FUNCTION IF EXISTS post_ledger_entry(...);`
2. Drop the outbox table: `DROP TABLE IF EXISTS dual_ledger_outbox CASCADE;`
3. Remove constraints: `ALTER TABLE ledger_entries DROP CONSTRAINT IF EXISTS ledger_entries_unique_ledger_reference;`
4. Remove balance constraints: `ALTER TABLE bank_accounts DROP CONSTRAINT IF EXISTS ...;`