Inventory & Orders API (PHP + MySQL)

A production-style REST API for inventory and order management, written in modern PHP without a framework, so the routing, auth, validation, error handling and data access are all visible and testable. It covers what a small shop or warehouse needs: a product catalogue with search and filters, customers, orders that decrement stock inside a database transaction, a low-stock report, and JWT authentication with admin / staff roles.

---

Contents

• Features
• Tech stack
• Architecture
• Quick start (Docker)
• Quick start (without Docker)
• Configuration
• API reference
• Example requests and responses
• Authentication flow
• Running tests and quality checks
• Security notes
• Roadmap
• License
• Author

Features

• Framework-free PHP 8.2+: a small regex router with typed placeholders ({id:[1-9][0-9]*}), immutable Request/Response objects, enums, readonly properties, first-class callables and declare(strict_types=1) everywhere.
• JWT authentication (HS256 via firebase/php-jwt v7): register, login, me. Tokens carry an issuer, expiry and jti. The user is re-loaded on every request, so role changes and deactivations apply immediately.
• Role-based access control: admin manages the catalogue and users; staff can browse the catalogue, manage customers and place or progress orders. Roles are declared per route.
• Products: CRUD plus search (q), filters (category_id, min_price, max_price, in_stock, is_active), multi-field sorting (sort=-price,name) and pagination with meta.
• Orders:
  • Created in one DB transaction. Rows are locked with SELECT … FOR UPDATE in ascending id order to avoid deadlocks.
  • Stock is decremented with a conditional UPDATE … WHERE stock >= :qty.
  • Prices come from the catalogue, never from the client.
  • If any line fails, the whole order is rolled back and the API returns 409 insufficient_stock.
• Order lifecycle state machine: pending → paid → shipped → delivered, and cancellation from pending/paid returns the stock. Invalid transitions return 409 with the allowed next states.
• Low-stock report: active products at or below their reorder_level (or a custom threshold), most urgent first, with the shortfall for each.
• Validation: a small declarative validator ('required|string|min:2|max:100'). It coerces query-string types, uses PUT (full) vs PATCH (partial) semantics, and reports per-field errors, including nested items.0.quantity.
• Consistent JSON errors with correct status codes: 400, 401, 403, 404, 405 (with an Allow header), 409, 415, 422, 500 and 503.
• Money handled safely: integer-cent arithmetic, DECIMAL storage and string amounts in responses ("1999.50").
• CORS allow-list, security headers, an X-Request-Id on every response, and .env configuration.
• Docker Compose stack (PHP 8.3 + Apache, MySQL 8.4) that creates the schema and loads seed data on first start.
• Documentation: OpenAPI 3 spec served at /openapi.yaml, Swagger UI at /docs/, a Postman collection and curl examples.
• Quality tooling:
  • 99 PHPUnit tests: 49 unit tests, plus 50 integration tests that drive the real HTTP kernel and run on SQLite or MySQL/MariaDB.
  • PHPStan at level 8.
  • PHP-CS-Fixer (PER-CS 2.0).

Tech stack

Concern	Choice
Language	PHP 8.2+ (tested on 8.4; Docker image uses 8.3)
Database	MySQL 8.0+ / MariaDB 10.6+ via PDO with native prepared statements (SQLite for tests)
Auth	JWT HS256 (firebase/php-jwt), bcrypt password hashing (password_hash)
Config	vlucas/phpdotenv + typed Config accessor
Autoloading	Composer PSR-4 (App\ → src/)
Tests	PHPUnit 11
Static analysis / style	PHPStan 2 (level 8, phpVersion: 80200), PHP-CS-Fixer 3 (PER-CS 2.0)
Runtime	Docker: php:8.3-apache + mysql:8.4; or PHP's built-in server for development
Docs	OpenAPI 3.0.3, Swagger UI, Postman v2.1 collection

Architecture

HTTP request
   │
   ▼
public/index.php ──► Application::handle()            (kernel: error mapping, CORS, security headers)
                        │
                        ├─ Router::match()            404 / 405 + Allow
                        ├─ Authenticator              Bearer JWT → active user from DB → role check (401 / 403)
                        ▼
                     Controller                       HTTP concerns only: read input, call Validator, build Response
                        │
                        ├─ Validator                  422 with per-field messages
                        ▼
                     Service (OrderService, AuthService)  business rules, transactions, state machine
                        │
                        ▼
                     Repository                       SQL only, PDO prepared statements, row → array mapping
                        │
                        ▼
                     MySQL / MariaDB  (SQLite in tests)

