Durable State, Transactions, and Idempotency

Enterprise problem: A checkout acknowledgment is false if a restart erases the order, and retries or concurrent buyers can create duplicate orders or negative inventory, breaking both the delivery promise and financial reconciliation.

Whole-course context: ParcelFlow has a framework-neutral order policy, owned ports, adapter contracts, and one explicit composition root; today replaces only the in-memory persistence adapters.

Today’s slice: Add PostgreSQL through Bun.SQL, migrations, tenant-scoped idempotency, an atomic order transaction, and a guarded inventory update while retaining the fake payment adapter.

End-of-day evidence: Integration tests show one result for repeated keys, one winner under inventory contention, complete rollback after an injected failure, and the same order after application restart.

Still unsolved: Cross-service transactions, outbox events, partial network failure, real payment reconciliation, production secrets, backups, and service extraction are intentionally deferred.

The thesis is that durability is a behaviour contract, not a database checkbox: constraints and transactions must make every retry and concurrency outcome explainable. The smallest complete model is command identity, atomic state transition, and durable replayable result.

When retry and restart behaviour is unspecified, customers can be charged twice, receive two deliveries, or lose an order that was already acknowledged.

Use case ID	Actor	Customer job	Success outcome	Denial or recovery evidence
D04-UC-01	Tenant checkout client	Submit or safely retry one order after timeout	First request creates one durable order; the same tenant and idempotency key return the original result	Reusing the key with a different payload returns 409 and does not mutate order or inventory
D04-UC-02	ParcelFlow operator	Preserve valid inventory and orders through concurrency, failure, rollback, and restart	Competing requests cannot oversell; committed order survives process restart	Injected mid-transaction failure rolls back all rows; losing contender receives 409 out-of-stock evidence

Without actor-centred recovery conditions, a happy-path database test can hide duplicates and partially committed state.

Story ID	Use case IDs	User story	Observable acceptance conditions
D04-US-01	D04-UC-01	As a tenant checkout client, I want retries to return the original order, so that a timeout cannot create a second delivery	Same tenant, key, and payload return the same order ID and body; a changed payload returns 409; database contains one request and one order
D04-US-02	D04-UC-02	As a ParcelFlow operator, I want constraints and transactions to preserve inventory, so that crashes and concurrent requests cannot acknowledge impossible deliveries	Two requests for the last item yield one 201 and one 409; injected failure leaves no partial order; restart reads the committed winner

If retry identity is checked only in memory, a second process or restart can bypass it and create a duplicate customer outcome.

Flow ID	Use case IDs	Path	Trigger	Numbered steps	Terminal evidence
D04-FLOW-01	D04-UC-01, D04-UC-02	Happy	Checkout posts a valid order with Idempotency-Key	1. Validate tenant, key, and payload.<br>2. Begin Bun.SQL transaction.<br>3. Claim unique tenant/key with payload hash.<br>4. Guardedly decrement inventory.<br>5. Insert order and stored response.<br>6. Commit.<br>7. Return 201.	One idempotency row, one order, one inventory decrement, and a response body whose order ID survives restart
D04-FLOW-02	D04-UC-01, D04-UC-02	Recovery	Duplicate request, conflicting payload, inventory exhaustion, or injected transient transaction error	1. Lock or read the existing key.<br>2. Replay a matching completed result or reject a changed payload without overwriting the original.<br>3. For exhausted inventory, store and commit the terminal 409 response under the claimed key.<br>4. For a thrown transient failure, roll back the claim and all mutations so retry remains possible.<br>5. Restart and replay durable terminal results.	Matching retries reproduce the exact 201 or 409 body; payload conflict remains 409; transient rollback leaves no claim; restart replays the committed result

When table access is shared casually across modules, a later service extraction cannot establish ownership and cross-service transactions become hidden coupling.

Use case ID	Entry point	Responsible services	Authoritative store	Failure evidence
D04-UC-01	Hono POST /orders route with tenant and idempotency headers	Order application, Bun.SQL order repository, fake payment adapter	PostgreSQL orders and idempotency_requests owned by order module	Payload-hash conflict, missing terminal response, transient claim that did not roll back, or response replay mismatch
D04-UC-02	Same order request plus restart and concurrency test harness	Order transaction coordinator and inventory repository within the still-single ParcelFlow deployment	PostgreSQL inventory_items owned by inventory module and terminal 201/409 results owned by order module	Guarded update returns zero rows without a stored 409, injected SQL error fails to roll back, or restart cannot replay committed state

