V8-archive: Rely on static files for core Directus rows

[rijkvanzanten]

Currently, we rely on rows in the database for Directus system configuration. These rows are not user editable and are required for the platform to work correctly. While it's great that everything in the API pulls from the same source of truth, updating these rows is very annoying to do, as it requires us to write database migrations in two places (on install / on update) that update the users database just to change the core Directus information.

I'm proposing we move the core data out of the database and into static files (probably JSON) that we can easily modify when needed.

This should take a lot of duplicate information out of the databases, and makes sure that the DB only holds information that's specific to the user's project.

Changes that are needed to make this happen:

• Whenever the API is trying to read something from a directus_* collection, it should read the corresponding records from the JSON file as well

Open questions

• What happens if a row exists in both places? Does the one in the database overwrite the one in the JSON file? Do they get merged?
• Where do we put system rows that are required yet still user editable? Like the admin / public role.

Examples

/src/system-data/
├── directus_fields.json
├── directus_relations.json
└── directus_roles.json

directus_fields.json

[
  {
    "id": 842,
    "collection": "directus_users",
    "field": "id",
    "type": "integer",
    "interface": "primary-key",
    "options": null,
    "locked": 1,
    "validation": null,
    "required": 1,
    "readonly": 0,
    "hidden_detail": 1,
    "hidden_browse": 0,
    "sort": 1,
    "width": null,
    "group": null,
    "note": null,
    "translation": null
  },
  {
    "id": 843,
    "collection": "directus_users",
    "field": "status",
    "type": "status",
    "interface": "status",
    "options": "{\"status_mapping\":{\"draft\":{\"name\":\"Draft\",\"text_color\":\"white\",\"background_color\":\"light-gray\",\"listing_subdued\":false,\"listing_badge\":true,\"soft_delete\":false},\"invited\":{\"name\":\"Invited\",\"text_color\":\"white\",\"background_color\":\"light-gray\",\"listing_subdued\":false,\"listing_badge\":true,\"soft_delete\":false},\"active\":{\"name\":\"Active\",\"text_color\":\"white\",\"background_color\":\"success\",\"listing_subdued\":false,\"listing_badge\":false,\"soft_delete\":false},\"suspended\":{\"name\":\"Suspended\",\"text_color\":\"white\",\"background_color\":\"light-gray\",\"listing_subdued\":false,\"listing_badge\":true,\"soft_delete\":false},\"deleted\":{\"name\":\"Deleted\",\"text_color\":\"white\",\"background_color\":\"danger\",\"listing_subdued\":false,\"listing_badge\":true,\"soft_delete\":true}}}",
    "locked": 1,
    "validation": null,
    "required": 1,
    "readonly": 0,
    "hidden_detail": 0,
    "hidden_browse": 0,
    "sort": 2,
    "width": null,
    "group": null,
    "note": null,
    "translation": null
  },
  {
    "id": 844,
    "collection": "directus_users",
    "field": "first_name",
    "type": "string",
    "interface": "text-input",
    "options": "{\"iconRight\":\"account_circle\"}",
    "locked": 1,
    "validation": null,
    "required": 1,
    "readonly": 0,
    "hidden_detail": 0,
    "hidden_browse": 0,
    "sort": 3,
    "width": "half",
    "group": null,
    "note": null,
    "translation": null
  },
  {
    "id": 845,
    "collection": "directus_users",
    "field": "last_name",
    "type": "string",
    "interface": "text-input",
    "options": "{\"iconRight\":\"account_circle\"}",
    "locked": 1,
    "validation": null,
    "required": 1,
    "readonly": 0,
    "hidden_detail": 0,
    "hidden_browse": 0,
    "sort": 4,
    "width": "half",
    "group": null,
    "note": null,
    "translation": null
  }
]

[bjgajjar]

@rijkvanzanten - Technically whatever you explained is achievable. There are some points which I would like to point out.

• With this flow; some data will be in DB and some data will be in files[Core data in files and Dynamic data will be in DB]. So every time the server will try to fetch the data from DB as well as file. It'll increase the execution time.
• Almost every request will require the core data. So basically the whole APP may face the performance issue.

What happens if a row exists in both places? Does the one in the database overwrite the one in the JSON file? Do they get merged?

If we are going with the approach where the core data will be stored in the file; we should remove the data from DB. And if in case when the user adds it manually in DB; we can add a condition where the core data[file] will overwrite with DB.

Where do we put system rows that are required yet still user editable? Like the admin / public role.

We can leave them in DB itself as it's editable.

[rijkvanzanten]

Yeah, I don't think this is going to be the way to go. Good to have it considered thought 🙂

We should figure out if there's a way where we can simplify the core data that's present. Writing migrations and updating the seeder every time we want to update the order of fields in settings for example is way too annoying to maintain. Maybe we can come up with some sort of way where it automatically updates the data based on a given data set on migration

[bjgajjar]

I'm proposing we move the core data out of the database and into static files (probably JSON) that we can easily modify when needed.

I think we should put the core data as it is in DB. As there'll be a drastic change as well as in the backend everywhere there's a condition for the columns of table. If we're going with this approach then it'll be from file and we'll not able to implement the where conditions too.

---

updating these rows is very annoying to do, as it requires us to write database migrations in two places

This is our main concern. So let me suggest a way where we can get rid from implement the changes in both files.

• We should declare another folder with install and upgrades named system-data - which contain all the JSON files which you have mentioned.
• If you check the current migrations - it already has an array. We can transfer them to this new folder.
• Every time the data will be inside the system-data folder and migrations file will read those and implement the expected[insert/update] iterations.

Example

/migrations/system-data/
├── directus_fields.json
├── directus_relations.json
└── directus_roles.json

directus_fields.json

[
 "id" : {
    "collection": "directus_users",
    "field": "id",
    "type": "integer",
    "interface": "primary-key",
    "options": null,
    "locked": 1,
    "validation": null,
    "required": 1,
    "readonly": 0,
    "hidden_detail": 1,
    "hidden_browse": 0,
    "sort": 1,
    "width": null,
    "group": null,
    "note": null,
    "translation": null
  },
  "first_name" : {
    "collection": "directus_users",
    "field": "first_name",
    "type": "string",
    "interface": "text-input",
    "options": "{\"iconRight\":\"account_circle\"}",
    "locked": 1,
    "validation": null,
    "required": 1,
    "readonly": 0,
    "hidden_detail": 0,
    "hidden_browse": 0,
    "sort": 3,
    "width": "half",
    "group": null,
    "note": null,
    "translation": null
  }
]

If you have noticed then; I introduced a key of json - which will use for upgrades. Migratons of install and upgrades will read the json file. install migrations will execute whole file but upgrades will read particular key from json file and update the data in DB.

[WoLfulus]

This needs a lot of discussion IMHO. I'm still digesting the information since Rijk asked my opinion over slack, but I'm still unsure what to think as I'm still wrapping my head over the current codebase

[rijkvanzanten]

I like where that's heading @bjgajjar. Lets keep it boiling in the back of our brains for the time being.