audit_answers_tickets

Bridge linking an audit question to the tickets that were created as follow-ups from that question's answer. One row per (audit, question, ticket) triple. This is how an audit finding becomes a punch-list ticket.

Source Mapping

CouchDB document type: an "audit" document — rows are produced by asyncconvert.py's audit_answers_tickets(obj) handler iterating obj.questions[*].questions[*].ticket[*] (each question's ticket array). The handler skips when obj.deleted != 'null'.

Column	CouchDB source	Notes
audit	obj.id	The audits.id this row belongs to.
ticket	obj.questions[i].questions[j].ticket[k]	The tickets.id created as a follow-up to this question's answer.
questionID	computed (hash)	hash(categoryid + ":" + question_text) — matches audits_questions.questionid for the same question. See Pitfalls. Note the camelCase column name.

This is the only table in the audit domain that does not carry projectid or couchdbid; identity is purely (audit, questionID, ticket).

Column Reference

Column	Type	Nullable	Description
audit	VARCHAR(255)	yes	Foreign-key reference to audits.id.
ticket	VARCHAR(255)	yes	Foreign-key reference to tickets.id — the ticket created from this finding.
questionID	VARCHAR(255)	yes	Computed hash(categoryid + ":" + question_text). Matches audits_questions.questionid. Same text → same id across audits — see Pitfalls.

Keys

• No primary key, no unique constraint. A question can spawn multiple tickets, and one ticket can satisfy multiple questions in the same audit; identity is the composite (audit, questionID, ticket). Uniqueness within an audit is enforced procedurally by the converter (delete-then-reinsert per audit document).

Indexes

• idx_ticket on ticket
• idx_audit on audit
• idx_questionID on questionID

This table is unusual in having a questionID index — it makes "which audits' findings produced this question's tickets?" lookups fast across the dataset.

Relationships

Direction	Other table	Join	Cardinality
outgoing	audits	audit_answers_tickets.audit = audits.id	many rows → one audit
outgoing	tickets	audit_answers_tickets.ticket = tickets.id	many rows → one ticket
outgoing	audits_questions	audit_answers_tickets.audit = audits_questions.audit AND audit_answers_tickets.questionID = audits_questions.questionid	many rows → one question
outgoing	audits_answer	audit_answers_tickets.audit = audits_answer.audit AND audit_answers_tickets.questionID = audits_answer.questionid	many rows → many answers (a question can have multiple recorded answers)

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

Refresh Semantics

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

1. Issues DELETE FROM audit_answers_tickets WHERE audit = <audit_id> (when delete tracking is enabled).
2. Iterates obj.questions[*].questions[*].ticket[*] and inserts one row per (audit, question, ticket) triple.

Reading during an in-flight reindex of an audit may briefly return zero rows for that audit. The tickets rows themselves are written by a separate reindex path (type=ticket); a finding here may reference a ticket ID whose tickets row arrives slightly earlier or later.

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

Common Queries

All tickets created from a single audit

SELECT
  aat.questionID,
  aq.question,
  t.id     AS ticket_id,
  t.title,
  t.status
FROM audit_answers_tickets aat
JOIN tickets t
  ON t.id = aat.ticket
LEFT JOIN audits_questions aq
       ON aq.audit      = aat.audit
      AND aq.questionid = aat.questionID
WHERE aat.audit = 'your_audit_id'
ORDER BY aq.question, t.lastmodifieddate DESC;

Tickets traced back to their source audit

SELECT
  t.id    AS ticket_id,
  t.title,
  a.id    AS audit_id,
  a.name  AS audit_name,
  a.audittype
FROM audit_answers_tickets aat
JOIN tickets t ON t.id = aat.ticket
JOIN audits  a ON a.id = aat.audit
WHERE t.projectid = 'your_project_id'
ORDER BY t.creationdate DESC;

Question fingerprints with the most follow-up tickets

SELECT
  aat.questionID,
  COUNT(DISTINCT aat.ticket) AS distinct_tickets,
  COUNT(DISTINCT aat.audit)  AS distinct_audits
FROM audit_answers_tickets aat
JOIN audits a ON a.id = aat.audit
WHERE a.projectid = 'your_project_id'
GROUP BY aat.questionID
ORDER BY distinct_tickets DESC
LIMIT 25;

This intentionally groups by questionID (the hash) to find recurring findings across audits — the collision is wanted here.

Cross-table queries (tickets created from audit findings, in full prose) live on the Audits domain landing.

Pitfalls

• questionID collision across audits. questionID is hash(categoryid + ":" + question_text). Joining audit_answers_tickets ↔ audits_questions (or ↔ audits_answer) on questionID alone collapses rows across unrelated audits. Always also filter or join on audit or projectid unless you explicitly want a cross-audit rollup (as in the "question fingerprints" query above). See the domain landing's deep dive.
• Column name is camelCase: questionID. Unlike the other question-id columns in the schema (which are lowercase questionid on audits_questions and audits_answer), this column was created with questionID. MariaDB column names are case-insensitive, so joining audits_answer.questionid = audit_answers_tickets.questionID works fine, but be alert when scripting against case-sensitive tools.
• No projectid and no couchdbid columns. This is the only audit-domain table without them. To filter by project, join through audits first.
• No primary key, no unique constraint. Identity is the composite (audit, questionID, ticket). One ticket can be linked to multiple findings within the same audit (when several questions point at the same ticket).
• References tickets that may not yet exist (or may have been archived). The ticket value here is a tickets.id written by the ticket reindex pipeline. If the ticket has been archived, the link still resolves; if the ticket has been hard-deleted upstream, the join will return NULL.