Users and Organization

Views for user records and organizational labels used for filtering and grouping across all modules.

These views provide user information and organizational labels. The users view is referenced by virtually every other view through created_by, updated_by, and assignee fields. The users_labels view enables filtering and grouping by team, department, site, or any custom organizational category.

---

reporting.users

All users in the system, including active status and employment metadata.

Field	Type	Description
user_id	uuid	Unique identifier of the user
first_name	text	First name
last_name	text	Last name
email	text	Email address
created_at	timestamp	When the user record was created
created_by	uuid	User who created the record
updated_at	timestamp	Last modification timestamp
updated_by	uuid	User who last modified the record
reporting_to	uuid	Manager's user ID
date_of_hire	date	Hire date
is_active	boolean	Computed active status (considers deleted_at, is_active_until, and is_active flag)
is_active_from	timestamp	Start of active period
is_active_until	timestamp	End of active period
deleted_at	timestamp	Soft delete timestamp

The is_active field is computed: a user is inactive if they are soft-deleted, past their is_active_until date, or explicitly marked inactive. Use this field rather than checking deleted_at directly.

Joins: Self-join on reporting_to = user_id for manager lookups. Join with users_labels on user_id for organizational grouping.

---

reporting.users_labels

Labels applied to users, used to group by team, site, department, role, or any custom category.

Field	Type	Description
user_id	uuid	The user being labeled
label_category	text	Type of label (e.g., department, site)
label_value	text	Label value

Joins: Join with users on user_id. Use this view for building permission-scoped or team-scoped dashboards.

---

Example queries

Active users with their department and site

SELECT
    u.first_name || ' ' || u.last_name AS name,
    u.email,
    MAX(CASE WHEN ul.label_category = 'Department' THEN ul.label_value END) AS department,
    MAX(CASE WHEN ul.label_category = 'Site' THEN ul.label_value END) AS site
FROM reporting.users u
LEFT JOIN reporting.users_labels ul USING (user_id)
WHERE u.is_active = true
GROUP BY u.user_id, u.first_name, u.last_name, u.email
ORDER BY u.last_name;

Headcount by department

SELECT
    ul.label_value AS department,
    COUNT(DISTINCT ul.user_id) AS headcount
FROM reporting.users_labels ul
JOIN reporting.users u USING (user_id)
WHERE ul.label_category = 'Department'
    AND u.is_active = true
GROUP BY ul.label_value
ORDER BY headcount DESC;