Who reported to whom last March? Modelling organisations over time

An organisation chart looks like a tree, so the first data model is usually a table of units with a parent_id. It works until the first restructuring. A department moves under a different division, someone asks for last quarter's report "as the organisation was then", and the answer is gone, because the parent was overwritten.

When I designed the organisation service behind an ERP, time was the central requirement: every relationship needed to be valid for a period, and the system had to answer what the structure looked like on any date. These are the ideas the design rests on.

Relationships have a validity period

Instead of storing the parent on the unit, the parent relationship becomes its own row with a start and an end. Moving a department does not edit anything; it closes the current row and opens a new one. The past stays exactly as it was.

create table unit_parent (
  unit_id    uuid not null references unit(id),
  parent_id  uuid not null references unit(id),
  valid      daterange not null,
  tenant_id  uuid not null,
  -- one active parent per unit at any moment
  exclude using gist (unit_id with =, valid with &&)
);

PostgreSQL's range types make this pleasant. A daterange holds the period, and an exclusion constraint guarantees that a unit never has two parents on the same day. That rule is enforced by the database itself, so no code path, import script or concurrent request can break it.

Put invariants where they cannot be skipped

Some rules are too important to live only in application code. A unit must never end up as its own ancestor, because a cycle turns every tree query into an infinite loop. Checking for cycles in the service is not enough: two requests running at the same time can each pass the check and together create one.

The answer is a trigger that walks up from the new parent within the same transaction and rejects the change if it reaches the unit being moved, combined with locking so that concurrent moves in the same part of the tree run one after another. The same approach keeps employment consistent with the organisation it belongs to. These rules are rarely triggered, but when they are, they prevent data that would be very expensive to repair.

Ask questions as of a date

With effective dated rows, "the tree" is no longer one thing. It is a function of a date. The service exposes that directly: every read that returns structure accepts an asOf parameter, defaulting to today, and the tree for that date is computed by a recursive query over the rows valid on that day.

with recursive tree as (
  select u.id, null::uuid as parent_id, 0 as depth
  from unit u where u.id = :root
  union all
  select p.unit_id, p.parent_id, t.depth + 1
  from unit_parent p
  join tree t on p.parent_id = t.id
  where p.valid @> :as_of::date
)
select * from tree;

Wrapping this in a SQL view keeps the logic in one place. Reports, approvals and the frontend all ask the same question in the same way, and an auditor who asks how things stood on a past date gets the same answer the system would have given on that day.

Positions are not people

The other common shortcut is attaching people directly to units. Real organisations have positions that exist whether or not someone fills them, people who hold more than one position, and reporting lines that cross the main hierarchy. Modelling positions, people, assignments and employment as separate concepts, each with its own validity period, means a vacancy or a matrix reporting line is represented honestly instead of hacked around.

People and organisations also share one identity root, a party. A supplier company and an employee can both be the subject of a contract or an approval, and the rest of the system does not need two code paths for it.

What this means for the screens

• +A date picker is a first class control. Every structure view has an "as of" date, so people can look at the past and at changes scheduled for the future.
• +Edits ask "from when?". Moving a unit is a change with an effective date, not an immediate overwrite, and the form says so.
• +History is a timeline. A unit's detail page shows its parents and positions over time, which answers most "why does this report look different" questions without a support ticket.
• +Errors explain the rule. When the database rejects a change, the API turns it into a message such as "this would make the unit report to itself", not a constraint name.

Effective dating adds some complexity up front. In exchange, the system never forgets, restructurings become routine instead of risky, and the question "how did it look then?" always has an answer.