Application Integration & Schema Evolution

Application Transaction Design

An application transaction should protect one explicit invariant with a short, retryable unit of database work. Deadlines, cancellation, ambiguous commit, and external side effects require outcome protocols beyond a convenient transaction annotation.

Start from the invariant and unit of work.
Keep remote calls outside database transactions.
Deadlines must reach the server.
Commit acknowledgement can be lost.
Retries replay the whole unit.
Atomicity stops at the database boundary.

Define the durable operation and outbox objects

CREATE TABLE checkout_operations (
  operation_id VARCHAR(80) PRIMARY KEY,
  order_id BIGINT NOT NULL UNIQUE REFERENCES orders(order_id),
  completed_at TIMESTAMP NOT NULL
);

CREATE TABLE application_outbox (
  event_id VARCHAR(80) PRIMARY KEY,
  aggregate_type VARCHAR(40) NOT NULL,
  aggregate_id BIGINT NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  payload_json VARCHAR(8000) NOT NULL,
  occurred_at TIMESTAMP NOT NULL,
  published_at TIMESTAMP NULL
);

Checkout unit of work

validate request and obtain operation_id
BEGIN
  if checkout_operations contains operation_id: return recorded outcome
  lock/read the pending order and verify invariant
  update orders from PENDING to PAID with a guarded predicate
  insert checkout_operations(operation_id, order_id, completed_at)
  insert application_outbox(event_id, ..., "OrderPaid", ...)
COMMIT
After commit, an independent relay publishes; consumers deduplicate event_id.
On lost COMMIT acknowledgement, query operation_id before retrying.

External effect after durable handoff

Outcome handling
Observation	Meaning	Action
statement rejected before commit	transaction failed/not committed	rollback; classify
serialization/deadlock abort	unit did not commit	bounded whole-unit retry
client deadline/cancel	server outcome may lag	drain and inspect; rollback/reset
connection lost during COMMIT	commit unknown	reconcile durable operation key
message publish failed	database may be committed	relay retries; do not roll back history