If constraints live only in TypeScript, another process or concurrent transaction can violate them after validation but before commit.

Generated-application database: Required in this slice — PostgreSQL is the generated application’s durable store; the order module owns orders and idempotency results, while the inventory module owns stock state.

Record or entity	Store and owner	Primary key	Foreign key or opaque reference	Tenant key	Material constraint	Lifecycle and deletion	Use case IDs
IdempotencyRequest	PostgreSQL orders.idempotency_requests, order module	Composite tenant_id, idempotency_key	Nullable order_id local FK because a completed 409 has no order	tenant_id is part of primary key	Payload hash is immutable; one key maps to one terminal 201 or 409 response; a thrown transient failure leaves no committed claim	Claimed and completed in one transaction, retained for bounded retry window, expired only after the associated order or rejection policy permits	D04-UC-01, D04-UC-02
Order	PostgreSQL orders.orders, order module	order_id UUID	payment_authorization_id opaque fake-adapter reference	tenant_id required and indexed	Tenant plus order identity is unique; accepted quantity is positive; idempotency request references exactly one order	Created atomically with reservation, retained for support/audit window, tenant-authorized export, tombstoned before policy deletion	D04-UC-01, D04-UC-02
InventoryItem	PostgreSQL inventory.inventory_items, inventory module	Composite tenant_id, sku	None — stock item is authoritative root state for inventory	tenant_id is part of primary key	available >= 0; guarded update checks sufficient quantity and expected version, then increments version	Seeded by inventory process, updated per reservation, retained while SKU active, archived or deleted only after order references age out	D04-UC-02

When database calls spread through policy code, transactions, tenant predicates, and query semantics cannot be reviewed or tested as one owned boundary.

The general rule is to keep SQL behind the ports defined by the application. The simple ParcelFlow adapter uses the built-in Bun.SQL tagged-template API and passes a transaction-scoped client through one repository operation. A realistic service configures a bounded pool, connection timeout, TLS, statement observability, and graceful sql.close(). The failure mode is opening a client per request or accidentally issuing one query through the global pool while the rest use a transaction connection. The decision rule is: a unit of work receives one scoped SQL client, and every query in that unit uses it.

import { SQL } from "bun";

const sql = new SQL(process.env.DATABASE_URL!, {
  max: 10,
  connectionTimeout: 10,
});

export async function closeDatabase(): Promise<void> {
  await sql.close({ timeout: 5 });
}

Bun documents Bun.SQL as a Promise-based API for PostgreSQL, MySQL, and SQLite. For PostgreSQL, sql.begin reserves a dedicated pooled connection, commits when the callback completes, and rolls back when it throws: Bun SQL documentation.

If production schema changes are improvised at startup, two replicas can race, rollback is unclear, and application code may run against a half-applied shape.

The general rule is to version schema migrations separately from request startup and make each owner explicit. The simple migration creates three tables and their constraints. A realistic deployment uses a single migration job, a dedicated high-privilege migration role, lower-privilege runtime roles per schema, forward-compatible expand/migrate/contract changes, and a tested restore procedure. The failure mode is granting the order runtime direct access to every future service table. The decision rule is: one module or service owns writes to its schema; sharing a PostgreSQL cluster does not mean sharing authority.

CREATE SCHEMA IF NOT EXISTS orders;
CREATE SCHEMA IF NOT EXISTS inventory;

CREATE TABLE orders.orders (
  order_id uuid PRIMARY KEY,
  tenant_id text NOT NULL,
  sku text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  payment_authorization_id text NOT NULL,
  status text NOT NULL CHECK (status IN ('accepted', 'cancelled')),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, order_id)
);

CREATE TABLE orders.idempotency_requests (
  tenant_id text NOT NULL,
  idempotency_key text NOT NULL,
  request_hash text NOT NULL,
  order_id uuid REFERENCES orders.orders(order_id),
  response_status integer,
  response_body jsonb,
  state text NOT NULL CHECK (state IN ('claimed', 'completed')),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, idempotency_key)
);

CREATE TABLE inventory.inventory_items (
  tenant_id text NOT NULL,
  sku text NOT NULL,
  available integer NOT NULL CHECK (available >= 0),
  version bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (tenant_id, sku)
);

When an API merely asks “have I seen this key?” before writing, two concurrent requests can both see absence and create duplicate orders.

