final_mahle: The Inventory System That Thinks Like a Factory Floor

A raw-SQL, Postgres-first ERP for manufacturing batches, nested product hierarchies, and permission logic that survives terminology drift.

8-10 min read • View on GitHub • More from durgaprasadg1

A wide industrial scene where a factory floor is drawn like a nested mechanical map. Large outer frames hold smaller modules inside them, and a batch ticket moves across the composition beside three shift clocks. It explains that the repository models manufacturing structure as a living data system rather than a generic inventory list. — The core idea is not just tracking stock. It is encoding tiers, cells, fractiles, and shifts as a machine-readable version of the factory floor.

Key Takeaways

final_mahle treats a factory as a data structure, so the schema follows the physical hierarchy instead of a generic item catalog.
Its permission layer is built for evolution, with aliases and fallback paths that keep older terminology working while the model changes.
The batch pipeline pushes reporting logic close to Postgres, which reduces round trips and keeps the data shape aligned with production needs.
The raw-SQL choice is not accidental, because this kind of ERP benefits from direct control over constraints, bulk writes, and custom joins.

The factory is the data model

Most inventory apps start with products, orders, and a dashboard. final_mahle starts with a physical hierarchy. The codebase maps manufacturing reality into Postgres with a chain like product_tiers to product_cells to product_fractiles, then layers shift-aware batch tracking on top.

That choice changes everything. A tier is not just a category. A cell is not just a bucket. A fractile is not a label. They are structural parts of the production system, which means the database is doing more than storing records. It is preserving the shape of the floor.

A close editorial engraving of nested boxes inside a larger frame, with arrows showing a product moving from tier to cell to fractile. Off to one side, three small clock faces indicate morning, afternoon, and night shifts. It explains how the project translates manufacturing structure into relational tables and batch timing. — The schema mirrors the plant. Hierarchy and shift are first-class concepts, not afterthoughts.

The permission system is a compatibility ladder. It does not just answer yes or no. It preserves old names, old rules, and newer resource-specific checks in one path.

Why the permission system is more interesting than it first looks

The RBAC layer is not just about locking screens. It is a compatibility machine. Resource names get normalized through alias handling, so multiple labels can collapse into one canonical key before the access decision even begins.

Then the system checks the modern path: resource-specific CRUD permissions. If that does not resolve the question, it falls back to legacy batch permissions. If that still leaves a gap, broader user-level flags step in. That waterfall matters because it lets the software evolve without breaking old data or old habits.

How production batches move through the system

The batch model is where the factory becomes an event stream. Production runs are inserted in bulk, duplicates are filtered before they reach the database, and reporting queries flatten the hierarchy with PostgreSQL aggregation instead of extra application-side stitching.

That design keeps the hot path efficient. The code pushes shape-changing work down into SQL, which means fewer round trips and fewer chances to lose the production context that operators need when they are looking at shifts, start times, and batch history.

SELECT
  b.id,
  b.batch_date,
  b.shift,
  STRING_AGG(DISTINCT t.name, ', ') AS tiers,
  STRING_AGG(DISTINCT c.name, ', ') AS cells,
  STRING_AGG(DISTINCT f.name, ', ') AS fractiles
FROM batches b
LEFT JOIN product_tiers t ON t.id = b.tier_id
LEFT JOIN product_cells c ON c.tier_id = t.id
LEFT JOIN product_fractiles f ON f.cell_id = c.id
GROUP BY b.id, b.batch_date, b.shift;

Why raw SQL is the right choice here

A heavier ORM would make this system look cleaner on paper and messier in practice. This repo needs custom types, conflict handling, carefully shaped joins, and bulk insert logic that stays close to the database. Raw SQL gives the authors direct control over all of it.

That matters in an industrial workflow. Reporting needs to be exact. Constraints need to be explicit. Backward compatibility needs to be encoded, not assumed. SQL-first code is a better fit for that reality than a generic abstraction layer that tries to smooth away the rough edges.

Dimension	Generic inventory app	final_mahle
Data model	Products and categories	Tiers, cells, fractiles, and shift-aware batches
Permission model	Direct role checks	Alias normalization plus fallback waterfall
Reporting style	App-side joins and formatting	Postgres aggregation close to the data
Persistence style	ORM-mediated CRUD	Raw SQL with explicit conflict and bulk handling
System memory	Mostly current state	Current state plus legacy terminology

What this repo is not

It is not a lightweight CRUD dashboard. It is not a generic SaaS admin panel. And it is not trying to hide manufacturing complexity behind friendly nouns.

That is the point. final_mahle behaves more like industrial memory than inventory software. It remembers hierarchy, timing, and terminology drift, then exposes those realities in a way the database can actually enforce.

The factory is the data model

Why the permission system is more interesting than it first looks

How production batches move through the system

Why raw SQL is the right choice here

What this repo is not

Sources and repo notes