100 lines
2.9 KiB
Markdown
100 lines
2.9 KiB
Markdown
|
# 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 ...;`
|