Project 22: Transactional Inventory Ledger and Reservation API

Build an Ecto-backed API whose append-only movements and atomic reservations prove that concurrent buyers cannot create negative stock or duplicate business effects.

Quick Reference

Attribute Value
Difficulty Level 3
Suggested Seniority Senior Elixir developer
Time Estimate 18-28 hours
Main Programming Language Elixir
Alternative Programming Languages Erlang, Gleam
Coolness Level Level 3
Business Potential Level 4
Prerequisites Elixir structs and result tuples, SQL transactions, HTTP API basics
Key Topics Ecto schemas, changesets, constraints, Ecto.Multi, ledger invariants, idempotency, concurrency

1. Learning Objectives

By completing this project, you will be able to:

  1. Represent inventory as an append-only movement ledger with a derived availability projection.
  2. Translate business invariants into database constraints and transactional operations rather than preflight checks alone.
  3. Compose reserve, release, and commit workflows with Ecto.Multi and interpret step-specific failure values.
  4. Make retries safe through idempotency keys and unique business constraints.
  5. Choose and justify row locking, conditional updates, or optimistic locking for a contested SKU.
  6. Write concurrent tests that prove stock never becomes negative and a request never creates two ledger effects.

2. All Theory Needed (Per-Concept Breakdown)

Transactional Invariants and Append-Only Ledgers

Fundamentals

An inventory system is correct only when its invariants hold across concurrent requests, failures, and retries. “Available quantity is not negative” is not a validation on an HTTP payload; it is a rule over durable state while many transactions compete. A ledger records immutable movements such as receipt, reserve, release, and commit, while a projection summarizes on-hand and reserved quantities for efficient decisions. Ecto changesets validate individual values and convert database constraint violations into explicit errors, but they do not make separate reads and writes atomic. Ecto.Multi groups named database operations into one transaction and returns the precise failed step. Correctness requires placing the decisive comparison and mutation inside the database transaction, using constraints, locks, or conditional updates so that two valid-looking requests cannot both spend the same stock.

Deep Dive into the concept

Start by defining the vocabulary. on_hand represents physical units currently controlled by the warehouse. reserved represents units promised to active orders but not yet committed. available = on_hand - reserved is derived. A receipt increases on-hand. A reservation increases reserved. A release decreases reserved because an order was cancelled or expired. A commit decreases both on-hand and reserved because reserved units have left inventory. Every transition needs a reference to the business command that caused it.

An append-only ledger provides auditability. Instead of overwriting “quantity = 7,” insert a movement saying “reserve 3 for order A” and update a projection in the same transaction. The ledger explains how state arose; the projection prevents every request from summing all history. These two representations must not drift. The transaction therefore inserts exactly one movement and updates exactly one corresponding projection, or commits neither. A reconciliation command can recompute projection totals from the ledger and report mismatches.

The fundamental concurrency bug is check-then-write. Suppose availability is five and two callers each request four. Both read five, both pass an application validation, and both update reserved, yielding eight. Changesets cannot prevent this if the condition depends on a stale read. A pessimistic design locks the SKU projection row during the transaction, calculates availability, and updates before releasing the lock. A conditional-update design performs one SQL update whose predicate requires enough availability and checks whether exactly one row changed. An optimistic design stores a lock version, updates only the version read, and retries on stale versions. Each is legitimate under different contention and latency profiles. The project should implement one and document why.

Database isolation matters. A transaction makes its own operations atomic but does not automatically serialize every business decision. Under common isolation, two transactions can read the same committed row unless they lock or use a conditional write. The invariant must be protected where races are arbitrated: the database. Add check constraints such as non-negative on_hand and reserved, uniqueness on movement business identity, and foreign keys. A constraint is a backstop, not a complete domain model; it should be surfaced through a named changeset error rather than as an unhandled exception.

