Documentation · reference

Agent standard

The rules an AI agent follows to build an app that Pyrite hosts without surprises: what the folder looks like, who gets in, who can read and write the data, what the scripts can do, which engine tools to use, which language to write in and how to deliver the package for “Upload app”.

Version 0.7.7 (alpha), for Pyrite 0.7.7 with PocketBase 0.40.6. When the engine changes, the tools chapter is printed again with pyrite.exe tools --md; in the engine console the same catalog is on the “Tools” page. The example app's zip can be loaded as it is with “Upload app”.

Standard version: 0.7.7 (alpha). Engine: Pyrite 0.7.7 with PocketBase 0.40.6. An Italian translation of this standard exists (AGENT-STANDARD.it.md): the two say the same thing, and this English text is the reference.

Some technical names use the orch prefix: $orch in the scripts, the orch_* collections and cookies and the ORCH_* variables; the engine APIs are under /api/platform/.

What changes in 0.7.5, for whoever knows the 0.7.4 standard:

• the app can be reached in more ways, and none of them must matter to it: the usual http://SERVER:PORT/, or its own name (for example https://booking.app.example.com/), or from the internet if an Owner opens it (section 1.1). No fixed host or port in the pages, in the scripts or in the settings;
• each app is “Local network only” or “Internet too”: an Owner decides, an Admin asks. It is not written in app.json (section 1.1);
• the engine writes the real address of the caller in X-Forwarded-For and X-Pyrite-Ip; the same headers written by the client never arrive (section 1.1);
• the events of the engine, also those that come from the apps (the tools, the Data tab), go to the engine history with who did them; the change history (history) now has the person's name also for the changes made by a route written by the app. The apps don't have to be changed (section 1.1).

What changes in 0.7.7, for whoever knows the 0.7.5 standard: the port of app.json is a request (whoever uploads the app can choose another one, and after the installation the port belongs to IT: an update never changes it, and going back to the previous version puts back the port it had before); an app.json with two keys equal apart from upper and lower case is refused; an app uploaded by an Admin warns the Owners by email, and an Owner can reject it with a reason.

This document is written for you, the AI agent that builds an app for a department. Read all of it before writing code. The numbered rules are mandatory: if the department's request contradicts them, don't work around them, write it in the delivery notes (section 15). The examples come from the example app “Parts price list” (parts-pricelist), tested in the engine and downloadable together with this document; the other code examples are tested in the same engine.

01 /The context

Pyrite is a Windows program (pyrite.exe) that runs on a company server, in the local network: people use the apps at addresses such as http://SERVER:8105/, or https://SERVER:8105/ when IT turns HTTPS on (one certificate for the engine and every app port), or with a name for each app; IT can also open some apps to the internet (section 1.1). The engine:

• hosts the departments' apps, each in its own folder and, if it has data, with its own separate PocketBase 0.40.6 (own process and database);
• is the front door: it decides who enters each app (single sign-on from the company portal), counts the requests, shows a clear page when the app is stopped;
• makes backups every night, restores them, restarts apps that crash, collects measures and logs;
• offers a toolbox of ready-made tools for the heavy jobs (import and export of Excel and CSV files, PDF documents, photos, sequence numbers, change history, statistics, scheduled jobs), usable from the app's scripts with $orch and, when the app allows it in toolAccess, from its pages (section 11);
• protects the records from concurrent changes and lets the scripts reach only the network addresses the app declares;
• speaks English and Italian: the engine's console, the portal and the engine's messages are in both languages, English first.

So you must not build: a server, logins, sign-up pages, password management, backups, HTTPS, automatic start, log shipping, libraries for Excel, PDF or images (in the scripts or in the pages: the engine tools do it). You build only the app: data, rules, scripts and pages.

The IT person who runs the engine is not a programmer: everything you deliver must work without manual steps, with explanations in plain words. IT installs your app from a zip file with the console page “Upload app”, reviews it and starts it. In the engine console IT people have a level: an Owner can do everything; an Admin manages only the apps an Owner assigns to them, and an app uploaded by an Admin waits for an Owner's approval before it can start.

1.1 How people reach the app

1. The address. The people reach the app at http://SERVER:PORT/ (or https://, when HTTPS is on). If IT chooses names, each app also has its own name, under the domain of Pyrite (for example https://booking.app.example.com/), and the portal has its own. Whoever opens an app by name without being signed in goes through the portal, signs in once and comes back to the app by itself; the engine uses no cookie valid for the whole domain. IT can also close the ports of the apps to the network: then people reach the apps only through the engine, with the names. So write in the pages relative paths (/api/…, /__platform/portal) and never a fixed host or port, in the pages or in the scripts.
2. Internet. In the engine each app is “Local network only” or “Internet too” (tab Exposure of the app). New apps start as “Local network only”. An Owner decides; an Admin of the app can take it off the internet on their own and, to open it, asks an Owner (the request appears in “To look at” and the history keeps who asked and who decided). An app seen from the internet always opens after the sign-in to the portal. It is not a field of app.json: if the app is meant to be used from outside, say it in DELIVERY.md, so that IT knows what to decide.
3. The address of the caller. The engine writes the real address of whoever calls in X-Forwarded-For and in X-Pyrite-Ip, and the app's PocketBase believes only X-Forwarded-For (in a route, e.realIP()): behind a tunnel or a proxy it is the address of the person, not the one of the tunnel. The headers X-Forwarded-For, X-Forwarded-Proto, X-Pyrite-Ip and X-Orch-Token written by the client never reach the app. CF-Connecting-IP, X-Real-IP and True-Client-IP are written by the engine too, with the real address, and the other headers that carry a client address (X-Client-IP, Fastly-Client-IP, Forwarded…) are removed: what the client writes in them never reaches the app.
4. The links in the emails. For the links in the emails the app sends (address check, forgotten password), the engine writes the right address in the app's settings at every start, and again without a restart when the names change: don't write it yourself, it would be replaced.
5. The engine history. The events of the engine (who started an app, who gave an access, who signed in, an uploaded file, a restored record…) are in the engine history, a file of its own that nobody deletes or changes from the console. An event that comes from an app (a tool, the Data tab) says who did it, if the person really exists and, in an app with login, has access. The change history of the records (history, section 11) now shows the person's name also for the changes made by a route written by the app, when a signed-in person calls it; the scheduled jobs of the app and the calls without a login stay “app script”. Nothing to change in the apps.

02 /What an app looks like

An app is a folder. The folder name is the identifier of the app (id in app.json).

parts-pricelist\                 the app folder (= id)
├── app.json                     the card: kind, port, access, roles, tools
├── DELIVERY.md                  notes for IT (section 15)
└── release\                     the code: it is replaced without touching the data
    ├── pb_migrations\           database schema and initial data (JavaScript)
    ├── pb_hooks\                server-side rules and routes (JavaScript, *.pb.js files)
    │   └── lib\                 shared code, loaded with require()
    ├── pb_public\               the pages: HTML, CSS, JS, images
    │   └── lib\                 the PocketBase SDK (pocketbase.umd.js), local copy
    ├── templates\               PDF templates (.json, .html) and their images, only with the pdf tool
    ├── import\                  import recipes of the app (`*.json`), only with the import tool
    └── seed\                    initial data files (CSV, Excel, JSON), if needed

The engine adds by itself, next to release\, the folders state\ (the data: state\pb_data) and logs\. They are not part of the app you deliver. The app's scripts write files only inside state\: for them app.json, release\ and logs\ are read-only (section 8). The backups of the app (and their copy to the network folder) contain only state\pb_data: a file that must last goes in a collection (file field), not elsewhere in state\, where a restore does not bring it back.

A static app (only pages, no database) has only app.json, the delivery notes and release\public\ (careful: public, not pb_public).

03 /The app.json card

Complete example (the example app):

{
  "id": "parts-pricelist",
  "name": "Parts price list",
  "description": "Prices of the spare parts in the warehouse, updated from the supplier's Excel or CSV file.",
  "kind": "pocketbase",
  "access": "login",
  "port": 8105,
  "version": "0.1.0",
  "owner": "Spare parts warehouse",
  "auth": {
    "collection": "users",
    "roleField": "role",
    "roles": ["manager", "storekeeper"],
    "defaultRole": "storekeeper"
  },
  "tools": ["import"]
}

name and description are a single text: write them in the main language of the people who use the app (the portal shows them as they are). Role names: lowercase letters, digits and underscore, 2 to 30 characters (for example manager, workshop_head).

The engine refuses the card (and so the app) if it isn't valid JSON, if kind isn't one of the three values, if name is missing, if port is out of range or already used (also port + 1000), if defaultRole isn't among the roles, if the id is invalid, reserved or already registered (if id is missing, the name of the folder is used; in a zip the app must then be inside a single folder). “Upload app” applies the same checks before installing (section 15).

04 /Choosing the kind of app

If the app must remember something (even just shared preferences or an editable list), it is pocketbase. Don't save shared data in the browser's localStorage.

05 /Users, access and roles

The principle: who enters is decided by IT, centrally, and people enter only from the portal; the role inside the app is managed by the engine together with the app.

