Skip to content

Inventory-System.md

Latest commit

 

History

History
514 lines (422 loc) · 18.3 KB

Inventory-System.md

File metadata and controls

514 lines (422 loc) · 18.3 KB
Raw

Inventory System

A secure, multi-user stock and warehouse management app that records inventory movements as immutable transactions, enforces role-based access, and keeps a tamper-evident audit trail.

Overview

The Inventory System tracks products, their stock levels across one or more warehouses, and every movement of stock as an append-only transaction (receipt, issue, adjustment, transfer). Rather than mutating a quantity column directly, stock on hand is derived from — or reconciled against — the sum of transactions, so the history is always reconstructable.

Three roles gate what a user can do:

  • admin — manage users, products, warehouses; view audit log.
  • manager — record any transaction, adjust stock, run reports.
  • clerk — record receipts and issues only; no adjustments, no user management.

Threat model. The app assumes authenticated internal users who may be careless, curious, or malicious. Primary risks: SQL injection through product search and reporting filters, broken access control (a clerk performing a stock adjustment or reading the audit log), CSRF on state-changing forms, session hijacking/fixation, and repudiation (a user denying they zeroed out a bin). Mitigations map to OWASP-Top-10-2021 A01 (access control), A03 (injection), A07 (auth failures), and A09 (logging). Money/quantity integrity is enforced with database constraints and server-side validation, never trusting client input.

Architecture

A thin front controller routes every request through a middleware chain (session → auth → CSRF → RBAC) before dispatching to a controller. Controllers call service/repository classes that own all PDO access. Views only escape and render.

flowchart LR
    B[Browser] -->|HTTPS| N[nginx]
    N -->|FastCGI| F[php-fpm]
    F --> R[public/index.php\nFront Controller]
    R --> MW{Middleware\nSession/Auth/CSRF/RBAC}
    MW --> C[Controllers]
    C --> S[Services / Repositories]
    S -->|PDO prepared stmts| DB[(MySQL)]
    S --> AL[[AuditLogger]]
    AL --> DB
Loading

Data flow for "record an issue of 5 units": browser POSTs the form with a CSRF token → middleware validates session, role (clerk+), and token → TransactionController::store() validates input → TransactionService opens a DB transaction, inserts the movement row, updates the denormalized stock_on_hand, and writes an audit entry → commit → redirect (PRG pattern).

Folder Structure

inventory-system/
├── public/
│   ├── index.php            # front controller, only web-exposed PHP
│   └── assets/
├── src/
│   ├── Core/
│   │   ├── Router.php
│   │   ├── Database.php      # PDO factory (singleton)
│   │   ├── Csrf.php
│   │   ├── Session.php
│   │   └── AuditLogger.php
│   ├── Middleware/
│   │   ├── AuthMiddleware.php
│   │   └── RoleMiddleware.php
│   ├── Controllers/
│   │   ├── AuthController.php
│   │   ├── ProductController.php
│   │   └── TransactionController.php
│   ├── Repositories/
│   │   ├── UserRepository.php
│   │   ├── ProductRepository.php
│   │   └── TransactionRepository.php
│   └── Services/
│       └── TransactionService.php
├── views/
│   ├── layout.php
│   ├── products/
│   └── transactions/
├── migrations/
│   └── 001_init.sql
├── tests/
│   └── TransactionServiceTest.php
├── docker/
│   ├── nginx.conf
│   └── php-fpm.Dockerfile
├── composer.json
├── docker-compose.yml
└── .env                     # secrets, never committed

Database Schema

