Saving custom OAuth variables to DB for later access

Queries and Resources
1

I'm looking to access Xero "offline" for my users, refreshing the data as need be.

  1. I connect to the Xero API (thank you @jclutterbuck for [this])(Encountered an error: CSRF DETECTED - #6 by jclutterbuck)

  2. This works, I can query the API, get and display data as needed. However, this only works "in session" - i.e. with the user logged in. What I really want to do is be able to access this now established authorization outside the user session in a daily workflow.

  3. I've defined the key variables (authenticationToken, TenantID and RefreshToken) in the custom OAuth, but they are basically "stuck" in the API call frame - I can't refer to them in any other part of the app. I'd like save them in a DB, so i can access Xero when the user isn't logged on.

  4. I've read everything on here (Custom API authentication | Retool Docs) - doesn't help as it assumes I use the custom API call every time. I've tried using javascript in the custom oauth but I can't access a DB from there.

  5. I've found a similar question here Store OAUTH2_TOKEN & refresh token in DB where @lemontec suggests he writes a custom OAuth and saves it to the db, but I honestly have zero understanding of how you'd do that in retool.

I'm new to ReTool and I have to admit I'm finding this extremely frustrating - it'd be almost trivial to do this in pure code, and here I feel I'm fighting the system to do something that should be simple. I'm sure I'm doing something wrong, but if anyone has any idea, I'd be extremely grateful.

PS: there's also an issue that retool defined variables with a custom OAuth can't handle arrays - in Xero most people have multiple companies meaning multiple tenant IDs (most Xero users are accountants with many clients). Handling TENNANT_ID within OAuth is basically impossible, hence another reason to use a DB to save responses obtained in the custom OAuth.

2
  1. Create a resource for Retool's Postgress DB and a simple table to store oauth data.
  2. Create a workflow in retool that will run periodically and refresh this data in your table. This workflow will run out of the scope of your app.
  3. Create a query and run it on a page load (advanced query settings) and (optionally) save data to localStorage.
3

Thanks unfortunately that doesn't address/explain the fundamental issue of how I get hold of the authentication tokens in the first place. I need the user to authenticate with xero to get the tokens and then store them.

4

Hey @drReech! Thanks for reaching out. You're right that there isn't a great solution for this that comes baked in. That is partially intentional, as we want to keep sensitive information - like access tokens - fairly sandboxed.

I think the simplest solution is probably to add an "API Request" step to your custom authentication flow that triggers a workflow via webhook in order to insert data into your database. That way, you can pass the authentication details to the workflow via the body of the REST request and have the workflow handle the database insertion.

Let me know if you have any clarifying questions! I hope that helps. :+1:

5

Hi Darren,

Thanks for getting back. I assume you mean the API should then be built and hosted outside the retool environment correct? I can do it, but it's a dependency...

Also - do you know if the retool variables store the tokens as array? The TENANT_ID is an array and retool, on the surface at least, only seems to store the first item.

Best wishes,

drReech

6

That's definitely an option, but not what I was specifically recommending. My suggestion is to:

  1. Create a workflow that takes in a set of auth data and uploads them to a shared database.

image
image1666×664 55.3 KB

  1. Trigger this workflow as one of the final steps of your custom auth flow, passing your auth data as the body of the POST request.

image
image1354×882 58.9 KB

Put together, this should effectively export your auth data and make it accessible without requiring a user to be logged in. :+1:

Last but not least - given the fact that you're retrieving tenant IDs via a generic REST request - you should have access to the entire body of the response, which will presumably be an array.

2 Likes
7

That is P E R F E C T

Thank you @Darren - really appreciate the help. :+1:

1 Like