tickets_custom_field_options

Selectable options catalog for choice-type custom fields (singleSelectMultipleChoice, multiSelectMultipleChoice). One row per (ticket, field, option) — the option catalog is captured per ticket (denormalized snapshot at the time of source sync), so the same option list is repeated for every ticket that carries the field.

Backfill: This table is auto-created on older datasets via migrate_tickets_custom_fields_tables in backend/services/sql.py.

Source Mapping

CouchDB document type: a "ticket" document — rows are produced by asyncconvert.py's tickets_custom_fields(obj) handler when a custom field is a choice type, by iterating customFields[i].richOptions[].

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.
option_id	customFields[i].richOptions[j].id	The option's identifier within the field.
option_type	customFields[i].richOptions[j].type	Option type marker from source (e.g. text, image).
option_text	customFields[i].richOptions[j].text	Display text for the option.
option_image	customFields[i].richOptions[j].image	Optional image reference for the option.
projectid	database	The project this ticket belongs to.
couchdbid	couchDbId	Legacy CouchDB document identifier (the ticket's).

Only choice-type fields produce rows here. freetext, numeric, and date fields generate no options.

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	Foreign-key reference to tickets_custom_fields.field_id (within the same ticket). Part of the primary key.
option_id	VARCHAR(255)	no	The option's identifier within the field. Part of the primary key.
option_type	VARCHAR(255)	yes	Option type marker from source (e.g. text, image).
option_text	TEXT	yes	Display text for the option.
option_image	VARCHAR(255)	yes	Optional image reference for the option.
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, option_id)

Indexes

• idx_ticket on ticketid
• idx_field on field_id
• idx_projectid on projectid

Relationships

Direction	Other table	Join	Cardinality
outgoing	tickets_custom_fields	(ticketid, field_id)	many options → one custom field
outgoing	tickets	tickets_custom_field_options.ticketid = tickets.id	many rows → one ticket
outgoing	projects	tickets_custom_field_options.projectid = projects.id	many rows → one project
incoming	tickets_custom_field_selections	(ticketid, field_id, option_id) = (ticketid, field_id, selected_option_id)	one option → zero or one selection per ticket

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

Refresh Semantics

Rebuilt per ticket document alongside tickets_custom_fields and tickets_custom_field_selections. On each observation of a ticket, the converter:

1. Issues DELETE FROM tickets_custom_field_options WHERE ticketid = <ticket_id> (when delete tracking is enabled).
2. For each choice-type field on the ticket, emits one INSERT per element of customFields[i].richOptions[].

A reindex with type=ticket (or a full reindex) rebuilds this table.

Common Queries

Option catalog for a field on a ticket

SELECT option_id, option_text, option_type
FROM tickets_custom_field_options
WHERE ticketid = 'your_ticket_id'
  AND field_id = 'your_field_id'
ORDER BY option_text;

Resolve the chosen options to their text

SELECT
  cs.ticketid,
  cs.field_id,
  GROUP_CONCAT(co.option_text SEPARATOR ', ') AS selected_text
FROM tickets_custom_field_selections cs
JOIN tickets_custom_field_options    co
  ON co.ticketid  = cs.ticketid
 AND co.field_id  = cs.field_id
 AND co.option_id = cs.selected_option_id
WHERE cs.ticketid = 'your_ticket_id'
GROUP BY cs.ticketid, cs.field_id;

Most-used options across a project

SELECT
  co.option_text,
  COUNT(DISTINCT cs.ticketid) AS tickets_selecting
FROM tickets_custom_field_options    co
JOIN tickets_custom_field_selections cs
  ON cs.ticketid  = co.ticketid
 AND cs.field_id  = co.field_id
 AND cs.selected_option_id = co.option_id
WHERE co.projectid = 'your_project_id'
GROUP BY co.option_text
ORDER BY tickets_selecting DESC;

Pitfalls

• Backfill table. Auto-created on older datasets via migrate_tickets_custom_fields_tables.
• Per-ticket snapshot of the option catalog. Each ticket carries its own copy of the option list as it stood when the ticket was synced. If a project edits an option's text, existing tickets keep the old text until they are re-synced. Aggregations across the whole project should be aware of this.
• Composite identity. Joins to selections must use all three columns (ticketid, field_id, option_id) — option_id alone is not unique because the catalog repeats per ticket.
• Choice fields only. freetext, numeric, and date fields produce no rows here. Their value lives on tickets_custom_fields.field_value directly.
• Reading during an in-flight reindex of a ticket may briefly return zero rows for that ticket.
• No FK constraints. Joins are by string match.