Server-side renderApp — the resource system

The server twin of the client's renderApp() / renderGroupedApp(). One controller, one content module, one definition file per resource.

Both sides speak the same wire contract: a record carries data-app, data-id, data-id-field, data-schema, data-row; a button carries data-method. The server sends data + schema, never modal markup. Full attribute reference in The client transport.

A resource reads from one of two sources, chosen by sourceType:

• table (default) — a single table through ResourceTable / TableApp, field-whitelisted.
• query — a hand-written SELECT (usually a JOIN) through ResourceQueryBuilder, for reads a single table can't express.

Writes are single-table either way. See Reading across tables.

SepoEngine/
├── Setup/
│   ├── ServerAppConfig.php      resources grouped by db handle (twin of AppsConfig)
│   └── ServerAppShell.php       lazy AppShell bootstrap + config index
├── App/Resources/
│   ├── ResourceDefinition.php     storage + columns + schema + actions
│   ├── ResourceRegistry.php       key -> definition (via ServerAppConfig)
│   ├── ResourceTable.php          TableApp + count() + distinct() + find()  (table source)
│   ├── ResourceQueryBuilder.php   raw SELECT/JOIN source: WHERE + sort + page  (query source)
│   ├── ResourceQuery.php          page/sort/search/filter, validated
│   ├── OptionSource.php           dynamic `options` spec (query / distinct / closure)
│   ├── ResourceOptions.php        resolves OptionSource -> concrete lists at render
│   ├── AppRenderer.php            renderApp() / renderGroupedApp() -> widgets
│   └── definitions/
│       ├── users.php              query mode — users LEFT JOIN api keys
│       └── api-keys.php           grouped card mode, custom item template
├── App/Controllers/ResourceController.php          index() + api()
└── App/views/content/resource/ResourceContent.php  the only view file needed

Request flow

GET /admin/users
  └─ ResourceController::index('users')
       ├─ ServerAppShell::definition('users')
       │    ├─ ResourceRegistry::get('users')   -> reads ServerAppConfig, loads
       │    │                                      definitions/users.php, injects
       │    │                                      the group's db handle
       │    └─ ensureDb('sp_db_auth')           -> opens THIS group's PDO, only now
       ├─ ResourceQuery::fromRequest(...)       -> page/sort/search, validated
       ├─ fetchList(...)  ── source-dependent ──┐
       │    ├─ 'table'  ResourceTable::forResource(...)   -> TableApp bound to the table
       │    │              ├─ get(baseWhere + search, options) -> this page of rows
       │    │              └─ count(baseWhere + search)        -> real total
       │    └─ 'query'  ResourceQueryBuilder::fetch($query)    -> [rows, total]
       │                   (baseWhere + search compiled onto your SQL, then
       │                    validated ORDER BY + LIMIT/OFFSET, then exclude-stripped)
       ├─ ResourceOptions::resolve($def)        -> any dynamic `options` (query/closure)
       │                                           -> concrete [{value,label}] lists
       └─ render('main.index', ['content' => ['resource'], 'resource' => $def, ...])
            └─ Registry.php -> ResourceContent.php
                 └─ AppRenderer::render($def, $rows, $query, $total)
                      -> widgets  (+ headerActions for the topbar)

Why connections are lazy: AppShell::registerDb() calls new PDO inside the register call. Walking ServerAppConfig at boot would open four connections on every request, including requests that touch no database. ServerAppShell indexes the config at boot and opens a group's connection the first time a resource in that group is resolved. One page view, one connection.

Part 1 — One-time wiring

Do this once. After it, adding a resource never touches these files again.

1.1 App/views/main/Registry.php

$contentModules = [
    'api-keys' => $contentDir . '/api-keys/ApiKeysContent.php',
    'profile'  => $contentDir . '/profile/ProfileContent.php',
    'resource' => $contentDir . '/resource/ResourceContent.php',   // <— add
];

1.2 serviceRegister.php

use SepoEngine\Setup\AppShell;
use SepoEngine\Setup\ServerAppShell;
use SepoEngine\App\Controllers\ResourceController;

