Database Changes

A complete reference for making database changes in Mantle projects using Aurora DSQL. This guide covers the end-to-end workflow, DSQL-specific constraints, validation tooling, and common failure modes.

The 5-Step Workflow

Every database schema change follows this sequence:

1. Edit schema  →  2. Generate  →  3. Validate  →  4. Deploy  →  5. Permissions

# 1. Edit src/db/entities/*.ts (Drizzle schema DSL)
# 2. Generate migration SQL
mantle db generate

# 3. Validate before applying
mantle check migrations        # journal ↔ SQL file consistency
mantle db check-dsql           # DSQL compatibility classification
mantle db migrate --dry-run    # preview against mock schema (no mutations)

# 4. Deploy (runs MigrateDSQL Lambda automatically)
mantle deploy --stage staging

# 5. Verify permissions (applied automatically by MigrateDSQL)
mantle db apply-permissions    # manual re-run if needed

When to Use mantle db generate vs Hand-Writing

Scenario	Approach
Adding/renaming tables or columns	mantle db generate
Adding indexes or constraints	mantle db generate
Data backfill (INSERT/UPDATE existing rows)	Hand-write SQL
Multi-step migration (add column → backfill → drop old)	Hand-write SQL
Conditional DDL (IF NOT EXISTS guards)	Hand-write SQL

After hand-writing, you must manually add a journal entry to migrations/meta/_journal.json:

{
  "idx": 3,
  "version": "7",
  "when": 1746835200000,
  "tag": "0004_my_migration",
  "breakpoints": true
}

The tag field must match the SQL filename without extension: 0004_my_migration.sql → "tag": "0004_my_migration".

The Journal Requirement

Every .sql file in migrations/ must have a corresponding entry in migrations/meta/_journal.json. The reverse also holds: every journal entry must reference an existing SQL file.

Why this matters: The Mantle migration runner tracks applied migrations by matching journal entries against the database. An orphaned SQL file (no journal entry) will never be applied. A missing SQL file (journal entry exists) will cause a runtime error.

Enforcement:

mantle check migrations

This command validates bidirectional consistency between SQL files and journal entries. It runs automatically in mantle ci and should be included in any pre-deploy CI pipeline.

DSQL Constraints Reference

Aurora DSQL is PostgreSQL-compatible but has significant differences. All migrations must account for these:

Constraint	Impact	Workaround
No ALTER COLUMN TYPE	Cannot change column types in-place	Add new column → backfill → use new column
No DROP COLUMN	Cannot remove columns	Leave deprecated columns in place
No SET DEFAULT	Cannot alter column defaults	Add new column with desired default
No SERIAL / sequences	No auto-incrementing integers	Use text with gen_random_uuid()
One DDL per transaction	Multiple DDL in one txn fails	Runner executes each statement in its own transaction
No synchronous index creation	CREATE INDEX blocks	Auto-converted to CREATE INDEX ASYNC by runner
No Row Level Security	ENABLE RLS fails	Stripped silently by runner
No PL/pgSQL DO blocks	Anonymous code blocks fail	Stripped silently by runner
UNIQUE CONSTRAINT required for onConflict	UNIQUE INDEX won't work	Use .unique() in Drizzle schema (creates CONSTRAINT)
GRANT ON <nonexistent_table> succeeds silently	Permissions appear applied but have no effect	See DSQL GRANT Behavior

DSQL GRANT Behavior

Critical gotcha: Aurora DSQL silently succeeds when you GRANT permissions on a table that doesn't exist. No error is raised. The grant is recorded but has no effect since the target table doesn't exist.

This means:

• If migrations fail partway (some tables created, others not), the subsequent permissions step will appear to succeed for ALL tables
• Lambda functions will get permission denied at runtime for tables that were never actually created
• The MigrateDSQL Lambda runs migrations BEFORE permissions specifically to avoid this — but if a deploy is interrupted between steps, re-deploy to fix

Diagnosis: If a Lambda gets permission denied after a deploy:

