---
title: "Portal Status Mappings, Compliance Inputs and Field Edits"
slug: portal-reference-tables
section: Program Configuration
status: stable
description: "Three Hub tables in BackOfficePrototype support the Workflow Builder and the Applicant Portal contract."
last-updated: 2026-10-07
---

# Portal Status Mappings, Compliance Inputs and Field Edits

## Overview

Three Hub tables in BackOfficePrototype support the Workflow Builder and the Applicant Portal contract. Two are small reference tables that are seeded and read by the back office. The third is an audit log of staff and system edits to applicant data.

| Table | Kind | Defined in |
|---|---|---|
| `portal_status_mappings` | Reference data, maps a portal status string to a display stage | `shared/models/portal.ts` |
| `compliance_input_definitions` | Reference data, catalogue of rule input keys | `shared/models/portal.ts` |
| `applicant_field_edits` | Append-only audit log of field changes | `shared/models/portal.ts` |

Routes for the two reference tables live in `server/routes/lookups.ts`. Routes for field edits live in `server/routes/applicantFieldEditRoutes.ts`, registered through `server/routes/applications/index.ts`. The seeder is `server/seed/systemLookups.ts`.

The compliance input keys are what a Step Library entry binds form fields to (see [Workflow Studio](/docs/workflow-builder)). Field edits are written back to the portal step snapshot, which is how a staff correction reaches the applicant side (see [Endpoint Reference](/docs/applicant-portal-endpoints)).

## Data Model

### portal_status_mappings

| Column | Type | Notes |
|---|---|---|
| `id` | varchar, PK | `gen_random_uuid()` default |
| `portal_status` | text, not null, unique | The status string, for example `h_submitted` |
| `stage_key` | text, not null | Display stage, for example `first_document_batch` |
| `label` | text, not null | Display label |
| `badge_variant` | text, not null | Default `outline` |
| `color` | text, not null | Default `text-muted-foreground` (a CSS class name) |
| `created_at` | timestamp | Default now |

Index: `idx_portal_status` on `portal_status`. The seeder writes 7 rows: `i_initial`, `ii_initial_incomplete`, `initial_incomplete` and `draft` map to stage `initial_applicant`; `h_submitted` to `first_document_batch`; `fw_form_issued_waiting` to `sevis`; `p_participating` to `on_program`.

### compliance_input_definitions

| Column | Type | Notes |
|---|---|---|
| `id` | varchar, PK | `gen_random_uuid()` default |
| `input_key` | text, not null, unique | Rule input name, for example `dateOfBirth` |
| `label` | text, not null | |
| `description` | text | |
| `data_type` | text, not null | Default `string`; seeded values are `date`, `string`, `number`, `boolean` |
| `transform_options` | jsonb | Nullable; the seeder sets none |
| `sort_order` | integer, not null | Default 0 |
| `is_active` | boolean, not null | Default true |
| `created_at` | timestamp | |

Index: `idx_compliance_input_key` on `input_key`. The seeder writes 12 keys: `dateOfBirth`, `nationality`, `priorJ1Participations`, `backgroundCheckCompleted`, `referencesCompletedCount`, `orientationCompleted`, `passportExpiryDate`, `documentsCompletedCount`, `documentsTotalCount`, `flightDetailsCompleted`, `housingInfoCompleted`, `insuranceCompleted`.

### applicant_field_edits

| Column | Type | Notes |
|---|---|---|
| `id` | varchar, PK | |
| `applicant_id` | varchar, not null | FK to `applicants` |
| `application_id` | varchar | Nullable FK to `applications` |
| `step_key`, `field_key`, `field_label` | text | `field_key` is not null; `step_key` is nullable |
| `actor_type`, `actor_id`, `actor_name` | text, varchar, text | `actor_type` not null; `actor_id` has no FK (polymorphic); the name is a snapshot kept for audit |
| `source` | text, not null | Seen in code: `backoffice`, `api`, `backoffice_patch`, `biographical_sync` |
| `set_by_role_id` | varchar | Nullable FK to `roles`; provenance for the cross-role overwrite check |
| `previous_value`, `new_value` | jsonb | |
| `reason` | text | |
| `created_at` | timestamp | |

Indexes: `idx_field_edit_applicant`, `idx_field_edit_application`, `idx_field_edit_step_field` (step_key, field_key), `idx_field_edit_created`.

## Routes

### Reference Table Reads

Both are GET only. No route in the Hub writes to either table (see Known Gaps).

| Verb and path | Guards | Response |
|---|---|---|
| `GET /api/portal-status-mappings` | `isAuthenticated`, `requirePermission("dashboard.view")` | All rows, wrapped with `wrapAsPage` in the success envelope |
| `GET /api/compliance-input-definitions` | `isAuthenticated`, `requirePermission("dashboard.view")` | Rows with `is_active` true, ordered by `sort_order`, same wrapper |

### Field Edit Routes

| Verb and path | Guards | Purpose |
|---|---|---|
| `POST /api/applicants/:applicantId/field-edits` | `isAuthenticatedOrApiKey("applications.write")`, `requirePermission("applicant.journey.update")`, field permission enforcer | Record one edit |
| `POST /api/applicants/:applicantId/field-edits/batch` | Same guards except the enforcer runs inside the handler for every field | Record many edits for one step in one request |
| `GET /api/applicants/:applicantId/field-edits` | `isAuthenticatedOrApiKey("applications.read")`, any of `applications.view`, `applicant.profile.view`, `applicants.list.view` | List edits, newest first |
| `GET /api/applicants/:applicantId/field-edits/history/:fieldKey` | Same as the list route | Edits for one field |

