---
title: "System Lookups and Cache Refresh"
slug: system-lookups
section: Governance & Operations
status: stable
description: "`system_lookups` is the Hub's shared table of reference values: statuses, categories, country records, partner roles, skill categories and similar enumerations are stored as rows keyed by `(group, key)` instead of being hard-coded enums…"
last-updated: 2026-10-07
---

# System Lookups and Cache Refresh

## Overview

`system_lookups` is the Hub's shared table of reference values: statuses, categories, country records, partner roles, skill categories and similar enumerations are stored as rows keyed by `(group, key)` instead of being hard-coded enums (see also [Finance and NetSuite](/docs/finance-netsuite), which states the same for its status values). Validation code across the Hub asks an in-process cache whether a key is valid for a group. This page covers the table, the admin routes under `/api/admin/system-lookups`, and the cache that `POST /api/admin/system-lookups/refresh-cache` reloads.

Owning code, all in the BackOfficePrototype repo (the Hub): `server/routes/lookups.ts` (routes), `server/services/lookupCache.ts` (cache), `shared/models/portal.ts` (table), `server/seed/systemLookups.ts` (seed data). Callers include the back-office admin pages that use the `/api/admin/system-lookups` routes (Skill Categories, Countries, Partner Models) and API-key integrations holding the `admin.write` scope. Skill categories referenced by [Workforce Capacity](/docs/workforce-capacity) are rows in this table (group `skill_category`).

## Data model

Table `system_lookups` (`shared/models/portal.ts`):

| Column | Type | Notes |
| --- | --- | --- |
| `id` | varchar, primary key | Default `gen_random_uuid()`. The admin PATCH and DELETE routes take this id. |
| `group` | text, not null | Lookup family, for example `skill_category`, `reference_country`, `partner_autonomy_level`. |
| `key` | text, not null | Unique within the group (unique index `idx_lookup_group_key` on `group, key`). |
| `label` | text, not null | Display label. |
| `description`, `color`, `icon`, `badge_variant` | text, nullable | Display hints. |
| `sort_order` | integer, not null, default 0 | Cache readers sort on it. |
| `metadata` | jsonb, nullable | Group-specific shape, see edge cases. |
| `is_active` | boolean, not null, default true | Soft-delete flag. |
| `created_at` | timestamp, default now | |

Index `idx_lookup_group` is on `group`.

## Routes

### Authentication and permissions

Every admin route below uses three layers in this order: `isAuthenticatedOrApiKey("admin.write")` (or `"admin.read"` for the one GET), then `requirePermission("system_settings.manage")`, then `auditMutation` for writes.

- Session callers: a signed-in staff session passes the first layer with no scope check. The second layer passes when the user holds `system_settings.manage`, or holds `system.admin` (the god-mode grant checked by `isGodMode`).
- API-key callers: the key goes in the `X-API-Key` header (the guard reads only that header). The key must hold the `admin.write` scope. In `server/rbac.ts` the scope `admin.write` maps to a list of permissions that includes `system_settings.manage`, so a key with `admin.write` also passes `requirePermission`. A key that holds the `data_lake.read` scope, and otherwise only that scope, `sensitive_data.view`, `applicant.pii.read` or `partner:`/`program:`/`season:`/`brand:` scopes, gets 403 (`details.reason` is `read_only_scope`) on any method other than GET, HEAD or OPTIONS. The wildcard scope `*` passes both the scope check and the permission check.
- The guard also refuses, with 403 `FORBIDDEN`, revoked keys, expired keys, and on `/api/admin/*` keys holding `portal.host_proxy` or `portal.partner_proxy` (`details.reason` is `proxy_scope`) or only host-side scopes (`host_only_scopes`). If the host-bound organisation lookup itself fails, the guard returns 503 `SERVICE_UNAVAILABLE` (`details.reason` is `host_lookup_failed`).
- Host-bound API keys are refused on admin routes with 403 `FORBIDDEN` (`details.reason` is `host_bound_org`).

The permission `system_settings.manage` is defined in `server/seed/rbac.ts` with category `admin`. See [RBAC](/docs/rbac).

### POST /api/admin/system-lookups/refresh-cache

