tickets_roles

User-to-ticket assignments by role. One row per (ticket, email, role) triple. Captures the RACI-style participants on a ticket.

Source Mapping

CouchDB document type: a "ticket" document — rows are produced by asyncconvert.py's tickets_roles(obj) handler iterating the ticket's participants arrays.

Column	CouchDB source	Notes
email	participants.consulted[i].email, participants.informed[i].email, participants.responsible.email	One row per email × role found in source.
role	computed (literal)	One of consulted, informed, responsible — emitted as a literal by the handler based on which array the email came from.
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).

The handler iterates participants.consulted[] and participants.informed[] (multi-valued) and reads the singular participants.responsible.email once. The responsible row here duplicates the tickets.responsible column on the parent ticket — they are kept in sync.

Column Reference

Column	Type	Nullable	Description
email	VARCHAR(255)	yes	The participant's email; foreign-key reference to users.email.
role	VARCHAR(255)	yes	RACI-style role — closed enum: consulted, informed, responsible.
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_email_role on (ticketid, email, role).
• Unique key: uk_ticket_email_role on (ticketid, email, role).

Indexes

• idx_projectid on projectid
• idx_ticket on ticketid

Relationships

Direction	Other table	Join	Cardinality
outgoing	tickets	tickets_roles.ticketid = tickets.id	many rows → one ticket
outgoing	users	tickets_roles.email = users.email	many rows → one user
outgoing	projects	tickets_roles.projectid = projects.id	many rows → one project

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

Refresh Semantics

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

1. Issues DELETE FROM tickets_roles WHERE ticketid = <ticket_id> (when delete tracking is enabled — see add_deletes).
2. Iterates each participants.{consulted, informed}[] array and participants.responsible.email and emits one INSERT IGNORE per email × role.

Inserts are INSERT IGNORE against the (ticketid, email, role) unique key, so duplicates within a single source document collapse.

A reindex with type=ticket (or a full reindex) rebuilds this table as part of the ticket reindex. There is no separate type=tickets_roles target.

Common Queries

All participants on a ticket

SELECT email, role
FROM tickets_roles
WHERE ticketid = 'your_ticket_id'
ORDER BY role, email;

Tickets a user is involved in (any role)

SELECT
  t.id,
  t.title,
  t.status,
  GROUP_CONCAT(tr.role ORDER BY tr.role SEPARATOR ', ') AS roles
FROM tickets_roles tr
JOIN tickets t ON t.id = tr.ticketid
WHERE tr.email   = 'user@example.com'
  AND t.archived IS NULL
GROUP BY t.id, t.title, t.status
ORDER BY t.lastmodifieddate DESC;

Role distribution across the project

SELECT
  role,
  COUNT(*)                  AS rows,
  COUNT(DISTINCT ticketid)  AS distinct_tickets,
  COUNT(DISTINCT email)     AS distinct_users
FROM tickets_roles
WHERE projectid = 'your_project_id'
GROUP BY role
ORDER BY rows DESC;

Pitfalls

• No primary key. Identity is (ticketid, email, role). A user appears once per role per ticket but may appear under multiple roles on the same ticket.
• role is a closed three-value enum hard-coded in the converter. Values are consulted, informed, responsible. The ticket's author is on tickets.author, not here. The "started" state of a ticket implies a responsible role; if that ever drifts (e.g. the responsible email changes), the row here is rebuilt on the next observation.
• 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. Reindex again if a partial reindex was interrupted.
• No FK constraints. Joins to users.email and tickets.id are by string match.