1. In apps with access: "login" the person enters from the company portal with the credentials of the central directory. The engine creates in the app's users collection a user with the email and the name of the person and links it to the person by ID: when IT changes the email of a person, or the person changes the displayed name in the profile, the engine aligns the app user, which stays the same record with the same id. The engine issues a session and puts it in the browser (key pocketbase_auth of localStorage, the one the PocketBase SDK uses by itself). When the page opens, the user is already signed in: pb.authStore.record.
2. The app must not have: login pages, sign-up, “forgot password”, change password, a “Sign out” button. Passwords belong to the directory: the engine applies the password rules (uppercase, lowercase, digit, special character, minimum length set by IT) and the mandatory change at the first access. To go back to the portal use the link /__platform/portal; people sign out from the portal.
3. The identity is locked by the engine: from the app's APIs no one can create users or change email, name or password. Don't try, and don't show fields for it. To say whose a record is, use the id of the user (a relation field to users), never the email: the email can change, the id stays. The person has a profile page in the engine (/profile/, from the portal): displayed name, language, theme, password and the open sessions, with “Sign out of all sessions”, which also closes the sessions in the apps (the next call of the page answers 401: section 9, rule 4). The app doesn't need to offer any of this.
4. The role is a select field of the users collection, called role (declared in auth.roleField), with the same values as auth.roles; when the app runs in the engine, the values of auth.roles are also accepted by the field. At the first entry (and whenever the user's role is empty) the engine writes the base level (auth.defaultRole); otherwise it doesn't touch it. The role is changed:
   • by IT, from the engine console (app page, “Access and roles” tab: an Owner, or an Admin who manages the app);
   • if needed, by whoever has the highest role, from a page of the app, if the updateRule of users allows it; when toolAccess uses minRole or destructiveRole, nobody can give a role more powerful than their own.
5. In apps with login nobody can change their own role: the engine blocks it (not in the local test of section 12.1, where the engine is missing). In public apps there is no such block: only when toolAccess uses minRole or destructiveRole the engine stops anyone from giving a role more powerful than their own (lowering is allowed); otherwise the updateRule of users decides.
6. The role field is created by a migration (section 7.3). The rules read it as @request.auth.role.
7. In apps with access: "public" anyone in the company network opens the pages without signing in: the requests are anonymous. No app has free sign-up, not even the public ones: who has a user in an app is decided by IT: in apps with login with the people and groups of the engine, in public apps by creating them in the app's Data tab (section 13). If a public app needs users (for example the few people who may write), they are created by IT or by the app's scripts (never in the migrations: rule 8), never by a sign-up page: the engine refuses every API request that creates a record in any auth collection (not only users; OAuth2 and /api/batch included): with 403 in public apps, with 400 in the users collection of an app with login; only a superuser of the app, that is the panel the engine opens, can create users and warns IT if a collection asks for it with its createRule. Keep the createRule of users at null (section 6, rule 5). Don't put confidential data in a public app and don't allow anonymous writes unless they are really the purpose of the app (for example a report form); in that case agree it with IT and write it in the notes.
8. Don't create users, test accounts or administrators in the migrations, and don't write passwords anywhere.

06 /API rules (mandatory)

PocketBase exposes every collection as a REST API. Who can read and write is decided only by the rules of the collection (listRule, viewRule, createRule, updateRule, deleteRule), checked by the server. Pages protect nothing: anyone with access to the app can call the APIs directly.

1. Every collection has the five rules written explicitly in the migration that creates it. Never leave them at the starting value.
2. null means “nobody” (only the engine and IT). "" (empty text) means “anyone, even without login”. Never "" on createRule, updateRule, deleteRule. "" on listRule/viewRule only in a public app and only for really public data.
3. Every rule of an app with login starts with @request.auth.id != ''. Without login @request.auth.id is '': a rule like requester = @request.auth.id becomes true for all the records with an empty requester, and those records become readable and writable by anyone. The right form is:

listRule: "@request.auth.id != '' && (@request.auth.role = 'buyer' || requester = @request.auth.id)",

1. Fields decided by the server (author, approval status, sequence numbers, validation dates) are not accepted from the client: either a hook sets them (section 7.1), or the rule forbids them with @request.body.field:isset = false.
2. The app's users collection has strict rules, in every app, public ones included: createRule: null (no sign-up), deleteRule: null, reading one's own user (and, if needed, the colleagues for whoever has the highest role), updateRule only for who may change the others' roles, or null.
3. The routes you write (routerAdd) do not go through the collection rules: protect them with $apis.requireAuth("users") and check the role in the code. In the same way, e.app.save(...) in scripts writes without rules: the script is the server. The $orch tools called by the scripts also write as the server; called by the pages (/api/tools/), they apply the rules of the app for the person and the permissions of toolAccess (section 11, rules 6 and 8).
4. Test the rules for real: without login, with every role, calling the APIs directly (section 12).

Typical rules (roles manager and storekeeper, field role):

The migration of the example app that creates the role field, the collections and their rules:

/// <reference path="../pb_data/types.d.ts" />
// Parts price list: the users' role, the price list and the files uploaded by the manager.
// In migrations the constants written out here can be used (in hook handlers they can't).

const AUTH = "@request.auth.id != ''";
const MANAGER = "@request.auth.id != '' && @request.auth.role = 'manager'";

migrate((app) => {
  // users: the engine creates them (same email as the company directory); here only the role field and strict rules
  const users = app.findCollectionByNameOrId("users");
  if (!users.fields.getByName("role")) {
    users.fields.add(new SelectField({ name: "role", maxSelect: 1, values: ["manager", "storekeeper"] }));
  }
  users.listRule = AUTH + " && (id = @request.auth.id || @request.auth.role = 'manager')";
  users.viewRule = AUTH + " && (id = @request.auth.id || @request.auth.role = 'manager')";
  users.createRule = null; // no sign-up: IT decides who gets in
  users.updateRule = MANAGER; // the manager changes the colleagues' role (not their own: the engine blocks it)
  users.deleteRule = null;
  app.save(users);

  const parts = new Collection({
    type: "base",
    name: "parts",
    listRule: AUTH,
    viewRule: AUTH,
    createRule: MANAGER,
    updateRule: MANAGER,
    deleteRule: MANAGER,
    fields: [
      { name: "code", type: "text", required: true, max: 40, presentable: true },
      { name: "description", type: "text", required: true, max: 300 },
      { name: "price", type: "number", min: 0 },
      { name: "supplier", type: "text", max: 120 },
      { name: "updated_by", type: "text", max: 200 },
      { name: "created", type: "autodate", onCreate: true, onUpdate: false },
      { name: "updated", type: "autodate", onCreate: true, onUpdate: true },
    ],
    indexes: ["CREATE UNIQUE INDEX idx_parts_code ON parts (code)"],
  });
  app.save(parts);

  // the supplier's files: the manager uploads them, then the route /api/pricelist/import/{id} imports them
  const uploads = new Collection({
    type: "base",
    name: "uploads",
    listRule: MANAGER,
    viewRule: MANAGER,
    createRule: MANAGER + " && @request.body.import_id:isset = false && @request.body.result:isset = false && @request.body.undone:isset = false",
    updateRule: null,
    deleteRule: null,
    fields: [
      { name: "file", type: "file", required: true, maxSelect: 1, maxSize: 20971520 },
      { name: "import_id", type: "text", max: 40 },
      { name: "result", type: "json", maxSize: 2000000 },
      { name: "undone", type: "bool" },
      { name: "created", type: "autodate", onCreate: true, onUpdate: false },
      { name: "updated", type: "autodate", onCreate: true, onUpdate: true },
    ],
  });
  app.save(uploads);
}, (app) => {
  for (const name of ["uploads", "parts"]) {
    try { app.delete(app.findCollectionByNameOrId(name)); } catch (_) {}
  }
  const users = app.findCollectionByNameOrId("users");
  users.fields.removeByName("role");
  app.save(users);
});

07 /Server-side JS scripts (PocketBase 0.40.6)

The scripts run inside the app's PocketBase, with the goja JavaScript engine: modern JavaScript, but without Node.js, without npm, without fetch and without async/await (handlers are synchronous). Use only the PocketBase 0.40.6 APIs; don't copy examples of old versions (before 0.23 the JS APIs were different: no $app.dao(), no onRecordBeforeCreateRequest).

7.1 Hooks and routes (release\pb_hooks\*.pb.js)

PocketBase loads only the files ending with .pb.js, in alphabetical order, when the app starts: after a change the app must be restarted.

1. Every handler runs in a separate JS machine: it doesn't see variables, constants or functions written outside the handler, in the same file. Shared code goes in a module (for example pb_hooks\lib\rules.js, without .pb.js) and is loaded inside the handler with require(__hooks + "/lib/rules.js"). Inside the module, the module's own constants and functions are visible.
2. In record and request hooks always call e.next() to go on; to block, throw an error: throw new BadRequestError("message for the person") (also ForbiddenError, NotFoundError, UnauthorizedError).
3. Use e.app inside handlers (it is the right app even inside a transaction); $app is fine in routes and cronAdd jobs. The $orch tools use the right app by themselves (section 11, rule 5).
4. e.auth is the user making the request (or null): e.auth.getString("role"), e.auth.email(), e.auth.id.
5. Request data: e.requestInfo().body, e.request.pathValue("id"), e.request.url.query().get("x"), e.request.header.get("Accept-Language"). Answers: e.json(200, object), e.string(200, text), e.noContent(204).
6. SQL only with bound parameters, never by concatenating texts: e.app.db().newQuery("... WHERE x = {:x}").bind({ x: value }). To read the rows: arrayOf(new DynamicModel({ text: "", whole: 0, decimal: -0, flag: false })) (for numbers with decimals the shape is -0, not 0).
7. Find records with filters and parameters: e.app.findRecordsByFilter("suppliers", "company_name ~ {:s}", "-created", 100, 0, { s: search }), e.app.findFirstRecordByFilter("suppliers", "account = {:a}", { a: account }), e.app.findRecordById("parts", id). Go through the results with for (const r of records).
8. Several writes that must succeed together: e.app.runInTransaction((txApp) => { ... txApp.save(r) ... }), using txApp inside.
9. Dates are saved in UTC (new DateTime() is now); the format is handled in the page.
10. console.log goes to the output of the process, which IT sees in the engine console (Status tab → “Process output”). Don't write personal data there.
11. The prefix /api/tools/ belongs to the engine (the tools from the pages): never register routes there. “Upload app” refuses a new package that registers a route written under /api/tools/ (a route with a placeholder that could also match there, such as /api/{coll}/{id}/approve, is only a warning, and at start-up it leaves the app's pages without the tools); an app already installed that has them keeps its routes, but its pages can't use the engine tools until the routes move. Put the app's routes under a name of the app, for example /api/pricelist/… as in the example.
12. Saves made by the scripts aren't subject to the protection from concurrent changes (section 9, rule 10): if a route changes a record on behalf of a person after showing it to her, compare the version yourself or use the pages.

Example (the example app): fields decided by the server, a protected route that uses an engine tool, the undo, and a summary with SQL.

/// <reference path="../pb_data/types.d.ts" />
// Parts price list: who changed a part, and the import of the supplier's file.
// Every handler runs in a separate JS machine: it doesn't see constants or functions written out here.
// The shared code is in lib/rules.js and is loaded with require() inside the handler.

onRecordCreateRequest((e) => {
  e.record.set("updated_by", e.auth ? (e.auth.getString("name") || e.auth.email()) : "");
  e.next();
}, "parts");

onRecordUpdateRequest((e) => {
  e.record.set("updated_by", e.auth ? (e.auth.getString("name") || e.auth.email()) : "");
  e.next();
}, "parts");

// imports the file of an "uploads" record: first the trial run, then the real load
routerAdd("POST", "/api/pricelist/import/{id}", (e) => {
  const rules = require(__hooks + "/lib/rules.js");
  rules.managerOnly(e);

  const rec = e.app.findRecordById("uploads", e.request.pathValue("id"));
  if (rec.getString("import_id")) {
    throw new BadRequestError(rules.text(e, "already_loaded"));
  }
  const file = $filepath.join(e.app.dataDir(), "storage", rec.collection().id, rec.id, rec.getString("file"));
  const recipe = rules.pricelistRecipe(e, file);

  const trial = $orch.import.load(file, recipe, { dryRun: true, app: e.app });
  if (trial.rejected > 0) {
    return e.json(400, { message: rules.text(e, "rows_to_fix", { n: trial.rejected }), result: trial });
  }
  const result = $orch.import.load(file, recipe, { by: e.auth.email(), app: e.app });
  rec.set("import_id", result.id);
  rec.set("result", result);
  e.app.save(rec);
  return e.json(200, result);
}, $apis.requireAuth("users"));

// undoes the import of a file: removes the parts it created and restores the ones it updated
routerAdd("POST", "/api/pricelist/undo/{id}", (e) => {
  const rules = require(__hooks + "/lib/rules.js");
  rules.managerOnly(e);

  const rec = e.app.findRecordById("uploads", e.request.pathValue("id"));
  if (!rec.getString("import_id")) {
    throw new BadRequestError(rules.text(e, "not_loaded"));
  }
  if (rec.getBool("undone")) {
    throw new BadRequestError(rules.text(e, "already_undone"));
  }
  const result = $orch.import.undo(rec.getString("import_id"), { by: e.auth.email(), app: e.app });
  rec.set("undone", true);
  e.app.save(rec);
  return e.json(200, result);
}, $apis.requireAuth("users"));

// summary per supplier: an SQL query with the parameters always bound, never concatenated
routerAdd("GET", "/api/pricelist/summary", (e) => {
  const min = parseFloat(e.request.url.query().get("min") || "0") || 0;
  // shape of a row: "" text, 0 whole number, -0 number with decimals
  const rows = arrayOf(new DynamicModel({ supplier: "", parts: 0, average_price: -0 }));
  e.app.db()
    .newQuery("SELECT supplier, COUNT(*) AS parts, AVG(price) AS average_price FROM parts WHERE price >= {:min} GROUP BY supplier ORDER BY supplier")
    .bind({ min: min })
    .all(rows);
  return e.json(200, rows);
}, $apis.requireAuth("users"));

The shared module pb_hooks\lib\rules.js, with the texts in the two languages of the app and the recipe built from the supplier's file:

// Shared rules of the price list: loaded inside the handlers with require(__hooks + "/lib/rules.js").
// The name doesn't end with .pb.js, so PocketBase doesn't load it on its own as a hook file.
// Inside a module, the constants and functions written here are visible to the module's functions.

// texts for people, in the two languages of the app (the page tells its language with Accept-Language)
const TEXTS = {
  en: {
    manager_only: "Only the warehouse manager can do this.",
    already_loaded: "This file has already been loaded.",
    not_loaded: "This file hasn't been loaded.",
    already_undone: "This import has already been undone.",
    rows_to_fix: "{n} rows need fixing: nothing was loaded.",
    missing_columns: "The file doesn't have the columns {list}.",
  },
  it: {
    manager_only: "Solo il responsabile del magazzino può farlo.",
    already_loaded: "Questo file è già stato caricato.",
    not_loaded: "Questo file non è stato caricato.",
    already_undone: "Questo caricamento è già stato annullato.",
    rows_to_fix: "Ci sono {n} righe da correggere: non è stato caricato niente.",
    missing_columns: "Nel file mancano le colonne {list}.",
  },
};

// the columns of the supplier's file: field of "parts" → accepted column titles (English or Italian)
const COLUMNS = {
  code: ["Code", "Codice"],
  description: ["Description", "Descrizione"],
  price: ["Price", "Prezzo"],
  supplier: ["Supplier", "Fornitore"],
};
const REQUIRED = ["code", "description", "price"];

// lang: the language of the request, "it" if the page asks for Italian, otherwise "en"
function lang(e) {
  return /^it\b/i.test(e.request.header.get("Accept-Language")) ? "it" : "en";
}

// text: a text for the person, in the language of the request, with its {placeholders} filled in
function text(e, key, vars) {
  let t = TEXTS[lang(e)][key];
  for (const name in vars || {}) {
    t = t.replace("{" + name + "}", String(vars[name]));
  }
  return t;
}

// managerOnly: only the warehouse manager (field "role" of the users, app.json → auth.roleField)
function managerOnly(e) {
  if (!e.auth || e.auth.getString("role") !== "manager") {
    throw new ForbiddenError(text(e, "manager_only"));
  }
}

// pricelistRecipe: the import recipe for the supplier's file (Excel or CSV, one row per part). The file is
// analyzed first: the CSV separator and the column titles (in English or in Italian) come from the file itself.
function pricelistRecipe(e, file) {
  let a = $orch.import.analyze(file, { header: "titles" });
  if (a.format === "csv" && a.suggestedSeparator && a.suggestedSeparator !== a.separator) {
    a = $orch.import.analyze(file, { header: "titles", separator: a.suggestedSeparator });
  }
  const titles = a.columns.map((c) => c.name);
  const fields = {};
  const missing = [];
  for (const field of Object.keys(COLUMNS)) {
    const title = titles.find((t) => COLUMNS[field].some((name) => name.toLowerCase() === t.trim().toLowerCase()));
    if (title) {
      fields[title] = field;
    } else if (REQUIRED.includes(field)) {
      missing.push(COLUMNS[field][lang(e) === "it" ? 1 : 0]);
    }
  }
  if (missing.length) {
    throw new BadRequestError(text(e, "missing_columns", { list: missing.join(", ") }));
  }
  const options = { format: a.format, header: "titles" };
  if (a.format === "csv") {
    options.separator = a.separator;
  }
  return { name: "Supplier price list", collection: "parts", options: options, fields: fields, key: ["code"] };
}

module.exports = { lang, text, managerOnly, pricelistRecipe };

Scheduled jobs (kind: "pocketbase-cron"), in a .pb.js file:

cronAdd("overdue-reminders", "0 7 * * 1-5", () => {
  const late = $app.findRecordsByFilter("cases", "due != '' && due < {:today} && closed = false", "due", 500, 0, { today: new DateTime() });
  for (const c of late) {
    c.set("overdue", true);
    $app.save(c);
  }
});

The engine records every run of every job (start, duration, result, error), lets IT pause, resume and “Run now” from the console, and writes an event when a job starts failing. So:

• give every job a stable, readable name (the first argument of cronAdd, lowercase letters, digits and dashes): it is the key of the history and of the pause;
• the schedule is in UTC, as in PocketBase ("0 7 * * 1-5" is 07:00 UTC, 08:00 or 09:00 in Italy);
• let the errors out of the function (or throw your own new Error("…")): an empty catch makes a failed job look fine;
• a restart of the app interrupts the runs in progress: the one asked by IT and also the automatic one after a change of the internal port, the memory limit, access, the auth roles/collection/field or the network mode (changes to network, toolAccess, concurrency are read again without a restart). The run stays in the history as “interrupted”. Write long jobs so that they can be redone, in pieces, never all or nothing.

Details in section 11, “Scheduled jobs”.

Slow routes. The engine waits at most 4 minutes 50 seconds for the answer of a route of your scripts (the limit of PocketBase itself is 5 minutes). If the route has not answered by then, the caller gets 504 with a clear message; if the app closes the connection without answering (also suddenly, for example when the app's process stops because of a fault), 502. A stopped or restarting app gives 503. The engine never repeats a request that was already sent: the route may have finished its work all the same, so check before trying again (a second click must not create a second record). If the caller goes away before the answer, the engine counts the request but not as done or as an error (status 499 in its own counters). A 504 or 502 leaves a warning in the engine events. So don't write routes that take minutes: for long work start a job (section 11, jobs) and answer at once.

7.2 Migrations (release\pb_migrations\)

1. The database schema changes only with migrations: files <number>_<name>.js, applied once, in name order. Use a Unix timestamp as the number (for example 1793600000_parts.js) and make the number grow with every new file.
2. A migration already delivered is never changed, renamed or removed (section 15, point 8): to change the schema add a new file. Always write the function that goes back too (second argument of migrate).
3. In migrations use the app you receive (migrate((app) => { ... })), not $app. Constants written outside migrate can be used.
4. Collections with new Collection({ type: "base", name, listRule, viewRule, createRule, updateRule, deleteRule, fields: [...], indexes: [...] }) and app.save(collection). Field types: text, editor, number, bool, email, url, date, autodate, select, file, relation, json, geoPoint. Always add created and updated of type autodate (sorting by -created needs them).
5. Don't touch the system collections (_superusers, _mfas, _otps, _externalAuths, _authOrigins) or the engine's ones (orch_*). Of the users collection, change only the rules and add your own fields (the role, the department…).
6. Initial data: from files in release\seed\ (CSV or Excel with the import tool, section 11; JSON with $os.readFile and JSON.parse(toString(...))). Never real personal data without IT's agreement, never users or passwords.

Example (the example app): initial data from a CSV delivered with the app.

/// <reference path="../pb_data/types.d.ts" />
// Initial price list, delivered with the app in release/seed (engine tool "import").

migrate((app) => {
  $orch.import.load("release/seed/initial_parts.csv", {
    name: "Initial price list",
    collection: "parts",
    options: { format: "csv", separator: ";" },
    fields: { "Code": "code", "Description": "description", "Price": "price", "Supplier": "supplier" },
    key: ["code"],
  }, { app: app, by: "initial data" });
}, (app) => {
  app.db().newQuery("DELETE FROM parts").execute();
});

Example: initial data from a JSON file.

/// <reference path="../pb_data/types.d.ts" />
// Departments delivered with the app as JSON in release/seed (for JSON no engine tool is needed).

migrate((app) => {
  const list = JSON.parse(toString($os.readFile("release/seed/departments.json")));
  const departments = app.findCollectionByNameOrId("departments");
  for (const d of list) {
    const rec = new Record(departments);
    rec.set("code", d.code);
    rec.set("name", d.name);
    app.save(rec);
  }
}, (app) => {
  app.db().newQuery("DELETE FROM departments").execute();
});

7.3 The role field

If the app has roles, the first migration adds to the users collection the field declared in auth.roleField (role), with the same values as auth.roles, and sets the rules of users (see the example in section 6).

7.4 People's data in the package

A package carries only the data the app needs to start. People's data (customers, employees, suppliers with name and address) is something else and has its own rules.

1. In the package goes only lists, service tables and a few made-up examples (in the example app: the initial price list and the departments, section 7.2, point 6).
2. A real archive of people's data is not locked inside the zip. IT loads it after the installation, with the engine's import tool (the console's “Import” tab, or $orch.import), from a file that stays where IT decides. In the package put at most the import recipe (release\import\*.json) and, in DELIVERY.md, the description of the expected columns.
3. Initial data is imported with the engine's tool ($orch.import.load, as in the example of section 7.2), never with direct SQL or a row-by-row save written by hand. This way every import stays in the engine's import history and goes through the checks (dry run, keys, relations). For a small service list in JSON the example of section 7.2 is fine.
4. DELIVERY.md has a mandatory “Personal data” line: which people's data there is, where it comes from and which roles see it. If there is none, write “none”.
5. The final checklist (section 14) has the line that checks it.

08 /What a script cannot do (the jail)

The scripts of each app are closed inside the app folder. The engine blocks, with an error in English:

• starting programs ($os.cmd, $os.exec) and closing the process ($os.exit);
• reading files outside the app folder ($os.readFile, $filepath.walk, $filepath.glob, $filesystem.fileFromPath, $template.loadFiles, require with paths that leave, even inside a module);
• writing, renaming or deleting files outside state\ ($os.writeFile, $os.mkdir, $os.rename, $os.remove and the like): app.json, release\ and logs\ can only be read by the scripts. If the app has to save a file of its own (for example a temporary file or an export), it puts it in state\; files uploaded by people go in a file field of a collection;
• using the database to open or write other files: ATTACH, DETACH, VACUUM INTO outside state\, extensions (load_extension);
• using names that Windows reads as another name: a part of the path that ends with a dot or a space (state.), or that contains : (NTFS streams, state::$DATA), is refused;
• going through links: a path that crosses a symbolic link (symlink) or a Windows junction inside the app folder is refused, for reading and for writing, even if it leads back inside the folder (for example to app.json or to state\). The only exception is the state\ folder itself: IT may replace it with a junction to keep the app's data on another disk, and for the scripts it remains the usual state\; links further down, inside state\, are refused. Scripts can't create links and the app doesn't need them: don't put any in the package (“Upload app” refuses them anyway);
• reaching the internal ports of the other apps and the engine's keys (they are not in the environment variables);
• calling network addresses that aren't declared in network (section 8.1), and calling the engine or another app of the same server (section 8.1);
• $apis.static on a folder outside the app folder; $app.onServe(), $app.restart(), $app.restoreBackup(), $app.rootCmd, unbind, unbindAll, bind on the app's hooks, onSend of the mail client and $app.unsafeWithoutHooks() (these names do not exist for the scripts: using one gives an error); paths that start with a slash (/release/x.json is the root of the disk, refused: write release/x.json).

The app.json card is changed only by IT (an Owner, from the Configuration tab of the engine console, or by hand): a script can't give itself another access, other roles or other tools.

Also, as rules:

1. No calls to external services or to the Internet unless IT asks for them. Every address the scripts call is declared in app.json, field network (section 8.1): the engine blocks the others. A company system in the local network is used only if IT asks for it.
2. No data exchange with other apps through their files or their ports: if it's needed, ask IT.
3. If you need a heavy job (reading Excel, creating PDFs, sending emails, shrinking images…), use the engine tool (section 11). If there isn't one, don't invent one with tricks: write in the delivery notes what would be needed.

8.1 Outbound network (network)

Every network call of the scripts ($http.send, $filesystem.fileFromURL, $filesystem.s3, and the calls PocketBase makes for the app: S3 storage, backups, OAuth2) goes through the engine, which allows only the addresses declared in app.json:

"network": {
  "allow": ["api.openai.com", "*.example.com", "erp.example.local:8443"],
  "allowHttp": ["old-erp.example.local"]
}

• An entry is a host name or an IP address, with the port if needed (without it: 443 for https, 80 for http). *.example.com covers the names below example.com and only public addresses (at least two labels after the *: *.com is not valid). A wrong entry is ignored and reported by “Upload app”. No * alone, no https://, no paths.
• Only https; plain http only for allowHttp.
• The engine checks the real IP address after the DNS lookup: a server in the company network is reached only with an exact entry (no *); the machine itself (127.0.0.1, ::1) and 169.254.x only with an entry written as an IP address, never through a name such as localhost; the engine itself and the other apps of this server are never reachable from a script, not even with an entry (403, code app_origin).
• The mail server of the app (SMTP: $app.newMailClient(), $mails.*) is an address too: declare it with its port, for example "smtp.example.com:587".
• Redirects only towards declared addresses (at most 9); responses up to 32 MB; at most 300 seconds per call (timeout, default 120).
• A call that isn't allowed throws an error in English that says what to declare. The engine logs every call (never the content) in the app's “Outbound network” tab; IT approves the addresses at “Upload app” and can change them from that tab.
• IT decides the mode of the outbound gate, in the engine's registry (not in app.json): enforce (Enforce, the starting mode) blocks what isn't declared, warn (Warn) only logs it.
• Write in DELIVERY.md what each address is for.

AI models. Apps call the AI models (OpenAI, Anthropic, Gemini, Mistral, Azure OpenAI, OpenRouter, Groq, DeepSeek…) only from the scripts, with $http.send and the provider's address in network, never from the browser: in a page the key would be visible to anyone. The page calls a route of the app (routerAdd with $apis.requireAuth()), which calls the model. The key is never in the code, in app.json or in the pages: ask IT where it is kept. The engine reads the model and the tokens from the responses (with OpenAI Chat Completions in streaming, ask for stream_options: { include_usage: true }) and shows usage and estimated costs on the console's “AI” page; it never records the messages. The complete text, with an example, is in the “Engine tools” chapter (pyrite.exe tools --md).

Scripts can't call the engine or other apps. A script can't use $http.send (or $filesystem.fileFromURL) to call the engine (console, portal, /api/platform/…) or another app on the same server, not even by declaring its address in network: the engine recognizes the calls made by apps and answers 403 with "code": "app_origin", and IT sees it in the events. Calling your own app (its port or its name) is still allowed: the call counts as a request coming from the network, so without the permissions of someone sitting at the server (called as http://127.0.0.1:PORT it is served in clear text even when HTTPS is on; called with the name or the network address of the server, it gets the redirect to https). Calling another program that runs on the same server (for example a local AI model) is still allowed when it is declared in network as an IP address with its port: it receives the X-Pyrite-App header, which it can ignore. An X-Pyrite-App header written by the script is always removed. If two apps need to exchange data, ask IT and write it in DELIVERY.md.

Hooks. Scripts have no unbind, unbindAll or bind on the app's hooks, no onSend on the mail client and no $app.unsafeWithoutHooks(): the engine's handlers can't be removed or skipped. To add a handler use the usual functions (onRecordCreate, onMailerSend…) or bindFunc.

The limit of the fence. All this is a defence inside the program: the script runs in the same process as the app's engine, with the same Windows user, so it greatly reduces what a script can do but it is not a full guarantee against a script written on purpose to get out. The full guarantee for apps of third parties (one Windows user for each app) is planned for a future version. Until then, don't count on the fence alone for scripts written by someone IT doesn't trust.

09 /The pages (release\pb_public\)

The engine serves the pages on the app's port, from the same address as the APIs: http://SERVER:8105/ for the pages and http://SERVER:8105/api/... for the data. In a PocketBase app, a path with no file in pb_public answers with index.html (single-page apps work with any path).

1. Always relative paths (style.css, lib/pocketbase.umd.js, /api/...). Never addresses or ports written in the code, and never http:// written by hand: the same app can be served in http or in https (IT decides, from the engine's Certificates page).
2. No resources from the Internet: no CDNs, Google Fonts, remote libraries, external images. Everything needed is in pb_public (or in public for static apps). Fonts: the system ones (system-ui, "Segoe UI", sans-serif).
3. PocketBase SDK: a local copy of pocketbase.umd.js in pb_public\lib\, the same as the example app (it's inside the downloadable example, with its license). It is created with const pb = new PocketBase("/"); and finds by itself the session delivered by the engine.
4. The engine inserts by itself, in the HTML pages of apps with login, the single sign-on script (/__platform/sso.js) and, in the pages of every PocketBase app, the script of the protection from concurrent changes (/__platform/concurrency.js, rule 10): don't add them and don't remove them. If a call answers 401, reload the page (location.reload()): the engine signs in again. This holds for every call, also for the forms' ones (saves, file uploads), not only for the list.
5. Link to go back to the portal: <a href="/__platform/portal">← Company portal</a> (in the app's language). No “Sign out” button.
6. The paths starting with /__platform/, /_/, /api/platform/ and /api/tools/ belong to the engine: don't use them for anything else. On the app port /_/ redirects to the engine console and /api/collections/_superusers/ is closed (403): the PocketBase dashboard and the superuser sign-in are not available there. /__platform/info returns in JSON the app's identifier, its name and the portal address ({ app, name, portal }); /__platform/tools.js is the helper for the tools from the pages (rule 11).
7. Show the error messages that come from the server (err.response.message with the SDK): the app's routes write them in the language of the request (section 10); the messages of PocketBase itself (field validation) are in English.
8. Hiding a button from who doesn't have the role is only a convenience: the real protection are the rules (section 6) and the routes.
9. Simple pages, readable on a small screen: at 390 px wide the page doesn't scroll sideways, and a wide table sits in a container that scrolls by itself (overflow-x: auto). Light and dark theme (color-scheme: light dark and prefers-color-scheme), a label on every field (<label for="…">, also on the file field), enough contrast.
10. Concurrent changes. Every change of a record (PATCH, or a PUT that updates, also inside /api/batch) declares the version it changes (If-Match: * means overwrite anyway; a DELETE is checked only if it carries If-Match, the script does not add it): the /__platform/concurrency.js script adds the If-Match header by itself for the records the page has read and, if someone else saved in the meantime, the answer is 409 and the script shows a notice with “Reload”; a change without any version gets 428. So: when you use fields, add $version (fields: "id,title,$version"); a page that changes records from a list that updates by itself (inline editing, kanban) sends the "$version" of the record it shows; before changing the user's own record, read it again (getOne or authRefresh), because pb.authStore.record has no $version. Show the 409 to the person in plain words (“Someone else changed it: reload”). Details in section 11, rule 9.
11. Tools from the pages. Export, PDF, photos, statistics, numbering, history and import can be used by the pages directly, through /__platform/tools.js (PyriteTools.call, run, upload, download…), only for the tools and the people that toolAccess allows. GET /api/tools says what this person may use: show or hide the buttons with it, but remember that the engine checks every call anyway. Long operations become background jobs with progress; the files produced are downloaded with a one-time link (section 11, rule 8).

Example (the example app, pb_public\index.html and style.css, excerpts):

<form id="fUpload">
  <label for="file" data-t="file_label">Supplier's Excel or CSV file</label>
  <input id="file" type="file" name="file" accept=".xlsx,.csv" required>
  <p class="muted small" data-t="file_hint">Columns: Code, Description, Price and, if there is one, Supplier (titles in English or in Italian).</p>
  <button type="submit" data-t="upload_button">Upload and import</button>
</form>

.table { overflow-x: auto; } /* a wide table scrolls sideways inside its box: the page stays still */

The page script (pb_public\app.js, excerpts): the session delivered by the engine, the upload of the file into a collection and the call to the app's route, with the 401 that reloads the page.

const pb = new PocketBase("/");
const $ = (id) => document.getElementById(id);
const me = pb.authStore.record; // { id, email, name, role, ... }
let lastUpload = ""; // the "uploads" record of the last import, for the Undo button

async function upload(ev) {
  ev.preventDefault();
  const btn = ev.target.querySelector("button");
  btn.disabled = true;
  $("undo").hidden = true;
  $("result").textContent = t("importing");
  try {
    const rec = await pb.collection("uploads").create(new FormData(ev.target)); // the file goes into the collection
    const res = await pb.send("/api/pricelist/import/" + rec.id, { method: "POST" }); // the app's route imports it
    $("result").textContent = t("done", res);
    lastUpload = rec.id;
    $("undo").hidden = false;
    ev.target.reset();
    list();
    summary();
  } catch (err) {
    if (err?.status === 401) return location.reload(); // session expired: the engine signs in again from the portal
    $("result").textContent = err.response?.message || err.message;
    const res = err.response?.result; // the rows to fix, with row number and reason
    if (res?.errors?.length) {
      const ul = document.createElement("ul");
      for (const r of res.errors) ul.appendChild(document.createElement("li")).textContent = `${t("row", r)}${r.column ? " (" + r.column + ")" : ""}: ${r.reason}`;
      $("result").appendChild(ul);
    }
  } finally {
    btn.disabled = false;
  }
}

10 /Languages and texts

1. An app can be in any language: the one of the people who use it, or more than one if the request says so. The engine itself speaks English first and Italian next to it. Texts for people are plain, without technical jargon, also in the empty states (“No parts.”).
2. Technical names and code comments are in English, whatever the language of the app: collections, fields, roles, routes, files, variables, functions, comments. The names people see are translated in the page (the role manager is shown as “responsabile” to an Italian user).
3. Which language to show. Follow the choice made in the portal: the portal's language switch writes the cookie orch_lang (en or it) over http and __Host-orch_lang (Secure, Path=/) over https, valid on the whole engine and also on the apps' ports; then the browser's language; then the main language of the app. An app reads both names (the __Host- one first) and, when it has its own switch, writes the one for the scheme of the page (orch_lang=it; path=/; max-age=31536000; SameSite=Lax over http, __Host-orch_lang=it; path=/; max-age=31536000; SameSite=Lax; Secure over https), so the choice is the same everywhere. In the example app:

function pickLang() {
  const ck = (n) => (document.cookie.match(new RegExp('(?:^|;\\s*)' + n + '=(en|it)\\b')) || [])[1];
  const chosen = ck('__Host-orch_lang') || ck('orch_lang');
  const browser = (navigator.languages || [navigator.language || ""]).map((l) => l.slice(0, 2).toLowerCase()).find((l) => TEXTS[l]);
  return chosen || browser || "en";
}

1. Server messages in the language of the page. The page tells its language to the PocketBase SDK (pb.lang = lang), which sends it in the Accept-Language header of every request; the app's routes read it with e.request.header.get("Accept-Language") and answer in that language (see lib/rules.js in section 7.1).
2. Numbers and dates in the format of the language: Intl.NumberFormat(lang === "it" ? "it-IT" : "en-US", …), toLocaleDateString(…).
3. Error messages say what to do: “Only the warehouse manager can do this”, not “403 Forbidden”.
4. The messages that scripts receive from the engine ($orch tools, the jail) are in English: they are for whoever writes the app. If the person has to see them, the app writes its own message in the person's language.

11 /Engine tools ($orch)

The engine has a toolbox of standard tools written in Go: ready-made components for the heavy jobs, which apps use from their JS scripts and, when IT allows it, from their pages, with no external services, no programs to start and no libraries to add. Before writing a file reader, a document generator or an email sender by hand, check whether the tool exists.

Rules common to all tools:

1. They are turned on per app, in app.json: "tools": ["import"]. An Owner can also turn them on from the engine console (app page, Configuration tab). A tool that is not turned on and is called by a script throws an error that explains how to turn it on. List only the tools the app really uses. The names of tools, functions, options and results are in English, as in this chapter: Italian names such as $orch.importa do not work in scripts.
2. They are used from the app's scripts (pb_hooks and pb_migrations) through the global object $orch: $orch.<tool>.<function>(…). The functions marked “from the app pages” can also be called by the frontend in pb_public, with the engine APIs under /api/tools/ (rule 8); for everything else the frontend calls a route of the app (routerAdd) that checks who the user is and then uses the tool.
3. File paths start from the app folder (the one that contains app.json), for example "release/seed/parts.xlsx". A path that leaves the folder is refused, and so is one that goes through a symbolic link or a Windows junction inside the folder, even if it leads back inside: the only exception is the state folder itself, which IT may move to another disk with a junction.
4. Results and errors. The functions return plain JS objects (they can be passed to e.json as they are). Errors arrive as exceptions with the message in English (the main language of the engine: messages for app developers are not translated): handle them with try { … } catch (err) { … err.message … }; if you don't, the route answers 400 with the same message. If the app has to show them to a person, it writes its own message in the language of the app.
5. The app to work on. In hooks, routes and cronAdd jobs, the functions that write to the database use the current $app on their own, which PocketBase makes the same as e.app in hooks and routes, even when the hook runs inside a transaction: there is nothing to pass, and { app: e.app } in the options gives the same result. The app must be passed in the options in two cases: in migrations, always { app: app } (there it is required, because the migration keeps its own transaction open), and inside runInTransaction((txApp) => { … }), with { app: txApp }: with another app the write would wait for the transaction.
6. Tools write as the server, not as the user: the API rules of the collections don't apply to writes. From scripts, checking who may start the operation is the job of the route that calls the tool; from the pages the engine does it with toolAccess (rule 8). When a tool reads data on behalf of a person (export, statistics) it reads it with the rules of the app instead, like the PocketBase APIs: the list rule of the collection, no hidden fields, emails only to whoever may see them, the onRecordsListRequest hooks of the app and the request limit of the collection (<collection>:list).
7. Coming. Tools marked “coming” are decided but not in the engine yet: $orch.<id> exists, but each of its functions answers that it isn't available. Don't build anything that depends on them.
8. From the app pages. The engine also offers the functions marked “from the app pages” as APIs on the app port, under the reserved prefix /api/tools/ (a package whose scripts register routes there can't be uploaded; an app that has them anyway, for example with the path built while the script runs, keeps them, and its pages can't use the tools until they move; the same for routes with a placeholder that also answer there, like /api/{coll}/{id}/approve: the routes of the apps go under /api/<id>/…). They are called with the app login (the token of the app user in Authorization, like the PocketBase APIs) and are closed by default: they need the tool in "tools" and the permission in "toolAccess". The request limit of the app applies here too (for example a rule on /api/tools/).
   • "toolAccess": { "<tool>": { "pages": true, "minRole": "editor", "destructiveRole": "admin", "collections": ["parts"], "recipes": ["Parts price list"], "maxRows": 5000, "guests": false } }. pages opens the tool to the pages; minRole is the minimum role (from the roles in auth.roles, from the most to the least powerful, read from the field auth.roleField; without minRole, being signed in is enough); the functions that remove data (effect destructive) also need destructiveRole, and without that entry nobody can use them; collections are the only collections the tool works on from the pages; recipes (import) the saved recipes the pages may use; snapshots (stats) the snapshots saved by the scripts that the pages may read; maxRows the maximum number of rows per call; guests: true opens to everyone, signed in or not and with any role, only the short read-only functions; uploading files, the jobs and the functions that write or remove data stay with the users of the app who have minRole. That's why guests and minRole go together: guests for reading, minRole for the rest. Without minRole the functions that write belong to anyone with a user of the app (with or without guests): in a public app those are the users that IT creates and the ones that the scripts of the app create (see “Users and roles in public apps”, below); if the scripts of the app create users, there minRole is needed. In apps with login guests isn't valid (there whoever reaches the pages has already signed in through the engine). A wrong entry (a name that doesn't exist, a role that isn't in auth.roles, guests in an app with login) keeps the tool closed, with a message that says what to fix. The engine reads app.json again when it changes: there is no need to restart the app. In public apps, as soon as toolAccess uses minRole or destructiveRole, the engine protects the role field: with the app APIs nobody gives themselves or others a role more powerful than their own; IT gives the higher roles from the app panel.
   • Users and roles in public apps. Sign-up is decided by IT: no app has open sign-up, not even the public ones. The engine refuses (403, with a message in the person's language) every API request that creates a record in an auth collection (all of them except _superusers) unless it comes from a superuser of the app, that is from the panel the engine opens: also inside /api/batch, and with OAuth2 when the sign-in would create a new user. A createRule on an auth collection changes nothing: the engine blocks it and tells IT (a warning in “Upload app”, then an event at start-up and a line in the “Recommended checks” of the app page; for the users collection of an app with login, which the engine keeps closed anyway, only an information line); the schema of the app is not touched. So don't build sign-up pages: leave createRule at null. The users of a public app are created by IT from the app panel (the “Data” tab, one at a time: every user created, changed or removed there leaves an event with who did it, and from there nobody can sign in as a user of the app); if the app must let people in by itself (for example an invitation with a code), a script of the app does it with $app.save in its own route, which checks the code and gives the right role: there the engine doesn't step in, and it's up to that route not to become an open sign-up. In an onRecordAuthWithOAuth2Request hook the script can find or create the user itself and put it in e.record before e.next(); if e.record stays empty the sign-in is refused. In apps with login the users are the copy of the engine directory (groups and access in the console): there users can't be created even from the app panel.
   • GET /api/tools tells which tools the pages may use and, for each function, whether this person may call it (allowed, and if not reason): use it to show or hide the buttons.
   • POST /api/tools/<tool>/<piece> calls the function (method and path are in the catalog, entries method and path; the body is the JSON object described by inputSchema). Long functions (long) become a background job: the answer is 202 { job }; GET /api/tools/jobs/<id>?wait=1 gives the state (queued, running, finished, failed, cancelled, with processed, total, result, error, files) and waits up to 5 seconds for it to finish; DELETE /api/tools/jobs/<id> cancels it. One long (“heavy”) job at a time per app ("toolJobs": { "heavy": 2 } in app.json to have more, up to 4): the others wait in line. A job lasts at most one hour, then it stops (failed); if it doesn't stop by itself within two minutes (from the end of the hour or from the cancellation) the engine closes it and its place in the line is free again (data.tools.code: foundations.job_stuck or foundations.job_stuck_cancelled); its function may still finish in the background, though (for the functions that write, an event in the engine log says so), so check the data before trying again. The result of a job (result) is at most 8 MB in JSON: beyond that the job ends failed (foundations.result_too_big); for a lot of data use a function that produces a file to download. Jobs are kept in memory: when the app stops or restarts they are lost, even the ones halfway through; that's why a restart of the app asked by a change of its settings waits for the running page jobs to finish, and meanwhile the ones waiting in line don't start (the restart loses them). IT sees and cancels them in the console, tab “Tools for pages”, box “Jobs started from the pages” (GET /api/platform/apps/<app>/tools/jobs, DELETE …/tools/jobs/<id>). With 3 jobs of the app that the engine closed after the two minutes and that are still running in the background, new jobs are refused (503, foundations.jobs_stuck) until the app restarts.
   • Files: POST /api/tools/files (multipart, first the field tool, then file) puts them in the file store of the app for that tool and answers { id, name, size, expires }; extensions and size are decided by the tool (entry files of the catalog). A file belongs only to whoever uploaded it (for the others it doesn't exist: 404), stays two hours after the last use and is lost if the app restarts. Each person keeps at most 10 files and 500 MB: beyond that their oldest files are removed, and if those are still in use the new file is refused. The files produced by a tool are downloaded with a token: POST /api/tools/files/<id>/token gives url, valid for 10 minutes and only once (at most 5 are valid at the same time for a file: a new one takes the place of the oldest), to use in a normal <a href>; the answer is an attachment, never a page. A HEAD request on the same address (some download managers make one) doesn't use up the token.
   • Errors are PocketBase answers (status, message in the language of the page, data.tools.code with the key of the message). Every operation from the pages that writes or exports (also a read-only function that produces files to download) leaves an event in the engine log (category “strumenti”). In apps with sign-in the gate refuses (403, foundations.cross_origin) the requests that write (POST, PUT, PATCH, DELETE, for all the APIs of the app and not only for the tools) started by the browser from another origin: from another app of the engine, from another site or from a sandboxed page. The pages of the app towards its own port, the portal of the engine and programs outside the browser pass normally.
   • The engine serves a small helper for the pages, /__platform/tools.js, which does all this with the app token (PyriteTools.list, call, run, upload, job, waitJob, cancelJob, download):

<script src="/__platform/tools.js"></script>
<script>
  async function loadPriceList(input) {
    const file = await PyriteTools.upload("import", input.files[0]);
    const trial = await PyriteTools.run("import", "jobs", { file: file.id, recipe: "Parts price list", dryRun: true },
      { onProgress: (job) => console.log(job.processed + " / " + job.total) });
    if (trial.status !== "finished" || trial.result.rejected > 0) {
      return trial;
    }
    return PyriteTools.run("import", "jobs", { file: file.id, recipe: "Parts price list" });
  }
</script>

1. Concurrent changes (for the pages, it isn't a tool). The engine protects every PocketBase app from concurrent changes, out of the box: every record of the API has the "$version" entry (and reading a single record also the ETag header), and every change (PATCH, also inside /api/batch) declares the version it changes, with the If-Match header or with "$version" in the body. If the record was changed in the meantime the answer is 409, with who and when in data["$version"]; without a version it's 428. The engine puts the /__platform/concurrency.js script in the app's pages by itself: it adds If-Match to the changes of the records the page has read and shows a notice with “Reload”; the safest way is still to save the object you read, with the changes, because it carries its own "$version". In app.json, "concurrency": "strict" (default), "warn" (never refuses: changes without a version or with an old version go through and are counted in the engine's events, so IT sees what strict would refuse), "off". The app's scripts ($app.save, e.app.save) and the engine console aren't subject to it. Good to know: with fields add $version (for example fields: "id,title,$version"), otherwise the entry isn't there and in strict the change gets 428. The script first remembers the versions of records read one at a time and of saves; the ones from lists, realtime and sign-in answers it uses only when it has none of those: a page that changes records from a list that updates by itself (a table with inline editing, a kanban) must send in the body the "$version" of the record it shows (or call pyriteConcurrency.remember(record) when it shows it), otherwise after the first save of the same record it may get 409 even though it already shows the new data. In "warn" the script works as in strict, but no change is stopped. The pb.authStore record isn't enough to change the profile: in apps with sign-in it comes from the engine's single sign-on without "$version" (in strict the change gets 428), in public apps it has the version of the moment of sign-in; before changing it, read it again (getOne or authRefresh) and save the object you read. The engine opens its transaction only around the write to the database, after the app's hooks and after the upload of files; in hooks write with the hook's app (e.app) anyway, which inside a /api/batch batch is a transaction.

The complete catalog, always in line with the engine in front of you, is at GET /api/platform/tools (only with a console key: Owners and Admins) and on the “Tools” page of the console: for each function it also gives the effect (effect: read, write, destructive), whether it is long, whether it works from the pages and the JSON schemas of input and output (inputSchema, outputSchema). This chapter is printed with pyrite.exe tools --md (--lang it for Italian).

Change history (history)

Keeps track of who changed what and when in the app's records, with the value before and after, and brings back an earlier version (even of a deleted record).

Turn it on with "tools": ["history"] in app.json. Category: Data.

Functions

$orch.history.list(collection, id?, options?)

The history entries of a record (id), or of a whole collection with an empty id, from the newest. Options: limit (from 1 to 500, 50 if missing), offset (entries to skip).

Returns: { collection, record, total, items: [{ id, collection, record, action, changes: { field: { before, after } }, by, byName, person, source, restoredFrom, created }] }.

changes has only the fields that changed: before and after for a change, only after for a creation, only before for a deletion. source is api, script, import or console; person is the person of the engine directory, when known.

routerAdd("GET", "/api/requests/{id}/history", (e) => {
  const rec = e.app.findRecordById("requests", e.request.pathValue("id"));
  if (!e.auth || rec.getString("owner") !== e.auth.id) throw new ForbiddenError("Not yours");
  return e.json(200, $orch.history.list("requests", rec.id, { limit: 20 }));
}, $apis.requireAuth());

From the app pages: POST /api/tools/history/list (effect: read only), with the permissions of toolAccess in app.json.

$orch.history.restore(entryId, options?)

Brings the record back to the values of that version (just after that change; for a deletion: the record as it was, with the same id). It leaves a new “restore” entry and an event. File fields aren't restored (the old files are gone). Options: by (who restores, for the history), app.

Returns: { id, entry, collection, record, action: updated | recreated | unchanged, restored, skipped }.

id is the new history entry (empty if nothing changed); skipped lists the fields left as they are.

const r = $orch.history.restore(entryId, { by: e.auth.getString("name"), app: e.app });

From the app pages: POST /api/tools/history/restore (effect: writes data), with the permissions of toolAccess in app.json.

Engine HTTP APIs

The app doesn't call them: they serve the console and the portal. They're listed so that the app doesn't rebuild them.

How to use it

When to use it. When the app must be able to say who changed a record, when, and what was there before: requests with an approval flow, shared master data, prices, deadlines. Don't write a history by hand in hooks: the engine records every creation, change and deletion inside the same transaction as the save, from any path (app pages, scripts, import, console).

Configuration in app.json, with the tool on:

"tools": ["history"],
"history": { "collections": ["requests", "suppliers"], "retentionDays": 365 }

What is recorded. For every save an entry with: the action (create, update, delete, restore), who (by: the id of the app user; byName: their name or email; person: the id of the person in the engine directory, when known, see “The person” below), when (created), from where (source: api for the app's pages, script, import, console) and the changed fields in changes, with the value before (before) and after (after). For a change made from the Data tab of the console, source is console, byName is the name (or the email) of the console person who made it and person their id in the engine directory: the engine tells it to the app, signed, and a page can't fake it. A change that changes nothing recorded leaves no entry. Never recorded: passwords, tokenKey, fields with the name of a secret (with “password”, “secret”, “token”, “credential” in the name, or with the word “key”, “pin”, “otp”, “hash”, “salt”: for example api_key, apiKey, pin_code), the ones in exclude and the automatic dates (created, updated: the when is the entry's); for file fields only the file names. Fields hidden from the API (Hidden) only if listed in includeHidden. The entry is written in the transaction of the write alone, after the app's hooks and after the upload of files.

The person (person). It's the person of the engine directory behind the app user. It's there for changes made from the pages (and from the page tools: the Excel and CSV import and the photo import, with their undo, where source is import and by is who started them) by a user the engine has linked to a person: with single sign-on or with the “Access and roles” tab. The engine tells the app which person is behind each user: when the person signs in, when they get a role and every time the app starts. The app remembers it after a restart too, in an internal table that the API and the pages can't see. A page can't fake it or change it: it doesn't come from a header or a field of the request, but from the user the token says. It's empty for a user created by the app itself, for guests and for saves made by scripts and by imports started from the console or from a script, which have no user (by is empty too). A person deleted from the directory no longer appears in new entries (running apps know it at once, stopped ones at their next start); the entries already written keep their id, because the history is never rewritten. A disabled person stays linked: they can't sign in, and if they become active again they are still themselves.

Where the history is. In the system collection orch_history of the app, created by the engine: it travels with the backups and can't be seen from the API (not even by superusers) or in the Data tab. Don't touch it and don't create it in migrations. In the engine console the app page has the “History” tab: the latest changes and the history of a record.

Restore. $orch.history.restore(entryId) brings the record back to the values of that version: the ones just after that change (for a delete entry: the record as it was before being deleted, created again with the same id). The save goes through the validations and the record hooks and leaves a restore entry in turn (with restoredFrom), plus an engine event. File fields aren't restored, because the earlier files are gone: they stay as they are and are listed in skipped, like the fields the collection doesn't have any more.

Rules for this tool:

1. Scripts read the history as the server does: the route that shows it to a person checks by itself that the person may see that record (the API rules of the collections don't apply here) and doesn't show them the fields they can't see. From the app pages the engine does it by itself (below, “From the app pages”).
2. A restore from a script is a real save, made as the server (without the API rules or the request hooks): do it only from a route that checks who asks for it and what they may change, and pass by with the person's name, which stays in the new entry.

Example: the history of a request, only for whoever created it.

routerAdd("GET", "/api/requests/{id}/history", (e) => {
  const rec = e.app.findRecordById("requests", e.request.pathValue("id"));
  if (!e.auth || rec.getString("owner") !== e.auth.id) throw new ForbiddenError("Not yours");
  return e.json(200, $orch.history.list("requests", rec.id, { limit: 20 }));
}, $apis.requireAuth());

Example: bringing a request back to an earlier version, only for the manager (role manager).

routerAdd("POST", "/api/history/{entry}/restore", (e) => {
  if (e.auth.getString("role") !== "manager") {
    throw new ForbiddenError("Only the manager can restore a version.");
  }
  const result = $orch.history.restore(e.request.pathValue("entry"), { by: e.auth.getString("name"), app: e.app });
  return e.json(200, result);
}, $apis.requireAuth("users"));

From the app pages (rule 8 of the common rules), without writing routes: the tool in "tools" and a permission in app.json.

"toolAccess": {
  "history": { "pages": true, "minRole": "editor", "collections": ["requests"], "maxRows": 100 }
}

• POST /api/tools/history/list { collection, record, limit, offset } → { collection, record, total, items, $version }: the history of one record as the person sees it. The viewRule of the record applies as for them (404 if they can't see it, and for a deleted record); the entries keep only the fields they would see reading the record from the API now (no hidden fields, no fields the app hides with hide() in onRecordEnrich, no emails they can't see), and byName never shows the email of other people (and to guests, with guests, not even person, the id of the person in the engine's directory). $version is the version of the record now, to send back with restore. At most maxRows entries per call (default 50, never more than 500); only the collections in collections.
• POST /api/tools/history/restore { entry, $version } (or the If-Match header) → the result of restore, with $version of the record after the restore. It's a real change made by the person: the updateRule with the restored values in @request.body (a value the rule doesn't allow them: 404, as the API), the manage rule of auth collections, the request hooks of the app, the number that pages can't change (it stays, in skipped) and the protection from concurrent changes: in strict mode (the default) without a version 428, with an old version 409, the same errors as a PATCH; in warn a restore without a version goes through and counts among the changes without a version, like a PATCH. Only the fields the person sees are brought back (as in list): the ones they don't see stay as they are and don't appear in restored or skipped. Effect write: it needs a user of the app and, if there is one, minRole (never guests). A deleted record can't be created again from the pages (404: the console and the scripts can). The event in the engine log is the one of the history (with who restored).

Example: the history of a request in its page, and a version brought back.

<script src="/__platform/tools.js"></script>
<script>
  async function showHistory(id) {
    const h = await PyriteTools.call("history", "list", { collection: "requests", record: id, limit: 20 });
    return h; // h.items: who changed what and when; h["$version"]: the version to send back with restore
  }
  async function restore(entry, version) {
    try {
      return await PyriteTools.call("history", "restore", { entry: entry, $version: version });
    } catch (err) {
      if (err.status === 409) {
        alert(err.message); // someone changed the request in the meantime: show the history again
      }
      throw err;
    }
  }
</script>

Excel and CSV export (export)

Creates from the records of a collection (with filters and order) an Excel or CSV file to download, with columns, numbers and dates in the right format, or fills an Excel template already laid out.

Turn it on with "tools": ["export"] in app.json. Category: Data.

Functions

$orch.export.csv(spec)

A CSV file from the records of a collection (or from the rows given by the script): separator of your choice, quotes as in RFC 4180, numbers and dates written with the profile (italian: ; and comma in the decimals, dd/mm/yyyy, UTF-8 with BOM; international: , and point, ISO dates). A text cell that starts with = + - @ gets an apostrophe in front (formula injection). From scripts it reads as the server, at most 1,000,000 rows; the file goes to the app's store (or to state/ with path).

Returns: { file: { id, name, size, expires, tool, downloadable }, path, name, size, format, rows, sheets: [{ name, collection, rows }] }.

file is the file in the store: send it with $orch.export.send, or give it to a person with { for: e.auth } (the page downloads it with PyriteTools.download). With path, path is where the file is.

routerAdd("GET", "/api/orders/export.csv", (e) => {
  // scripts read AS THE SERVER (no API rules): the filter decides what this person gets
  const r = $orch.export.csv({
    collection: "orders",
    filter: "owner = {:me} && status = {:status}",
    params: { me: e.auth.id, status: "paid" },
    sort: "-created",
    columns: [
      { field: "code", title: "Order" },
      { field: "created", title: "Date", format: "date" },
      { field: "total", title: "Total", format: "number", total: "sum" },
    ],
    profile: "italian", // ; between the fields, comma in the decimals, dates dd/mm/yyyy
  });
  return $orch.export.send(e, r);
}, $apis.requireAuth("users"));

From the app pages: POST /api/tools/export/csv (effect: read only), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.export.xlsx(spec)

An Excel file written row by row (also a million rows): bold header with the colors chosen, column widths, Excel number and date formats, first row frozen, filters, more sheets (sheets), a totals row (columns[].total). The cells always hold values, never formulas from the data; the totals are SUBTOTAL formulas written by the engine, with the value already computed.

Returns: { file: { id, name, size, expires, tool, downloadable }, path, name, size, format, rows, sheets: [{ name, collection, rows }] }.

const r = $orch.export.xlsx({
  name: "orders-2026",
  sheets: [
    // scripts read AS THE SERVER: only this person's orders
    { name: "Paid", collection: "orders", filter: "owner = {:me} && status = 'paid'", params: { me: e.auth.id }, columns: [{ field: "code" }, { field: "total", format: "number", total: "sum" }] },
    { name: "Open", collection: "orders", filter: "owner = {:me} && status = 'open'", params: { me: e.auth.id }, columns: [{ field: "code" }, { field: "total", format: "number" }] },
  ],
  header: { fill: "#1F4E79", color: "#FFFFFF" },
  for: e.auth, // the file goes to this person's store: the page downloads it with PyriteTools.download(r.file.id)
});

From the app pages: POST /api/tools/export/xlsx (effect: read only), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.export.fill(template, data, options?)

Fills an .xlsx template of the app: the cells with only a placeholder take the value as it is (numbers stay numbers, dates stay dates), the rows go into the Excel table with that name, which grows; totals, charts and pivot tables of the template are updated when the file is opened.

Returns: { file: { id, name, size, expires, tool, downloadable }, path, name, size, format, rows, sheets: [{ name, collection, rows }], missing }.

missing lists the placeholders of the template without a value (left empty).

const r = $orch.export.fill("monthly-report.xlsx", {
  values: { title: "Orders of March", month: "2026-03-01", target: 25000 },
  // scripts read AS THE SERVER: the filter decides which orders go in the report
  tables: { Data: { collection: "orders", filter: "created >= '2026-03-01' && created < '2026-04-01'", sort: "created" } },
}, { name: "orders-march" });
return $orch.export.send(e, r);

From the app pages: POST /api/tools/export/fill (effect: read only), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.export.send(e, result)

Sends the file of the result as a download (attachment, no cache) from the route of a script; a file of the store made by a script without “for” is removed after sending. Use it as the last thing of the route: return $orch.export.send(e, result).

Returns: null.

return $orch.export.send(e, $orch.export.csv({ collection: "parts" }));

$orch.export.templates()

The templates of the app in one list with their origin: those of the app package (release/templates/) and those uploaded by IT from the console (state/export/templates/, which replace a template of the package with the same name), with tables and placeholders.

Returns: [{ name, origin, size, modified, overrides, tables, placeholders, error }].

const names = $orch.export.templates().filter((t) => !t.error).map((t) => t.name);

From the app pages: POST /api/tools/export/templates (effect: read only), with the permissions of toolAccess in app.json.

$orch.export.recent(options?)

The exports of a person still to download (jobs and files of the last 2 hours); without “for”, the files made by the scripts.

Returns: [{ job, fn, status, rows, error, createdAt, finishedAt, files: [{ id, name, size, expires }] }].

const mine = $orch.export.recent({ for: e.auth });

From the app pages: POST /api/tools/export/recent (effect: read only), with the permissions of toolAccess in app.json.

Engine HTTP APIs

The app doesn't call them: they serve the console and the portal. They're listed so that the app doesn't rebuild them.

How to use it

When to use it. Whenever people want the app's data in a spreadsheet: the orders of the month in Excel, a CSV for another program, a report already laid out with the logo and the charts. Don't write CSV or Excel files by hand in the scripts and don't bring libraries into the pages: the engine writes the file (even a million rows without keeping them in memory), with numbers and dates in the right format and with the defenses against formulas hidden in the data.

What to export (the spec of csv and xlsx):

format of a column: text, integer, number (two decimals), percent, date, datetime, time, bool, or an Excel number format written in full ("#,##0.000", "0.0%", "dd/mm/yyyy hh:mm"); in the CSV the decimals come from the format. Without format the field decides: a number field gives numbers, a date field gives dates, a text field stays text even when it looks like a number. A date at exactly midnight UTC is a date without a time and stays that day; the others are written in the time zone of the export. In the rows given by a script, a text with an ISO date ("2026-01-31", and JavaScript dates, which arrive like that) counts as a date. total: "sum", "count" (the filled cells) or "avg", in a totals row at the end.

The CSV. Quotes as in RFC 4180 (a field with the separator, quotes or a line break goes between quotes, and the quotes inside are doubled), lines ending with CRLF. A text cell that starts with =, +, -, @, a tab or a carriage return gets an apostrophe in front: so a spreadsheet doesn't take it for a formula (the OWASP “CSV injection”). Numbers stay numbers, negative ones too, written without thousands separators; yes/no values are VERO/FALSO in the Italian profile (the words that Excel in Italian reads as logical values) and true/false in the international one. A CSV has one table only: for more sheets use xlsx.

The Excel file. Rows are written one at a time (the excelize StreamWriter: above 16 MB they go to a temporary file in a folder of the job inside state/tmp, removed at the end of the job or, if the app stops halfway, when it starts again), with the header in bold and the colors chosen, the column widths (width, or estimated from the first rows), the Excel number and date formats, the first row frozen and the filters. The cells always hold values: a text that starts with = stays a text. The engine writes the totals row with a SUBTOTAL formula over the data rows (109 sum, 103 count, 101 average) and with the value already computed: the total stays right if someone corrects a value, and it follows the filters. An Excel sheet holds 1,048,576 rows: with the cap of 1,000,000 per export it never fills up, and if it did the export would be refused instead of splitting the data over another sheet.

Templates (fill). A template is an .xlsx workbook already laid out that the app fills. How to prepare it in Excel:

1. Write {{name}} in the cells that take a value (values.name; with {{customer.name}} an entry inside an object). A cell with only the placeholder takes the value as it is: a number stays a number, a date stays a date with the cell's format (or with the profile's if the cell is “General”), a yes/no stays yes/no; a cell formatted as Text writes it as text. Inside a longer text (“Orders of {{month}}”) the value becomes text. A placeholder without a value is emptied and listed in missing.
2. Put the rows in an Excel table (Insert › Table) and give it a name (Table Design › Table Name, for example Data). Leave the header and one sample row: its style is copied to all the rows, and a column with a formula (a calculated column, for example =[@Quantity]*[@Price]) keeps it on all of them. Each column of the table takes the field with the same name as the header (ignoring case, spaces and _), or the column of columns with that title.
3. For the totals use the table's totals row or formulas with structured references (=SUM(Data[Total])): Excel writes them by itself when, while typing the formula, you click the table cells. They follow the table when it grows. The charts on the table rows, the defined names that cover them and the pivot tables made from the table grow too; the file asks for recalculation when opened and the pivot tables for a refresh when opened.
4. Save as “Excel Workbook (*.xlsx)”. Templates are refused with macros (.xlsm, vbaProject, Excel 4.0 macro sheets), with links to external files or data (other workbooks, data connections, web queries, OLE and ActiveX objects), with formulas that go to the internet or to other programs (WEBSERVICE, IMAGE, RTD, STOCKHISTORY, TRANSLATE, DETECTLANGUAGE, COPILOT, PY, CALL, REGISTER: the filled file asks for recalculation when opened and would send the table data out), over 20 MB or over 50 MB once unpacked (20 MB per part), with more than 500,000 cells or 200,000 rows once opened (Excel keeps, in each row, every column up to the last one used: no values or formatting far from the content) and with more than 1,000 example rows in a table. A template is filled with at most 100,000 rows (1,000,000 cells, 64 MB of text): for longer lists use xlsx. Two tables that share rows (side by side in the same sheet) are not filled: the rows one gains or loses would move the other; put them one under the other or in different sheets.
5. Make the template with example rows only, not starting from a real export. In the filled file the engine removes the copy of the data that pivot tables keep (the rows they were made from) and the values kept in the charts, which Excel reads again when opening; the labels and numbers a pivot table showed when you saved the template, however, stay written in the sheet until Excel refreshes it, that is when the person opens the file and enables editing. The conditional formatting and the data validation of the example row apply to the new rows too.

Where the templates are, in one list with their origin ($orch.export.templates()): those of the app package in release/templates/ (they come with a version of the app) and those uploaded by IT from the “Export” tab of the console, which go to state/export/templates/ (never into release/) and replace a template of the package with the same name until they are removed.

Where the file goes. From scripts, into the app's store (the same as the pages' files: it stays 2 hours and is lost when the app restarts): $orch.export.send(e, result) sends it as a download from the route and then removes it; with { for: e.auth } it stays with the person, who downloads it from the page with PyriteTools.download(result.file.id). With { path: "state/exports/…" } it goes to disk, inside state/ (release/ and the rest of the folder stay read-only).

From the app's pages (rule 8 of the common rules), without writing routes: the tool in "tools" and a permission like this in app.json.

"toolAccess": {
  "export": { "pages": true, "minRole": "viewer", "collections": ["orders", "customers"], "maxRows": 50000 }
}

• POST /api/tools/export/csv and POST /api/tools/export/xlsx with the spec (without rows, path, for, maxRows) and POST /api/tools/export/fill { template, values, tables: { Data: { collection, filter, params, sort, columns } }, name, profile } → 202 { job }. The job is in the common queue (one heavy job at a time per app, unless "toolJobs"), lasts at most 10 minutes and leaves the file in the job's files; it is downloaded with the token (PyriteTools.download) for 2 hours.
• The reading is always the person's, with the app's rules: the list rule of the collection, the filter with their rights, the onRecordsListRequest hooks, hidden fields and emails as in the API. Only what the person already sees in the app is exported, and only from the collections in collections; hidden fields can't be asked for. When the job starts (even much later, if it was queued) the person is read again: if meanwhile the user was removed, changed the password or lost the minRole role, the job doesn't start. The rows come from the same snapshot of the data, each one once, even if meanwhile someone adds or removes records.
• At most maxRows rows (without it: 100,000; for a template at most 100,000): beyond that the export is refused with the number, it is not cut. The count is the one of the rows the person sees with the filter.
• POST /api/tools/export/templates {}: the app's templates, with tables and placeholders. POST /api/tools/export/recent {}: my exports still to download.
• Every export leaves an event in the engine registry, with who made it, the file and how many rows; failed or cancelled ones too (also while queued). The exports made by IT from the console and the templates uploaded or removed show in the history of the “Export” tab.

Rules for this tool:

1. From scripts the engine reads as the server: no rules, hidden fields too if you list them. Before exporting for a person from a route, check yourself what they can see, or leave the export to the pages (toolAccess), which read with their rules. The users' password and token key are never exported.
2. send writes the response of the route: use it as the last thing, return $orch.export.send(e, r), with no e.json after it.
3. A big export from the pages is a job: use PyriteTools.run, which waits for the end, then PyriteTools.download with the file id.
4. In templates, base the formulas on the table's structured references, not on ranges written by hand: they grow with the table.

Example: a CSV for another program, with commas and ISO dates, saved every night in state/.

cronAdd("export-orders-csv", "30 2 * * *", () => {
  $orch.export.csv({
    collection: "orders",
    filter: "created >= {:from}",
    params: { from: new Date(Date.now() - 86400000).toISOString().slice(0, 10) },
    columns: [{ field: "code" }, { field: "created", format: "datetime" }, { field: "total", format: "number" }],
    profile: "international",
    path: "state/exports/orders-yesterday.csv",
  });
});

Example: the “Export to Excel” button in a dashboard page of the app, with /__platform/tools.js (the person downloads their orders, with the app's rules).

<button id="excel">Export to Excel</button>
<span id="status"></span>
<script src="/__platform/tools.js"></script>
<script>
  document.getElementById("excel").addEventListener("click", async () => {
    const status = document.getElementById("status");
    try {
      const job = await PyriteTools.run("export", "xlsx", {
        collection: "orders",
        filter: "status = 'paid'",
        sort: "-created",
        columns: [
          { field: "code", title: "Order" },
          { field: "created", title: "Date", format: "date" },
          { field: "total", title: "Total", format: "number", total: "sum" },
        ],
        name: "paid-orders",
      }, { onProgress: (j) => (status.textContent = j.processed + " / " + j.total) });
      if (job.status !== "finished") throw new Error(job.error || job.status);
      await PyriteTools.download(job.files[0].id);
      status.textContent = "";
    } catch (err) {
      status.textContent = err.message;
    }
  });
</script>

Images (images)

Photos without external programs: reduces, compresses, converts to JPEG or PNG, straightens phone photos (the EXIF orientation applied to the pixels), removes the location and the personal data of the EXIF while keeping the color profile. A rule per field in app.json does it by itself on every upload; bulk import of photos from a folder or a zip, with dry run, duplicates and undo; a photo picker for the app's pages.

Turn it on with "tools": ["images"] in app.json. Category: Files.

Functions

$orch.images.info(source)

Information about a photo without decoding it: format, size as stored and as seen, EXIF orientation, whether it has the location (GPS), the EXIF, a color profile or other metadata, the estimated JPEG quality. The source is a path in the app folder or the file of a record ({ collection, id, field, name } or { record, field }: without name the first new photo of the field, then the first saved one).

Returns: { format, width, height, displayWidth, displayHeight, orientation, needsStraightening, hasGps, hasExif, hasColorProfile, colorProfile, hasOtherMetadata, alpha, quality, bytes, camera, taken, maxPixels }.

const i = $orch.images.info({ record: e.record, field: "photo" });
if (i.needsStraightening || i.hasGps) console.log("the photo will be straightened and cleaned");

From the app pages: POST /api/tools/images/info (effect: read only), with the permissions of toolAccess in app.json.

$orch.images.process(source, target, options?)

Works a photo and writes the result in the target: a path in state/ or the file field of a record ({ collection, id, field }, or { record, field, save: false } to set it without saving). Options: { maxSide, width, height, mode: fit | fill | crop, crop: { x, y, width, height }, quality, format: keep | jpeg | png, straighten, stripGps }. Without options the photo stays as it is; sizes never grow; JPEG and PNG get back the color profile and a clean EXIF.

Returns: { format, width, height, bytes, changes, path | record, field, name }.

$orch.images.process("release/seed/logo.png", "state/public/logo.jpg",
  { maxSide: 800, format: "jpeg", quality: 80, straighten: true, stripGps: true });

From the app pages: POST /api/tools/images/process (effect: read only), with the permissions of toolAccess in app.json.

$orch.images.resize(source, target, options)

Like process, to change the size: maxSide (the longest side), width and height (a box), mode fit (inside the box, the default), fill (exactly the box, cutting the edges) or crop (the rectangle in crop, on the photo as it is seen). It never enlarges.

Returns: { format, width, height, bytes, changes, path | record, field, name }.

$orch.images.resize({ record: e.record, field: "photo" }, { record: e.record, field: "thumb", save: false },
  { mode: "fill", width: 300, height: 300 });

From the app pages: POST /api/tools/images/resize (effect: read only), with the permissions of toolAccess in app.json.

$orch.images.compress(source, target, options?)

Like process, recompressing a JPEG at quality (default 75). A JPEG already compressed as much, or a result that isn't lighter, stays as it is: the photo doesn't get worse at every pass. PNG is lossless: to make it lighter, convert it to JPEG.

Returns: { format, width, height, bytes, changes, path | record, field, name }.

const r = $orch.images.compress("state/upload/big.jpg", "state/upload/small.jpg", { quality: 70 });

From the app pages: POST /api/tools/images/compress (effect: read only), with the permissions of toolAccess in app.json.

$orch.images.convert(source, target, options)

Like process, to change the format: format jpeg or png (WebP comes later). Transparent pixels become white in a JPEG.

Returns: { format, width, height, bytes, changes, path | record, field, name }.

$orch.images.convert("state/scan/page.tif", "state/scan/page.png", { format: "png" });

From the app pages: POST /api/tools/images/convert (effect: read only), with the permissions of toolAccess in app.json.

$orch.images.straighten(source, target)

Turns the pixels as the EXIF orientation says and sets it to 1: the photo is straight for every program, even those that don't read the tag. A photo already straight stays as it is.

Returns: { format, width, height, bytes, changes, path | record, field, name }.

$orch.images.straighten({ collection: "cars", id: id, field: "photo" }, { collection: "cars", id: id, field: "photo" });

From the app pages: POST /api/tools/images/straighten (effect: read only), with the permissions of toolAccess in app.json.

$orch.images.stripGps(source, target)

Removes the location and the personal data of the EXIF (GPS, XMP, IPTC, serial numbers, owner, maker notes, thumbnail, extra images or video after the photo), keeping camera, date, exposure and the color profile. When the pixels don't change, the photo is rewritten without losing quality.

Returns: { format, width, height, bytes, changes, path | record, field, name }.

$orch.images.stripGps("state/upload/visit.jpg", "state/upload/visit.jpg");

From the app pages: POST /api/tools/images/strip-gps (effect: read only), with the permissions of toolAccess in app.json.

$orch.images.fingerprint(source)

The SHA-256 of the bytes (identical files) and the perceptual fingerprint, 16 hexadecimal digits: two nearly identical photos (resized, recompressed, turned) differ in few bits.

Returns: { sha256, phash }.

const a = $orch.images.fingerprint("state/a.jpg"), b = $orch.images.fingerprint("state/b.jpg");

$orch.images.duplicates(sources, options?)

Groups of identical photos (same SHA-256) and of nearly identical ones (fingerprints at most 10 bits apart, or distance in the options), at most 1000 photos. Photos that can't be read are in errors.

Returns: { identical: [[source]], similar: [[source]], errors: [{ source, reason }] }.

const d = $orch.images.duplicates(["state/a.jpg", "state/b.jpg", "state/c.jpg"]);
if (d.similar.length) console.log("nearly identical: " + JSON.stringify(d.similar));

$orch.images.import(path, options)

Loads photos in bulk from a folder of the app or a .zip (at most 5000 files): mode new (one photo = one new record; keyField gets the file name, values the other fields) or match (the photo goes to the record whose keyField is the file name without extension, ignoring upper and lower case; separator cuts the name: ART001_2.jpg with “_” is ART001). The images rule of the field applies. dryRun says what would happen to each photo, with identical and nearly identical photos; the real load goes in the registry of loads and can be undone. Options by and app as in $orch.import.load.

Returns: { id, source, collection, field, mode, dryRun, photos, created, attached, skipped, rejected, duplicates, identical, similar, items: [{ name, code, action, record, reason, sameAs }], ruleApplied, duration }.

action of each photo: create, attach, replace, skip, duplicate (identical to sameAs, not loaded), reject (with reason). Replaced photos don't come back with undo.

const trial = $orch.images.import("state/incoming/photos.zip",
  { collection: "products", field: "photo", mode: "match", keyField: "code", dryRun: true });
if (trial.rejected === 0) $orch.images.import("state/incoming/photos.zip",
  { collection: "products", field: "photo", mode: "match", keyField: "code", app: e.app });

From the app pages: POST /api/tools/images/import (effect: writes data), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.images.undo(id, options?)

Undoes a photo load (the id in the result of import): removes the records it created and takes the new photos off the records it updated. Options by and app.

Returns: { id, file, collection, read, created, updated, errors, errorCount, warnings, duration }.

$orch.images.undo(load.id, { by: e.auth.email(), app: e.app });

From the app pages: POST /api/tools/images/undo (effect: removes data), as a background job (202 with the job), with the permissions of toolAccess in app.json.

How to use it

When to use it. Every time an app receives photos: photos of vehicles, of products, of site visits, avatars. Don't add libraries and don't ask for programs on the server: the engine reduces, compresses, converts, straightens and cleans photos by itself. In most apps no code is needed: a rule per field in app.json is enough, and every photo uploaded in that field comes out right, whatever way it arrives.

Why phone photos come out turned. The camera sensor is mounted one way only. When you turn the phone to take a portrait photo, the phone doesn't turn the pixels: it saves them as the sensor read them (lying on their side) and writes a tag in the photo, the “EXIF orientation”, which says “to see it straight, turn it by 90°”. The phone, the gallery and today's browsers read the tag and show the photo straight; many other programs (management software, PDFs, emails, prints, previews of some servers, old programs) don't read the tag and show the photo on its side or upside down. Straightening (straighten) really turns the pixels and sets the tag to 1: the photo is straight for everybody. The same photos often carry the location (GPS: where the photo was taken, within a few meters) and other personal data (serial number of the phone, name of the owner, sometimes a video or a second photo attached after the first one): stripGps removes them and keeps what is useful (camera, date, exposure, color profile).

The rule of a field. With the tool active, in app.json:

"tools": ["images"],
"images": {
  "products.photo": { "maxSide": 2560, "quality": 85, "format": "jpeg", "straighten": true, "stripGps": true, "keepOriginal": "photo_original" },
  "people.avatar": { "mode": "fill", "width": 400, "height": 400 }
}

• The rule applies by itself on every upload in the field: the app's APIs (pages, PocketBase SDK), the Data tab of the console, bulk import, scripts that save a record with a photo. The size check of the field (maxSize) applies to the photo already reduced: a 12 MB photo in a 5 MB field gets in, if it fits once reduced (the body of the request stays within PocketBase's limits: maxSize times the number of files plus 32 MB). The keepOriginal field has its own checks: give it a maxSize large enough.
• Without losses when possible: if the pixels don't change (photo already straight and already small) and there's only the location to remove, the photo is rewritten with the same image data and clean metadata. When the pixels change, the photo is rewritten in JPEG or PNG with the color profile (ICC, for example the Display P3 of iPhones) and a clean EXIF put back by hand.
• It doesn't get worse at every pass: it reduces only larger photos, recompresses only JPEGs heavier than the requested quality, and if recompressing doesn't make the photo lighter it keeps it as it was. Files that aren't photos pass as they are, GIFs too (unless a format is requested: then the first frame); with stripGps GIFs lose XMP (which can hold the location), program data and comments, without touching the frames.
• Refused with a validation error on the field, in the person's language: HEIC and AVIF (see below), damaged photos, photos too large for the memory of the app. The code of the error is validation_images_<reason> (for example validation_images_heic), the message says what to do.
• A badly written rule (an entry that doesn't exist, a value out of range, a keepOriginal that isn't a file field) refuses the uploads in that field with the reason until it is fixed: better an error than a photo with the location that had to be removed. Without "images" in "tools" the rule doesn't apply (the worker writes it in the log).
• A change of the record that doesn't touch the field doesn't work the photo. The protection from concurrent changes ($version, If-Match) checks the version first: with an old version the photo isn't even decoded.

HEIC. iPhones take photos in HEIC (or HEIF) by default, a format the engine can't read (there is no free library in pure Go). A HEIC photo is recognized by its content (not by the extension) and refused with a clear message: on the iPhone Settings › Camera › Formats › “Most Compatible” (new photos will be JPEG), or export the photo as JPEG and upload it again. The picker of the pages (/__platform/images.js) notices it before uploading and says the same. The same goes for AVIF.

Memory limits. Before decoding a photo the engine reads only its header (size and format) and compares it with the thumbnail limits of the app, which follow memoryLimitMB: the most pixels (at least about 52 million, the 48-50 Mpx photos of phones) and the estimated memory (a progressive JPEG or a 16-bit PNG need more). A photo over the limit is refused without decoding it, with the size in the message; a photo weighs at most 100 MB. Photos are worked a few at a time (as many as the thumbnails).

From scripts. The source is a path in the app folder or the file of a record: { collection, id, field, name? } or { record: e.record, field, name? } (without name: the first photo of the field not saved yet, if there is one, then the first saved one). The target is a path in state/ or the file field of a record ({ collection, id, field } saves the record; { record, field, save: false } puts the photo in the record without saving it, for example in onRecordCreateRequest before e.next()). The functions with options (process, resize, compress, convert) by default don't straighten and don't remove the location: ask for it with straighten: true, stripGps: true, or with the functions straighten and stripGps. The result says what changed in changes (straightened, resized, cropped, compressed, converted, locationRemoved, metadataCleaned; empty = the photo stayed as it was).

// pb_hooks/products.pb.js: a square thumbnail next to the photo, before saving
onRecordCreateRequest((e) => {
  if (e.record.getUnsavedFiles("photo").length) {
    $orch.images.resize({ record: e.record, field: "photo" }, { record: e.record, field: "thumb", save: false },
      { mode: "fill", width: 300, height: 300, straighten: true, stripGps: true });
  }
  e.next();
}, "products");

Bulk import. $orch.images.import(path, options) loads the photos of a folder of the app (up to 4 levels of subfolders) or of a .zip, at most 5,000 files:

• mode: "new": one photo = one new record; keyField gets the file name without extension (if a record with that code exists already the photo is skipped, and so are the other photos of the same batch with the same code: one record per code), values the values of the other fields;
• mode: "match": the photo goes to the record with the same code: ART001.jpg → the record with keyField = ART001, ignoring upper and lower case (accented letters too: caffè.jpg finds CAFFÈ; Mac file names, which write the accent apart, count like the others); with separator: "_" also ART001_2.jpg. In a field with one photo, a record that has one already is skipped (replace: true replaces it: the replaced photo is deleted and undo doesn't bring it back); in a multiple field the photo is added (up to maxSelect).
• dryRun: true says what would happen to each photo (items[].action: create, attach, replace, skip, duplicate, reject with reason), without saving anything, and finds the duplicates: identical byte by byte (identical, same SHA-256: with duplicates: "skip", the default, only the first one is loaded; in the mode match only an identical photo with the same code is skipped, because an identical one with another code goes to another record and is loaded, listed among the identical) and nearly identical (similar: the same photo reduced, recompressed or turned, with the perceptual fingerprint; only in the dry run, and they are loaded all the same).
• Every photo goes through the rule of the field. The real load goes in the import registry of loads (orch_imports, “Import” tab of the console) and is undone with $orch.images.undo(id) (removes the records created, takes the new photos off the records updated); the change history records it with source “import”.
• The dry run and the real load say the same thing: in the real load too a multiple field counts the photos it had before the batch plus those the batch attaches. Undo brings every record back to what it was before the whole batch.
• Zip: names that try to get out of the folder (“..”, absolute paths, drives) are refused and listed, an entry that expands too much to be a photo (zip bomb) is refused, and at most 4 GB are read in all. Before opening the zip its entries are counted: over 5,000 entries, or an index larger than 16 MB, the zip is refused without opening it. Files that aren't photos are listed as skipped.

const opz = { collection: "products", field: "photo", mode: "match", keyField: "code", dryRun: true };
const trial = $orch.images.import("state/incoming/photos.zip", opz);
console.log(trial.attached + " photos for existing products, " + trial.rejected + " rejected, " + trial.similar.length + " groups of similar photos");

From the app's pages (rule 8 of the common rules), without writing routes: the tool in "tools" and a permission in app.json.

"toolAccess": {
  "images": { "pages": true, "minRole": "editor", "destructiveRole": "admin", "collections": ["products"], "maxRows": 1000,
              "keyFields": ["products.code"] }
}

• POST /api/tools/files with tool = images and a photo (.jpg, .png, .gif, .webp, .bmp, .tif; HEIC is refused at once with the message) or a .zip (at most 500 MB).
• POST /api/tools/images/info { file }: like info.
• POST /api/tools/images/process { file, maxSide, width, height, mode, crop, quality, format, straighten, stripGps } (and resize, compress, convert, straighten, strip-gps): the worked photo as a new file of the deposit, to download with PyriteTools.download(result.file.id). They work only on the person's files: somebody else's file doesn't exist (404).
• POST /api/tools/images/import { file, collection, field, mode, keyField, separator, replace, duplicates, dryRun } → 202 { job }: the bulk import of a zip in a collection of collections, at most maxRows photos (without it: 500). The real load only after a successful dry run by the same person with the same zip and the same options; replace takes destructiveRole; no values from the pages. Who loads is written by id, as for the Excel import.
• The key field is chosen by whoever builds the app (keyFields, closed by default): from the pages keyField can be used only if "collection.field" is in the list toolAccess.images.keyFields, and the IT sees it in the summary of “Upload app” and in the tab “Tools for pages”. Without the list, from the pages the mode match is refused and the mode new works only without keyField (new records don't get the file name). Every entry of the list must be a text, number, email or web address field that isn't hidden or a system field, of a collection in collections. An entry of the wrong form (not "collection.field", or of a collection that isn't in collections) closes the whole tool to the pages, info and process included, like the other entries of toolAccess written wrong; an entry on a field that doesn't exist or of another type closes only the import from the pages, with a message that says what to fix, and writes it in the app's log too (at start and at every import refused). The console tab shows the entries as they are written: the app checks the type of the fields, with its schema. Scripts and superusers choose keyField freely.
• From the pages every photo follows the rules of the app for the person, as if they made the API calls themselves: the code is looked up only among the records the person sees (the listRule: a record they don't see isn't there, not even to say “it exists already”); a new record goes through the createRule and one to change through the updateRule, with in @request.body the fields the import really writes (the key field and the photo field). From the pages keyField is only a text, number, email or web address field that isn't hidden or a system field: the file name becomes its value, and with a relation or a yes/no the person would choose owner or status by themselves. The app's request hooks (onRecordCreateRequest…) don't run: the import saves as the server, in the collections the IT put in collections; if the app fills a field in a hook (the owner, for example), the records created from the pages don't have it.
• POST /api/tools/images/undo { id } → 202 { job }: undoes a photo load made from the pages, only with the destructiveRole of toolAccess.images (the undo of the Excel import doesn't touch photo loads).

The photo picker for the pages. /__platform/images.js (less than 10 KB, no libraries) puts in a page an area where to choose or drag photos, usable from the keyboard (Enter or Space open it), with small previews loaded only when they are seen (PocketBase thumbnails ?thumb=), the upload status read by screen readers and the texts in the person's language. With collection, record and field it uploads the photos to the record (with the version for the protection from concurrent changes: the rule of the field applies as always); without them, it gives the files to the page with onChange. HEIC is recognized before uploading. maxFiles (default 20, 1 without multiple) also counts the photos the record has already: the extra photos are not sent and the message stays written after the others are uploaded.

<div id="photos"></div>
<script src="/__platform/images.js"></script>
<script>
  PyriteImages.picker("#photos", { collection: "products", record: productId, field: "photo", multiple: true,
    onUploaded: (record) => console.log(record.photo) });
</script>

Import from Excel, CSV and JSON (import)

Reads Excel (.xlsx and the old .xls), CSV and JSON files the way Power Query does: recognizes columns and types (numbers in the Italian or English style, dates, codes with leading zeros, merged cells) and loads the rows into a collection of the app following a reusable “recipe”, or the whole file into several collections (one sheet per collection, even several collections from a single sheet, all or nothing). With a key, for example the license plate, a new import updates the records instead of duplicating them; every load can be undone.

Turn it on with "tools": ["import"] in app.json. Category: Data.

Functions

$orch.import.analyze(path, options?)

Reads the file and tells how it's made: columns with name, type and examples, the first rows as they are and as interpreted, warnings. It writes nothing. Use it to prepare the recipe. In the preview, texts are cut to 300 characters and the first rows show at most 200 columns.

Returns: { file, format, sheets, sheet, encoding, separator, suggestedSeparator, header, titleRow, firstDataRow, rows, blankRows, columns: [{ index, letter, name, title, field, type, wholeNumbers, values, blanks, examples, notes }], rawRows: [{ number, cells }], merges: [{ row1, col1, row2, col2 }], sample: [{ number, values: [{ type, text, number, date, bool }] }], warnings }.

const a = $orch.import.analyze("release/seed/parts.xlsx", { header: "titles" });
console.log(a.rows + " rows: " + a.columns.map((c) => c.name + " (" + c.type + ")").join(", "));

From the app pages: POST /api/tools/import/preview (effect: read only), with the permissions of toolAccess in app.json.

$orch.import.load(path, recipe, options?)

Loads the rows of the file into the recipe's collection. With { dryRun: true } it checks everything (conversions, field rules, keys) without saving anything. Rows with errors are discarded and described, the others are loaded in batches, each in a transaction. Options: dryRun, by (who started the load), app (the app the script has at hand). A recipe with parts (recipe.parts, like the one of plan) loads several collections in the right order, all or nothing: first it checks everything without writing, then it writes in a single transaction, at most 50,000 rows and 60 seconds (meanwhile the app's other writes wait: waitSeconds estimates it in the dry run).

Returns: { id, file, collection, dryRun, read, created, updated, unchanged, skipped, rejected, duplicates, errors: [{ row, column, field, value, reason, sheet, collection }], errorCount, warnings, duration, tables: [{ sheet, collection, read, created, updated, …, skippedReason }], rolledBack, waitSeconds }.

id is the number of the load: keep it to be able to undo it.

const recipe = {
  collection: "parts",
  options: { format: "excel", header: "titles" },
  fields: { "Code": "code", "Description": "description", "Price": "price" },
  key: ["code"],
};
const trial = $orch.import.load("release/seed/parts.xlsx", recipe, { dryRun: true, app: e.app });
if (trial.rejected === 0) {
  const result = $orch.import.load("release/seed/parts.xlsx", recipe, { by: e.auth.email(), app: e.app });
}

From the app pages: POST /api/tools/import/jobs (effect: writes data), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.import.undo(id, options?)

Undoes a real load, all or nothing: removes the records created and restores the updated ones. It stops without changing anything if records of others are linked to records created by the load, or if records were changed after it (it says which ones, in blocked and errors); with anyway: true it also undoes the records changed afterwards. Options: by, anyway, app.

Returns: { id, file, collection, read, created, updated, errors, errorCount, warnings, duration, rolledBack, blocked }.

The same result as load; here created = records removed, updated = records restored.

const result = $orch.import.undo(id, { by: e.auth.email(), app: e.app });

From the app pages: POST /api/tools/import/undo (effect: removes data), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.import.history(options?)

The latest loads of the app (at most 50), the most recent first; with by only those started by that person. Options: by, limit (how many), app.

Returns: [{ id, file, collection, status, by, undoneBy, recipe, createdAt, updatedAt, read, created, updated, unchanged, skipped, rejected, errorCount, duration }].

const mine = $orch.import.history({ by: e.auth.email(), limit: 10 });

From the app pages: POST /api/tools/import/history (effect: read only), with the permissions of toolAccess in app.json.

$orch.import.plan(path, options?)

Reads the whole file (all the sheets of an Excel, all the lists of a JSON file) and proposes where the data go: for each sheet the most similar collection of the app (name of the sheet and titles of the columns, with a degree of similarity from 0 to 1), the columns → fields, the key, the field to find linked records, the load order (first the collections the others link to) and, for a sheet with everything in it, the linked collection filled from its columns (one record per key). recipe is ready for load; missing says, in words, what finds no place (sheets without a collection, columns without a field). It writes nothing. Options: the read options and app.

Returns: { recipe: { name, options, parts: [{ sheet, collection, fields, key, onlyNew, ignoreBlank, relations, distinct }], partial } (a whole recipe: its other entries are empty), sheets: [{ sheet, label, rows, columns, hidden, error, collection, similarity, ambiguous, weak, alternatives: [{ collection, similarity }], linked, unmatchedColumns, notAllowed }], order, missing, warnings }.

const plan = $orch.import.plan("release/seed/old_system.xlsx", { app: e.app });
console.log("load order: " + plan.order.join(", ") + "; missing: " + plan.missing.join(" "));
const trial = $orch.import.load("release/seed/old_system.xlsx", plan.recipe, { dryRun: true, app: e.app });

From the app pages: POST /api/tools/import/plan (effect: read only), with the permissions of toolAccess in app.json.

$orch.import.rows(path, options?, range?)

All the data rows of the file, in pieces: the same values as the sample of analyze, with the full texts. Pass { offset, limit } (at most 5,000 rows per piece, fewer for very wide files) and go on with next until it is null. Every piece reads the file again.

Returns: { file, format, sheet, columns, rows: [{ number, values: [{ type, text, number, date, bool }] }], offset, limit, total, next, warnings }.

let next = 0;
while (next !== null) {
  const piece = $orch.import.rows("release/seed/parts.xlsx", { header: "titles" }, { offset: next, limit: 1000 });
  piece.rows.forEach((r) => console.log(r.number + ": " + r.values[0].text));
  next = piece.next;
}

Engine HTTP APIs

The app doesn't call them: they serve the console and the portal. They're listed so that the app doesn't rebuild them.

How to use it

When to use it. Whenever the app has to read an Excel (.xlsx, also the old .xls), CSV or JSON file: initial data delivered with the app, the history of an old management program, the monthly export of another program. Don't write a CSV reader by hand and don't add libraries: use this tool. Old .xls files (Excel 97-2003) are read too, except those protected by a password and those from Excel 95 or earlier: open them in Excel and save them as .xlsx. If the file is loaded only once, or IT loads it, no code is needed: in the engine console the app page has the “Import” tab (for the Owners and for the Admins who manage the app), with preview, trial run, history and undo. When people upload the files from the app pages, a permission in app.json is enough (below, “From the app pages”); code is needed for the initial data in a migration or for a load with the app's own rules.

Where the files are. Paths start from the app folder (the one with app.json); a path that leaves the folder, or that goes through a symbolic link or a junction inside the folder, is refused (rule 3 of the common rules).

• Files delivered with the app: in release/seed/, for example "release/seed/suppliers.csv".
• Files uploaded by people into a file field of a record: they are in the app's data and the path is built like this:

const p = $filepath.join($app.dataDir(), "storage", rec.collection().id, rec.id, rec.getString("file"));

Read options (second argument of analyze, options entry of the recipe):

Column types recognized by analyze (type entry of columns[] and of the values in sample[].values[]): text, number, date, datetime, time, boolean, empty. Numbers are read in the Italian and in the English way (1.234,56 and 1,234.56): the convention is decided per column from its values, a column that mixes the two stays text (with a note), and the doubtful cases (only values like 1.234) are read the Italian way unless englishNumbers is set. Dates are read day first (31/10/2026) or as ISO dates (2026-10-31); month first only when the values of the column prove it (with a note). Codes with leading zeros (00123) and numbers with more than 15 digits (IBANs, chassis numbers) stay text; n.d., - and Excel errors count as an empty cell. The type is the most precise one that fits all the values of the column: when in doubt, text. For numbers, wholeNumbers says whether they are all integers.

The analyze preview is shortened: in rawRows, sample, columns[].examples and columns[].title every text has at most 300 characters (longer ones end with “…”), rawRows and sample show at most the first 200 columns and only the first 200 columns have examples. columns still lists them all, and load reads all the columns with the full texts (a title longer than 300 characters also counts, shortened, as the column name in the recipe).

Text limits of an Excel file. Excel stores equal texts only once, in a list of shared strings. The engine reads at most 5,000,000 distinct texts and 256 MB of text in all; beyond that, analyze and load (like “Import preview” and the “Import” tab of the console) stop with an error that says which limit was exceeded: in scripts and in the English console “the Excel file contains too much text (more than 5,000,000 distinct texts)” or “(more than 256 MB of text)”; in the Italian console it begins with “Il file Excel contiene troppo testo”. These ceilings are far beyond real files (they are enough for a full sheet, 1,048,576 rows, with almost five columns of all-different texts) and they keep a file built on purpose from exhausting the engine's memory. If a real file exceeds them, split it into several files or save it as CSV, which has no such limits.

The recipe (second argument of load):

An entry that the recipe or the options don't expect (for example a misspelled name) is an error that names it.

Fields that can be filled: text, editor, email, url, number, bool, date, select (if multiple, values separated by ;, , or |), relation, json. Attached files, passwords and automatic dates (autodate) are not imported; the id is decided by the database.

Options of load (third argument): dryRun (true = checks everything and saves nothing), by (who started the load, it stays in the registry), app (see the common rules). undo takes by, anyway (see below) and app.

The result. Rows with errors are discarded and the others loaded: always check rejected and errors (at most 500 errors described with row, column, field, value and reason; errorCount counts them all). The other counts: read (rows read), created, updated, unchanged, skipped (already present, with onlyNew), duplicates (rows with a key already seen in the same file); warnings are the warnings, duration how long it took. id is the number of the load, to keep in order to undo it; in a trial run (dryRun) it is empty. The result of undo has the same shape: there created are the records removed and updated the ones restored.

Undoing. undo is all or nothing: it removes the records the load created and restores the updated ones, from the most recent (the same record updated twice goes back to how it was before the first time). Before changing anything it checks, and stops without touching anything in two cases, saying which records they are (in errors and in the message, at most 20): records of others linked to a record the load created (removing it would break the link or delete the other record in cascade: blocked: "links"; those links have to be removed first) and records changed after the load (blocked: "changed": undoing would lose those changes). With { anyway: true } it also undoes the records changed afterwards; links of others always stop it. The “Import” tab asks for confirmation with “Undo anyway”. From the console, undo works only if all the collections of the load are among those of import.consoleCollections (see below).

JSON files. analyze, load and the others also read JSON files (.json, .jsonl, .ndjson): a list of objects ([ {…}, {…} ]) is a table; an object with several lists ({ "suppliers": [ … ], "orders": [ … ] }) is a file with several sheets, one per list (sheets, and sheet to choose one; the keys without a list of objects are skipped, with a warning); one object per line (JSON Lines) is a table. The column names are the keys, in the order in which they first appear; nested parts become columns with a dot (supplier.name), up to 8 levels (deeper, JSON text); lists inside an object stay JSON text (["a","b"]), which multiple select fields and multiple relations can split. Numbers, yes/no and null already have their type (numbers with more than 15 digits stay text); texts go through the usual recognition (ISO dates, numbers). The title row is row 0: the n-th object is row n. The options about the first and the last rows don't apply. Limits: 128 MB per file, 16 MB per object (an element of the list), 64 levels of nesting, 16,384 columns, 1,048,576 objects per list, 1 MB of text per value. In a JSON file the numbers written as text ("2.500") are read the English way, with the dot for decimals: that is how the programs that write JSON do it (the column has the note).

The whole file into several collections. $orch.import.plan(path, options?) reads all the sheets of an Excel file (or all the lists of a JSON file) and proposes where they go: for each sheet the most similar collection, with a degree of similarity from 0 to 1 (the name of the sheet and the titles of the columns, regardless of upper case, accents, spaces, singular and plural, with a small Italian and English vocabulary of management programs: fornitore = supplier, partita IVA = VAT number…), the columns → fields, the key (a unique index, then a field like code, VAT number, license plate, email, then a “presentable” field or a name; there is an automatic key only with a unique index that has all the columns (an index with WHERE counts only if it leaves out just the empty values of its fields), or in a collection without links matched to a column of the file: there a code (also email, VAT number, tax code, license plate, VIN), a “presentable” field or a name, with all different values, filled in every row (a number 0 counts as empty) and matching at most one record in the database. A collection with a link matched to a column, required or not, single or with several values (the companies of a contact), has no automatic key: a proposed key never updates the row of another parent (for example, for vehicles linked to a branch, a unique index on the license plate is needed). The warning suggests the one to choose by hand, for example order + item; a key chosen by hand, also a composite one, works as always. At load time a key that matches more than one record discards the row, with the reason; with onlyNew the row is skipped, but not if the record found has another link; a value of a link that matches more than one record (in the database or among those of the import) discards the row, with the reason; with partial a row that looks up the record of a discarded row of the same import is discarded too (“the linked record … is not loaded”), without looking it up in the database; in a sheet with everything in it a row with the order number and without the customer counts as the full row with the same number, if the file has one) and the field to find the linked records. A sheet with the data of several collections in it (each row has the order and also the name and VAT number of the supplier) is split: the supplier columns (“Supplier”, “Supplier VAT number”, supplier.vat) go into its collection, one record per key (distinct: the rows after the first with the same key don't count; ignoreBlank), and the order is linked to it. recipe is ready for load; sheets gives for each sheet the chosen collection, the similarity, the alternatives, the linked collections and the columns that find no field; order is the load order; missing says in words what finds no place (sheets without a collection, columns without a field): it is skipped, it stops nothing. Data go only into collections that already exist: the structure is decided by the app with its migrations.

load with a recipe with parts (recipe.parts: each with sheet and the entries of a recipe: collection, fields, key, onlyNew, ignoreBlank, relations, distinct, its own options if needed) loads the collections in the order of the relations (first the ones the others link to), so every link finds its record, also the one just created by the same import. It is all or nothing, in two steps: first it checks everything without writing (conversions, links, field rules; the new records already have their id and the writes to do go into a working file, not into memory), then it writes in a single transaction for all the collections. With a rejected row (without partial) it stops at the check; with an error during the writes, with the job stopped or beyond the time limit nothing stays (rolledBack: true, “failed” in the registry). While it writes, the other writes of the app wait (reads don't): that's why an import into several collections takes at most 50,000 rows (of all the sheets; beyond, split the file or import one collection at a time) and the writes last at most 60 seconds. The trial run (dryRun) checks everything without saving, also the links to the records the import would create, and waitSeconds estimates for how many seconds the other writes will wait. Links to the same collection (an employee's boss) and links that go in a circle (department → manager → department) are filled in a second pass, when all the records are there (the part needs a key and the field must not be required; otherwise a warning says so). A part that can't be loaded (a sheet the file doesn't have, a collection that isn't there, a missing key column) has the reason in skippedReason: without partial nothing is loaded, because all or nothing would only half apply; with partial the others are loaded. A column of the recipe that the file doesn't have is skipped with a warning. In the proposal, a sheet with everything in it fills the linked master data only with new records (onlyNew: the ones the app already has are not changed), and the same key with different values in the rows has a warning (the first row counts); a weak proposal, or one almost tied with another collection, has a warning in warnings (and isn't green in the tab), and a key that repeats in the file is not proposed. The field used to find the linked records (relations) must be id or a text, number, email, URL or select field of the linked collection. The result has the totals and, in tables, the result of each part in load order (with sheet); the errors in errors also have sheet and collection. undo removes the whole import, in reverse load order. From the “Import” tab a running job is stopped with “Stop” (an import into several collections that is stopped leaves nothing).

All the rows, in pieces. analyze shows only the first 20 rows; to read them all from a script there is $orch.import.rows(path, options?, { offset, limit }): the same values as sample (with the full texts), at most 5,000 rows per piece (fewer if the file is very wide or the texts are long). Go on with next until it is null. Every piece reads the file again.

Empty cells in number fields. PocketBase has no empty number (a number field is 0). An empty cell in a number field means “no value”: in a record already there it doesn't change the number (even without ignoreBlank); in a new record the field stays as it is (the app decides it, for example with a hook); if the field is required, in a new record the row is rejected with the reason.

Recipes in the app package. The recipes the app needs are delivered with the package, in release/import/*.json (the same JSON as a recipe, also with parts; name optional: without it, the file name). The “Import” tab of the console offers them next to the ones saved by IT, read-only (they are used, saved under another name, not deleted; a broken one is shown with the reason and isn't used). From the pages they are used by name like the saved ones (toolAccess.import.recipes); a recipe saved by IT with the same name is used instead of the package one.

What the console may import. The “Import” tab of the console imports like the server: the API rules and the routes of the app don't apply. An app that keeps its data in order with its own routes (movements, histories, states) lists in app.json the only collections the console may import into:

"import": { "consoleCollections": ["suppliers", "orders", "order_lines"] }

With the entry the tab offers and accepts only those (also with “The whole file”); an empty list closes it completely; an entry written wrong closes it, with the reason. Without the entry the console imports into all the collections, and the tab says so. The app's scripts are not affected; the pages follow toolAccess.import.

Rules for this tool:

1. First { dryRun: true }, then the real load. Show the person the errors with the row number and the reason.
2. The load goes through the field validations and the record hooks (onRecordCreate, onRecordValidate…) but not through the API rules of the collection nor the request hooks (onRecordCreateRequest…): the server writes. That's why the route that starts the import checks by itself who is calling: $apis.requireAuth("users") and the user's role.
3. Real loads are recorded in the system collections orch_imports and orch_import_rows, created by the engine at the first load; the recipes saved from the console are in orch_import_recipes. Don't touch them and don't create them in migrations.
4. A large file can take seconds or minutes: start the import from a route (routerAdd) called by the page, not inside the save hook of another record.
5. For a file uploaded by people: a record in a collection such as uploads with a file field, then a POST route that receives the record id, builds the path as above and runs first the trial and then the load.

From the app pages (rule 8 of the common rules), without writing routes: the tool in "tools" and in app.json a permission like this one.

"toolAccess": {
  "import": { "pages": true, "minRole": "editor", "destructiveRole": "admin", "collections": ["parts"], "recipes": ["Parts price list"], "maxRows": 5000 }
}

• POST /api/tools/files with tool = import and the file (.xlsx, .xlsm, .xls, .csv, .txt, .tsv, .json, .jsonl, .ndjson, at most 100 MB) → { id, … }.
• POST /api/tools/import/preview { file, options }: the preview, like analyze (it saves nothing), plus warningCodes.
• POST /api/tools/import/plan { file, options }: the proposal for the whole file, like plan, only with the collections of toolAccess.import.collections (it loads nothing).
• POST /api/tools/import/jobs { file, recipe, dryRun } → 202 { job }: trial run or real load in the background; the state of the job has the result in result, like the one of load. The recipe is the name of a recipe saved from the “Import” tab of the console (collection orch_import_recipes), listed in recipes, on a collection listed in collections: the page never sends a recipe of its own. The real load is accepted only after a successful trial run by the same person with the same file and the same recipe (if IT changes the recipe in the meantime, the trial run must be done again). A file with more rows than maxRows (without it: 10,000) is refused. Cancelling the job stops it between one row and the next: the batches already saved stay and are removed by undoing the load.
• POST /api/tools/import/undo { id, anyway? } → 202 { job }: undoes a load made from the pages (never one made from the console or by a script), only with destructiveRole and with all the collections of the load in collections; anyway as in undo.
• POST /api/tools/import/history {}: the loads made from the pages by whoever asks (in the registry started_by is “name <email> [pages: <collection>/<user id>]” and the history looks for the [pages: …] part: the link is by id, changing name or email removes nothing; of whoever undid a load only the name is shown). A cancelled trial run leaves no event. From scripts: $orch.import.history({ by, limit }).
• Every real load and every undo leaves an event in the engine log; the trial run doesn't.

Example: initial data delivered with the app, in a migration.

/// <reference path="../pb_data/types.d.ts" />
migrate((app) => {
  $orch.import.load("release/seed/suppliers.csv", {
    collection: "suppliers",
    options: { format: "csv", separator: ";" },
    fields: { "Account": "account", "Company name": "company_name", "VAT number": "vat_number" },
    key: ["account"],
  }, { app: app, by: "initial data" });
}, (app) => {});

Example: import started from a page of the app, only for the manager.

routerAdd("POST", "/api/pricelist/import/{id}", (e) => {
  if (e.auth.getString("role") !== "manager") {
    throw new ForbiddenError("Only the manager can import the price list.");
  }
  const rec = e.app.findRecordById("uploads", e.request.pathValue("id"));
  const file = $filepath.join(e.app.dataDir(), "storage", rec.collection().id, rec.id, rec.getString("file"));
  const recipe = {
    collection: "parts",
    options: { format: "excel", header: "titles" },
    fields: { "Code": "code", "Description": "description", "Price": "price" },
    key: ["code"],
  };
  const trial = $orch.import.load(file, recipe, { dryRun: true, app: e.app });
  if (trial.rejected > 0) {
    return e.json(400, { message: "Some rows need fixing.", result: trial });
  }
  const result = $orch.import.load(file, recipe, { by: e.auth.email(), app: e.app });
  rec.set("import_id", result.id);
  e.app.save(rec);
  return e.json(200, result);
}, $apis.requireAuth("users"));

Numbering (numbering)

Safe sequence numbers even when several people save at the same time (for example the number of a purchase request per year), in the format you choose: no duplicates and no gaps, because the number is taken in the same transaction as the save.

Turn it on with "tools": ["numbering"] in app.json. Category: Data.

Functions

$orch.numbering.next(name, options?)

Takes the next number of a counter of app.json (the counter moves on). Use it inside your own runInTransaction, with the transaction's app and saving the record with that same app, otherwise a save that fails leaves a gap (in record hooks the hook's app is usually not in a transaction): for the numbers of a collection, the automatic assignment of app.json is better. Options: app.

Returns: { name, number, seq, period }.

number is the number written with the format, seq the sequence number in the period.

// in your own transaction, saving with the same app: if the save fails, the number goes back too
e.app.runInTransaction((txApp) => {
  rec.set("code", $orch.numbering.next("parts", { app: txApp }).number);
  txApp.save(rec);
});

$orch.numbering.peek(name, options?)

The counter as it is now, with the next number, without taking it.

Returns: { name, collection, field, format, reset, period, current, next, nextSeq, periods: [{ period, value, updated }] }.

const p = $orch.numbering.peek("rda"); // p.next is the number the next request will get

From the app pages: POST /api/tools/numbering/peek (effect: read only), with the permissions of toolAccess in app.json.

$orch.numbering.list(options?)

All the counters of app.json, with the current value and the next number.

Returns: [{ name, collection, field, format, reset, period, current, next, nextSeq, error, errorKey, errorArgs, periods }].

const counters = $orch.numbering.list();

From the app pages: POST /api/tools/numbering/list (effect: read only), with the permissions of toolAccess in app.json.

Engine HTTP APIs

The app doesn't call them: they serve the console and the portal. They're listed so that the app doesn't rebuild them.

How to use it

When to use it. Whenever the records of a collection have a sequence number that people read and quote: the number of a purchase request, of an order, of a report, of a ticket. Don't compute the number by hand in hooks (for example by counting the records or reading the last one): two saves at the same time would get the same number, and a save that fails would leave a gap. The engine assigns the number inside the same transaction as the save: no duplicates and no gaps, even with many people saving at the same time.

Configuration in app.json, with the tool on:

"tools": ["numbering"],
"numbering": {
  "rda": { "collection": "requests", "field": "number", "format": "RDA-{YYYY}-{seq:4}", "reset": "yearly" }
}

How it works. When a record of the collection is created, from any path (app pages, scripts, import, console), if the field is empty the engine writes the next number in it, just before the write to the database: after the app's hooks (in onRecordCreate and onRecordCreateExecute the number is there after e.next(); a number the hook writes before e.next() stays). During validation the empty field holds the number the counter would give now, so a required field doesn't stop the creation (also in the import's trial run and with $app.validate()); in the app's onRecordValidate hooks the field is still empty. From the API used by the pages the field can't be chosen or changed: the engine drops the value sent by the page at creation and puts it back as it was on every change. A script, the import or the console can instead write a given number (for example the numbers of an old management program): it stays, and the counter skips it when it gets there. A counter written wrong in app.json stops the creation with an error that says what is missing (to the app's people in the language of the page) and shows in the “Numbering” tab. Put a unique index on the field anyway, as a safety net.

Where the counters are. In the system collection orch_counters of the app, created by the engine: it travels with the backups and can't be seen from the API (not even by superusers) or in the Data tab. Don't touch it and don't create it in migrations. In the engine console the app page has the “Numbering” tab, with the last number given and the next one; an Owner can correct a counter (for example to go on from the last number of an old program), and the correction stays in the events.

Rules for this tool:

1. For the numbers of records use the automatic assignment of app.json: no code is needed.
2. $orch.numbering.next(name) takes a number and moves the counter on at once: use it inside your own runInTransaction, passing the transaction's app ({ app: txApp }) and saving the record with that same app, otherwise a save that fails leaves a gap. In record hooks the hook's app is usually not in a transaction (it is inside a /api/batch batch). peek and list only read.
3. To show the person the number the request will get before saving it, peek gives the next number: it's only a hint, because another person may save first.
4. The number is assigned in the transaction of the write alone, after the app's hooks and after the upload of files: in hooks write with the hook's app (e.app), which inside a /api/batch batch is a transaction.

Example: the same counter used for a code other than the automatic one, from an app route, in the same transaction as the save.

routerAdd("POST", "/api/parts", (e) => {
  const body = e.requestInfo().body;
  const rec = new Record(e.app.findCollectionByNameOrId("parts"));
  e.app.runInTransaction((txApp) => {
    rec.set("name", body.name);
    rec.set("code", $orch.numbering.next("parts", { app: txApp }).number);
    txApp.save(rec); // if the save fails, the number goes back too
  });
  return e.json(200, rec);
}, $apis.requireAuth("users"));

Example: the next number for the page of a new request.

routerAdd("GET", "/api/requests/next-number", (e) => {
  return e.json(200, { next: $orch.numbering.peek("rda").next });
}, $apis.requireAuth("users"));

From the app pages (rule 8 of the common rules), only reading: the tool in "tools" and a permission in app.json.

"toolAccess": {
  "numbering": { "pages": true, "collections": ["requests"] }
}

• POST /api/tools/numbering/peek { name }: the counter and the next number, like peek, without taking it.
• POST /api/tools/numbering/list {}: like list, only the counters the person can read.
• Which counters: the ones with collection among the collections of the permission, and only if the app shows the records of that collection to someone with its API (list rule or view rule not null): a counter says how many records there are, and a collection that the app keeps for superusers only must not say it to the pages. The counters without a collection are only for the scripts. Careful: the counter is the one of the whole collection (how many numbers have been given, that is about how many records there are), and anyone with the permission reads it, even someone who, with the rules of the app, sees no record of that collection. If the total must not be told to everyone, set a minRole (or don't open numbering to the pages).
• Why next is not offered to the pages. The number of a record is assigned by the engine by itself when the record is created, inside the transaction of the save: the page doesn't need to ask for it. A number taken from a page would be outside every transaction: if the page then doesn't save (tab closed, error, validation), that number is lost and the sequence has a gap; and anyone allowed could use up numbers. To show the number that the next record will get, use peek: it's a hint, because another person may save first; the real number is in the record that the API gives back after the creation.

Example: the page of a new request shows the number it will probably get.

<script src="/__platform/tools.js"></script>
<script>
  async function nextNumberHint() {
    const c = await PyriteTools.call("numbering", "peek", { name: "rda" });
    return c.next; // for example "RDA-2026-0043": the request saved now will probably get it
  }
</script>

PDF (pdf)

Creates PDF documents, and the same documents as a single HTML file, from a document made of blocks (titles, texts, fields, tables with totals, images, QR codes and barcodes) or from the app's templates filled with data, in the Pyrite print style with the IBM Plex fonts inside. It also reads PDFs that contain text (information, text with positions, form fields, a page as an image) and merges PDFs. No OCR.

Turn it on with "tools": ["pdf"] in app.json. Category: Documents.

Functions

$orch.pdf.render(doc | { template, data }, options?)

Makes a PDF (or, with format: "html", a single HTML file) from a document of blocks, or from a template of release/templates/ filled with data: { template, data }, where @pyrite is the engine's standard template. The file goes where the options say, otherwise it stays in memory for send. Options: format, name, lang, path, record, field, owner, app, maxPages.

Returns: { format, type, name, size, pages, id? | path? | record?, field? | file? }.

pages is the number of pages; id is there when the file stays in memory (for $orch.pdf.send, for 5 minutes); path, record and field, or file, when it was saved.

routerAdd("GET", "/api/orders/{id}/pdf", (e) => {
  const order = e.app.findRecordById("orders", e.request.pathValue("id"));
  const r = $orch.pdf.render({ template: "order", data: { number: order.getString("number"), supplier: order.getString("supplier") } },
                             { name: "order-" + order.getString("number") + ".pdf", lang: "it" });
  return $orch.pdf.send(e, r);
}, $apis.requireAuth("users"));

From the app pages: POST /api/tools/pdf/render (effect: read only), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.pdf.html(template, data?, options?)

Runs an HTML template of the app (Go html/template: the data are escaped) and makes a single HTML file with fonts and images inside; scripts and external resources are removed. Functions in the template: image, recordImage, qr, barcode, money, number, percent, date, datetime, pyriteLogo. Options: name, lang, path, record, field, owner, app.

Returns: { format: "html", type, name, size, id? | path? | record?, field? | file? }.

const r = $orch.pdf.html("label.html", { code: "F-0001", price: 12.5 }, { path: "state/labels/F-0001.html" });
// release/templates/label.html: <p>{{.code}} · {{money .price}}</p><img src="{{qr .code}}">

From the app pages: POST /api/tools/pdf/html (effect: read only), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.pdf.send(e, result, options?)

Sends a file made by the other functions as the response of the route, as a download (inline: true opens PDFs and PNG images in the browser). Options: inline, name.

Returns: null (the response is written).

return $orch.pdf.send(e, $orch.pdf.render(doc), { inline: true });

$orch.pdf.info(source, options?)

Pages, metadata and whether the PDF is encrypted. The source is a path in the app folder or the file of a record: { record, field }. Options: password, app.

Returns: { pages, encrypted, passwordRequired, version, width, height, title, author, subject, keywords, creator, producer, created, modified, size, hasForm }.

const i = $orch.pdf.info("release/seed/manual.pdf"); // or { record: e.record, field: "attachment" }

From the app pages: POST /api/tools/pdf/info (effect: read only), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.pdf.text(source, options?)

The text of each page with the position of each piece, in points from the top left corner. Only for PDFs that contain text: there is no OCR. Options: pages, positions, password, maxPages, app.

Returns: { pageCount, chars, pages: [{ page, width, height, text, pieces: [{ text, x, y, width, height, size }] }] }.

const t = $orch.pdf.text({ record: e.record, field: "invoice" }, { pages: [1] });
const total = t.pages[0].pieces.find((p) => p.text.startsWith("Total"));

From the app pages: POST /api/tools/pdf/text (effect: read only), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.pdf.fields(source, options?)

The fields of the PDF forms, with their values. Options: pages, password, maxPages, app.

Returns: { pageCount, fields: [{ page, name, type, value, values, checked, options, readOnly, required }] }.

const f = $orch.pdf.fields("state/uploads/form.pdf");

From the app pages: POST /api/tools/pdf/fields (effect: read only), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.pdf.pageImage(source, page, options?)

One page as a PNG image, at most 300 dpi and 20,000,000 pixels (fewer for apps with little memory). Options: dpi, password, name, path, record, field, owner, app.

Returns: { format: "png", type, name, size, width, height, dpi, page, id? | path? | record?, field? | file? }.

const img = $orch.pdf.pageImage("release/seed/manual.pdf", 1, { dpi: 100, path: "state/previews/manual-1.png" });

From the app pages: POST /api/tools/pdf/page-image (effect: read only), as a background job (202 with the job), with the permissions of toolAccess in app.json.

$orch.pdf.merge(sources, options?)

Merges from 2 to 20 PDFs into one, in the order given (the pages, without bookmarks and form fields). Encrypted PDFs are refused. Options: name, path, record, field, owner, maxPages, app.

Returns: { format: "pdf", type, name, size, pages, id? | path? | record?, field? | file? }.

const all = $orch.pdf.merge(["state/out/cover.pdf", { record: e.record, field: "attachment" }], { name: "dossier.pdf" });

From the app pages: POST /api/tools/pdf/merge (effect: read only), as a background job (202 with the job), with the permissions of toolAccess in app.json.

How to use it

When to use it. Whenever an app hands out a document to print, sign or send: a purchase order, a report, a summary, a label with a barcode. Don't build PDFs by hand in scripts and don't install libraries: describe the document with blocks (or write a template in release/templates/) and the engine makes the PDF, with the IBM Plex fonts inside the file, in the Pyrite print style (black rules, small-caps mono labels, strong titles). The same document also gives a single HTML file (fonts, style and images inside), for whoever wants to open it in the browser or archive it. The tool also reads PDFs that contain text (not scans: no OCR in this version) and merges PDFs.

The document of blocks (sizes in millimeters, fonts in points):

{
  "lang": "en", "title": "Purchase order 2026/42", "currency": "EUR",
  "page": { "size": "A4", "orientation": "portrait", "margins": 18 },
  "header": { "logo": "pyrite", "right": "Rossi Garage" },
  "footer": { "left": "Internal document", "right": "Page {page} of {pages}" },
  "blocks": [
    { "type": "title", "text": "Purchase order", "subtitle": "no. 2026/42 of November 3, 2026" },
    { "type": "fields", "columns": 3, "items": [ { "label": "Supplier", "value": "Bianchi Parts" }, { "label": "Total", "value": 1234.5, "format": "money" } ] },
    { "type": "table", "columns": [ { "key": "code", "title": "Code", "width": 30 }, { "key": "qty", "title": "Qty", "format": "integer" }, { "key": "price", "title": "Price", "format": "money" } ],
      "rows": [ { "code": "F-0001", "qty": 2, "price": 10.5 } ], "totals": { "price": 21 } },
    { "type": "text", "text": "Delivery by **Friday**, *unless agreed otherwise*." },
    { "type": "qr", "text": "https://example.com/orders/42", "size": 28, "align": "right" }
  ]
}

Values of format (columns and fields): text, number (2 decimals), integer, money (the currency of the document or the currency of the column: €1,234.50 in English, 1.234,50 € in Italian), percent (0.25 → 25 %, like Excel), date, datetime (from the PocketBase format or ISO: 2026-11-03 in English, 03/11/2026 in Italian). Numbers are rounded half up as they are written (10.125 → 10.13, 2.5 → 3). PocketBase writes dates in UTC: a date with its time zone is written in the time zone timeZone of the document (an IANA name like "Europe/Rome"; without it: the server's), a date without time or without time zone stays as it is. Characters that the IBM Plex fonts don't have beyond the basic plane (emoji) become � in the PDF; the HTML keeps them. In the header and footer {page} and {pages} are the page number and how many pages there are; logo is "pyrite" (the engine mark) or an image of the app. When the HTML is printed (Chromium browsers) the texts of the header and footer go into the margins of every page and the logo is printed at the top of the first page; in the PDF the logo is on every page. A misspelled entry is an error that tells which block and which entry.

Templates of the app. A file release/templates/<name>.json with the same document and {{name}} placeholders (also {{customer.name}}, {{rows.0.code}}), filled from the data: $orch.pdf.render({ template: "order", data }). A text that is only a placeholder takes the value as it is ("rows": "{{rows}}" receives the list), inside a longer text it becomes text. "if": "notes" on a block keeps it only if the data is there (not empty, not zero, not false). Data values are not read again: a {{…}} written in a value stays as it is, and the asterisks of a value in the text of a text block stay asterisks (the bold and italic are the template's). The engine template "@pyrite" is the starting point (logo in the header, page numbers in the footer, title, fields, table with totals, notes, QR): data title, subtitle, company, reference, fields, intro, columns, rows, totals, totalsLabel, notesTitle, notes, qr, qrCaption, footer, lang, currency. To make your own, copy its JSON (below) into release/templates/ and change it.

{
  "lang": "{{lang}}",
  "title": "{{title}}",
  "author": "{{company}}",
  "currency": "{{currency}}",
  "page": { "size": "A4" },
  "header": { "logo": "pyrite", "center": "{{reference}}", "right": "{{company}}" },
  "footer": { "left": "{{footer}}", "right": "{page} / {pages}" },
  "blocks": [
    { "type": "title", "text": "{{title}}", "subtitle": "{{subtitle}}" },
    { "if": "fields", "type": "fields", "columns": 3, "items": "{{fields}}" },
    { "if": "intro", "type": "text", "text": "{{intro}}" },
    { "if": "rows", "type": "table", "columns": "{{columns}}", "rows": "{{rows}}", "totals": "{{totals}}", "totalsLabel": "{{totalsLabel}}" },
    { "if": "notes", "type": "heading", "text": "{{notesTitle}}", "level": 2 },
    { "if": "notes", "type": "text", "text": "{{notes}}" },
    { "if": "qr", "type": "qr", "text": "{{qr}}", "size": 28, "align": "right", "caption": "{{qrCaption}}" }
  ]
}

Free HTML. $orch.pdf.html(template, data, options): the template is the HTML text (Go html/template: {{.field}}, {{range .rows}}…{{end}}, {{if}}) or the name of a file release/templates/<name>.html. The data go through automatic escaping in the right context: a value with <script> or quotes stays text. Extra functions: {{image "release/templates/logo.png"}} (for src), {{recordImage "vehicles" .id "photo"}}, {{qr .link}}, {{barcode "ean13" .ean}}, {{pyriteLogo}}, {{money .total}}, {{number .qty 0}}, {{percent .discount}}, {{date .day}}, {{datetime .when}} (with the time zone as second argument: {{datetime .when "Europe/Rome"}}). The file produced is a single file: the engine puts at the top the encoding, a Content-Security-Policy (no scripts, no requests outside the file) and the IBM Plex fonts (font-family: "IBM Plex Sans" and "IBM Plex Mono"), and removes scripts, iframes, objects, SVG animations, on… attributes, javascript: addresses and external resources (images and CSS taken from outside, also written with the \ sequences of CSS). The real defence is the Content-Security-Policy: the cleaning is a second fence for the programs that ignore it, and the template is the app's code (the data can't add markup). No PDF from free HTML in this version: the PDF is made from the document of blocks. The template stops at the time limit (1 minute) even with loops that write nothing: the job ends with "time limit reached", but the loop goes on in the app's process until it ends by itself (even for hours), and meanwhile the app refuses the other HTML templates (pdf.err.html_busy). The console log shows a warning with the template's name; restarting the app from the console stops the loop at once. At most 2 HTML templates run together in an app: the others wait for their turn, within the time limit. Write loops that end (no {{range 1000000000}}, no nested loops over long lists that write nothing).

Where the file goes (options of render, html, pageImage, merge): path (a file inside state/), record with field (a file field of the record, saved with the app of the script: it respects the maxSize of the field), owner (a user of the app, for example e.auth: the file goes into the tools file deposit and the page downloads it with PyriteTools.download(r.file.id), if toolAccess.pdf.pages is true). Without any of the three the file stays in memory for 5 minutes and $orch.pdf.send(e, r) sends it as the response of the route, as a download ({ inline: true } opens PDFs and PNGs in the browser; HTML is always downloaded). The result has format, type, name, size, pages and, depending on the destination, id, path, record and field, or file.

Reading PDFs. info, text, fields, pageImage receive a path in the app folder ("state/…", "release/…") or the file of a record: { record: e.record, field: "attachment" } or { collection, id, field, name }. text gives the text of each page and the pieces with their position (x, y, width, height in points from the top left corner, size of the font); { pages: [1, 2] } chooses the pages, { positions: false } leaves out the positions. info of a PDF encrypted with an opening password says passwordRequired: true; the other functions want { password }. PDFium does it (the PDF reader of Chromium) compiled to WebAssembly, closed in a sandbox: it sees no files, no network, nothing else, and it has a memory and time limit. The first reading after the engine is installed compiles PDFium (about 2 seconds), then it comes from the cache. merge merges from 2 to 20 PDFs in the order given, with PDFium in the same sandbox: it copies the pages (with their annotations), not the bookmarks nor the form fields; encrypted PDFs are refused.

Limits. PDFs to read at most 100 MB and 500 pages per reading (maxPages up to 2000); PDFs to merge at most 200 MB in all; the image of a page at most 300 dpi and 20,000,000 pixels (fewer in apps with a small memoryLimitMB: PDFium gets a quarter of it, between 64 and 256 MB); text read at most 10 million characters (from the pages, the result at most 4 MB); a document produced at most 2000 pages, 5000 blocks, 50,000 table rows, 50 images (each at most 10 MB and 16,000,000 pixels); time at most 1 minute to make a document and 2 minutes to read one. A PDF that is damaged, encrypted, too big or built to do damage gives an error that says so, and the app stays up (merging too: everything happens inside the sandbox, with its memory limit).

From the app pages (rule 8 of the common rules), without writing routes: the tool in "tools" and in app.json a permission like this one.

"toolAccess": {
  "pdf": { "pages": true, "minRole": "editor", "templates": ["order", "label.html", "@pyrite"], "collections": ["vehicles"], "maxPages": 200 }
}

• POST /api/tools/pdf/render { template, data, format, name } → 202 { job }: the template must be in templates (without it: none; a name alone, like "order", is the block template order.json); the data are the ones the page sends, which the person already has. The file produced goes into the deposit: job.result.file.id → PyriteTools.download(id).
• POST /api/tools/pdf/html { template, data, name }: a template release/templates/<name>.html listed in templates with the extension ("label.html"): "label" alone doesn't open it, so an HTML template written for the scripts doesn't open to the pages by mistake.
• POST /api/tools/files with tool = pdf and the PDF (at most 100 MB) → { id }; then POST /api/tools/pdf/info, text or fields { file, pages, positions, password }, POST /api/tools/pdf/page-image { file, page, dpi, name }, POST /api/tools/pdf/merge { files: [id, id, …], name } (at most 200 MB in all, checked before reading). Only the files uploaded by the same person can be read (for the others: 404). The text read for a page is at most 4 MB (as JSON): for long PDFs choose the pages, or leave out the positions.
• From the pages the images of the templates come only from release/pb_public/ (already public) and release/templates/; the files of records only from the collections in collections, with the list rule of the app for whoever asks (only what the person already sees can be read), and never protected files. Pages at most maxPages (default 500).
• Every operation from the pages that produces a file (document, image of a page, merge) leaves an event in the engine log, and so does the download.

Rules for this tool:

1. Documents to print are described with blocks, or with a template in release/templates/: no PDF libraries in the app, no HTML converted by hand.
2. Data enter the templates only as data: don't build template text by joining values written by people.
3. For a document to download from a route: render without a destination and then return $orch.pdf.send(e, r); to keep it: record + field.
4. Only PDFs with text can be read: a scan gives pages without text (no OCR).
5. Readings and merges are slow compared to a normal route: in scripts, do them in a route called by the page or in a scheduled job, not in the save hooks of records.

Example: the PDF of an order from a route of the app, with the template release/templates/order.json.

routerAdd("GET", "/api/orders/{id}/pdf", (e) => {
  const order = e.app.findRecordById("orders", e.request.pathValue("id"));
  const rows = e.app.findRecordsByFilter("order_rows", "order = {:id}", "code", 0, 0, { id: order.id }).map((x) => ({
    code: x.getString("code"), description: x.getString("description"), qty: x.getInt("qty"), price: x.getFloat("price"),
    amount: x.getInt("qty") * x.getFloat("price") }));
  const r = $orch.pdf.render({ template: "order", data: { number: order.getString("number"), supplier: order.getString("supplier"), rows: rows,
    total: { amount: rows.reduce((s, x) => s + x.amount, 0) } } }, { name: "order-" + order.getString("number") + ".pdf", lang: "it" });
  return $orch.pdf.send(e, r);
}, $apis.requireAuth("users"));

Example: the standard template of the engine, with the PDF saved in the record.

onRecordAfterCreateSuccess((e) => {
  $orch.pdf.render({ template: "@pyrite", data: {
    title: "Delivery report", company: "Rossi Garage", reference: e.record.getString("number"),
    fields: [{ label: "Vehicle", value: e.record.getString("plate") }, { label: "Date", value: e.record.getString("created"), format: "date" }],
    notes: e.record.getString("notes"),
  } }, { record: e.record, field: "report", lang: "it", app: e.app });
  e.next();
}, "deliveries");

Example: the text of a PDF uploaded in a record and page 1 as a preview.

routerAdd("POST", "/api/invoices/{id}/read", (e) => {
  const rec = e.app.findRecordById("invoices", e.request.pathValue("id"));
  const t = $orch.pdf.text({ record: rec, field: "pdf" }, { pages: [1] });
  const total = t.pages[0].pieces.find((p) => p.text.startsWith("Total"));
  $orch.pdf.pageImage({ record: rec, field: "pdf" }, 1, { dpi: 72, record: rec, field: "preview", app: e.app });
  return e.json(200, { text: t.pages[0].text, total: total ? total.text : null });
}, $apis.requireAuth("users"));

Example: from a page of the app, with /__platform/tools.js.

const job = await PyriteTools.run("pdf", "render", { template: "order", data: { number: "2026/42", rows: rows }, format: "pdf" });
if (job.status === "finished") {
  await PyriteTools.download(job.result.file.id);
}

Scheduled jobs (jobs)

The jobs that the app scripts schedule with cronAdd, under control: every run recorded (when it started, how long it took, how it ended, the error), pause and resume that survive a restart, “Run now”, an engine event when a job fails. In the console, the “Scheduled jobs” page with all the apps together.

Turn it on with "tools": ["jobs"] in app.json. Category: Automation.

Functions

$orch.jobs.list()

The app's scheduled jobs (not PocketBase's own), with the schedule (cron expression and in words, English, UTC), pause, next run and last run. failing = the last finished run failed.

Returns: [{ name, schedule, scheduleText, paused, pausedBy, pausedAt, running, nextRun, lastRun: { id, job, source, startedBy, status, startedAt, finishedAt, duration, error } | null, failing }].

routerAdd("GET", "/api/myapp/jobs", (e) => {
  const jobs = $orch.jobs.list();
  return e.json(200, jobs.map((j) => ({ name: j.name, next: j.nextRun, ok: !j.failing })));
}, $apis.requireSuperuserAuth());

$orch.jobs.run(name)

Starts the app's job now, in the background (even if it's paused). If it's already running it throws an error. The run is recorded with source “script”.

Returns: { name, started }.

cronAdd("nightly-totals", "0 2 * * *", () => {
  // … recalculations …
});
routerAdd("POST", "/api/myapp/recalculate", (e) => {
  return e.json(202, $orch.jobs.run("nightly-totals"));
}, $apis.requireSuperuserAuth());

Engine HTTP APIs

The app doesn't call them: they serve the console and the portal. They're listed so that the app doesn't rebuild them.

How to use it

When to use it. The app schedules its jobs as in plain PocketBase, with cronAdd(name, schedule, handler) in pb_hooks: nothing changes in how they are written. The engine records every run of every job (when it started, how long it took, how it ended, the error message), keeps the pause state of each job, and shows all of this to IT in the console, “Scheduled jobs” page and the app page tab, with “Run now”, “Pause” and “Resume”. This works for every PocketBase app, even without the tool turned on: turn the tool on ("tools": ["jobs"]) only if the app's own scripts need $orch.jobs.list() or $orch.jobs.run(name), for example a “Recalculate now” button on an app page.

Rules for the jobs.

• Give every job a stable, readable name (the first argument of cronAdd): it is the name IT sees, the key of the history and of the pause. Renaming a job starts a new history and drops its pause. Use lowercase letters, digits and dashes, for example "nightly-totals".
• The schedule is in UTC, as in PocketBase: "0 2 * * *" runs at 02:00 UTC (03:00 or 04:00 in Italy, depending on daylight saving time). The console writes the schedule in words with “UTC” and the next run in local time. PocketBase checks all five fields together: day of the month and day of the week must both match (not one or the other, as in Unix cron).
• A failed job throws. Let the error leave the handler (or throw your own new Error("…") with a clear message): the run is recorded as failed with that message, the engine writes an event and the console overview shows the job in red. Events are not one per run: one when the job starts failing, then at most a reminder every 6 hours while it keeps failing (with how many runs failed since the last alert), and one when it works again. Every run, with its error, stays in the job history. Don't swallow errors with an empty catch: the job would look fine.
• A paused job doesn't start on its own, even after the app restarts. “Run now” (console or $orch.jobs.run) starts it all the same, once; the “Run” button of the app's PocketBase dashboard counts as “Run now” too (in the history: “PocketBase dashboard” and who pressed it). Write jobs so that an extra run, or a skipped one, does no harm: compute from the data, don't count on “it ran yesterday”.
• Long jobs. A job runs in the background in one of the app's script machines; PocketBase can start a new run while the previous one is still running. If that would be a problem, check at the start (for example a record with the state of the job). “Run now” refuses to start a job that is already running. A restart of the app interrupts the runs in progress: the one IT asks for (“Restart”) and also the automatic one after a change to the app's card (app.json, roles, port, memory, outbound network mode). The automatic restart waits for the jobs started from the pages (rule 8), not for scheduled jobs: the run stays in the history as “interrupted”. Write a long job so that it can be done again (in pieces, keeping in a record how far it got), not all or nothing.
• The engine keeps the last 50 runs of each job in the hidden system collection orch_job_runs, and the pause states in orch_jobs: the app must not write in them. PocketBase's own jobs (__pb…, logs cleanup, automatic backups) and the ones the engine adds to every app by itself (cleanup of the tools' file deposit and of the history) are not app jobs: they are not listed and can't be paused.

// pb_hooks/jobs.pb.js
cronAdd("nightly-totals", "0 2 * * *", () => {
  const orders = $app.findRecordsByFilter("orders", "status = 'paid'", "", 0, 0);
  // … recalculations; on a problem: throw new Error("totals not computed: …")
});

Statistics and estimates (stats)

Counts “beyond Excel” on the app's collections, in one SQL query: groups by fields or by periods of a date (day, ISO week, month, quarter, year, also in a time zone), sums, averages, minimums, maximums, distinct counts, medians and percentiles, with the PocketBase filter. Saved snapshots that scheduled jobs refresh, and functions for small series: median, percentiles, moving average, linear trend, change from the previous period.

Turn it on with "tools": ["stats"] in app.json. Category: Analytics.

Functions

$orch.stats.aggregate(query)

Computes the statistics of a collection in one SQL query. Operations: count, countDistinct, sum, avg, min, max, median, percentile; periods of a date (by): day, week, month, quarter, year. Fields and operations are checked against the collection schema; the filter is PocketBase's, with {:name} placeholders filled from params. System collections can't be counted.

Returns: { collection, groupBy: [{ name, field, by }], measures: [{ name, op, field, p }], rows: [{ <group>: value, <measure>: value }], truncated, timezone, duration }.

One object per group in rows, with the group names and the measure names (default names: count, sum_total, p90_total…; with as you choose them). truncated = there were more rows than limit (default 1000, at most 10000).

const r = $orch.stats.aggregate({
  collection: "orders",
  filter: "status = {:status} && created >= {:from}",
  params: { status: "paid", from: "2026-01-01" },
  groupBy: ["customer", { field: "created", by: "month" }],
  measures: [{ op: "count" }, { op: "sum", field: "total" }, { op: "median", field: "total" }, { op: "percentile", field: "total", p: 90 }],
  sort: "-sum_total",
  timezone: "Europe/Rome",
});
// r.rows[0] → { customer: "…", created_month: "2026-03", count: 12, sum_total: 3400.5, median_total: 250, p90_total: 610 }

From the app pages: POST /api/tools/stats/aggregate (effect: read only), with the permissions of toolAccess in app.json.

$orch.stats.snapshot(name, query?, options?)

Computes the statistics and saves them under a name, in the hidden system collection orch_stats_snapshots. Without query it recomputes the saved one: call it from a cronAdd job to keep it fresh.

Returns: { name, query, result, takenAt, duration }.

cronAdd("stats-sales-by-month", "15 2 * * *", () => {
  $orch.stats.snapshot("sales-by-month", {
    collection: "orders",
    groupBy: [{ field: "created", by: "month" }],
    measures: [{ op: "sum", field: "total" }],
  });
});

$orch.stats.read(name, options?)

The snapshot saved under this name, or null.

Returns: { name, query, result, takenAt, duration } | null.

routerAdd("GET", "/api/myapp/sales", (e) => {
  const s = $orch.stats.read("sales-by-month");
  return e.json(200, s ? s.result.rows : []);
}, $apis.requireAuth());

From the app pages: POST /api/tools/stats/read (effect: read only), with the permissions of toolAccess in app.json.

$orch.stats.median(values)

The median of a list of numbers (null if the list is empty).

Returns: number | null.

const m = $orch.stats.median([3, 1, 4, 1, 5]); // 3

$orch.stats.percentile(values, p)

The percentile p (0–100) of a list of numbers, with linear interpolation like Excel's PERCENTILE.INC.

Returns: number | null.

const p90 = $orch.stats.percentile(prices, 90);

$orch.stats.rolling(values, window)

The moving average: the average of the last window values, for each position (null until the window is full).

Returns: [number | null].

const avg3 = $orch.stats.rolling([10, 20, 30, 40], 3); // [null, null, 20, 30]

$orch.stats.trend(values)

The linear trend of a series (least squares, x = position): slope, intercept, r2 and next, the value expected for the next position.

Returns: { slope, intercept, r2, next, n } | null.

const t = $orch.stats.trend(r.rows.map((x) => x.sum_total)); // t.next = the next month, if the trend holds

$orch.stats.change(values)

Each value compared with the one before: difference and percentage (null where it can't be said).

Returns: [{ value, previous, delta, percent }].

const c = $orch.stats.change([100, 120, 90]); // c[1].percent = 20, c[2].percent = -25

How to use it

When to use it. Whenever a page shows numbers computed on many records: totals per month, average price per model, median delivery time, the top 10 customers, how many different customers bought this year. Don't load the records into the script to add them up in JS, and don't write SQL by hand: aggregate does it in one SQL query on the app database, with fields and operations checked against the collection schema and the PocketBase filter. On a normal PC, 100,000 records are counted in a few tenths of a second.

PocketBase “view” collections or stats? A view collection is a fixed SELECT … GROUP BY written in a migration: the frontend reads it with the normal PocketBase API, with its API rules, and filters and sorts the rows of the result. It is enough, and it is the simplest choice, when:

• the grouping is always the same and known in advance (totals per customer, counts per status);
• COUNT, SUM, AVG, MIN, MAX are enough;
• the filters the page needs are on the columns of the result (the API filter of a view works after the grouping: “totals per month of customer X” needs the customer among the groups of the view).

Use stats when:

• the person chooses what to see: the period (day, week, month…), the grouping, the measures;
• the filter must choose the records before the counts (“orders paid between two dates, by month”), with parameters;
• you need medians, percentiles, distinct counts, periods in a time zone (timezone: "Europe/Rome": an order at 00:30 in Italy belongs to that day, not to the day before as in UTC) or ISO weeks;
• the count is heavy and many people look at it: save a snapshot from a cronAdd job and let the pages read it (read), without counting again;
• you need the trend of a series: moving average, linear trend, change from the previous period.

The query (argument of aggregate, second argument of snapshot):

Operations (op): count (records; with field: the records where the field is filled in), countDistinct (different values of field), sum, avg (number fields), min, max (number or date fields), median, percentile (number fields; p from 0 to 100, with linear interpolation like Excel's PERCENTILE.INC). Empty texts and empty dates don't count; number fields count their 0 (PocketBase writes 0 when a number is missing).

Fields that can be used: number, bool, text, email, url, single select, single relation (the group is the id of the related record: read the names with $app.findRecordsByIds), date and autodate (in groups with by, and in min/max). Not: JSON, files, editor, password, select and relations with several values. A field that doesn't exist, or of the wrong type for the operation, throws an error that names it.

The result. One object per group in rows, with the group names and the measure names: by default the group is called like the field (customer, or created_month for a period) and the measure count, sum_total, avg_total, countDistinct_customer, median_total, p90_total; with as you choose the name (letters, digits and _, not starting with a digit, at most 50 characters). Numbers are numbers, bool are true/false, periods are texts that sort correctly. groupBy and measures in the result describe the columns, duration is in milliseconds.

Snapshots. $orch.stats.snapshot(name, query) computes and saves the result under name (letters, digits, _ . -); $orch.stats.snapshot(name) recomputes it with the saved query; $orch.stats.read(name) gives { name, query, result, takenAt, duration } or null. They are in the hidden system collection orch_stats_snapshots: the app must not write in it. Refresh them from a cronAdd job (see “Scheduled jobs”).

cronAdd("stats-sales-by-month", "15 2 * * *", () => {
  $orch.stats.snapshot("sales-by-month", {
    collection: "orders",
    filter: "status = 'paid'",
    groupBy: [{ field: "created", by: "month" }],
    measures: [{ op: "sum", field: "total" }, { op: "count" }],
    timezone: "Europe/Rome",
  });
});

routerAdd("GET", "/api/myapp/sales", (e) => {
  const s = $orch.stats.read("sales-by-month");
  if (!s) return e.json(200, { months: [] });
  const totals = s.result.rows.map((r) => r.sum_total);
  return e.json(200, {
    months: s.result.rows,
    trend: $orch.stats.trend(totals),
    change: $orch.stats.change(totals),
    average3: $orch.stats.rolling(totals, 3),
    takenAt: s.takenAt,
  });
}, $apis.requireAuth());

Who sees the numbers. Like every tool, stats counts as the server: the API rules of the collection don't apply (rule 6). A route that shows statistics to people checks who they are first, and counts only what they may see (for example with a filter on the user). From the app pages, instead, the engine counts as the person (below).

Small series (median, percentile, rolling, trend, change): lists of numbers already in memory, at most 100,000 values, for example the rows of an aggregate. For big data use aggregate, which counts in SQL.

From the app pages (rule 8 of the common rules), without writing routes: the tool in "tools" and a permission in app.json.

"toolAccess": {
  "stats": { "pages": true, "minRole": "editor", "collections": ["orders"], "maxRows": 500, "snapshots": ["sales-by-month"] }
}

• POST /api/tools/stats/aggregate with the query in the body (the same as aggregate): the count is made as the person, on the records they would see with GET /api/collections/{c}/records. The list rule of the collection applies as in the PocketBase record list (a collection without a list rule is only for superusers: 403); hidden fields and the email of auth collections can't be used in groups, measures or the filter (a field that doesn't exist or that the person can't use gives the same message); @request. and @collection. in the filter are refused (403). Only the collections in collections; at most maxRows rows (default 1000, never more than 10,000: a higher limit is lowered and truncated says so); the filter at most 3500 characters, with the values of params in place (as in the record API); at most 15 seconds per count, two counts from the pages at a time per app and one at a time per person (for guests: per IP address): whoever finds the places taken waits at most 5 seconds, then gets 503 ("try again in a moment"). The filter compares dates in UTC, like the PocketBase API: timezone counts only for the periods of the groups (in the example below the limits of the year are those of Rome). The request limit of the collection (<collection>:list, if the request limit of the app is on: it's off by default) applies, and the app's onRecordsListRequest hooks are called with an empty list: a hook that turns the person away turns away the statistics too. A hook that removes records from the list doesn't apply to the counts (they are made in SQL, not on the records): if the app hides records with a hook, put the condition in the list rule, or don't open stats on that collection.
• POST /api/tools/stats/read { name }: a snapshot saved by the scripts. A snapshot is computed as the server, without the rules of the person, so the pages read only the ones listed in snapshots, and without the saved query: { name, result, takenAt, duration } (404 if it hasn't been computed yet).
• snapshot (which counts as the server) and the functions on small series aren't offered to the pages: a page computes the medians and the trends of its rows by itself.

Example: a page of the app with the totals per month, and the snapshot saved every night.

<script src="/__platform/tools.js"></script>
<script>
  async function salesByMonth(year) {
    const r = await PyriteTools.call("stats", "aggregate", {
      collection: "orders",
      // the filter compares UTC dates: the year in Rome starts at 23:00 UTC of 31 December (winter time, UTC+1)
      filter: "status = 'paid' && created >= {:from} && created < {:to}",
      params: { from: (year - 1) + "-12-31 23:00:00.000Z", to: year + "-12-31 23:00:00.000Z" },
      groupBy: [{ field: "created", by: "month" }],
      measures: [{ op: "count" }, { op: "sum", field: "total" }],
      timezone: "Europe/Rome",
    });
    return r.rows; // [{ created_month: "2026-03", count: 12, sum_total: 3400.5 }, ...]: only the orders this person sees
  }
  async function savedSales() {
    const s = await PyriteTools.call("stats", "read", { name: "sales-by-month" });
    return s.result.rows;
  }
</script>

Coming

Decided for the next versions of the engine, not available yet: don't use them and don't build anything that depends on them. If the app needs one, write it in the delivery notes.

• Bookings (bookings): calendar of shared resources (rooms, courtesy cars, desks) with overlap checks.
• Email and notifications (notifications): sends emails from templates and notices inside the apps, with the mail server configured by IT.

Outbound network: declaring the addresses ("network")

The scripts of an app (pb_hooks and pb_migrations) reach other systems only through the engine. Every call of $http.send, $filesystem.fileFromURL and $filesystem.s3, and the calls PocketBase makes for the app (S3 storage and backups, OAuth2), go through a check of the engine:

1. Only declared addresses. The app lists them in app.json:

json "network": { "allow": ["api.openai.com", "*.example.com", "erp.example.local:8443"], "allowHttp": ["old-erp.example.local"] }

An entry is a host name or an IP address, with the port if needed; without a port it is the normal port of the protocol (443 for https, 80 for http). *.example.com covers the names below example.com, not example.com itself. No * alone, no https:// in front, no paths. An app without "network" can't call any address. Declare only the addresses the app really calls, and write in DELIVERY.md what each one is for: IT approves them when it installs the app (“Upload app” lists them), and from then on it can change them in the app's “Outbound network” tab.

1. Only https. Plain http only for the entries of allowHttp, for internal systems that have no https: the data travels unencrypted.
2. Internal network. The engine checks the real IP address after the DNS lookup and connects to that address. A server in the company network (10.x, 172.16-31.x, 192.168.x) is reached only with an exact entry, without *; the machine itself (127.0.0.1, localhost) and link-local addresses (169.254.x, cloud metadata) only with an entry written as an IP address, but the engine itself and the other apps of this server are never reachable from a script, not even with an entry (403, code app_origin); 0.0.0.0 and multicast addresses never. With * only public addresses are reached: write the company servers in full.
3. Mail. The app's mail server (SMTP, used by $app.newMailClient(), $mails.* and the one-time codes) is an address too: declare it with its port, for example "smtp.example.com:587". The same rules on the IP apply, and every message sent is logged (never recipients, subject or text).
4. Redirects are followed (at most 9) only towards declared addresses.
5. Limits for the scripts: a response of at most 32 MB, and at most 300 seconds per call (timeout in seconds; without it, 120).
6. Errors. A call that isn't allowed throws an exception in English that says what to declare: handle it with try { … } catch (err) { … }. The engine logs every call (method, host, status, size, time: never the content) and IT sees the log in the app's “Outbound network” tab. IT also decides the mode: enforce blocks what isn't declared, warn only logs it.

AI models

• Apps call the AI models (OpenAI, Anthropic, Google Gemini, Mistral, Azure OpenAI, OpenRouter, Groq, DeepSeek…) only from the scripts, with $http.send, never from the browser: in a page the provider key would be visible to anyone who opens it, and the call wouldn't go through the engine. The page calls a route of the app (routerAdd) that checks who the user is and then calls the model.
• The provider's address goes in "network" like any other, for example "api.openai.com".
• The key is never written in the code, in app.json or in the pages: ask IT where it is kept (for now an environment variable of the server; a vault for secrets is coming in the engine).
• The engine reads from the responses the model and the tokens, also in streaming when the provider sends the usage in the last event (with OpenAI Chat Completions ask for it with stream_options: { include_usage: true }), and shows usage and estimated costs on the console's “AI” page. It never records the content of the messages.

routerAdd("POST", "/api/summary", (e) => {
  const text = String(e.requestInfo().body.text || "").slice(0, 20000);
  try {
    const res = $http.send({
      url: "https://api.openai.com/v1/chat/completions",
      method: "POST",
      headers: { "Authorization": "Bearer " + $os.getenv("OPENAI_API_KEY"), "Content-Type": "application/json" },
      body: JSON.stringify({ model: "gpt-5-mini", messages: [{ role: "user", content: "Summarize: " + text }] }),
      timeout: 90,
    });
    if (res.statusCode !== 200) {
      return e.json(502, { message: "The AI service did not answer (" + res.statusCode + ")." });
    }
    return e.json(200, { summary: res.json.choices[0].message.content });
  } catch (err) {
    return e.json(502, { message: String(err.message || err) });
  }
}, $apis.requireAuth());

12 /Testing the app

12.1 Local test, with the engine's executable

The engine's executable (pyrite.exe, IT gives it to you) runs one app on its own, with the same limits and the same $orch tools as the engine, but without the front door, without single sign-on and without the identity lock: you can create users from the dashboard (as a superuser; a sign-up through the APIs is refused with 403 here too) and change name, email and password, and whoever can modify the users (in the example the manager) can also change their own role. The ban on changing one's own role is put by the engine, and is tested in the test engine (12.2). On a copy of the app folder:

pyrite.exe worker --app-dir C:\tests\parts-pricelist superuser upsert <email> <password>
pyrite.exe worker --app parts-pricelist --app-dir C:\tests\parts-pricelist --http 127.0.0.1:8190

The first command creates the database (in C:\tests\parts-pricelist\state\) and a test administrator, with an email and a password chosen by you and used only on this copy; the second starts the app, which applies the migrations. Then:

• http://127.0.0.1:8190/_/ is the app's PocketBase dashboard: you check collections, rules and data, and you create the test users with email, password and role;
• http://127.0.0.1:8190/ are the pages. Without the engine nobody puts the session in the browser: to test as a user, open the browser console and sign in by hand with await pb.collection("users").authWithPassword("email", "password"), then reload;
• the APIs are tested directly, for example with a script, with every role and without login.

The test users and the state\ folder stay on your copy: they do not go into the package. To start again from scratch delete the copy's state\.

The local test also has the tools from the pages (/api/tools/ with toolAccess), the protection from concurrent changes and the outbound network gate in enforce (only the addresses in network), as in the engine.

A static app is tested by opening release\public\index.html with a small local server (for example python -m http.server from the folder).

12.2 Test in a test engine

If IT has a test engine: IT uploads the zip with “Upload app” (if an Admin uploads it, an Owner approves it first; an Owner can also copy the folder into the engine's apps\ and press “Rescan folders”), presses “Start” on the app's page, gives access to the test people (People or Groups) and you enter from the portal as a real person. It is the only way to test the single sign-on, the identity lock (nobody changes their own role, name, email or password), the roles given from the console and the language chosen in the portal.

12.3 What to test, always

1. Every rule of every collection: without login, with every role, calling the APIs directly (not only from the pages). A low role must not read or write what isn't its own. In the test engine (12.2), also, nobody must be able to change their own role.
2. The routerAdd routes: without login (they must answer 401) and with a role that isn't allowed (403).
3. The migrations from an empty database (delete the copy's state\ and restart).
4. The errors: wrong files, missing fields, out-of-range values. The messages must be in the language of the page and understandable.
5. The pages on a small screen (at 390 px wide the page doesn't scroll sideways), in dark theme and in every language of the app.
6. The process output (command window or Status tab of the console) without errors at start.
7. Concurrent changes: the same record open in two windows, saved in both; the second save gets the notice (409), not a silent overwrite, and no change of the pages answers 428.
8. The tools from the pages with every role and without login: whoever toolAccess doesn't allow gets 403 (and doesn't see the button).
9. The network calls of the scripts: they work with the addresses in network; the others are refused with a clear error.

13 /After installation: the Data tab and changes made by hand

In the engine console, the app's “Data” tab (for the Owners and for the Admins who manage the app) opens the app's own PocketBase dashboard, reduced to collections and records (structure included). Settings, logs and internal backups are not there, and in the apps with login neither are the app's users (they are the copy of the engine directory: Access and roles tab); they are managed in the engine's tabs (Access and roles, Logs, Backups, Configuration, Import), and the engine also blocks them on the server side. In a public app the users collection is there: IT creates, changes and removes the users in it one at a time, and every one of these changes leaves an event with who made it. Its structure stays locked, and from the Data tab nobody can sign in as a user of the app (impersonate), empty the collection or send the sign-in emails.

1. IT may change the structure of a collection by hand from the Data tab (a field, an index, a new collection). The engine allows it but writes a warning in the events: “Structure of the collection … changed by hand in the dashboard: it must be carried over to the app package”.
2. For you this means: the package is the truth. If IT tells you about such a change (or you find it in the events), carry it into the package as a new migration; never rely on a change made by hand, which a reinstall from the package would lose.
3. The structure of the app's users collection and the system collections can't be changed from the Data tab.

14 /Checklist before delivery

• ☐ The folder is named as the id and contains only app.json, DELIVERY.md and release\.
• ☐ app.json is valid JSON, with name, kind, port (the one given by IT or 8199), a raised version, the intended access, and tools.
• ☐ The port is a request: neither the code nor the documents of the package count on an address with a fixed port (for example http://server:8199/…); the port can change when IT uploads the app.
• ☐ With login and roles: auth with roleField: "role", roles from the most to the least powerful and defaultRole; the role field exists in a migration with the same values.
• ☐ tools lists all and only the tools used in the scripts and in toolAccess, with the English names ($orch.import.load, { dryRun: true }…).
• ☐ toolAccess opens to the pages only the tools they really use, with minRole where not every user may use them, collections limited to the needed ones, destructiveRole only if the pages must remove data, keyFields for the photo import from the pages.
• ☐ Every collection has the five rules written; no write rule is ""; every rule of an app with login starts with @request.auth.id != ''.
• ☐ users: createRule and deleteRule are null, also in a public app (no sign-up); in the test engine nobody changes their own role.
• ☐ Whose a record is, is written with the user id (relation), never with the email.
• ☐ The routerAdd routes have $apis.requireAuth(...) and check the role; none is under /api/tools/.
• ☐ The fields decided by the server are set in hooks or forbidden in the rules.
• ☐ Handlers don't use variables written outside: shared code is in a module loaded with require inside the handler.
• ☐ SQL only with bound parameters ({:name} and .bind).
• ☐ No login, sign-up, password or “Sign out” page; there is the link to the portal /__platform/portal.
• ☐ Pages: every field has its label, tables are in a container that scrolls, at 390 px the page doesn't scroll sideways; every call answering 401 reloads the page.
• ☐ No resources from the Internet (CDN, fonts, libraries); the SDK is in pb_public\lib\; no Excel, PDF or image library in the pages: the engine tools do it.
• ☐ The changes from the pages carry the $version (with fields, $version is in the list); the 409 is shown in plain words; no 428 in the tests.
• ☐ Scheduled jobs with a stable name, errors not swallowed, long jobs that can be redone after a restart.
• ☐ No address, port or absolute path in the code.
• ☐ No external program, no file outside the folder, no symbolic link or junction in the folder, no external service that IT hasn't asked for.
• ☐ Every address called by the scripts is in network of app.json (and in DELIVERY.md with what it is for); AI models only from the scripts, never from the pages; no key in the code.
• ☐ The migrations start from an empty database and have the function that goes back; the ones already delivered haven't been changed.
• ☐ No state\, pb_data, logs\, node_modules, .git, temporary files, test users or passwords, real personal data.
• ☐ Technical names and code comments in English; texts for people in the app's language(s), plain; numbers and dates in the format of the language.
• ☐ The tests of section 12.3 have been done and are described in DELIVERY.md.
• ☐ No archive of people's data in the zip (section 7.4); initial data is imported with the engine's tool; DELIVERY.md has the “Personal data” line.
• ☐ The app was tested on a Pyrite of your own (section 15, point 10) and DELIVERY.md starts with the engine version it is written for (point 11).
• ☐ The zip has the structure of section 15 and stays within its limits.

15 /Delivery: the zip package and “Upload app”

IT installs the app from the engine console, page “Upload app”: it chooses (or drags) the zip, the engine checks it step by step and, if the real checks pass, installs it. An Owner installs it directly; an app uploaded by an Admin is installed waiting for approval (point 6).

1. The package is a ZIP archive (for example parts-pricelist-0.1.0.zip) with the app folder inside: parts-pricelist/app.json, parts-pricelist/DELIVERY.md, parts-pricelist/release/…. app.json may also be at the root of the zip, but one folder named as the id is the clearest. Only one app per zip.
2. What doesn't go in it: state/ and logs/ (if present they are not installed: the engine creates them; in an update, a state/ folder refuses the package: point 8), pyrite-archivio.json and pyrite-ripristino.json next to app.json (the engine keeps those names for its Archive: they are not installed, and the summary says so), node_modules, .git, program files (.exe, .bat, .ps1…: they are copied but the engine never runs them, and the check points them out).
3. Limits (refused beyond them): zip up to 200 MB, at most 10,000 entries, at most 1 GB once extracted, app.json up to 256 KB and in UTF-8 (a byte order mark, as Notepad writes, is removed with a warning). Paths that are refused: with .., absolute or with a drive (C:), backslashes that climb, Windows reserved names (CON, NUL, COM1…), forbidden characters (: * ? " < > |), names ending with a space or a dot, two names that differ only by upper/lower case, symbolic links, encrypted entries, names that are not UTF-8, control characters, names over 255 characters (400 for the whole path), compression other than store/deflate, a file and a folder with the same name. __MACOSX, .DS_Store, Thumbs.db and desktop.ini are ignored.
4. What “Upload app” really checks (three steps, each with a score):
   • Reading the package: the limits and the paths above, and where the app folder is;
   • App card (app.json): the same rules as “Rescan folders” and “New app” (an app.json with two keys equal apart from upper and lower case is refused): valid, not reserved (also the names of the Windows devices, section 3) and not registered id (the id of an installed app that the person manages makes it an update: point 8), free folder, name, kind, access, port in range, not used by the engine or by another registered app (even a stopped one) and really free, together with port + 1000; valid role names, defaultRole among the roles; as information, the tools open to the pages and who may use them (toolAccess) and the network addresses the app declares (network), with a warning for every wrong entry;
   • Folder structure: release/; release/public for a static app, release/pb_public for a PocketBase app (with index.html), release/pb_hooks required for pocketbase-cron, migrations, delivery notes, the import recipes in release/import/*.json (each is read: an unknown entry makes the recipe not valid and the “Import” tab shows it with the reason); a package that registers routes written under /api/tools/ in release/pb_hooks is refused (with a placeholder that could match there: only a warning) (the summary says file, line and the path to use, /api/<id>/…).
5. What is only a demonstration in this alpha: the steps Security check and Compatibility with the standard show a simulated score, marked “indicative only”, with a note that says it is not a security check. The lines they show are true (program files found, local copy of the SDK, notes present), but they don't review your code: IT reviews the app before starting it (and an Owner before approving it). Don't rely on them, and follow this standard.
6. The installed app stays stopped: it is copied into the engine's apps\ and registered, but it doesn't appear in the portal until IT presses “Start” on its page. If an Admin uploaded it, it is waiting for approval: nothing can start it until an Owner presses “Approve”; then the Admin, who manages it, can start it. Whoever uploaded it does not wait in the dark: the app's page says when the Owners were told (they receive an email, if the engine has mail set up), the uploader can press “Remind the Owners” once a day, and if an Owner presses “Reject” the app is archived and the uploader reads the reason in the Overview (and in an email). Then IT gives access to people or groups, an Owner checks the tools in the Configuration tab, the permissions of the pages in the “Tools for pages” tab and the addresses in the “Outbound network” tab.
7. DELIVERY.md, in plain words, for IT (in the language IT reads; English by default). “Upload app” shows its beginning in the summary, as plain text. README.md, NOTES.md and CONSEGNA.md are accepted too; put only one of them. It says:
   • what the app is for and who uses it;
   • kind, access, proposed port;
   • roles: what each one can do and which one is the base level;
   • engine tools to turn on (the same as in app.json) and the tools open to the pages (toolAccess): which ones, for whom and why;
   • the network addresses (network) and what each one is for;
   • the scheduled jobs: name, when they run (in UTC) and what they do;
   • initial data included (what they are, where they come from);
   • Personal data (mandatory line, section 7.4): which people's data there is, where it comes from, which roles see it; “none” if there is none;
   • the tests done and how to repeat them;
   • what isn't there or would be needed (missing tools, requests in conflict with the standard).
8. Updates. An update is a new zip with the same id and a higher version, loaded from “Upload app” or from the Update from zip button of the app (from there another id is refused). The package passes the same checks as a new one (the port stays the one of the app, and the summary says so); then the engine compares it with the installed version and shows IT what changes (version, fields of app.json, new, removed and renamed migrations), before touching anything. An Owner starts it; if an Admin who manages the app uploads it, it waits for an Owner's approval and meanwhile the current version keeps running. An update waits while jobs started from the pages are running. The swap is all or nothing: the app stops (scheduled jobs in progress are interrupted, as with a restart), a safety copy is made (release\, app.json and the whole state\ without the engine's temporary folders tmp, import, importa, tools\files: files the scripts write in state\ come back too), release\, app.json and the delivery notes in the root (DELIVERY.md, README.md, NOTES.md, CONSEGNA.md; those that the new version does not have are removed) are replaced, the new version starts, applies its new migrations and must answer. If it doesn't (a migration stops, a script doesn't load, a configuration error, no answer), everything goes back as it was by itself, data included, and IT reads why. An app that was stopped starts only for this and stops again. Access, people's roles and links, and the app's record in the engine don't change. Later an Owner can press Go back to the previous version: the delivery notes come back as they were too, and the data written after the update are lost for the app (they are set aside in a copy): the numbering goes back and numbers can repeat (also on a PDF already sent), so it is meant to be used right after an update. The port goes back to the one it had before the update too (if IT changed it in the meantime, it changes it again in the Configuration tab). Rules for whoever prepares an update:
   • The package has the whole release\ (a file that isn't in the package disappears) and the complete app.json.
   • Never a state\ folder: the package is refused, an update never replaces the data. Only release\, app.json and the delivery notes at the root are replaced: other files at the root aren't copied (DELIVERY.md appears in the summary: write there what changes in this version).
   • Migrations are the .js and .ts files of pb_migrations\, as the worker reads them (a .ts runs too). Schema changes arrive only as new migrations, with a new and higher number. A migration already delivered is never changed, renamed or removed: the engine remembers migrations by file name, so a renamed file would run again on the real data. For a renamed or removed one IT gets a strong warning that must be accepted on purpose; a delivered migration whose content changes keeps its name and will not run again, so the change has no effect on the data (IT gets a warning). To change the schema add a new migration; if that migration then stops with an error, the previous version comes back by itself.
   • All the new migrations of an update run in one transaction, on an app with real data: they must not assume empty collections, must not add sample data, and must be quick (IT has a maximum time before the update is undone: a few minutes).
   • id can't change, and neither can the kind between static and PocketBase (pocketbase and pocketbase-cron can). version must be higher, compared as semver (1.0.0-beta.10 comes after 1.0.0-beta.9; what follows + is ignored): otherwise IT gets a warning.
   • Changes to access, port, auth, network, toolAccess, concurrency and tools are shown to IT one by one: say why in DELIVERY.md. What widens permissions (access that becomes public, new entries in network, toolAccess or tools, new roles) is a strong warning that IT must confirm on purpose: the new app.json also overwrites what IT changed from the console (a restricted access, the addresses). A new port is checked as for an installation. If you remove a role, say what happens to the people who have it: their role in the app's data doesn't change by itself.
   • No file whose name ends in .agg-tmp (the engine's temporary files: the package is refused).
9. Without “Upload app” an Owner can still install the app by copying its folder into the engine's apps\ and pressing “Rescan folders” in the console (the app is registered and starts).
10. Test first on your own Pyrite. Whoever writes an app installs it and tests it on a Pyrite of their own before uploading it to the company's engine: the executable is enough, with a double click. There you have the real numbering, history and roles, which the local test of section 12.1 does not have. On the company's engine an app uploaded by an Admin waits for an Owner's approval (point 6): this is intended, do not work around it.
11. For which version. Before delivering, look at the version of the engine where the app will go (it is written in the console) and write it in the first line of DELIVERY.md, for example “For Pyrite 0.7.7”. Do not write the standard's version or the one you remember: what counts is the engine where the app will be installed.