Warehouse UserHub — Worker Identity & Access Management
Internal workforce management tool for warehouse operators: full worker lifecycle, role-based access, training records, AI proposals that never touch the access tables, and an append-only audit log enforced by a database trigger — not application convention.
Problem
Warehouse operations run on fragile identity hygiene. Workers move between warehouses, take temporary roles, complete training cycles, and leave — and the access rights that accumulate over that lifecycle are rarely audited in real time. Onboarding checklists get missed, training certificates expire without triggering revocation, and access granted for a temporary assignment outlives the assignment itself. The tool is built for the HR team and warehouse admins who own this lifecycle: one screen per worker, a complete change history, and automation that catches drift before it becomes a compliance event.
The hardest constraint was containing AI involvement without neutering it. Warehouse access decisions have real consequences — granting the wrong role on the wrong warehouse is a security and compliance event. The AI layer had to be genuinely useful (surfacing expiring certificates, flagging anomalous access patterns, interpreting natural-language queries over worker data) while being structurally prevented from touching the access tables. The NL→SQL surface alone has three obvious attack vectors: prompt injection, schema exposure, and unconstrained queries. And every mutation — whether human-initiated or AI-proposed — had to leave a forensically immutable trace at the database layer, not the application layer.
Solution
A three-layer architecture where the trust boundary is structural, not conventional. The deterministic service layer owns every mutation and writes to audit_log in the same transaction. The AI layer (OpenRouter/Anthropic, Zod-validated output) generates proposals, detections and NL→SQL interpretations — all stored as pending rows in ai_proposals, never executed directly. The human approval gate is the only path from ai_proposals to the deterministic service: an operator reviews, approves or rejects, and only then does the service execute. NL→SQL queries run on a read-only Postgres role behind AST validation (node-sql-parser), a view allowlist, a forced LIMIT clause and a statement timeout — four independent layers, not one.
Architecture
Browser → Next.js 15 App Router → Supabase (Postgres + Auth + RLS + Storage) with Drizzle ORM. LLM traffic goes through a provider abstraction (OpenRouter or Anthropic) with Zod validation at the output boundary. Scheduled evaluation runs via POST /api/cron/evaluate with bearer token protection.
Product
Key metrics
Live links
Key decisions
AI containment as architecture, not prompt convention
The LLM layer structurally cannot call mutation services. Proposals go to a queue; the queue routes to deterministic code; deterministic code writes the audit log. No prompt drift can accidentally grant access — the boundary is enforced by the call graph, not by wording.
Audit immutability at trigger level
A Postgres trigger that blocks UPDATE/DELETE on audit rows survives framework upgrades, new engineers, and refactors. Application-layer 'append-only' conventions are conventions; a database constraint is a guarantee.
Four orthogonal NL→SQL safety layers
AST validation, view allowlist, LIMIT enforcement, and a read-only role each block a different attack surface. They share no code path, so no single bypass defeats all four. Any single layer has gaps; four orthogonal layers don't share attack surfaces.
Drizzle + Zod as the typed contract at the AI boundary
Drizzle keeps the schema as TypeScript source of truth for both migrations and query types. Zod validates every LLM output against a schema before anything downstream consumes it. Together they extend type-safety across the AI boundary, not just within application code.
Reflection
The project forced me to think about AI containment as a structural question, not a prompt engineering one. Once the trust boundary was the architecture — the AI layer literally cannot call mutation services — every other decision became local: how to make the proposal queue useful to operators, how to surface anomaly detections without overwhelming the feed, how to make NL→SQL feel fast without relaxing the safety constraints. The most interesting constraint was the audit log: enforcing immutability by a database trigger instead of an application convention means the invariant survives code changes, framework upgrades and new engineers.