Matkalkyl Databas

The database is vertical in the sense that new features do not expand the number of specific tables, but add tables within a generic frame of table_[PluginID]a and table_[PluginID]b. The a/b division serves the purpose of optimizing table structure depending the type of data the plugin wants to store. Type A is data selected for by time ranges, while type B is for data selected for using other criteria such as group identification (sub_type). The concept of this database requires the data to be divided in this way.

The A/B tabes stores the user's data. The table users stores minimal amount of metadata about the user.

The server does not know who owns what row in the A/B tables, and uses a hash bucket/rolling window to delete inactive rows (say rows being several years old where we can know the user has not been active within a frame of the past x years).

The database is optimized for storing encrypted data.

There are more to say about design choices, but that will be written here later.

Permanonce is for persistent connections so client can restart browser and not need to enter password again. Permanonce is also to enable write protection without directly assigning a user with a particular row of data in the a/b tables.

The metavault functions in a particular way, explained later.

> show tables;
+----------------+
| Tables_in_mkdb |
+----------------+
| table_1a       |
| table_1b       |
| users          |
+----------------+

Users

> describe users;
+-------------------+--------------+------+-----+---------------------+-------+
| Field             | Type         | Null | Key | Default             | Extra |
+-------------------+--------------+------+-----+---------------------+-------+
| metavault_uuid    | uuid         | NO   | PRI | NULL                |       |
| username          | varchar(128) | YES  | UNI | NULL                |       |
| permanonce_uuid   | uuid         | NO   | UNI | NULL                |       |
| permanonce_pubkey | text         | NO   |     | NULL                |       |
| metavault_blob    | blob         | YES  |     | NULL                |       |
| created_at        | datetime     | YES  |     | current_timestamp() |       |
+-------------------+--------------+------+-----+---------------------+-------+

table_[num]a

> describe table_1a;
+--------------+-------------+------+-----+---------------------+-------------------------------+
| Field        | Type        | Null | Key | Default             | Extra                         |
+--------------+-------------+------+-----+---------------------+-------------------------------+
| row_uuid     | uuid        | NO   | PRI | NULL                |                               |
| user_id      | uuid        | NO   | MUL | NULL                |                               |
| data_subtype | smallint(6) | YES  |     | 0                   |                               |
| data_state   | tinyint(4)  | YES  |     | 1                   |                               |
| data         | blob        | NO   |     | NULL                |                               |
| created_at   | datetime    | YES  |     | current_timestamp() |                               |
| modified_at  | datetime    | YES  |     | current_timestamp() | on update current_timestamp() |
+--------------+-------------+------+-----+---------------------+-------------------------------+

table_[num]b

> describe table_1a;
+--------------+-------------+------+-----+---------------------+-------------------------------+
| Field        | Type        | Null | Key | Default             | Extra                         |
+--------------+-------------+------+-----+---------------------+-------------------------------+
| row_uuid     | uuid        | NO   | PRI | NULL                |                               |
| user_id      | uuid        | NO   | MUL | NULL                |                               |
| data_subtype | smallint(6) | YES  |     | 0                   |                               |
| data_state   | tinyint(4)  | YES  |     | 1                   |                               |
| data         | blob        | NO   |     | NULL                |                               |
| created_at   | datetime    | YES  |     | current_timestamp() |                               |
| modified_at  | datetime    | YES  |     | current_timestamp() | on update current_timestamp() |
+--------------+-------------+------+-----+---------------------+-------------------------------+