Designing Transactional Workflows with Ecto.Multi: An Architecture Note

When a single user action must create or modify several rows — say, registering an account, provisioning a profile, and writing an audit entry — the naive approach is a sequence of Repo.insert/2 calls. The problem: if the third call fails, the first two rows are already committed, and your cleanup code is now part of the failure surface. Ecto.Multi solves this by letting you declare the whole workflow as a data structure and execute it atomically in one database transaction.

This note covers when Multi is the right tool, the smallest design that works, where the trust boundaries sit, and the conditions that should push you toward a different approach. Examples assume Ecto 3.x with PostgreSQL.

Requirements that justify Multi

Reach for Ecto.Multi when all of these hold:

• Multiple writes must succeed or fail together (atomicity).
• Later steps need the results of earlier ones — for example, a profile row that references the newly inserted user's id.
• Some steps are conditional or computed, not just plain changesets.
• The whole workflow is short-lived and touches one database.

If you only need one insert, Multi is overhead. If steps must call external services or span databases, a transaction cannot help you — see the redesign conditions below.

The smallest suitable design

A Multi is a named pipeline of operations. Each step gets a unique tag, and function steps receive a map of all prior results keyed by those tags:

alias Ecto.Multi

multi =
  Multi.new()
  |> Multi.insert(:user, Accounts.user_changeset(params))
  |> Multi.insert(:profile, fn %{user: user} ->
       Accounts.profile_changeset(%{user_id: user.id}, params)
     end)
  |> Multi.run(:audit, fn repo, %{user: user} ->
       Audit.log(repo, "user_registered", user.id)
     end)

case Repo.transaction(multi) do
  {:ok, %{user: user}} -> {:ok, user}
  {:error, failed_step, reason, _changes_so_far} ->
    {:error, {failed_step, reason}}
end

Key properties of this design:

• Atomic rollback. If any step returns {:error, reason} or produces an invalid changeset, the transaction aborts and every prior insert is rolled back. There is no partial state to clean up.
• Data flows through the result map. The :profile step receives %{user: user}, so foreign keys come from real inserted rows, not guessed IDs.
• The failure tuple names the step. {:error, :profile, changeset, _} tells you exactly which operation failed, which is what you log and pattern-match on.

Trust and data boundaries

The Multi definition is the single place that knows which operations belong to the unit of work. Callers either get the full result map or an error — they can never observe a half-committed workflow. That is the boundary that matters.

Two boundaries Multi does not enforce for you:

• Constraint enforcement. Multi does not add validation. Uniqueness, foreign keys, and check constraints must come from the database (surfaced via unique_constraint/3 and friends in your changesets) or from changeset validations inside each step. A step with a valid-looking changeset can still fail at insert time if the database rejects it — which is fine, because the transaction still rolls back cleanly.
• Side effects. Anything a Multi.run/3 function does outside the database — sending email, writing files, calling an API — is not rolled back. Keep non-database effects out of the Multi; trigger them from the {:ok, result} branch after the transaction commits.

Operational checks

Because a Multi is one transaction, its failure modes are database failure modes. Verify these in a staging environment or IEx session with a configured repo:

1. Atomicity. Build the two-step Multi above, force the second step to return {:error, :forced}, and confirm the first row is absent afterward: Repo.get_by(User, email: params.email) should return nil.
2. Observability. Ecto emits telemetry events such as [:my_app, :repo, :query] for queries inside the transaction, and repo-level logging shows the transaction's begin/commit. Attach a handler with :telemetry.attach/4 and confirm total transaction duration stays within your budget (tens of milliseconds for a few inserts is typical; your numbers will vary).
3. Failure routing. Pattern-match on the failed step name and confirm your error handling surfaces something actionable — a changeset for form errors, an atom for invariant violations — rather than collapsing everything into a 500.

Failure modes and redesign triggers

The main operational risk is lock duration. Every row written inside the transaction is held until commit. A Multi with many steps, slow steps, or any blocking call inflates contention on hot tables. Keep steps fast and never perform network I/O inside Multi.run/3.

Redesign away from a single Multi when:

• Steps must call external services mid-workflow. Transactions cannot roll back an email or a payment. Move to a saga/outbox pattern: commit the database work, then dispatch side effects from a durable outbox row written in the same transaction.
• The workflow spans databases or repos. A transaction is per-connection. Cross-repo atomicity needs a different mechanism entirely.
• Steps are long-running. Imports or batch jobs that take seconds should be chunked into many small transactions, possibly with Ecto.Multi per chunk, so locks are released between chunks.
• You need partial success. Multi is all-or-nothing by design. If "three of five rows is acceptable," model that explicitly instead of fighting the rollback.

Used within its boundaries — short, single-database, all-or-nothing workflows — Multi replaces ad-hoc cleanup logic with a declarative pipeline whose failure behavior you can verify with a single forced-error test.