Idempotency handles uncertainty after a client submits a command. If a network times out after commit, the client cannot know whether retrying will duplicate the reservation. Give each command an idempotency key within a defined scope and store it with the resulting movement or command record under a unique constraint. A repeated request with the same key and equivalent input returns the recorded outcome. Reusing the key with different input is a conflict, not a second operation. The uniqueness check must be part of the transaction; an application lookup followed by insertion still races.

Ecto.Multi is useful because inventory workflows contain several related operations: claim idempotency, lock/load projection, validate transition, insert movement, update projection, and record an outbox notification. Each step has a name. On success, the caller receives a map of results; on failure, it receives the failed operation name, reason, and prior successful values, all rolled back. This encourages explicit result handling with pattern matching instead of exceptions for normal conflicts. Multi.run can express calculations that depend on prior results, but long network calls must stay outside the transaction because they extend lock time and make database availability depend on external services.

Reservation lifecycle is a state machine even if it is represented relationally. An active reservation can be committed once or released once; committed and released are terminal. Expiry is a release caused by time. State changes should use predicates or locking so a commit and expiry worker cannot both create effects. The ledger movement references the reservation, and uniqueness prevents more than one terminal movement. If external publication is needed, add an outbox row in the same transaction and publish it later; do not call a message broker before commit and hope rollback will retract it.

Error taxonomy is part of the API contract. Distinguish invalid quantity, unknown SKU, insufficient availability, duplicate-equivalent command, idempotency conflict, invalid reservation transition, stale lock, and temporary database failure. 409 Conflict is suitable for business conflicts such as insufficient stock or mismatched idempotency reuse; malformed input remains 422; an unknown resource is 404; transient storage failure is 503. Return the current availability carefully: it may already be stale by the time the client sees it, so describe it as an observation, not a promise.

Testing must create real overlap. Sequential tests prove arithmetic, not concurrency control. Use a barrier so many processes begin reservations together against a database sandbox configuration that permits concurrent connections. After completion, assert that total successful quantity is at most the starting quantity, the projection is non-negative, ledger totals reconcile, each idempotency key appears once, and failed transactions left no partial rows. Repeat enough times to catch timing bugs, while keeping a deterministic aggregate assertion.

The main lesson is that Elixir’s explicit data and Ecto’s composable transactions make the workflow readable, but correctness ultimately depends on a database-enforced serialization point. The process that receives a request may crash; a client may retry; an expiry worker may race a checkout. If the durable constraints and transactional transition remain correct, those failures become ordinary recoverable events.

How this fits on projects

The API uses changesets for input and constraint mapping, Ecto.Multi for named atomic steps, and an append-only movement model for audit. Concurrency tests prove the chosen database arbitration strategy.

Definitions & key terms

  • Invariant: Rule that must hold before and after every committed transition.
  • Ledger movement: Immutable record of a quantity change and its business cause.
  • Projection: Derived current-state table optimized for decisions.
  • Idempotency key: Client-supplied identity that makes retry outcomes stable.
  • Pessimistic lock: Database lock that excludes conflicting writers during a transaction.
  • Optimistic lock: Version check that rejects a stale writer.
  • Outbox: Durable event record inserted with domain changes and published afterward.

Mental model diagram

HTTP command + idempotency key
             |
             v
        [Changeset]
             |
             v
  +------ Ecto.Multi transaction ------------------------------+
  | claim key -> lock/compare projection -> validate transition |
  |      -> append movement -> update projection -> add outbox  |
  +-------------------------------------------------------------+
             | commit all / rollback all
             v
 [movement ledger] <----reconcile----> [current projection]
             |
             +----> [outbox publisher] ----> external consumers

How it works (step-by-step, with invariants and failure modes)

  1. Cast and validate command fields with a changeset.
  2. Begin a named multi-operation transaction.
  3. Claim or resolve the idempotency key.
  4. Reach the SKU serialization point and observe availability.
  5. Reject insufficient quantity or invalid reservation state.
  6. Append one movement and update the projection.
  7. Add an outbox record and commit.
  8. Invariants: quantities remain non-negative; one command produces at most one movement; ledger and projection change together.
  9. Failure modes: deadlocks, stale optimistic versions, client retry after unknown outcome, expiry/commit race, and constraint-name mismatch.