// ------------------------------------------------------------------ resources

$router->registerService(
    ServerAppShell::class,
    static function (): ServerAppShell {
        $shell = ServerAppShell::boot(
            require PathHelper::basePath('SepoEngine/Setup/ServerAppConfig.php'),
            new AppShell()
        );

        // The env keys end in _PASS_ENC. Wire the SAME decryptor the rest of
        // the bootstrap uses, or MySQL is handed ciphertext.
        // $shell->setPasswordResolver(static fn(string $raw): string => Crypto::decrypt($raw));

        if (($_ENV['APP_DEBUG'] ?? '') === 'true') {
            foreach ($shell->validate() as $problem) {
                error_log('ServerAppConfig: ' . $problem);
            }
        }

        return $shell;
    }
);

$router->registerService(
    ResourceController::class,
    static fn(ServerAppShell $apps): ResourceController => new ResourceController($apps)
);

1.3 routes.php

Must sit below the explicit /admin/... routes so those keep their own controllers.

$router->group('/admin', function () use ($router) {
    $router->get('/{resource}',      'ResourceController@index');
    $router->post('/{resource}/api', 'ResourceController@api');
}, [
    'middlewares' => ['AuthMiddleware']
]);

If bare {resource} is greedy in your Router, constrain it: {resource:[a-z0-9_-]+}.

1.4 Sidebar (optional)

index.php can stop hand-listing pages:

use SepoEngine\App\Resources\ResourceRegistry;

$items = [];
foreach (ResourceRegistry::keys() as $key) {
    $def = ResourceRegistry::get($key);
    $items[] = ['label' => $def->title, 'href' => $def->url()];
}
$navGroups[] = ['label' => 'Data', 'items' => $items];

Part 2 — Build a server-side app, step by step

Worked example: an Invoices page at /admin/invoices, reading tsp_invoices from sp_db_datamanager.

Step 1 — Know your table

You need three things before writing anything:

• the logical db handle the table lives under (sp_db_datamanager) — the array key in ServerAppConfig, not the physical database name
• the table name (tsp_invoices)
• the primary key column (sp_invoice_uid)

Step 2 — Register the resource

Setup/ServerAppConfig.php, in the group that owns the table:

'sp_db_datamanager' => [
    'env' => [ /* unchanged */ ],
    'resources' => [
        'invoices' => $definitions . '/invoices.php',   // <— add
    ],
],

The array key invoices is the URL segment, so it must match ^[a-z][a-z0-9_-]*$. Keys must be unique across all groups — ServerAppShell throws at boot if two groups claim the same key, because a URL can only resolve to one place.

Step 3 — Write the definition

App/Resources/definitions/invoices.php. Minimum viable:

<?php

declare(strict_types=1);

return [
    'title'   => 'Invoices',
    'table'   => 'tsp_invoices',
    'idField' => 'sp_invoice_uid',

    // TableApp's whitelist. Nothing outside this list can be selected,
    // ordered by, or written — this is the security boundary, not decoration.
    // (Relaxed for 'query' resources — see Reading across tables.)
    'fields' => [
        'sp_invoice_uid',
        'sp_invoice_number',
        'sp_invoice_client',
        'sp_invoice_total',
        'sp_invoice_status',
        'sp_invoice_created_at',
    ],

    'columns' => [
        'sp_invoice_number' => ['label' => 'Number', 'sortable' => true],
        'sp_invoice_client' => ['label' => 'Client',  'sortable' => true],
        'sp_invoice_total'  => ['label' => 'Total',   'sortable' => true],
        'sp_invoice_status' => ['label' => 'Status'],
    ],

    'schema' => [
        'sp_invoice_number' => ['label' => 'Number', 'type' => 'text', 'required' => true],
        'sp_invoice_client' => ['label' => 'Client', 'type' => 'text'],
        'sp_invoice_total'  => ['label' => 'Total',  'type' => 'number'],
        'sp_invoice_status' => ['label' => 'Status', 'type' => 'select',
                                'options' => ['draft' => 'Draft', 'sent' => 'Sent', 'paid' => 'Paid']],
    ],
];

