Aggregating People tags across docs

Architecture question for the group.

For project management, let’s say I’ve got a doc per project keeping track of to-do’s, scheduling, tons of data like that about a project. To-do’s (and sometimes schedule items) are assigned to one or more people.

I’d like to give people a dashboard doc showing everything they’re assigned. How would you approach this? Is this maybe a Cross-Doc Plus thing @Paul_Danyliuk ? Roll my own solution via Pack that relies on API?

One option is to have a single doc that contains all projects, but:

  • Might run into scaling issues (hundreds of projects)
  • While many projects share common planning interfaces, some projects need arbitrary data structures
  • Permissions are important to me; not all users should have access to all projects

Yeah, that’s the case that Union Tables from Sync Tables Pro should be solving.
I actually wanted to get that feature out today but Pack Studio broke (code hints stopped working)


More thoughts / design constraints:


  • Each project doc will have multiple subprojects, each requiring its own todo list.
  • I might be able to steer everything into a single to-do table per doc, and filter or group by subproject when relevant. This is obviously the Codian way to do it, I’m just not 100% sure it’ll be an acceptable UX.
  • I might want to let people drag a todolist template onto the page whenever they want, and I would want those template-based todo lists to be automatically included in the aggregation

Active vs Archived Projects

  • I need a way to archive projects once they’re finished, hiding them from the aggregation
  • This could be by moving them into another folder, and/or via an Action inside the doc
  • Ideally I’m also going to want a way to bulk-manage active/inactive status of these project docs (can I move docs from folder to folder via API?)
  • @Paul_Danyliuk in the Sync Tables Pro approach, how would you recommend adding/removing todo list tables from the list of “everything that should be union’d into the aggregator table”?

Time Lag

  • Hourly refresh sucks (I get why it’s necessary, but that doesn’t change its suckiness)
  • I can educate users about the refresh button, but still not ideal
  • I’m tempted to try a push solution (webhooks) instead of a pull solution (cross-doc). This is definitely more brittle and prone to data getting out of sync. But maybe there’s a hybrid approach here:
    • Aggregation table is not a sync table, it’s editable
    • Aggregation table receives webhook create/update/deletes
    • Sync table is also running in the background on the same doc; automation listens for updates to sync table and applies them to the aggregation table as a way to make sure nothing gets missed/out of sync
  • This webhook approach seems hard to scale (if there’s one aggregation doc per user, directing traffic gets really complicated really quickly)
    • One option is to actually just have a single aggregation doc, with security-through-obscurity. I have to consider if this is acceptable in my use case, and it miiiiight be
    • Another option is to use one main aggregation doc as a central repository, receiving webhook updates from the project docs, and forwarding them as appropriate to individual user aggregation docs via automations

Yeah the Create Doc API call will let you choose a destination folder

It’s not a good idea to keep multiple sources of truth, and this pretty much tells you the architecture you need. I would probably just make the master doc a non-editable API-auto-constructed aggregation doc (maybe have each page = a doc). I would also set some rules for people such as:
→ you can only make a doc for this project in this folder, because the automated aggregation only works in this folder
→ you should only make a new doc for a project if…

I wouldn’t do more than a 2 layer deep system. IMO it’s fastest to add search to your master doc in which you can drilldown to the other docs quickly.

This can be done with a native PC copy and paste. If you make your system such that you can quickly copy paste via text, this will make it much simpler and on the plus side you won’t have to build a small widget thingy

In your master aggregation doc, someone can see everything. Would you want the Boss or PM to only be able to see this?

Thanks Jake!

Just on creation, or can I move existing docs?

Can you say more about how you imagine this working?

Yeah that’s fine, aggregation doc would be limited to folks who are allowed to see everything. But then I would use Sync Tables Pro to create a doc for each user, giving them a view of “all to-do’s assigned to me across all projects”, which is derived from the aggregation doc

Just something simple that would enable quickly copy pasting. The power of copy paste is very powerful. Your system shouldn’t be too complicated that people can’t copy and paste

Yeah so the Create Doc API if I recall lets you copy an existing doc and choose a destination folder. On quick inspection, there isn’t an API call to move a doc to another location. You can only create a doc

Create request (also a copy request if you select a source doc as a template)

1 Like

Just to give you further idea on the API and the aggregation doc.

Say you tell all your peeps to make docs in one folder

You can use the List Available Docs API call to get a list of Docs in this folder

Create a new master doc in another folder accessible only by management. Call it “PM Master Oct 4 11 AM v2” for example.

For each doc in your working folder(s), append the contents of that doc into this master doc.

Your master doc can have permissions at the folder level if you add it to a certain folder, or at the individual level via additional calls with the “Add Permision” API call

The end result are a lot of master aggregation timestamped snapshots docs named like “PM Master Oct-4-2022 11 AM v2” in one folder. This gives you archival capability too

I don’t have much need for the archiving, but yeah I think the List Available Docs API is the way to go, with all active projects in a single Active Projects folder. I’ll probably implement via a Pack that:

  • lists the docs into a sync table (which includes buttons for adding people’s permissions as you say, and also copying to the finished-projects folder and deleting the original? is that the best bet to get around the lack of API move functionality do you think? the copy feels icky but…)
  • creates another sync table of all the todos from all those docs

And then this aggregator doc would also have a users table, which I’d use as an interface to spin up individual user docs (each with their own Sync Tables Pro view of the aggregated todos table, plus a table of all the docs they have permissions on)

1 Like

Instead of deleting the original, wouldn’t it be better to just have timestamped snapshots available at a whim?

I think you should do an objects mapping/diagramming to figure out your db architecture

If you mean the original doc for a certain project, I can’t have hundreds of old project docs hanging around in the Active Projects folder, if users are going to that folder to access docs. I need to move them out of there (and sounds like cloning/deleting is the only API-available option; could make everyone do it doc-by-doc from the 3-dots menu, but that is not ideal as sometimes we cull a batch)

If you mean the aggregation doc, I plan to cross-doc its aggregated tables into individual docs per user, so that they can see a permission-bounded set of just their material. Because of this I think I need to keep a consistent aggregation doc.

I continue to be challenged by the 1hr refresh limit though. Main issue in my design:

  • A user wants to refresh to see the latest list of project docs they have access to / todo’s assigned to them
  • They hit refresh in their personal doc, which does refresh their sync table
  • However their sync table is fed by an out-of-date sync table in the main aggregation doc, which they don’t have the ability to force a refresh on

Really makes me think hard about whether I can get by with giving everyone access to the main aggregation doc, and just filtering to User() [Warning to others reading this - this is NOT real security and is only acceptable in situations where users poking around in others’ data is acceptable to you]