Copy page

Base de Datos

Database Patterns

Common patterns used across the Twin backend database layer.

Soft Delete

Most entities support soft delete via a nullable deleted_at timestamp column. The record is never physically removed -- instead, deleted_at is set to the current time.

Model Configuration

Objection.js models use a notDeleted modifier to filter out soft-deleted rows by default:

Code
class CirculationWallet extends BaseModel {
  static modifiers = {
    notDeleted(query: QueryBuilder<CirculationWallet>) {
      query.whereNull('deleted_at');
    },
  };
}

Deleting a Record

Use patch to set deleted_at instead of delete:

Code
await CirculationWallet.query()
  .findById(id)
  .patch({ deletedAt: new Date().toISOString() });

Unique Constraints with Soft Delete

Regular UNIQUE constraints conflict with soft delete -- a soft-deleted row still occupies the index, preventing recreation of the same record.

Solution: Partial Unique Index

Add WHERE deleted_at IS NULL to only enforce uniqueness on active (non-deleted) rows:

Code
CREATE UNIQUE INDEX idx_circulation_wallet_unique_active
  ON circulation_wallet (ops_token_id, wallet_address)
  WHERE deleted_at IS NULL;

Tables Using This Pattern

Table	Unique Columns	Notes
circulation_wallet	(ops_token_id, wallet_address)	Per-token wallet tracking
ops_token	(LOWER(address), network)	Case-insensitive address matching
bridge_route	(bridge_token_key, network)	One route per token per chain

UniqueViolationError Handling

Objection.js wraps PostgreSQL's unique violation (code 23505) as a UniqueViolationError from db-errors, but the PG code is not always directly exposed. Check all three possible locations:

Code
try {
  await OpsToken.query().insert(data);
} catch (error) {
  const e = error as {
    name?: string;
    code?: string;
    nativeError?: { code?: string };
  };

  if (
    e.name === 'UniqueViolationError' ||
    e.code === '23505' ||
    e.nativeError?.code === '23505'
  ) {
    throw Errors.badRequest('A token with this address and network already exists');
  }

  throw error;
}

Applied in: CirculationService.createWallet, MintingService.createWallet, OpsTokenService.createToken, OpsTokenService.updateToken, BridgeService.createRoute.

JSON Columns

Several tables use JSON/JSONB columns for flexible configuration:

Table	Column	Content
uniswap_wallet	pool_config	{ poolId, token0, token1, fee, tickSpacing }
uniswap_wallet	bot_parameters	{ rebalanceTriggerPercent, priceBandPercent, maxBudgetToken0, maxBudgetToken1 }
uniswap_wallet	oracle_tokens	Token symbols for price reference

These are defined as jsonb in migrations and typed as interfaces in Objection.js models.

Transactions

For operations that span multiple tables, use Objection.js transactions:

Code
await Model.transaction(async (trx) => {
  const wallet = await MintingWallet.query(trx).insert(walletData);
  await MintingTransaction.query(trx).insert({
    mintingWalletId: wallet.id,
    ...transactionData,
  });
});

If any operation within the transaction throws, all changes are rolled back.

BaseModel

All models extend a shared BaseModel that provides:

• id as primary key
• createdAt and updatedAt timestamps (auto-managed by $beforeInsert / $beforeUpdate)
• tableName static property
• Common modifiers

Code
class OpsToken extends BaseModel {
  static tableName = 'ops_token';

  id!: number;
  description!: string;
  address!: string;
  network!: string;
  type!: string;
  decimals!: number;
  deletedAt?: string | null;
}

knexSnakeCaseMappers

The Knex configuration uses knexSnakeCaseMappers() to automatically convert between snake_case (database) and camelCase (JavaScript):

Code
import { knexSnakeCaseMappers } from 'objection';

const knexConfig = {
  client: 'pg',
  connection: process.env.DATABASE_URL,
  ...knexSnakeCaseMappers(),
};

This means:

• Migrations use snake_case column names (wallet_address, ops_token_id)
• Models and services use camelCase property names (walletAddress, opsTokenId)
• The conversion is automatic and bidirectional