Self-Hosted Inventory Management for Nonprofits: A Complete Build

Running inventory on someone else's servers means your stock data, supplier relationships, and customer records are subject to their terms of service, their pricing changes, and their outages. A self-hosted PHP/MySQL inventory system puts that data on hardware you control — and it runs fine on a Raspberry Pi sitting in your office.

This guide walks through the decisions and patterns behind building a production-grade PHP inventory system: schema design, CRUD operations, authentication, and security. If you want to skip straight to deploying the finished result, see the CoreConduit Inventory System deployment guide. If you want to understand how it's built, read on.

What You'll Need

Schema Design

The schema is where inventory systems succeed or fail. A common mistake is treating inventory as a simple quantity field on a product — that works until someone asks "who changed this, and why?" A production schema needs audit trails baked in from day one.

Core tables

-- Products with physical location tracking
CREATE TABLE products (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    sku         VARCHAR(100) UNIQUE,
    category_id INT,
    location    VARCHAR(100),          -- "Shelf A3", "Warehouse B"
    quantity    INT NOT NULL DEFAULT 0,
    price       DECIMAL(10,2),
    reorder_qty INT DEFAULT 0,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every quantity change is logged — no silent updates
CREATE TABLE stock_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    user_id    INT,
    delta      INT NOT NULL,           -- positive = received, negative = sold/adjusted
    reason     ENUM('purchase','donation','sale','adjustment','count','correction'),
    notes      TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Order-centric sales

Treating each sale as an independent record creates accounting nightmares — you lose the concept of "what went out together." Grouping sales under orders gives you pick lists, invoices, and the ability to cancel an order and restore stock automatically.

CREATE TABLE orders (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    status      ENUM('quote','pending','processing','shipped','complete','cancelled') DEFAULT 'pending',
    notes       TEXT,
    created_by  INT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by)  REFERENCES users(id)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,  -- snapshot price at time of order
    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Runtime settings table

Avoid hardcoding things that admins might want to change — currency, tax rates, organization name. A key/value settings table lets you expose these through an admin UI without touching config files.

CREATE TABLE settings (
    setting_key   VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed defaults
INSERT INTO settings (setting_key, setting_value) VALUES
    ('currency_code', 'USD'),
    ('org_name',      'My Organization');

The PHP Data Layer

Every database call goes through PDO with positional parameters. No string concatenation in SQL — ever. The pattern is consistent enough to put in a shared helper file:

// includes/sql.php
function get_pdo(): PDO {
    static $pdo = null;
    if ($pdo === null) {
        $pdo = new PDO(
            'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8mb4',
            DB_USER, DB_PASS,
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
             PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
        );
    }
    return $pdo;
}

function find_all(string $table): array {
    $pdo  = get_pdo();
    $stmt = $pdo->prepare("SELECT * FROM `$table` WHERE deleted_at IS NULL ORDER BY id DESC");
    $stmt->execute();
    return $stmt->fetchAll();
}

function find_by_id(string $table, int $id): array|false {
    $pdo  = get_pdo();
    $stmt = $pdo->prepare("SELECT * FROM `$table` WHERE id = ? AND deleted_at IS NULL");
    $stmt->execute([$id]);
    return $stmt->fetch();
}

function insert(string $table, array $data): int {
    $pdo     = get_pdo();
    $cols    = implode(', ', array_keys($data));
    $placeholders = implode(', ', array_fill(0, count($data), '?'));
    $stmt    = $pdo->prepare("INSERT INTO `$table` ($cols) VALUES ($placeholders)");
    $stmt->execute(array_values($data));
    return (int) $pdo->lastInsertId();
}

function delete_by_id(string $table, int $id): void {
    $pdo  = get_pdo();
    $stmt = $pdo->prepare("DELETE FROM `$table` WHERE id = ?");
    $stmt->execute([$id]);
}

Output Escaping

Every piece of user-supplied data that reaches the browser must be escaped. Define a single helper and use it everywhere — never trust that data was sanitized on the way in.

// includes/functions.php
function h(mixed $value): string {
    return htmlspecialchars((string) $value, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

// In templates — always use h():
echo '<td>' . h($product['name']) . '</td>';
echo '<input value="' . h($product['sku']) . '">';

Authentication

Session setup

// config/config.php — session hardening
ini_set('session.cookie_httponly', '1');
ini_set('session.cookie_secure',   '1');  // HTTPS only
ini_set('session.cookie_samesite', 'Strict');
session_start();

Login logic

// users/login.php
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $pdo  = get_pdo();
    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND is_active = 1");
    $stmt->execute([$_POST['username']]);
    $user = $stmt->fetch();

    if ($user && password_verify($_POST['password'], $user['password_hash'])) {
        session_regenerate_id(true);           // prevent session fixation
        $_SESSION['user_id']   = $user['id'];
        $_SESSION['user_role'] = $user['role'];
        header('Location: /index.php');
        exit;
    }
    $error = 'Invalid username or password.';
}

Role checks

Every protected page calls a role-check function at the top — before any output. Three roles: admin, manager, staff.

// includes/auth.php
function require_login(): void {
    if (empty($_SESSION['user_id'])) {
        header('Location: /users/login.php');
        exit;
    }
}

function require_role(string ...$roles): void {
    require_login();
    if (!in_array($_SESSION['user_role'], $roles, true)) {
        http_response_code(403);
        exit('Access denied.');
    }
}

// Usage at top of admin-only pages:
require_role('admin');

// Usage at top of manager+ pages:
require_role('admin', 'manager');

CSRF Protection

Every state-changing form needs a CSRF token — a secret value tied to the session that proves the form submission came from your page, not an attacker's site.

// includes/csrf.php
function generate_csrf_token(): string {
    if (empty($_SESSION['csrf_token'])) {
        $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
    }
    return $_SESSION['csrf_token'];
}

function csrf_field(): string {
    return '<input type="hidden" name="csrf_token" value="' . h(generate_csrf_token()) . '">';
}

function verify_csrf(): void {
    $token = $_POST['csrf_token'] ?? '';
    if (!hash_equals($_SESSION['csrf_token'] ?? '', $token)) {
        http_response_code(403);
        exit('Invalid CSRF token.');
    }
}

// In every form:
echo '<form method="post">';
echo csrf_field();

// At the top of every POST handler:
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    verify_csrf();
    // ... process form
}

Runtime Settings

A simple class wraps the settings table with a per-request cache and a safe fallback for when the table doesn't exist yet (useful during migrations).

// includes/settings.php
class Settings {
    private static array $cache = [];

    public static function get(string $key, string $default = ''): string {
        if (isset(self::$cache[$key])) {
            return self::$cache[$key];
        }
        try {
            $stmt = get_pdo()->prepare("SELECT setting_value FROM settings WHERE setting_key = ?");
            $stmt->execute([$key]);
            $row = $stmt->fetch();
            return self::$cache[$key] = $row ? (string) $row['setting_value'] : $default;
        } catch (PDOException) {
            return $default;    // table doesn't exist yet — safe during deploys
        }
    }

    public static function set(string $key, string $value): void {
        get_pdo()->prepare(
            "INSERT INTO settings (setting_key, setting_value)
             VALUES (?, ?)
             ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)"
        )->execute([$key, $value]);
        self::$cache[$key] = $value;
    }
}

// Usage:
$currency = Settings::get('currency_code', 'USD');

Deployment

The full LAMP setup, Apache virtual host configuration, and security hardening steps are covered in the CoreConduit Inventory System deployment guide. The short version:

1. Install Apache, PHP 8.1+, MySQL 8.0+ on your Pi
2. Create a dedicated database user with privileges scoped to one database only
3. Import schema.sql — then apply any numbered migrations in order
4. Set config.php credentials and timezone
5. Set file permissions: 640 on config files, 755 on the app root
6. Configure Apache to deny direct access to includes/ and config/

What's Next

The patterns in this guide — PDO prepared statements, session-based RBAC, CSRF tokens, and soft-delete logging — are the foundation of the CoreConduit Inventory System. That system extends this base with order management, customer CRM, multi-currency support (admin-selectable from 91+ ISO 4217 codes), pick list generation, and a full audit trail. If you're deploying for a real organization rather than learning the internals, start there:

CoreConduit Inventory — Complete Deployment Guide →