Skip to main content

Tickets

What is a ticket in EdControls?​

A ticket in EdControls is a single unit of work that needs attention on a project — an issue, a snag, a punch-list item, a deficiency, or any other actionable observation. Tickets are the operational currency of the platform: they are the things that get created, assigned, worked on, completed, and reported against.

A ticket carries a title and body, an author, a person responsible for resolving it, a status, a due date, and (optionally) a position on a project map (x / y pixel coordinates and / or a latitude / longitude geo coordinate). Tickets can also carry tags, progress labels (a finer-grained sub-status), and custom fields — an EdControls feature that lets each project define its own structured metadata on top of the built-in ticket schema.

A ticket lives on a single project. Many tickets are created inside an audit as a follow-up to a question's answer; that linkage is captured in the audit_answers_tickets bridge (documented under the Audits domain) and exposed here as the "tickets created from audit findings" cross-table query.

Domain ER diagram​

The audit_answers_tickets bridge is documented in the Audits domain; it links a ticket back to the audit answer that created it (when applicable). This subgraph just shows how tickets connect outward.

Tables in this domain​

Status and progress labels​

The built-in tickets.status column has three closed values:

  • created — the ticket exists but has no responsible person.
  • started — a responsible person is assigned (tickets.responsible is non-empty) and work is in progress.
  • completed — the ticket has been finished. dateCompleted is set.

Beyond status, projects can use progress labels to track finer-grained workflow states ("waiting on parts", "ready for QA", etc.) without changing the closed status enum. Progress labels are free-text strings stored one row per (ticket, label) in tickets_progress_labels. A ticket can carry zero, one, or multiple labels.

Custom fields​

EdControls projects can extend the ticket schema with custom fields, defined per project. Each custom field has a field_type drawn from a closed set:

  • freetext — a free-text string.
  • numeric — a number, stored as a string.
  • date — an ISO date string.
  • singleSelectMultipleChoice — pick one of N predefined options.
  • multiSelectMultipleChoice — pick zero or more of N options.

Three tables back this:

  • tickets_custom_fields — one row per (ticket, field). For simple types, field_value carries the value directly. For choice types, field_value carries a JSON-stringified array of selected option IDs.
  • tickets_custom_field_options — for choice fields only, the option catalog as it existed for that ticket (text, type, optional image).
  • tickets_custom_field_selections — for choice fields only, one row per (ticket, field, selected option) — the normalized form of the same selection that field_value carries as a JSON array.

The three custom-field tables are auto-created on older datasets via the backfill path in backend/services/sql.py (migrate_tickets_custom_fields_tables). If you encounter a dataset where the custom-field tables exist but are empty, the dataset has been migrated but no ticket has yet been re-synced with custom-field data. See the leaf pages for details.

Lifecycle​

  • A tickets row is upserted when a ticket document is observed in source data: the converter does DELETE FROM tickets WHERE id = <id> followed by an INSERT.
  • A ticket can be archived (tickets.archived is set to a DATETIME). Archived tickets remain in the table; downstream joins (e.g. from audit_answers_tickets) continue to resolve.
  • All five satellite tables (tickets_roles, tickets_tags, tickets_progress_labels, tickets_custom_fields, tickets_custom_field_options, tickets_custom_field_selections) are rebuilt per ticket document: on each observation of a ticket, every existing satellite row for that ticket is deleted and the current set is re-inserted. Reading any satellite during an in-flight reindex of one ticket may briefly return zero rows for that ticket.
  • A reindex with type=ticket (or a full reindex) drops and rebuilds all seven tables in this domain. There is no per-satellite reindex target.

Cross-table queries​

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

Ticket burndown by status​

SELECT
  status,
  COUNT(*)                                       AS tickets,
  SUM(CASE WHEN archived IS NULL THEN 1 ELSE 0 END) AS active,
  AVG(created_completed) / 86400                 AS avg_days_to_complete
FROM tickets
WHERE projectid = 'your_project_id'
GROUP BY status
ORDER BY tickets DESC;

Tickets with their tags and progress labels​

SELECT
  t.id,
  t.title,
  t.status,
  GROUP_CONCAT(DISTINCT tt.tag SEPARATOR ', ')              AS tags,
  GROUP_CONCAT(DISTINCT tpl.progress_label SEPARATOR ', ')  AS progress_labels
FROM tickets t
LEFT JOIN tickets_tags             tt  ON tt.ticketid  = t.id
LEFT JOIN tickets_progress_labels  tpl ON tpl.ticketid = t.id
WHERE t.projectid = 'your_project_id'
  AND t.archived IS NULL
GROUP BY t.id, t.title, t.status
ORDER BY t.lastmodifieddate DESC;

Tickets with custom-field values​

SELECT
  t.id                AS ticket_id,
  t.title,
  cf.field_label,
  cf.field_type,
  cf.field_value
FROM tickets t
JOIN tickets_custom_fields cf ON cf.ticketid = t.id
WHERE t.projectid = 'your_project_id'
  AND t.archived  IS NULL
ORDER BY t.id, cf.field_label;

Tickets with their selected choice options resolved to text​

SELECT
  t.id            AS ticket_id,
  t.title,
  cf.field_label,
  GROUP_CONCAT(co.option_text SEPARATOR ', ') AS selected_text
FROM tickets t
JOIN tickets_custom_fields            cf ON cf.ticketid = t.id
JOIN tickets_custom_field_selections  cs ON cs.ticketid = t.id AND cs.field_id = cf.field_id
JOIN tickets_custom_field_options     co ON co.ticketid = t.id
                                         AND co.field_id = cf.field_id
                                         AND co.option_id = cs.selected_option_id
WHERE cf.field_type IN ('singleSelectMultipleChoice', 'multiSelectMultipleChoice')
  AND t.projectid = 'your_project_id'
GROUP BY t.id, t.title, cf.field_label;

Tickets by progress label​

SELECT
  tpl.progress_label,
  COUNT(DISTINCT t.id) AS tickets
FROM tickets t
JOIN tickets_progress_labels tpl ON tpl.ticketid = t.id
WHERE t.projectid = 'your_project_id'
  AND t.archived IS NULL
GROUP BY tpl.progress_label
ORDER BY tickets DESC;

Tickets created from audit findings​

audit_answers_tickets (documented in the Audits domain) links a ticket back to the audit answer that produced it.

SELECT
  a.id    AS audit_id,
  a.name  AS audit_name,
  t.id    AS ticket_id,
  t.title AS ticket_title,
  t.status
FROM audit_answers_tickets aat
JOIN audits  a ON a.id = aat.audit
JOIN tickets t ON t.id = aat.ticket
WHERE t.projectid = 'your_project_id'
ORDER BY a.creationdate DESC, t.creationdate DESC;

User workload across active tickets​

SELECT
  t.responsible              AS owner,
  COUNT(*)                   AS assigned_tickets,
  SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) AS completed,
  AVG(CASE WHEN t.status = 'completed' THEN t.created_completed END) / 86400
                             AS avg_days_to_complete
FROM tickets t
WHERE t.projectid = 'your_project_id'
  AND t.archived IS NULL
  AND t.responsible IS NOT NULL
GROUP BY t.responsible
ORDER BY assigned_tickets DESC;