Custom Fields

Configurable field definitions for the forms a project fills: prestarts and diaries, activities, dockets (subcontractor dockets included), materials dockets, issues, worker sign-up, Drug & Alcohol tests and declarations, and induction roles (managed in the induction role editors, below). Org admins define templates at the org level; when a project is created, org fields are auto-copied to the project. Project admins can then freely add, edit, delete, and reorder their project-level fields independently.

Eight authorable entity types — prestart_diary, activity, docket, materials_docket, worker_signup, issue, drug_alcohol_entry (Drug & Alcohol) and drug_alcohol_declaration (D&A Declaration). Prestarts and diaries share the same field definitions: when a prestart is published it creates a diary, and the prestart’s fields link over to that diary. When a diary is created fresh (not from a prestart), it pulls its custom fields from the project.

Architecture

Copy-on-inherit model:

• Org admins create field definitions (org-level templates)
• Templates auto-copied to all active projects on creation
• New projects receive all current org templates during project creation
• Project copies are fully independent after copying — edits do not propagate back

Single table with scope: custom_field_definitions uses a nullable project_id column to distinguish org templates (NULL) from project fields (set).

Data Model

Custom Field Definitions

Enums

• custom_field_entity: prestart_diary, activity, docket, subcontractor_docket, materials_docket, issue, worker_signup, induction_role, org_default_induction_role, drug_alcohol_entry (Drug & Alcohol), drug_alcohol_declaration (D&A Declaration)
• custom_field_type: text, textarea, number, checkbox, dropdown, radio, multiselect, date, time, file, user, year, day_of_week, address, csv_lookup, table

Indexes

Copy-on-Inherit Flow

Creating an org template

1. Admin creates a field via POST /v1/orgs/{orgId}/custom-fields
2. Go service inserts the org template (project_id = NULL)
3. Service queries ListOrgProjectIDs to get all active projects
4. For each project, calls CopyOrgFieldToProject (INSERT…SELECT)
5. Errors on individual project copies are logged but do not fail the request

Creating a new project

After project creation and admin auto-add (in project/service.go), CopyAllOrgFieldsToProject bulk-copies all current org templates to the new project in a single INSERT…SELECT query.

Independence after copy

Project-level fields are fully independent. Editing or deleting an org template does not affect existing project copies. Editing a project field does not affect the org template.

One exception: a template’s reporting column is copied to its project copies, and changing it on the template changes it on the copies, except in a project where another field already uses that column. There the copy has none. When that other field is deleted or gives up the column, the copy takes it.

Reporting column

A worker sign-up field can fill one standard report column: Apprentice, Trainee, Gender, Date of birth, Postcode, Year of commencement, Aboriginal or Torres Strait Islander, Language group or Beakon ID. Pick it in Reporting column when you add or edit the field.

• Each column can be used by one field per project, and by one organisation template.
• A column only appears for field types that suit it. Date of birth, for example, needs a Date or Text field.
• Fields that already had one of these labels were matched automatically. Where a project had two fields with the same label, the one with more answers was matched.
• Fields from an organisation template show their column in the project’s list; change it on the template.

API Endpoints

Org-Level Templates

Project-Level Fields

CSV Lookup Field Type (csv_lookup)

A csv_lookup field lets an admin upload a multi-column CSV/XLSX dataset and nominate N ordered “filter” columns (cascading) plus exactly 1 “target” column. At response time the user is shown a sequence of cascading dropdowns — picking Region=NSW filters the Site options, picking Site filters Trade, and once all filters are chosen the matching target value (e.g. a Cost Code) is presented and stored as a plain string in the entity’s custom_field_values. Other columns of the matched row can fill fields on the same form (see Filling the form’s fields).

