Retool: Building a dashboard for external users

Retool is a good platform to speed up your internal development and shift from paper-operated business or even GSheets (yes, we know a lot about you!) to the all-digital interactive environment, where data flows freely and quickly. While you can visualize your data with ease and manage them effectively in Retool, a security question is also in focus. The platform provides pretty flexible ways to manage user permissions, assign user roles, and protect apps and resources from unauthorized use – not all the employees should be able to see all the data, correct?

In addition to that, recently a client of ours came with an urgent need to build an external dashboard. While operating on the same dataset, these apps should also solve two main problems before going live: how to distinguish an external user from an internal one and how to protect the company's data not to expose anything sensitive to the whole world. What ways to solve these tasks in Retool have we found?

External User Management

We’ll start from the first question. What’s the way to manage your external accounts? First of all, you need to upgrade to the Business plan. It might look costly, but gives you a number of important features: you can create multiple environments, have global app color themes, remove all the default branding, use a custom domain, and automatically assign your external users to the special permission group on a lower seat price.

Let’s estimate costs of external users for you, depending on the number of people you are going to invite to your instance.

The Retool Business plan costs $65 per developer or administrator. A regular user from the internal team costs $18. For end-users that do not belong to your organization, Retool offers an ‘External Users Tier’. The first 249 external users will cost $10 each ($2490), and then users from 250 to 499 will cost $7 per one ($2490 + $1743 = $4233 ). Starting from 500, you can get all the user seats for free—let’s say 1000 users will cost you $0 ($4233).

To make it more visible:

What’s good about these special pricing tier, except the… price itself? Well, you can specify which domains are internal and belong to your company, and all the invited users that have emails on different domains, will be automatically added to the built-in External Users group. It means that you configure permissions for External Users and the company's internal domain, and then just send out invitations. Voila!

You can find a detailed guide following this link.

Data Management and Safety

While we have to set up automated user management, we still need to make sure that externals see only the data that they are allowed to see. How to achieve this? We need to store and manage additional information to map users to companies and their data. Imagine, you have a table called ‘clients’ that holds all the data about your clients—and all other data tables refer to it using the client_id foreign key, to say. Also we have users with some emails outside our company. How to link these datasets? Let’s add an additional table called client_domains. It might have just three columns: primary key (id), foreign key (client_id), and the string field to store a domain name (domain).

The data inside this mapping table can be managed manually using a simple app, or you can grab it from your CRM systems, etc. automatically using workflows.

It’s better to have this mapping table next to your data as long as you will be able to use SQL joins to filter the data based on a user email. And this is a pretty safe approach: SQL is run on the backend side, and having the ‘Protect Queries from Variable Spoofing’ feature enabled by default, we are sure that {{current_user.email}} reference in a query is replaced by a real email of a user that triggers it.

Read more about spoofing protection here.

Development Approaches

The most interesting and challenging part in the client’s request that gave a start to this article was that the client wanted to have the same dashboards for internal and external users to be developed in parallel.

The first (and very obvious) move for us was to fork the existing internal app in its actual stage, modify it to limit data fetching based on external user email-company relationship, and then develop two apps in parallel.

‍

It might work, and sometimes it’s the right approach. However, the source (internal) app at that moment was in the early development only, we were only figuring out how to better design it, and the timeframe was limited (less than a week, to say), so we switched to another option—we added a logic to distinguish external users and fetch only their data, if an app is opened by an external user.

To achieve this, we needed to inject a check in all the SQL queries we work with. If a user belongs to the ‘External Users’ group, an additional WHERE condition was fired to get the client_id based on the user’s email domain – and then this client_id was used in all the JOINs to filter out the data.

This is a pretty simple but safe approach. However, it might be tricky, as you have to make sure that if a user is classified as external, but no cliend_id is returned based on their email, your data queries should return nothing (and not all the unfiltered data).

The last question here is how to modify the UI based on the user role. For better manageability, we divided all the UI parts into logical groups—sometimes it was just a control, sometimes it was a group of controls, gave them friendly names (‘campaigns_container’, ‘campaigns_total_votes’, ‘users_active’, ‘users_return’ as examples), and saved this data to a special table with bool columns with one row. This approach is very similar to that we use in a flexible report config example.

If a user is external, an additional query is fired that grabs the current UI parts availability—and then this data is used in the ‘Hidden’ prop of components on the canvas. How to make the remaining components take all the width remaining? Use a brand new Retool component called ‘Stack’.

Here to help

We hope that this guide will make your life easier and give you some useful advice. If you have any questions, or maybe additions to the topic, feel free to reach me or any of the Akveo representatives. Let’s find the best approaches possible together.

---

Dmitry is the Head of Low-Code Department 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 with him how to use Retool Workflows, how to improve application security, how to schedule changes, how to generate and send email reports, what is Retool Database, and how to work with Retool Dynamic Tables. More topics are coming. Stay tuned!