Audits

What is an audit in EdControls?

An audit in EdControls is a single execution of an audit template on a project — an inspection, form, checklist, or any structured question-and-answer pass over the work in progress. Audits are how EdControls captures evidence: the auditor walks the site (or reviews the work), answers a list of questions defined by a template, and optionally creates tickets as follow-ups for anything that needs attention.

An audit is a template instance. When a user starts an audit, EdControls duplicates the template's category and question structure into a concrete audits row that lives only on that project. From that point on, the audit and the template are independent: editing the template later does not change audits already in flight.

An audit carries a name, an audittype, a status, an author and a last modifier, a person responsible for completing it, due and completion dates, an optional template reference, and (optionally) a group. Around the audit row, eight satellite tables capture its lifecycle and content:

• audits_category and audits_questions — the category/question structure copied from the template.
• audits_answer — the answers the auditor recorded.
• audit_answers_tickets — bridge to any tickets created from a question's answer (this is the "audit finding becomes a follow-up ticket" link).
• audit_roles — the participants on the audit (responsible and informed).
• audit_signatures — sign-offs collected at completion.
• audits_tags — free-text tags applied to the audit.
• audits_maps — the maps the audit covers (documented under the Maps domain).

Domain ER diagram

The audits_maps bridge lives in the Maps domain; this subgraph just shows it for context. The tickets table is documented in the Tickets domain.

Tables in this domain

• audits — one row per audit instance.
• audits_category — categories copied from the template into this audit.
• audits_questions — questions copied from the template into this audit.
• audits_answer — the answers recorded for each question.
• audit_answers_tickets — bridge linking a question's answer to follow-up tickets.
• audit_roles — responsible and informed participants on the audit.
• audit_signatures — sign-offs collected at audit completion.
• audits_tags — free-text tags applied to an audit.

audits_maps is referenced from this domain but documented under Maps — no duplicate documentation here.

Status values

The audits.status column has three values:

• started — the audit has been assigned to a responsible person and is ready to begin (but no work has been recorded yet).
• In Progress — the audit is actively being conducted. Note the capital I and the space — the source value is preserved verbatim, so case-sensitive comparisons must use the exact string.
• completed — the audit is finished. completiondate is set, and sign-offs may have been collected in audit_signatures.

These values are stored verbatim from the source obj.status field without normalization. If you query WHERE status = 'in progress' (lowercase) you will get zero rows.

Lifecycle

• An audits row is upserted when an audit document is observed in source data: the converter does DELETE FROM audits WHERE id = <id> followed by an INSERT (only when the document is not marked deleted).
• An audit can be archived (audits.archived is set to a DATETIME). Archived audits remain in the table; downstream joins (e.g. from audit_answers_tickets) continue to resolve. Filter on archived IS NULL for active reporting.
• All eight satellite tables (audits_category, audits_questions, audits_answer, audit_answers_tickets, audit_roles, audit_signatures, audits_tags, audits_maps) are rebuilt per audit document: on each observation of an audit, every existing satellite row for that audit is deleted and the current set is re-inserted. Reading any satellite during an in-flight reindex of one audit may briefly return zero rows for that audit.
• A reindex with type=audit (or a full reindex) drops and rebuilds all eight tables in this domain (plus audits_maps). There is no per-satellite reindex target.

The questionID collision pitfall

This is the canonical footgun for joining across audits. Read it once here; it is repeated as a Pitfall on the three leaves where it bites (audits_questions, audits_answer, audit_answers_tickets).

questionID is not a unique identifier per audit. It is computed as a hash of the question's category and text:

categoryID = hash(categoryName)
questionID = hash(categoryID + ":" + question_text)

Two consequences:

1. Same template, same category, same question text → same questionID across every audit that uses that template. This is intentional — it makes "all answers to the same question across audits" a single GROUP BY.
2. Different audits (or even different templates) that happen to share an identical category-name and question-text pair → same questionID. Joining audits_questions ↔ audits_answer ↔ audit_answers_tickets on questionID alone collapses rows across unrelated audits.

Always also filter or join on audit or projectid when you join on questionID. Every audit-domain table that carries questionid (or questionID) also carries audit; use both.

Cross-table queries

The following queries combine audits with its satellites or with neighbouring domains. Per-table queries live on each leaf page.

Audit completion percentage

