[TRICK] Public Apps on Coda - "hidden" Cross doc from public shared doc to private Backend

[Mario]

Hello community!

I’ve recently stepped into a case in which i had to move data from a public doc to a “Backend” doc made to be private, but in which cross doc was not working correctly due to “filter” “limitations”, i’ll explain my case below.

I have doc A that is public, it is shared with “everyone who has a link”
This doc contain the personal pages or profiles of users of the doc, in my case it is a music club, so users can manage their accounts, set their “partecipate/ not partecipating” variable for each events, and others functions (like pay for entering with stripe, and others)

The data that this doc contains are all shared with all users of that doc (basically whoever opens the link)

I have then another doc, doc B, that is the Backend of that app, in which organizers can see all their club members, set their events, configure payments link with stripe, manage event partecipation and entry/exits etc

This doc instead is “locked” and just certain users can actually use it, the organizers

Now, most of the data’s comes from doc B to doc A (so, “events” table, and others, are cross-docked into doc A)

This is fine, 'cause original datas are just in the doc B, unaccessible from external users, easy to hide just creating a filtered views with just some columns visible, and then the one that is synced is that one, this is true for all tables except one, the “People” (or “Members”) table, that is into doc A

So, the problem is, how can i “lock” the table “People” in doc A, but still unlock it for it to be able to be sync into doc B?

“lock” i mean “filter out from sight”, the table is already in a locked dock in a hidden page, so user can still see it but they can take no direct action on it.

The problems is:
The “People” table must shows no data to public users, this is “easy”, we can just hide all columns and filter it for something like “rowid=0” (that is always nothing)

Like that in doc A, no data is shown, but all formulas and other tables in the same doc read datas without problems, so this act of hiding by filtering would be enough if we had just one doc

BUT

Cross doc doesnt sync rows that are filtered in the original table AND does not sync columns that are hidden, so with this configuration i get “no rows synced/available” message in doc B when trying to sync the “People” table

So, how can i show no content in doc A but still sync data to doc B?

The trick is to understand how “Cross-doc” ask for datas in doc A

Let’s start with some practical formulas

In the people table, in doc A, we’re gonna have a formula similar to this: (each row of “people” table rappresent a members, they are created in the first login in the doc with a button, that row have a column, “User”, that contains who created this row, as a user reference)

Filter(user=user())

Like this, every user see just their own profile row when looking at the table, then we add some cool canvas formula, in my case 3 of them

1. currentmember
   

   

2. is currentuser superuser?
   

   

3. is user anonymous?
   

   

Description of those

1. people.filter(user=user()) - this is to understand who member (and not user) is using the app, this is to have an internal reference to who’s using the app not as a coda user but as a App Member, it’s gonna be useful to check for example if currentmembers is validated or accepted, and to see if he have payed for example iscription fees etc, before being able to partecipate in events
2. if(user()=@mariobrussi, true, false) This just check if currentuser (user()) is me or someone else, this is useful to unfilter all tables if i am looking at the doc, this is pretty useful to diagnostic purposes and to edit manually the data
3. Check based on user().email if current user is anonymous (i do not want anonymous user to be able to do actions and things on the doc)

So, we’re gonna use those to get our final filter formula for our People table who does those 4 things

1. Show their profile if user is not anonymous
2. Hide all datas if user is anonymous
3. shows all data if user is superuser (actually, you’ll see in the final formula below, in this case i’ve decided to use a toggle (true/false) placed above the table, to allow also for other users that have “admin” capabilities to manually see and edit all Members datas, i could have edited the “superuser” canvas formula to check if user() is inside an extra table called “admins”, but for the moment i like this application, in future i could change it)
4. shows all rows if “user()=cross doc”, this is tricky, as you can read on apis documentation, is not really clear how cross doc identify itself when talking to coda doc, and especially to coda filter formulas, so the goal here is to find a formula that is able to discriminate between a normal user, and a cross doc sync, i’ve found it after trying for HOURS and it’s the main reason why i’m writing this post!

p.s. this is the toggle i’m referencing in the formula, it says “manual control of users”

SOOOOO ARE YOU READY FOR THE FINAL FORMULA???

p.p.s. the formula who’s able to discriminate is isSignedin()

Cross doc’s sync are NOT “signed in” in coda, BUT also others could not be signed in, so we need a little more tricky passages, for example checking if user is anonymous, but at the end of the story this is the trick

So, data’s are hidden in doc A, (if you take JSON no you can still have all of them, Paul talked about this “problems” 100+ times in the community so you can just search for it), but still synced in doc B!

