I believe you agree that usually, we want to have everything now or never. For me, that rule works when I start thinking about having a new gadget, like a laptop, a kickscooter, or even a water filter. If I prevent myself from buying that stuff right away, I will save tons of money – in a week my focus will be changed.

But in the business world, sometimes changes and acquisitions need to be delayed, or better, scheduled. For example, the employee title should be changed starting next month, or the product price should be changed in a week. Let’s track these changes with Retool Workflows!

Real Example

We have recently built an app for a huge international company to manage their employees. Having a way to track and schedule changes was one of the core requirements for this project. The second limitation was that the app should be SOX-complaint, so no unauthorized changes should be made to the data tables, and scheduled changes should be reflected in the database only on their Effective Date. In other words, even if the future change is approved today, it shouldn’t appear on the data table till its effective date, or auditors might be confused with this ‘future’ record.

To handle all these requirements (and a lot of others!), we use temporal tables in MariaDB alongside the Retool Workflows. MariaDB handles ‘basic’ logging in the background thanks to the system versioning enabled. Different employee record card versions are handled by the MariaDB application time periods. However, the approvals and changes are implemented on Retool Workflows. As long as we are going to work with the standard Retool DB, we will modify the idea to work on the basic setup.

Main Idea

So, let’s discuss how we cope with this task in brief. For example, to store the employees data, we have the employees table. This table will store the actual data only. To store the historical data, we will have an additional table called employees_historical. It should follow the original table structure adding effective_end and record_id columns. By the way, the primary key in this table will be record_id.

To store all the changes, we will have the change_requests table. It might have a limited number of columns: id,  target_table_name, target_row_id, change_payload, effecrive_date, and is_processed flag.

Also, one workflow will be needed, let’s call it processChangeRequests. Now we have agreed on what we need to create. Let’s go then!

Employee Card

Let’s start with the Employee Card. The most effective way to create a UI like this is to use the form component. Why? First, it could be generated automatically based on the table data, either in the DB or in the application. Second, it automatically aggregates the individual component outputs to the data property of a form. Third, it allows you to submit data back automatically alongside its validation. If you are new to the form component, take a look at the official documentation.

Funny enough, the third feature is not what we need for this project. While we still require the form to validate itself on its ‘Submit’ event, we do not want the new or updated data to go back directly to the data table. We want to be able to schedule the changes, remember?

The new data coming from the form should go to the change_requests table. To achieve this, we have to construct the payload first. Just look at the picture below: I created a demo form and displayed the requested changes as a stringified JSON in the console.

What will be the actual SQL query then?

Something like this:

INSERT INTO change_requests (target_table_name, target_row_id, change_payload, effective_date)

VALUES ('employees', NULL, {{JSON.stringify(form1.data)}}, '2024-07-01');

Just for clarity, what is going on here: we are adding a new row to the change_requests table. We know that the target table for this change request will be employees, we are adding a new employee - that’s why the target_row_id is NULL. As a change_payload we pass the stringified version of the form output. And the effective date is set to July, 1st. Why? Just because I want :) But you obviously will have to create a date input component in the UI to let the user select the desired effective date for the change request.

Inside the Change Requests Table

I’ll show you what a real change request table might look like. The column names here are different, reflecting the real business needs, but the idea is the same.

As you can see, we have different target tables – they are called crs_type_name here. When a workflow fetches the data from this table, it reads this column first – to understand what to do with the data. And remember, you can do it also, that’s why we added the target_table_name column.

The Process Change Request Workflow

Okay, now we are going to examine the most important part here: how to organize the workflow.

The first step is to read the data from the DB. We are interested in unprocessed change requests with effective dates today or in the past. That’s why we run something like this against the table:

select * from change_requests where is_processed = 0 and crs_effective_date <= CURRENT_DATE

The second step is a loop over the returned array. We are going to determine if it is a new record, or if we should proceed with the updating. Also, we should check the table name to know in which table to place the data. A JS code block could help you with this.

The third step, in case of a new record, consists of two actions: we execute an INSERT query against the target_table_name table, and mark the current change request as is_processed running UPDATE query on the change_requests table.

In case of an update, the third step consists of three actions. First, we copy current data to the employees_historical table. This step could look like this:

INSERT INTO historical_employees (id, name, position, salary, hire_date, effective_end)

SELECT id, name, position, salary, hire_date, NOW()

FROM employees

WHERE id = <target_row_id>;

Now, we should update the existing record in the employees table. The data object here is the payload from the change request:

UPDATE employees

SET

   name = {{data.name}},

   position = {{data.position}},

   salary = {{data.salary}},

   hire_date = {{data.hire_date}}

WHERE id = <target_row_id>;

Now we have to mark the current change request as is_processed running UPDATE query on the change_requests table.

Okay, we are done, aren’t we?

Not really. To make the system work, we should schedule this workflow to run, say, every 2 minutes. And then we are definitely done.

A real production workflow might look something like this:

Wrapping Up

What have we done today? We discussed a simple approach to implement scheduled change processing. You can use it right away – or modify it accordingly. For example, you can add an app to approve requested changes first, or to cancel the scheduled changes. Or you can add an app to display the different versions of the Employee Record Card.

Don’t be afraid of experimenting with Retool! And remember, we are here to help.

---

Dmitry is our Low-Code Department Technical Lead at Akveo. With a rich background in media and marketing, Dmitry brings an extraordinary look at the world of IT technologies. He is deeply passionate about finding the most effective and elegant solutions to complex client problems. In our blog articles, Dmitry will share his expertise in Retool and other low-code platforms to offer you valuable insights on Retool to enhance business process efficiency.

We already discusses how to use Retool Workflows and how to improve application security. More topics are coming. Stay tuned!