project_participants

One row per (project, email, role) triple — a user's involvement on a project under a specific RACI-style role. A user assigned to one project under multiple roles produces multiple rows.

Source Mapping

CouchDB document type: a "project" document — participants are extracted from the project's participants.{accountable, support, consulted, informed} arrays (see asyncconvert.py's project_participants(obj) handler).

Column	CouchDB source	Notes
email	participants.<role>.email	The user's email; canonical identifier across the schema.
role	computed (literal)	One of 'accountable', 'support', 'consulted', 'informed'. Hard-coded per inserter branch — see Pitfalls.
projectid	id	The project this row belongs to (the source-document id is the project ID).

The accountable role is taken from the singleton participants.accountable.email. The support, consulted, and informed roles are each taken by iterating the corresponding array and inserting one row per email.

Column Reference

Column	Type	Nullable	Description
email	VARCHAR(255)	yes	The participant's email address.
role	VARCHAR(255)	yes	One of 'accountable', 'support', 'consulted', 'informed'.
projectid	VARCHAR(255)	yes	The project this participation row belongs to.

Keys

• Primary key: none
• Unique key uk_project_email_role: (projectid, email, role) — a user can hold multiple roles on a project, but each (project, email, role) triple is unique.

Indexes

• idx_project on projectid

Relationships

Direction	Other table	Join	Cardinality
outgoing	projects	project_participants.projectid = projects.id	many participants → one project
outgoing	users	project_participants.email = users.email	many participation rows → one user

No FK constraints in MariaDB; joins are by string match.

Refresh Semantics

project_participants is rebuilt per project document. On every observation of a project, every existing project_participants row for that project is deleted and the current set is re-inserted from the source's participants.{accountable, support, consulted, informed} arrays.

The accountable insert is unconditional (one row per project even when the email is missing); the other three roles iterate their respective arrays and skip when empty. Inserts use INSERT IGNORE, so the unique key absorbs duplicates within a single document.

A reindex with type=project (or a full reindex) drops and rebuilds the table.

Common Queries

Participants on a project, by role

SELECT
  role,
  COUNT(*) AS participants,
  GROUP_CONCAT(email ORDER BY email SEPARATOR ', ') AS emails
FROM project_participants
WHERE projectid = 'your_project_id'
GROUP BY role
ORDER BY FIELD(role, 'accountable', 'support', 'consulted', 'informed');

Find every project a user is involved in

SELECT projectid, role
FROM project_participants
WHERE email = 'user@example.com'
ORDER BY projectid, role;

Users with multiple roles on the same project

SELECT projectid, email, COUNT(*) AS roles
FROM project_participants
GROUP BY projectid, email
HAVING roles > 1
ORDER BY roles DESC;

Cross-table queries that join participants with project metadata live on the Projects domain landing.

Pitfalls

• No primary key. Identity is the composite unique key (projectid, email, role). There is no surrogate id. Joins from this table need both projectid and email (and usually role) to be deterministic.
• role is a closed enum, hard-coded. The inserter writes literal strings — 'accountable', 'support', 'consulted', 'informed'. Other RACI-like roles in the wider product (e.g. responsible) do not appear here; responsible lives on the audits.responsible and tickets.responsible columns directly.
• accountable is always inserted. Even when the source's participants.accountable.email is missing, an accountable row is produced (with whatever empty / null value the deep_get extracts). Filter accordingly when counting "real" participants.
• No FK constraints. email and projectid are joined by string match. If a user disappears upstream without the project being re-synced, the row remains.
• Rebuilt on every observation. Reading this table during an in-flight reindex of a project may briefly return zero rows for that project between the DELETE and the INSERTs.