����������

Handling compliance tasks requires us to have a clear record of which policies are in place and how they change over time.

For this, we need a robust system that enables colleagues to interact with our policy data in a structured way. The trouble is the specific internal workflows involved in this are rarely the same from one organization to the next.

Today, we’re exploring one way of addressing this problem, by building a custom policy management system in ����������.

By the end, you’ll have a fully working tool that you can easily modify for your own more granular needs in our open-source, low-code platform.

First, though, let’s start with the basics.

What is a policy management system?

As the name suggests, a policy management system is an internal tool that’s used to manage our company policies.

In the simplest examples, this might mean enabling CRUD actions, allowing us to create, read, update, or delete policy data.

More often, though, we’ll need to enforce more sophisticated workflows. For instance, creating approval workflows, where certain types of users can request changes to policies while others can respond to or approve these.

We may even require additional layers of approval or more granular permissions for specific users to request or approve changes to particular kinds of policies.

On top of this, policy management tools can be used to provide a record of how our policies have changed over time, providing oversight and auditability.

����…

What are we building?

We’re building a simple policy management solution on top of an existing Postgres database, although with ����������, we could just as easily use another RDBMS or NoSQL tool of our choice.

This will be built around two user roles. Editors will be able to submit new versions for existing policies, using the policy_versions. Approvers can then approve these, as well as performing full CRUD actions on our policies table.

Along the way, we’ll provide the queries you need to create a look-alike database so you can build along with our tutorial.

When a new policy_version is approved, the relevant policies table will also be updated to reflect these changes.

This means that we can keep our policies up to date with our real-world requirements, while also providing a clear paper trail for how these have evolved over time.

You might also like our guide to building an engineering change management system .

Let’s get started.

How to build a policy management system in 5 steps

If you haven’t already, sign up for a ���������� account, which will allow you to build as many apps as you like for free.

1. Connecting our database

The first thing we’ll need to do is create a new ���������� app. We could use a pre-built template or an existing application dump, but today we’re starting from scratch.

When we choose this option, we’ll be prompted to give our app a name and URL extension. We’re going to go with Policy Management System.

At this point, we’re offered a range of data sources that we can connect our app to, including RDBMSs, NoSQL tools, APIs, spreadsheets, and more.

As we said earlier, we’re using a PostgreSQL database. When we choose this option, we’ll be prompted to enter our connection details.

Then, we’re offered a choice of which tables we’d like to Fetch, making them queryable within ����������. Our database has two tables called policies and policy_versions. We’re going to select both.

You can use the following query to create our tables:

 1CREATE TABLE policies (
 2
 3  id SERIAL PRIMARY KEY,
 4
 5  title TEXT UNIQUE,
 6
 7  description TEXT,
 8
 9  category TEXT,
10
11  status TEXT CHECK (status IN ('Active', 'Archived')),
12
13  created_at TIMESTAMP DEFAULT NOW(),
14
15  updated_at TIMESTAMP DEFAULT NOW()
16
17);
18
19CREATE TABLE policy_versions (
20
21  id SERIAL PRIMARY KEY,
22
23  policy_id INTEGER REFERENCES policies(id) ON DELETE CASCADE,
24
25  title TEXT,
26
27  description TEXT,
28
29  category TEXT,
30
31  status TEXT CHECK (status IN ('Draft', 'Pending Approval', 'Approved', 'Rejected', 'Archived')),
32
33  created_at TIMESTAMP DEFAULT NOW(),
34
35  updated_at TIMESTAMP DEFAULT NOW()
36
37);

You can populate these with:

 1-- Insert Active Policy
 2
 3INSERT INTO policies (title, description, category, status)
 4
 5VALUES 
 6
 7  ('IT Security Policy', 'Defines security standards for IT systems', 'Security', 'Active'),
 8
 9  ('Employee Conduct Policy', 'Guidelines for employee behavior and ethics', 'Human Resources', 'Archived'),
