Add the workforce hierarchy store, read API and development seed
What does this MR do and why?
Adds the Phase 1 org hierarchy store for Organization Hierarchy Sync. It lets GitLab hold one tenant's current people and reporting structure. It can answer who reports into a manager, which GitLab user each person is, and which person a user is.
Every later Phase 1 issue (sync, defects, scheduling, deletion) builds on this store.
What is in this MR:
- A new
Workforcebounded context. It is vendor-neutral and nothing in it names Workday. - Table
workforce_hierarchies, one per tenant. The tenant is the root group on GitLab.com, and the organization on Self-Managed and Dedicated. - Table
workforce_peoplewithexternal_xid,full_name,manager_external_xid,user_id, thetraversal_idsancestry path, and the tenant key. It holds no email or other HR attribute. - Sharding key
group_idororganization_id, enforced byCHECK (num_nonnulls(group_id, organization_id) = 1). Thegitlab_schemaisgitlab_main_org. - Indexes on
workforce_people:- unique
(workforce_hierarchy_id, external_xid) - partial unique
(workforce_hierarchy_id, user_id) WHERE user_id IS NOT NULL (workforce_hierarchy_id, manager_external_xid)- btree
(workforce_hierarchy_id, traversal_ids)for the subtree range scan, the same technique asindex_namespaces_on_traversal_ids_for_groups_btree
- unique
- Models
Workforce::HierarchyandWorkforce::Personwith the scopesin_hierarchy,subtree_of,direct_reports_of,matched,unmatchedandfor_user. ActiveRecord is the only read path in Phase 1. Every read scope takes the hierarchy, or a person in it, and adds the tenant filter itself. A hierarchy, and each person in it, rejects a subgroup as its tenant. - A development seed,
ee/db/fixtures/development/99_workforce_hierarchy.rb. It builds 10,000 people by default, up to 50,000, and 100 when CI is set. - An env-gated benchmark spec,
ee/spec/models/workforce/person_subtree_benchmark_spec.rb, which runs only withBENCHMARK=1.
Out of scope, as stated in the issue: the sync, the defects table, the Workday integration, and any UI.
Decisions reviewers should know
-
The table is
workforce_people(modelWorkforce::Person), notworkforce_hierarchy_recordsas in the issue's draft SQL.Why
The name matches the domain language: a person in a hierarchy. It also matches the model name. -
workforce_hierarchies.integration_idis nullable for now.Why
The Workday integration lands in https://gitlab.com/gitlab-org/gitlab/-/work_items/629838, which will make the column NOT NULL. One hierarchy per tenant is already enforced by unique indexes on
group_idandorganization_id. -
The destroy services clean up the workforce rows and declare it with
handles_removal_of, so nothing is added tospec/support/database/destroy_service_foreign_keys_todo.yml.Why
A root group or organization holds up to 50,000
workforce_peoplerows (the largest supported tenant), too many for one cascading delete.Groups::DestroyServiceandOrganizations::HardDeleteServicecallWorkforce::Hierarchy.delete_for_tenantjust before the group or organization is deleted. It deletes the people in batches of 1,000, then the hierarchy. The call goes through an empty CE hook (before_group_destroy,before_organization_destroy), so it runs inside the services' existing failure handling.Deleting a user leaves the person unmatched: the ghost user migration clears
user_idthroughEE::User#workforce_people(dependent: :nullify). The foreign keyON DELETEactions stay as a backstop. -
config/organizations/transfer_support.ymllinks both tables to the follow-up https://gitlab.com/gitlab-org/gitlab/-/work_items/634509, as Tenant Scale asked.Workforce::Personis inskipped_transfer_modelsofOrganizations::Transfer::UsersService.Why
A person belongs to the tenant that imported the hierarchy, not to the GitLab user it is matched to. Moving a user between organizations must not move the row. -
The read API scopes
subtree_ofanddirect_reports_ofare listed inscripts/lint/keela_excluded.yml. They have no callers in application code until the follow-up issues land. Removing the entries is tracked in https://gitlab.com/groups/gitlab-org/-/work_items/23251#note_4055742904. -
The benchmark is skipped unless
BENCHMARK=1is set, so CI never runs the timing assertion.Why
A wall-clock assertion on a shared runner would be flaky. The spec lives under
ee/spec/models/workforce/because the spec tooling (Quality::TestLevel) does not recogniseee/spec/benchmarks. A name that does not end in_spec.rbfails the RSpec RuboCop cops. -
Tenant Scale (rutgerwessels) and database review (bauerdominic, abdwdd) have approved the
group_id/organization_idsharding key. This is an acceptance criterion of the issue.
References
- Issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/629837
- Epic, Phase 1: https://gitlab.com/groups/gitlab-org/-/work_items/23625
- Parent initiative: https://gitlab.com/groups/gitlab-org/-/work_items/23251
- Blocked by this MR, sync: https://gitlab.com/gitlab-org/gitlab/-/work_items/629839
- Blocked by this MR, data removal: https://gitlab.com/gitlab-org/gitlab/-/work_items/629842
- Workday integration: https://gitlab.com/gitlab-org/gitlab/-/work_items/629838
Screenshots or screen recordings
This is not a UI change, so there are no before and after screenshots. See the "Evidence for the acceptance criteria" section below instead.
How to set up and validate locally
-
Seed a 50,000-person hierarchy. It attaches to the first root group, or set
GROUP_ID=<root group id>.FILTER=99_workforce_hierarchy COUNT=50000 bundle exec rake db:seed_fu -
In the Rails console, run the reads:
hierarchy = Workforce::Hierarchy.last root = hierarchy.people.find_by(manager_external_xid: nil) Workforce::Person.subtree_of(root).count # => 50000 manager = hierarchy.people.where('array_length(traversal_ids, 1) = 3').first Workforce::Person.subtree_of(manager).count Workforce::Person.direct_reports_of(manager).count Workforce::Person.matched(hierarchy).count Workforce::Person.unmatched(hierarchy).count -
Check that the plan uses the traversal index:
puts Workforce::Person.subtree_of(manager).explainExpect
Index Scan using index_workforce_people_on_hierarchy_id_and_traversal_ids.
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Database queries
The SQL for every scope this MR adds, with local EXPLAIN (ANALYZE, BUFFERS) plans. No request path calls these scopes yet; callers come in the follow-up issues.
- Data: local GDK,
COUNT=50000seed, so one tenant with 50,000 people (69 matched to users). The tables wereANALYZEd first. - Seq scans below are expected locally. With one tenant, every row matches
workforce_hierarchy_id, so the planner reads the table. With many tenants, the composite indexes listed at the end apply. - Database Lab plans for production-sized data will follow once my PostgresAI access is activated.
Workforce::Person.in_hierarchy(hierarchy): the tenant filter every other read builds on. Not called on its own except by the development seed.
SELECT "workforce_people".* FROM "workforce_people" WHERE "workforce_people"."workforce_hierarchy_id" = 1Plan
Seq Scan on workforce_people (cost=0.00..2971.00 rows=50000 width=170) (actual time=0.002..2.649 rows=50000 loops=1)
Filter: (workforce_hierarchy_id = 1)
Buffers: shared hit=2346
Planning Time: 0.012 ms
Execution Time: 3.564 msWorkforce::Person.for_user(hierarchy, user): resolves a GitLab user to their person in one tenant, served by the partial unique index on (workforce_hierarchy_id, user_id).
SELECT "workforce_people".* FROM "workforce_people" WHERE "workforce_people"."workforce_hierarchy_id" = 1 AND "workforce_people"."user_id" = 1Plan
Index Scan using index_workforce_people_on_hierarchy_id_and_user_id on workforce_people (cost=0.14..2.16 rows=1 width=170) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: ((workforce_hierarchy_id = 1) AND (user_id = 1))
Buffers: shared hit=2
Planning:
Buffers: shared hit=3
Planning Time: 0.028 ms
Execution Time: 0.005 msWorkforce::Person.unmatched(hierarchy): people with no GitLab user; a filter for later list views, which will paginate.
SELECT "workforce_people".* FROM "workforce_people" WHERE "workforce_people"."workforce_hierarchy_id" = 1 AND "workforce_people"."user_id" IS NULLPlan
Seq Scan on workforce_people (cost=0.00..2971.00 rows=49927 width=170) (actual time=0.005..2.057 rows=49931 loops=1)
Filter: ((user_id IS NULL) AND (workforce_hierarchy_id = 1))
Rows Removed by Filter: 69
Buffers: shared hit=2346
Planning Time: 0.013 ms
Execution Time: 2.957 msWorkforce::Person.direct_reports_of(person): served by (workforce_hierarchy_id, manager_external_xid).
SELECT "workforce_people".* FROM "workforce_people" WHERE "workforce_people"."workforce_hierarchy_id" = 1 AND "workforce_people"."manager_external_xid" = 'wd-2'Plan
Index Scan using index_workforce_people_on_hierarchy_id_and_manager_xid on workforce_people (cost=0.29..5.06 rows=7 width=170) (actual time=0.003..0.004 rows=3 loops=1)
Index Cond: ((workforce_hierarchy_id = 1) AND (manager_external_xid = 'wd-2'::text))
Buffers: shared hit=3
Planning Time: 0.029 ms
Execution Time: 0.007 msWorkforce::Person.subtree_of(person): a manager's whole reporting tree, as a half-open range on traversal_ids served by (workforce_hierarchy_id, traversal_ids). Shown for a depth-4 manager with 3,652 people; the 50,000-person case is in acceptance criterion 1 below.
SELECT "workforce_people".* FROM "workforce_people" WHERE "workforce_people"."workforce_hierarchy_id" = 1 AND (workforce_people.traversal_ids >= '{1,2,6,17}' AND '{1,2,6,18}' > workforce_people.traversal_ids)Plan
Index Scan using index_workforce_people_on_hierarchy_id_and_traversal_ids on workforce_people (cost=0.41..1164.16 rows=3500 width=170) (actual time=0.015..1.584 rows=3652 loops=1)
Index Cond: ((workforce_hierarchy_id = 1) AND (traversal_ids >= '{1,2,6,17}'::bigint[]) AND (traversal_ids < '{1,2,6,18}'::bigint[]))
Buffers: shared hit=1149
Planning Time: 0.121 ms
Execution Time: 1.825 msGroup and organization deletion: Workforce::Hierarchy.delete_for_tenant, called by Groups::DestroyService and Organizations::HardDeleteService just before the group or organization is deleted. The organization form looks up organization_id = ? AND group_id IS NULL.
SELECT "workforce_hierarchies".* FROM "workforce_hierarchies" WHERE "workforce_hierarchies"."group_id" = 22 AND "workforce_hierarchies"."organization_id" IS NULL ORDER BY "workforce_hierarchies"."id" ASC LIMIT 1000
-- repeated until fewer than 1,000 rows are deleted
DELETE FROM "workforce_people" WHERE ("workforce_people"."id") IN (SELECT "workforce_people"."id" FROM "workforce_people" WHERE "workforce_people"."workforce_hierarchy_id" = 1 LIMIT 1000)
DELETE FROM "workforce_hierarchies" WHERE "workforce_hierarchies"."id" = 1Deleting the 50,000-person seed took 230 ms in 51 batches (run in a rolled-back transaction).
Plan for one batch
Delete on workforce_people (cost=72.21..1474.82 rows=0 width=0) (actual time=1.204..1.204 rows=0 loops=1)
Buffers: shared hit=4115
-> Nested Loop (cost=72.21..1474.82 rows=1000 width=38) (actual time=0.262..1.014 rows=1000 loops=1)
Buffers: shared hit=3115
-> HashAggregate (cost=71.92..81.92 rows=1000 width=40) (actual time=0.258..0.346 rows=1000 loops=1)
Group Key: "ANY_subquery".id
Batches: 1 Memory Usage: 193kB
Buffers: shared hit=115
-> Subquery Scan on "ANY_subquery" (cost=0.00..69.42 rows=1000 width=40) (actual time=0.007..0.181 rows=1000 loops=1)
Buffers: shared hit=115
-> Limit (cost=0.00..59.42 rows=1000 width=8) (actual time=0.004..0.121 rows=1000 loops=1)
Buffers: shared hit=115
-> Seq Scan on workforce_people workforce_people_1 (cost=0.00..2971.00 rows=50000 width=8) (actual time=0.003..0.083 rows=1000 loops=1)
Filter: (workforce_hierarchy_id = 1)
Buffers: shared hit=115
-> Index Scan using workforce_people_pkey on workforce_people (cost=0.29..1.41 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=1000)
Index Cond: (id = "ANY_subquery".id)
Buffers: shared hit=3000
Planning Time: 0.337 ms
Execution Time: 1.298 msUser deletion: the ghost user migration's nullify_dependent_associations_in_batches runs this until fewer than 100 rows are updated, using index_workforce_people_on_user_id.
UPDATE "workforce_people" SET "user_id" = NULL WHERE ("workforce_people"."id") IN (SELECT "workforce_people"."id" FROM "workforce_people" WHERE "workforce_people"."user_id" = 1 LIMIT 100)Development seed only: 99_workforce_hierarchy.rb clears the seeded tenant before re-seeding. It never runs in production.
DELETE FROM "workforce_people" WHERE "workforce_people"."workforce_hierarchy_id" = 1Indexes
CREATE UNIQUE INDEX index_workforce_hierarchies_on_group_id ON public.workforce_hierarchies USING btree (group_id)
CREATE UNIQUE INDEX index_workforce_hierarchies_on_integration_id ON public.workforce_hierarchies USING btree (integration_id)
CREATE UNIQUE INDEX index_workforce_hierarchies_on_organization_id ON public.workforce_hierarchies USING btree (organization_id)
CREATE UNIQUE INDEX workforce_hierarchies_pkey ON public.workforce_hierarchies USING btree (id)
CREATE INDEX index_workforce_people_on_group_id ON public.workforce_people USING btree (group_id)
CREATE UNIQUE INDEX index_workforce_people_on_hierarchy_id_and_external_xid ON public.workforce_people USING btree (workforce_hierarchy_id, external_xid)
CREATE INDEX index_workforce_people_on_hierarchy_id_and_manager_xid ON public.workforce_people USING btree (workforce_hierarchy_id, manager_external_xid)
CREATE INDEX index_workforce_people_on_hierarchy_id_and_traversal_ids ON public.workforce_people USING btree (workforce_hierarchy_id, traversal_ids)
CREATE UNIQUE INDEX index_workforce_people_on_hierarchy_id_and_user_id ON public.workforce_people USING btree (workforce_hierarchy_id, user_id) WHERE (user_id IS NOT NULL)
CREATE INDEX index_workforce_people_on_organization_id ON public.workforce_people USING btree (organization_id)
CREATE INDEX index_workforce_people_on_user_id ON public.workforce_people USING btree (user_id)
CREATE UNIQUE INDEX workforce_people_pkey ON public.workforce_people USING btree (id)Evidence for the acceptance criteria
Evidence was collected locally on GDK (PostgreSQL), on the branch rebased onto master on 2026-10-09. Issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/629837
| Acceptance criterion | Status | Proof |
|---|---|---|
| Resolving any manager's subtree in a 50,000-person organisation completes in under 100 ms, verified by a benchmark spec | Met | Slowest read is 14.4 ms (whole org at the root); EXPLAIN plans use the traversal index |
| Every matched GitLab user resolves to exactly one person within their tenant | Met | Unique partial index and model specs |
| A spec proves reads never return another tenant's data | Met | "tenant isolation" specs in person_spec.rb |
| No email column exists anywhere in the store | Met | "stores no email" specs on both models |
| The development seed builds a valid tree of the requested size in development | Met | 50,000 people seeded in 4.7 s; tree checks all 0 |
| Database review has approved the tenant key column as a sharding key | Met | Approved by database review (bauerdominic, abdwdd) and Tenant Scale (rutgerwessels); sharding key and destroy-service foreign key specs pass |
1. Subtree resolves in under 100 ms for 50,000 people
The slowest subtree read is the whole organisation at the root, at 14.4 ms. The spec is ee/spec/models/workforce/person_subtree_benchmark_spec.rb. It is skipped unless BENCHMARK is set, because a wall-clock assertion on shared CI runners would be flaky.
The spec builds 50,000 people (span of control 7) and runs ANALYZE. It asserts the row count.
Then, for one manager at each depth, it asserts the subtree read (a pluck of ids) takes under 100 ms. For subtrees under 10% of the org, it also asserts that the plan uses index_workforce_people_on_hierarchy_id_and_traversal_ids; for larger ones a sequential scan is the cheaper plan.
Benchmark spec output
BENCHMARK=1 bundle exec rspec ee/spec/models/workforce/person_subtree_benchmark_spec.rbWorkforce subtree benchmark
depth 0: 50000 people in 14.4 ms
depth 1: 19608 people in 6.1 ms
depth 2: 2801 people in 1.4 ms
depth 3: 400 people in 0.7 ms
depth 4: 57 people in 0.5 ms
depth 5: 8 people in 0.5 ms
depth 6: 1 people in 0.6 ms
resolves any manager subtree within the budget
Finished in 13.74 seconds (files took 23.44 seconds to load)
2 examples, 0 failuresThe two plans below were taken on the 50,000-person development seed. A 15,812-person subtree executes in 2.4 ms and a 379-person subtree in 0.06 ms. Both are an Index Scan on the traversal index, with the range in the Index Cond.
EXPLAIN (ANALYZE, BUFFERS) plans
-- EXPLAIN ANALYZE subtree_of(person at depth 3)
Index Scan using index_workforce_people_on_hierarchy_id_and_traversal_ids on workforce_people (cost=0.41..3068.41 rows=15744 width=8) (actual time=0.010..2.149 rows=15812 loops=1)
Index Cond: ((workforce_hierarchy_id = 1) AND (traversal_ids >= '{120101,120102,120106}'::bigint[]) AND (traversal_ids < '{120101,120102,120107}'::bigint[]))
Buffers: shared hit=5009
Planning:
Buffers: shared hit=6
Planning Time: 0.068 ms
Execution Time: 2.442 ms
-- EXPLAIN ANALYZE subtree_of(person at depth 5)
Index Scan using index_workforce_people_on_hierarchy_id_and_traversal_ids on workforce_people (cost=0.41..2.44 rows=1 width=8) (actual time=0.005..0.050 rows=379 loops=1)
Index Cond: ((workforce_hierarchy_id = 1) AND (traversal_ids >= '{120101,120102,120106,120117,120191}'::bigint[]) AND (traversal_ids < '{120101,120102,120106,120117,120192}'::bigint[]))
Buffers: shared hit=124
Planning Time: 0.033 ms
Execution Time: 0.060 ms2. A matched user resolves to exactly one person per tenant
The database enforces this with a unique partial index, index_workforce_people_on_hierarchy_id_and_user_id, on (workforce_hierarchy_id, user_id) WHERE user_id IS NOT NULL. One hierarchy per tenant is enforced by unique indexes on workforce_hierarchies.group_id and workforce_hierarchies.organization_id. The model specs below cover both.
Model spec output (relevant lines)
bundle exec rspec ee/spec/models/workforce/person_spec.rb ee/spec/models/workforce/hierarchy_spec.rbWorkforce::Person
tenant isolation
resolves a matched user to exactly one person within a tenant
uniqueness
rejects a second person matched to the same user within a hierarchy
allows many unmatched people within a hierarchy
Workforce::Hierarchy
tenant uniqueness
rejects a second hierarchy for the same group
rejects a second hierarchy for the same organization
Finished in 11.98 seconds (files took 24.36 seconds to load)
47 examples, 0 failures3. Reads never return another tenant's data
The "tenant isolation" block in ee/spec/models/workforce/person_spec.rb sets up a second tenant (an instance-level hierarchy). It gets a person with the same external identifier, the same matched user, and an ancestry path deliberately planted inside the first tenant's subtree, plus an unmatched person. Subtree, direct reports, matched, unmatched and reverse lookup by user all exclude them.
The other tenant's unmatched person reports to a manager with the same external identifier as the first tenant's root. The direct-reports read therefore has a row it must exclude.
Every read scope adds the tenant filter itself: matched, unmatched and for_user take the hierarchy, and subtree_of and direct_reports_of take it from the person passed in.
Tenant isolation spec output
bundle exec rspec ee/spec/models/workforce/person_spec.rb -e "tenant isolation"Workforce::Person
tenant isolation
excludes the other tenant from a subtree read
excludes the other tenant from a direct reports read
resolves a matched user to exactly one person within a tenant
excludes the other tenant from a matched read
excludes the other tenant from an unmatched read
excludes the other tenant from a reverse lookup by user4. No email column in the store
Both model specs have a "stores no email" example. It checks that no column name contains "email", so it also catches names like work_email. The tables store only external_xid, full_name, manager_external_xid, user_id, traversal_ids, plus the tenant key and timestamps.
Model spec output (relevant lines)
bundle exec rspec ee/spec/models/workforce/person_spec.rb ee/spec/models/workforce/hierarchy_spec.rbWorkforce::Person
attribute minimisation
stores no email
Workforce::Hierarchy
attribute minimisation
stores no email
Finished in 11.98 seconds (files took 24.36 seconds to load)
47 examples, 0 failures5. The development seed builds a valid tree
The seed is ee/db/fixtures/development/99_workforce_hierarchy.rb. The default size is 10,000, the maximum is 50,000, and it is 100 when CI is set. Spans of control are 2-4 near the top and 4-10 lower, seeded random, built from root to leaf.
The validation was run with a rails runner script against the seeded data. It found 50,000 people, 1 root and 8 levels. It found 0 paths not ending in the person's own id, 0 unresolved managers, and 0 paths that differ from the manager's path plus own id.
Seed and validation output
FILTER=99_workforce_hierarchy COUNT=50000 bundle exec rake db:seed_fu2026-10-09 08:44:14 UTC: Seeded 50000 people into the workforce hierarchy of toolbox.
== ee/db/fixtures/development/99_workforce_hierarchy.rb took 4.72 seconds
== Seeding took 4.72 secondsValidation (rails runner script, run after seeding):
people: 50000
roots: 1
levels (depth => count): {1=>1, 2=>4, 3=>11, 4=>74, 5=>524, 6=>3730, 7=>25962, 8=>19694}
path not ending in self: 0
unresolved managers: 0
path != manager path+self: 06. Database review of the tenant key as a sharding key
Status: approved by database review (bauerdominic, abdwdd) and Tenant Scale (rutgerwessels). The automated sharding-key and destroy-service foreign key specs pass (17 examples, 0 failures).
The sharding key is group_id (GitLab.com, root group) or organization_id (Self-Managed and Dedicated). Both tables have CHECK (num_nonnulls(group_id, organization_id) = 1) and gitlab_schema is gitlab_main_org. The destroy services handle both tables with handles_removal_of, so nothing is added to spec/support/database/destroy_service_foreign_keys_todo.yml.
Guard spec output
bundle exec rspec spec/lib/gitlab/organizations/sharding_key_spec.rb spec/lib/gitlab/database/foreign_keys_to_destroy_service_tables_spec.rb17 examples, 0 failures