Reminds me a lot of a final year group software project at uni. Instead of building a solution for our client we built a kind of meta solution, then ran out of time to actually solve his problem in it.
I've seen these custom fields tables in a number of database schemas I've worked with in the past. It adds dynamism to a fixed table structure. I wonder how CRMs do it these days without having poor performance at scale?
Being able to partial index into JSON has made this much more straight forwards now than ever before, but historically pre-creating empty indexed custom columns was somewhat common (leading to hard limits like max 20 custom tags), as was EAV (which arguably is inner-platform).
There are more solutions than these, but until you're at truly custom DB scale with a specific problem here, these will solve it for you.
yeah, generally instancing a table per customer is an old smell indicating they have either a permissions issue (no RLS) or you're using a db which doesn't support partial indexes (which basically everything does now).
I've made the mistake of creating this kind of problem many moons ago. The dream was to have non-technical domain experts implement the product. I did not know at the time that this was a cursed problem. Probably one of the most cursed, in fact.
Putting it my sql based scripting engine took 2 weeks. Backing it out is going on 4 years now. Perhaps the biggest technical misstep I've ever made. It's kind of like Pandora's box because once the non technical people feel the speed/control, they'll never let it go. You could place a literal money printer on their desk as an alternative and they'd reject it if you took their new power away.
"Any sufficiently complicated database program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of COBOL."
There are more solutions than these, but until you're at truly custom DB scale with a specific problem here, these will solve it for you.
That worked great... until the thousands of instances had to be merged into a single unified schema.
Putting it my sql based scripting engine took 2 weeks. Backing it out is going on 4 years now. Perhaps the biggest technical misstep I've ever made. It's kind of like Pandora's box because once the non technical people feel the speed/control, they'll never let it go. You could place a literal money printer on their desk as an alternative and they'd reject it if you took their new power away.