Skip to main content

Contracts

What is a contract in EdControls?​

A contract in EdControls is the commercial agreement between EdControls and a customer organization that authorizes a set of projects to run. It is the layer above projects: the customer signs one contract, and one or more projects sit underneath it. Contracts carry licensing and pricing metadata (licensetype, priceplan) and the link to the upstream billing record (debtorid).

A contract does not itself carry tickets, audits, or maps — all entity-level work lives on a project. Contracts exist in this schema mostly so analytics can roll up activity from many projects back to a single commercial relationship.

The link between contracts and projects is many-to-many, modelled through the contracts_projects bridge table:

  • A contract typically spans multiple projects (a customer with many sites or jobs under one agreement).
  • A project can, in principle, sit under multiple contracts (rare, but the schema permits it — for example when a project is partly funded under two agreements).

Domain ER diagram​

The projects table is documented in the Projects domain. This subgraph just shows how contracts connect outward through the bridge.

Tables in this domain​

Lifecycle​

  • A contracts row is upserted when a contract document is observed in source data, and replaced on subsequent observations. The inserter does a DELETE FROM contracts WHERE id = <contract_id> followed by an INSERT.
  • contracts_projects is rebuilt per contract document. On each observation of a contract, every existing contracts_projects row for that contract is deleted, and one row is inserted per project in the source's projects[] array.
  • A reindex with type=contract (or a full reindex) drops and rebuilds both contracts and contracts_projects. There is no separate reindex target for the bridge.
  • Contracts have no archive column. Once a contract document stops being emitted by source, the row remains until the next reindex that drops the table.

Cross-table queries​

The following queries combine contracts with contracts_projects or projects. Per-table queries live on each leaf page.

Contracts and the projects under each​

SELECT
  c.id            AS contract_id,
  c.name          AS contract_name,
  c.licensetype,
  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, c.licensetype
ORDER BY projects_in_contract DESC, c.name;

Projects under multiple contracts​

The schema allows this even though it is rare in practice. This query surfaces any project that ended up linked to more than one contract — useful as a data-quality check.

SELECT
  cp.projectid,
  p.name        AS project_name,
  COUNT(*)      AS contract_count,
  GROUP_CONCAT(c.name ORDER BY c.name SEPARATOR ', ') AS contract_names
FROM contracts_projects cp
JOIN projects  p ON p.id  = cp.projectid
JOIN contracts c ON c.id  = cp.contractid
GROUP BY cp.projectid, p.name
HAVING contract_count > 1
ORDER BY contract_count DESC;

Contract portfolio by license type​

SELECT
  c.licensetype,
  COUNT(DISTINCT c.id)          AS contracts,
  COUNT(DISTINCT cp.projectid)  AS projects_covered
FROM contracts c
LEFT JOIN contracts_projects cp ON cp.contractid = c.id
GROUP BY c.licensetype
ORDER BY contracts DESC;

Contracts with no projects​

A contract that has no rows in contracts_projects either has not been used yet, or has had its projects removed upstream. Either way, no project-level activity will roll up to it.

SELECT
  c.id,
  c.name,
  c.licensetype,
  c.creationdate
FROM contracts c
LEFT JOIN contracts_projects cp ON cp.contractid = c.id
WHERE cp.contractid IS NULL
ORDER BY c.creationdate DESC;

Activity across a contract's projects​

The bridge gives a clean way to roll project-level entities up to the contract. Replace 'your_contract_id' to scope the query.

SELECT
  c.id          AS contract_id,
  c.name        AS contract_name,
  COUNT(DISTINCT t.id) AS active_tickets,
  COUNT(DISTINCT a.id) AS active_audits
FROM contracts c
JOIN contracts_projects cp ON cp.contractid = c.id
LEFT JOIN tickets t ON t.projectid = cp.projectid AND t.archived IS NULL
LEFT JOIN audits  a ON a.projectid = cp.projectid AND a.archived IS NULL
WHERE c.id = 'your_contract_id'
GROUP BY c.id, c.name;