Copy page

Base de Datos

Migrations

Schema changes are managed through Knex.js migrations in twin-backend/packages/database/src/migrations/.

Location

Code
twin-backend/packages/database/src/migrations/
  20260101000000_initial_schema.ts
  20260115000001_add_collaborators.ts
  20260402000003_add_chlt_bolt.ts
  ...

Naming Convention

Code
YYYYMMDD######_description.ts

• YYYYMMDD -- date the migration was created
• ###### -- six-digit sequence number (usually 000000, 000001, etc.)
• description -- snake_case description of the change

Examples:

• 20260406000000_add_source_to_minting_transaction.ts
• 20260407000001_partial_unique_ops_token.ts

Commands

Run from the database package directory:

Code
cd twin-backend/packages/database

# Run all pending migrations
npm run migrate

# Rollback the last batch of migrations
npm run migrate:undo

# Create a new migration file
npm run make -- <description>

Rules

Use import type for Knex

Knex is a CommonJS module but migrations run with sucrase/ESM. Always use the type-only import:

Code
// Correct
import type { Knex } from 'knex';

// Wrong -- will cause runtime errors
import { Knex } from 'knex';

Idempotency

Migrations that insert data must be idempotent (safe to run multiple times). Use check-before-insert:

Code
export async function up(knex: Knex): Promise<void> {
  // For data inserts
  const exists = await knex('cron_job').where('key', 'NEW_JOB').first();
  if (!exists) {
    await knex('cron_job').insert({
      key: 'NEW_JOB',
      cron_expression: '*/5 * * * *',
      is_active: true,
      status: 'idle',
    });
  }

  // For schema changes
  if (!(await knex.schema.hasColumn('table', 'new_column'))) {
    await knex.schema.alterTable('table', (t) => {
      t.string('new_column');
    });
  }
}

For index creation, use IF NOT EXISTS:

Code
await knex.raw(`
  CREATE INDEX IF NOT EXISTS idx_table_column
  ON table (column)
`);

Batch Behavior

• All pending migrations run in a single batch
• If any migration in the batch fails, all migrations in that batch are rolled back
• Each migration's up function runs in its own transaction by default

Partial Unique Indexes for Soft Delete

Tables with soft delete (deleted_at) must use partial unique indexes instead of regular unique constraints. Without this, soft-deleted rows occupy the index and block recreation.

Code
await knex.raw(`
  CREATE UNIQUE INDEX IF NOT EXISTS idx_table_unique_active
  ON table (column_a, column_b)
  WHERE deleted_at IS NULL
`);

Tables using this pattern:

• circulation_wallet -- (ops_token_id, wallet_address) WHERE deleted_at IS NULL
• ops_token -- (LOWER(address), network) WHERE deleted_at IS NULL
• bridge_route -- (bridge_token_key, network) WHERE deleted_at IS NULL

Snake Case

All column names use snake_case in migrations. Objection.js models handle the conversion to camelCase in JavaScript via knexSnakeCaseMappers.

Code
// Migration
t.string('wallet_address');
t.integer('ops_token_id');
t.timestamp('deleted_at');

// Model (accessed as camelCase)
wallet.walletAddress
wallet.opsTokenId
wallet.deletedAt