Inventory Audit API & MCP Server
A production-grade inventory auditing and variance tracking engine built with FastAPI, SQLAlchemy 2, Alembic, and PostgreSQL 16. Designed with pure functional domain logic, fail-closed security, and native Model Context Protocol (MCP) tooling for AI pair programmers.
🌐 Production Deployment Topology
Zero Downtime Keepalive ActiveEnforces HTTPS, TLS 1.3, DDoS protection, and forwards real visitor IPs via CF-Connecting-IP for precision rate-limiting.
Hosts two distinct containerized services: the primary REST API service and the read-only HTTP MCP JSON-RPC service.
Managed database with connection pooling, automated Alembic migrations, and Row Level Security enabled across all tables.
Key Architectural Pillars
Deterministic Discrepancy Auditing
All warehouse inventory decision logic lives in decide_audit(), a pure, side-effect-free function with zero database dependencies. If a cycle count discrepancy occurs while an inbound purchase shipment is in transit, the system holds the audit rather than prematurely declaring lost stock.
SQL Window Functions
Warehouse managers track stock drift over time using optimized SQL window queries (SUM(variance_quantity) OVER (PARTITION BY item_id ORDER BY created_at)). Enables real-time shrink detection and bin reconciliation.
Token Buckets & Row Level Security
Write operations require API key authentication that fails closed. Unauthenticated requests are throttled at exactly 120 allowed / 80 blocked per burst. All database tables enforce PostgreSQL Row Level Security (RLS) to prevent unauthorized Data API exposure.
Model Context Protocol (MCP)
Exposes structured read-only tools (list_open_audits, get_variance_report, explain_audit) to AI coding agents (Claude Code, Antigravity) via a lightweight client over HTTP.
| Component | Specification | Measured Result | Verification |
|---|---|---|---|
| Unit & Integration Tests | Pytest against isolated PostgreSQL DB | 159 passing tests | Passed |
| Code Coverage Floor | CI fails below 95% coverage | 99.82% coverage | Enforced in CI |
| Rate-Limiting Verification | 120 requests/min token bucket | 120 allowed / 80 blocked | Live Measured |
| Database Row Level Security | RLS enabled on all public tables | All 6 tables true | SQL Confirmed |