audits_answer

One row per recorded answer to an audit question. For multiple-choice questions with rich options, the answer is resolved from the option ID to the option's display text before insert. For other answer types, the raw answer string is stored.

Source Mapping

CouchDB document type: an "audit" document — rows are produced by asyncconvert.py's audits_answer(obj) handler iterating obj.questions[*].questions[*].answer[*]. The handler reads each answer in the question's answer array and emits one row per element.

Column	CouchDB source	Notes
categoryid	computed (hash)	hash(question.categoryName) — matches audits_category.categoryid for the same category.
questionid	computed (hash)	hash(categoryid + ":" + question_text) — matches audits_questions.questionid for the same question text. See Pitfalls.
answer	obj.questions[i].questions[j].answer[k]	The recorded answer. For 'multiplechoice' questions with settings.richOptions, the converter looks up the matching option in richOptions and stores its text; otherwise stores the raw answer value.
answertype	obj.questions[i].questions[j].settings.answertype	The answer type at the time of recording. Free text.
description	absent	Schema column exists but is never written by the converter (no source column maps to it). Always NULL.
audit	obj.id	The audits.id this answer belongs to.
projectid	obj.database	The project this audit belongs to.
couchdbid	obj.couchDbId	Legacy CouchDB document identifier (the audit's).

Column Reference

Column	Type	Nullable	Description
categoryid	VARCHAR(255)	yes	Computed hash(categoryName).
questionid	VARCHAR(255)	yes	Computed hash(categoryid + ":" + question_text). Same text → same id across audits — see Pitfalls.
answer	TEXT	yes	The recorded answer. For multiple-choice with rich options, this is the option's display text; otherwise the raw value.
description	TEXT	yes	Reserved column — never written by the converter. Always NULL.
answertype	VARCHAR(255)	yes	Answer type at the time of recording (free text).
audit	VARCHAR(255)	yes	Foreign-key reference to audits.id.
projectid	VARCHAR(255)	yes	The project this audit belongs to.
couchdbid	VARCHAR(255)	yes	Legacy CouchDB identifier of the audit document.

Keys

• No primary key, no unique constraint. A question can have multiple answers (e.g. a multi-select with several options chosen); identity is (audit, questionid, answer) in practice. Uniqueness within an audit is enforced procedurally by the converter (delete-then-reinsert per audit document).

Indexes

• idx_categoryid on categoryid
• idx_project on projectid
• idx_parent on audit

There is no index on questionid. Joining audits_questions to audits_answer on questionid alone will scan; pre-filter by audit or projectid.

Relationships

Direction	Other table	Join	Cardinality
outgoing	audits	audits_answer.audit = audits.id	many answers → one audit
outgoing	audits_questions	audits_answer.audit = audits_questions.audit AND audits_answer.questionid = audits_questions.questionid	many answers → one question
outgoing	audits_category	audits_answer.audit = audits_category.audit AND audits_answer.categoryid = audits_category.categoryid	many answers → one category
outgoing	projects	audits_answer.projectid = projects.id	many rows → one project

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

Refresh Semantics

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

1. Issues DELETE FROM audits_answer WHERE audit = <audit_id> (when delete tracking is enabled).
2. Iterates obj.questions[*].questions[*].answer[*] and inserts one row per recorded answer. For multiple-choice questions with settings.richOptions, the answer ID is resolved to the option's display text before insert.

Reading during an in-flight reindex of an audit may briefly return zero rows for that audit.

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

Common Queries

All answers in an audit

SELECT
  ac.categoryname,
  aq.question,
  aa.answer,
  aa.answertype
FROM audits_answer aa
JOIN audits_questions aq
  ON aq.audit      = aa.audit
 AND aq.questionid = aa.questionid
JOIN audits_category ac
  ON ac.audit      = aa.audit
 AND ac.categoryid = aa.categoryid
WHERE aa.audit = 'your_audit_id'
ORDER BY ac.categoryname, aq.question;

Note the join on (audit, questionid) and (audit, categoryid) — not on the hashed IDs alone. See Pitfalls.

Unanswered questions in an audit

SELECT
  aq.question
FROM audits_questions aq
LEFT JOIN audits_answer aa
       ON aa.audit      = aq.audit
      AND aa.questionid = aq.questionid
WHERE aq.audit  = 'your_audit_id'
  AND aa.answer IS NULL;

Answer distribution for a single question across audits

SELECT
  aa.answer,
  COUNT(*) AS times_recorded
FROM audits_answer aa
JOIN audits_questions aq
  ON aq.audit      = aa.audit
 AND aq.questionid = aa.questionid
WHERE aq.question  = 'Is the safety equipment present?'
  AND aa.projectid = 'your_project_id'
GROUP BY aa.answer
ORDER BY times_recorded DESC;

This works precisely because questionid is deterministic from the question text — same text across audits = same questionid. Filtering by aq.question plus projectid keeps the rollup scoped.

Cross-table queries (completion %, tickets created from findings) live on the Audits domain landing.

Pitfalls

• questionID collision across audits. questionid is hash(categoryid + ":" + question_text). Joining audits_answer ↔ audits_questions (or ↔ audit_answers_tickets) on questionid alone collapses rows across unrelated audits. Always also filter or join on audit or projectid. See the domain landing's deep dive.
• description is always NULL. The schema reserves it but the converter never writes a value. Don't filter on it; the field on audits_questions is the one that carries the question's helper text.
• No primary key. A question can have multiple answers in this table (multi-select). Identity is (audit, questionid, answer).
• Multiple-choice answers store text, not option IDs. For answertype = 'multiplechoice' with rich options, the converter resolves the chosen option ID to its display text before insert. This means joining back to a separate options catalog is not needed — but also that an option renamed upstream will produce a different string the next time the audit is re-synced.
• No questionid index. Joining on questionid alone scans the table; pre-filter by audit or projectid so the optimizer can use idx_parent or idx_project.