tickets_custom_field_selections

Normalized form of choice selections on choice-type custom fields. One row per (ticket, field, selected option) — the same selection that the parent tickets_custom_fields.field_value carries as a JSON-stringified array.

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].value (treated as a list of selected option IDs; scalars are wrapped into a single-element list).

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.
selected_option_id	customFields[i].value[k]	One selected option's identifier; for singleSelect… exactly one element, for multiSelect… zero or more.
projectid	database	The project this ticket belongs to.
couchdbid	couchDbId	Legacy CouchDB document identifier (the ticket's).

Only choice-type fields produce rows here. Empty selections produce no rows (the parent field_value is '[]' in that case).

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.
selected_option_id	VARCHAR(255)	no	The selected option's identifier; foreign-key reference to tickets_custom_field_options.option_id for the same (ticketid, field_id). Part of the primary key.
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, selected_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 selections → one custom field
outgoing	tickets_custom_field_options	(ticketid, field_id, selected_option_id) = (ticketid, field_id, option_id)	many selections → one option
outgoing	tickets	tickets_custom_field_selections.ticketid = tickets.id	many rows → one ticket
outgoing	projects	tickets_custom_field_selections.projectid = projects.id	many rows → one project

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_options. On each observation of a ticket, the converter:

1. Issues DELETE FROM tickets_custom_field_selections 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].value (treated as a list).

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

Common Queries

Selected option IDs for a ticket

SELECT field_id, selected_option_id
FROM tickets_custom_field_selections
WHERE ticketid = 'your_ticket_id'
ORDER BY field_id, selected_option_id;

Selected options resolved to text

SELECT
  cs.ticketid,
  cs.field_id,
  co.option_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'
ORDER BY cs.field_id, co.option_text;

Tickets that selected a particular option

SELECT DISTINCT ticketid
FROM tickets_custom_field_selections
WHERE projectid          = 'your_project_id'
  AND field_id           = 'your_field_id'
  AND selected_option_id = 'your_option_id';

Pitfalls

• Backfill table. Auto-created on older datasets via migrate_tickets_custom_fields_tables.
• Empty selections produce no rows. A multiSelect… field with nothing chosen has tickets_custom_fields.field_value = '[]' and zero rows here. Use a LEFT JOIN and check for NULL if you want to surface "field present but unselected".
• Composite identity. A row's natural key is the full triple. selected_option_id alone is meaningless across tickets — option IDs are scoped to (ticketid, field_id) because the option catalog is per-ticket-snapshotted (see tickets_custom_field_options).
• Choice fields only. freetext, numeric, date fields produce no rows here.
• 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.