Single-edit body: `stepKey` and `fieldKey` required; `applicationId`, `fieldLabel`, `previousValue`, `newValue`, `reason` optional. Success is 201 with the created row. Batch body: `stepKey`, optional `applicationId`, and `edits` (at least one item, each with `fieldKey` and optional label, values, reason); success is 201 with the array of created rows. List query filters: `applicationId`, `stepKey`, `fieldKey`; history takes an optional `stepKey` query. Both reads return 200.

## Errors

| Status | Top-level `error.code` | `details.code` | Raised when |
|---|---|---|---|
| 400 | `VALIDATION_ERROR` | none | Missing ids or keys, duplicate `fieldKey` in a batch, application not owned by the applicant, step or field is legally locked, unusable passport number or expiry |
| 403 | `FIELD_ACCESS_DENIED` | none (`details.fieldPermission` carries forensics) | Role has `hidden` or `no_access` on the field |
| 403 | `FIELD_READ_ONLY` | none | Role has `view` only |
| 403 | `FIELD_OVERWRITE_DENIED` | none | Value was last set by a different role and the caller is not a sponsor with `override` |
| 500 | `FIELD_PERMISSION_ERROR` | none | The field permission check itself threw; the route fails closed |
| 500 | `FIELD_EDIT_SYNC_FAILED` | none (`details.stage`, `stepKey`, `fieldKeys`) | Snapshot persist or applicant-row promotion failed after the edit row was written |

The batch route evaluates the field permission for every field before any write, so a single denied or locked field rejects the whole batch.

## Who Writes and Reads

| Table | Writers | Readers |
|---|---|---|
| `portal_status_mappings` | `seedSystemLookups` upserts on `portal_status` | `GET /api/portal-status-mappings`; client hook `usePortalStatusMappings` |
| `compliance_input_definitions` | `seedSystemLookups` upserts on `input_key` | `GET /api/compliance-input-definitions`; Step Library page via `useComplianceInputDefinitions` |
| `applicant_field_edits` | The two POST routes; `PATCH` applicant route in `server/routes/applications/applicants.ts` (identity fields `firstName`, `lastName`, `dateOfBirth`, source `backoffice_patch`); `server/services/biographicalSync.ts` (date of birth promotion, actor `system`, source `biographical_sync`) | The two GET routes; the applicant profile progress section in the client |

The Gateway Globus backend exposes `submit_applicant_field_edits` and `list_applicant_field_edits` tools that call the field-edit URLs (`alliance-mcp-gateway backends/globus-mcp/src/tools.ts`).

## Behaviour and Edge Cases

- Seeding is an upsert: existing rows are overwritten from the seed for `label`, `stage_key`, `badge_variant`, `color` (mappings) and for label, description, type, options, order, active flag (inputs). A manual database change to those columns is reset on the next seed run.
- Compliance input keys are consumed through the Step Library. Each entry has a `complianceInputMapping` array of `fieldName`, `inputKey`, optional `transform`. The Step Library form rejects a row with an empty field or key, and rejects a field name absent from the step schema unless it is a virtual mapping. `server/services/coverageAnalysis.ts` indexes these mappings by `inputKey` to decide whether each configured rule has a step that supplies its inputs, and suggests steps that would fill a gap.
- A field edit with an `applicationId` also writes an audit log row (`field_edit`), emits a step-updated event, and merges the value into the portal step snapshot, keeping `is_completed` unchanged and promoting biographical fields to the applicant row. That write is awaited. After the snapshot write, a compliance re-evaluation keyed `staff.field.edit` is queued only when the snapshot step is already complete and the edit touched an evaluable fact field whose value reached the place the rules read it (for promoted biographical fields, the `applicants` column must now carry the edit). Queue failures are logged and swallowed.
- Independent of `applicationId`, an edit to one of the biographical fields that trigger it flags DS-2019 resubmission for the applicant's issued applications.
- Without `applicationId` the edit row and audit log are written, and the DS-2019 resubmission flag still applies; no step-updated event, snapshot write or re-evaluation occurs.
- Empty passport number or expiry is accepted (clearing a field); a non-empty unreadable value is rejected.
- The table is an audit log, not live state; the client test `progress-section.stale-field-edit.test.ts` records that overlaying the latest edit row onto step data was a bug.

## Known Gaps

- No admin write route or UI for `portal_status_mappings` or `compliance_input_definitions` was found; maintenance is by changing the seed.
- `usePortalStatusMappings` is defined but no component calling it was found. `server/services/checkInSync.ts` uses a hard-coded status map instead of this table.
- The endpoint schema sample for `GET /api/portal-status-mappings` shows `internalStatus` and `portalStatus` fields, which are not columns of the table.
- The API reference lists the field-edit GET routes with permission `applicants.view`, but the handlers check the permissions in the routes table above.
- The batch route is absent from the mutation registry (only the single POST was found there).
- The Gateway `submit_applicant_field_edits` tool posts an `edits` object as the body, which does not match the single-edit body the route validates.