Idempotency means repeating the same operation identifier has the same externally visible effect. The simple rule makes (tenant_id, idempotency_key) unique and stores a hash of the canonical request plus every deterministic terminal response, including out-of-stock 409. A realistic handler atomically claims the key, locks an existing claim when necessary, rejects a different payload without overwriting the original result, and replays the completed response. A thrown connection, serialization, or other transient infrastructure error is different: the transaction rolls back the claim so the same key can retry. The failure mode is throwing a terminal domain rejection inside the transaction, because that also erases the response meant to make the rejection idempotent. The decision rule is: commit deterministic outcomes; roll back transient inability to decide.

type SqlClient = typeof sql;

type PlaceOrderResponse =
  | { status: 201; body: { orderId: string; status: "accepted" } }
  | { status: 409; body: { code: "out_of_stock"; sku: string } }
  | { status: 409; body: { code: "idempotency_conflict" } };

async function storeCompletedResponse(
  tx: SqlClient,
  tenantId: string,
  key: string,
  response: PlaceOrderResponse,
): Promise<void> {
  await tx`
    UPDATE orders.idempotency_requests
    SET state = 'completed',
        response_status = ${response.status},
        response_body = ${JSON.stringify(response.body)}::jsonb
    WHERE tenant_id = ${tenantId} AND idempotency_key = ${key}
  `;
}

async function loadAndValidateReplay(
  tx: SqlClient,
  command: PlaceOrderCommand,
  key: string,
): Promise<PlaceOrderResponse> {
  const [existing] = await tx`
    SELECT request_hash, response_status, response_body
    FROM orders.idempotency_requests
    WHERE tenant_id = ${command.tenantId} AND idempotency_key = ${key}
    FOR UPDATE
  `;
  if (!existing) throw new Error("idempotency row disappeared");
  if (existing.request_hash !== hashCommand(command)) {
    return { status: 409, body: { code: "idempotency_conflict" } };
  }
  return {
    status: existing.response_status,
    body: existing.response_body,
  } as PlaceOrderResponse;
}

async function placeDurably(
  command: PlaceOrderCommand,
  key: string,
): Promise<PlaceOrderResponse> {
  return sql.begin(async (tx: SqlClient) => {
    const requestHash = hashCommand(command);
    const [claim] = await tx`
      INSERT INTO orders.idempotency_requests
        (tenant_id, idempotency_key, request_hash, state)
      VALUES
        (${command.tenantId}, ${key}, ${requestHash}, 'claimed')
      ON CONFLICT (tenant_id, idempotency_key) DO NOTHING
      RETURNING tenant_id
    `;

    if (!claim) return loadAndValidateReplay(tx, command, key);

    const [stock] = await tx`
      UPDATE inventory.inventory_items
      SET available = available - ${command.quantity}, version = version + 1
      WHERE tenant_id = ${command.tenantId}
        AND sku = ${command.sku}
        AND available >= ${command.quantity}
      RETURNING available, version
    `;
    if (!stock) {
      const rejected: PlaceOrderResponse = {
        status: 409,
        body: { code: "out_of_stock", sku: command.sku },
      };
      await storeCompletedResponse(tx, command.tenantId, key, rejected);
      return rejected;
    }

    const order = await insertOrder(tx, command);
    const accepted: PlaceOrderResponse = {
      status: 201,
      body: { orderId: order.orderId, status: "accepted" },
    };
    await storeCompletedResponse(tx, command.tenantId, key, accepted);
    return accepted;
  });
}

PostgreSQL enforces uniqueness through unique indexes and supports conflict handling atomically with INSERT ... ON CONFLICT: PostgreSQL INSERT. Do not implement “select then insert” as an application race.

If order insertion and inventory reservation commit independently, a crash can create a paid order with no stock or reduce stock for an order that does not exist.

The general rule is to put state changes that must succeed or fail together in one local database transaction. In today’s still-single deployment, order and inventory schemas share PostgreSQL, so the first implementation can atomically claim the key, guard stock, insert the order, and store either the accepted 201 or deterministic out-of-stock 409 response. Returning a 409 from the callback commits it; throwing a transient database error rolls back the claim and every mutation. A realistic extracted inventory service will own its database and refuse cross-service SQL; later days replace the cross-boundary transaction with explicit contracts and events. The failure mode is holding a database transaction open across a real remote payment call. The decision rule is: keep transactions local to one owner; external effects need their own idempotency and recovery protocol. ParcelFlow still uses a deterministic fake payment adapter, not a real processor.