No 'db' key — the group supplies it. No 'sourceType' key — it defaults to 'table'. /admin/invoices now lists, sorts, paginates, creates, edits and deletes.

Note columns vs schema: columns is what you see, schema is what you can write. They are separate on purpose — readable is not automatically writable. A column in columns but not schema is display-only; idField is never client-writable regardless.

Step 4 — Choose a view mode

'mode'        => 'grouped',
'groupBy'     => 'sp_invoice_status',
'groupLabels' => ['draft' => 'Drafts', 'sent' => 'Awaiting payment', 'paid' => 'Paid'],

groupLabels also fixes the section order. Grouping happens on the fetched page — same as the client renderer, which groups whatever it was handed. If a group must always be complete, raise perPage or sort by groupBy first.

mode is independent of sourceType: a query resource can render as a table, cards or grouped just the same.

Step 5 — Add templates

A column template renders one cell. It returns raw HTML and owns its own escaping — same rule as createTable's columnTemplates:

$e = static fn($v): string => htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8');

'sp_invoice_total' => [
    'label'    => 'Total',
    'sortable' => true,
    'template' => static fn($v): string =>
        '<strong>K' . $e(number_format((float)$v, 2)) . '</strong>',
],

An item template renders one whole card (cards/grouped mode):

'item' => static function (array $row, DomBuilder $b, ResourceDefinition $def) use ($e): string {
    return $b->createDiv([
        'data'  => ['field' => 'sp_invoice_number'],
        'style' => ['fontWeight' => '600'],
        'text'  => (string)$row['sp_invoice_number'],
    ]) . $b->createDiv([
        'data' => ['field' => 'sp_invoice_total'],
        'text' => 'K' . number_format((float)$row['sp_invoice_total'], 2),
    ]);
},

Mark value nodes with data-field — the same convention the client renderer uses, so templates read the same on both sides. The card wrapper (data-app / data-id / data-schema / data-row) is added by AppRenderer; the template only renders the inside.

Omit item and a label/value list is generated from columns.

For everything DomBuilder can do inside a template, see DOMBUILDER.md.

Step 6 — Search, filters, scope, actions

'searchable' => ['sp_invoice_number', 'sp_invoice_client'],   // LIKE %term%
'filterable' => ['sp_invoice_status'],                        // exact match dropdown
'baseWhere'  => ['sp_invoice_deleted' => 0],                  // always applied

'defaultOrderBy' => 'sp_invoice_created_at',
'defaultOrder'   => 'DESC',
'perPage'        => 25,

'rowActions' => [
    ['method' => 'update', 'label' => 'Edit'],
    [
        'method'  => 'delete',
        'label'   => 'Void',
        'variant' => 'danger',
        'confirm' => 'Void this invoice?',
        'showIf'  => static fn(array $row): bool => $row['sp_invoice_status'] !== 'paid',
    ],
],

A filterable column populates its dropdown from that column's options in columns. Those options can be a fixed list or resolved from the database at render time — see Dynamic options for filters that offer only values the data actually holds. baseWhere is merged into every read and every write. These all work identically under a query source — with the column-naming caveats in Reading across tables. For per-viewer row ownership (each user sees only records they own or were shared on), see Row-ownership scope.

Step 7 — Decide where writes go

One key:

So a feature can move to a definition without rewriting its backend, then drop the app key when TableApp should take over. With APP_DEBUG=true, ServerAppShell::validate() cross-checks the name against AppsConfig and logs a typo at boot rather than on the first save.

Writes always go single-table — through TableApp against $def->table — including for a query resource, because a join can't be updated.

ResourceController::api() accepts:

{ "method": "get|add|update|delete", "data": { ... }, "id": "..." }

and answers with TableApp's envelope: { "status": "success", "table": ... }, or { "status": "error", "message": "..." } with a 400/403/404 status.

script.js picks the transport off the resource: prefix — see The client transport below.

Step 8 — Restrict what's allowed

'allow'     => ['get', 'update'],   // no create, no delete
'canCreate' => false,               // hide the topbar "New" button

