I spent the last few days working through one of the most foundational layers in our system — the user–supervisor–contract structure. The goal was simple: ensure that every user in the system has:

• a contract with the right supervisor,
• a valid assignment to a unit,
• a role that fits into a larger hierarchical model.

But of course, the real world isn’t simple. So here’s how it actually went.

---

A Multi-Level Hierarchy

At the top sits the system owner — let’s call it the Admin. Below that, the structure branches into multiple levels:

1. Regional Coordinators – One per region.
2. Local Managers – One per organizational unit.
3. Team Leads – Four per unit.
4. Operators – Eight per unit, two per lead.

Each of these has a contract, always with their direct supervisor, and is tied to a specific unit.

Contract Details

I used a single SQL table to express contracts:

sql CREATE TABLE users.user_contracts ( id UUID PRIMARY KEY, unit_d UUID NOT NULL, job_title VARCHAR NOT NULL, contract_type_id UUID NOT NULL, contract_amount NUMERIC(10,2) NOT NULL, user_a_id UUID NOT NULL, -- supervisor user_b_id UUID NOT NULL, -- subordinate valid_from DATE NOT NULL, valid_to DATE, data JSONB, created_by UUID, updated_by UUID, contract_number VARCHAR, subscriber_id UUID );

Each contract included:

• a job title consistent with the role (e.g. "Local Manager"),
• a contract type selected from three (b2b, uop, uz),
• randomized but coherent dates (start + optional end),
• amounts in predefined ranges per role,
• and optional JSON metadata ({ "source": "seed", "batch": 1 }).

Assigning Contracts and Supervisors

The logic flowed like this:

1. For each region, one unit gets a Regional Coordinator.
2. That person signs contracts with each Local Manager in the region.
3. Each Local Manager signs with four Team Leads.
4. Each Team Lead signs with two Operators.

Each of these assignments required:

• generating a new UUID for the contract,
• matching unit IDs across datasets,
• ensuring user_b_id.supervisor_id = user_a_id,
• and storing it all in user_contracts.

Finally, we issued a massive UPDATE across users, setting each person’s supervisor_id accordingly.

Statistical Summary

The full structure after seeding:

• Total users with contracts: 1,408
• Regional Coordinators: 16
• Local Managers: 160
• Team Leads: 640
• Operators: 592
• Total units: 160 (10 per region × 16 regions)
• Contracts inserted: 1,408
• Supervisor relationships assigned: 1,408
• Contract date range:
• Earliest start: 2020-01-01
• Latest end: 2035-12-31
• Salary ranges per role:
• Regional Coordinators: 12,000–15,000
• Local Managers: 12,000–15,000
• Team Leads: 7,000–10,000
• Operators: 5,000–7,000

Example Contract Insert

INSERT INTO users.user_contracts (
  id, unit_id, job_title, contract_type_id,
  contract_amount, user_a_id, user_b_id,
  valid_from, valid_to, data,
  created_by, updated_by, contract_number, subscriber_id
) VALUES (
  'uuid-1234', 'unit-xyz', 'Team Lead',
  'type-uop', 8500.00, 'uuid-local-manager', 'uuid-team-lead',
  '2023-02-01', NULL, '{"source":"seed"}',
  'uuid-admin', 'uuid-admin', 'CN-0001', 'uuid-admin'
);

Reflections

It’s deceptively complex to model hierarchical relationships in a way that remains queryable, maintainable, and realistic. The contract layer became the glue — simultaneously anchoring business logic, permission models, and structural relationships.

This might not be the most glamorous piece of code in the system, but it’s one of the most satisfying ones to get right.