Correctness Before Convenience: Designing a Trading Backend

CRUD convenience is a sensible priority for many applications. A trading system changes the order of concerns.

Balances must not become negative. Funds must not be spent twice. An order must not produce duplicate trades. A retried settlement must not pay the same account twice. These are not interface details; they are invariants that must survive concurrency, process crashes, and deployment changes.

That leads to a useful architectural rule:

Choose tools that make correctness visible before choosing tools that make the common path short.

An ORM Cannot Own the Invariants

An ORM can provide typed queries, transactions, migrations, and protection against accidental SQL injection. It cannot infer the business rules of a market.

For example, this code looks reasonable:

const account = await db.query.accounts.findFirst({
  where: eq(accounts.id, accountId),
});

if (account.balance >= amount) {
  await db
    .update(accounts)
    .set({ balance: account.balance - amount })
    .where(eq(accounts.id, accountId));
}

Two requests can read the same balance before either update runs. Both checks pass, and both spend the same funds.

The fix is not a different method name. The operation needs an explicit concurrency strategy, such as an atomic conditional update:

UPDATE accounts
SET balance = balance - $1
WHERE id = $2
  AND balance >= $1;

The application then verifies that exactly one row changed. Another valid design might lock the account row inside a transaction. The right choice depends on the larger workflow, but the invariant must be represented in something the database can enforce.

This is why a SQL-visible tool such as Drizzle can be attractive for transaction-heavy systems. It gives TypeScript assistance without removing the need to understand the query, lock, constraint, or transaction being executed.

Money Should Not Depend on Binary Floating Point

JavaScript uses IEEE 754 binary floating-point numbers for number. Many decimal fractions cannot be represented exactly:

0.1 + 0.2 === 0.3; // false

That behavior is harmless in many interfaces and dangerous in accounting logic.

One common approach is to store integer minor units:

Displayed amount: 12.34 USDC
Stored amount:    12,340,000
Scale:            10^6

If values can exceed JavaScript’s safe integer range, use bigint or another representation that preserves the integer exactly.

Another approach is PostgreSQL numeric, which stores exact decimal values. Node.js code should then keep values as strings or use a decimal library rather than immediately converting them to number.

The representation is a domain decision. It should specify scale, rounding rules, overflow behavior, and where conversions are allowed.

Production Schema Changes Need Reviewable History

Drizzle Kit supports more than one schema workflow. drizzle-kit push compares the TypeScript schema with the database and applies the resulting changes directly. That is useful for rapid local iteration.

A production change has more questions:

• What SQL will run?
• Will a table rewrite or lock block traffic?
• How will existing rows be backfilled?
• Can the old and new application versions run during the deployment?
• What happens if the migration stops halfway through?
• Which environments have already applied it?

A reviewable migration flow makes those questions visible:

Change TypeScript schema
        ↓
Generate migration SQL
        ↓
Review and commit it
        ↓
Test against representative data
        ↓
Apply and record the migration

drizzle-kit generate creates SQL migration files, while drizzle-kit migrate applies migrations that have not yet been recorded as complete. Generated SQL is a starting point, not a substitute for reviewing operational impact.

Shared Database Code Needs a Real Boundary

In a monorepo, the API and background worker often need the same schema, connection factory, migration metadata, and transaction types:

API ─────→ Database package
Worker ──→ Database package

Putting that code in a workspace package creates an explicit boundary:

apps/
├── api/
└── worker/

packages/
└── database/

The package can expose stable entry points:

import { createDatabase } from "@market/database";

Consumers should not reach into packages/database/src/internal/.... A package exports map can define the supported surface and allow internal files to change without breaking every application.

The database package should contain shared infrastructure, not every use case. Order placement, authorization, settlement policy, and market rules belong in domain or application layers. Otherwise the “shared” package becomes a hidden monolith that every process depends on.

Workers Turn Retries Into a Correctness Problem

An API handles work while a user is waiting. A worker handles asynchronous, scheduled, expensive, or retryable work:

Browser → API → PostgreSQL
              ↓
          Redis queue
              ↓
            Worker

A worker may publish outbox events, reconcile balances, close expired markets, build snapshots, or confirm blockchain transactions.

Separating it from the API allows independent scaling and failure isolation. It also introduces an important failure window:

1. Worker applies a settlement.
2. Worker crashes before acknowledging the job.
3. Queue delivers the job again.

The worker must behave correctly when the same job runs more than once. Typical protections include:

• a stable idempotency key;
• a unique database constraint;
• a business state transition that can happen only once;
• a transaction that applies the effect and records completion together;
• reconciliation for uncertain external side effects.

Retries are operationally useful only when repeated execution is safe.

Concurrency Control Is a Designed System

No single transaction isolation level eliminates the need for design. A trading backend usually combines several mechanisms:

• atomic conditional updates for simple invariants;
• row locks for multi-step changes to known records;
• unique and check constraints as the final database guard;
• a consistent lock order to reduce deadlocks;
• bounded retries for deadlocks or serialization failures;
• tests that create real concurrent requests.

PostgreSQL detects deadlocks by aborting one transaction, so the application must be prepared to retry an operation that is safe to repeat. A retry loop without a limit, backoff, or idempotency can create a different failure mode.

The Architectural Test

Before selecting a shortcut, ask whether it preserves the evidence needed to reason about correctness:

1. Can we identify the transaction boundary?
2. Can we inspect the generated SQL?
3. Does the database enforce the invariant?
4. Is the numeric representation exact?
5. Can a migration be reviewed and replayed?
6. Can a worker safely process the same job twice?
7. Can a failed operation be reconciled?
8. Do tests exercise competing operations rather than only sequential happy paths?

Frameworks and ORMs are valuable because they provide capabilities. They do not prove that balances, orders, trades, and settlements remain correct.

In a trading backend, the best abstraction is not the one that hides the database most completely. It is the one that removes repetition while leaving transactions, constraints, numeric semantics, and failure behavior clear enough to verify.