Relationships

A relationship is a column that references another entity. Within your app, that is a real foreign key. Across apps, it is not — and that boundary is deliberate.

references

"serial_id": {
  "type": "uuid",
  "notNull": true,
  "references": { "entity": "device_serials", "column": "id", "onDelete": "cascade" }
}

The referencing column's type must match the referenced column's type — a uuid pointing at a uuid id.

onDelete: "cascade" is the right choice when the child row has no meaning without its parent: a stock level for a location, a service event for a serial. restrict is the right choice when losing the child silently would be worse than a failed delete.

A worked pair:

{
  "entities": {
    "locations": {
      "columns": {
        "id":   { "type": "uuid", "pk": true, "default": "gen_random_uuid()" },
        "code": { "type": "text", "notNull": true },
        "name": { "type": "text", "notNull": true }
      },
      "indexes": [{ "columns": ["tenant_id", "code"], "unique": true }]
    },
    "stock_levels": {
      "columns": {
        "id": { "type": "uuid", "pk": true, "default": "gen_random_uuid()" },
        "location_id": {
          "type": "uuid",
          "notNull": true,
          "references": { "entity": "locations", "column": "id", "onDelete": "cascade" }
        },
        "product_id": { "type": "uuid" },
        "sku":        { "type": "text" },
        "on_hand":    { "type": "numeric(14,3)", "notNull": true, "default": "0", "check": "on_hand >= 0" }
      },
      "checks": ["product_id is not null or sku is not null"],
      "indexes": [
        { "columns": ["location_id"] },
        { "columns": ["tenant_id", "sku"] }
      ]
    }
  }
}

Index the foreign key. A child table without an index on its parent column turns every "the stock for this location" read into a scan.

Cross-app foreign keys are rejected

A references block naming another app's entity fails when the app is applied. An app's schema is private to it.

The way you relate to another app's data is to store its identifier as a plain uuid column, and read the related record through that app's capability:

"product_id": { "type": "uuid", "notNull": true }

"permissions": [
  { "capability": "products.get", "compatible": "^1.0" }
]

A dangling reference is a real cost of that boundary, and it is deliberate: it means one app's schema change can never break another app's tables, and no app can be blocked from deleting a row by a foreign key it has never heard of.

Two consequences worth designing for:

• Validate the reference when you write it, not when you read it. If a product_id has to exist, call products.get in the handler that accepts it.
• Tolerate the record being gone at read time. Render the id, or a "no longer available" state — not an exception.

See Calling another app for the mechanics, and Cross-app lookups for the narrow, declarative way another app's admin UI resolves your ids to a label.

Nested resources over the gateway

Inside your app, a relationship is just a column you filter on. On the public API you usually want the child exposed as a nested resource — /locations/{location_id}/stock rather than /locations/stock?location_id=….

mountCrud takes a parent for exactly this:

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

mountCrud(app, db.stock_levels, {
  path: '/locations/{location_id}/stock',
  columns: ENTITIES.stock_levels.columns,
  parent: { param: 'location_id', column: 'location_id' },
});

That mounts the five standard routes under the nested path, and binds the parent path parameter to the child's foreign-key column: every list is filtered by it, and every create writes it. A caller cannot list one location's stock and receive another's, and cannot create a row under a parent it did not name in the URL.

Declare each nested route as its own capability, exactly as you would a flat one, and give the path parameter a parameters entry:

{
  "type": "define",
  "capability": "locations.stock.list",
  "version": "1.0.0",
  "summary": "List stock levels for one location",
  "route": { "method": "GET", "path": "/locations/{location_id}/stock" },
  "parameters": [
    { "name": "location_id", "in": "path", "required": true,
      "schema": { "type": "string", "format": "uuid" } },
    { "name": "limit", "in": "query",
      "schema": { "type": "integer", "minimum": 1, "maximum": 200 } },
    { "name": "offset", "in": "query", "schema": { "type": "integer", "minimum": 0 } }
  ],
  "response": { "title": "StockLevelPage", "type": "object" }
}

The capability key stays dotted and hierarchical (locations.stock.list) whatever the URL looks like — the key and the path are allowed to diverge, and here they conveniently do not.

Next steps

• Column types — the types a key column may be.
• CRUD — everything mountCrud mounts.
• Cross-app lookups — letting another app resolve your ids.
• Calling another app — reading the record behind a foreign id.