Retool: Source and Request Intake

[mbodeantor]

Context

We would like to replace Airtable for editing data sources and other information: part of https://github.com/Police-Data-Accessibility-Project/data-sources-app/issues/32

https://retool.com/

Requirements

• [x] Create a single retool app with one page for Data Source Intake and one page for Data Request Intake
  • there should be a button for logging in, which prompts users for their credentials
    • for the development period, this can just auto-use admin credentials
  • the structure of each intake workflow is:
    • a row of action buttons: approve reject/archive (source/request)
    • these buttons submit the status as well as changes made to the form as a PUT to the API
    • for the development period, an add test row button (for testing)
    • a form, prepopulated with the submitted properties and editable by the approver
• [x] We should use our API to make changes to the database, not edit the database directly
• [x] requests start with intake status, and the approval process either causes them to be archived (the approver should be prompted to enter an archive_reason) or ready to start
• [x] sources start with intake status, and the approval process either causes them to be rejected (prompt for a reason) or approved
• [x] after action is taken, everything should refresh

Nice to have:

• [x] since there are so many data sources properties, let's hide the empty ones and have a "show hidden properties" button to expand them

Tests

We'll need to be careful about data integrity and which functions we allow.

Docs

We'll need internal docs for approvers (how to access, when to do it)

[josh-chamberlain]

PDAP has a Retool app/account initiated, I can share access when the time comes

[maxachis]

PDAP has a Retool app/account initiated, I can share access when the time comes

@josh-chamberlain Can you share access with me?

[josh-chamberlain]

@maxachis invite sent. Marty's using retool as part of his day job, so the plan was that he would take this on / get it started so we have good patterns established

[maxachis]

@josh-chamberlain Surprise attack! I finished a draft of the approval process app! This does, however, rely on a direct connection to the database, rather than an API.

https://pdapinternal.retool.com/apps/d4d34d3c-4755-11ee-b52a-c70af80341b4/Data%20Sources%20internal

[maxachis]

@josh-chamberlain A larger question is: What are all the distinct functionalities we want to perform with retool, in addition to the approval process? I'll create a header here that we can fill in as need be:

What we want Retool to do

• An approval workflow for requests and data sources, where users make modifications to submitted properties, select or enter an approval (or a rejection reason), and trigger an approval API call

[maxachis]

@josh-chamberlain there's also some interesting wrinkles in terms of using API calls rather than directly referencing the database, which I'll explain below:

API or Direct Database Calls?

I recognize that our general approach has been to try to mitigate direct calls to the database where possible in favor of API calls. That is an option here with Retool -- using a RESTful API is supported. But so is directly querying the database, and both options have some advantages and disadvantages.

Considerations	RESTful API	Direct Database Query
Simplicity	More complex. Involves multi-step authentication and refresh process, with more room for errors. Pulling data similarly involves more steps.	Simpler. Login to the database with a user, pull tables and select rows as desired.
Security	More secure. Multi-step authentication process (albeit one that still requires the email/password to be defined explicitly and initially). Routing requests through the API enables us to have more control over the process by which data is accessed.	Less secure. One point of logging into the database, and has exposure to all tables the user has exposure to quite easily.
Speed	Slower. Requests must be routed through Flask, then to Database, and then back through both channels.	Faster. Requests simply routed to database.
Maintainability	Better because decoupled from database logic -- as long as the endpoint remains the same in intent, the internal functionality of the endpoint can change without impacting these requests. If we ever use something other than Retool, we can maintain the API request logic.	Worse because coupled with database logic. More sensitive to changes in database schema. If we move away from Retool, we're more likely to have to redesign this logic.
Useful for	Designing app for long term compatibility with Retool and modularity with other systems.	Rapid prototyping of apps
Other advantages	Enables us a second means of testing the endpoints.	Enables us to more quickly detect breaking changes in schema.

My thinking is that, while we should aim ultimately for using the RESTful API calls, the database calls enable rapid prototyping that will allow us to determine what it is we want before committing to more substantial endpoint logic.

[josh-chamberlain]

@maxachis wow, surprise attack indeed! Is it PDAP's birthday?

I tested the workflow and it seems to be working. Nicely done. I slightly modified your "what we want retool to do" comment. It's exclusively related to approvals, though the approval process is the appropriate time to do things like fixing typos. re: database vs. API, I must be firm here. You are lovingly crafting an API to protect us from the pitfalls of direct database manipulation. One motion, many actions!

That said, there's not really much endpoint logic to worry about—provided someone has the appropriate permissions to be approving stuff in Retool, all they're really doing is modifying a few fields in a given row. It's a PUT endpoint. At the moment, this is just admins, and probably just me.

One immediate change I can see: can we just do a reject and approve button, and if someone selects reject they are prompted for a reason? The most common case is approval, which currently takes two clicks.

[maxachis]

@josh-chamberlain Got it! I'm working on the Rest API version of it (easier than I thought it would be), and will be providing that here shortly.

One last question, and it is a minor one: Do you want the approval queue to refresh after every approve/reject, or refresh with a manual "Refresh" button (or both)?

[maxachis]

@josh-chamberlain Updated API version is up! See here.

Note that this is running the API from https://data-sources-v2.pdap.dev/api . This means:

• We're running on the stage database
• There aren't currently any real pending data sources, so we'd need to either add some or change the status of some to pending to make it work (either is fine because the database will refresh daily).

Additionally, currently the Log in to API button does nothing -- the current version simply uses a pre-set test admin user.

[josh-chamberlain]

@maxachis I didn't see this comment before we spoke today, so thanks for repeating yourself! It doesn't need to refresh after each one. That might be nice in the future—if there are concurrent approvers working, or someone leaves their session open, things would get annoying fast.

As mentioned, I think a "create test data source" button would be good for testing this workflow.

[maxachis]

@josh-chamberlain I added a create test data source button! Because of the nature of retool, it might take a second for the data to update, and the page may need to be refreshed.

What do you think the next steps should be?

[josh-chamberlain]

@maxachis I'm getting a {"status":400,"message":"Not enough segments"} when trying to add a test data source

[maxachis]

@josh-chamberlain It's possible that you may need to click the Log In to Admin API thing: try that out and see if it works then. If not, I shall have to get to the drawing board, for it would be the most dreaded #WorksOnMyMachine problem.

[josh-chamberlain]

@maxachis this did work for me, after a try or two—not going to dwell on it.

I think the next steps would be merging these two approaches so that they work in basically the same way.

• Data Request Intake features a form which shows the relevant properties, which can be edited before action is taken by the reviewer. Data Source Intake should have the same.
  • given the large number of properties, perhaps we should hide properties which are empty
  • given the exceptions that might occur, it might be nice to have a "show hidden properties" button, if such things are possible
• Data Source Intake features a lovely set of 3 buttons—approve, reject, add test. Data Request Intake should have 3 similar buttons, except with archive instead of reject.
• In either case, submitting the form triggers a PUT request to the API.

I think once both forms look + work basically the same, you can safely assign the issue to me and I will experiment with layout and instructions for approvers.

[maxachis]

@josh-chamberlain I believe this is now ready for you to take the reins, unless there's something I'm missing!

[josh-chamberlain]

@maxachis magnificent! I think this is good to close for now, and we can always come along with enhancements. I'm sure as I use it, some will occur to me.