Each layer has one job. Controllers never touch SQL. Repositories never see HTTP. Every failure becomes an HttpException that the kernel turns into the standard error envelope. The kernel is a plain object (handle(Request): Response), so the integration tests run the whole stack in-process without a web server.

.
├── bin/setup-db.php            # create schema (+ --seed, --fresh) using .env settings
├── database/
│   ├── schema.sql              # MySQL / MariaDB schema (FKs, CHECKs, indexes)
│   ├── schema.sqlite.sql       # SQLite equivalent used by the test-suite
│   └── seed.sql                # demo users, categories, products, customers, orders
├── docs/
│   ├── curl-examples.md
│   ├── images/swagger-ui.png
│   └── postman/inventory-orders-api.postman_collection.json
├── public/
│   ├── index.php               # front controller
│   ├── .htaccess               # Apache rewrite + Authorization header pass-through
│   └── docs/index.html         # Swagger UI (loads /openapi.yaml)
├── src/
│   ├── Application.php         # kernel + dependency wiring + route table
│   ├── Auth/                   # JwtService, Authenticator, AuthUser
│   ├── Config/Config.php       # typed env access
│   ├── Controller/             # Auth, User, Category, Product, Customer, Order, Report, System
│   ├── Database/ConnectionFactory.php
│   ├── Domain/                 # Role and OrderStatus enums (state machine)
│   ├── Http/                   # Request, Response, Router, Route, Cors, HttpException, ValidationException
│   ├── Repository/             # PDO repositories
│   ├── Service/                # AuthService, OrderService (transactions)
│   ├── Support/                # Money, Pagination, Sort, Clock, Str
│   └── Validation/Validator.php
├── tests/
│   ├── Unit/                   # validator, router, JWT, money, state machine, helpers
│   └── Integration/            # full HTTP flows against SQLite or MySQL
├── openapi.yaml
├── docker-compose.yml / Dockerfile
├── phpunit.xml.dist / phpstan.neon.dist / .php-cs-fixer.dist.php
└── .env.example

Quick start (Docker)

Requirements: Docker with Compose v2.

git clone https://github.com/Josephobaje/inventory-orders-api-php.git
cd inventory-orders-api-php
docker compose up --build

• API: http://localhost:8080 · Health: http://localhost:8080/health · Swagger UI: http://localhost:8080/docs/
• MySQL is published on localhost:33060 (user inventory, password inventory_secret, database inventory_api).
• On first start the database container runs database/schema.sql and database/seed.sql. To reset everything, run docker compose down -v.

The compose file includes development-only defaults so the stack starts with one command. For anything shared or deployed, put a real JWT_SECRET and passwords in a .env file next to docker-compose.yml, or export them in your shell. Compose picks them up automatically.

Demo accounts (seed data, for local use only):

Role	Email	Password
admin	admin@example.com	Admin@12345
staff	staff@example.com	Staff@12345

Quick start (without Docker)

Requirements: PHP 8.2+ with pdo_mysql and mbstring (plus pdo_sqlite for the tests), Composer 2, and MySQL 8 or MariaDB 10.6+.

git clone https://github.com/Josephobaje/inventory-orders-api-php.git
cd inventory-orders-api-php
composer install

cp .env.example .env
# edit .env: DB_* credentials, and set JWT_SECRET, e.g.
php -r "echo bin2hex(random_bytes(32)), PHP_EOL;"

# create a database and user (adjust names/passwords to match .env)
mysql -u root -p -e "CREATE DATABASE inventory_api CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  CREATE USER 'inventory'@'localhost' IDENTIFIED BY 'change-me';
  GRANT ALL ON inventory_api.* TO 'inventory'@'localhost';"

composer db:setup      # = php bin/setup-db.php --seed   (use composer db:fresh to drop & recreate)
composer serve         # = php -S 0.0.0.0:8080 -t public public/index.php

