#🔒 Handling and running rules from a database

30 messages · Page 1 of 1 (latest)

real pulsar
#

I am trying to create a billing tool for my company. Basically we'll import usage from CSVs provided by suppliers, and then match/apply that usage to monthly/yearly invoices in our billing system. Right now I am specifically trying to figure out the best way to handle matching rules. 90% of this could be handled by just matching client names, but that last 10% is full of annoying edge cases, so rather than trying to add a bunch of different fields for the usage data, I figured creating some "rules" that will be run to choose the relevant lines is probably better.

The data will all be stored in SQLite DB (possibly moved to another service for deployment, depending on what my company wants). See current table here: https://paste.pythondiscord.com/WC6Q. The "problem" I am running into, is that in its current form, I will have to create TONS of rules to make sure I account for everything, because each rule can only take a single input for each category.

I have a couple of thoughts on solving this, but this is the first time I've ever made something like this, and I'm worried I could be overcomplicating it a ton.

  1. Create another column for each category that would simply store whether to treat it as MATCH or DONT MATCH (IS, IS NOT). EG: If source_system_type is set to MATCH then any items with a matching source system will be include. If it is set to DON'T MATCH, then any items that match will not be included. The idea here would be that you would set each category, and this would allow you to exclude a specific usage item (say an item that a specific client gets for free)
  2. Keep the above idea, but allow categories to be lists of items to look for, instead of just a single item. My worry here is that if I ever move away from Python, I now have some weirdly formatted data I have to work around.
    (continued in comment)
turbid ploverBOT
#

@real pulsar

Python help channel opened

Remember to:

  • Ask your Python question, not if you can ask or if there's an expert who can help.
  • Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
  • Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.
real pulsar
#

I am about a month deep into this project, and I've been working on it 6 - 12 hours a day with pretty much no input from anyone else other than "if it works I'm happy", so I am looking for any and all advice about this, or just general data handling best practices. If anything isn't clear, please feel free to ask questions, and I will answer as best I can.

Full database schema is here: https://paste.pythondiscord.com/S7MQ

TIA!

tranquil topaz
#

I think you'd find it easier to define your rules as code, not as database entries.

What you've described seems like an XY problem: you need a rules engine, you've decided that it will exist as entries in a table, but now there are issues with that implementation, so you're asking for help fixing that implementation. That is the Y, but what you really need is a solution for the problem X (the rules engine itself).

The solution may be as I mentioned: not using a database table at all. Write code that acts on this data.

real pulsar
tranquil topaz
#

Eh. Just hard code stuff into the code. 🙂

Previous job I had, we had a rules engine. It has loads of definitions for exceptions, for mappings from one thing to another, etc. Much of it was just hard-coded.

The database should be reserved for data at rest. Any business logic is much easier to work with in the code layer.

It doesn't have to be super smart code to get the job done, of course. And writing simple rules, even over and over again, is very maintainable and upgradable later.

#

Sounds like you've been at this a while and want to ship. So, write the code that works and do so.

real pulsar
real pulsar
tranquil topaz
#

If there are some that are simple mappings like, column A is this value, it must be this thing B instead, and that can work for 50 instances; that's a simple rule to create.

real pulsar
tranquil topaz
#

You probably should not hard code product IDs to map to individual rows, though. Find a way to finagle the bad data into working data that can match more naturally.

real pulsar
tranquil topaz
#

Do individual customers send different csvs?

real pulsar
# tranquil topaz Do individual customers send different csvs?

So kinda, yeah. The customers are the ones being billed with the invoice templates. We resell them stuff, and then those resellers/suppliers send us the usage information in CSV format. They are not consistent at all. Some use an ID to reference a client, some use a name, some combine a sort of ID/name system, etc. Thats why I needed the customer_mapping table

#

But I think I can make a sort of hybrid system, where I make rules that are SQL queries, and then store those rules in a database, which also contains the line IDs, and priority

#

Having them in a database will also be nice if I need to run a few, but not all of them

#

Unless you think im building myself into a trap/corner here?

tranquil topaz
#

I was thinking more along the lines of regex patterns to pull data.
If the number of clients is manageable, I would suggest an integration layer built for each client to "clean" their specific incoming data according to whatever rules they follow.

If that's not workable, just general purpose set of patterns to extract the info you need or search for matching info from the other fields.

real pulsar
#

I do plan to have some integration layer stuff. I have some code from the version before this one that let me automatically find and map an invoice line to a client which saved alot of time

tranquil topaz
#

The engine I worked with had a number of layers of matching rules, trying to identify one line to one item based on certain fields. Also customer data, very inconsistent across the board. But we would match with a certain confidence based on that first rule layer, then go deeper if we failed to find things for a line.

#

Part of that worked because we normalized data into a DataFrame first and then did some joins to match things in batches.
The upside is that's generally fast; the downside is that required having all the product data in memory and that system tended to run out of mem a lot (working in little microservice pods in Kubernetes).

real pulsar
#

Oh I like a confidence based matching system alot actually

#

So then sorry just to make sure I understand, would you lean having the rules hardcoded vs stored in DB?

tranquil topaz
#

I would, yes. Having some mappings in the database makes sense if there are many thousands of potential mappings from one thing to another, maybe.

But I think that's a bigger issue than what you're doing now. So keeping the logic in code will help you keep some flexibility as well as sanity

real pulsar
#

Got it! This has truly been extremely helpful, I cannot thank you enough! I've had virtually no input on this since I started, and was starting to go insane

#

In fact I think I was insane

turbid ploverBOT
#
Python help channel closed for inactivity

This help channel has been closed. Feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.