CREATE TABLE users (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role          ENUM('admin','manager','clerk') NOT NULL DEFAULT 'clerk',
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE warehouses (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code  VARCHAR(16) NOT NULL UNIQUE,
    name  VARCHAR(120) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku           VARCHAR(64) NOT NULL UNIQUE,
    name          VARCHAR(200) NOT NULL,
    unit_price    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- denormalized running balance per product/warehouse, kept in sync inside a txn
CREATE TABLE stock_levels (
    product_id    INT UNSIGNED NOT NULL,
    warehouse_id  INT UNSIGNED NOT NULL,
    quantity      INT NOT NULL DEFAULT 0,
    PRIMARY KEY (product_id, warehouse_id),
    FOREIGN KEY (product_id)   REFERENCES products(id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CHECK (quantity >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- append-only ledger; quantity_change is signed (+ receipt, - issue)
CREATE TABLE stock_transactions (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id      INT UNSIGNED NOT NULL,
    warehouse_id    INT UNSIGNED NOT NULL,
    user_id         INT UNSIGNED NOT NULL,
    type            ENUM('receipt','issue','adjustment','transfer') NOT NULL,
    quantity_change INT NOT NULL,
    reference       VARCHAR(120) NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id)   REFERENCES products(id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (user_id)      REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_log (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NULL,
    action      VARCHAR(80) NOT NULL,
    entity      VARCHAR(80) NOT NULL,
    entity_id   VARCHAR(64) NULL,
    metadata    JSON NULL,
    ip_address  VARBINARY(16) NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Implementation

PDO factory — one hardened connection, exceptions on, emulation off so prepared statements are truly server-side.

<?php
declare(strict_types=1);

namespace App\Core;

use PDO;

final class Database
{
    private static ?PDO $pdo = null;

    public static function connection(): PDO
    {
        if (self::$pdo === null) {
            $dsn = sprintf(
                'mysql:host=%s;dbname=%s;charset=utf8mb4',
                $_ENV['DB_HOST'],
                $_ENV['DB_NAME']
            );
            self::$pdo = new PDO($dsn, $_ENV['DB_USER'], $_ENV['DB_PASS'], [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
            ]);
        }
        return self::$pdo;
    }
}

Session hardening — called once at boot; regenerates the id on privilege change to defeat fixation.

<?php
declare(strict_types=1);

namespace App\Core;

final class Session
{
    public static function start(): void
    {
        session_set_cookie_params([
            'lifetime' => 0,
            'path'     => '/',
            'secure'   => true,
            'httponly' => true,
            'samesite' => 'Strict',
        ]);
        session_start();
    }

    public static function regenerate(): void
    {
        session_regenerate_id(true);
    }
}

CSRF tokens — per-session secret, constant-time comparison.

<?php
declare(strict_types=1);

namespace App\Core;

final class Csrf
{
    public static function token(): string
    {
        if (empty($_SESSION['csrf'])) {
            $_SESSION['csrf'] = bin2hex(random_bytes(32));
        }
        return $_SESSION['csrf'];
    }

    public static function validate(?string $sent): bool
    {
        return is_string($sent)
            && !empty($_SESSION['csrf'])
            && hash_equals($_SESSION['csrf'], $sent);
    }
}

Authentication — password_hash() on registration, password_verify() on login, session id rotated on success.

<?php
declare(strict_types=1);

namespace App\Controllers;

use App\Core\Session;
use App\Repositories\UserRepository;

final class AuthController
{
    public function __construct(private UserRepository $users) {}

    public function login(string $email, string $password): bool
    {
        $user = $this->users->findByEmail($email);
        // password_verify is constant-time; the dummy hash on the null branch
        // avoids a user-enumeration timing side channel.
        $hash = $user['password_hash']
            ?? '$2y$12$usesomesillystringforsalttoblocktiming.................';

        if (!password_verify($password, $hash) || $user === null) {
            return false;
        }
        Session::regenerate();
        $_SESSION['user_id'] = $user['id'];
        $_SESSION['role']    = $user['role'];
        return true;
    }
}

Repository with prepared statements — search never concatenates user input.

<?php
declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;

final class ProductRepository
{
    public function search(string $term): array
    {
        // VULNERABLE: string interpolation invites SQL injection
        // $sql = "SELECT * FROM products WHERE name LIKE '%$term%'";
        // FIX: bind the term as a parameter; wildcards go in the value.
        $sql = 'SELECT id, sku, name, unit_price
                FROM products WHERE name LIKE :term ORDER BY name LIMIT 50';
        $stmt = Database::connection()->prepare($sql);
        $stmt->execute([':term' => '%' . $term . '%']);
        return $stmt->fetchAll();
    }
}

Transaction service — the atomic write path: ledger insert, balance update, and audit entry all commit or all roll back.

<?php
declare(strict_types=1);

namespace App\Services;

use App\Core\Database;
use App\Core\AuditLogger;
use RuntimeException;

final class TransactionService
{
    public function record(
        int $productId,
        int $warehouseId,
        int $userId,
        string $type,
        int $qtyChange,
        ?string $reference
    ): int {
        $pdo = Database::connection();
        $pdo->beginTransaction();
        try {
            $ins = $pdo->prepare(
                'INSERT INTO stock_transactions
                   (product_id, warehouse_id, user_id, type, quantity_change, reference)
                 VALUES (:p, :w, :u, :t, :q, :r)'
            );
            $ins->execute([
                ':p' => $productId, ':w' => $warehouseId, ':u' => $userId,
                ':t' => $type, ':q' => $qtyChange, ':r' => $reference,
            ]);
            $txId = (int) $pdo->lastInsertId();

            $upd = $pdo->prepare(
                'INSERT INTO stock_levels (product_id, warehouse_id, quantity)
                 VALUES (:p, :w, :q)
                 ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity)'
            );
            $upd->execute([':p' => $productId, ':w' => $warehouseId, ':q' => $qtyChange]);

            // CHECK (quantity >= 0) blocks issuing more than on hand
            AuditLogger::log($userId, 'stock.record', 'stock_transaction', (string) $txId, [
                'type' => $type, 'qty' => $qtyChange,
            ]);
            $pdo->commit();
            return $txId;
        } catch (\Throwable $e) {
            $pdo->rollBack();
            throw new RuntimeException('Transaction failed', 0, $e);
        }
    }
}

Role middleware — deny by default; enforced server-side, never by hiding buttons.

<?php
declare(strict_types=1);

namespace App\Middleware;

final class RoleMiddleware
{
    /** @param string[] $allowed */
    public function handle(array $allowed): void
    {
        $role = $_SESSION['role'] ?? null;
        if ($role === null || !in_array($role, $allowed, true)) {
            http_response_code(403);
            exit('Forbidden');
        }
    }
}

Output escaping — every dynamic value is escaped at render.

<?php declare(strict_types=1); ?>
<td><?= htmlspecialchars($product['name'], ENT_QUOTES, 'UTF-8') ?></td>
<input type="hidden" name="csrf"
       value="<?= htmlspecialchars(\App\Core\Csrf::token(), ENT_QUOTES, 'UTF-8') ?>">

Testing

Test the money/quantity invariants and the security gates, not the framework glue. Priorities: (1) a service transaction rolls back fully on any failure; (2) issuing more than stock on hand is rejected by the CHECK constraint; (3) password_verify fails on wrong passwords; (4) CSRF validate() rejects missing/forged tokens; (5) RBAC returns 403 for an under-privileged role. Use a disposable SQLite/MySQL test schema and a transactional rollback per test for isolation.

<?php
declare(strict_types=1);

use App\Services\TransactionService;
use PHPUnit\Framework\TestCase;

final class TransactionServiceTest extends TestCase
{
    public function testIssueBeyondStockIsRejected(): void
    {
        $service = new TransactionService();
        // seed: product 1 has 3 units in warehouse 1
        $service->record(1, 1, 1, 'receipt', 3, 'seed');

        $this->expectException(RuntimeException::class);
        // issuing 5 would drive quantity to -2, tripping CHECK (quantity >= 0)
        $service->record(1, 1, 1, 'issue', -5, 'over-issue');
    }

    public function testCsrfRejectsForgedToken(): void
    {
        $_SESSION['csrf'] = 'expected-secret';
        $this->assertFalse(\App\Core\Csrf::validate('attacker-guess'));
        $this->assertTrue(\App\Core\Csrf::validate('expected-secret'));
    }
}

Security Review

Risk OWASP 2021 Mitigation in this build
SQL injection via search/report filters A03 All queries via PDO prepared statements; EMULATE_PREPARES=false; wildcards bound in the value
Broken access control (clerk adjusts stock, reads audit log) A01 Server-side RoleMiddleware, deny-by-default; UI hiding is never the control
CSRF on state-changing POSTs A01 Per-session token, hash_equals(), SameSite=Strict cookie
Session fixation / hijacking A07 session_regenerate_id(true) on login; Secure, HttpOnly, SameSite cookies; HTTPS only
Weak credential storage A02 / A07 password_hash() bcrypt cost 12; no plaintext; dummy-hash to blunt user enumeration
Repudiation of stock changes A09 Append-only stock_transactions + audit_log with user id and IP
Negative/inconsistent stock A04 (insecure design) CHECK (quantity >= 0), atomic DB transaction wrapping ledger + balance + audit
Secrets in source A05 Credentials in .env, gitignored; no secrets in code
Verbose errors leaking internals A05 display_errors=Off in prod; generic 500 page; details logged, not shown

Deployment

Runs as three containers: nginx (TLS, static assets), php-fpm (app), MySQL. nginx passes only public/index.php to FastCGI so no PHP under src/ is web-reachable.

# docker-compose.yml (excerpt)
services:
  web:
    image: nginx:1.27
    ports: ["443:443"]
    volumes:
      - ./public:/var/www/html/public:ro
      - ./docker/nginx.conf:/etc/nginx/conf.d/default.conf:ro
    depends_on: [app]
  app:
    build: { context: ., dockerfile: docker/php-fpm.Dockerfile }
    environment:
      DB_HOST: db
    env_file: [.env]
    depends_on: [db]
  db:
    image: mysql:8.4
    environment:
      MYSQL_DATABASE: inventory
    volumes: ["db_data:/var/lib/mysql"]
volumes: { db_data: {} }
# docker/nginx.conf (excerpt) — deny PHP outside public/
location ~ \.php$ {
    root /var/www/html/public;
    try_files $uri =404;                 # never pass non-existent scripts
    fastcgi_pass app:9000;
    fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
    include fastcgi_params;
}

Bring it up with docker compose up -d, then docker compose exec app php migrations/run.php. Set composer install --no-dev --optimize-autoloader and opcache.validate_timestamps=0 in the production image. See Docker-for-PHP for the full php-fpm hardening baseline.

Exercises

  1. Stock transfers. Add a transfer flow that atomically issues from a source warehouse and receipts into a destination in one DB transaction; write a test proving it rolls back if either leg fails.
  2. Reorder alerts. Add a reorder_point per product and a report of items below threshold — build the filter with bound parameters, then try to inject through it to prove the control holds.
  3. JWT read API. Expose a read-only /api/stock endpoint authenticated with short-lived tokens; see JSON-Web-Tokens-JWT and REST-API.
  4. Immutable audit hashing. Chain each audit_log row to the previous via a SHA-256 of its contents, making silent deletion detectable.
  5. Rate-limit login. Add per-IP throttling and lockout to AuthController::login() to slow credential stuffing.

References

Related