audit_roles

User-to-audit assignments by role. One row per (audit, email, role) triple. Captures the participants on an audit beyond the row-level audits.author and audits.lastmodifier columns.

Source Mapping

CouchDB document type: an "audit" document — rows are produced by asyncconvert.py's audits_roles(obj) handler iterating obj.participants.informed[] and reading the singular obj.participants.responsible.email. The handler skips when obj.deleted != 'null'.

Column	CouchDB source	Notes
email	obj.participants.informed[i].email, obj.participants.responsible.email	One row per email × role found in source.
role	computed (literal)	One of informed or responsible — emitted as a literal by the handler based on which array the email came from.
audit	obj.id	The audits.id this row belongs to.
projectid	obj.database	The project this audit belongs to.
couchdbid	obj.couchDbId	Legacy CouchDB document identifier (the audit's).

The responsible row here duplicates the audits.responsible column on the parent audit — they are kept in sync. The responsible row is only emitted if participants.responsible.email is non-empty.

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: informed, responsible.
projectid	VARCHAR(255)	yes	The project this audit belongs to.
audit	VARCHAR(255)	yes	Foreign-key reference to audits.id.
couchdbid	VARCHAR(255)	yes	Legacy CouchDB identifier of the audit document.

Keys

• No primary key. Identity is the unique constraint uk_audit_email_role on (audit, email, role).
• Unique key: uk_audit_email_role on (audit, email, role).

Indexes

• idx_projectid on projectid
• idx_parent on audit

Relationships

Direction	Other table	Join	Cardinality
outgoing	audits	audit_roles.audit = audits.id	many rows → one audit
outgoing	users	audit_roles.email = users.email	many rows → one user
outgoing	projects	audit_roles.projectid = projects.id	many rows → one project

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

Refresh Semantics

audit_roles is rebuilt per audit document. On each observation of an audit, the converter:

1. Issues DELETE FROM audit_roles WHERE audit = <audit_id> (when delete tracking is enabled).
2. Iterates obj.participants.informed[] and reads obj.participants.responsible.email, emitting one INSERT IGNORE per email × role.

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

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

Common Queries

All participants on an audit

SELECT email, role
FROM audit_roles
WHERE audit = 'your_audit_id'
ORDER BY role, email;

Audits a user is informed on or responsible for

SELECT
  a.id,
  a.name,
  a.status,
  GROUP_CONCAT(ar.role ORDER BY ar.role SEPARATOR ', ') AS roles
FROM audit_roles ar
JOIN audits a ON a.id = ar.audit
WHERE ar.email   = 'user@example.com'
  AND a.archived IS NULL
GROUP BY a.id, a.name, a.status
ORDER BY a.lastmodifieddate DESC;

Role distribution across the project

SELECT
  role,
  COUNT(*)                AS rows,
  COUNT(DISTINCT audit)   AS distinct_audits,
  COUNT(DISTINCT email)   AS distinct_users
FROM audit_roles
WHERE projectid = 'your_project_id'
GROUP BY role
ORDER BY rows DESC;

Pitfalls

• role is a closed two-value enum hard-coded in the converter. Values are informed and responsible. There is no consulted or accountable role on audits — those concepts only exist on tickets (see tickets_roles) and on project_participants. The audit's author lives on audits.author, not here.
• responsible is duplicated on the parent. The same email also appears in audits.responsible. They are kept in sync by the converter; if you only need the responsible person, query the parent column directly to skip the join.
• No primary key. Identity is (audit, email, role). A user appears once per role per audit but may appear under both roles on the same audit.
• Reading during an in-flight reindex of an audit may briefly return zero rows for that audit. Between the DELETE and the INSERTs, WHERE audit = <id> returns nothing.
• No FK constraints. Joins to users.email and audits.id are by string match.