audits_category

One row per category copied from the audit's template into a specific audit instance. Categories are the top-level grouping of questions within an audit (e.g. "Roof", "Foundation", "Electrical").

Source Mapping

CouchDB document type: an "audit" document — rows are produced by asyncconvert.py's audits_category(obj) handler iterating obj.questions[*]. Each top-level entry in obj.questions is one category.

Column	CouchDB source	Notes
categoryname	obj.questions[i].categoryName	Human-readable category name. Free text from the template.
audit	obj.id	The audits.id this category belongs to.
categoryid	computed (hash)	hash(categoryName). Deterministic from the name alone — see Pitfalls.
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
categoryname	TEXT	yes	Human-readable category name (free text).
audit	VARCHAR(255)	yes	Foreign-key reference to audits.id.
categoryid	VARCHAR(255)	yes	Computed identifier — hash(categoryName). Same name → same categoryid across audits.
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 category appears once per audit, but the same categoryid recurs across every audit that uses a category with the same name. 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

Relationships

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

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

Refresh Semantics

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

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

Reading during an in-flight reindex of an audit may briefly return zero rows for that audit. Reindex again if a partial reindex was interrupted.

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

Common Queries

Categories within an audit

SELECT categoryid, categoryname
FROM audits_category
WHERE audit = 'your_audit_id'
ORDER BY categoryname;

Most common category names across a project

SELECT categoryname,
       COUNT(DISTINCT audit) AS audits_with_category
FROM audits_category
WHERE projectid = 'your_project_id'
GROUP BY categoryname
ORDER BY audits_with_category DESC;

Question counts per category

SELECT
  ac.audit,
  ac.categoryname,
  COUNT(aq.questionid) AS question_count
FROM audits_category ac
LEFT JOIN audits_questions aq
       ON aq.audit      = ac.audit
      AND aq.categoryid = ac.categoryid
WHERE ac.projectid = 'your_project_id'
GROUP BY ac.audit, ac.categoryname
ORDER BY ac.audit, question_count DESC;

Pitfalls

• categoryid is deterministic from the name. Two audits with a category named "Roof" will share the same categoryid. Joining audits_category rows on categoryid alone collapses across audits. Always also filter or join on audit (or projectid) when you need per-audit identity.
• No primary key. If a partial reindex fails between the DELETE and the INSERTs, an audit may briefly have zero categories. Reindex again to recover.
• categoryname is free text from the template. Spelling and casing differences across templates create separate categoryid values for what users would consider "the same" category. Cross- template rollups by category have to do their own normalization.