allow gates the methods server-side; canCreate only affects the UI. Set both when a resource is read-mostly. A read-only query resource is just 'allow' => ['get'].

Reading across tables — the query source

ResourceTable (and TableApp under it) is single-table by design: one table, one alias, a field whitelist. When a list needs columns from more than one table — users with their API keys, invoices with a client name — you don't write a new controller, you switch the resource's source from table to query and hand it the SELECT. ResourceController then routes reads through ResourceQueryBuilder instead of ResourceTable.

'sourceType' => 'query',
'table'      => 'tsp_sys_users',        // still needed: the write target
'idField'    => 'sp_user_uid',

'query' => [
    'sql'      => 'SELECT T1.*, T2.sp_api_public_key
                     FROM tsp_sys_users AS T1
                     LEFT JOIN tsp_sys_api_keys AS T2
                       ON T1.sp_user_uid = T2.sp_api_client_uid',
    'countSql' => 'SELECT COUNT(*)
                     FROM tsp_sys_users AS T1
                     LEFT JOIN tsp_sys_api_keys AS T2
                       ON T1.sp_user_uid = T2.sp_api_client_uid',
],

Everything else is unchanged. The builder consumes the same ResourceQuery as the table path, so searchable, filterable, sortable columns and pagination behave exactly as they do in table mode, and columns, schema, mode, item, rowActions are read identically. Switching a resource between sources is a sourceType change, not a rewrite.

What the builder does with your SQL

ResourceQuery's WHERE (search + filters) and baseWhere are compiled to parameterised clauses and appended to your sql; the same clauses are appended to countSql. Then a validated ORDER BY and integer LIMIT / OFFSET are added. Values always travel as bound parameters — only your own SQL and the definition's own clauses are ever inlined.

• countSql is optional. Provide it and it runs verbatim (fast, no window). Omit it and the builder wraps your query — SELECT COUNT(*) FROM (<your sql + where>) AS sp_count_sub — so pagination totals stay correct, just costlier. Provide it for any list of size.
• table + idField are still required if the resource writes. You can't UPDATE a join, so add/update/delete target $def->table through ResourceTable. For a read-only join, set 'allow' => ['get'] and the base table is never actually written.

WHERE operators (search, filters, JSON)

baseWhere and the search/filter map understand a small operator set, so an OR search group can mix plain-text and JSON columns:

The JSON suffix searches inside a JSON column: the bound value is a JSON_SEARCH pattern (so a %term% matches array members partially, a bare value exactly). Non-JSON / NULL / '' are coerced to an empty array first, so the clause never errors — but point it at real JSON columns. SCOPE is the purpose-built shorthand for the common owner-or-shared case; the raw operator is there for one-off JSON matches elsewhere.

The four things to get right

1. exclude is what keeps secrets out of a SELECT *. In table mode the field whitelist never selects a hidden column. A raw SELECT T1.* has no such filter — it pulls sp_user_password straight off disk. ResourceQueryBuilder strips every exclude column from each row before it leaves the server, so list exclude for anything the join can reach:

'exclude' => ['sp_user_password', 'sp_api_secret_key'],

This is the query-mode equivalent of TableApp::setFields($fields, $exclude). Miss it and the secret rides out in the JSON and the data-row attribute.

2. Result-set column names must be unambiguous. columns, searchable, filterable and sort all key on the bare names PDO returns (sp_user_email, not T1.sp_user_email). If both joined tables expose a column of the same name, alias one in the SELECT (T2.sp_status AS api_status) and refer to the alias in columns.

3. baseWhere is written for the read, and qualified. Because the SELECT joins two tables, scope conditions carry the alias — 'T1.sp_user_status' => 1 — so they aren't ambiguous across the join. That's correct for the read.

4. Writes are single-table, and the controller un-qualifies the scope. add/update/delete run through ResourceTable against $def->table, which speaks bare column names. ResourceController::writeWhere() strips the T1. prefix off baseWhere keys for the write, so the same scope still applies — but the column has to exist on the base table. Keep scope columns on the base table (not on a joined-in table), or the write scope can't be enforced.

