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 Workforce bounded 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_people with external_xid, full_name, manager_external_xid, user_id, the traversal_ids ancestry path, and the tenant key. It holds no email or other HR attribute.
  • Sharding key group_id or organization_id, enforced by CHECK (num_nonnulls(group_id, organization_id) = 1). The gitlab_schema is gitlab_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 as index_namespaces_on_traversal_ids_for_groups_btree
  • Models Workforce::Hierarchy and Workforce::Person with the scopes in_hierarchy, subtree_of, direct_reports_of, matched, unmatched and for_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 with BENCHMARK=1.

Out of scope, as stated in the issue: the sync, the defects table, the Workday integration, and any UI.

Decisions reviewers should know

  1. The table is workforce_people (model Workforce::Person), not workforce_hierarchy_records as in the issue's draft SQL.

    Why The name matches the domain language: a person in a hierarchy. It also matches the model name.
  2. workforce_hierarchies.integration_id is 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_id and organization_id.

  3. The destroy services clean up the workforce rows and declare it with handles_removal_of, so nothing is added to spec/support/database/destroy_service_foreign_keys_todo.yml.

    Why

    A root group or organization holds up to 50,000 workforce_people rows (the largest supported tenant), too many for one cascading delete. Groups::DestroyService and Organizations::HardDeleteService call Workforce::Hierarchy.delete_for_tenant just 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_id through EE::User#workforce_people (dependent: :nullify). The foreign key ON DELETE actions stay as a backstop.

  4. config/organizations/transfer_support.yml links both tables to the follow-up https://gitlab.com/gitlab-org/gitlab/-/work_items/634509, as Tenant Scale asked. Workforce::Person is in skipped_transfer_models of Organizations::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.
  5. The read API scopes subtree_of and direct_reports_of are listed in scripts/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.

  6. The benchmark is skipped unless BENCHMARK=1 is 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 recognise ee/spec/benchmarks. A name that does not end in _spec.rb fails the RSpec RuboCop cops.

  7. Tenant Scale (rutgerwessels) and database review (bauerdominic, abdwdd) have approved the group_id / organization_id sharding key. This is an acceptance criterion of the issue.

References

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

  1. 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
  2. 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
  3. Check that the plan uses the traversal index:

    puts Workforce::Person.subtree_of(manager).explain

    Expect 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=50000 seed, so one tenant with 50,000 people (69 matched to users). The tables were ANALYZEd 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" = 1
Plan
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 ms

Workforce::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" = 1
Plan
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 ms

Workforce::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 NULL
Plan
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 ms

Workforce::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 ms

Workforce::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 ms

Group 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" = 1

Deleting 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 ms

User 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" = 1
Indexes
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.rb
Workforce 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 failures

The 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 ms

2. 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.rb
Workforce::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 failures

3. 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 user

4. 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.rb
Workforce::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 failures

5. 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_fu
2026-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 seconds

Validation (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: 0

6. 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.rb
17 examples, 0 failures
Edited by Jatin Parihar

Merge request reports

Loading
Loading