User data and the database schema

So I’m trying to understand the Nextcloud database schema a bit, starting with users/accounts and their profile information. I poked around a bit in the database of a Nextcloud instance which has some tens of manually added users and over a thousand provisioned users, so there is some test data to look at.

  • Why is there a users table (storing display name and password) and an accounts table (storing a JSON representation of user profile data), instead of just one table for a user and their profile data?

  • Is there a conceptual and practical difference between users and accounts in Nextcloud? I do see that the SAML app uses its own tables (user_saml_*) so I guess it has nothing to do with that.

  • Why is there the aforementioned accounts table with JSON data and also an accounts_data table with more or less the same data but not in JSON but individual rows? Seems redundant.

  • The preferences table is a bit unclear to me - in it I see e.g. a row with appid=settings, configkey=email and configvalue=<my e-mail address>. Since my e-mail address is already in the accounts and accounts_data tables, why is it in here too? What’s this table for?

  • There’s also a properties table, which in my instance actually only has one single row, for one user, containing VCARD data. What’s this table for?

  • If I need to store additional information for a user, e.g. some key-value data where the value might be any simple data type or more complex one needing JSON, where should I store this? I’m not talking about data that the user change themselves, but other data I need to record for the user at hand.

  • Finally, is there anywhere that one can read about the tables in the database schema and what their purpose and rationale is? I found very very little, nothing useful or substantial, about it in the developer manual.

Hello.
I cannot answer all your questions, but I try to give you some advice as far as I can get you (without digging my heels too much into the code myself).

That table holds system and per-app configuration. So, e.g. the IConfig::getAppParam and IConfig::getUserParam methods to obtain data from that table. Setting is possible as well. There should be a user column as well if I am not completely mislead.

Could this be the data the users are able to set in their personal settings? Just a wild guess.

I am not sure what the different notions are here. I guess one table consists of all valid accounts and the other just the accounts directly generated (in contrast to SSO and/or LDAP). Or they are managed by different apps.

Well, that part is really a good question and cannot be answered trivially.

  • If you want to to poke around in the server/core apps, it would be very hand to have some documentation there. I guess there is distributed knowledge over the leads of the various projects but no written documentation so far. → TODO
  • If you want to understand how the core is working internally, see above.
  • When writing a custom app, you should not work on the tables directly. The structure might change and should be considered an implementation detail. Use the API instead.
  • Many of the tables can be accessed via occ commands in case you need to fix some things.

The dev manual is significantly short in some ways and here is an example (needs advanced and architecture knowedge). If you find anything useful and are comfortable in writing this down, feel free to open PRs to get that into the docs. I would however suggest to consolidate that with the doc team to avoid redundant work.

Christian

That’s quite interesting considering there is another table named appconfig in which app specific configuration is saved. Based on what you say there are then at least two places where app configurations are stored, appconfig and preferences.

I figured this one out - if a user goes to edit their profile and on the Groupware section save the Availability settings, they are saved as a VCARD in this table (properties).

There’s no difference in number of lines in the users and accounts tables - I have the same number in both of them.

Yeah, I will use the API whenever possible. Considering there are custom tables (e.g. by the SAML app) I do however presume that when needed one might have to resort to using the DB abstraction layer directly (after all, that’s what the migrations are for).


All in all, most or almost all of the things I wrote above are still a mystery. If any core developer can clarify the things I asked about, that would be great :slight_smile: At glance much of it seems terribly distributed and redundant.

Well no, that is not entirely true. You should not use foreign tables directly. Use always the API. That way any implementation change will be handled by the core code.

For example, you could register custom user manager as am app. Then you would manage these users and provide an API to access these.

That was the reason i asked for your motivation. If you want to understand the core, this is more in detail and closer to the database. If you want to know how the app interface works and how you can register various functionality, you have to look at the apps description and interfaces.

However, i confirm that a basic documentation is the architecture and principles might help contributing to the server.

Christian

Now you made me curious. I checked on the source code.

The main configuration is located in this file.

You can see that the user configuration users the table called preferences.
Theapp config is delegated to another class that uses theapp config table.

So, you are right these seeing are stored differently and not in a redundant way.

I hope this makes it clear.