`tickets_custom_fields`

One row per (ticket, custom field). Carries the value of each project-defined custom field on a ticket. For simple types (freetext, numeric, date) field_value carries the value directly; for choice types it carries a JSON-stringified array of selected option IDs (the same selection is normalized into tickets_custom_field_selections).

Backfill: This table is auto-created on older datasets via migrate_tickets_custom_fields_tables in backend/services/sql.py. New datasets get it as part of the initial schema; older ones gain it on first migration.

Source Mapping

CouchDB document type: a "ticket" document — rows are produced by asyncconvert.py's tickets_custom_fields(obj) handler iterating the ticket's customFields[] array.

Column	CouchDB source	Notes
`ticketid`	`id`	The `tickets.id` this row belongs to.
`field_id`	`customFields[i].id`	The custom field's identifier within the project.
`field_label`	`customFields[i].label`	Human-readable field label as defined for the project.
`field_type`	`customFields[i].type`	One of the closed `field_type` enum values (see Pitfalls).
`field_value`	computed (derived)	For simple types, `customFields[i].value` directly. For choice types, a JSON-stringified array of selected option IDs (`'[]'` when nothing is selected).
`projectid`	`database`	The project this ticket belongs to.
`couchdbid`	`couchDbId`	Legacy CouchDB document identifier (the ticket's).

Column Reference

Column	Type	Nullable	Description
`ticketid`	VARCHAR(255)	no	Foreign-key reference to `tickets.id`. Part of the primary key.
`field_id`	VARCHAR(255)	no	The custom field's identifier within the project. Part of the primary key.
`field_label`	VARCHAR(255)	yes	Human-readable label as defined for the project (denormalized from source).
`field_type`	ENUM('freetext', 'numeric', 'date', 'singleSelectMultipleChoice', 'multiSelectMultipleChoice')	yes	Closed enum naming the field's data type.
`field_value`	TEXT	yes	Direct value for simple types; JSON-stringified array of selected option IDs for choice types.
`projectid`	VARCHAR(255)	yes	The project this ticket belongs to.
`couchdbid`	VARCHAR(255)	yes	Legacy CouchDB identifier of the ticket document.

Keys

Primary key: (ticketid, field_id)

Indexes

idx_ticket on ticketid
idx_projectid on projectid
idx_field_type on field_type

Relationships

Direction	Other table	Join	Cardinality
outgoing	`tickets`	`tickets_custom_fields.ticketid = tickets.id`	many rows → one ticket
outgoing	`projects`	`tickets_custom_fields.projectid = projects.id`	many rows → one project
incoming	`tickets_custom_field_options`	`tickets_custom_field_options.(ticketid, field_id) = tickets_custom_fields.(ticketid, field_id)`	one field → many options (choice types)
incoming	`tickets_custom_field_selections`	`tickets_custom_field_selections.(ticketid, field_id) = tickets_custom_fields.(ticketid, field_id)`	one field → many selections (choice types)

None of these are foreign-keyed in MariaDB; joins are by string match.

Refresh Semantics

tickets_custom_fields is rebuilt per ticket document. On each observation of a ticket, the converter:

Issues DELETE FROM tickets_custom_fields WHERE ticketid = <ticket_id> (when delete tracking is enabled). The same handler also deletes any rows in tickets_custom_field_options and tickets_custom_field_selections for that ticket.
Iterates the ticket's customFields[] array and emits one INSERT IGNORE per field. For choice fields, it additionally emits rows into tickets_custom_field_options and tickets_custom_field_selections.

Inserts are INSERT IGNORE against the composite primary key.

A reindex with type=ticket (or a full reindex) rebuilds all three custom-field tables.

Common Queries

Custom-field values on a ticket

SELECT field_label, field_type, field_value
FROM tickets_custom_fields
WHERE ticketid = 'your_ticket_id'
ORDER BY field_label;

All `numeric` field values across a project

SELECT
  ticketid,
  field_label,
  CAST(field_value AS DECIMAL(20,4)) AS numeric_value
FROM tickets_custom_fields
WHERE projectid = 'your_project_id'
  AND field_type = 'numeric'
  AND field_value IS NOT NULL
  AND field_value <> '';

Field-type distribution

SELECT
  field_type,
  COUNT(DISTINCT field_id) AS distinct_fields,
  COUNT(*)                  AS rows
FROM tickets_custom_fields
WHERE projectid = 'your_project_id'
GROUP BY field_type
ORDER BY rows DESC;

Cross-table queries (resolving choice selections to their option text) live on the Tickets domain landing.

Pitfalls

Backfill table. This table is auto-created on older datasets via migrate_tickets_custom_fields_tables. If a dataset's tickets_custom_fields is empty, either no ticket carries custom fields, or no ticket has been re-synced since the migration.
field_id is project-scoped, not global. Two projects each defining a "Cost" field will have two different field_id values. Always include projectid (or the parent ticket's projectid) when joining across projects.
field_value carries different shapes for different types. For freetext / numeric / date, it is the raw value as a string. For choice types, it is '[]' (when empty) or a JSON array of selected option IDs as a string. Use the tickets_custom_field_selections table for normalized choice data; reach for field_value only when you need the raw form.
field_type is a closed ENUM. Five values: freetext, numeric, date, singleSelectMultipleChoice, multiSelectMultipleChoice. Casing matters — singleSelect… is camelCase.
field_label is denormalized from source. The label can drift if a project renames the field upstream and a ticket has not been re-synced. Use field_id for joins.
Reading during an in-flight reindex of a ticket may briefly return zero rows for that ticket.
No FK constraints. Joins to tickets.id are by string match.

Source Mapping​

Column Reference​

Keys​

Indexes​

Relationships​

Refresh Semantics​

Common Queries​

Custom-field values on a ticket​

All numeric field values across a project​

Field-type distribution​

Pitfalls​