Transactions

In the Fragno database layer, transactions are built around a two-phase pattern:

• Retrieval phase: schedule reads, then execute them together
• Mutation phase: schedule writes, then execute them atomically

In terms of code organization, the important architectural rule is:

• Route handlers control transaction execution via this.handlerTx()
• Services define reusable operations via this.serviceTx(schema)

This lets you call multiple service methods inside one transaction, and the DB work will be batched.

Route handlers: control the transaction boundary

In route handlers, use this.handlerTx() to build and execute transactions:

const [subscribers] = await this.handlerTx()
  .withServiceCalls(() => [services.getSubscribers({ search, sortBy, pageSize, cursor })])
  .execute();

The handlerTx() builder:

• automatically retries on optimistic concurrency conflicts
• chains .retrieve(), .mutate(), .withServiceCalls() to compose operations
• ends with .execute() to run the transaction

Preserve Tuple Types With serviceCalls()

When you build service calls dynamically (e.g. Array.from(...) + spread), TypeScript widens the tuple and you lose precise types for serviceResult / serviceIntermediateResult. Use serviceCalls(...) to preserve tuple inference:

import { serviceCalls } from "@fragno-dev/db";

const historyCalls = Array.from({ length: 3 }, (_, runNumber) =>
  services.listHistory({ workflowName, instanceId, runNumber }),
);

const [status, runNumber, ...history] = await this.handlerTx()
  .withServiceCalls(() =>
    serviceCalls(
      services.getInstanceStatus(workflowName, instanceId),
      services.getInstanceRunNumber(workflowName, instanceId),
      ...historyCalls,
    ),
  )
  .execute();

Services: define reusable operations

Services define database operations using this.serviceTx(schema), returning a TxResult that handlers can execute:

Example pattern (mailing-list subscribe()):

subscribe: function (email: string) {
  return this.serviceTx(mailingListSchema)
    .retrieve((uow) =>
      uow.find("subscriber", (b) =>
        b.whereIndex("idx_subscriber_email", (eb) => eb("email", "=", email)),
      ),
    )
    .mutate(({ uow, retrieveResult: [existing] }) => {
      if (existing.length > 0) {
        return { alreadySubscribed: true, id: existing[0].id, email };
      }

      const id = uow.create("subscriber", { email, subscribedAt: new Date() });
      return { alreadySubscribed: false, id, email };
    })
    .build();
}

The serviceTx() builder:

• chains .retrieve() and .mutate() to define operations
• ends with .build() to return a TxResult
• does not execute — handlers execute service results via .withServiceCalls()

Why this design?

Because services return TxResult instead of executing directly, you can:

• call multiple service methods in one handler transaction
• batch all database operations together
• get automatic retry handling from the handler

As a result of these design decisions:

• transactions never block the database
• database round-trips are amortised
• every successful transaction only has one round-trip (read-only) or two (read-write)

Optimistic concurrency with .check()

Fragno DB supports optimistic concurrency control using a hidden _version column. When you read a row, its id is a FragnoId that also carries version information.

Calling .check() on a mutation tells Fragno:

"Only apply this update/delete if the row's version is still the one I read."

If the row was modified by another transaction in between, the mutation phase will fail and handlerTx() will retry the whole transaction (up to the retry policy).

update(...).check() / delete(...).check()

You can enable version checking on updates and deletes:

updateUser: function (userId: FragnoId) {
  return this.serviceTx(mySchema)
    .mutate(({ uow }) => {
      uow.update("users", userId, (b) => b.set({ name: "New name" }).check());
    })
    .build();
}

Retrying unique mutation races

Concurrent transactions can both observe a unique value as available before one create or update claims it. Mark that mutation as retryable so the unique-constraint race retries the complete transaction:

createEvent: function (eventId: string, payload: EventPayload) {
  return this.serviceTx(mySchema)
    .retrieve((uow) =>
      uow.findFirst("events", (b) =>
        b.whereIndex("primary", (eb) => eb("id", "=", eventId)),
      ),
    )
    .mutate(({ uow, retrieveResult: [existing] }) => {
      if (existing) {
        return existing;
      }

      uow.create("events", { id: eventId, payload }, {
        retryOnUniqueConflict: ({ error }) =>
          error.columns?.length === 1 && error.columns[0] === "id",
      });
      return { id: eventId, payload };
    })
    .build();
}

