template_groups

One row per template group — a folder that organizes related audit templates within a project (e.g. "Safety", "Quality", "Handover").

Source Mapping

CouchDB document type: a "templategroup" document (see asyncconvert.py's template_groups(obj) handler).

Column	CouchDB source	Notes
id	id	Group identifier; project-scoped.
name	name	Human-readable group name.
projectid	database	The project this group belongs to.
couchdbid	couchDbId	Legacy CouchDB document identifier.
creationdate	dates.creationDate	Parsed via parseDate(...).
lastmodifieddate	dates.lastModifiedDate	Parsed via parseDate(...).

Column Reference

Column	Type	Nullable	Description
id	VARCHAR(255)	no	Primary key. Group identifier; project-scoped.
name	TEXT	yes	Human-readable group name (free text).
projectid	VARCHAR(255)	yes	The project this group belongs to.
couchdbid	VARCHAR(255)	yes	Legacy CouchDB identifier.
creationdate	DATETIME	yes	When the group was created upstream.
lastmodifieddate	DATETIME	yes	When the upstream group was last edited.

Keys

• Primary key: id

Indexes

• idx_projectid on projectid
• idx_couchdbid on couchdbid
• idx_id on id

The PK already implies an index on id; the explicit idx_id is redundant but harmless.

Relationships

Direction	Other table	Join	Cardinality
outgoing	projects	template_groups.projectid = projects.id	many groups → one project
incoming	audittemplates	audittemplates.groupid = template_groups.id	one group → many templates

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

Refresh Semantics

A row is upserted when its template-group document is observed in source, and replaced on subsequent observations. There is no archived column — if a group is removed upstream, its row is dropped on the next reindex, but until then any audittemplates.groupid referencing it continues to resolve.

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

Common Queries

List groups on a project

SELECT id, name, creationdate
FROM template_groups
WHERE projectid = 'your_project_id'
ORDER BY name;

Groups with their template counts

SELECT
  tg.id,
  tg.name,
  COUNT(at.id) AS template_count
FROM template_groups tg
LEFT JOIN audittemplates at
  ON at.groupid = tg.id
 AND at.archived IS NULL
WHERE tg.projectid = 'your_project_id'
GROUP BY tg.id, tg.name
ORDER BY template_count DESC, tg.name;

Empty groups

SELECT tg.id, tg.name, tg.projectid
FROM template_groups tg
LEFT JOIN audittemplates at ON at.groupid = tg.id
WHERE at.id IS NULL
ORDER BY tg.projectid, tg.name;

Cross-table queries (templates grouped by their group, etc.) live on the Audit Templates domain landing.

Pitfalls

• id is project-scoped, not global. Two projects each with a "Safety" group are two separate rows with different id values. Group by name for cross-project rollups, with the same caveats as for audittemplates.name.
• No FK constraint to audittemplates. A row can be left here with no templates referencing it (an empty group), and conversely a template's groupid can outlive its group row briefly across reindex windows.
• Denormalized groupname lives on audittemplates. When the group is renamed, audittemplates.groupname may lag until each template is re-synced. The authoritative value is here.