# Custom APIs — timesheet demo You are seeing the PUBLIC view: every route this API exposes, whether or not you could call it. To see **your own** documentation — only the routes your key can call — append `?key=` to this URL, or send it as a bearer token. Get a key from `GET /keys`; it is public and needs no signup. The two views are worth diffing: what disappears is what that key is not allowed to do. ## The challenges `GET /challenges` serves them as data — persona, request, body and expected status for every step. Public, no key needed. Start there if you were told to work through them. - **Seven reads** (`reads`, ids `challenge-1` … `challenge-6`, with `challenge-3b` between 3 and 4). No state is consumed, so run them in any order and as often as you like. - **A seven-step write sequence** (`writeSequence`), in order. Step 1 creates a timesheet and returns its id; every later `{id}` is that id. That is what makes the writes repeatable — a timesheet approves exactly once, so a step naming a fixed id would pass for the first visitor and 409 for everyone after. ## This deployment Three companies share this API, and **read scope and write scope are separate axes** — holding a permission somewhere does not say where you hold it: - **Company 1 — Harbourline Hospitality** and **Company 2 — Kestrel Facilities Group**: curated demo data, **read-only for every key**. A write against them is a 404 by permission — the refusal does not admit the row exists, and it is not a bug. - **Company 3 — Scratch Sandbox**: the only writable company, shared by every visitor. Nothing written there is precious — writes and approvals are meant to be attempted, and the whole database rebuilds daily at 04:00 UTC. The published write sequence provisions the row it acts on, so it never depends on what another visitor did. Entries in `GET /activity` can outlive the rows they describe — a 404 on one of them means the rebuild has been through, not that you were refused. | Key | Home company | Reads companies | Writes companies | |---|---|---|---| | Alice Nguyen | Harbourline Hospitality | 1, 3 | 3 | | Sam Okafor | Harbourline Hospitality | 1, 3 | 3 | | Priya Raman | Harbourline Hospitality | 1, 3 | — (read-only) | | Omar Haddad | Kestrel Facilities Group | 2 | — (read-only) | | Tomas Ferreira | Harbourline Hospitality | 1, 3 | 3 | So the duty manager can *see* every Harbourline timesheet but can *approve* only sandbox ones — try both; `GET /me` pre-commits each key to its refusals. ## Headers Send `authorization: Bearer `. A missing or invalid credential is a 401; a valid credential whose permission scope is empty is a 403 (§9.3). Keys are public — `GET /keys` returns one per persona, no signup — and rotate every 2 hours on clock-aligned boundaries, with a 15-minute grace for the previous set. A 401 means the key aged out: fetch `/keys` again. THIS APPLIES TO EVERY ROUTE unless that route says otherwise: a route that is open carries its own `auth` field saying so (§4.3). No `auth` field on a route means exactly this note. | Request header | When | Meaning | |---|---|---| | `content-type` | required for POST and PATCH | Must be `application/json` — it is the only type parsed (§4.2). Any other type leaves the body EMPTY, so you get validation errors naming missing fields rather than an error about the header. Malformed JSON is a 400; a body over the route limit is a 413. | | `x-ua-request-id` | optional | Your own correlation id, echoed back on the response (§4.1). Must match `^[A-Za-z0-9._:-]{1,128}$`; anything else is DROPPED SILENTLY — never a 400, and never echoed, so a missing echo is your signal it was rejected. Never used for authorization. | | Response header | When | Meaning | |---|---|---| | `x-api-request-id` | always present | The server request id — the SAME value as `meta.requestId` in the body, the server logs' requestId, and the audit `correlation_id`. This is the support key: quote it in a bug report and the whole request can be traced. Present on every response including errors, so it is readable even when there is no envelope to parse. | | `x-ua-request-id` | conditional — only when you sent a valid one | Echo of your own `x-ua-request-id` (§4.1). Absent if you sent none OR if what you sent failed validation — the two are indistinguishable, so treat a missing echo as "my id did not get through" and correlate on `x-api-request-id` instead. | | `cache-control` | always present | `no-store` by default on EVERY response, including errors and custom routes — responses are tenant-scoped and permission-filtered, and this API does not require the `Authorization` header that would otherwise stop a shared cache storing them (RFC 9111 §3.5). Do not cache a response yourself unless a route sends a different value; a route that IS cacheable overrides it and says so in its own documentation. | ## Conventions - Field names are camelCase everywhere the caller meets them — response bodies, request bodies, filter and sort params (api-format spec §3). The underlying column names are NOT a second accepted spelling: sending `work_date` where the field is `workDate` is a 400, exactly like any unknown field. - Five uniform route types per resource: GET (single member, GET //:id), SEARCH (collection, GET / with query-param filters), POST (create, POST /), PATCH (update — JSON Merge Patch RFC 7386, omitted fields untouched, null clears; no PUT), DELETE. - Responses are always wrapped in a success envelope `{ data, meta }`; `data` is an object (member) or array (SEARCH); `meta.requestId` always present, `meta.page` only for SEARCH (§4.11). - Errors use one envelope `{ error: { code, message, details?, requestId } }` (§4.11). - Filter grammar (SEARCH, §8.4): `?field=value` (eq); `?field[in]=a,b`; date/number also `[gt]/[gte]/[lt]/[lte]`; text also `[like]` (ILIKE %value%); JSONB dotted-path `?col.subkey=value`; external keys `?externalKey.=value` / `[in]` / `[isnull]` (key absent — reconciliation sweep) / `[notnull]`. Unknown filter → 400. - Sorting: `?sort=field` / `?sort=-field` (desc), comma-separated for tiebreaks; the unique `id` is always appended so paging is deterministic (§8.4). A route may restrict which fields are sortable, separately from which are filterable — its Layer-2 detail lists them when it does, and anything else is a 400. - Pagination: `?limit=&offset=`; omitted limit = 25, clamped to a max of 100 (framework hard max 1000); a route may narrow these — its Layer-2 detail says so when it does. `?includeTotalCount` is OFF app-wide, so `totalItems`/`totalPages` are never returned (§8.4). - Views (§8.6): `?view=` expands declared associations; no view = base root-only shape. - Auth is on by default (§1). Permissions derive as `customapis__` unless overridden (§7.3). Tenancy is record-derived and enforced automatically (§7.2). - Bigint ids/FKs serialize as JSON numbers in the API (§14.1). - Dates and times are ISO 8601 in both directions, UTC everywhere. A DATE column takes `2026-08-12` (or a date-time, truncated to its UTC date); a timestamp column takes a date-time WITH an offset (`2026-08-12T07:00:00Z`, `…+10:00`) or a bare date meaning midnight UTC; a TIME column takes `07:00`, `07:00:00` or `07:00:00.500`. A date-time with NO offset is a 400 — UTC, server-local and caller-local are three different instants. Non-ISO input is never guessed at (`03/04/2026` is two different dates depending on the reader). - Date filters: a bare date on a timestamp column means THAT WHOLE UTC DAY, not midnight exactly — `?at=2026-08-10` is `>= 2026-08-10T00:00Z AND < 2026-08-11T00:00Z`. So `[gt]` means "after that day" and `[lte]` includes all of it. A full date-time is an instant and is compared as one. Note the day is a UTC day: if your users think in a local day, send explicit instants. - Dates render as `2026-08-10`, timestamps as `2026-08-10T07:00:00.000Z` (always UTC, milliseconds), times as `07:00:00`. The original offset a caller sent is not preserved. ## Errors | Status | Code | Meaning | |---|---|---| | 200 | `ok` | get/update/search/delete (delete returns the object) | | 201 | `created` | create | | 400 | `validation_failed` | body/schema or unknown filter | | 400 | `audit_message_required` | route requires an `auditMessage` body field but it was absent (§10.4) | | 401 | `unauthenticated` | no/invalid credential | | 402 | `payment_required` | a billing/quota gate rejected the request (§4.11) | | 403 | `permission_denied` | empty scope — the permission itself is denied (§9.3) | | 404 | `not_found` | not found / cross-tenant / out-of-scope — indistinguishable (§9.3) | | 409 | `conflict` | any uniqueness conflict (§4.7) | | 410 | `gone` | the resource is permanently gone (§4.11) | | 422 | `unprocessable_entity` | a well-formed request rejected by a business/semantic rule (§4.11) | | 428 | `precondition_required` | request must be conditional, e.g. optimistic concurrency (§4.11, §18) | | 429 | `too_many_requests` | rate limited (§4.11) | | 503 | `service_unavailable` | route/app temporarily unavailable, e.g. maintenance (§4.11) | | 413 | `payload_too_large` | body over maxBodySize (§4.2) | | 500 | `internal_error` | unexpected | > 403 is used ONLY when the permission itself is denied (empty scope), decided before any load. Cross-tenant, out-of-scope, and not-found are indistinguishable → 404 — INCLUDING an out-of-scope or nonexistent parent FK on POST (404-on-POST), even though the collection URL exists. The error body never identifies which FK failed (§9.3). ## Entities | Entity | Tenant scoping | | |---|---|---| | `demo_events` | non-scoped (reference table) | demo_events — non-scoped (reference table); PK id; fields: id, eventType, model, tenantId, objectData, correlationId, emittedAt. | | `departments` | tenant-scoped (via departments__location_id → locations__organisation_id) | departments — tenant-scoped (via departments__location_id → locations__organisation_id); PK id; fields: id, locationId, name, createdById, createdByDisplayName, createdAt, lastUpdatedById, lastUpdatedByDisplayName, lastUpdatedAt, tenantId. | | `employees` | tenant-scoped (via employees__department_id → departments__location_id → locations__organisation_id) | employees — tenant-scoped (via employees__department_id → departments__location_id → locations__organisation_id); PK id; fields: id, departmentId, fullName, email, userId, createdById, createdByDisplayName, createdAt, lastUpdatedById, lastUpdatedByDisplayName, lastUpdatedAt, tenantId. | | `invoices` | tenant-scoped (via invoices__department_id → departments__location_id → locations__organisation_id) | invoices — tenant-scoped (via invoices__department_id → departments__location_id → locations__organisation_id); PK id; fields: id, departmentId, reference, amount, status, createdById, createdByDisplayName, createdAt, lastUpdatedById, lastUpdatedByDisplayName, lastUpdatedAt, tenantId. | | `local_users` | non-scoped (reference table) | local_users — non-scoped (reference table); PK id; fields: id, displayName, persona. | | `locations` | tenant-scoped (direct via organisationId) | locations — tenant-scoped (direct via organisationId); PK id; fields: id, organisationId, name, createdById, createdByDisplayName, createdAt, lastUpdatedById, lastUpdatedByDisplayName, lastUpdatedAt, tenantId. | | `organisations` | non-scoped (reference table) | organisations — non-scoped (reference table); PK id; fields: id, name, createdById, createdByDisplayName, createdAt, lastUpdatedById, lastUpdatedByDisplayName, lastUpdatedAt. | | `pgrm_audit_log` | tenant-scoped (direct via tenantId) | pgrm_audit_log — tenant-scoped (direct via tenantId); PK id; fields: id, tenantId, partitionValue, entityType, entityId, action, actorId, actorDisplayName, changes, systemMessage, userMessage, correlationId, userAgentRequestId, context, createdAt. | | `timesheets` | tenant-scoped (via timesheets__employee_id → employees__department_id → departments__location_id → locations__organisation_id) | timesheets — tenant-scoped (via timesheets__employee_id → employees__department_id → departments__location_id → locations__organisation_id); owner-scoped (rows private to their owner); PK id; fields: id, employeeId, workDate, startAt, endAt, hours, status, costRate, note, ownerId, ownerDisplayName, createdById, createdByDisplayName, createdAt, lastUpdatedById, lastUpdatedByDisplayName, lastUpdatedAt, tenantId. | ### Relationships - departments → locations (belongsTo via locationId) - employees → departments (belongsTo via departmentId) - employees → local_users (belongsTo via userId) - invoices → departments (belongsTo via departmentId) - locations → organisations (belongsTo via organisationId) - timesheets → employees (belongsTo via employeeId) - timesheets → local_users (belongsTo via ownerId) ## Routes | | Route | What it does | |---|---|---| | `SEARCH` (HTTP `GET`) | `/activity` | Every committed change in your company, newest first: who, what, when, the field-level diff, and the written reason where one was required. | | `GET` | `/challenges` | Seven read challenges and a seven-step write sequence, with the status to expect at every step. Public, like `/keys`. | | `GET` | `/employees/:id` | GET employees | | `SEARCH` (HTTP `GET`) | `/employees` | SEARCH employees | | `GET` | `/events/recent` | The most recent domain events, newest first. Public, like the sandbox. | | `GET` | `/invoices/:id` | GET invoices | | `SEARCH` (HTTP `GET`) | `/invoices` | SEARCH invoices | | `GET` | `/keys` | The current demo key set. Public by design — start here. | | `GET` | `/me` | Who this key is, what it can reach, and what it will be refused. | | `POST` | `/timesheets/:id/approve` | Approve a submitted timesheet. Needs a written reason in `auditMessage`, which lands in the audit trail. Approving twice is a 409; approving outside your write scope — the Scratch Sandbox here — is a 404. `costRate` appears only if you can read it — otherwise the field is absent, not null. [owner-scoped: results are per-caller] *(owner-scoped — results are per-caller)* | | `GET` | `/timesheets/:id` | One timesheet by id. `costRate` appears only if you can read it — otherwise the field is absent, not null. [owner-scoped: results are per-caller] *(owner-scoped — results are per-caller)* | | `POST` | `/timesheets` | Submit a timesheet. The `employeeId` must be in your write scope — the Scratch Sandbox here — or you get a 404; `GET /me` names one your key can use. `costRate` appears only if you can read it — otherwise the field is absent, not null. [owner-scoped: results are per-caller] *(owner-scoped — results are per-caller)* | | `SEARCH` (HTTP `GET`) | `/timesheets` | List the timesheets in your read scope. `costRate` appears only if you can read it — otherwise the field is absent, not null. [owner-scoped: results are per-caller] *(owner-scoped — results are per-caller)* | ### `SEARCH /activity` Every committed change in your company, newest first: who, what, when, the field-level diff, and the written reason where one was required. Permission: `customapis_read_activity` | Filter | Operators | |---|---| | `entityType` | eq, ne, in, notIn, isnull, notnull, like | | `entityId` | eq, ne, in, notIn, isnull, notnull, gt, gte, lt, lte | | `action` | eq, ne, in, notIn, isnull, notnull, like | | `actorId` | eq, ne, in, notIn, isnull, notnull, gt, gte, lt, lte | | `correlationId` | eq, ne, in, notIn, isnull, notnull, like |
Full detail ```json { "id": "search_activity", "method": "SEARCH", "httpMethod": "GET", "path": "/activity", "operation": "search", "model": "pgrm_audit_log", "purpose": "Every committed change in your company, newest first: who, what, when, the field-level diff, and the written reason where one was required.", "permission": "customapis_read_activity", "specialRules": [ "Filter allow-list (§8.4): only the filterable fields above are accepted; id, tenantId, partitionValue, actorDisplayName, changes, systemMessage, userMessage, userAgentRequestId, context, createdAt are real columns but are rejected with a 400 on this route. To allow one, add it to the route's `filters.allow` — see the per-field cost of doing so.", "Sort restricted (§8.4): `?sort=` accepts only id, createdAt; anything else is a 400. Sorting is governed separately from filtering, so a field being filterable above does not make it sortable.", "Accepts the optional tenant selector `?tenantId=` to narrow the result to one tenant within the caller's scope; a tenant outside scope returns an empty page, never a 403/404 (search-tenant-narrowing spec §2)." ], "fieldSemantics": { "createdAt": "reserved managed column — server-authoritative, read-only on read (§16)." }, "filterableFields": [ { "field": "entityType", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "like" ] }, { "field": "entityId", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "gt", "gte", "lt", "lte" ] }, { "field": "action", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "like" ] }, { "field": "actorId", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "gt", "gte", "lt", "lte" ] }, { "field": "correlationId", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "like" ] } ], "restrictedFilters": [ { "field": "id", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "tenantId", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "partitionValue", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "actorDisplayName", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "changes", "cost": "JSONB predicate — unindexed in the standard shape, so it scans the filtered set. Cost grows with the table, not the page." }, { "field": "systemMessage", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "userMessage", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "userAgentRequestId", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "context", "cost": "JSONB predicate — unindexed in the standard shape, so it scans the filtered set. Cost grows with the table, not the page." }, { "field": "createdAt", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." } ], "sortableFields": [ "id", "createdAt" ], "defaultSort": "-createdAt, -id" } ```
### `GET /challenges` Seven read challenges and a seven-step write sequence, with the status to expect at every step. Public, like `/keys`. Permission: `none (public)`
Full detail ```json { "id": "get_challenges", "method": "GET", "httpMethod": "GET", "path": "/challenges", "operation": "custom", "purpose": "Seven read challenges and a seven-step write sequence, with the status to expect at every step. Public, like `/keys`.", "permission": "none (public)", "specialRules": [], "fieldSemantics": {}, "auth": "none — no credential required; this route is open, unlike the rest of the API (§4.3). The caller is genuinely anonymous: there is no user behind the request, so audit entries carry no actor." } ```
### `GET /employees/:id` GET on employees. Permission: `customapis_get_employees (auto-derived)`
Full detail ```json { "id": "get_employees_id", "method": "GET", "httpMethod": "GET", "path": "/employees/:id", "operation": "get", "model": "employees", "purpose": "GET on employees.", "permission": "customapis_get_employees (auto-derived)", "specialRules": [], "fieldSemantics": { "createdById": "reserved managed column — server-authoritative, read-only on read (§16).", "createdByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "createdAt": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedById": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedAt": "reserved managed column — server-authoritative, read-only on read (§16)." } } ```
### `SEARCH /employees` SEARCH on employees. Permission: `customapis_search_employees (auto-derived)` | Filter | Operators | |---|---| | `departmentId` | eq, ne, in, notIn, isnull, notnull, gt, gte, lt, lte | | `fullName` | eq, ne, in, notIn, isnull, notnull, like |
Full detail ```json { "id": "search_employees", "method": "SEARCH", "httpMethod": "GET", "path": "/employees", "operation": "search", "model": "employees", "purpose": "SEARCH on employees.", "permission": "customapis_search_employees (auto-derived)", "specialRules": [ "Filter allow-list (§8.4): only the filterable fields above are accepted; id, email, userId, createdById, createdByDisplayName, createdAt, lastUpdatedById, lastUpdatedByDisplayName, lastUpdatedAt are real columns but are rejected with a 400 on this route. To allow one, add it to the route's `filters.allow` — see the per-field cost of doing so.", "Accepts the optional tenant selector `?organisationId=` to narrow the result to one tenant within the caller's scope; a tenant outside scope returns an empty page, never a 403/404 (search-tenant-narrowing spec §2)." ], "fieldSemantics": { "createdById": "reserved managed column — server-authoritative, read-only on read (§16).", "createdByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "createdAt": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedById": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedAt": "reserved managed column — server-authoritative, read-only on read (§16)." }, "filterableFields": [ { "field": "departmentId", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "gt", "gte", "lt", "lte" ] }, { "field": "fullName", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "like" ] } ], "restrictedFilters": [ { "field": "id", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "email", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "userId", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "createdById", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "createdByDisplayName", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "createdAt", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "lastUpdatedById", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "lastUpdatedByDisplayName", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "lastUpdatedAt", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." } ], "defaultSort": "id" } ```
### `GET /events/recent` The most recent domain events, newest first. Public, like the sandbox. Permission: `none (public)`
Full detail ```json { "id": "get_events_recent", "method": "GET", "httpMethod": "GET", "path": "/events/recent", "operation": "custom", "purpose": "The most recent domain events, newest first. Public, like the sandbox.", "permission": "none (public)", "specialRules": [], "fieldSemantics": {}, "auth": "none — no credential required; this route is open, unlike the rest of the API (§4.3). The caller is genuinely anonymous: there is no user behind the request, so audit entries carry no actor." } ```
### `GET /invoices/:id` GET on invoices. Permission: `customapis_get_invoices (auto-derived)`
Full detail ```json { "id": "get_invoices_id", "method": "GET", "httpMethod": "GET", "path": "/invoices/:id", "operation": "get", "model": "invoices", "purpose": "GET on invoices.", "permission": "customapis_get_invoices (auto-derived)", "specialRules": [], "fieldSemantics": { "createdById": "reserved managed column — server-authoritative, read-only on read (§16).", "createdByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "createdAt": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedById": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedAt": "reserved managed column — server-authoritative, read-only on read (§16)." } } ```
### `SEARCH /invoices` SEARCH on invoices. Permission: `customapis_search_invoices (auto-derived)` | Filter | Operators | |---|---| | `departmentId` | eq, ne, in, notIn, isnull, notnull, gt, gte, lt, lte | | `status` | eq, ne, in, notIn, isnull, notnull, like |
Full detail ```json { "id": "search_invoices", "method": "SEARCH", "httpMethod": "GET", "path": "/invoices", "operation": "search", "model": "invoices", "purpose": "SEARCH on invoices.", "permission": "customapis_search_invoices (auto-derived)", "specialRules": [ "Filter allow-list (§8.4): only the filterable fields above are accepted; id, reference, amount, createdById, createdByDisplayName, createdAt, lastUpdatedById, lastUpdatedByDisplayName, lastUpdatedAt are real columns but are rejected with a 400 on this route. To allow one, add it to the route's `filters.allow` — see the per-field cost of doing so.", "Accepts the optional tenant selector `?organisationId=` to narrow the result to one tenant within the caller's scope; a tenant outside scope returns an empty page, never a 403/404 (search-tenant-narrowing spec §2)." ], "fieldSemantics": { "createdById": "reserved managed column — server-authoritative, read-only on read (§16).", "createdByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "createdAt": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedById": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedAt": "reserved managed column — server-authoritative, read-only on read (§16)." }, "filterableFields": [ { "field": "departmentId", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "gt", "gte", "lt", "lte" ] }, { "field": "status", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "like" ] } ], "restrictedFilters": [ { "field": "id", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "reference", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "amount", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "createdById", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "createdByDisplayName", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "createdAt", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "lastUpdatedById", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "lastUpdatedByDisplayName", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "lastUpdatedAt", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." } ], "defaultSort": "id" } ```
### `GET /keys` The current demo key set. Public by design — start here. Permission: `none (public)`
Full detail ```json { "id": "get_keys", "method": "GET", "httpMethod": "GET", "path": "/keys", "operation": "custom", "purpose": "The current demo key set. Public by design — start here.", "permission": "none (public)", "specialRules": [], "fieldSemantics": {}, "auth": "none — no credential required; this route is open, unlike the rest of the API (§4.3). The caller is genuinely anonymous: there is no user behind the request, so audit entries carry no actor." } ```
### `GET /me` Who this key is, what it can reach, and what it will be refused. Permission: `none (public)`
Full detail ```json { "id": "get_me", "method": "GET", "httpMethod": "GET", "path": "/me", "operation": "custom", "purpose": "Who this key is, what it can reach, and what it will be refused.", "permission": "none (public)", "specialRules": [], "fieldSemantics": {} } ```
### `POST /timesheets/:id/approve` Approve a submitted timesheet. Needs a written reason in `auditMessage`, which lands in the audit trail. Approving twice is a 409; approving outside your write scope — the Scratch Sandbox here — is a 404. `costRate` appears only if you can read it — otherwise the field is absent, not null. Permission: `customapis_update_timesheets (auto-derived)`
Full detail ```json { "id": "post_timesheets_id_approve", "method": "POST", "httpMethod": "POST", "path": "/timesheets/:id/approve", "operation": "update", "model": "timesheets", "purpose": "Approve a submitted timesheet. Needs a written reason in `auditMessage`, which lands in the audit trail. Approving twice is a 409; approving outside your write scope — the Scratch Sandbox here — is a 404. `costRate` appears only if you can read it — otherwise the field is absent, not null.", "permission": "customapis_update_timesheets (auto-derived)", "specialRules": [ "Owner-scoped: non-exempt callers see only rows they own; another user's row is 404 (never 403), indistinguishable from not-found (§9.3). Owner-exempt permission: customapis_admin_timesheets — sees all owners within tenant scope.", "ownerId is immutable — a row cannot be re-owned via PATCH (owner-scoped rows spec §3).", "Tenant-immutable on PATCH — silently discarded, never a 400: employeeId (§9.4).", "PATCH is JSON Merge Patch (RFC 7386): omitted untouched, null clears, JSONB merges (§4.7)." ], "fieldSemantics": { "createdById": "reserved managed column — server-authoritative, read-only on read (§16).", "createdByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "createdAt": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedById": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedAt": "reserved managed column — server-authoritative, read-only on read (§16).", "ownerId": "reserved owner column — server-set to the creating user, immutable, read-only on read (owner-scoped rows spec).", "ownerDisplayName": "reserved owner column — the owner's display name captured at creation; a later rename does not rewrite it (owner-scoped rows spec §2.1)." }, "requestBody": { "contentType": "application/json", "fields": [ { "field": "auditMessage", "type": "string", "required": true, "description": "Required justification, recorded as the audit entry’s user message (§10.4)." } ], "required": [ "auditMessage" ], "rejectsUnknownFields": true }, "consultedPermissions": [ { "permission": "customapis_read_cost_rate", "description": "See labour cost rates on timesheets" } ] } ```
### `GET /timesheets/:id` One timesheet by id. `costRate` appears only if you can read it — otherwise the field is absent, not null. Permission: `customapis_get_timesheets (auto-derived)`
Full detail ```json { "id": "get_timesheets_id", "method": "GET", "httpMethod": "GET", "path": "/timesheets/:id", "operation": "get", "model": "timesheets", "purpose": "One timesheet by id. `costRate` appears only if you can read it — otherwise the field is absent, not null.", "permission": "customapis_get_timesheets (auto-derived)", "specialRules": [ "Owner-scoped: non-exempt callers see only rows they own; another user's row is 404 (never 403), indistinguishable from not-found (§9.3). Owner-exempt permission: customapis_admin_timesheets — sees all owners within tenant scope." ], "fieldSemantics": { "createdById": "reserved managed column — server-authoritative, read-only on read (§16).", "createdByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "createdAt": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedById": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedAt": "reserved managed column — server-authoritative, read-only on read (§16).", "ownerId": "reserved owner column — server-set to the creating user, immutable, read-only on read (owner-scoped rows spec).", "ownerDisplayName": "reserved owner column — the owner's display name captured at creation; a later rename does not rewrite it (owner-scoped rows spec §2.1)." }, "consultedPermissions": [ { "permission": "customapis_read_cost_rate", "description": "See labour cost rates on timesheets" } ] } ```
### `POST /timesheets` Submit a timesheet. The `employeeId` must be in your write scope — the Scratch Sandbox here — or you get a 404; `GET /me` names one your key can use. `costRate` appears only if you can read it — otherwise the field is absent, not null. Permission: `customapis_create_timesheets (auto-derived)`
Full detail ```json { "id": "post_timesheets", "method": "POST", "httpMethod": "POST", "path": "/timesheets", "operation": "create", "model": "timesheets", "purpose": "Submit a timesheet. The `employeeId` must be in your write scope — the Scratch Sandbox here — or you get a 404; `GET /me` names one your key can use. `costRate` appears only if you can read it — otherwise the field is absent, not null.", "permission": "customapis_create_timesheets (auto-derived)", "specialRules": [ "Owner-scoped: non-exempt callers see only rows they own; another user's row is 404 (never 403), indistinguishable from not-found (§9.3). Owner-exempt permission: customapis_admin_timesheets — sees all owners within tenant scope.", "ownerId (and ownerDisplayName) are server-stamped from the caller on create; client-supplied owner values are rejected (stripped from the writable schema).", "An out-of-scope or nonexistent parent FK returns 404 (not 403/422), even though the collection URL exists (§9.3)." ], "fieldSemantics": { "createdById": "reserved managed column — server-authoritative, read-only on read (§16).", "createdByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "createdAt": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedById": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedAt": "reserved managed column — server-authoritative, read-only on read (§16).", "ownerId": "reserved owner column — server-set to the creating user, immutable, read-only on read (owner-scoped rows spec).", "ownerDisplayName": "reserved owner column — the owner's display name captured at creation; a later rename does not rewrite it (owner-scoped rows spec §2.1)." }, "requestBody": { "contentType": "application/json", "fields": [ { "field": "employeeId", "type": "integer", "required": true }, { "field": "endAt", "type": "string (date-time) | string (date)", "required": true, "description": "Accepts a date-time with an offset `2026-08-12T07:00:00Z` or `2026-08-12T07:00:00+10:00`, or a bare date `2026-08-12` (midnight UTC). A date-time with NO offset (`2026-08-12T07:00` — the value an `` produces) is a 400: UTC, server-local and caller-local are three different instants. Convert with `new Date(v).toISOString()`. Rendered as ISO 8601 UTC with milliseconds." }, { "field": "hours", "type": "number", "required": true }, { "field": "startAt", "type": "string (date-time) | string (date)", "required": true, "description": "Accepts a date-time with an offset `2026-08-12T07:00:00Z` or `2026-08-12T07:00:00+10:00`, or a bare date `2026-08-12` (midnight UTC). A date-time with NO offset (`2026-08-12T07:00` — the value an `` produces) is a 400: UTC, server-local and caller-local are three different instants. Convert with `new Date(v).toISOString()`. Rendered as ISO 8601 UTC with milliseconds." }, { "field": "workDate", "type": "string (date) | string (date-time)", "required": true, "description": "Accepts a calendar date `2026-08-12`, or a date-time with an offset which is truncated to its UTC date. Rendered as `YYYY-MM-DD`." }, { "field": "note", "type": "string|null", "required": false }, { "field": "status", "type": "string (one of: draft, submitted)", "required": false, "description": "Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted. Optional — the database defaults this to \"draft\" when omitted." } ], "required": [ "employeeId", "endAt", "hours", "startAt", "workDate" ], "rejectsUnknownFields": true }, "consultedPermissions": [ { "permission": "customapis_read_cost_rate", "description": "See labour cost rates on timesheets" } ] } ```
### `SEARCH /timesheets` List the timesheets in your read scope. `costRate` appears only if you can read it — otherwise the field is absent, not null. Permission: `customapis_search_timesheets (auto-derived)` | Filter | Operators | |---|---| | `employeeId` | eq, ne, in, notIn, isnull, notnull, gt, gte, lt, lte | | `workDate` | eq, ne, in, notIn, isnull, notnull, gt, gte, lt, lte | | `status` | eq, ne, in, notIn, isnull, notnull, like |
Full detail ```json { "id": "search_timesheets", "method": "SEARCH", "httpMethod": "GET", "path": "/timesheets", "operation": "search", "model": "timesheets", "purpose": "List the timesheets in your read scope. `costRate` appears only if you can read it — otherwise the field is absent, not null.", "permission": "customapis_search_timesheets (auto-derived)", "specialRules": [ "Owner-scoped: non-exempt callers see only rows they own; another user's row is 404 (never 403), indistinguishable from not-found (§9.3). Owner-exempt permission: customapis_admin_timesheets — sees all owners within tenant scope.", "Filter allow-list (§8.4): only the filterable fields above are accepted; id, startAt, endAt, hours, costRate, note, ownerId, ownerDisplayName, createdById, createdByDisplayName, createdAt, lastUpdatedById, lastUpdatedByDisplayName, lastUpdatedAt are real columns but are rejected with a 400 on this route. To allow one, add it to the route's `filters.allow` — see the per-field cost of doing so.", "Sort restricted (§8.4): `?sort=` accepts only id, employeeId, workDate, startAt, endAt, hours, status, note, ownerId, ownerDisplayName, createdById, createdByDisplayName, createdAt, lastUpdatedById, lastUpdatedByDisplayName, lastUpdatedAt; anything else is a 400. Sorting is governed separately from filtering, so a field being filterable above does not make it sortable.", "Accepts the optional tenant selector `?organisationId=` to narrow the result to one tenant within the caller's scope; a tenant outside scope returns an empty page, never a 403/404 (search-tenant-narrowing spec §2)." ], "fieldSemantics": { "createdById": "reserved managed column — server-authoritative, read-only on read (§16).", "createdByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "createdAt": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedById": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedByDisplayName": "reserved managed column — server-authoritative, read-only on read (§16).", "lastUpdatedAt": "reserved managed column — server-authoritative, read-only on read (§16).", "ownerId": "reserved owner column — server-set to the creating user, immutable, read-only on read (owner-scoped rows spec).", "ownerDisplayName": "reserved owner column — the owner's display name captured at creation; a later rename does not rewrite it (owner-scoped rows spec §2.1)." }, "consultedPermissions": [ { "permission": "customapis_read_cost_rate", "description": "See labour cost rates on timesheets" } ], "filterableFields": [ { "field": "employeeId", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "gt", "gte", "lt", "lte" ] }, { "field": "workDate", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "gt", "gte", "lt", "lte" ] }, { "field": "status", "operators": [ "eq", "ne", "in", "notIn", "isnull", "notnull", "like" ] } ], "restrictedFilters": [ { "field": "id", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "startAt", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "endAt", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "hours", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "costRate", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "note", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "ownerId", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "ownerDisplayName", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "createdById", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "createdByDisplayName", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "createdAt", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "lastUpdatedById", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." }, { "field": "lastUpdatedByDisplayName", "cost": "text predicate — `like` compiles to ILIKE '%…%', which no b-tree can serve; needs a trigram (pg_trgm) index to stay sub-linear." }, { "field": "lastUpdatedAt", "cost": "indexable predicate — cheap if an index covers the resulting access pattern, a scan otherwise." } ], "sortableFields": [ "id", "employeeId", "workDate", "startAt", "endAt", "hours", "status", "note", "ownerId", "ownerDisplayName", "createdById", "createdByDisplayName", "createdAt", "lastUpdatedById", "lastUpdatedByDisplayName", "lastUpdatedAt" ], "defaultSort": "id" } ```