10
11  ('Data Privacy Policy', 'Outlines data handling and privacy practices', 'Compliance', 'Active');
12
13-- Insert Draft Policy Versions
14
15INSERT INTO policy_versions (policy_id, title, description, category, status)
16
17VALUES 
18
19  (1, 'IT Security Policy', 'Initial draft of IT security policy', 'Security', 'Draft'),
20
21  (2, 'Employee Conduct Policy', 'Initial draft of employee conduct policy', 'Human Resources', 'Draft'),
22
23  (3, 'Data Privacy Policy', 'Initial draft of data privacy policy', 'Compliance', 'Draft');
24
25-- Insert Pending Approval Policy Versions
26
27INSERT INTO policy_versions (policy_id, title, description, category, status)
28
29VALUES 
30
31  (1, 'IT Security Policy', 'Reviewed IT security policy draft', 'Security', 'Pending Approval'),
32
33  (2, 'Employee Conduct Policy', 'Updated draft of employee conduct policy', 'Human Resources', 'Pending Approval');
34
35-- Insert Approved Policy Versions
36
37INSERT INTO policy_versions (policy_id, title, description, category, status)
38
39VALUES 
40
41  (1, 'IT Security Policy', 'Final version of IT security policy', 'Security', 'Approved'),
42
43  (3, 'Data Privacy Policy', 'Final version of data privacy policy', 'Compliance', 'Approved');
44
45-- Insert Rejected Policy Versions
46
47INSERT INTO policy_versions (policy_id, title, description, category, status)
48
49VALUES 
50
51  (2, 'Employee Conduct Policy', 'Rejected version of employee conduct policy', 'Human Resources', 'Rejected');

Here’s how our policies table looks in ����������’s Data section once we’ve Fetched it.

Altering existing columns

In ����������, we can make adjustments within the Data section that will then be reflected when we start generating UIs and automations.

Firstly, all of our TEXT columns are handled the same way in our database, but ���������� distinguishes between a few types of textual data. Each database table has a field called description.

We’ll change these from Text to Long Form Text. Remember to repeat this process on both tables.

Next, our tables have a few attributes that we only want to offer defined options for. These are the status and category columns on each table. Our possible categories will be Security, Human Resources, and Compliance, although you could add more to suit your needs.

We’ll change the type for these columns to Single Select and input these options. Again, remember to do this for both tables.

The status in each of our tables will have different options. On the policies table, these will be Active and Archived.

For policy_versions, we’ll use Draft, Pending Approval, Approved, and Rejected.

Default values

We can also add Default Values within the Data layer. These will be used as a fallback when a row is created if a value isn’t specified for a particular column. We’re going to add these across all of our statuses and dates on both tables.

We’ll start with the statusattributes, using the options picker in their settings. On policies, we’ll default to Active.

We’ll repeat this same process to set the default status for our policy_versions table to Pending Approval.

Next, each of our tables contains columns called created_at and updated_at. We want both of these to be populated with the current date and time when a row is created, so we’ll bind their default values to {{ Date }}.

Again, make sure to repeat this process across both date columns in each of our tables.

Configuring relationships and user columns

Our tables already have the data we need to denote relationships. Each one has a unique id attribute. The policy_id attribute under policy_versions then corresponds to the id attribute in the policies table.

We just need to configure this in ����������.

Start by hitting Define Relationship.

We’re then presented with this modal, where we can set up our relationship.

We’re going to set this so that one row in the policies table links to many rows on policy_versions, using id as our primary key and policy_id as our foreign key.

Now, we can see related rows across each of our tables.

We also want the ability to link rows on our policy_versions table to specific users, but this works a little bit differently since we’re dealing with ����������’s internal Users table.

We offer two special data types for this, depending on whether we want to relate rows to one user or many.

We’re going to add two Single User columns to our policy_versions table. The first will be called editor. We’re also enabling the option to default to current user.

We’ll then add a second Single User column and call it approver. This time, we don’t need a default value.

Adding user roles

