DEVONthink + Relational Databases

[Lydian91]

I see there are a handful of other posts that touch on relational databases—curious if others have tried these in conjunction with DEVONthink. I’ve spent a few years cultivating a setup I’m genuinely happy with, but there are some friction points that one inevitably encounters with any knowledge system. So I’m both interested in sharing my own progress and also seeing how others navigate some of the problem areas in their own setups.

My system has two pillars:

I use DEVONthink for capturing, distilling, and archiving information-dense material. The sync is excellent, it handles large file collections gracefully, and the UID linking is very robust.

Collections Database serves as a global relational index on top of DEVONthink (I also tried Tap Forms, which too is excellent). Collections is fast, easy on the eyes, mobile/touch-oriented, and makes a large knowledge base easier to browse from a higher altitude. I have databases for people, books, articles, and other record types, with explicit relationships between them along with various metadata fields. When I open a person’s record, I see every book, article, and item associated with them—most of which contain URLs to the accompanying files in DEVONthink. Both apps support UID linking on desktop and mobile, so that connective tissue is what makes this possible. Collections has been a really powerful front-end that fosters more agile surface browsing and discovery that can be harder in a deep, more surgical environment like DEVONthink.

I tried ditching Collections and leaning more into DEVONthink’s discoverability tools, but See Also, AI clustering, tags, etc. are emergent and probabilistic. They surface connections I didn’t know existed, which is still valuable, but Collections gives me relationships that I deliberately construct. Both matter, and I don’t feel that they’re substitutes for each other. Additionally, DEVONthink’s tags are siloed to a single database. Because I work across multiple databases, I have no native way to maintain a global index of something like People — who appear across every domain I work in. Collections solves this.

I’ve also looked seriously at Obsidian bases but have personally found it best for journaling exclusively. The vault architecture is more flexible and bases are really powerful, but Obsidian for me is not optimal for heavy file work or deep relational data. At the file sizes I’m working with (video courses etc.), Obsidian’s iCloud sync requires downloading the complete database to a mobile device, and Obsidian Sync has a storage cap that is too low. DEVONthink’s sync handles this problem more adeptly at scale.

Pairing DEVONthink with a relational database has been a surprisingly powerful combination that has stuck with me in a way that other setups have not, but finding the right dance between the two has taken some real effort. They contain complementary data quite optimally but at the expense of pruning two gardens. Both apps have deep automation support, and I think this is probably where the where the answer lives for truly optimizing this pairing. Collections has comprehensive Shortcuts integration as does DEVONthink to Go. DEVONthink is highly scriptable on the desktop but lacks Shortcuts support, so any automation I build for mobile won’t carry over to the desktop—something to figure out.

Given that, I’m curious about automation strategies for these apps. Capture is a big problem area. Items can enter the system from either app, so I need a way for example to add something to DEVONthink and have a parallel Collections record created, with UID links automatically populated in both places.

I can offer more specific workflow friction points, but I’m also more broadly curious about how others manage parallel data sets across multiple apps and devices—particularly for long term knowledge and research work.

[rkaplan]

I think example screenshots would really help to understand the type of data you are working with.

[Lydian91]

Sure, here’s a more detailed breakdown. I have four DEVONthink databases. I’ve landed on these as a compromise between content category, file size (syncing), and unique words. You’ll have to forgive some of my cryptic naming. “Maps” is essentially my word for the “Resources” branch of PARA.

• Artwork (art and media—primarily PDF music scores, tables/markdown, liner notes, supplementary info)
• Feeds (informal “streams”—YouTube, etc.)
• Products (documentation and product information)
• Publications (formal/structured works)

The root level groups in each DEVONthink database each have a corresponding “subcollection” (nested databases) in Collections. Records within a subcollection often point to a DEVONthink subgroup below these root level items. The group URL field contains the UID link to the Collections record. As you’ll see in the “Applications” example below, everything gets a record in Collections, but it only gets a parallel group in DEVONthink if there are files that need storing (in this case, just Carbon Copy Cloner).

Collections also has some exclusive databases (i.e. no parallel DEVONthink groups). Things like People, Groups (bands, companies, organizations), Topics, etc. These are relational fields that help to aggregate resources and build meaningful connections. This way I can browse by people or topics and quickly see all relevant resources (which are often spread across multiple DEVONthink databases). A single person could be associated with items located in multiple places.

I’ve basically tried to atomize these data types into their most basic forms (articles, books, people, topics, etc.) so I can dynamically patch them like a switchboard operator.

[rkaplan]

Have you considered TapForms instead of Collections?

I say this because it has a URL scheme so you could create a link to each item and save it as metadata in the corresponding Devonthink record.

Then you could create a Devonthink item link to each item and save it in the corresponding TapForms record.

That way you could use whatever features you want of each application but have a durable method to go back and forth between these apps and view the data for the same record.

[Lydian91]

Yes, I tested Tap Forms thoroughly, and it works great in tandem with DEVONthink. Collections is equally capable to Tap Forms in terms of URL schemes, and I rely on them extensively. Collections and DEVONthink URLs work great together on desktop and mobile.

Tap Forms is quite similar to DEVONthink in the sense that it’s a desktop-class application with a mobile app. Collections is more like Things or Bear where there is a stronger emphasis on feature parity across desktop and mobile. I felt that this philosophy and touch-oriented design made it a better “front end” navigation layer.

But I deliberated for a while between Collections and Tap Forms, ultimately deciding that I wanted to keep the complexity and depth contained to DEVONthink, because that’s where I really needed it. Collections allows for pretty easy CSV export, so I do have an escape hatch if I change my mind down the road.

[troejgaard]

@Lydian91

Collections has comprehensive Shortcuts integration as does DEVONthink to Go. DEVONthink is highly scriptable on the desktop but lacks Shortcuts support, so any automation I build for mobile won’t carry over to the desktop—something to figure out.

Given that, I’m curious about automation strategies for these apps. Capture is a big problem area. Items can enter the system from either app, so I need a way for example to add something to DEVONthink and have a parallel Collections record created, with UID links automatically populated in both places.

It’s true that DEVONthink on the mac has no direct support for Shortcuts. But AppleScript itself (and its cousin JXA, JavaScript for Automation) actually has decent integration with Shortcuts.

On macOS you can run AppleScript and JXA Scripts directly inside a shortcut. But you can also run shortcuts from inside a script. You can even pass input to the shortcut and pass output back to the script.

Here is a simple example to demonstrate. The shortcut just shows an alert with the input and returns a line of text.

I run it from an AppleScript:

With that in mind, you could build your shortcuts in a modular fashion. I think it should be possible to use a single shortcut that creates a Collections record with the given input and returns a collections:// link. On macOS you can then run that shortcut from a script, and on iOS you can run it from another shortcut using DTTG actions.

[BLUEFROG]

I covered how to work with Automator and Shortcuts in the Automation > Shortcuts and Automator section of the built-in Help and manual…

[troejgaard]

I know, but all your examples are focused on file paths. And you only mention the Run AppleScript action, not running shortcuts from within an AppleScript. I think the second option could be easier to work with here, and I don’t know how many are aware of it. It was just a little supplement to what you cover in the manual

[Lydian91]

Cool, this will definitely test me, but I’m glad to have a clearer sense of what the solution will involve. Thanks!