Cordova Web App using PouchDB / CouchDB Schema Design

Question

I'd like to use PouchDB with CouchDB because of the great syncing and replication features for my web app but have a difficult time finding the best way to model my data for what I need to do.

The app lets a user create events in a calendar which can be shared only with users they choose. The users that are associated to that event can add comments and make changes to that event.

1) So far from what I understand, each user should have their own database to avoid accessing other user data if all stored in one db. If this is the case, how would one update or share a documents data from different databases?

2) Should the user login be managed on another server using an sql database with an API?

Any guidance would be greatly appreciated!

Danio

Beeck

Answer 1

If I understand your app correctly, you have docs that look something like this:

{
  "_id": "event-{timestamp/name/something}",
  "organizer": "alice",
  "attendees": [
    "alice", "bob", "charlie"
  ]
}

If that's roughly right, then I'd propose the following architecture to make it work with PouchDB & CouchDB's replication.

The goal here is to "cut with the grain" of CouchDB's replication protocol, compartmentalize permissions into containers (database-per-user and database-per-share/relationship), and provide a latent-friendly sharing/distribution model...because the cloud goes away sometimes. ;)

In the flowchart below, you can see Alice's "device" (or app, etc) on the left. Alice keeps events in a private-user-space database. Any event document (like the one above) with attendees on it, gets copied (possibly via a background, filtered replication process) to share databases based on the user identifiers used in the attendees key (or however you model your stuff).

Each of those share-with-* database is continuously (ideally) replicated to a cloud-hosted (most likely) CouchDB (or compatible) database. Bob and Charlie have their apps connected to the same cloud-hosted CouchDB (or compatible) database, and get Alice's event replicated into their share-with-alice database.

The app then provides those events to Bob and Charlie, let's them make their edits, replicates those changes back to the share-with-alice database, and then (eventually; because network) back up to the cloud and back to Alice.

In all of this, the cloud bit is optional. ;) Depending on the deployment these could be three devices on the same network finding each other someway or other and replicating as available.

From what I understand of your app, this should work. :) You mentioned that there are other comment documents also, and they'd need to be modeled in a similar way--or the app would leverage their relationship with the event document to do the Right Thing with the related comments.

I'd be very curious to know if this sounds like a possible path as I'm exploring it myself for a few projects.

Hope something in there's helpful regardless. :)

Dispensation

So I take it everything in the dark gray boxes is PouchDB, and the light gray box is Cloudant/CouchDB? And the share-with-* databases also do an unfiltered replication back to the private-user-space db? If so, this actually seems like a great design. Should work well with PouchDB. :) Although a downside is that you're duplicating a bit of data on the client side, the upside is that replication is super clear and straightforward. – Pulvinate

@Pulvinate correct. The light gray box, however, could be considered optional if/when the dark gray boxes can "see" each other on the network and thereby avoid the "distant" relay. – Dispensation

Thank you both! It does seem like a good solution! It does seem a little overwhelming with the replication part, probably because I am new to CouchDB/PouchDB. One thing I would like to do as well is be able to add users to an event by email even though they have not created an account yet. Once that user signs up, it should automatically show him the events he is attached to. This probably makes things even more complicated :) Which brings me to my next question, how will the share-with-* databases be called? Will it have the users email address appended to it? or a user-id? – Beeck

@Dispensation that design is similar to an app i'm doing. But i have another requirement that is giving me a headache. On my app all users have their own database too. Until here no problem, replication works fine. But on the desktop app i have some users, admins and supervisors, that have to be capable to list all items of all users databases and add new items to them. I'm doing this replicating all databases to a mysql DB but it not seems a good solution for me. Should i open a new question? – Bree

Yeah. That sounds worthy of a new question. :) – Dispensation

@Dispensation #32905096 – Bree

Answer 2

Probably the easiest thing to do in this case would be to assign roles and use the "one database per role" model (as opposed to the "one database per user" model).

You don't need a separate database for this. CouchDB can handle it just fine. However, you will need some server-side logic to manage the user permissions, assign the roles, etc.

If an event can have any combination of users, it sounds like you may have to do something like "one document per role," which translates to "one document per database." That might be unfeasible if you expect users to create a lot of documents, but keep in mind that you can always sync multiple remote databases to a single local database (or vice versa, or any combination you want). Good luck!

Pulvinate

Thank you for your response. It sounds impractical to have one DB per event document. In a relational modal it is very easy to accomplish. One Event table, and one Event User table. but because we deal with local db's, things get more tricky I assume. I am a bit discouraged. I really got excited with PouchDB/CouchDB's features at first, but trying to do what I need to seems a lot harder :( – Beeck

PouchDB isn't a perfect solution to every problem. In particular, the authentication/role modeling in CouchDB is quite weak and everybody has their own workarounds, often implemented on top of CouchDB (see this page for some examples). In general, PouchDB/CouchDB is good at syncing but weak at querying and role management. – Pulvinate

Answer 3

I believe there is always a perfect solution for any kind of use-case in the CouchDB ecosystem, but what really take us away from it is to think relationally complex and personal freedom reduced by security. The best design solutions are also the most simple ones. One most simple solution for the claimed security problem leading to the non-circular database per user design, I mentioned at Couchdb/Pouchdb schema design

Mozarab