orders-inventory-api

A REST API for order and inventory management where SQL is the logic, not a layer under an ORM. Stock is an append-only ledger, the balance is derived by summation, and confirming an order takes row locks so that concurrent orders can never sell the same goods twice.

Flask 3 + PostgreSQL 16 + psycopg 3, raw SQL throughout. Alembic runs the migrations; there are no ORM models anywhere in the project.

The problem this solves

Two managers confirm orders for the same product at the same second. Both queries read a balance of 100, both decide 60 is available, both write. Stock ends at −20 and the warehouse has sold goods it does not have.

This is not theoretical — it is what this codebase does when the lock is removed. Deleting the four lines that take the lock and re-running the suite gives:

FAILED tests/test_concurrency.py::test_two_competing_orders_cannot_both_win
FAILED tests/test_concurrency.py::test_only_as_many_orders_succeed_as_stock_allows
FAILED tests/test_concurrency.py::test_stock_never_goes_negative_under_load
    assert -50.0 >= 0

Five concurrent orders of 30 against 100 in stock: four were confirmed, and the ledger closed at −50. With the lock in place, exactly three succeed, two get 409 insufficient_stock, and the balance lands on 10.

How the confirmation works

POST /api/v1/orders/{id}/confirm, all inside one transaction:

1. SELECT ... FROM orders WHERE order_id = %s FOR UPDATE — two confirmations of the same order cannot both proceed.
2. SELECT product_id FROM products WHERE product_id = ANY(%s) ORDER BY product_id FOR UPDATE — one lock per product, always ascending.
3. Compare requested quantities against sum(delta_qty) from the ledger.
4. Any shortage raises, the transaction rolls back, and nothing is written: no movements, no status change, no invoice. The order stays a draft and can be confirmed later once stock arrives.
5. Otherwise a single INSERT ... SELECT writes one sale movement per line, the order becomes confirmed, and an invoice is issued.

Why lock products and not the ledger. A competing transaction does not modify existing stock_movements rows — it INSERTs a new one, and an INSERT is invisible to FOR UPDATE. Locking rows that already exist would protect nothing. The products row has no role here other than being a single agreed rendezvous point per SKU: whoever holds it is the only writer for that product until they commit. Ascending product_id order means two orders sharing several products queue up rather than deadlock.

Why the balance check is trustworthy only after step 2. Under PostgreSQL's default READ COMMITTED, each transaction reads its own snapshot, so two of them can both see a sufficient balance. The lock is what serialises them; the check is only meaningful once it is held.

The trigger is a second line of defence, not the mechanism. trg_stock_no_negative refuses any movement that would push a balance below zero, which protects a direct psql session or a future code path that forgets to lock. It cannot replace the lock — under snapshot isolation it would let both transactions through. test_database_refuses_a_negative_movement_even_without_the_api covers it.

Deadlocks surface as 40P01. The endpoint retries once, then answers 409 rather than hanging.

Architecture

client → Flask blueprint → SQL (psycopg 3, parameterised) → PostgreSQL
                                                              ├── tables (3NF)
                                                              ├── v_stock_on_hand      (derived balance)
                                                              ├── trg_stock_no_negative (guard)
                                                              └── mv_monthly_sales     (pre-aggregated)

Every value reaches the database as a bound parameter; nothing is interpolated into a statement. The one thing that cannot be bound — a sort column — is whitelisted, and test_unknown_sort_column_is_rejected proves an injected column name is refused rather than executed.

Data model