The field-whitelist trade-off

ResourceDefinition::fromArray()'s load-time check — every columns / schema / searchable / filterable / groupBy key must exist in fields — is relaxed in query mode, because the SELECT, not fields, decides which columns exist. In exchange, ResourceQueryBuilder re-establishes the two runtime guards that matter:

• ORDER BY is validated against the definition's declared columns/fields before it is concatenated. (ResourceQuery already restricts orderby to sortableColumns() upstream, so this is defense-in-depth.)
• exclude is enforced on the way out (point 1 above).

Writes still pass through TableApp's full assertKnownFields whitelist, so mass-assignment protection on the write side is identical to table mode. What you lose in query mode is the load-time typo catch on read columns — so eyeball your columns keys against the SELECT.

Row-ownership scope — the SCOPE key

A plain 'col' => value in baseWhere scopes every row by a fixed condition. Row ownership — each viewer sees only the records they own or were shared on — needs an OR of two columns, resolved against the current user per request. That is the reserved SCOPE key:

// resolve identity from the session at the top of the definition file
$currentUserUid  = (string) ($_SESSION['user_uid'] ?? '');
$currentUserFlag = (string) ($_SESSION['sp_flag'] ?? '');
$isAdmin         = str_contains(strtoupper($currentUserFlag), 'SYS_USER');

'baseWhere' => [
    'T1.sp_user_status' => 1,          // ordinary fixed scope
    'SCOPE' => [
        'uid'         => $currentUserUid,
        'ownerField'  => 'T1.sp_owner_uid',
        'sharedField' => 'T1.sp_shared_with',   // JSON array of uids
        'bypass'      => $isAdmin,               // admins see every row
    ],
],

ResourceQueryBuilder::compileScope() turns that into one parameterised, parenthesised group and ANDs it with the status filter and any search/filters:

... AND (T1.sp_owner_uid = ?
         OR JSON_CONTAINS(IF(JSON_VALID(T1.sp_shared_with), T1.sp_shared_with, '[]'),
                          JSON_QUOTE(?)))

Both ? bind the uid — nothing is inlined — and it applies to the count as well as the page, so pagination stays correct.

Why a reserved key and not an OR. The search box already emits an OR group, and baseWhere is array_merged with the request WHERE — a second OR would clobber it. SCOPE compiles to its own group, so search and ownership coexist: WHERE status=1 AND (owner OR shared) AND (search…).

Fields. Either ownerField or sharedField may be omitted; whichever are present are OR-ed together. sharedField is treated as a JSON array — the IF(JSON_VALID(...)) guard coerces NULL / '' / non-JSON to an empty array, so a malformed row simply doesn't match instead of erroring. Qualify both with the join alias (T1.), same as any other baseWhere key.

Safety posture.

• Bypass — a truthy 'bypass' (e.g. an admin, keyed off sp_flag) emits no clause at all, so every row is in scope. Checked before anything else.
• Fail closed — an empty uid (no identity resolved) yields 1 = 0: the list shows nothing rather than leaking every row. Resolve identity before you rely on it.

On create, set the owner yourself. SCOPE is a read construct — it shapes which rows come back; it does not stamp ownership on insert, and it is not a scope column the create path can inject. Give a new row an owner through defaults so it lands inside its own scope:

'defaults' => [
    'sp_owner_uid'       => static fn(): string => (string) ($_SESSION['user_uid'] ?? ''),
    'sp_user_created_at' => static fn(): string => date('Y-m-d H:i:s'),
],

Without it a created row has no owner, falls outside (owner OR shared), and vanishes from the very list that created it — the classic "silent insert" symptom.

Reuse. Drop the same SCOPE block into any query resource's baseWhere — programs.php, enrollments.php, transcript.php all carry sp_owner_uid / sp_shared_with. Resolve uid + isAdmin once per request (a small helper, or your controller) if you'd rather not repeat the three lines in every definition.

Keep it consistent with the other two layers. SCOPE controls which rows; the resource's permissions control which actions; the sidebar roles control what's visible. Point all three at the same identity field (here sp_flag) so "admin" means the same thing everywhere.