Before we go any further, we’ll need to set up our policy management system’s access roles. As we outlined earlier, we’ll have two roles called editor and approver, who can submit and approve new policy versions, respectively.

We’ll start by hitting Manage Roles. This opens ����������’s visual RBAC editor.

Here, we can configure our user roles. We can then assign permissions to these elsewhere in the builder.

We’ll start by adding our two roles, editor and approver.

We want our approvers to inherit their permissions from editors. To do this, we’ll place editorto the left ofapprover` and draw a line between them to denote inheritance.

Building role-specific views

We can define what permissions we’ll give to users with each role without leaving the Data section.

That is, we’re going to create database views based on each table that will define which read and write actions each role is granted.

The first thing we need to do is remove their permissions to perform full CRUD actions on the underlying tables. We’ll do this by setting the Access for each table to App Admin, which is the highest user role.

We’ll start by creating views for our policies table, as the permissions within these will be quite simple. Hit Create a View. We’ll be presented with this modal, where we can give our new view a name.

We’ll call this first one Editor Policies.

Then, we’ll set the Access to Editor.

Then, under Columns, we’ll set everything to read-only.

We’ll repeat this process to make a second view called Approver Policies, setting its Access to Approver. This time, we’ll leave all columns readable, except updated_at, which we’ll make read-only.

Next, we’ll create role-specific views for our policy_versions table.

We’ll create one called Editor Versions, with the Access set to Editor. This time, we’ll set all columns except title, description, and category to read-only.

Finally, Approver Policies will have all columns set to read-only, except for status.

And that’s our data model ready to go. Now, we can start generating automation logic and UIs based on this.

2. Setting up approval logic

Our approval management system will rely heavily on a couple of automation rules. So, we want to build these before we start designing our app screens.

Archiving policies

First, we’re going to set up a simple rule that will allow Approvers to set the status of policies to Archived with a button press. To do this, we’ll use a Row Action, which we can generate from the Data section.

Head to the Approver Policies view and hit Create row action.

When prompted, we’ll call this Archive.

This creates an automation rule that’s triggered by user actions on a specific row from the front-end of your app.

We’re going to add a single action step after our trigger. Hit the plus icon. Here, we’ll select Update Row.

We’ll set the Table to policies and hit the lightning bolt icon to open the bindings drawer for our Row ID.

Under Trigger Outputs, we’ll choose id.

Now, the Update Row action will be taken on whichever row triggered our automation rule.

We’ll use then add the status and updated_at fields.

We’ll set status to Archived as a static value. We’ll then bind the updated_at column to {{ date now "" }}, setting the current timestamp.

We can then hit Run Test to confirm that this works.

Approving policy change requests

Next, we’re going to build an automation that will be triggered each time a policy_version is approved. This will take the data from the approved version and use it to update the corresponding row on the policies table.

We’ll start by adding a new automation, which we’ll call Approve. This time, we’re selecting a Row Updated trigger.

We’ll set the Table for our trigger to policy_versions.

Next, we’ll add a condition so that we only continue if {{ trigger.row.status }} equals Approved.

Next, we need to retrieve the relevant policies row that our automation will update. To do this, we’ll add a Query Rows action, pointed at the policies table.

We only want to return the row that’s related to the policy_versions row that’s been approved. So, we’ll add a filter condition so that id equals {{ trigger.row.policy_id }}.

Lastly, we’ll use the id of the row this returns within an Update row action, pointed at the policies table.

We’ll add category, title, updated_at, and description as fields and bind these to their respective values from our trigger output, along with the current timestamp for our updated_at field.

Again, we’ll hit Run Test to confirm that everything works.

3. Building a change request screen

Now that we’ve set up our automation logic, we can start building UIs. Head to the Editor Policies view in the data section and hit Generate App Screen.

Here, we’ll choose the option for a table with modal forms.

Here’s how this will look in the Design section.

The first thing we’ll do is tidy up our existing UI by editing our Headline component and removing any unnecessary columns from our table.

Editors aren’t allowed to add new rows to the policies table, so we can also delete our Create New button and the associated modal form.

Here’s our remaining Edit form.

At present, this updates the policies row that a user clicks on in our table. We’re going to modify this so that it creates a new policy_versions row, linked to the policies row that a user clicks on.

We’ll start by setting our form’s Data to Editor Versions and its Type to Create.

We’ll then remove all fields except policies, title, category, and description.

We’ll also update our display text, then under Styles, set our Button Position to Top.

Now, we don’t want users to have to specify the policy they’re editing. Rather, this should be automatically populated based on which table row they click.

Currently, when a user clicks on a row, a State variable is set, using the clicked row’s _id. We’re going to set a default value for our policy field using this, with {{ State.ID_5Whzvriuv }}. We’ll then select the option to disable this field, so it can’t be written.

Here’s how this will look when we preview our app.

However, we also want to display the current values of our policy data, so that users don’t need to rewrite this from scratch. To do this, we’ll need to expose our form to the entire policies row to which our new version will be related.

We’ll start by nesting our Form Block inside a Data Provider component.

Then, we’ll add a filter to this so that it only returns the single row where _id equals our state variable from before.

We’ll then add default values to our remaining fields in the format {{ [Policies Data Provider].Rows.0.category }}.

Here’s how this looks in our preview.

4. Building approver screens

Next, we’ll start building screens for our Approvers.

Policy CRUD UIs

Firstly, we want to build a screen where users with the Approval role will have full CRUD permissions for our policies table.

We’ll start by generating another table UI with modal forms, this time from our Approver Policies view.

As before, we’ll start by editing our display text and removing any extraneous columns from our table.

For our Create form, we’re removing all columns except title, description, and category. We’ve also moved our button to the top again.

On our Edit form, we’re leaving all columns visible, but setting the ones we don’t need to Disabled, we’ve also arranged them into columns using their Layout settings.

Note that when we generated this screen, it automatically included a button to trigger our Archive row action. Since we have this, we don’t need our Delete button, so we’ll remove that.

Lastly, we need to set our updated_at column to the current timestamp whenever a user updates a row. To do this, we’ll open the actions drawer for our Save button.

Here, we can manually specify a value for our column, using {{ date now “” }}.

Policy version approvals

Next, we’re going to add a screen where users can review and approve new policy_versions.

So, we’ll start by generating a screen from our Approver Versions view.

Again, we’ve updated our display text and removed any columns we don’t need.

Approvers don’t need to create new policy_versions, so we’ll delete the button and modal forms that relate to this. We’ve then replaced our button with an Options Picker with its field and placeholder set to Status.

We’ll set the options for this to match the possible status values from our database, as well as giving it a default value of Pending Approval.

Then, we’ll add a filter to our table so that it only returns rows where status equals {{ [Status Filter Options Picker].Value }}

Now, when we load the screen, only pending requests appear by default.

Lastly, on our remaining form, we’ve set all fields to disabled except for status.

Then, under our save button’s actions, we’ll use the same binding as before to populate an updated_at value, as well as setting our approver column to {{ Current User._id }}.

From a functional point of view, that’s our app done.

5. Design tweaks and publishing

Before we push our policy management system live, we’re going to make a few minor UX improvements.

Firstly, under Screen and Theme, we’ll choose Midnight. While we’re here, we’ll also adjust our app’s color scheme to better reflect the ���������� brand.

Across each of our tables, we’ll use the Label setting to add proper capitalization to our display texts.

We’ll do the same on our forms using the Label and Placeholder settings.

Similarly, we’ll use the Label settings under Navigation to make the entries in our nav bar more appropriate.

Lastly, we’ll remove the navigation entry for our /editor-policies screen since users with the Editor role can only access a single UI.

Here’s a reminder of what our finished app looks like.

Turn data into action with ����������

���������� is the open-source, low-code platform that empowers IT teams to turn data into action.

To learn more about why organizations of all sizes choose ���������� to build internal tools, CRUD apps, approval workflows, ticketing systems, and more, head to our features overview.