audits_tags

Free-text tags applied to an audit. One row per (audit, tag) pair. Tags are user-defined and not constrained to a controlled vocabulary — two projects' "QC Inspection" tag may be spelled differently.

Source Mapping

CouchDB document type: an "audit" document — rows are produced by asyncconvert.py's audits_tags(obj) handler iterating obj.tags (a string array). The handler skips when obj.deleted != 'null'.

Column	CouchDB source	Notes
tag	obj.tags[i]	Tag text (free string).
auditid	obj.id	The audits.id this tag belongs to. Note the column name is auditid (not audit) — this table is unique in using that spelling.
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
tag	VARCHAR(255)	yes	Tag text (free string).
auditid	VARCHAR(255)	yes	Foreign-key reference to audits.id. Spelled auditid, not audit (see Pitfalls).
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. Identity is the unique constraint uk_audit_tag on (auditid, tag).
• Unique key: uk_audit_tag on (auditid, tag).

Indexes

• idx_projectid on projectid
• idx_audit on auditid

Relationships

Direction	Other table	Join	Cardinality
outgoing	audits	audits_tags.auditid = audits.id	many tags → one audit
outgoing	projects	audits_tags.projectid = projects.id	many rows → one project

No FK constraints in MariaDB; joins are by string match.

Refresh Semantics

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

1. Iterates obj.tags[*] and emits one INSERT IGNORE per tag.
2. Issues DELETE FROM audits_tags WHERE auditid = <audit_id> (when delete tracking is enabled).

Inserts are INSERT IGNORE against the (auditid, tag) 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 tags on an audit

SELECT tag
FROM audits_tags
WHERE auditid = 'your_audit_id'
ORDER BY tag;

Audits with their tags (concatenated)

SELECT
  a.id,
  a.name,
  a.status,
  GROUP_CONCAT(at.tag SEPARATOR ', ') AS tags
FROM audits a
LEFT JOIN audits_tags at ON at.auditid = a.id
WHERE a.projectid = 'your_project_id'
GROUP BY a.id, a.name, a.status
ORDER BY a.creationdate DESC;

Filter audits by a single tag

SELECT a.id, a.name, a.status, a.dueDate
FROM audits a
JOIN audits_tags at ON at.auditid = a.id
WHERE at.tag       = 'QC Inspection'
  AND a.archived   IS NULL;

Tag frequency across a project

SELECT
  tag,
  COUNT(DISTINCT auditid) AS distinct_audits
FROM audits_tags
WHERE projectid = 'your_project_id'
GROUP BY tag
ORDER BY distinct_audits DESC;

Pitfalls

• Foreign-key column is auditid, not audit. Every other satellite in this domain uses audit (e.g. audits_questions.audit, audit_roles.audit). This table is unique in using auditid. Adjust your JOIN ... ON clauses accordingly.
• Tag text is free. Two audits' "QC Inspection" tag may be spelled differently — 'QC Inspection', 'Qc Inspection', 'qc inspection' — and will be three separate rows. Normalize at query time if you need cross-audit rollups.
• No primary key. Identity is (auditid, tag). INSERT IGNORE on the unique key collapses duplicate emissions within one observation.
• Reading during an in-flight reindex of an audit may briefly return zero rows for that audit.
• No FK constraint. Joins to audits.id are by string match. A tag's auditid may briefly point at an audits row that has been archived or deleted upstream.