erDiagram
    roles           ||--o{ users : grants
    users           ||--o{ orders : creates
    users           ||--o{ stock_movements : records
    clients         ||--o{ orders : places
    warehouses      ||--o{ orders : "ships from"
    warehouses      ||--o{ stock_movements : holds
    products        ||--o{ prices : "priced by"
    products        ||--o{ order_items : "ordered as"
    products        ||--o{ stock_movements : moves
    orders          ||--o{ order_items : contains
    orders          ||--o| invoices : "billed by"
    orders          ||--o{ stock_movements : "causes (sale, return)"

    stock_movements {
        bigserial movement_id PK
        int     product_id FK
        int     warehouse_id FK
        numeric delta_qty "positive in, negative out"
        text    reason "receipt|sale|return|write_off|adjustment"
        int     order_id FK "set for sale and return"
        timestamptz created_at
    }
    orders {
        serial order_id PK
        int    client_id FK
        int    warehouse_id FK
        text   status "draft|confirmed|cancelled"
        timestamptz confirmed_at
    }
    order_items {
        serial order_item_id PK
        int     order_id FK
        int     product_id FK
        numeric qty "CHECK > 0"
        numeric unit_price
    }

Loading

stock_movements is never updated or deleted. A cancelled order does not erase its sale — it appends a return, so the ledger doubles as an audit trail and test_cancelling_a_confirmed_order_returns_the_stock asserts both rows are still there.

Constraints do the work the application would otherwise have to remember: CHECK (reason <> 'sale' OR delta_qty < 0) makes a positive sale impossible, UNIQUE (order_id, product_id) forces a repeated SKU to raise the quantity instead of creating a second line, and CHECK (status <> 'confirmed' OR confirmed_at IS NOT NULL) keeps the timestamp honest.

Quick start

cp .env.example .env          # optional; defaults work as-is
docker compose up --build

The container waits for PostgreSQL, applies migrations, seeds a demo catalogue, then serves on http://localhost:8000. Interactive reference at http://localhost:8000/docs, specification at /openapi.json.

Seeded accounts (change them via SEED_* in .env before exposing anything): admin/admin123, manager/manager123, viewer/viewer123.

Without Docker

pip install -r requirements.txt
export DATABASE_URL=postgresql://orders:orders@localhost:5432/orders
python -m alembic upgrade head
python scripts/seed.py
flask --app wsgi run --port 8000

Endpoints

Method	Path	Roles	What it does
POST	/api/v1/auth/login	—	Exchange credentials for a JWT
GET	/api/v1/auth/me	any	Identity behind the current token
GET	/api/v1/products	any	Filters, sorting, keyset pagination
POST	/api/v1/products	admin	Create a product with an optional price
GET	/api/v1/products/{id}	any	One product with its total balance
GET	/api/v1/clients	any	Clients with confirmed-order counts
POST	/api/v1/clients	admin, manager	Create a client
GET	/api/v1/warehouses	any	List warehouses
GET	/api/v1/stock	any	Balances from v_stock_on_hand
GET	/api/v1/stock/movements	any	The ledger, keyset paged
POST	/api/v1/stock/movements	admin, manager	Receipt, return, write-off, adjustment
GET	/api/v1/orders	any	Filter by client, status, date range
POST	/api/v1/orders	admin, manager	Create a draft; prices resolved in SQL
GET	/api/v1/orders/{id}	any	Order with lines and invoice
POST	/api/v1/orders/{id}/confirm	admin, manager	Atomic stock deduction
POST	/api/v1/orders/{id}/cancel	admin, manager	Cancel, returning stock if confirmed
GET	/api/v1/reports/sales	any	Period sales with a ROLLUP subtotal
GET	/api/v1/reports/top-products	any	Rank and revenue share via window functions
GET	/api/v1/reports/low-stock	any	Replenishment signals via HAVING
GET	/api/v1/reports/clients/{id}/statement	any	Running total via SUM() OVER
GET	/api/v1/reports/monthly	any	Reads mv_monthly_sales
POST	/api/v1/reports/refresh	admin	REFRESH MATERIALIZED VIEW CONCURRENTLY

"any" means admin, manager or viewer — a valid token is always required.

Walking through it with curl

BASE=http://localhost:8000/api/v1

TOKEN=$(curl -s -X POST $BASE/auth/login \
  -H 'Content-Type: application/json' \
  -d '{"username":"manager","password":"manager123"}' | jq -r .access_token)

AUTH="Authorization: Bearer $TOKEN"

# What is on hand
curl -s $BASE/stock -H "$AUTH" | jq '.items[:3]'

# A draft order: no unit_price, so the current price is taken from history
curl -s -X POST $BASE/orders -H "$AUTH" -H 'Content-Type: application/json' \
  -d '{"client_id":1,"warehouse_id":1,"items":[{"product_id":1,"qty":12.5}]}' | jq

# Confirm it: stock is deducted and an invoice is issued
curl -s -X POST $BASE/orders/1/confirm -H "$AUTH" | jq '{status, invoice_number, total_amount}'

# Ask for more than exists: 409, and nothing is written
curl -s -X POST $BASE/orders -H "$AUTH" -H 'Content-Type: application/json' \
  -d '{"client_id":1,"warehouse_id":1,"items":[{"product_id":1,"qty":99999}]}' | jq .order_id
curl -s -X POST $BASE/orders/2/confirm -H "$AUTH" | jq

The refusal comes back with the shortage spelled out per line:

{
  "error": "insufficient_stock",
  "message": "not enough stock to confirm this order",
  "details": {
    "shortages": [
      {"product_id": 1, "sku": "SAL-001", "requested": 99999.0, "available": 87.5}
    ]
  }
}

Reports:

curl -s "$BASE/reports/sales?group_by=category" -H "$AUTH" | jq
curl -s "$BASE/reports/top-products?limit=5" -H "$AUTH" | jq
curl -s "$BASE/reports/low-stock?threshold=20" -H "$AUTH" | jq
curl -s "$BASE/reports/clients/1/statement" -H "$AUTH" | jq

Pagination

Listings use keyset, not OFFSET. OFFSET 10000 makes PostgreSQL read and discard ten thousand rows, so deep pages get progressively slower, and a row inserted mid-scroll shifts every later page. A keyset page asks for rows after a cursor — one index seek at any depth, and stable under concurrent inserts.

curl -s "$BASE/products?limit=3&sort=name" -H "$AUTH" | jq '.next'
# {"after": "Chorizo fresh", "after_id": 4}
curl -s "$BASE/products?limit=3&sort=name&after=Chorizo%20fresh&after_id=4" -H "$AUTH"

test_pages_are_disjoint_and_cover_everything asserts that consecutive pages never overlap and together reproduce the full ordered list.

Tests

export DATABASE_URL=postgresql://orders:orders@localhost:5432/orders
python -m pytest -v

36 tests against a real PostgreSQL — row locking, the guard trigger and the materialized view do not exist in SQLite, so a substitute database would test nothing that matters here.

File	Covers
test_auth.py	Login, role enforcement, identical replies for unknown user and wrong password
test_orders.py	Draft holds no stock, confirm deducts and invoices, rollback leaves no trace, cancel appends a return
test_concurrency.py	Competing confirmations, the never-negative invariant, the database guard
test_reports.py	ROLLUP totals reconcile, running totals accumulate, refresh is admin-only
test_pagination.py	Disjoint keyset pages, clamped page size, rejected sort column
test_openapi.py	Every live route appears in the specification

Each test starts from the same opening stock: the fixture truncates the transactional tables and re-inserts it, so tests do not depend on each other.

CI runs the same suite against a postgres:16 service, and additionally rebuilds the schema from scratch (downgrade base then upgrade head) to prove the migrations are reversible.

Project structure

app/
  __init__.py        application factory
  config.py          settings, all from os.environ
  db.py              connection pool, transaction/read helpers
  auth.py            JWT verification and the roles_required decorator
  errors.py          uniform JSON errors, including the trigger's 409
  openapi.yaml       the specification served at /openapi.json
  api/
    auth.py catalog.py stock.py orders.py reports.py
migrations/
  sql/               DDL as .sql files, readable as SQL
  versions/          Alembic revisions that run them
scripts/seed.py      idempotent demo data
tests/               pytest against a real PostgreSQL
docker/entrypoint.sh waits for the DB, migrates, seeds, then serves
Dockerfile  docker-compose.yml  .github/workflows/ci.yml

Notes and limitations

• Passwords are hashed with Werkzeug's PBKDF2. The seeded defaults are for local use; SEED_* environment variables override them.
• Tokens carry the role as a claim, so a role change takes effect at the next login rather than immediately. A revocation list would fix that and is out of scope here.
• The materialized view is refreshed on demand, not after every order — a refresh per confirmation would cost far more than the reports save.
• docker compose up seeds a demo catalogue. Point DATABASE_URL at an empty database and skip scripts/seed.py to start clean.

License

MIT — see LICENSE.