tickets_tags

Free-text tags applied to a ticket. One row per (ticket, tag). Used for ad-hoc categorization that does not fit the closed status enum or progress-label semantics.

Source Mapping

CouchDB document type: a "ticket" document — rows are produced by asyncconvert.py's tickets_tags(obj) handler iterating the ticket's tags[] array. Each element becomes one row.

Column	CouchDB source	Notes
tag	tags[i]	One element of the ticket's tags array, cast to a string. Free text.
ticketid	id	The tickets.id this row belongs to.
projectid	database	The project this ticket belongs to.
couchdbid	couchDbId	Legacy CouchDB document identifier (the ticket's).

Column Reference

Column	Type	Nullable	Description
tag	VARCHAR(255)	yes	Free-text tag value applied to the ticket.
ticketid	VARCHAR(255)	yes	Foreign-key reference to tickets.id.
projectid	VARCHAR(255)	yes	The project this ticket belongs to.
couchdbid	VARCHAR(255)	yes	Legacy CouchDB identifier of the ticket document.

Keys

• No primary key. Identity is the unique constraint uk_ticket_tag on (ticketid, tag).
• Unique key: uk_ticket_tag on (ticketid, tag).

Indexes

• idx_projectid on projectid
• idx_ticket on ticketid

Relationships

Direction	Other table	Join	Cardinality
outgoing	tickets	tickets_tags.ticketid = tickets.id	many rows → one ticket
outgoing	projects	tickets_tags.projectid = projects.id	many rows → one project

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

Refresh Semantics

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

1. Issues DELETE FROM tickets_tags WHERE ticketid = <ticket_id> (when delete tracking is enabled).
2. Iterates the ticket's tags[] array and emits one INSERT IGNORE per element.

Inserts are INSERT IGNORE against the (ticketid, tag) unique key, so duplicate tags within a single source document collapse.

A reindex with type=ticket (or a full reindex) rebuilds this table as part of the ticket reindex.

Common Queries

All tags on a ticket

SELECT tag
FROM tickets_tags
WHERE ticketid = 'your_ticket_id'
ORDER BY tag;

Ticket count per tag (project scoped)

SELECT
  tag,
  COUNT(DISTINCT ticketid) AS tickets
FROM tickets_tags
WHERE projectid = 'your_project_id'
GROUP BY tag
ORDER BY tickets DESC;

Tickets carrying a specific tag

SELECT t.id, t.title, t.status, t.dueDate
FROM tickets t
JOIN tickets_tags tt ON tt.ticketid = t.id
WHERE tt.tag    = 'urgent'
  AND t.archived IS NULL
ORDER BY t.dueDate;

Pitfalls

• tag is free text. Casing and spelling drift across users. Group with LOWER(tag) if cross-user rollups matter.
• No primary key. Identity is (ticketid, tag) — a tag appears at most once per ticket, but the same string can apply to many tickets.
• Reading during an in-flight reindex of a ticket may briefly return zero rows for that ticket. Between the DELETE and the INSERTs, WHERE ticketid = <id> returns nothing.
• No FK constraints. Joins to tickets.id are by string match.