Requirements. JSON_VALID / JSON_CONTAINS / JSON_QUOTE need MySQL 5.7.8+ or MariaDB 10.4.3+. On an older server, replace the shared-with test with a FIND_IN_SET / LIKE variant.

Dynamic options — data-driven selects and filters

A select field or a filterable column normally lists fixed choices:

'options' => ['admin' => 'Administrator', 'member' => 'Member', 'viewer' => 'Viewer'],

That's fine until the choices live in the data — roles in a lookup table, categories that change, statuses you'd rather not hard-code in two places. An options entry can instead be a source resolved from the database at render time:

use SepoEngine\App\Resources\OptionSource;

// any SELECT — alias the value/label columns (defaults: 'value' / 'label')
'options' => OptionSource::query(
    'SELECT sp_cat_key AS value, sp_cat_name AS label
       FROM tsp_sys_user_categories ORDER BY sp_cat_name'
),

// the distinct values a column actually holds (table defaults to the resource's)
'options' => OptionSource::distinct('sp_user_category'),

// anything else — a closure gets the PDO and the definition
'options' => static fn(PDO $pdo, ResourceDefinition $def): array =>
    $pdo->query('SELECT id AS value, name AS label FROM teams')->fetchAll(),

OptionSource::distinct is the "powerful filters" shortcut: the dropdown only ever offers values that exist in the data, so a filter can't present a choice that matches no row.

How it resolves

ResourceController::index() calls ResourceOptions::resolve($def) once, just before rendering. It walks columns and schema, runs each source, and rewrites the options into the concrete [{value,label}] list the rest of the system already consumes — ResourceDefinition::clientSchema() for the form modal, AppRenderer::toolbar() for the filter dropdowns. Nothing downstream knows the choices were dynamic; static array options are left untouched.

Define a source once and reuse it for the column filter and the schema field — the resolver caches by the source's signature, so the same query runs a single time even when it appears in both places:

$roleOptions = OptionSource::query(
    'SELECT sp_cat_key AS value, sp_cat_name AS label FROM tsp_sys_user_categories'
);

'columns' => [
    'sp_user_category' => ['label' => 'Role', 'options' => $roleOptions],
],
'schema' => [
    'sp_user_category' => ['label' => 'Role', 'type' => 'select', 'options' => $roleOptions],
],

Two things to keep in mind

The value must match what's stored. A filter matches column = value, and the form saves value into the column. So a source's value has to equal what the row actually holds — OptionSource::distinct is consistent by construction; a lookup table's value column must carry the same keys stored in the row.

A different database is fine. OptionSource::query($sql, $db) and OptionSource::distinct($col, $table, $db) take an optional connection handle, so options can come from a table in another group. Omit it and the resource's own db is used. The resolver ensureDbs the handle before reading, same lazy rule as everything else.

Failure is soft

The SQL in a source is author-written — it lives in the definition file, never in request input — so it's trusted like the query source's SQL, and distinct()'s identifiers are pattern-checked. If a source errors anyway (missing table, a typo), it's logged and falls back to an empty list: the dropdown renders empty, the page does not break.

The client transport

script.js sends every action to one of two places, chosen purely by whether data-app starts with resource:. Nothing in the JS knows which resources exist — or which source they read from — so switching a resource between backends, or between table and query sources, is a definition change only.

The endpoint is built from window.SepoDeskConfig.baseUrl (written inline by index.php before any module loads), not root-relative — same sub-path reason as the sort links.

Attribute contract

The renderer stamps these; the delegate resolves them by walking outward from the clicked button.

[data-row] is matched on any element, not just <tr>. Cards from AppRenderer are <div data-row>, and the topbar "New" button carries data-row on itself (a blank record from blankRecord()) — closest() matches the element too, which is how the create modal gets seeded.

add vs create

TableApp's method is add; the client's verb has historically been create. intentOf() normalizes add/create/new to one intent, so both spellings open the form modal, and RESOURCE_WIRE_METHOD maps back to add on the wire. Client-app transport still receives data-method verbatim, so existing apps expecting create are unaffected.