For a hot inventory row, the atomic guarded UPDATE ... WHERE available >= quantity is the simplest concurrency control. Adding AND version = expected_version gives optimistic concurrency: a stale writer updates zero rows and may re-read and retry with a bounded policy. PostgreSQL’s transaction-isolation documentation explains concurrent updates and why serializable transactions may require retries: transaction isolation. The database CHECK (available >= 0) is a last invariant, not the customer-facing out-of-stock decision.

When persistence tests use mocks, they cannot prove unique constraints, transaction rollback, connection lifecycle, or actual PostgreSQL concurrency.

The general rule is to run repository contracts and state-transition tests against a real disposable PostgreSQL instance. The simple suite starts with a clean migrated database and invokes two concurrent commands for the last unit. A realistic CI suite also kills the application between write and read, injects an error after inventory update, tests migrations from the previous schema, and captures SQLSTATE plus correlation IDs. The failure mode is truncating tables between assertions in a way that hides transaction boundaries. The decision rule is: prove observable state before and after failure using independent database reads.

import { expect, test } from "bun:test";

test("one contender wins the final unit", async () => {
  await seedInventory({ tenantId: "tenant-a", sku: "parcel-small", available: 1 });
  const responses = await Promise.all([
    placeDurably(order("tenant-a"), "key-a"),
    placeDurably(order("tenant-a"), "key-b"),
  ]);

  expect(responses.map(({ status }) => status).sort()).toEqual([201, 409]);
  const rejectedKey = responses[0].status === 409 ? "key-a" : "key-b";
  const replay = await placeDurably(order("tenant-a"), rejectedKey);
  expect(replay).toEqual(responses.find(({ status }) => status === 409));
  expect(await countOrders("tenant-a")).toBe(1);
  expect(await availableStock("tenant-a", "parcel-small")).toBe(0);
});

test("injected failure rolls back reservation and order", async () => {
  await expect(
    placeWithFailureAfterReservation("tenant-a", "transient-key"),
  ).rejects.toThrow("injected");
  expect(await countOrders("tenant-a")).toBe(0);
  expect(await availableStock("tenant-a", "parcel-small")).toBe(1);
  expect(await countIdempotencyRequests("tenant-a", "transient-key")).toBe(0);
});

docker compose up -d postgres
bun run db:migrate
bun test tests/integration/orders-postgres.test.ts
bun run src/server.ts
# Stop and restart the process, then GET the committed order with the same tenant.

These commands and tests define the required proof sequence; this lesson does not claim they were executed here.

Without database-enforced invariants, retries and concurrency turn apparently correct TypeScript into duplicate or impossible customer outcomes.

• Bun.SQL is an adapter behind application-owned ports, not a reason to put SQL in policy.
• A unique tenant-plus-idempotency key, immutable request hash, and stored response define safe replay.
• Commit terminal 201 and 409 results for exact replay; roll back transient failures and their claims.
• Guard inventory atomically and treat zero updated rows as conflict, exhaustion, or retry evidence.
• Keep transactions local to one owner; do not hold them open across real remote effects.
• Separate schema ownership and credentials even when modules temporarily share one PostgreSQL cluster.
• Prove durability through real migration, rollback, restart, and concurrency integration tests.

If any restart, duplicate, or contention outcome is unobserved, ParcelFlow cannot honestly promise durable order acceptance.

• [ ] Apply versioned migrations with a dedicated migration role.
• [ ] Enforce (tenant_id, idempotency_key) uniqueness in PostgreSQL.
• [ ] Store request hash and exact terminal response for replay.
• [ ] Repeat an out-of-stock key and verify the exact stored 409 response after restart.
• [ ] Inject a transient SQL failure and verify no idempotency claim remains.
• [ ] Guard inventory with tenant, quantity, and optional expected version.
• [ ] Inject a mid-transaction error and independently verify rollback.
• [ ] Restart the Bun process and read the committed order from PostgreSQL.
• [ ] Keep fake payment explicit and defer real external payment coordination.

When transaction and concurrency guidance lacks primary references, subtle driver or database semantics can be taught incorrectly.

• Bun SQL API, pooling, transactions, and reserved connections
• PostgreSQL INSERT and ON CONFLICT
• PostgreSQL transaction isolation
• PostgreSQL constraints