I hope you don’t consider this a low-effort post- I’m a distiller by trade with fairly limited knowledge and experience in tech, while the question I’m asking is quite broad, it comes after putting in a huge amount of effort into researching options and building the rest of the system on limited time! I’m happy to learn myself, I just need a bit of a steer in the right direction! There’s lots of sub-optimal things with doing it this way, but ultimately, with the variety and volume we’re producing, my job is quickly becoming unmanageable. The way we gather data is time-consuming, the duplication of work is extreme, and we still have virtually zero visibility on the basics. Off-the-shelf systems have game-breaking issues, are inflexible and often insanely expensive. For this to work, this has to fit within our current practices, which shouldn’t really be hard!

I’ve built a prototype Postgres DB that integrates inventory management, traceability, batch tracking, and more niche functions like alcohol duty loss reporting. I’ve built the underlying PostgreSQL database, but now I need to actually get the data in, as well as presenting the data in a more user-friendly format! While I can manually manage some data, the complexity of data entry due to numerous foreign keys is a challenge, and it means that I’m the bottleneck.

I began creating forms with Retool, but it’s time-consuming to design each one uniquely, it feels very artisan, hand-placing every box for every variation of every form. Retool’s module system is restrictive, which is preventing me from building things in a more procedural way, with reused modules, and comes with a few other limitations. Mostly, I’m concerned with piling loads of time into building with Retool, which I’m generally very impressed with, only to end up putting ourselves at the mercy of their subscription model.

The forms range from simple ones (like goods inwards with dropdowns and CRUD operations) to complex batch sheets for requiring recipes, calculations and conversions. For example, recipes and inventory for alcohol are best done in LPA (litres of pure alcohol), but the exact ABV (alcohol content) and, therefore, density of the inputs varies between batches (think of them as separate instances of an inventory item), and the actual input amount is measured in Kg, plus we may use more than one different batches of the same input item. In this case, you’d take the total amount of that input required (in LPA), select the batch you’re going to use from a dropdown, depending on the ABV and density properties of the batch, we can then output the weight required of that batch to hit the LPA target, and it may be required to use two different batches with different properties to get there.

This is probably the most extreme it gets in complexity, which frankly isn’t very complex, and actually isn’t that high in the requirements priority. 90% of it will be basic CRUD stuff, with dropdown values populated from certain queries. There’s just quite a lot of them to build by hand, and some of the functionality (like being able to add/remove elements from listviews then submit them to the DB) is actually incredibly clunky in Retool. But most of it is complex enough that a lot of the ‘form builder’ tools are too simple and designed more for surveys and such.

As an aside, I think I’m going to recreate the database model in Strapi CMS. Accessing via API and abstracting some of the complexity seems like a much better starting point for frontend development than directly to the DB via SQL, and more secure, which was not so much of a consideration with Retool, but potentially is here. Plus, it means that I don’t have to build forms for everything, less common operations, such as adding a new product, can be done directly in the CMS by semi-technical staff, and the related fields work seamlessly. I just need to make sure that it will still have the flexibility that I need, which is actually quite challenging as most material is focused on using it as a CMS for website building, storing lots of blog posts and rich content as opposed to lots of numeric data values that need to be manipulated. Before I decide that, I need to make sure it is going to be a good fit with my frontend option(s).

My task now is to find a platform or framework that allows for the quick creation of dynamic forms. I’ve been crash-coursing just about everything from the more ‘low-level’ frameworks like React, to the more abstracted config-based form builders that use YAML, JSON schema forms, internal tool builders, etc. Lots of things look suitable, but due to my lack of experience, the only way I’ll know is by trying them and seeing, which is very time consuming, and I don’t have a lot of time to burn!

I’m seeking a front-end solution that’s:

• Configured via config or code, not drag-and-drop only
• Complex enough for things like dropdowns populated from queries/API calls and basic calculations
• The more complex things like the batch sheet example could use a separate solution to the more basic, repetitive forms
• Preferably open-source (but not necessarily free) and self-hostable

If anyone can provide advice or share experiences with suitable tools, it would be immensely helpful. Please let me know if you need any more information. I could share a few prototypes that kinda show some examples, however I will need to populate with sample data first!