Did you know about this “trick” to hide datas from original table but still get them synced on the cross doc table?

Let me know down below!

Thanks for the attention :)))

[Agile_Dynamics]

@Mario, this is genius!

it solves a big problem indeed

• ability to hide data from users of the doc
• but reveal the data to the cross-doc sync process

and you explained it so clearly.

so in summary:
the cross-doc sync process has IsSignedIn()=false AND is not an anonymous user.
is that correct?

thanks for your generosity, ingenuity and clarity.
this is exactly the stuff that makes this forum great!

respect
max

[Mario]

@Agile_Dynamics

and you explained it so clearly.

Thanks @Agile_Dynamics , i was really unsure about this

I’m sure it could be explained better, but i’m happy it have reached the goal

@Agile_Dynamics

so in summary:
the cross-doc sync process has IsSignedIn()=false AND is not an anonymous user.
is that correct?

Yes! This is the best way to summarize the concept!

To be a little more precise when the “cross doc sync” agent ask to sync data, the filter of the table is loaded and so, i imagine a process like:

Doc: “Hey you who are you?”
Sync: “Hey! hi there, i’m -coda api key-, we’re basically cousins!”
Doc: “okey, you’re authorized to get those datas, but they are filtered, i’ve got a couple more question for you before giving you your data”
Sync: “here for this, ready when you are”
Doc: “So, first question is: “are you logged in”?
Sync: “”
Doc: “What a crystal clear answer!” “now, are you an anonymous user? tell me your mail!”
Sync: “”
Doc: “Hey man! your answer are so clear! It’s a pleasure to work with you! you’re definitely part of our family!” Here’s the data i can give you, just so you know, you’re gonna get every row which have the row id of itself, said between us, all of them!”
Sync: "cool man, thanks for the data, appreciated, i’ll call you back in an hour, i would visit you more often but actually my parents let me go out just once every hour at maximum …unless some cool looking monkey press that new button someone put in you Doc, the RefreshTable() one! So sad our friend automation cannot press it "

Man i love this ahahahhaah

Final confirmation i’m childish lol

But, let’s be honest, wouldnt you pay to see how the story unfold between Sync and Doc? aren’t you already affictionate to Doc’s character???

[Mario]

Update!

Under suggestion by our dear @Christiaan_Huizer , we can open an “improvement” call to fix that undoubtly ugly formula, just to start from the same point, this is the algorithm:

P.s. precisation to add: the “manual edit” toggle MUST be on “Personal” and not on “Collaborative”, if not it would “hack” the formula and create problem! (that’s why i probably didnt used the “superuser” canvas formula, sync agent will probably have hard time managing that)

[Martin_Portevin]

Thanks a lot Mario, this is going to be very useful ! I love this forum and ingenuity of this community.
I’m pretty good with Coda now and this is the kind of stuff that makes me want to share immediately if I ever discover a new approach

[Mario]

@Christiaan_Huizer suggested a way more elegant formula, using switchif instead of a lines of nested ifs, in this way we have a formula that look for specific situations, each that have to be described separately

switchIf(
ManualUserEdit = true, result 1,
IsSignedIn = true , result 2,
IsUserAnonymous = true, result 3,
sSignedIn =false AND  IsUserAnonymous = false, result 4)

Result 1 - Shows all rows for manual editing
Result 2 - Shows each current user() own profile, just his own row
Result 3 - User is logged in anonymously, so don’t show any rows
Result 4 - This is the cross doc sync agent, so show all rows

In this way we define unique situations without other nested conditions, i think it’s a really clean way to write this formula!

All credits to Christiaan for this application

[Paul_Danyliuk]

Uhhhhh I haven’t yet read all of it but something doesn’t sound right already. I’m setting a reminder on this topic to have a closer look at it.

I’m just having a feeling that you could have an insecure setup or do unnecessary steps there.

P.S. Alright, you’re right about the “100+ times” one Gonna tell it once again: nothing is hidden in a doc that a person can load into their browser. There’s no need to even look into the document binaries to get the data. Send me the link and I’ll prove it to you.

Cool that you’ve found the formula that worked for filtering specifically for a cross-doc account. Earlier it was enough to just test for “automation-bot@coda.io” to be the user.

If I were you I would reconsider the setup though. You don’t want to have your table of People to live in your public doc. You rather want a form, a “push” (either through Cross-doc Actions or a custom pack with just one AddRow method), or any other possible way to get the data from your client doc into the backend doc, and store it safely there.

[Mario]

