Users

What is a user in EdControls?

A user in EdControls is a person who has been invited to one or more projects. Users are identified by their email address — that is the canonical identifier across the entire schema. Once a user appears in any project (as an author, a participant, a responsible party, an auditor, or a signee), a single row exists for them in users with their profile data: name, company, address, phone, language, privacy preference.

Users are global across the dataset: there is exactly one row per email, regardless of how many projects, contracts, or audits they touch. Their role in any specific project is captured in project_participants, audit_roles, or tickets_roles — never on the user row itself.

A user row is created the first time the user appears in source data and is updated whenever their profile changes upstream.

Domain ER diagram

The right-hand tables are documented in their own domains (Projects, Audits, Tickets) — this subgraph just shows how users connects out.

users.email is also referenced from author/responsible/lastmodifier fields on tickets, audits, and projects. Those references are not foreign-keyed in MariaDB (there is no FOREIGN KEY constraint), so joins are by string match on email.

Tables in this domain

• users — one row per person, keyed by email.

Lifecycle

• A users row is created on the first sync that observes the user.
• The row is updated when profile fields change upstream.
• Rows are not soft-deleted via an archived column; if a user disappears upstream, their row remains in the analytics database and the join targets (e.g. project_participants) keep referring to it.
• During reindex of the user resource, the table is dropped and rebuilt from source data — see Refresh Semantics on the leaf.

Cross-table queries

The following queries combine users with role/participation tables. They are placed here rather than on the leaf because they each touch multiple tables.

User engagement across the project

SELECT
  u.email,
  CONCAT(u.firstname, ' ', u.lastname) AS full_name,
  COUNT(DISTINCT pp.projectid) AS projects,
  COUNT(DISTINCT t.id)         AS tickets_authored,
  COUNT(DISTINCT a.id)         AS audits_authored
FROM users u
LEFT JOIN project_participants pp ON pp.email = u.email
LEFT JOIN tickets t              ON t.author = u.email
LEFT JOIN audits a               ON a.author = u.email
GROUP BY u.email
ORDER BY projects DESC, tickets_authored DESC;

Companies present in the dataset

SELECT
  extractedcompanyname AS company,
  COUNT(*)             AS users
FROM users
WHERE extractedcompanyname IS NOT NULL
GROUP BY extractedcompanyname
ORDER BY users DESC;

extractedcompanyname is normalized from the email domain when companyname is absent — see the leaf's Source Mapping section.