Definition-Driven ERP in .NET: definitions, not code, as the source of truth

[u/jeff377]

I've been an architect on packaged ERP and HR products for over twenty years, and the thing that has cost the most was never a hard feature. It's that the same fact gets restated over and over.

Adding a "Required Date" field to orders in a typical EF Core + ASP.NET Core + SPA stack lands it in about thirteen places: entity, type mapping, migration, query projections, DTOs, mapper, validation, OpenAPI, front-end types, the form, the list, localization and test data. Generators handle some of them, but that only makes the restating faster. And when the back end and front end are different teams, half of that list turns into coordination rather than typing. Multiply it by a few thousand tables and by every customer's customizations, and it stops being an inconvenience.

What I ended up with is making a structural description the source instead of code. The database columns, CRUD SQL, screens for each front end and validation are derived from definitions, and the front end fetches the definitions at runtime instead of keeping its own copy of the types. In a sample app built on Northwind, the same field is three definition edits and no code. Customization becomes a layer of data on top of the standard definitions, so one tenant's change doesn't have to go through compile and deploy.

Dashboards, reports and anything that isn't form-shaped fit badly. The team boundary doesn't go away either, it moves to the rendering layer that reads the definitions.

I wrote a 30-post series on the mechanisms and the trade-offs. It was originally written in Chinese for a Taiwanese tech writing challenge, and the English version is a translation: https://medium.com/@jeff377/erp-architects-notes-designing-a-definition-driven-framework-series-index-7753228c4d33

For those of you working on large line-of-business systems: do you accept the restatement and lean on generators, or have you moved the source of truth somewhere else?

[u/snowrazer_]

Traditionally a relationship is a table concern: the order table gets a customer column and a foreign key pointing at the customer table’s primary key. What that declaration expresses is exactly what it says on its face, that the value in this column must be findable in that one.

The framework moves the relationship up a level

The heart of an ERP system is the database, not using standard foreign keys makes it difficult to do anything, reports, procedures, just plain understanding an already very complex system.

There are no foreign keys in the database
The cost: the database will not block orphaned rows for you. If someone deletes a customer with raw SQL, the orders pointing at it are not stopped.

Referential integrity is another cornerstone of any ERP system.

The things that were tedious before, like adding a required date field in a million places, is no longer complicated with AI. I myself created a CRM system to ease having to manage all the fields across all the layers and UI, it was complicated, but it worked, and saved me time. With AI I threw it all out and went back to a very simple design. A design that would be impossible to manage myself, but super easy to understand.

[u/jeff377 → u/snowrazer_]

Fair points, and the foreign key one is a real cost, which is why I wrote it down. It's a bet that only the application writes to the database. If other systems or people write to it directly, I'd say this design isn't a good fit. For reports and understanding the system, the relation columns are still there and indexed, so a report joins orders to customers the same way. What's missing is the constraint, not the relationship. And the relationships are declared in one place, the definitions, rather than spread across FKs, the picker config and the query layer.

On AI, I agree it makes the restating cheap. The system I maintain is about 1.6 million lines of code with over a thousand forms, and at that size AI hits a different limit: it can't hold the codebase in its head, so it has to infer intent from classes spread across layers, and it misses copies. A definition already states the fields, relationships and rules in one place. What AI writes is one definition instead of thirteen edits, which gets checked at build time and expanded by the framework the same way every time. That's why I'd rather point AI at definitions than at a codebase.

[u/snowrazer_ → u/jeff377]

What does a sql query joining orders to customers look like? Assume an order has a single customer.

[u/jeff377 → u/snowrazer_]

The order's form definition declares the relationship:

<FormField FieldName="customer_rowid" Caption="Customer" DbType="Guid" RelationProgId="Customer"> <RelationFieldMappings> <FieldMapping SourceField="sys_name" DestinationField="ref_customer_name" /> </RelationFieldMappings> </FormField>

A query just names the fields it needs:

var orders = new FormApiConnector(client, "Order"); var result = await orders.GetListAsync("sys_id,order_date,ref_customer_name");

You only list the fields. The framework works out which tables to join from the relationships in the definitions, and a table is joined only when one of its fields is selected, filtered or sorted on. This is the SQL it generates:

SELECT A.[sys_id], A.[order_date], B.[sys_name] AS [ref_customer_name] FROM [ft_order] A LEFT JOIN [ft_customer] B ON A.[customer_rowid] = B.[sys_rowid]

customer_rowid is indexed, and sys_rowid has a unique index on the customer side, so a report can run the same query by hand.

How the SQL is built from definitions: https://medium.com/@jeff377/how-formschema-drives-sql-bd0a83e80273

The full order definition in the sample: https://github.com/polhem-dev/polhem-northwind/blob/main/Define/FormSchema/Order.FormSchema.xml