| Item | Value |
| --- | --- |
| Verb and path | `POST /api/admin/system-lookups/refresh-cache` |
| Auth | `isAuthenticatedOrApiKey("admin.write")` then `requirePermission("system_settings.manage")` |
| Audit | `auditMutation` with action `system.cache_refresh`, entity type `system_lookups` |
| Request body | None read by the handler. |
| Success | 200, `{ "success": true, "data": { "message": "Lookup cache refreshed successfully" } }` |
| Failure | 401, 403 from the guards (see error codes). A database error inside the reload is not caught by the handler and goes to the global error handler. |

The handler calls `refreshLookupCache()`, which is an alias for `initLookupCache()`: it selects every row with `is_active = true`, clears the map, and rebuilds it.

### Other admin routes

| Route | Scope | Body | Success | Notes |
| --- | --- | --- | --- | --- |
| `POST /api/admin/system-lookups` | `admin.write` | `group` (literal `skill_category`), `key` (lowercase letters, digits, underscore), `label`, optional `description`, `sortOrder`, `isActive`, `metadata` | 201 with the created row | Only the `skill_category` group can be created. Default `metadata` is `{ skills: [] }`. Refreshes the cache. |
| `PATCH /api/admin/system-lookups/:id` | `admin.write` | Any of `label`, `description`, `metadata`, `sortOrder`, `isActive` | 200 with the updated row | Refreshes the cache. Works on any group. |
| `DELETE /api/admin/system-lookups/:id` | `admin.write` | None | 200 with `{ id, isActive }` | Soft delete (`is_active = false`), `skill_category` rows only. Refreshes the cache. |
| `POST /api/admin/system-lookups/country/sync-all` | `admin.write` | None | 200 with `{ group, enqueued }` | Emits a country-catalog "created" event per active `reference_country` row, awaiting each; an emit failure is not caught and goes to the global error handler. Does not refresh the cache. |
| `POST /api/admin/system-lookups/country/set-all-eligible` | `admin.write` | `isSwtEligible` boolean | 200 with `{ group, isSwtEligible, updated }` | Sets `metadata.isSwtEligible` on every active `reference_country` row, emits an "updated" event per row (emit failures are logged), then refreshes the cache. |
| `GET /api/admin/system-lookups/country/date-windows` | `admin.read` (the permission gate is still `system_settings.manage`, which `admin.read` does not map to, so a key needs `admin.write` or `*`) | Query `programKey` (default `summer_work_travel`), `seasonLabel` (default `summer_2027`) | 200 with program and `program_date_windows` rows | Returns `program: null` and an empty `windows` list when the program key is unknown. |

### Read routes

| Route | Auth | Behaviour |
| --- | --- | --- |
| `GET /api/system-lookups` | session only (`isAuthenticated`), `dashboard.view` | Active rows, ordered by group then sort order, paged wrapper. Reads the database directly. A request carrying `X-API-Key` and no session gets 401 `SESSION_REQUIRED`. |
| `GET /api/system-lookups/:group` | session, `dashboard.view` | Rows of one group ordered by sort order. Query `includeInactive=true` also returns inactive rows. Reads the database directly. |

## Error codes

Error bodies use the shape `{ error: { code, message, details, traceId } }`. Handler-built errors below set `details` to `null` unless stated; the 404 bodies from PATCH and DELETE contain only `code` and `message`. See [Error Handling](/docs/error-handling).

