Inventory Management Overview

This page documents how the system works: the core concepts, database layout, and how the FastAPI backend and Tailwind‑styled UI work together when you build products from parts.

FastAPI backend SQLite storage SQLAlchemy models TailwindCSS UI

What this app is for

  • Track individual parts and on‑hand quantities.
  • Define finished products (e.g. complete builds).
  • Attach a Bill of Materials (BOM) to each product.
  • When you record a build, the system automatically subtracts the required parts from inventory.

High‑level stack

Frontend / UI
Server‑rendered templates (Jinja2) styled with TailwindCSS, served by FastAPI.
API / Data layer
FastAPI routes expose JSON endpoints for parts, products, BOM, and builds.
Persistence
SQLite database accessed via SQLAlchemy models. The connection URL can be swapped later for Postgres or another hosted database with minimal changes.

Core Domain Concepts

Part

A single inventory item (e.g. lower receiver, barrel, handguard). Each part has:

  • sku – unique identifier for the part
  • name – human‑readable description
  • quantity_on_hand – how many you currently have
  • cost – optional unit cost for later reporting

Product

A finished configuration you “build” from parts (e.g. a complete rifle, upper, or lower). Products are defined by:

  • sku – unique identifier for the product
  • name – descriptive name
  • A linked BOM that specifies which parts and quantities are required

Bill of Materials (BOM)

The “recipe” that connects products to parts. Each BOM line says:

  • For product product_id
  • Use part part_id
  • With quantity qty_per_product

When you build N units of a product, required parts = qty_per_product × N.

Build

A build represents actually assembling one or more units of a product. A build record stores:

  • product_id – what was built
  • quantity_built – how many units
  • built_at – timestamp when it was recorded

Recording a build triggers inventory checks and deductions for all parts referenced by the product’s BOM.

Database Layout (SQLite via SQLAlchemy)

Table: parts

  • id – primary key
  • sku – unique, indexed
  • name
  • quantity_on_hand
  • cost – optional

Table: products

  • id – primary key
  • sku – unique, indexed
  • name

Table: product_bom

  • id – primary key
  • product_id → products.id
  • part_id → parts.id
  • qty_per_product – integer

Table: builds

  • id – primary key
  • product_id → products.id
  • quantity_built
  • built_at – datetime

All of these tables are declared as SQLAlchemy models (Part, Product, ProductBOM, Build) and created via Base.metadata.create_all(bind=engine). Swapping to a hosted database later mostly means changing the connection URL in the database configuration.

System Flow & Backend Behavior

Typical workflow

  1. Create parts on the /parts page. The UI calls POST /api/parts with the new part data.
  2. Create products on the /products page. This uses POST /api/products.
  3. Attach a BOM to each product by linking parts and quantities via the BOM endpoints (/api/products/{product_id}/bom).
  4. Record builds from the builds page using POST /api/builds (product + quantity).
  5. The backend validates inventory, subtracts the required parts, and stores a build record.

Under the hood (FastAPI)

  • HTML pages (like this one) are rendered via Jinja2 templates returned from FastAPI routes such as GET /, GET /parts, etc.
  • API routes are type‑safe using Pydantic schemas (e.g. PartCreate, PartRead, BuildCreate).
  • Database access is handled through a scoped SQLAlchemy session dependency (get_db()) injected into each route.
  • TailwindCSS is compiled into static/css/main.css (e.g. using pytailwindcss) and linked from base.html, so all templates share the same design system.