Schema

Each file in `database/Model/` describes one table. The file sets the columns, the relations, and which roles may perform which operations.

Name and table #

"name": "brand",     // model name: singular, lowercase
"table": "brands"    // SQL table name: plural

name becomes the class name (Brand), the controller name (IsuperBrandController), the TypeScript interface and the URL segment (/api/isuper/brand).

Default columns #

Every table gets id, created_at and updated_at unless you override default:

"default": ["id", "updated_at"]   // e.g. a pivot table without created_at

updated_at also drives delta sync. Every generated all endpoint accepts ?latest=<timestamp> and returns only the rows that changed after it. The Angular and SolidJS services use this for incremental refreshes.

Columns: data #

"data": [
  { "name": "name",   "mysql_data": "varchar(255)",  "datatype": "string" },
  { "name": "email",  "mysql_data": "varchar(255) UNIQUE", "datatype": "string" },
  { "name": "note",   "mysql_data": "longtext",      "datatype": "string", "default": "NULL" },
  { "name": "status", "mysql_data": "varchar(20)",   "datatype": "string", "sql_attribute": " NOT NULL DEFAULT 'draft'" },
  { "name": "secret", "mysql_data": "varchar(255)",  "datatype": "string", "fillable": "false" }
]

Enable flag #

"enable": 1

Adding enable with any value creates enable TINYINT(1) DEFAULT 1. The runtimes use it for toggling (Model::toggle), and admin UIs show it as a switch.

Extras: additional #

"additional": ["slug", "seo", "delete"]

Unique keys #

"unique": ["email", ["book_id", "invoice_number"]]

Each entry becomes UNIQUE KEY <name>_<cols>_unique. A nested list creates a composite key.

Relations #

List the models this table belongs to. Use relations; the older key relation works the same way.

"relations": [
  "user",                                     // user_id → users.id  (NOT NULL)
  { "name": "photo", "default": "NULL" },     // photo_id, nullable
  { "name": "user", "alias": "owner_id" }     // owner_id → users.id, relation key "owner"
]

For a foreign key to a table under a different column name, use the explicit keyed form:

"relations": {
  "buyer": { "name": "buyer_id", "table": "users", "key": "id" }
}

For every relation, the generator:

• adds a <rel>_id bigint UNSIGNED column and a <model>_<rel>_id_foreign foreign key;
• registers the relation on the model, so ->with('user') / .with('user') eager-loads it;
• adds the reverse relation to the target model automatically, so users gets brand.

If a relation points to a table that doesn’t exist, a warning is printed and the relation is skipped.

CRUD and roles #

crud maps a role namespace to the operations that role may perform:

"crud": {
  "isuper":  ["c", "r", "u", "d", "a", "p", "w"],
  "islogin": ["c", "r", "u", "a", "w"],
  "ipublic": ["r", "a"],
  "roles": {
    "executive": ["r", "a", "u"]
  }
}

The custom role names are inserted into the roles table for you.

The HTTP verb for each letter depends on the runtime. See PHP and Deno.

Which rows: owner and under #

A list of letters says what a namespace can do, not which rows. With a plain list, islogin reads and writes every row in the table, exactly like isuper. To limit rows, write the entry as an object: can holds the letters, plus owner or under.

// book.json: a user owns their books; an executive is assigned books
"crud": {
  "isuper":  ["c", "r", "u", "d", "a", "p", "w"],
  "islogin": { "can": ["c", "r", "u", "d", "a", "w"], "owner": "user_id" },
  "roles": {
    "executive": { "can": ["r", "a"], "owner": "executive_id" }
  }
}

// client.json: a client lives inside a book
"crud": {
  "isuper":  ["c", "r", "u", "d", "a", "p", "w"],
  "islogin": { "can": ["c", "r", "u", "d", "a", "p", "w"], "under": "book" },
  "roles": {
    "executive": { "can": ["r", "a", "w"], "under": "book" }
  }
}

Where each value comes from, for client.json "islogin": { "under": "book" }:

• book and book_id: from under. The parent model is book; the URL part and the column are book_id (the same <name>_id rule as relations).
• user_id: from book.json, its islogin entry’s owner. A role reads the parent’s entry for the same role, so the executive route checks executive_id and the user route checks user_id. One table can have a different owner for each namespace.
• Your login id: from the session at request time.

On every request under a book, the generated controller first runs one query, books WHERE id = :book_id AND user_id = <you>. No row means 404 and nothing else runs. Then it filters by book_id, and on create, update and upsert it sets book_id from the URL, so the request body cannot pick or change the book.

Generation stops with a message when:

• the parent has no owner for that namespace (client.json "islogin" says "under": "book", but book.json has no "owner" for "islogin".);
• the parent schema does not exist;
• public / ipublic uses owner or under (there is no logged-in user).

owner is one column, so it means one person per row per namespace. Several people per row (for example a manager assigned to many clients) needs a link table and is not supported yet.

To serve a model at a different URL segment, add path: { "can": ["c", "d", "a"], "under": "book", "path": "asset" } serves book_asset at /islogin/book/:book_id/asset.

Public access uses the key public. The Deno generator does not read ipublic.

Photo models: type #

"type": { "name": "photo", "version": { "thumb": { "width": 300, "quality": 80 } } }

With this set, the PHP generator emits an upload controller instead of a plain CRUD one. It stores the original file and creates a webp version for each entry in version.

Full example #

This is invoice.json from the INTAX billing app:

{
  "name": "invoice",
  "table": "invoices",
  "crud": {
    "islogin": ["c", "r", "u", "d", "a", "w"],
    "isuper":  ["c", "r", "u", "a", "w"]
  },
  "data": [
    { "name": "invoice_number", "mysql_data": "varchar(255)",  "datatype": "string" },
    { "name": "amount",         "mysql_data": "decimal(12,2)", "datatype": "number" },
    { "name": "status",         "mysql_data": "varchar(255)",  "datatype": "string", "sql_attribute": " NOT NULL DEFAULT 'draft'" },
    { "name": "gst_rate",       "mysql_data": "float(8,2)",    "datatype": "number", "sql_attribute": " NOT NULL DEFAULT 18" },
    { "name": "due_date",       "mysql_data": "varchar(50)",   "datatype": "string" },
    { "name": "notes",          "mysql_data": "varchar(255)",  "datatype": "string", "default": "NULL" }
  ],
  "relation": ["book", "user", { "name": "client", "default": "NULL" }]
}

Legacy format #

Projects from 2022 (the, thesolidmarket) used a flat "crud": ["c","r","u","d"] array with a separate "roles": {"read": [...], "write": [...]} block, and kept the schemas in App/Karl/setup/model/. The current generator no longer reads that format. Convert those files to the per-role crud object shown above.