How We Fixed Approver Delegations When User IDs and Employee IDs Got Mixed Up

Part of Building PRANI OPS — Technical Notes. See also the PRANI OPS case study · previous: action processor side effects.

Introduction

The hook

This one was not a missing button.

Leave approvals for a manager who was “out” were supposed to show up for their delegate — with a clear Acting for … label. Sometimes the queue was empty. Sometimes creates looked fine in the admin UI and still failed routing.

The bug lived in a place teams ship often: two ID spaces, one column.

The objective

You’ll see how we:

1. Diagnosed user ID vs employee ID confusion on approver_delegations.
2. Chose a canonical model (employee IDs on the FK).
3. Repaired seed data with Flyway.
4. Accepted user IDs at the API boundary and resolved them.
5. Proved the Carol → Dana demo path with an integration test.

Prerequisites

• Multi-table identity (users vs people/employees)
• Basic Flyway / migration thinking
• Willingness to admit seed data can lie

---

Body

What broke in plain language

PRANI OPS has:

• users — login identity, roles, JWT subject
• employees — the HR person record (title, manager, leave, etc.)

Approver delegations answer: “While this approver is away, send their queue to that person.”

Those “people” in HR workflows are employees. An early seed (and some create paths) stuffed user UUIDs into columns that the schema and leave router treated as employee UUIDs.

Nothing exploded with a red stack trace on every request. Routing just… missed.

Root cause in one sentence

We changed the FK target to employees without making every writer and every seed agree.

Classic migration drift: V43-era seed wrote user IDs; a later migration retargeted FKs toward employees; the admin create path and UI still thought in user-ID terms.

Fix 1 — Pick a canonical ID

We locked the rule:

delegate_from_id / delegate_to_id store employee IDs.

No “sometimes user, sometimes employee.” One domain meaning.

Fix 2 — Repair data you already shipped

A Flyway repair migration mapped existing user IDs → their employee rows and refreshed the demo window (Carol → Dana) so the sandbox told the truth again.

Empty or wrong seed rows after an FK retarget are worse than no feature: demos teach the wrong story.

Fix 3 — Resolve at the boundary

Callers still might send a user ID (old clients, copy-paste from JWT, muscle memory). The service accepts either and resolves:

input UUID
   ├─ looks like employee? → use it
   └─ looks like user?     → find that user’s employee → use employee id

Conceptually:

UUID resolveEmployeeId(UUID idOrUserId) {
    if (employeeRepository.existsById(idOrUserId)) {
        return idOrUserId;
    }
    return employeeRepository.findByUserId(idOrUserId)
        .map(Employee::getId)
        .orElseThrow(/* clear 4xx */);
}

Fix 4 — Stop asking humans for raw UUIDs

The admin Delegations screen now uses employee selects (names you can read), not a blank UUID field that invites the wrong namespace.

Fix 5 — Prove “acting for” end to end

Integration test shape:

1. Create a delegation using Carol’s user ID (the messy input).
2. Assert stored FKs are Carol/Dana employee IDs.
3. As Dana, see Alice’s leave in the pending queue with Acting for Carol Santos (or equivalent).
4. Approve successfully as the delegate.

If that test is green, the demo path is trustworthy.

What I’d do earlier next time

• Name columns delegate_from_employee_id when the type is employee — make the wrong write feel wrong in review.
• Add a DB check or startup assert: every delegation ID must exist in employees.
• Never seed “convenient” UUIDs from the wrong table just because the insert succeeded.

---

Conclusion

Summary

• User ID ≠ employee ID. Pick one for domain FKs.
• Retargeting FKs without repairing seeds and writers creates silent routing bugs.
• Resolve messy inputs at the API edge; store only the canonical ID.
• One integration test that walks “acting for” is worth more than another screenshot.

Call to action

On the live demo, compare HR Delegations (employee pickers) with Dana’s manager approvals when Carol is covered. Full narrative: PRANI OPS case study.

That’s the third note in this mini-series — hosting, side effects, then identity. If you’re building multi-role HR software, start the ID model conversation before the second migration lands.