• Dataset storage: the CSV is stored as rows in a dedicated custom_field_lookup_rows table (one row per line, cells JSONB), not in the definition. The column config (which columns are filters/target, their order, source filename, row count, the field map and the warnings) lives in custom_field_definitions.metadata.lookup.
• Scope (v1): datasets are project-scoped — each project uploads its own CSV via the Dataset action on the field row. A new project inherits the field shape + column config (the copy queries carry metadata) but starts with no dataset until uploaded.
• Organisation lookups: a lookup copied from an organisation template takes a dataset upload in each project, but its field map is read-only there (like every other setting of an organisation field).
• Cascade endpoint: a server-side distinct-values endpoint drives the dropdowns; the client never downloads the whole table. Input is validated so column and every filters key must be a configured column and a prefix-subset of the cascade (IDOR/probe defence).
• Entity types: allowed on prestart_diary, activity, docket, materials_docket, issue. Not on worker_signup / induction_role (the cascade endpoint is member-auth’d; public sign-on cascade is a later slice).
• Stored value: the chosen target string. ValidateValues enforces a non-empty string shape; the cascade is the integrity boundary. A stored answer shows as Current: {value} with Change and Clear; filter picks never clear it, and no cascade read is sent until Change is pressed.

Filling the form’s fields

Each dataset column can fill one field on the form the lookup is on. The admin chooses it per column in the Fills select of the Dataset dialog (“Form fields” first, then “Custom fields”); it is saved as metadata.lookup.field_map:

"field_map": [
  { "column": "description", "target": { "kind": "builtin", "key": "description" } },
  { "column": "unit", "target": { "kind": "builtin", "key": "target_qty_unit" } },
  { "column": "crew_notes", "target": { "kind": "custom_field", "field_id": "<uuid>" } }
]

• column is the normalised column key (Cost Code → cost_code).
• A builtin target is one of the form’s own header fields (below). A custom_field target is a live custom field of the same entity type, other than files, tables and lookups.
• One lookup per field per form. A second mapping of the same field is refused with {field} is already filled by {lookup}. docket and materials_docket lookups count together at setup, although a materials docket shows only its materials_docket fields. A field no form can edit is refused with {field} can't be filled by a lookup.
• The mapping is checked and saved in one transaction under the lookup’s lock.

Form fields a lookup can fill (the Fills select offers exactly these):

Never targets: lines (workers, plant, materials, subcontractors), the pricing grid, docket money, docket and materials contract, plant attribution, statuses, approvals and assignees. The key vocabulary is kept identical in three places, each guarded against the others: openapi CustomFieldLookupBuiltinKey, packages/lookup-fill/src/lookup-targets.ts and Go backend/api/internal/customfield/lookup_targets.go.

What a pick fills

Once a person picks the final answer (or a filter pick leaves one answer, which is then chosen for them), the form reads the matched rows once — only when the lookup has a field map, never for a filter pick. Then:

• Empty fields fill straight away. Empty means no value, an empty text or list, or an unticked checkbox. A value the form put there itself, that the person hasn’t touched, also counts as empty. 0 is a value.
• Fields that already hold a different value are listed in Replace existing values? — Replace fills them, Keep mine keeps them.
• If several rows match the answer and differ on a field this form shows, Pick a row asks which one to use. Rows that give the same values are one row.
• Values that don’t match (an unknown unit, stage, location or person) are left alone and named under Couldn’t fill: … — inline under the lookup on the web, a toast on the phone (an inline note on the phone’s activity screen).
• Start and end times that would end up equal are both left as they were and named under Couldn’t fill.
• Stage before cost code. When a fill changes the stage, the stage, cost code and cost code description move together; a new stage without a mapped cost code clears the cost code, as changing the stage by hand does.
• A form fills only the fields it shows; the rest are skipped. An empty cell fills nothing.

How cells are read

Matching ignores capitals and outer spaces.

Setup warnings

The server works the warnings out over the rows on every upload and every mapping save, and stores them as metadata.lookup.warnings (read-only; a save never sends them). They show under the columns in the Dataset dialog:

CSV-Lookup endpoints

lookup-match refuses a partial pick (a missing or extra filter column, or an empty target) with 400, and answers 404 for an unknown, other-organisation or non-lookup field. It is not offered to the Copilot.

Table Field Type (table)

A table field lets an admin define a grid of typed columns; users then add free-form rows when filling in a record. It behaves like a lightweight embedded spreadsheet attached to a prestart, diary, docket, or other entity.

Column configuration (admin)

Each column has:

The column config is stored in custom_field_definitions.metadata.columns as a JSON array.

Data entry (user)

