audittemplates

One row per audit template — the reusable blueprint that audits are instantiated from. Identified by id; lives on a single project.

Source Mapping

CouchDB document type: an "audittemplate" document (see asyncconvert.py's audittemplates(obj) handler).

Column	CouchDB source	Notes
id	id	Template identifier; unique within the dataset across projects (project-scoped semantics — see Pitfalls).
name	name	Human-readable template name. Free text.
groupid	groupId	The template_groups.id this template belongs to. May be empty when the template is ungrouped.
groupname	group	Denormalized group name from source — convenient but can drift from template_groups.name.
author	author.email	Email of the user who authored the template.
lastmodifier	lastmodifier.email	Email of the user who last edited the template.
creationdate	dates.creationDate	Parsed via parseDate(...).
lastmodifieddate	dates.lastModifiedDate	Parsed via parseDate(...).
archived	archived	Parsed via parseDate(...). NULL for active templates.
status	computed (derived)	'published' when isPublished in source is truthy, otherwise 'draft'. The default in the schema is 'draft'.
projectid	database	The project this template belongs to.
couchdbid	couchDbId	Legacy CouchDB document identifier.

Column Reference

Column	Type	Nullable	Description
archived	DATETIME	yes	Archive timestamp; NULL for active templates.
author	VARCHAR(255)	yes	Email of the user who authored the template.
creationdate	DATETIME	yes	When the template was created upstream.
lastmodifieddate	DATETIME	yes	When the upstream template was last edited.
groupid	VARCHAR(255)	yes	Foreign-key reference to template_groups.id.
groupname	VARCHAR(255)	yes	Denormalized group name; convenience copy of template_groups.name from source.
name	VARCHAR(255)	yes	Human-readable template name. Free text — may vary in spelling across projects.
lastmodifier	VARCHAR(255)	yes	Email of the user who last edited the template.
status	VARCHAR(20)	yes	Lifecycle state: 'draft' or 'published'. Defaults to 'draft'.
id	VARCHAR(255)	no	Primary key. Template identifier; project-scoped (see Pitfalls).
projectid	VARCHAR(255)	yes	The project this template belongs to.
couchdbid	VARCHAR(255)	yes	Legacy CouchDB identifier.

Keys

• Primary key: id

Indexes

• idx_project on projectid
• idx_status on status

Relationships

Direction	Other table	Join	Cardinality
outgoing	template_groups	audittemplates.groupid = template_groups.id	many templates → one group
outgoing	projects	audittemplates.projectid = projects.id	many templates → one project
outgoing	users (author)	audittemplates.author = users.email	many templates → one author
outgoing	users (lastmodifier)	audittemplates.lastmodifier = users.email	many templates → one user
incoming	audits	audits.template = audittemplates.id	one template → many audit instances

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

Refresh Semantics

A row is upserted when its template document is observed in source data, and replaced on subsequent observations. Archive is a soft state: the archived DATETIME is set when the template is archived upstream, but the row is not removed and existing audits that reference it continue to resolve.

A reindex with type=audittemplate (or a full reindex) drops the audittemplates table and rebuilds it from source. See backend/services/sql.py for the drop and setup paths.

Common Queries

List published templates on a project

SELECT id, name, groupname, creationdate
FROM audittemplates
WHERE projectid = 'your_project_id'
  AND status = 'published'
  AND archived IS NULL
ORDER BY name;

Find draft templates that have not been touched recently

SELECT id, name, author, lastmodifieddate
FROM audittemplates
WHERE status = 'draft'
  AND archived IS NULL
  AND (lastmodifieddate IS NULL OR lastmodifieddate < (NOW() - INTERVAL 60 DAY))
ORDER BY lastmodifieddate;

Templates by author

SELECT
  author,
  COUNT(*)                                            AS templates,
  SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published
FROM audittemplates
WHERE archived IS NULL
GROUP BY author
ORDER BY templates DESC;

Cross-table queries (template usage by audits, templates by group) live on the Audit Templates domain landing.

Pitfalls

• id is project-scoped, not global. Two projects each with a "Quality Inspection" template produce two audittemplates rows with two different id values. Joining on id does not give you cross-project usage — group by name for that. See the domain landing.
• status is a string, not a boolean. Values are 'draft' or 'published' (and the schema default is 'draft'). Compare with string equality, not boolean operators. Source's isPublished is flattened into this string before insert.
• groupname can drift from template_groups.name. It is a denormalized copy from source. If the group is renamed, the authoritative value is template_groups.name; groupname may lag until the template document is re-synced.
• No FK constraints. groupid, projectid, author, and lastmodifier are joined by string match. If an upstream group is deleted without templates being re-synced, groupid may point to a missing template_groups row.