SELECT
  a.id,
  a.name,
  a.status,
  a.completiondate,
  COUNT(DISTINCT aq.questionid)                                          AS total_questions,
  COUNT(DISTINCT CASE WHEN aa.answer IS NOT NULL AND aa.answer <> ''
                      THEN aq.questionid END)                            AS answered_questions,
  ROUND(
    COUNT(DISTINCT CASE WHEN aa.answer IS NOT NULL AND aa.answer <> ''
                        THEN aq.questionid END)
    / NULLIF(COUNT(DISTINCT aq.questionid), 0) * 100,
    2)                                                                   AS completion_pct
FROM audits a
LEFT JOIN audits_questions aq
       ON aq.audit = a.id
LEFT JOIN audits_answer aa
       ON aa.audit = a.id
      AND aa.questionid = aq.questionid
WHERE a.projectid = 'your_project_id'
GROUP BY a.id, a.name, a.status, a.completiondate
ORDER BY completion_pct DESC;

Note the join on aa.audit = a.id AND aa.questionid = aq.questionid — joining on questionid alone would conflate answers across audits (see the questionID collision pitfall above).

Audit performance metrics by audit type

SELECT
  audittype,
  COUNT(*)                                                       AS total_audits,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)          AS completed,
  AVG(CASE WHEN completiondate IS NOT NULL
           THEN DATEDIFF(completiondate, creationdate) END)      AS avg_completion_days,
  AVG(dueDate_completed) / 86400                                 AS avg_days_vs_due_date
FROM audits
WHERE projectid = 'your_project_id'
  AND archived IS NULL
GROUP BY audittype
ORDER BY total_audits DESC;

dueDate_completed is stored in seconds (positive = completed before due, negative = completed after due). Divide by 86400 for days.

Monthly audit activity

SELECT
  YEAR(creationdate)  AS year,
  MONTH(creationdate) AS month,
  COUNT(*)                                                       AS audits_created,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)          AS audits_completed,
  AVG(CASE WHEN completiondate IS NOT NULL
           THEN DATEDIFF(completiondate, creationdate) END)      AS avg_completion_time
FROM audits
WHERE projectid = 'your_project_id'
GROUP BY YEAR(creationdate), MONTH(creationdate)
ORDER BY year, month;

Auditor performance

SELECT
  author                                                         AS auditor,
  COUNT(*)                                                       AS audits_conducted,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)          AS completed_audits,
  AVG(CASE WHEN completiondate IS NOT NULL
           THEN DATEDIFF(completiondate, creationdate) END)      AS avg_completion_days
FROM audits
WHERE projectid = 'your_project_id'
  AND archived IS NULL
GROUP BY author
ORDER BY audits_conducted DESC;

Audit timeline (planned vs. actual)

SELECT
  id,
  name,
  creationdate,
  dueDate,
  completiondate,
  DATEDIFF(dueDate, creationdate)  AS planned_duration,
  CASE
    WHEN completiondate IS NOT NULL THEN DATEDIFF(completiondate, creationdate)
    ELSE                                  DATEDIFF(NOW(),         creationdate)
  END                              AS actual_duration,
  dueDate_completed / 86400        AS days_vs_due_date
FROM audits
WHERE projectid = 'your_project_id'
ORDER BY creationdate;

Tickets created from audit findings

audit_answers_tickets links a ticket back to the audit answer that produced it. Use it to trace "this ticket exists because of that finding".

SELECT
  a.id     AS audit_id,
  a.name   AS audit_name,
  aq.question,
  aa.answer,
  t.id     AS ticket_id,
  t.title  AS ticket_title,
  t.status AS ticket_status
FROM audit_answers_tickets aat
JOIN audits           a  ON a.id  = aat.audit
JOIN tickets          t  ON t.id  = aat.ticket
LEFT JOIN audits_questions aq
       ON aq.audit      = aat.audit
      AND aq.questionid = aat.questionID
LEFT JOIN audits_answer    aa
       ON aa.audit      = aat.audit
      AND aa.questionid = aat.questionID
WHERE a.projectid = 'your_project_id'
ORDER BY a.creationdate DESC, t.creationdate DESC;

The two LEFT JOINs on (audit, questionid) are deliberate — joining on questionID alone would pull in unrelated audits' rows.

Template usage analysis

The template-usage cross-table query lives on the Audit Templates domain landing.