audits_questions

One row per question copied from the audit's template into a specific audit instance. Questions sit inside categories (audits_category); answers to these questions live in audits_answer.

Source Mapping

CouchDB document type: an "audit" document — rows are produced by asyncconvert.py's audits_questions(obj) handler iterating obj.questions[*].questions[*] (the per-category question lists). The handler skips when obj.deleted != 'null'.

Column	CouchDB source	Notes
categoryid	computed (hash)	hash(question.categoryName) — same as audits_category.categoryid for the same category name.
questionid	computed (hash)	hash(categoryid + ":" + question_text). Same category + same question text → same questionid across audits. See Pitfalls.
question	obj.questions[i].questions[j].question	The question text shown to the auditor. Free text.
description	obj.questions[i].questions[j].description	Optional helper text below the question. Free text.
answertype	obj.questions[i].questions[j].settings.answertype	The expected answer type (e.g. 'multiplechoice', 'text', 'number'). Free text — no closed enum is enforced at the storage layer.
audit	obj.id	The audits.id this question 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). Joins to audits_category.categoryid — but always also filter on audit (see Pitfalls).
questionid	VARCHAR(255)	yes	Computed hash(categoryid + ":" + question_text). Same text → same id across audits.
question	TEXT	yes	The question text shown to the auditor.
description	TEXT	yes	Optional helper text below the question.
answertype	VARCHAR(255)	yes	Expected answer type — free text from source (e.g. 'multiplechoice', 'text', 'number').
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. Identity within an audit is the composite (audit, categoryid, questionid); 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 at this table — but the parallel table audit_answers_tickets has one on questionID. Cross-table joins on questionid will scan audits_questions if not pre-filtered by audit or projectid; both indexes exist for that purpose.

Relationships

Direction	Other table	Join	Cardinality
outgoing	audits	audits_questions.audit = audits.id	many questions → one audit
outgoing	audits_category	audits_questions.audit = audits_category.audit AND audits_questions.categoryid = audits_category.categoryid	many questions → one category
outgoing	projects	audits_questions.projectid = projects.id	many rows → one project
incoming	audits_answer	audits_answer.audit = audits_questions.audit AND audits_answer.questionid = audits_questions.questionid	one question → zero or more answers
incoming	audit_answers_tickets	audit_answers_tickets.audit = audits_questions.audit AND audit_answers_tickets.questionID = audits_questions.questionid	one question → zero or more follow-up tickets

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

Refresh Semantics

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

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

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

Questions in an audit, grouped by category

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

Question count per audit

SELECT
  audit,
  COUNT(*) AS question_count
FROM audits_questions
WHERE projectid = 'your_project_id'
GROUP BY audit
ORDER BY question_count DESC;

Most common questions across a project

SELECT
  question,
  COUNT(DISTINCT audit) AS audits_using_question
FROM audits_questions
WHERE projectid = 'your_project_id'
GROUP BY question
ORDER BY audits_using_question DESC
LIMIT 50;

This intentionally groups by free-text question rather than by questionid — equivalent here, since questionid is a hash of the text plus category, so duplicates collapse identically.

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

Pitfalls

• questionID collision across audits. questionid is hash(categoryid + ":" + question_text). Two audits that share an identical category-name and question-text pair will have the same questionid. Joining audits_questions ↔ audits_answer (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.
• No primary key. Identity within an audit is the composite (audit, categoryid, questionid). If a partial reindex fails between the DELETE and the INSERTs, an audit may briefly have zero questions. Reindex again to recover.
• answertype is free text. Common values include 'multiplechoice', 'text', 'number', but the storage layer does not enforce a closed enum. Coerce defensively when branching on this column.
• questionid is not indexed on this table. Joining audits_answer to audits_questions on questionid alone will scan; pre-filter by audit or projectid so the optimizer can use idx_parent or idx_project.