Minimal concrete example

PSEUDOCODE — reserve command

transaction reserve(command):
  existing <- claim_idempotency(command.key, digest(command.input))
  if existing: return stored_outcome(existing)
  projection <- lock_sku(command.sku)
  require projection.on_hand - projection.reserved >= command.quantity
  reservation <- insert active reservation
  movement <- append reserve movement linked to command and reservation
  update projection.reserved += command.quantity
  insert outbox event
  return reservation summary

Common misconceptions

  • “A changeset validation prevents overselling.” It cannot arbitrate concurrent stale reads.
  • “Wrapping code in a transaction serializes all callers.” Isolation still requires a lock, predicate, or version check.
  • “Retries are safe because the endpoint is POST.” Safety comes from a durable idempotency contract.
  • “The projection is the ledger.” It is disposable derived state and must be reconcilable.

Check-your-understanding questions

  1. Why can two valid changesets still oversell one SKU?
  2. What must be unique to make a reservation retry idempotent?
  3. Why should broker publication occur after commit through an outbox?

Check-your-understanding answers

  1. Each may validate against the same stale availability before either commits.
  2. The command scope plus idempotency key, with input-digest conflict detection.
  3. The outbox commits atomically with inventory and avoids publishing an event for rolled-back state.

Real-world applications

  • Warehouse and commerce inventory.
  • Seat, ticket, and appointment holds.
  • Credit-limit allocations and quota reservations.

Where you’ll apply it

  • Schema and constraint design in Section 4.
  • Transaction composition and error mapping in Section 5.
  • Contention, retry, and reconciliation tests in Section 6.

References

  • Ecto.Schema: https://hexdocs.pm/ecto/Ecto.Schema.html
  • Ecto.Changeset: https://hexdocs.pm/ecto/Ecto.Changeset.html
  • Ecto.Multi: https://hexdocs.pm/ecto/Ecto.Multi.html
  • Ecto SQL: https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html

Key insight

Model the invariant clearly in Elixir, but enforce its concurrency boundary atomically in the database.

Summary

An append-only ledger preserves evidence, a projection makes decisions efficient, Ecto.Multi keeps related writes atomic, and database constraints plus an explicit contention strategy protect invariants under real races.

Homework/Exercises to practice the concept

  1. Trace two reservations of four units against five available under check-then-write and row locking.
  2. Design the unique keys for reserve, release, and commit movements.
  3. Decide how a repeated idempotency key with a different quantity should respond.

Solutions to the homework/exercises

  1. Check-then-write may accept both; row locking lets one commit and makes the second observe only one remaining unit.
  2. Use command-scope idempotency uniqueness and one terminal movement per reservation, while retaining a unique movement ID.
  3. Return an idempotency conflict; never reinterpret one key as a new command.

3. Project Specification

3.1 What You Will Build

Build a JSON API and operator CLI supporting receipt, reserve, release, commit, reservation lookup, stock summary, ledger history, and reconciliation. Seed one contested SKU and demonstrate concurrent requests without overselling.

3.2 Functional Requirements

  1. Append immutable movements for every accepted stock transition.
  2. Maintain on-hand/reserved projections atomically with movements.
  3. Require idempotency keys for mutating commands.
  4. Prevent negative quantities and invalid reservation transitions.
  5. Return stable API errors from changeset, constraint, and transaction failures.
  6. Reconcile projections from the ledger and report discrepancies.
  7. Insert outbox events without doing external work in the transaction.

3.3 Non-Functional Requirements

  • Concurrent tests prove invariants across at least 100 competing attempts.
  • Transactions avoid network calls and document a lock-time budget.
  • Ledger history is immutable through the application API.
  • Query plans use indexes for SKU, reservation, and idempotency lookups.

3.4 Example Usage / Output

