In the last couple of posts we went over the concept, project design and basic architecture, now it's time to look at the schema for the data.
Relations
As you might remember our CMS must work with multiple databases and work as simple as possible. For that to happen we need a 'source of truth' for the way the data will store, this is the schema. Some databases are schemeless and data can be added in any way, but this will mean data becomes a mess as the system is developed as there are no rules for how some data is related to others data, for example if a user has a role we could:
- Store the role with the user
- Store the role on it's own and store the id of the role with the user
It is simple to store the role with the user then when you get a user, you get their role. However, if you want to know all the roles on the system, then you have to get all the users and check their roles, then remove the duplicates. If there are 3 roles on the system and 2 Million users, this is an expensive (both in time and money) check.
If we store the roles in a separate place from the users and store the id of the role with the user, we can get our users and their roles because we know the relation or we can just get the roles by getting all roles. Both of these are much easier and less expensive.
By storing the roles separately from the user we can easily update the role so we can easily rename the role (mistakes happen all the time and `admin` is not the same as `Admin`) or delete something if it's no longer used. These relations are all part of the normalisation steps and getting this correct at the beginning is a necessity.
Most databases have automated blockers in place for relationships, like deleting something that is relied on elsewhere in the data, so if we delete a role that is in use it will fail with an error keeping the system running correctly even when the user makes an error.
Validation
Our schema is a list of all these relations and the IDs and types of data all helping keep the size of the storage low, while adding validation. The schema has lots of validation for things like the type so we don't save "two" into something that needs a number or a date. This is the type of data like number, string or date.
We also check for 'nullable' so we know if we can leave fields blank or if we must add something. We can add much more to this validation as we go for example we can check for the size of a field, so we don't add too many characters into a field. We can then use this schema for any forms to check this if we need to.
Code
Our schema could have been saved as a simple json file, but we decided to save it as a JavaScript file so we can add more to the file later like permissions and key for the API route vs model name and anything else we need for data. This file is the source of truth for the data and will contain everything for the data, but because of Separation of Concerns (SoC) we will separate this files 'parts' to multiple files later. For now, it has the schema only so we don't need to think of SoC just yet. As of today the schema looks like:
const schema = {
user: {
primaryKey: { name: 'id', type: 'string', default: 'cuid' },
name: { type: 'string', required: true },
email: { type: 'string', isUnique: true, required: true },
emailVerified: { type: 'date', isNullable: true },
image: { type: 'string', isNullable: true },
passwordHash: { type: 'string', isNullable: true },
passwordResetToken: { type: 'string', isNullable: true },
passwordResetTokenExpiry: { type: 'date', isNullable: true },
deletedAt: { type: 'date', isNullable: true },
createdAt: { type: 'date', default: 'now()' },
updatedAt: { type: 'date', default: 'now()' },
accounts: { relation: { type: 'account', isList: true } },
sessions: { relation: { type: 'session', isList: true } },
roles: { relation: { type: 'role', isList: true, name: 'user_roles' } },
},
account: {
primaryKey: { name: 'id', type: 'string', default: 'cuid' },
type: { type: 'string' },
provider: { type: 'string' },
providerAccountId: { type: 'string' },
refresh_token: { type: 'string', isNullable: true },
access_token: { type: 'string', isNullable: true },
expires_at: { type: 'int', isNullable: true },
token_type: { type: 'string', isNullable: true },
scope: { type: 'string', isNullable: true },
id_token: { type: 'string', isNullable: true },
session_state: { type: 'string', isNullable: true },
userId: { type: 'string' },
user: { relation: { type: 'user' } },
unique: { fields: ['provider', 'providerAccountId'] }
},
session: {
primaryKey: { name: 'id', type: 'string', default: 'cuid' },
sessionToken: { type: 'string', isUnique: true },
expires: { type: 'date' },
userId: { type: 'string' },
user: { relation: { type: 'user' } },
},
role: {
primaryKey: { name: 'id', type: 'string', default: 'cuid' },
name: { type: 'string', isUnique: true },
description: { type: 'string', isNullable: true },
deletedAt: { type: 'date', isNullable: true },
createdAt: { type: 'date', default: 'now()' },
updatedAt: { type: 'date', default: 'now()' },
permissions: { relation: { type: 'permission', isList: true, name: 'role_permissions' } },
users: { relation: { type: 'user', isList: true, name: 'user_roles' } },
},
permission: {
primaryKey: { name: 'id', type: 'string', default: 'cuid' },
name: { type: 'string', isUnique: true },
description: { type: 'string', isNullable: true },
deletedAt: { type: 'date', isNullable: true },
createdAt: { type: 'date', default: 'now()' },
updatedAt: { type: 'date', default: 'now()' },
roles: { relation: { type: 'role', isList: true, name: 'role_permissions' } },
},
theme: {
primaryKey: { name: 'key', type: 'string', isUnique: true },
value: { type: 'string' },
deletedAt: { type: 'date', isNullable: true },
createdAt: { type: 'date', default: 'now()' },
updatedAt: { type: 'date', default: 'now()' },
},
setting: {
primaryKey: { name: 'key', type: 'string', isUnique: true },
value: { type: 'string' },
deletedAt: { type: 'date', isNullable: true },
createdAt: { type: 'date', default: 'now()' },
updatedAt: { type: 'date', default: 'now()' },
},
};The first thing to notice is the schema tables/collections (different tech has different names for the same thing) are the keys for the objects and the fields are key/value pairs (except primaryKey and the relationships).
The second thing to notice is there is no mention of content in this CMS schema. The reason for this is the content schema is being added later, we will get the full admin system working before we add content.
You will also notice we we have camelCase and snake_case in our schema, we will make this all snake_case later.
Parsing the schema
Our schema will be used for all data, but not everything can read it in this way, for example, we are using Prisma ORM and that has it's own schema 'type' so we have to take this one and create another Prisma can read. Usually, this will be done at build time, but our rules are 'no build' as this CMS must work for any user without a programming background, so, we will package the Prisma schema with the system, not expect it to be built. If we are developing we will rebuild at setup so a developer can add to the schema without worrying, and a user can simply install.
As we are developing, we are also running this system with Firebase so it must work with their datastore called Firestore. This means we have to also bundle their API with the system and make sure it works with that too. As Firestore is schemeless we will not create a separate schema, but we will use our schema to update or create data.
Aggregation layer
The last part of all this is the aggregation layer. This will allow the user to simply add an environment variable with their chosen database type from:
From that information, the aggregation layer will identify either Prisma or Firestore and use the correct 'repository' and format the data correctly. For example, with most databases we can 'search' for something like 'Huyton' and we would see 'Huyton Web Services', but Firebase does not have this search functionality, so when we save the data we have to manually save keywords that can be searched with the data. When searching we check these words. I won't go into this too much here, but it means we have 2 completely different read and write methods for the 2 repositories. Later, we may add more repositories (e.g. Vercel) which might have different ways of reading or writing.
Finishing up
This schema is far from complete, but with the aggregation layer and the 2 repositories we have a system that works with multiple databases in development and production.
Next time we will look at the Admin UI and the tech we have chosen.
As always, any questions or issues from this post leave a comment or contact us and if you need any web work contact us for a quote.