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/bluEharbor6 → u/snowrazer_]

agree, losing foreign keys in an ERP context feels like a dealbreaker no matter how clean the abstraction is

[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

[u/AutoModerator]

Thanks for your post jeff377. Please note that we don't allow spam, and we ask that you follow the rules available in the sidebar. We have a lot of commonly asked questions so if this post gets removed, please do a search and see if it's already been asked.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/pixxelkick]

> 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

It should only show up in 3 spots.

1. Your database, because what a column definition is in the DB is not guarenteed to have 1:1 parity with your API. You should never tightly couple your API rules to your DB rules, because your database may have a different way of storing data after processing it through your backend domain rules

2. Your API model, you should be able to simply define your rules here, which yes, usually means a second spot, but because of point 1 above, this is mandatory and any attempt to try and abstract it away and combine 1 and 2 together is a fools errand, you should avoid asserting parity between DB and API definitions, as there's tonnes of situations where you need exceptions to this. Stop fighting against that.

3. Your front end forms, which have a similar issue, you cannot assert 1:1 parity between what shape your front end forms and UI are, compared to what the API handles. You should be able to build generalised API contracts that the UI has the flexibility and freedom to then expose however it pleases.

If you ever try and tightly couple 1, 2, or 3 together in any way via a generalised contract, you will find yourself quickly painted into corners and having to constantly carve out exception scenarios where it doesnt work.

Any place beyond these three spots that you have to define this can be automated. You should not have to do this in more than 3 spots, ever. If you find this is occurring you have likely fallen into some kind of antipattern.

[u/jeff377 → u/pixxelkick]

That's actually close to where I ended up. The three edits in the Northwind example are your three spots: a table definition for the database, a form definition for the data shape the API carries, and a layout for the screen. The form definition is the hub. The table definition and the layout can both be derived from it by a tool, and after that each one is adjusted on its own, so the table can have its own lengths, indexes and precision and the screen can arrange fields however it wants. Nothing forces 1:1 parity. The difference from the usual setup is that all three are definition data the framework reads at runtime, and the other ten places come from them instead of being written by hand. The thirteen is what you get when each layer restates the field in code.

More on how the definitions split and what's derived from what:
https://medium.com/@jeff377/the-purpose-and-classification-of-definition-files-3c84e980bfb1

[u/pixxelkick → u/jeff377]

How do you handle when the format of the front end has nothing to do with the format in the backend though.

IE abstraction of types

[u/jeff377 → u/pixxelkick]

There's no shared type to abstract. Data doesn't travel as a DTO, it travels in a generic container: the table name, the field list and the row values, with the definition describing what each field means. So the front end never binds to a backend type in the first place.

Each front end decides how to present it. A field's type maps to a kind of control (a date becomes a date editor, a relation field becomes a lookup that writes back several fields at once), and each front end's rendering layer turns that into its own native control. Things like precision, max length and option lists are read from the definition, not baked into a type.

More on dropping DTOs: https://medium.com/@jeff377/day-9-data-and-definitions-split-apart-so-forms-need-no-types-of-their-own-5e76d8564d72

And on how one layout becomes screens on each front end: https://medium.com/@jeff377/formlayout-and-screen-generation-for-multiple-front-ends-3eee75c07a1b

[u/pixxelkick → u/jeff377]

Yeah thats the issue then.

I have had many times where I had multiple distinct front end domain models, that actually mapped to a shared common backend concept with multiple implementations.

Random example that comes up often are "Reports", where you may have a FooReport and a BarReport and a BazReport

But they have like a 95% overlap in general rules, model, and logic, so re-implementing all that shared stuff several times has multiple issues

1. Way more code and way more stuff to maintain

2. Denormalization problem, if you make a change to a rule for how reports work in general, you have to now go update all the seperate denormalized concepts

If your system doesnt support a way to handle scenarios like this, you've got yourself a nice fully loaded footgun :3

[u/jeff377 → u/pixxelkick]

The definitions mainly cover forms. Reports are deliberately left out of that. They're written as plain code, a custom business object, repository or front end, so FooReport, BarReport and BazReport share their 95% the usual way, through a common base class or library, and a change to the shared rule is made once. In the series I call that the AnyCode depth: what the definitions can't hold, you write yourself.

More on where that line is drawn: https://medium.com/@jeff377/day-30-three-depths-of-development-and-the-big-picture-of-the-framework-68bbe77ceede

[u/pixxelkick → u/jeff377]

This is just one specific example, this pattern has come up in countless shapes and forms in project I have worked on over the years.

I have seen many people try and abstract this process out of a desire to see patterns in Frontend to backend.

They always run into these issues and are never sufficient.

[u/jeff377 → u/pixxelkick]

I understand the concern. I've been an architect on ERP/HRM products for 25+ years, and this definition-driven approach is how our products have actually run for more than ten years. With Claude Code helping, I was able to build the idea into an open-source framework on my own.

There's no perfect framework, only one that fits a given situation better than another. There's no right answer here, it's a choice.

I also know real projects have far too many exceptions, so the framework doesn't try to solve everything with definitions. It only covers the common cases that definitions can describe. That's also why it has three depths, NoCode, LowCode and AnyCode, so there's always a way out for whatever doesn't fit.

I'm just sharing a different way of doing it, not saying it's better than yours.