$ inventoryctl receive SKU-RED-42 10 --key receipt-001
movement=mov_1 on_hand=10 reserved=0 available=10

$ inventoryctl reserve SKU-RED-42 7 --order ORD-9 --key reserve-009
reservation=res_9 status=active available=3

$ inventoryctl reserve SKU-RED-42 4 --order ORD-10 --key reserve-010
error=insufficient_availability requested=4 observed_available=3

3.5 Data Formats / Schemas / Protocols

Core tables: products/SKUs, inventory projections, reservations, immutable movements, idempotency commands, and outbox events. Quantities use integers in the smallest indivisible unit.

3.6 Edge Cases

  • Receipt retried after a client timeout.
  • Commit racing reservation expiry.
  • Release issued twice.
  • Zero or negative quantities.
  • Database deadlock requiring transaction retry.
  • Outbox publisher crash after successful external publication but before acknowledgement.
  • Projection deliberately corrupted for reconciliation testing.

3.7 Real World Outcome

3.7.1 How to Run (Copy/Paste)

$ mix ecto.create
$ mix ecto.migrate
$ mix test
$ mix run priv/demo_inventory.exs

3.7.2 Golden Path Demo (Deterministic)

The demo receives 25 units, releases 20 concurrent reservations of two units through a barrier, and proves exactly 12 succeed. The projection ends at 24 reserved and one available, while ledger reconciliation reports zero drift.

3.7.3 Exact terminal transcript

$ mix run priv/demo_inventory.exs
seed sku=DEMO-1 on_hand=25 reserved=0
race attempts=20 quantity_each=2
result accepted=12 rejected=8 accepted_quantity=24
projection on_hand=25 reserved=24 available=1
ledger expected_on_hand=25 expected_reserved=24 drift=0
idempotency_duplicates=0 negative_rows=0
DEMO PASS

4. Solution Architecture

4.1 High-Level Design

[API/CLI] -> [Command Changeset] -> [Inventory Context]
                                         |
                                         v
                                  [Ecto.Multi]
                       +-------------+---+--------------+
                       v             v                  v
                  [Ledger]     [Projection]       [Outbox]
                       ^             |
                       +--[Reconciler]

4.2 Key Components

Component Responsibility Key Decision
Inventory Context Public command/query boundary No Repo calls from controllers
Command Changesets Cast input and map constraints Separate command and persistence schemas where useful
Transition Multi Compose atomic named operations No external calls inside transaction
Projection Lock Serialize contested SKU decision Row lock or conditional update
Reconciler Rebuild expected totals Read-only report by default
Outbox Publisher Deliver committed effects At-least-once with consumer identity

4.3 Data Structures (No Full Code)

  • Movement fields: ID, SKU, kind, signed quantity semantics, reservation ID, command ID, occurred-at.
  • Projection fields: SKU, on-hand, reserved, lock version, timestamps.
  • Reservation fields: order, SKU, quantity, status, expiry, terminal movement ID.
  • Idempotency record: scope, key, input digest, outcome reference.

4.4 Algorithm Overview

Each mutation is O(1) indexed work plus lock wait. Ledger history queries are O(log n + k) with an index and page size k. Reconciliation is O(m) over movements for the selected SKU set.

5. Implementation Guide

5.1 Development Environment Setup

Use an actively supported Elixir/OTP release, Ecto and Ecto SQL, PostgreSQL or another adapter with documented locking semantics, and a database sandbox configured for concurrent tests.

5.2 Project Structure

inventory_service/
├── lib/inventory/{sku,projection,reservation,movement,command,outbox}.ex
├── lib/inventory/transitions/{receive,reserve,release,commit}.ex
├── lib/inventory/reconciler.ex
├── lib/inventory_web/
├── priv/repo/migrations/
└── test/inventory/{transition,concurrency,reconciliation}_test.exs

5.3 The Core Question You’re Answering

“Where must an inventory invariant live so it survives concurrent requests, process crashes, and uncertain client retries?”