• Users add, edit, and remove rows freely within the field.
• Each row is a map of { columnId → cellValue } stored in the entity’s custom_field_values.
• Columns marked Sum display an auto-calculated totals row at the bottom (read-only).

v1 limits

• Maximum 12 columns per table field.
• Maximum 100 rows per instance.

Rendering

The table renders in all three surfaces:

Entity availability

Available on: prestart_diary, activity, docket, materials_docket, issue.

Not available on worker_signup or induction_role — the row-entry UI requires an authenticated member session.

UI

Org Configuration Page

Located at /org/configuration. Accessible via the “Configuration” sidebar nav item (between Users and Settings).

• Entity type filter: Nine chips (All / Prestart & Diary / Activity / Docket / Materials Dockets / Worker Sign-Up / Issues / Drug & Alcohol / D&A Declaration) toggle which fields are shown
• Table columns: Label, Entity, Type, Required, Options, Sort Order, Actions (Edit / Archive)
• Create dialog: Entity type selector, field type selector, label, description, required toggle, options editor (shown for dropdown/radio/multiselect), sort order
• Edit dialog: Same as create but entity type and field type are read-only (shown as badges)
• Query key: ["org-custom-fields", orgId, entityType]

Project Custom Fields Tab

Located in the project detail view (/admin/config) as the fourth tab alongside Overview, Stages, and Members.

• Same structure as the org tab but uses project-level server actions
• Query key: ["project-custom-fields", orgId, projectId, entityType]
• Sections: Fields, Sign-on acknowledgements and Guide, switched in place from the header (the URL’s ?section= is rewritten without a route change).
• Sign-on acknowledgements: the project’s sign-on acknowledgements — the items a worker answers before signing themselves on (see Sign-Ons). It is a panel on this tab, not a custom-field entity type, and it is per-project: the org tab has no equivalent. Deep-linkable as /admin/config?tab=custom-fields&section=sign-on-acknowledgements. The panel also holds the project setting Allow workers to sign on prior to the prestart (Yes or No, No by default); it saves on its own, with the last saved questions.
• Guide: how CSV lookups work, deep-linkable as /admin/config?tab=custom-fields&section=guide (each part also has an anchor, e.g. #guide-warnings). It covers what a lookup does, what each column fills, what happens on a form, how to write values, the warnings, “Couldn’t fill”, and a made-up sample CSV (built in the browser). For a manager, the Dataset dialog’s warnings link to its Warnings part. Its Start here has two buttons, shown only with customfields.manage:
  • Add a CSV lookup opens the new-field dialog with CSV Lookup chosen;
  • Set up a dataset opens the new-field dialog when the project has no lookup, that lookup’s Dataset dialog when it has one, and a short list to choose from when it has several.

Real-Time Updates

The custom_field_definitions table has a LISTEN/NOTIFY trigger that fires on org_changes. The WebSocket provider maps this to invalidate both org-custom-fields and project-custom-fields query keys.

Security

• customfields.manage for every change — create, update, archive, reorder, dataset upload and mapping saves all need the customfields.manage permission (checked by the Go API). The web hides the setup controls and the Guide’s Start here buttons without it.
• Tenant isolation — Every query includes AND org_id = $N. Handlers verify JWT org_id matches the URL {orgId} parameter.
• Soft deletes — No permanent data removal. Archived fields can be restored in the future.
• Double input validation — Zod schemas in the BFF layer + Go struct validation with enum checks and length limits.
• Error-tolerant fan-out — If copying a template to one project fails, other projects still receive the copy.

Key Files

Short Term Induction Role fields are not managed here

Custom fields on a Short Term Induction Role do not appear in the Custom Fields tabs (org or project), and cannot be created from them. They are managed in the induction role editors:

Both editors support create, edit, reorder and delete.

The reason is not cosmetic. A row created from a Custom Fields tab carries entity_id = NULL, but the induction sign-on renderer only reads fields whose entity_id matches the role — so a field authored from the tab was a dead row that never appeared on any induction form. The tabs now filter these rows out even under the All filter, because the entity filter is applied server-side and All sends no entity_type at all.

induction_role remains a valid entity type in the Go API and in the database; nothing changed server-side.