Skip to main content

Projects

What is a project in EdControls?​

A project in EdControls is the top-level container for work on a single site or job — typically a building, a renovation, a delivery, or any other physical scope of work. It is the anchor that ties together every other entity in the system: tickets, audits, audit templates, maps, and the people involved.

A project has a name, a location (free-text plus optional latitude / longitude), a planned start and end date, an accountable owner, and a set of participants in supporting roles. Projects belong to one or more contracts (commercial agreements between EdControls and a customer organization) — the link is many-to-many through contracts_projects.

Every other table in the schema carries a projectid pointing back here. projectid is the most common join key in the entire schema, and most analytics queries should filter on it early — both because of the indexes on projectid across nearly every table, and because the analytics database is per-dataset (each dataset is its own MariaDB database), but a single dataset can contain many projects.

Domain ER diagram​

The neighbouring tables (contracts, tickets, audits, audittemplates, maps, users) are documented in their own domains. This subgraph just shows how projects connects out — the hub of the schema.

Tables in this domain​

The projectid join rule​

Almost every join in the schema starts with projectid. This is true for both performance and correctness reasons:

  • Performance: every table with a projectid column has an index on it (idx_project or idx_projectid). Filter on projectid early — ideally in the outer WHERE clause of any analytics query — so the optimizer prunes early.
  • Correctness: several entities use composite identity rather than a globally-unique id. Audit templates, maps, and map groups carry IDs that are project-scoped, not global. A handful of bridge tables (audit_answers_tickets, etc.) carry a composite projectid + couchdbid rule for cross-table joins. When in doubt, include projectid on both sides of a join.

A single dataset corresponds to one MariaDB database that holds many projects; there is no "all projects across all customers" query — cross-customer rollups belong upstream of this database.

Lifecycle​

  • A projects row is upserted when a project document is observed in source data, and replaced on subsequent observations.
  • A project can be archived (projects.archived is set to a DATETIME). Archived projects remain in the table; tickets, audits, and other entities that reference them continue to resolve.
  • project_participants is rebuilt per project document: on each observation of a project, every existing project_participants row for that project is deleted and the current set is re-inserted from the project's participants.{accountable, support, consulted, informed} arrays.
  • A reindex with type=project (or a full reindex) drops and rebuilds both projects and project_participants.

Cross-table queries​

The following queries combine projects with project_participants, contracts_projects, or downstream entities. Per-table queries live on each leaf page.

Project participants with their roles​

SELECT
  p.name        AS project_name,
  pp.email,
  pp.role
FROM projects p
JOIN project_participants pp ON pp.projectid = p.id
WHERE p.id = 'your_project_id'
ORDER BY pp.role, pp.email;

All entities for a project (counts)​

SELECT
  p.id,
  p.name,
  (SELECT COUNT(*) FROM tickets   t WHERE t.projectid = p.id AND t.archived IS NULL) AS active_tickets,
  (SELECT COUNT(*) FROM audits    a WHERE a.projectid = p.id AND a.archived IS NULL) AS active_audits,
  (SELECT COUNT(*) FROM maps      m WHERE m.projectid = p.id AND m.archived IS NULL) AS active_maps,
  (SELECT COUNT(DISTINCT cp.contractid)
     FROM contracts_projects cp WHERE cp.projectid = p.id)                            AS contracts
FROM projects p
WHERE p.id = 'your_project_id';

Projects under each contract​

SELECT
  c.id            AS contract_id,
  c.name          AS contract_name,
  COUNT(cp.projectid) AS projects_in_contract
FROM contracts c
LEFT JOIN contracts_projects cp ON cp.contractid = c.id
GROUP BY c.id, c.name
ORDER BY projects_in_contract DESC, c.name;

Project ownership across the dataset​

SELECT
  p.accountable AS owner,
  COUNT(*)      AS projects_owned,
  SUM(CASE WHEN p.archived IS NULL THEN 1 ELSE 0 END) AS active
FROM projects p
GROUP BY p.accountable
ORDER BY projects_owned DESC;