5.4 Concepts You Must Understand First

  1. Changesets and constraints — Programming Ecto, Chapters 3–5.
  2. Transactions and Ecto.Multi — Programming Ecto, Chapter 6.
  3. Isolation and consistency — Designing Data-Intensive Applications, 2nd Edition, transactions chapter.
  4. Idempotency and integration — Enterprise Integration Patterns, “Idempotent Receiver”.

5.5 Questions to Guide Your Design

  1. What exact quantities must never become negative?
  2. What operation makes the availability decision atomic?
  3. Which unique keys encode one business effect?
  4. How can projection drift be detected and repaired?
  5. Which errors are domain conflicts versus temporary infrastructure failures?

5.6 Thinking Exercise

Draw a timeline for a commit, expiry release, and client retry arriving together for one reservation. Identify the lock or predicate, unique constraints, successful terminal state, and expected API result for each loser.

5.7 The Interview Questions They’ll Ask

  1. Why is changeset validation insufficient to prevent overselling?
  2. Compare pessimistic locking, conditional updates, and optimistic locking.
  3. How does Ecto.Multi report failure, and what is rolled back?
  4. How do idempotency keys differ from reservation IDs?
  5. Why keep both a ledger and a projection?
  6. How would you test a concurrency invariant reliably?

5.8 Hints in Layers

Hint 1: Start with algebra — Write transition tables for each movement before creating schemas.

Hint 2: Protect one SKU — Implement one transaction and prove it with a two-caller race.

Hint 3: Add durable identity — Put idempotency and terminal-transition uniqueness in constraints.

Hint 4: Recompute truth — Fold the ledger into expected projection values and compare.

5.9 Books That Will Help

Topic Book Chapter
Ecto modeling Programming Ecto Ch. 3–6
Transactions Designing Data-Intensive Applications, 2nd Edition Transactions chapter
Idempotency Enterprise Integration Patterns Idempotent Receiver
Stability Release It!, 2nd Edition Ch. 4

5.10 Implementation Phases

Phase 1: Ledger and Projection (5-7 hours)

  • Define transition algebra, schemas, migrations, constraints, and reconciliation.

Phase 2: Atomic Reservation Lifecycle (7-11 hours)

  • Build named transactions, contention strategy, error mapping, and idempotency.

Phase 3: API and Evidence (6-10 hours)

  • Add endpoints, outbox, concurrent tests, query-plan review, and demo report.

5.11 Key Implementation Decisions

Decision Options Recommendation Rationale
Contention Row lock, conditional update, optimistic retry Choose one and benchmark Correctness first; workload decides tradeoff
History Mutable balance only, ledger + projection Ledger + projection Audit plus efficient decisions
Side effects Inside transaction, outbox Outbox Avoid external/DB split brain
Quantity Float, decimal, integer units Integer units Exact inventory arithmetic

6. Testing Strategy

6.1 Test Categories

Category Purpose Examples
Transition unit Prove movement algebra receive/reserve/release/commit
Constraint Prove database backstops duplicate key, negative quantity
Transaction Prove all-or-nothing writes injected failure before projection
Concurrency Prove contention invariant barrier-released reservation race
Idempotency Prove stable retry result same and conflicting payload
Reconciliation Detect projection drift intentional corruption

6.2 Critical Test Cases

  1. One hundred requests contend for less stock; accepted total never exceeds on-hand.
  2. Same idempotency key sent concurrently creates one movement and one outcome.
  3. Same key with a different payload returns conflict.
  4. Commit and expiry race produces one terminal movement.
  5. Injected failure after movement insertion rolls back ledger, projection, and outbox.
  6. Deadlock or optimistic conflict follows the documented retry policy.
  7. Reconciliation detects and describes an intentionally corrupted projection.
  8. Every database constraint maps to a stable domain/API error.

6.3 Test Data

Use SKUs with zero, one, and high stock; reservations at exact boundary; repeated command keys; expired reservations; and randomized sequences whose ledger fold must equal the projection.