Want to try it without MySQL at all? Set DB_DRIVER=sqlite and DB_DATABASE=/absolute/path/inventory.sqlite in .env, then run php bin/setup-db.php --seed. The demo seed.sql is portable.

For Apache, point the document root at public/. The bundled .htaccess handles rewrites. For Nginx, use try_files $uri /index.php?$query_string;.

Configuration

Variable	Default	Description
APP_NAME	Inventory & Orders API	Shown on GET /
APP_DEBUG	false	Adds exception class/message to 500 responses. Never enable in production.
DB_DRIVER	mysql	mysql or sqlite
DB_HOST / DB_PORT	127.0.0.1 / 3306	MySQL connection
DB_DATABASE	inventory_api	Database name (or SQLite file path)
DB_USERNAME / DB_PASSWORD	root / empty	MySQL credentials
JWT_SECRET	required	At least 32 characters. The app refuses to boot otherwise.
JWT_TTL	3600	Access-token lifetime in seconds (minimum 60)
JWT_ISSUER	inventory-orders-api	iss claim, verified on every request
ALLOW_REGISTRATION	true	Enables POST /api/v1/auth/register (always creates staff accounts)
CORS_ALLOWED_ORIGINS	empty	Comma-separated origins, or *

Real environment variables always win over .env, because the file is loaded immutably.

API reference

All endpoints return JSON. Success responses look like {"data": ...}, and list responses add "meta": {page, per_page, total, total_pages}. Full schemas live in openapi.yaml; when the server is running you can browse them at /docs/.

Method	Path	Auth	Description
GET	/health	public	Liveness + DB check (503 if the DB is down)
GET	/openapi.yaml	public	OpenAPI 3 specification
POST	/api/v1/auth/register	public	Self-register a staff account, returns a token
POST	/api/v1/auth/login	public	Email + password → JWT
GET	/api/v1/auth/me	any role	Current user
GET	/api/v1/users	admin	List users (role, pagination)
PATCH	/api/v1/users/{id}	admin	Change name / role / is_active (not your own role)
GET	/api/v1/categories	any role	List (q, sort, pagination)
GET	/api/v1/categories/{id}	any role	Detail with product_count
POST	/api/v1/categories	admin	Create (slug generated, names unique)
PUT / PATCH	/api/v1/categories/{id}	admin	Replace / partially update
DELETE	/api/v1/categories/{id}	admin	Delete (409 if it still has products)
GET	/api/v1/products	any role	Search, filter, sort, paginate
GET	/api/v1/products/{id}	any role	Detail
POST	/api/v1/products	admin	Create (SKU unique, upper-cased)
PUT / PATCH	/api/v1/products/{id}	admin	Replace / partially update
DELETE	/api/v1/products/{id}	admin	Delete (409 if it appears on orders; deactivate instead)
GET	/api/v1/reports/low-stock	any role	Products at/below reorder level (threshold optional)
GET	/api/v1/customers	any role	List (q on name/email/phone, sort, pagination)
GET	/api/v1/customers/{id}	any role	Detail with orders_count and total_spent
POST	/api/v1/customers	any role	Create (email unique)
PUT / PATCH	/api/v1/customers/{id}	any role	Replace / partially update
DELETE	/api/v1/customers/{id}	admin	Delete (409 if the customer has orders)
GET	/api/v1/orders	any role	List (status, customer_id, from, to, sort, pagination)
GET	/api/v1/orders/{id}	any role	Detail with line items
POST	/api/v1/orders	any role	Place an order (transactional, decrements stock)
PATCH	/api/v1/orders/{id}/status	any role	Advance or cancel (cancel restocks)

Product list parameters: q, category_id, min_price, max_price, in_stock (true/false), is_active, sort (any of name, sku, price, stock, created_at, id, comma-separated, with a - prefix for descending), page (≥ 1) and per_page (1–100, default 20). Invalid values return 422 instead of being silently ignored.

Example requests and responses

More are in docs/curl-examples.md and the Postman collection. The responses below were captured from a live run against MariaDB 11.8 with the seed data.

Log in

curl -s -X POST http://localhost:8080/api/v1/auth/login \
  -H 'Content-Type: application/json' \
  -d '{"email":"admin@example.com","password":"Admin@12345"}'

