Next.js and SQL Database Architecture
I had an interesting architectural/process challenge recently. A lot of the projects I've built lately have been for supply chain tools and analytics. I have to collaborate very closely with these supply chain teams around how their data is structured and accessed in order to build these analytics tools to their needs.
The most involved part of that collaboration is with their data analysts. They are excellent with SQL and they know their data structures very well, which is great because I can be more hands-off with business logic in my role. However, they like to frequently iterate on their analytics process, and their data needs to be scrubbed semi-regularly. This results in frequent adjustments to the SQL that hydrates the analytics tools I build. Manually rebuilding, retesting, redploying, and change managing the application on this frequent basis can be time consuming.
We decided to completely separate out the SQL code from the analytics tool code as it's own code repository. I did some quick training with the data analysts to get them set up with a code editor of their choice and an easy Git GUI so they could take charge of all the SQL logic that hydrates the analytics tool. When the analytics tool starts up on the server, it goes to look for those SQL files from the supply chain team's repo. It loads in those files on startup and keeps them available in the application runtime. Anytime the supply chain analysts make an adjustment to an SQL file, the pipeline that deploys the SQL code also triggers a quick restart on the analytics tool. So the tool always has the latest SQL to work with. There's a utility function on startup that validates each SQL file to make sure it's not using certain restricted operations, and fulfills certain minimum data set attributes.
This architecture allows them to make changes and deployments on a much smaller codebase without having to involve development team resources for our change process. They get to iterate on the data as quickly as they like and we get to be hands-off with business logic, so it's a win-win. This process has been working well for us for several months now.