Skip to main content

Audit Templates

What is an audit template in EdControls?​

An audit template in EdControls is the reusable blueprint that defines what an audit looks like: its question structure, categories, and signoff requirements. When a user starts an audit on a project, EdControls instantiates the template — copying its structure into a concrete audits row that lives only on that project.

Templates can be draft while being authored or published once they are ready to use. Only published templates can be instantiated into audits. Templates are organized into template groups (template_groups) — for example, a "Safety" group containing templates for fire, electrical, and fall-hazard inspections.

A template lives on a single project. Two physically identical templates on two different projects are two separate rows with two separate id values; see the cross-project caveat below.

Domain ER diagram​

The audits table is documented in the Audits domain. This subgraph just shows how templates feed into audit instances.

Tables in this domain​

Templates and audits​

Audits are essentially copies of templates. When an auditor starts an audit, EdControls duplicates the template's question and category structure into the new audit. After that point, the audit and the template are independent: editing the template later does not change audits already in flight.

Common audit template types in the wild (organization-specific, not fixed by the schema) include:

  • "Quality Inspection" — quality control processes
  • "Delivery" — delivery and logistics audits
  • "Safety Issue" — safety incident investigations

Grouping audits by template name is a meaningful way to compare processes across a project — see the cross-table queries below.

Cross-project caveat​

Audit templates have unique IDs per project, not globally.

If two projects each have a "Quality Inspection" template, those are two separate audittemplates rows with two separate id values. Joining on audittemplates.id across projects therefore does not give you "all uses of the Quality Inspection template across the company" — it gives you uses within one project at a time.

For a cross-project view, group by audittemplates.name (or, for a more rigorous match, by name plus question/category structure). Be aware that template names are free text and may differ in spelling, casing, or whitespace between projects.

Lifecycle​

  • A template row is created when a user authors a new template (status = 'draft') or imports one from elsewhere.
  • Publishing a template flips status from 'draft' to 'published'.
  • Only published templates can be instantiated into audits.
  • A template can be archived (archived is set to a DATETIME). Archived templates remain in the table; existing audits that referenced them keep working.
  • A reindex with type=audittemplate (or a full reindex) drops and rebuilds both audittemplates and template_groups from source.

Cross-table queries​

The following queries combine audittemplates with audits or template_groups. Per-table queries live on each leaf page.

Template usage within a project​

SELECT
  at.name              AS template_name,
  COUNT(a.id)          AS times_used,
  AVG(CASE WHEN a.completiondate IS NOT NULL
           THEN DATEDIFF(a.completiondate, a.creationdate) END) AS avg_completion_days
FROM audits a
JOIN audittemplates at ON a.template = at.id
WHERE a.projectid = 'your_project_id'
GROUP BY a.template, at.name
ORDER BY times_used DESC;

Templates grouped by their template group​

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

Cross-project template usage (by name)​

SELECT
  at.name              AS template_name,
  COUNT(DISTINCT at.projectid) AS projects,
  COUNT(a.id)          AS times_used
FROM audittemplates at
JOIN audits a ON a.template = at.id
WHERE at.status = 'published'
GROUP BY at.name
ORDER BY projects DESC, times_used DESC;

This query intentionally groups by name rather than id to work across projects — see the cross-project caveat above.