{
  "data": {
    "token_type": "Bearer",
    "access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9...",
    "expires_in": 3600,
    "expires_at": "2026-10-07T07:49:24Z",
    "user": { "id": 1, "name": "Admin User", "email": "admin@example.com", "role": "admin", "is_active": true,
              "created_at": "2026-10-07T06:49:07Z", "updated_at": "2026-10-07T06:49:07Z" }
  }
}

Search products: GET /api/v1/products?q=monitor

{
  "data": [
    {
      "id": 2, "sku": "ELEC-MON-002", "name": "Dell 24\" IPS Monitor",
      "description": "Full HD IPS monitor with HDMI and DisplayPort",
      "price": "185000.00", "stock": 4, "reorder_level": 5, "is_active": true, "low_stock": true,
      "category": { "id": 1, "name": "Electronics" },
      "created_at": "2026-10-07T06:49:07Z", "updated_at": "2026-10-07T06:49:07Z"
    }
  ],
  "meta": { "page": 1, "per_page": 20, "total": 1, "total_pages": 1 }
}

Place an order: POST /api/v1/orders → 201 Created, Location: /api/v1/orders/5

{ "customer_id": 6, "items": [ { "product_id": 2, "quantity": 1 }, { "product_id": 14, "quantity": 3 } ], "notes": "Deliver before noon" }

{
  "data": {
    "id": 5, "reference": "ORD-20261007-F844A112", "status": "pending", "total": "213500.00", "items_count": 4,
    "notes": "Deliver before noon",
    "customer": { "id": 6, "name": "Emeka Nwosu", "email": "emeka.nwosu@example.com" },
    "created_by": { "id": 2, "name": "Store Staff" },
    "created_at": "2026-10-07T06:49:25Z", "updated_at": "2026-10-07T06:49:25Z",
    "items": [
      { "id": 7, "product_id": 2, "sku": "ELEC-MON-002", "name": "Dell 24\" IPS Monitor", "quantity": 1, "unit_price": "185000.00", "line_total": "185000.00" },
      { "id": 8, "product_id": 14, "sku": "ACC-MOU-001", "name": "Wireless Mouse", "quantity": 3, "unit_price": "9500.00", "line_total": "28500.00" }
    ]
  }
}

Errors always use the same envelope:

{ "error": { "status": 409, "code": "insufficient_stock",
  "message": "Insufficient stock for \"Dell 24\" IPS Monitor\" (SKU ELEC-MON-002): requested 10, available 4.",
  "details": { "product_id": 2, "sku": "ELEC-MON-002", "requested": 10, "available": 4 } } }

{ "error": { "status": 422, "code": "validation_failed", "message": "The given data was invalid.",
  "details": {
    "sku": ["The sku may only contain letters, numbers, dots, dashes and underscores."],
    "price": ["The price must have at most 2 decimal places."] } } }

Status	code examples	When
400	invalid_json	Malformed JSON, or a JSON array instead of an object
401	unauthenticated, invalid_token, token_expired, invalid_credentials	Missing/bad token or wrong login (includes WWW-Authenticate: Bearer)
403	forbidden, account_disabled, registration_disabled	Wrong role, deactivated account
404	not_found, route_not_found	Unknown resource or route
405	method_not_allowed	Path exists for other methods (with an Allow header)
409	insufficient_stock, invalid_status_transition, sku_taken, category_not_empty, product_in_use, ...	Conflicts with current state
415	unsupported_media_type	Body sent without Content-Type: application/json
422	validation_failed	Per-field messages in details
500	internal_error	Unexpected error (logged with the request_id; no internals leaked unless APP_DEBUG=true)

Authentication flow

Client                                   API
  │  POST /api/v1/auth/login {email,pw}    │
  │ ─────────────────────────────────────► │  password_verify (constant work even for unknown emails)
  │  200 {access_token, expires_at, user}  │  JWT HS256: iss, sub, role, iat, nbf, exp, jti
  │ ◄───────────────────────────────────── │
  │                                        │
  │  GET /api/v1/orders                    │
  │  Authorization: Bearer <token>         │
  │ ─────────────────────────────────────► │  1. verify signature, exp, nbf, iss
  │                                        │  2. load user by sub → must exist and be active
  │                                        │  3. check the route's required roles
  │  200 {data: [...]} / 401 / 403         │
  │ ◄───────────────────────────────────── │

