Database

Schema JSON

Besides the typed classes, the build can write the models out as plain JSON — a description of every table for something outside the project to read.

Not to be confused with the JSON Schemas in Features — those validate the framework's own config files in the editor. This one describes your database.

Turning it on

It is off unless you name a file for it, through DB_SCHEMA_FILE in the .env:

DB_SCHEMA_FILE = "database"

SchemaJSON then writes database.json at the root of the app on the next build, adding the .json for you. It is a builder like any other, so destroy removes the file again, and leaving the variable empty skips it entirely.

What it returns

One entry per table, sorted by name, covering the framework's own models as well as yours. Each entry opens with the description from the #[Model] attribute, then lists every column and how the table is wired to the others:

{
    "settings": {
        "description": "The application settings, one typed row per variable, grouped in sections.",
        "fields": [
            { "name": "section",      "type": "string", "isPrimary": true },
            { "name": "variable",     "type": "string", "isPrimary": true },
            { "name": "value",        "type": "text" },
            { "name": "variableType", "type": "enum" },
            { "name": "modifiedTime", "type": "date" }
        ],
        "foreigns": []
    }
}

Key	Is
`description`	What the table is for, in a sentence, from the `description` of the #[Model] attribute. Empty when the model does not give one.
`fields`	Every column of the table. Only `name` and `type` are always given — the rest are left out when they hold nothing worth saying.
`foreigns`	Every column that points at another table, as `fromField`, `toTable` and `toField`. Both the relations the model is read together with and the columns it merely points at are given here, since to a reader they are the same edge.

The attribute flags are not written out. The columns they add — status, createdTime, createdUser, modifiedTime, modifiedUser and isDeleted — appear in fields like any other, so a reader sees the table as it really is rather than having to know what hasTimestamps implies. The modifiedTime above is there because Settings is declared with hasTimestamps and canEdit.

A field

Key	Is
`name`	The column as the database has it, which is not always the property name — an `isID` field is written in upper snake case, so `sessionID` is stored as `SESSION_ID`.
`type`	How the value is stored. See the table below, since these do not all match the property types.
`length`	The `length` given to #[Field]. Absent when it was left to the default.
`isPrimary`	Present and `true` when the column is part of the primary key — the `isID` field, and anything else marked `isPrimary`. Absent otherwise.
`isKey`	Present and `true` when the column is indexed — the `isKey` fields, and the status column. Absent otherwise.

A key is only written when it says something, so length, isPrimary and isKey are missing far more often than not. Read them with a default rather than expecting them — field.isKey ?? false — and treat a column of just name and type as the ordinary case.

The types

The property type decides the column type and the #[Field] attribute refines it, so the name in the file is not always the name you wrote in PHP:

Type	Comes from
`number`	An `int` property — not `int` or `integer`.
`float`	A `float` property.
`boolean`	A `bool` property — not `bool`.
`string`	A plain `string` property.
`text` / `longtext`	A `string` property widened with `isText` or `isLongText`. A string column can therefore arrive under any of three names.
`encrypt`	A `string` property marked `isEncrypt`. It is a storage choice rather than a type of its own — the value is still text, held encrypted by the database.
`date`	A `Date` property. Stored as a unix timestamp, so the column is a number despite the name.
`enum`	An enum property, including the generated status enums.
`json` / `array`	A `JSON` or `array` property, stored as encoded text.
`file`	A `File` property, which holds the name of the file rather than its contents.
`none`	A field with no type at all. It should not appear in a built schema.

A whole entry

The log of actions points at both the session it happened in and the credential that did it, so it shows the fields and the edges together:

"log_action": {
    "description": "What each credential did, as a module and an action, tied to its session.",
    "fields": [
        { "name": "ACTION_ID",     "type": "number", "isPrimary": true },
        { "name": "SESSION_ID",    "type": "number", "isKey": true },
        { "name": "CREDENTIAL_ID", "type": "number", "isKey": true },
        { "name": "currentUser",   "type": "number" },
        { "name": "module",        "type": "string" },
        { "name": "action",        "type": "string" },
        { "name": "dataID",        "type": "text" },
        { "name": "createdTime",   "type": "date" }
    ],
    "foreigns": [
        { "fromField": "SESSION_ID",    "toTable": "log_session", "toField": "SESSION_ID" },
        { "fromField": "CREDENTIAL_ID", "toTable": "credential",  "toField": "CREDENTIAL_ID" }
    ]
}

SESSION_ID is there because the model declares a relation to the session, and CREDENTIAL_ID because the field says it belongs to the credential. The framework reads the first as a join and the second as a plain column, but both are the same edge to anything reading the file, so they are given together. createdTime is there because the model is declared with hasTimestamps and canCreate.

Using it

The file is regenerated on every build, so it should be committed only if the thing reading it expects to find it in the repository — otherwise treat it like the rest of the generated code and ignore it. Nothing in the framework reads it back; it exists purely to be consumed from outside:

$schema = JSON::readFile($path, "database.json");

foreach ($schema as $tableName => $table) {
    foreach ($table["foreigns"] as $foreign) {
        // ... draw an edge from $tableName to $foreign["toTable"]
    }
}

Because it is written from the same models the migrations read, it never drifts from the database the app actually builds.

Drawing it with DER

DER reads this file and draws the database as a diagram, with a link for each foreigns entry.

The framework's own tables

Every app inherits the tables the framework declares, for the credentials, the settings, the email and notification queues, and the logs. They are written in the shape above, and read back here from that very file — so what follows is the schema itself, not a copy of it that could fall behind:

Reading schema.json…

← PreviousGenerated Schema Next →DER