Schema Reference

This is the field-by-field reference for schema.json, the file that defines the tables your app owns. The authoritative JSON Schema is at https://schemas.revenexx.com/schema.schema.json.

When you apply a schema.json, the platform:

• Creates or updates the tables, namespaced to {vendor}__{app}__{entity}.
• Injects tenant_id (and org_id) — you never declare them — and scopes every read and write to the calling tenant.
• Generates the typed data client at src/db.generated.js from the same file.

You do not write migrations. The difference between your schema.json and what is deployed is the change set, computed when the app is applied.

Migrations are additive. That is the single most important thing about this file, and it is covered in Migrations — read it before you ever delete a line from here.

Complete example

{
  "$schema": "https://schemas.revenexx.com/schema.schema.json",
  "entities": {
    "device_serials": {
      "columns": {
        "id":         { "type": "uuid", "pk": true, "default": "gen_random_uuid()" },
        "serial":     { "type": "text", "notNull": true, "unique": true, "check": "length(serial) > 0" },
        "product_id": { "type": "uuid", "notNull": true },
        "status":     { "type": "text", "notNull": true, "default": "'in_stock'",
                        "check": "status in ('in_stock','sold','in_service')" },
        "sold_price": { "type": "numeric(12,2)" },
        "attributes": { "type": "jsonb", "default": "'{}'::jsonb" },
        "sold_at":    { "type": "timestamptz" },
        "created_at": { "type": "timestamptz", "notNull": true, "default": "now()" },
        "updated_at": { "type": "timestamptz", "notNull": true, "default": "now()" }
      },
      "indexes": [
        { "columns": ["product_id"] },
        { "columns": ["status", "created_at"] },
        { "expression": "lower(serial)", "unique": true },
        { "columns": ["sold_at"], "where": "sold_at is not null" }
      ],
      "checks": ["(status <> 'sold') or (sold_at is not null)"],
      "scopeable": ["market"],
      "lookup": { "label": "serial", "search": ["serial"], "columns": ["status"] }
    },
    "service_events": {
      "columns": {
        "id":        { "type": "uuid", "pk": true, "default": "gen_random_uuid()" },
        "serial_id": { "type": "uuid", "notNull": true,
                       "references": { "entity": "device_serials", "column": "id", "onDelete": "cascade" } },
        "kind":      { "type": "text", "notNull": true },
        "notes":     { "type": "text" },
        "occurred_at": { "type": "timestamptz", "notNull": true, "default": "now()" }
      },
      "indexes": [{ "columns": ["serial_id", "occurred_at"] }]
    }
  }
}

Top level

The top-level key is entities. Entity names are snake_case ASCII matching ^[a-z][a-z0-9_]{0,62}$, and become {vendor}__{app}__{entity} in the database — so two apps can both own an orders entity without colliding.

Nothing else is allowed at the top level; the schema is strict, so a typo fails validation.

Entity

Column properties

The property names matter: it is pk, not primary; notNull, not required. There is no enum, no max_length, no precision/scale — those are expressed as a check and in the type itself.

Table-level checks

Constraints spanning more than one column go in the entity's checks array rather than on a column:

"checks": [
  "(status <> 'sold') or (sold_at is not null)",
  "product_id is not null or sku is not null"
]

Each entry is a raw SQL CHECK expression, applied to the table.

The injected columns

tenant_id and org_id are added by the platform. Never declare them — the schema rejects the names, and the platform indexes and enforces them itself. You may name tenant_id in an index. Full detail in Tenant isolation.

Indexes

Declare exactly one of columns or expression per index. Both, or neither, is an error.

"indexes": [
  { "columns": ["product_id"] },
  { "columns": ["status", "created_at"] },
  { "columns": ["serial"], "unique": true },
  { "columns": ["sold_at"], "where": "sold_at is not null" },
  { "expression": "lower(serial)", "unique": true },
  { "expression": "((attributes->>'sku'))" },
  { "columns": ["attributes"], "method": "gin" }
]

Index the columns the database has to search or order by: foreign keys, columns you filter on, columns you sort by, and anything that must stay unique. Each index costs work on every insert and update, so let real query patterns decide rather than guessing.

For per-tenant uniqueness, put tenant_id first in a composite unique index:

{ "columns": ["tenant_id", "code"], "unique": true }

staging

Provisions sibling tables for bulk data work.

"staging": { "mode": "import+delta" }

Reaching the data

Two ways, and neither is a generated CRUD API you can point a partner integration at.

From inside your own function, use the typed client generated from this file — src/db.generated.js, one repository per entity with list, page, get, create, update and delete:

const serials = await db.device_serials.list({
  where: { status: 'in_stock', product_id: id },
  order: 'created_at.desc',
  limit: 50,
});

See The typed client.

From outside, through the capabilities your app declares. mountCrud turns an entity into the five standard REST routes, and a capability in manifest.capabilities.json publishes each of them on the public gateway:

mountCrud(app, db.device_serials, {
  path: '/serials',
  columns: ENTITIES.device_serials.columns,
});

curl "https://api.revenexx.com/v1/serials?status=in_stock&limit=50" \
  -H "X-Revenexx-Tenant: <TENANT_SLUG>" \
  -H "X-Revenexx-Api-Key: rvxk_..."

The route is public because a capability declares it, not because the table exists. See Capabilities and CRUD.

Practical guidance

Use uuid primary keys with gen_random_uuid(). They are safe to generate anywhere and do not collide, and several features require a uuid id column outright — scopeable, lookup, staging: ab.

Use numeric(p,s) for money and timestamptz for time. double precision will quietly turn 1.23 + 4.56 into something you do not want on an invoice, and a timezone-less timestamp is a bug waiting for a tenant in another region.

Never name a column after a reserved word in another language. Column names travel into generated clients for every SDK target the platform emits. A column called class, function, new, default or return breaks code generation for at least one of them, and you find out at build time rather than here.

Add columns as nullable. Existing rows have no value for a new column, so a new notNull column without a default cannot be applied to a populated table.

Give every entity created_at and updated_at. Retrofitting them later is far more painful than declaring them now.

Next steps

• Column types — the allowed types, and default/check as SQL.
• Relationships — references and the app boundary.
• Migrations — how a change is applied, and how a column is removed.
• Scoping — what scopeable buys you.
• Cross-app lookups — the lookup block.