| Status | `error.code` | Where | Cause |
| --- | --- | --- | --- |
| 401 | `UNAUTHORIZED` (top-level) | all admin routes | No session and no `X-API-Key`, or the key lookup failed. |
| 401 | `SESSION_REQUIRED` | the two read routes | `X-API-Key` header present and no valid session. |
| 403 | `MFA_SETUP_REQUIRED` | the two read routes | Session has MFA enrolment pending. The dual guard on the admin routes does not check this. |
| 403 | `FORBIDDEN` | all admin routes | Key lacks the scope (`details.required`, `details.granted`), revoked or expired key, read-only key on a write (`details.reason` = `read_only_scope`), proxy, host-only or host-bound key (see Authentication and permissions), or permission missing (message `Permission denied` for sessions; for keys `details.required` = `system_settings.manage`). |
| 400 | `VALIDATION_ERROR` (top-level) | POST create, PATCH, set-all-eligible, date-windows | Zod parse failure (body, or query for date-windows), mapped by the global handler with `details.issues`. |
| 422 | `VALIDATION_ERROR` (top-level) | PATCH, POST create | Empty PATCH body ("No updatable fields provided"), invalid `skill_category` `metadata.skills` shape or duplicate skill key (PATCH and POST create), invalid `reference_country` `metadata.region` or non-boolean `metadata.isSwtEligible` (PATCH only). |
| 404 | `NOT_FOUND` (top-level) | PATCH, DELETE | No row with that id. |
| 403 | `FORBIDDEN` (top-level) | DELETE | Row is not in group `skill_category`; message `DELETE not permitted on lookups in group "<group>"`. |
| 409 | `DUPLICATE_KEY` (top-level) | POST create | Postgres error 23505 on `(group, key)`. |

## Cache behaviour

`server/services/lookupCache.ts` holds a module-level `Map<group, Map<key, row>>` in the memory of each server process. It contains only rows with `is_active = true`; there is no TTL and no timer. Exposed readers: `isValidLookup`, `getLookupKeys`, `getLookup`, `getAllLookups` (sorted by `sortOrder`), `getLookupMetadata` (default key `default`), `lookupValidator` and `zodLookup` (a Zod refine against the cache). An unknown group yields `false`, an empty list, or `undefined`.

A refresh first selects the active rows from the database, then clears the map and inserts the rows in one synchronous block with no `await` between the clear and the inserts, so a reader running on the same event loop does not see an empty or partial map. While the select is in flight, readers keep seeing the previous contents.

### When the cache is refreshed

| Trigger | Location |
| --- | --- |
| Server startup | `server/index.ts` calls `initLookupCache()`. If it throws and `SKIP_SEEDS` is not `true`, startup fails; if `SKIP_SEEDS=true` the error is logged and startup continues with an empty cache. |
| `POST /api/admin/system-lookups/refresh-cache` | Manual reload. |
| Admin create, PATCH, DELETE, and `set-all-eligible` | `server/routes/lookups.ts`, after the database write. |
| Creating an agreement-library type | `server/services/agreementLibrary.ts` inserts a lookup row and refreshes. |

Writes made any other way (seed scripts, migrations, direct SQL, another instance) do not update a running process. Because the cache is per process, a refresh call reaches only the instance that serves it. The code read has no mechanism that propagates a refresh to other instances.

## Configuration

| Variable | Default | Effect |
| --- | --- | --- |
| `SKIP_SEEDS` | unset | When exactly `true`, a failed `initLookupCache()` at startup is logged instead of aborting startup. |

No other environment variable was found for the cache.

## Edge cases

- `PATCH` on a `skill_category` row validates `metadata.skills` as an array of `{ key, label, isActive }` with unique keys. A `PATCH` with `metadata` replaces it.
- `PATCH` on a `reference_country` row merges the incoming `metadata` over the existing object, so a region-only edit keeps `iso3` and `fips`. `region` must be one of the canonical country regions in `shared/countryRegions`.
- `PATCH` on a `partner_autonomy_level` row also calls `invalidateCapabilityCache()`. `PATCH` on `reference_country` emits a country-catalog "updated" event without waiting for it; emit failures are logged.
- The `PATCH` write succeeds before the cache refresh runs; if the refresh throws, the caller gets an error although the row changed.
- `DELETE` is soft and `skill_category` only, so values already stored on applicants or job orders keep resolving.
- Admin `GET` for inactive rows is the `includeInactive=true` query on the group read route; the cache never holds them.

## Known gaps

- The documented sample response for `refresh-cache` in `server/routes/endpointSchemas/admin-1.ts` says `System lookup cache refreshed`; the handler returns `Lookup cache refreshed successfully`.
- Multi-instance behaviour (whether a refresh reaches every Cloud Run or pod instance) was not confirmed; no fan-out code was found.
- Which staff roles are granted `system_settings.manage` was not traced.
- The global handler's exact 500 body for a failed reload was not read.
- The full list of groups in the table and every call site of the cache readers were not enumerated.