Updates opt in through their builder and can combine this behavior with row-version checking:

uow.update("users", user.id, (b) =>
  b
    .set({ slug })
    .check()
    .retryOnUniqueConflict(({ error }) => error.constraint === "users_slug_idx"),
);

The callback receives the normalized DatabaseConstraintError and logical mutation context. Return true only when the retrieval phase checks that unique key and resolves the conflict on retry; returning false preserves the original constraint error. Constraint metadata varies by database, so fail closed when the callback cannot identify the intended constraint. .check() protects the updated row's version, while .retryOnUniqueConflict(...) handles the accepted unique race.

Standalone uow.check(table, id)

Use a standalone check to guard a row without updating it:

transfer: function (from: FragnoId, to: FragnoId) {
  return this.serviceTx(mySchema)
    .mutate(({ uow }) => {
      uow.check("accounts", from);
      uow.check("accounts", to);
      uow.create("transfers", { fromAccountId: from.toString(), toAccountId: to.toString() });
    })
    .build();
}

Assert absence with uow.checkAbsent()

Use a complete, non-null unique-index key to assert that no matching row exists:

uow.checkAbsent("file", "idx_file_provider_key", {
  provider,
  key: fileKey,
});

The unique constraint remains the final guard against a concurrent create after the check.

Important: .check() requires a FragnoId, not a string

• ✅ uow.check("users", user.id)
• ❌ uow.check("users", "user-123")

Updates and deletes can use a string ID without .check(), but then use last-write-wins semantics.

End-to-end example: handler controls execution, service performs the transfer

• Route handler: owns the transaction boundary via handlerTx()
• Service: defines operations with .check() for optimistic locking

import { ExponentialBackoffRetryPolicy } from "@fragno-dev/db";

// Route handler - controls transaction execution
defineRoute({
  method: "POST",
  path: "/transfer",
  handler: async function ({ input }, { json }) {
    const { fromAccountId, toAccountId, amount } = await input.valid();

    const [result] = await this.handlerTx()
      .withServiceCalls(() => [
        services.transferBetweenAccounts({ fromAccountId, toAccountId, amount }),
      ])
      .execute({
        retryPolicy: new ExponentialBackoffRetryPolicy({
          maxRetries: 5,
          initialDelayMs: 10,
          maxDelayMs: 250,
        }),
      });

    return json(result);
  },
});

defineService({
  transferBetweenAccounts: function (args: {
    fromAccountId: string;
    toAccountId: string;
    amount: number;
  }) {
    const { fromAccountId, toAccountId, amount } = args;

    return this.serviceTx(mySchema)
      .retrieve((uow) =>
        uow
          .findFirst("accounts", (b) =>
            b.whereIndex("primary", (eb) => eb("id", "=", fromAccountId)),
          )
          .findFirst("accounts", (b) =>
            b.whereIndex("primary", (eb) => eb("id", "=", toAccountId)),
          ),
      )
      .mutate(({ uow, retrieveResult: [from, to] }) => {
        if (!from || !to) {
          return { ok: false as const };
        }

        if (from.balance < amount) {
          return { ok: false as const };
        }

        // Mutations with version checks
        uow.update("accounts", from.id, (b) => b.set({ balance: from.balance - amount }).check());
        uow.update("accounts", to.id, (b) => b.set({ balance: to.balance + amount }).check());

        return { ok: true as const };
      })
      .build();
  },
});

Retries and side effects

handlerTx() automatically retries the whole transaction on optimistic concurrency conflicts.

That means you should avoid side effects (sending emails, webhooks, etc.) inside service code that runs during the transaction.

Use Durable Hooks for that: they record the side effect request in the same transaction and execute it after commit.

Error handling

Handlers are responsible for error handling, not services:

• Handlers call .execute() and should catch errors
• Services return TxResult via .build() — they don't execute or handle errors
• If .execute() throws, the handler catches it and decides how to respond

This design keeps error handling centralized at the transaction boundary (the handler), where you have the full context to decide how to respond to the user.