Hey Paul! Thanks for checking out my post!

now i’m a little curious about your “getting data’s from docs” methods!
I knew about JSON for a long time, and off course i know copy & paste of tables (i actually “solved” this in another tricky way, basically referencing things that cannot be copyed in another doc, for example synced tables or pack tables) but i’m unaware of others entering/exploring methods (does api’s allow everyone to edit a doc if it shared with them? also with just a shared with a link? or do they lock it somehow? documentation are definitely unclear on this and support couldnt help me about this, but i’m sure you know how api’s works…)

If you can share them i would like to “prepare” for those kind of attacks! as you probably remember i’m a long time supporter/advocate for using coda for creating public apps!

“Sadly” now cross doc doesnt identify anymore as automation-bot@coda (i’ll skip this easy easy joke) , but if you ask me that would have been a more elegant way to know who’s doing what

Now the critical part!
The “having people table on the public doc”!
I know it’s so damn wrong to have whatever data accessible to users, but, i have some motivations!

First, all sensible data’s are NEVER stored in the public doc but ALWAYS in the private ones, for example i store phone numbers of users and their “fiscal code” (something like social security numbers equivalent? basically the number gov give you) in the private doc, also information about “did this user pay for his account?” are just synced from the private (and appear in columns of the people table in the public doc, so i can use them without actually showing them, i mean for example, in order to be able to pay you have to complete before your profile inserting your “gov code”, this is just a cross doc action (new row button) that user can press just once, once the data are sent to the private doc, i sent back just a “true/false” to let the public doc know if that part is done fine and so he can unlock features)

So, sensitives datas are not present anymore, BUT still there is email of user and username, those are basically impossible to be removed (also if also here i’ve found a way, if you copy and paste a table, the “createdby()” of rows is resetted, so, little improvement )

Now, let’s keep in mind that the table “People” contains also profiles for users! So also if i move People in the private doc, i would have to have a user table in doc A (public) anyway! this table would have one row per user and some columns that have “true/false” in them!

So, at the end of the story, i would end with another table that have very similar data

i would prefer 100 times to have all sensitive datas (including user rows etc) in another doc and just use cross doc actions, but you know better than me that the biblical times required to sync tables doesnt allow for a realtime use of those…

Just in case, imagine this guy outside of the music club, that is waiting for his app to get the confirmation of sending the data for 5 minutes, or waiting for his payment receipt for 5 minutes (usually actually more)

It’s simply ridicolous! So, actually, cross doc sync works “fine” for slow or definitely not real time updates…
But it is more or less unusable for fast or real time updates…

p.s. Count me in the voting for realtime cross doc!!! :)))

So, storing is 0 problems, retriving data’s and confirmation of cross doc actions is THE problem, that made me chose to host “real time/fast” procedures in the public doc…

[Paul_Danyliuk]

Can you please share the doc privately with me? (paul@codatricks.com) as if you were sharing it with any other user? and I’ll try to break into it and also send you a video of that, privately?

When it comes to security, I prefer to overreact rather than underreact I know you’re a skilled Coda user though so I would be surprised if you didn’t have your valid reasons.

[Mario]

With pleasure!
I’m really curious!

[Paul_Danyliuk]

On the 5 minutes thing, you could use webhooks now, which are faster than regular automations and cross-doc syncs. You could also use per-user docs so that everyone’s only shared what they should have access to. You can actually make a setup with two webhooks triggering one another.

I was able to see the full list of profiles without opening developer tools or making any API requests. There are no people with IDs between 6 and 47

[Mario]

@Paul_Danyliuk

You can actually make a setup with two webhooks triggering one another.

i’m very curious about this kind of setup! Did you wrote any tutorial or post about those?

Also, the “one doc per user” could be an approach, basically every user instead of adding a row in the public doc, will press a button to copy the doc! This is a nice way but it would leave those docs open to edit for 2000+ users (that are mostly music club users, not the safest move i would say…also 'cause locking wouldnt be usable…)
What if they right-click on a cross doc action and mess it up? should i mantain 2000+ doc’s individually?

P.s. yes you’ve seen the complete table
Also, i’ve just noticed that if you copy and paste the table into another coda doc you get no usable data’s (more or less) but if you copy and paste it in a sheet or excel the data’s are all there
Was this the way? any idea how to mitigate this?

p.p.s. how did you copy the table? with a fast cmd+c when the “Persone” table was loading right? good trick in case! maybe solvable with some predetermined filter that shows nothing and then after loading the filtered table instead of a show everything and then filter all…