Errors

Resource errors carry a top-level message; app errors nest it under table. executeAction reads both. The resource POST also parses the JSON body on a 400/403 rather than treating a non-2xx as a network failure — the useful text is in that body, not the status line.

Note that TableApp deliberately replaces PDO messages with generic ones at the source, so a SQL failure surfaces to the user as "Database error occurred while…" and the real MySQL text only ever reaches error_log.

The query source is just as loud in the log and just as quiet on the page. Every read runs through one runStatement() choke point in ResourceQueryBuilder, which catches both a thrown PDOException and a silent execute() that returns false (so a query failing quietly can't masquerade as "no rows"). Before it re-throws it logs a single diagnostic line:

[ResourceQueryBuilder] resource=users SQLSTATE=42S22 driver=1054: Unknown column 'T1.sp_owner_uid' in 'where clause' | SQL: SELECT … | params: ["<uid>"]

— resource key, SQLSTATE, driver code, message, the flattened SQL and the bound params (scalars only; anything else reduced to its type). It then throws through to ResourceController, which also logs ResourceController::index [key]: … and shows the user the generic "This section could not be loaded." A malformed join, a missing column, or a JSON function your server lacks shows up in that [ResourceQueryBuilder] line — the driver code names it fast (1054 unknown column · 1146 no such table · 1305 function does not exist · 3141 invalid JSON) — never on the page.

Definition reference

Storage — db (injected from the group), sourceType, query, table, alias, fields, exclude, idField, idStrategy, baseWhere, defaults List — mode, groupBy, groupLabels, columns, item, searchable, filterable, defaultOrderBy, defaultOrder, perPage, emptyMessage Write — schema, formTitle, allow, app, rowActions, canCreate, createLabel Routing — basePath (defaults to /admin/<key>)

sourceType is 'table' (default) or 'query'. When it's 'query', query holds ['sql' => ..., 'countSql' => ...] — the SELECT that reads the list and an optional matching COUNT. table + idField are still consumed by the write path. See Reading across tables.

baseWhere accepts, besides plain 'col' => value scope, the operator suffixes in WHERE operators and two reserved keys: an 'OR' => [...] group and a 'SCOPE' => [...] row-ownership block (owner or shared-with, with an admin bypass). See Row-ownership scope.

basePath is app-relative config, not an href. The install may sit under a sub-path (a WordPress plugin at /wordpress/sepodesk), and a root-relative /admin/api-keys in a link resolves against the origin, dropping that prefix. ResourceDefinition::url() runs it through Url::to() — which reads RouterSettings app_url — and every link the renderer emits (sort headers, pagination, the search form's action, Clear) is built from that. Use $def->url() anywhere you need the link yourself.

A column entry: ['label' =>, 'sortable' =>, 'options' =>, 'template' =>] A schema entry: ['label' =>, 'type' =>, 'required' =>, 'options' =>, 'placeholder' =>, 'readonly' =>]

options may be written as ['admin' => 'Administrator'], ['admin', 'member'], or [['value' => 'admin', 'label' => 'Administrator']] — optionList() normalizes all three before they reach the client. That conversion is load-bearing: createSmartInput calls options.map(...), and a PHP associative array JSON-encodes to an object, which has no .map. Sending the map straight through kills the modal with "options.map is not a function". options may also be an OptionSource (a SQL query or distinct column) or a closure, resolved from the database at render time — see Dynamic options. A row action: ['method' =>, 'label' =>, 'variant' =>, 'confirm' =>, 'showIf' =>, 'class' =>]

showIf takes a closure or a declarative condition — ['sp_api_active' => 'active'], ['sp_api_active !=' => 'revoked'], ['col' => ['a','b']], OR/NOT groups. See DOMBUILDER.md.

ResourceDefinition::fromArray() validates at load: every key in columns, schema, searchable, filterable and groupBy must exist in fields, and idField must too. A typo fails loudly at definition-load time instead of as a confusing SQL error three layers down. In query mode this membership check is relaxed — the SELECT defines the available columns, not fields — so a column typo surfaces at query time, not load time; check columns keys against your SQL by eye. idField is still required.

What happens on create

writablePayload() strips any client-supplied id, so a new record's identity is decided server-side and nowhere else. Three layers are then applied, in this order:

1. defaults — soft. A value the user actually filled in wins over these.
2. the client payload — narrowed to schema keys.
3. scope columns from baseWhere — hard, applied last, so a create can't place the row outside the resource's own scope. Without this, a new record is written outside baseWhere and vanishes from the very list that created it, which looks exactly like a silently failed insert.

Only plain 'col' => value entries are injected here. The structured 'OR' and 'SCOPE' keys are read constructs — SCOPE in particular does not stamp ownership on insert, so give a scoped resource its owner through defaults (see Row-ownership scope).

Then the id, per idStrategy:

Getting this wrong fails confusingly: a UUID string sent to an AUTO_INCREMENT int column truncates to 0, and omitting an id for a plain varchar PK inserts an empty string that collides on the second record. TableApp::add() returns lastInsertId() ?: true, which is useless for a generated varchar key, so the response reports the id we minted and falls back to the database's only when the strategy is 'auto'.

On update, scope columns are stripped from the payload — they decide which rows the resource can see, so a write must not be able to move a record between scopes. A corollary: don't put a column in both baseWhere and schema — a scope column is treated as immutable on write, so making it an editable form field is a contradiction the update path resolves by dropping the edit.

Query resources on the write path: baseWhere keys carry the join alias for the read ('T1.sp_user_status' => 1). The write path un-qualifies them (ResourceController::writeWhere()) so scope applies against the base table's bare column. This only works if the scope column lives on the base table.

Why this is safe to point at raw request input

Four layers, all driven by the definition:

1. Resource key — regex-validated by ResourceRegistry before any require, so a URL segment can never become a path.
2. fields — TableApp's existing whitelist. assertKnownFields rejects unknown write columns (mass assignment); assertKnownColumn rejects unknown orderBy/groupBy (SQL injection). ResourceTable adds only count(), distinct() and find(), all built on buildWhereSql / assertKnownColumn — no new SQL paths.
3. schema ⊃ writable columns — writablePayload() drops anything else silently, so an over-eager client can't probe the schema.
4. baseWhere — merged into every read and write, so a guessed id can't reach a row outside the resource's scope. The reserved SCOPE key extends this to row ownership — a parameterised (owner OR shared-with) group, admin-bypassable — without opening a new injection surface: the uid binds as a parameter, only the definition's own field names are inlined.

Plus allow gates methods per resource, ResourceQuery only accepts an orderby that the definition marked sortable (falling back to the default rather than throwing on a stale bookmark), and search terms have their LIKE wildcards escaped.

Under a query source, layer 2 shifts. The raw SELECT bypasses the fields whitelist for reads, so ResourceQueryBuilder re-adds the two guards that touch request input: it validates ORDER BY against the declared columns/fields, and it strips exclude columns from every returned row. Request values still bind as parameters; only your own SQL is inlined. Writes are unchanged — they run through TableApp's assertKnownFields, so layers 3 and 4 hold exactly as in table mode.

Dynamic options never touch request input. An OptionSource runs only author-written SQL from the definition; distinct()'s column/table identifiers are pattern-checked, and a failing source logs and yields an empty list rather than surfacing a database error. The filter value the user then picks is an ordinary exact-match term, bound as a parameter like any other filter.

Two behaviours worth knowing

Pagination is genuinely server-side. createTable paginates the array it is handed, which would be wrong here — we hold one page. AppRenderer pins perPage to the row count so createTable renders all rows and skips its own pager, then draws a real one from the total. That total comes from ResourceTable::count() in table mode, and from countSql (or a wrapping COUNT(*) subquery) in query mode. Sortable headers still come from createTable, pointed at basePath with the current search and filters preserved.

Past-the-last-page snaps back. A stale bookmark on page 9 of a list that now has 3 pages re-queries the last page instead of showing an empty table. Both sources clamp: ResourceQueryBuilder adjusts $query->page in place when a query resource's requested page runs off the end.