Add a data model
The first tutorial built a Locations app with one entity and plain CRUD. Real apps have more than one table, relationships between them, and custom endpoints that do something CRUD can't. This tutorial grows the Locations app into a small inventory: it adds a stock_levels entity related to locations, a custom batch availability endpoint with input guards, and shows the typed data client in full.
This is for backend developers continuing from the first tutorial. You'll edit schema.json, manifest.json, src/main.js, and scripts/capabilities.js, then regenerate and test.
1. Add a second entity with a relationship
A relationship is a column that references another entity. Add a stock_levels entity: each row belongs to one location and tracks the on-hand and reserved quantity for a product or SKU.
The references block makes location_id a foreign key onto locations.id. onDelete: "cascade" means deleting a location removes its stock rows. A row tracks an item by product_id or sku — the checks block enforces that at least one is present.
{
"$schema": "https://schemas.revenexx.com/schema.schema.json",
"entities": {
"locations": {
"columns": {
"id": { "type": "uuid", "pk": true, "default": "gen_random_uuid()" },
"code": { "type": "text", "notNull": true, "check": "length(code) > 0" },
"name": { "type": "text", "notNull": true, "check": "length(name) > 0" },
"type": { "type": "text", "notNull": true, "default": "'warehouse'", "check": "type in ('warehouse', 'store', 'dropship', 'virtual')" },
"priority": { "type": "integer", "notNull": true, "default": "0" },
"enabled": { "type": "boolean", "notNull": true, "default": "true" },
"created_at": { "type": "timestamptz", "notNull": true, "default": "now()" },
"updated_at": { "type": "timestamptz", "notNull": true, "default": "now()" }
},
"indexes": [
{ "columns": ["tenant_id", "code"], "unique": true },
{ "columns": ["enabled"] }
]
},
"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" },
"reserved": { "type": "numeric(14,3)", "notNull": true, "default": "0", "check": "reserved >= 0" },
"created_at": { "type": "timestamptz", "notNull": true, "default": "now()" },
"updated_at": { "type": "timestamptz", "notNull": true, "default": "now()" }
},
"checks": [
"product_id is not null or sku is not null"
],
"indexes": [
{ "columns": ["location_id"] },
{ "columns": ["tenant_id", "product_id"] },
{ "columns": ["tenant_id", "sku"] }
]
}
}
}
Grant the new entity in the manifest. Bump the version — every schema change is a new version.
{
"name": "locations",
"vendor": "acme",
"version": "0.2.0",
"title": "Locations",
"type": "public",
"permissions": [
{ "entity": "locations", "access": ["read", "create", "update", "delete"] },
{ "entity": "stock_levels", "access": ["read", "create", "update", "delete"] }
]
}
Regenerate the typed client so it knows the new entity:
npx appsdk generate --schema ./schema.json --manifest ./manifest.json --out ./src --target js
2. The typed data client in depth
createDb() returns one client per entity. The surface is the same for every entity, and it's worth knowing exactly, because the shapes are easy to get wrong.
await db.stock_levels.list({ where, order, limit, offset }); // → Row[] (an array)
await db.stock_levels.page({ where, order, limit, offset }); // → { items, total, limit, offset }
await db.stock_levels.get(id); // → Row | null (null if missing — never throws)
await db.stock_levels.create(data); // → Row
await db.stock_levels.update(id, patch); // → Row
await db.stock_levels.delete(id); // → void
The details that bite:
list()returns an array, not a tuple. Writeconst rows = await db.x.list(...). Neverconst [rows] = await db.x.list(...)— that destructures the first row, not the array. When you need a total, usepage().orderis a string,'column.direction', e.g.'created_at.desc'or'priority.asc'. Never an array.wherevalues are equality filters only —{ where: { location_id: id, enabled: true } }. For anything richer, fetch and filter in code, or model it as a custom route.get()returnsnullfor a missing id; it never throws a 404. Check the result.
3. A custom action route with guards
CRUD covers reading and writing rows. An availability check is different: it takes a batch of items, sums stock across locations, and answers whether each item is orderable. That's a custom route — your own logic, mounted with app.post.
The job of a custom route's first few lines is guarding the input: reject bad requests with a clear 4xx before touching data. The router gives you badRequest, notFound, forbidden, conflict, and the HttpError class for exact status codes.
Add this to buildApp(db) in src/main.js, after the mountCrud calls:
const { createApp, mountCrud, badRequest } = require('@revenexx/app-sdk/router');
const num = (v) => Math.round(Number(v) * 1000) / 1000;
// Guard + normalize the request body into a clean list of items.
function parseItems(body) {
const items = Array.isArray(body?.items) ? body.items : null;
if (!items || items.length === 0) throw badRequest("'items' array is required");
if (items.length > 200) throw badRequest('at most 200 items per call');
return items.map((raw, i) => {
if (!raw.product_id && !raw.sku) throw badRequest(`items[${i}] needs 'product_id' or 'sku'`);
return { product_id: raw.product_id ?? null, sku: raw.sku ?? null, quantity: num(raw.quantity ?? 1) };
});
}
// Inside buildApp(db), after the mountCrud calls:
mountCrud(app, db.stock_levels, { path: '/locations/stock', columns: ENTITIES.stock_levels.columns });
app.post('/locations/availability', async (c) => {
const items = parseItems(c.body);
// One read for enabled locations, one for stock — not a query per item.
const locations = await db.locations.list({ where: { enabled: true }, order: 'priority.asc', limit: 1000 });
const locationIds = new Set(locations.map((l) => l.id));
const allStock = (await db.stock_levels.list({ limit: 10000 })).filter((r) => locationIds.has(r.location_id));
const availability = items.map((item) => {
const rows = allStock.filter((r) =>
(item.product_id && r.product_id === item.product_id) ||
(item.sku && r.sku === item.sku));
const onHand = rows.reduce((sum, r) => sum + Number(r.on_hand), 0);
const reserved = rows.reduce((sum, r) => sum + Number(r.reserved), 0);
const available = num(onHand - reserved);
return {
product_id: item.product_id,
sku: item.sku,
requested: item.quantity,
available,
tracked: rows.length > 0,
orderable: rows.length > 0 ? available >= item.quantity : false,
};
});
return c.json({ availability });
});
Two patterns worth keeping:
- Guard first, work second.
parseItemsthrows abadRequestfor every shape of bad input. By the time the handler runs, the data is clean. - Read in bulk, not per item. The handler issues one query for locations and one for stock, then matches in memory. A query per item (an N+1 loop) is the classic latency trap. When a handler needs data from several sources, run the reads in parallel with
Promise.allrather than awaiting them back to back.
4. Declare the new capabilities
The gateway only exposes capabilities your manifest declares, so add the new routes to scripts/capabilities.js. A custom route declares its request and response schemas; the gateway validates live requests against the request schema, so it must match exactly what your handler requires — never stricter.
const AVAILABILITY_ITEM = {
title: 'AvailabilityItem',
type: 'object',
properties: {
product_id: { type: ['string', 'null'], format: 'uuid' },
sku: { type: ['string', 'null'] },
quantity: { type: ['number', 'null'], description: 'Requested quantity (default 1).' },
},
anyOf: [{ required: ['product_id'] }, { required: ['sku'] }],
};
const AVAILABILITY_REQUEST = {
title: 'AvailabilityRequest',
type: 'object',
required: ['items'],
properties: {
items: { type: 'array', items: AVAILABILITY_ITEM, minItems: 1, maxItems: 200 },
},
};
const AVAILABILITY_RESPONSE = {
type: 'object',
properties: {
availability: {
type: 'array',
items: {
title: 'ItemAvailability',
type: 'object',
properties: {
product_id: { type: ['string', 'null'], format: 'uuid' },
sku: { type: ['string', 'null'] },
requested: { type: 'number' },
available: { type: 'number' },
tracked: { type: 'boolean' },
orderable: { type: 'boolean' },
},
},
},
},
};
// Add to the capabilities array:
// def('locations.stock.list', 'List stock levels', { method: 'GET', path: '/locations/stock' }, { parameters: PAGINATION_PARAMS, response: pageOf(STOCK) }),
// def('locations.stock.get', 'Read one stock level', { method: 'GET', path: '/locations/stock/{id}' }, { response: STOCK }),
// def('locations.stock.create', 'Create a stock level', { method: 'POST', path: '/locations/stock' }, { request: STOCK_INPUT, response: STOCK }),
// def('locations.stock.update', 'Update a stock level', { method: 'PUT', path: '/locations/stock/{id}' }, { request: STOCK_INPUT, response: STOCK }),
// def('locations.stock.delete', 'Delete a stock level', { method: 'DELETE', path: '/locations/stock/{id}' }, { response: DELETED }),
// def('locations.availability', 'Batch availability across locations', { method: 'POST', path: '/locations/availability' }, { request: AVAILABILITY_REQUEST, response: AVAILABILITY_RESPONSE }),
Build the STOCK item schema the same way LOCATION was built in the first tutorial — from schema.entities.stock_levels.columns. Then regenerate:
node scripts/capabilities.js
Add pagination params only to the stock.list capability. The availability route is a batch read with a single response object, so it takes no pagination params.
5. Test the new surface
Extend test/locations.test.js with the relationship and the guarded route. Test the 4xx paths too — a guard you never test is a guard that can rot.
test('availability sums stock across locations', async () => {
await call('POST', '/locations/defaults');
const [loc] = (await call('GET', '/locations')).body.items;
await call('POST', '/locations/stock', { body: { location_id: loc.id, sku: 'WIDGET-1', on_hand: 10, reserved: 2 } });
const r = await call('POST', '/locations/availability', { body: { items: [{ sku: 'WIDGET-1', quantity: 5 }] } });
assert.equal(r.body.availability[0].available, 8);
assert.equal(r.body.availability[0].orderable, true);
});
test('availability rejects an empty items array', async () => {
const r = await call('POST', '/locations/availability', { body: { items: [] } });
assert.equal(r.code, 400);
});
test('an item with neither product_id nor sku is a 400', async () => {
const r = await call('POST', '/locations/availability', { body: { items: [{ quantity: 1 }] } });
assert.equal(r.code, 400);
});
npm test
Troubleshooting
- A write to
stock_levelsis denied. The manifest grants are per entity — confirmstock_levelshas thecreate/updateaccess you need, then regenerate the client. db.stock_levelsis undefined. The client wasn't regenerated after the schema change. Re-runappsdk generate.- The gateway rejects a valid availability request. The request schema in
capabilities.jsis stricter than the handler. Alignrequired/anyOfwith whatparseItemsactually enforces, and regenerate. - The availability check is slow. Confirm you read locations and stock once each and match in memory, rather than querying per item.
Where to go next
- Deploy with the CLI — push the updated app and verify the new capabilities live, plus caching for the read-heavy ones.
- App SDK — the full data-client and router reference.
- App Marketplace — publishing and lifecycle.