• Tokens are short-lived (default 1 hour). When one expires, the client logs in again (see the roadmap for refresh tokens).
• Self-registration can never grant admin, and any role field in the body is ignored. Admins promote users with PATCH /api/v1/users/{id}.
• Because the user is loaded from the database on each request, deactivating a user or changing their role takes effect immediately, even for tokens already issued.

Running tests and quality checks

composer test              # PHPUnit: unit + integration (in-memory SQLite, no setup needed)
composer test:unit
composer test:integration
composer analyse           # PHPStan level 8
composer cs                # PHP-CS-Fixer dry run (composer cs:fix to apply)
composer check             # all of the above

Run the same integration suite against MySQL/MariaDB. It drops and recreates the tables in the target database, so use a dedicated test database:

TEST_DB_DRIVER=mysql TEST_DB_HOST=127.0.0.1 TEST_DB_DATABASE=inventory_api_test \
TEST_DB_USERNAME=inventory TEST_DB_PASSWORD=secret vendor/bin/phpunit --testsuite integration

What the tests cover:

• Unit: validator rules and coercion, the router (placeholders, escaping, 404/405), JWT issuing and verification (expiry, tampering, wrong issuer, alg: none, weak secrets), money maths, the order state machine, sorting/pagination/config helpers.
• Integration: every endpoint through the real kernel. This includes RBAC, PUT vs PATCH, search/filter/sort/pagination, LIKE-wildcard escaping, rollback when stock is insufficient (stock untouched, no order written), restocking on cancel, FK-protected deletes, CORS preflight, 400/405/415 handling and security headers.

Security notes

• SQL injection: every query uses PDO prepared statements with emulation turned off. Dynamic ORDER BY columns come from a whitelist, never from user input, and LIKE wildcards in search terms are escaped.
• Passwords are hashed with password_hash() (bcrypt) and rehashed automatically when the algorithm changes. Login does the same work for unknown emails and wrong passwords, so timing does not reveal which accounts exist, and both cases return the same error.
• JWT:
  • HS256 only, so alg: none and algorithm-confusion attacks are rejected.
  • The secret must be at least 32 characters.
  • exp, nbf and iss are verified.
  • Users are re-checked in the database on every request.
• Least privilege: write access to catalogue data and users is admin-only, and self-registration is staff-only (and can be turned off with ALLOW_REGISTRATION=false).
• Data integrity:
  • Foreign keys and CHECK (stock >= 0) constraints.
  • Transactional order writes with row locks.
  • Server-side pricing.
  • Unique constraints backed by friendly 409 responses.
• No information leaks: unexpected errors return a generic 500 with a request_id for log correlation. Stack traces never reach clients unless APP_DEBUG=true.
• Headers: X-Content-Type-Options: nosniff, Referrer-Policy: no-referrer, Cache-Control: no-store, a CORS allow-list, plus expose_php=Off and ServerTokens Prod in the Docker image.
• Secrets live in environment variables. .env is git-ignored, and only .env.example (with no secrets) is committed. The seed accounts are for local demos; change or remove them before any real deployment.
• Not included yet: rate limiting and token revocation lists. For production, put the API behind a reverse proxy that handles TLS and rate limits (see the roadmap).

Roadmap

• Refresh tokens with rotation and a revocation list
• Login rate limiting and account lockout
• Stock movement ledger (purchases, adjustments, returns) with audit trail
• Suppliers and purchase orders, with automatic re-order suggestions from the low-stock report
• Sales reports (revenue per day, top products, top customers)
• Idempotency keys for POST /orders
• Versioned SQL migrations instead of a single schema file
• GitHub Actions workflow running composer check against PHP 8.2–8.4 and MySQL
• OpenTelemetry tracing and structured JSON logs

License

Released under the MIT License. © 2026 Joseph Obaje.

Author

Joseph Obaje: full-stack developer (PHP, Flutter, Python) based in Nigeria.

• GitHub: @Josephobaje
• LinkedIn: joseph-obaje

If this project is useful to you, a ⭐ on the repository is appreciated. Issues and pull requests are welcome.