# Check if the table actually exists
mantle db check-dsql

# Re-run the full migration + permissions sequence
aws lambda invoke --function-name <stage>-<app>-MigrateDSQL --payload '{}' response.json

Validation Workflow

Before deploying migration changes, validate at three levels:

1. Journal Consistency

mantle check migrations

Ensures every SQL file has a journal entry and vice versa. Catches the most common developer error (adding a SQL file without updating the journal, or using drizzle-kit generate in a non-interactive terminal where it silently produces nothing).

2. DSQL Compatibility

mantle db check-dsql

Classifies each statement in your migration files:

• OK — Fully compatible with DSQL
• INDEX — Will be rewritten to CREATE INDEX ASYNC
• STRIP — Unsupported, will be skipped (RLS, DO blocks, sequences)
• RECREATION — Requires table recreation (ALTER COLUMN TYPE)

3. Dry-Run Migration

mantle db migrate --dry-run

Creates a preview schema and replays all migrations against it. Reports per-statement classification without acquiring locks or mutating the live database.

4. Local Clone Testing (Optional)

mantle db clone --stage staging

Clones the remote DSQL database to a local Docker PostgreSQL container (port 5433). Run migrations against real data locally before deploying to staging.

Terraform Deploy Trigger

The terraform_data.rerun_migration resource controls when MigrateDSQL is re-invoked on deploy. It hashes:

1. The MigrateDSQL Lambda bundle (build/lambdas/MigrateDSQL/index.mjs)
2. The permissions SQL file (permissions/permissions.sql)
3. All files in the migrations folder (build/lambdas/MigrateDSQL/migrations/**/*)

Any change to migration files, the journal, or permissions triggers automatic re-invocation. This is generated by mantle generate infra — never edit the trigger hash manually in instance .tf files.

Troubleshooting

Migration not applied after deploy

Symptom: New table doesn't exist in production despite SQL file being in migrations/.

Causes:

1. Missing journal entry → Run mantle check migrations to detect
2. Stale terraform trigger → Re-run mantle generate infra to regenerate migrate_dsql.tf
3. Build didn't include migrations → Check assets.json = ["migrations", "permissions"] exists in the MigrateDSQL handler directory

Permission denied after deploy

Symptom: Lambda gets pgCode 28000 or permission denied for table.

Causes:

1. Missing Step 3 of C29 → Run mantle db apply-permissions
2. Table didn't exist when permissions were applied (see DSQL GRANT Behavior) → Re-invoke MigrateDSQL
3. Missing @RequiresTable annotation → Add it, run mantle generate permissions

Journal/SQL mismatch

Symptom: mantle check migrations reports orphaned or missing files.

Fix:

• Orphaned SQL (no journal entry): Either delete the SQL file or add a journal entry manually
• Missing SQL (journal entry exists): Either create the SQL file or remove the journal entry
• Run mantle db generate to let Drizzle reconcile from schema

drizzle-kit generate produces nothing

Cause: Requires an interactive terminal (process.stdout.isTTY). CI environments and piped shells won't work.

Fix: Run mantle db generate in a real terminal session, not in CI or via automation.

Known Client-Side Issues

EventBuffer Retry Exhaustion (iOS OMD)

The EventBuffer in the iOS OfflineMediaDownloader analytics client has a data-loss bug:

EventBuffer.flush() → events.removeAll() → sendWithRetry() → [FAILURE] → events LOST

events.removeAll() executes before sendWithRetry() completes. If the send fails after all retries are exhausted, the events are permanently lost — they've already been removed from the buffer.

Impact: Analytics events may be silently dropped during network interruptions or API errors.

Future fix: Move removeAll() after successful send confirmation, or persist failed batches to disk for retry on next app launch.

Related Guides

• Database — Schema definition, providers, CRUD operations
• Migration — Drizzle Kit workflow, DSQL runner, MigrateDSQL Lambda
• Entity Queries — @RequiresTable permission declarations
• Deployment — Full deploy workflow including database steps