6.4 Definition of Done

  • Receipt, reserve, release, and commit produce immutable movements.
  • Ledger and projection update atomically.
  • Database constraints prevent negative quantities and duplicate effects.
  • Concurrent contention test proves no overselling.
  • Retry with the same idempotency key returns one stable outcome.
  • Commit/expiry races yield one valid terminal transition.
  • Reconciliation detects zero drift after normal workloads and finds injected drift.
  • Deterministic demo produces the documented transcript.

7. Common Pitfalls & Debugging

7.1 Frequent Mistakes

Problem: “Rare load tests oversell.”

  • Why: Availability is checked outside the transaction or without a serialization point.
  • Fix: Put comparison and update behind a row lock, conditional write, or version check.
  • Quick test: Barrier-release many requests against one SKU.

Problem: “A retry duplicates a movement.”

  • Why: Idempotency uses an application lookup without a unique constraint.
  • Fix: Claim the key transactionally and persist its input digest/outcome.
  • Quick test: Submit the same key concurrently from two connections.

Problem: “Ledger and balance disagree.”

  • Why: Writes happen in different transactions or an operator mutates the projection directly.
  • Fix: Compose both writes in one Multi and restrict mutation paths.
  • Quick test: Run reconciliation after every fault-injection point.

7.2 Debugging Strategies

  • Include failed Ecto.Multi operation names in structured internal logs.
  • Inspect database locks and slow transaction duration under contention.
  • Query all rows by command ID to detect partial or duplicate effects.
  • Recompute one SKU from its movement history before blaming cached availability.

7.3 Performance Traps

  • Holding locks while calling HTTP or message brokers.
  • Missing indexes on idempotency scope, SKU, or active reservation expiry.
  • Recalculating all ledger history on every command.
  • Retrying deadlocks without a limit or jitter.

8. Extensions & Challenges

8.1 Beginner Extensions

  • Add paginated movement history with running totals.
  • Add reservation expiry preview without mutating state.

8.2 Intermediate Extensions

  • Implement and benchmark a second contention strategy.
  • Publish outbox events through an idempotent worker.
  • Add property-based command-sequence testing.

8.3 Advanced Extensions

  • Partition projections by warehouse while preserving a global product view.
  • Add transfer workflows with compensating operations.
  • Define SLOs and a repair process for reconciliation drift.

9. Real-World Connections

9.1 Industry Applications

Inventory reservations resemble ticket holds, quota allocation, wallet holds, and credit authorization. All need durable identity, explicit transitions, and a concurrency-safe scarce-resource decision.

  • Ecto and Ecto SQL for schemas, queries, transactions, and constraints.
  • PostgreSQL row locking and uniqueness as a correctness boundary.
  • Oban as a possible outbox or expiry worker after core transactions are correct.

9.3 Interview Relevance and Scope Boundary

This project demonstrates relational transactions, Ecto error semantics, and invariant-driven design. Project 3 explores a distributed in-memory key-value store; P22 deliberately uses one authoritative relational database and ACID transactions. It is not a consensus or replication project.

10. Resources

10.1 Essential Reading

  • Ecto.Schema: https://hexdocs.pm/ecto/Ecto.Schema.html
  • Ecto.Changeset: https://hexdocs.pm/ecto/Ecto.Changeset.html
  • Ecto.Multi: https://hexdocs.pm/ecto/Ecto.Multi.html
  • Ecto.Query: https://hexdocs.pm/ecto/Ecto.Query.html
  • Ecto SQL adapter: https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html

10.2 Standards and Further Study

  • PostgreSQL transaction isolation: https://www.postgresql.org/docs/current/transaction-iso.html
  • PostgreSQL explicit locking: https://www.postgresql.org/docs/current/explicit-locking.html
  • Designing Data-Intensive Applications, 2nd Edition, transactions and distributed consistency chapters.
  • Enterprise Integration Patterns, Idempotent Receiver and Transactional Client patterns.