06 · Development · Constraints
Without a unique constraint on request ID, a retry inserts a second row
The database engineer designs tables, constraints, and migrations so document numbers and ledgers can block duplicates, instead of relying on the program to remember to check. Slow queries and killing sessions in production belong to database operations.
The database engineer uses constraints to keep document numbers and balances
The backend engineer decides which tables this business writes. The database engineer decides how those tables refuse dirty data. The job’s mark is putting key invariants into the database, not only into one code review. The same request ID cannot have two ledger rows. The same document cannot have two external document numbers. A closed document cannot be updated back to draft. These are blocked with unique constraints and foreign keys or state checks. If the app only checks then inserts, two concurrent requests will both pass the query.
He takes part in migration scripts. Add column, backfill, create index must be able to finish on production data volume, and have a rollback. Hand-editing tables in production is not delivery. Indexes target real filters: warehouse plus state plus date, not every field. Indexing every field slows receiving writes. The dock will feel the system stuck, then double-tap.
Transactions and locks
He looks at transaction boundaries with backend. Calling an external API inside a long transaction leaves the lock until timeout. The database engineer points out which updates must be short, and which logs can be async. If a deadlock appears at receiving peak, he must locate the concrete document from the waiting statement, not restart the database. Restart turns half-finished requests into unknown state. Integration fears that most.
On permission, the application account cannot own drop-table and free full-database export. Reports use a read-only account. People use personal accounts, recoverable at expiry. A shared admin password is a defect on this role, not a convenience.
The time the balance was overwritten
One implementation used “read the balance, add qty, write the balance back.” Two concurrent receipts overwrote each other. One posting was lost. The database engineer changed it to insert an immutable ledger. Balance is summed from the ledger or atomically updated in a short transaction, and request ID gets a unique constraint. Historical wrong books are recalculated from the ledger, not from the last overwritten balance. After that, a concurrent script submitting two at once produced two ledger rows, not one.
The go-live script ran on a staging copy at production volume. The time went into the release notes. Database operations schedules the window from that time, instead of discovering on go-live day that backfill takes four hours. The database engineer also specifies archive. Ledgers closed past the agreed period can move to a history table, but the unique request ID must also stay in the archive table, so an old request ID is not reused by a new transaction. Once a request ID is reused, idempotency will treat two receipts far apart as the same posting. Archive scripts and online constraints are reviewed together. Rows are not deleted on the day the disk alerts. Batch backfill must be batched, and record which primary key has been processed. Mid-fail can continue from the breakpoint, not lock the whole table and rerun. A table lock will hold receiving at the dock. If a stats field is redundant, he writes which event maintains it, and forbids people to change it directly.
Once a human-edited summary disagrees with the ledger, every later report is forced to choose which number to believe. After go-live, what he watches is not autoincrement and fragmentation. It is someone bypassing the transaction to fix a number. Fixing a number must have a script, a backup point, affected row count, and a reviewer. Missing any of the four, it goes back. Going back is not because process looks good. It is because once a receiving ledger is hand-edited, idempotency and write-back lose their reference. Explaining from memory the next day which rows changed is already too late.
Constraints before conscience. That is the sentence the database engineer leaves for development.