Design an Employee Management System

Priya in HR creates Alice’s employee record, puts her in Engineering, and sets Bob as her manager. A week later Charlie joins and reports to Alice.

The directory now looks like this:

Bob
 └── Alice
      └── Charlie

That graph is small, but it already contains the hard parts of this problem. Two HR admins might edit Alice at the same time. Someone might try to make Charlie Bob’s manager and close a loop. Search, org charts, caches, and audit logs all want a copy of the same change. If those copies are written independently, they will disagree.

The main design question is:

How do we keep employee records correct under concurrent edits, while search, org charts, caches, and audit trails stay useful without becoming sources of truth?

We will start with one PostgreSQL database and a small API. Cache, Kafka, and OpenSearch appear only after a concrete limitation appears.

1. Clarify the problem

“Design an Employee Management System” can mean payroll, benefits, attendance, performance reviews, and a full HRIS. That is too broad for one interview.

I would ask:

• Are we designing a company directory or a complete HR platform?
• Do employees leave by deactivation, or must records be physically deleted?
• Do we need the full organization tree, or only direct reports?
• Is search exact-match SQL, or full-text across name, email, and designation?
• Who may create, update, or view employees?
• How large is the company, and which other systems consume employee changes?
• Must we keep an immutable history of mutations?

If the interviewer gives no extra constraints, I would state:

Product              Internal employee directory and org graph
Not in scope         Payroll, benefits, attendance, recruiting
Identity             UUID employee and department ids
Lifecycle            Soft delete: ACTIVE or INACTIVE
Hierarchy            Adjacency list via manager_id
Search               Name, email, designation, department, status
Auth                 Simple RBAC in the backend
Deployment           One region first
Stack                Go, PostgreSQL, Redis, Kafka, OpenSearch

The stack is a credible interview implementation, not a claim that a 100,000-row catalog needs Kafka. PostgreSQL can store this catalog. Events and search exist because other systems and access patterns need them.

2. Functional requirements

The system must:

1. Create an employee.
2. Get an employee by id.
3. Update an employee.
4. Deactivate an employee without physically deleting the row.
5. List employees with pagination.
6. Search employees by name, email, department, designation, status, and manager.
7. Create and list departments.
8. Assign an employee to a department.
9. Assign or change a manager.
10. Get direct reports.
11. Get an employee’s organization subtree.
12. Keep an audit history of important mutations.

The first version does not include:

• payroll, compensation, or tax forms;
• leave, attendance, or timesheets;
• performance reviews;
• recruiting or onboarding workflows; or
• a public people-search product for the open internet.

3. Non-functional requirements

The important quality targets are:

Consistency is not one global setting.

Employee row in PostgreSQL     source of truth; read-your-write for the actor
Redis employee cache           derived; may be briefly stale
OpenSearch document            derived; may lag by a few seconds
Audit log                      derived; at-least-once, idempotent
Org chart from manager_id      computed from source rows

Edge cases to keep in mind

• Two HR admins update Alice with the same version.
• Charlie is assigned as Bob’s manager, which would cycle the chain.
• An inactive employee is proposed as a manager.
• Alice cannot be her own manager.
• Duplicate email on create or update.
• Search is down while Priya is still hiring.
• Redis is down during a profile read spike.
• The same Kafka event is delivered twice.
• A client retries create after a timeout.

4. Estimate the scale

Use round numbers. The goal is to find what is not the bottleneck.

Assume a large company directory:

Employees                         100,000
Departments                       500
Internal daily active users       20,000
Profile reads                     50/user/day
Searches                          10/user/day
Org-chart views                   5/user/day
Employee mutations                2,000/day

Read traffic:

20,000 × 50 profile reads = 1,000,000 reads/day
1,000,000 / 86,400        ≈ 12 average QPS
20× peak                  ≈ 240 QPS

Search and org-chart traffic is even smaller. Mutations are tens per second at a noisy peak, not thousands.

Storage is also small:

Employee row + indexes     ~2 KB
100,000 × 2 KB             ≈ 200 MB
Audit rows over 5 years    still low single-digit GB

PostgreSQL can hold this comfortably. The interview value is not “we outgrew SQL.” It is concurrent correctness, hierarchy rules, authorization, and keeping derived systems honest.

Where scale still matters:

• A CEO org-chart walk can touch tens of thousands of rows if implemented naively.
• Hot keys such as employee:{ceo_id} are read far more than average.
• Other systems — badge access, payroll, IT provisioning — want the same change event.
• Full-text search with several filters is awkward as growing ILIKE queries.

Those are the reasons to add cache, events, and a search projection later.

5. What are we storing?

We are storing facts about people and the reporting graph.

Employee     a person in the company directory
Department   a named org bucket
Manager edge employee.manager_id → employee.id
Audit        an immutable record of a mutation
Outbox       a durable “this change happened” event

An employee is not deleted. Status tells us whether they are currently in the working graph.

ACTIVE     appears in lists, may manage others
INACTIVE   hidden from normal lists, never a manager, kept for history

Search documents and cache entries are copies of those facts. If they disagree with PostgreSQL, PostgreSQL wins.

6. APIs

Keep the API small and independent of Redis, Kafka, or OpenSearch.

Create employee

POST /v1/employees
Authorization: Bearer <token>
Idempotency-Key: <uuid>

{
  "name": "Alice Kumar",
  "email": "alice@example.com",
  "phone": "+1-555-0101",
  "designation": "Senior Engineer",
  "department_id": "d_eng",
  "manager_id": "e_bob",
  "joining_date": "2026-08-17"
}

HTTP 201 Created

{
  "id": "e_alice",
  "name": "Alice Kumar",
  "email": "alice@example.com",
  "phone": "+1-555-0101",
  "designation": "Senior Engineer",
  "department_id": "d_eng",
  "manager_id": "e_bob",
  "status": "ACTIVE",
  "joining_date": "2026-08-17",
  "version": 1,
  "created_at": "2026-08-17T05:01:00Z",
  "updated_at": "2026-08-17T05:01:00Z"
}

The idempotency key matters. If Priya’s client times out after the row is inserted, retrying the same request should return the original employee, not a second row.

Get employee

GET /v1/employees/e_alice
Authorization: Bearer <token>

HTTP 200 OK

{
  "id": "e_alice",
  "name": "Alice Kumar",
  "email": "alice@example.com",
  "department_id": "d_eng",
  "manager_id": "e_bob",
  "status": "ACTIVE",
  "version": 1,
  "updated_at": "2026-08-17T05:01:00Z"
}

Update employee

Every mutation carries the version the client last read.

PATCH /v1/employees/e_alice
Authorization: Bearer <token>

{
  "designation": "Staff Engineer",
  "version": 1
}

HTTP 200 OK

{
  "id": "e_alice",
  "designation": "Staff Engineer",
  "version": 2,
  "updated_at": "2026-08-17T06:10:00Z"
}

If another writer already moved Alice to version 2:

HTTP 409 Conflict

{
  "error": {
    "code": "VERSION_CONFLICT",
    "message": "Employee was updated by another request"
  }
}

Deactivate employee

DELETE /v1/employees/e_alice
Authorization: Bearer <token>

HTTP 200 OK

{
  "id": "e_alice",
  "status": "INACTIVE",
  "version": 3
}

This is a status change, not a DELETE FROM employees.

List and search

GET /v1/employees?department_id=d_eng&status=ACTIVE&limit=50&cursor=...
GET /v1/employees/search?q=alice&department=engineering&status=ACTIVE&limit=20

HTTP 200 OK

{
  "items": [ { "id": "e_alice", "name": "Alice Kumar" } ],
  "next_cursor": "eyJpZCI6ImVfYWxpY2UifQ",
  "limit": 50
}

Hierarchy and departments

GET  /v1/employees/{id}/reports
GET  /v1/employees/{id}/organization
POST /v1/departments
GET  /v1/departments

Errors

Clients always see a stable envelope. Internal SQL messages stay in logs.

{
  "error": {
    "code": "EMPLOYEE_NOT_FOUND",
    "message": "Employee not found"
  }
}

400  VALIDATION_ERROR, INVALID_MANAGER, SELF_MANAGER
401  UNAUTHENTICATED
403  FORBIDDEN
404  EMPLOYEE_NOT_FOUND, DEPARTMENT_NOT_FOUND
409  VERSION_CONFLICT, EMAIL_ALREADY_EXISTS, MANAGER_CYCLE
500  INTERNAL_ERROR

7. Basic data model

Department

departments
-----------
id            UUID PK
name          unique
created_at
updated_at

Employee

employees
---------
id              UUID PK
name
email           unique
phone
designation
department_id   FK → departments.id
manager_id      FK → employees.id  (nullable)
status          ACTIVE | INACTIVE
joining_date
created_at
updated_at
version         integer, starts at 1

Important indexes:

employees(email)                 uniqueness and login/directory lookup
employees(department_id)
employees(manager_id)            direct reports
employees(status)
employees(department_id, status) common list filter

manager_id is nullable for the CEO or any root. A check constraint can forbid manager_id = id. Inactive-manager and cycle rules need application logic, because they look at other rows.

Outbox

outbox_events
-------------
id               UUID PK
aggregate_type   EMPLOYEE
aggregate_id     employee id
event_type       EMPLOYEE_CREATED | EMPLOYEE_UPDATED | ...
payload          JSON
created_at
processed_at     nullable

Audit

employee_audit_logs
-------------------
id
employee_id
action
old_value
new_value
performed_by
created_at

Audit rows are insert-only. Updates and deletes are not part of the API.

Source of truth     employees, departments
Derived data        OpenSearch documents, org-chart responses
Cache               employee:{id}
Event system        outbox → Kafka
Ephemeral state     request ids, in-flight worker leases

8. Start with one service and PostgreSQL

Do not begin with six microservices. One Go process and PostgreSQL can implement every functional requirement.

Client
  │
  ▼
HTTP Handler
  │  authn, request id, JSON
  ▼
Service
  │  validation, RBAC, transactions
  ▼
Repository
  │
  ▼
PostgreSQL

Handlers parse HTTP and map domain errors to status codes. Services own business rules. Repositories own SQL. That split is enough dependency inversion for an interview. It is also easy to test: fake the repository, exercise the service.

Create employee, in one transaction:

BEGIN
  insert employee
  insert outbox EMPLOYEE_CREATED
COMMIT

The outbox row can wait until we have a worker. Putting it in the same transaction now means we will not invent a dual-write later.

At this stage Redis, Kafka, and OpenSearch are unused. The API already works.

9. Soft delete, not physical delete

DELETE /employees/{id} sets status = INACTIVE.

That single rule has several consequences:

Inactive employees
  - do not appear in normal lists
  - cannot be assigned as managers
  - cannot receive new direct reports
  - remain readable by id for HR and audit
  - remain in historical manager chains as facts

If Alice is deactivated while Charlie still has manager_id = Alice, we should reject new assignments that use Alice as manager. Existing edges can stay until HR reassigns Charlie, or the deactivation transaction can require manager_id changes for direct reports. The interview-quality choice is:

Deactivate Alice
  1. Reject if she still has ACTIVE direct reports, or
  2. In the same transaction, set those reports' manager_id to Alice's manager

Option 1 is simpler and forces an explicit HR decision. Option 2 is nicer for operators and still fits one transaction. Either is defensible if you state it.

Physical delete would break audit history, dangling manager_id foreign keys, and any external system that still holds the employee id.

10. Two HR admins update the same employee

Priya and Sam both open Alice at version 5.

Priya reads Alice version=5
Sam   reads Alice version=5

Priya: PATCH designation, version=5   → success, version=6
Sam:   PATCH manager_id,  version=5   → 0 rows updated → 409

The SQL is the lock:

UPDATE employees
SET designation = $1,
    version = version + 1,
    updated_at = now()
WHERE id = $2
  AND version = $3

If the WHERE matches no row, either Alice does not exist or someone else already wrote. Distinguish those with a follow-up read, then return EMPLOYEE_NOT_FOUND or VERSION_CONFLICT.

Why optimistic locking instead of SELECT FOR UPDATE on every write?

Optimistic     good when conflicts are rare; no lock held across the HTTP call
Pessimistic    good when the same row is hot and retries would thrash

Employee records are not concert tickets. Two admins colliding on Alice is uncommon. Returning 409 and asking the client to reload is the simplest production-credible answer. Put the version predicate in SQL so two application nodes cannot both think they won.

A unique constraint on email covers the other common race: two creates with the same email. The second insert fails, and the API returns EMAIL_ALREADY_EXISTS.

11. Manager hierarchy and cycle prevention

The reporting graph is an adjacency list:

employees.manager_id → employees.id

Bob.id    manager_id = NULL
Alice.id  manager_id = Bob
Charlie.id manager_id = Alice

That model is easy to explain and cheap for “who is my manager?” and “who reports to me?”

Rules at assignment time:

1. The manager must exist.
2. The manager must be ACTIVE.
3. manager_id != employee.id.
4. The new manager must not already lie in the employee’s subtree.

Rule 4 is the cycle check. If Charlie became Bob’s manager:

Charlie → Alice → Bob → Charlie   ← cycle

Walk upward from the proposed manager until we run out of managers or hit the employee being edited:

wouldCreateCycle(employee, newManager):
  current = newManager
  seen = {}
  while current is not null:
    if current.id == employee.id:
      return true
    if current.id in seen:          # corrupt data, fail closed
      return true
    seen.add(current.id)
    current = current.manager
  return false

Complexity is O(depth) queries, or O(depth) after one chain fetch. Company trees are almost always shallow. Cap the walk, for example at 50, and fail closed if the cap is hit.

A recursive CTE can do the same check in one SQL round trip. Either is fine. The important point is that the check runs in the same transaction as the update, so a concurrent move cannot sneak a cycle in between the read and the write. Combine it with the version predicate.

Self-manager is the depth-0 case of the same idea. Inactive manager is a status check on the target row, also inside the transaction.

12. Direct reports and organization subtree

Direct reports are one indexed lookup:

GET /employees/e_alice/reports

SELECT * FROM employees
WHERE manager_id = 'e_alice'
  AND status = 'ACTIVE'
ORDER BY id
LIMIT 50

The organization endpoint returns Alice’s subtree, not the whole company.

GET /employees/e_alice/organization

Alice
 ├── Charlie
 └── Diya
      └── Evan

Walk downward with BFS so a deep, bushy tree does not blow the call stack:

organization(root):
  queue = [root]
  nodes = {root.id: root}
  children = {}

  while queue is not empty:
    manager = queue.pop()
    reports = loadActiveByManager(manager.id)
    children[manager.id] = reports
    for each report:
      nodes[report.id] = report
      queue.push(report)

  return tree from root using children

Complexity is O(N) in the subtree size, with one query per manager if implemented naively. That is acceptable for typical teams. For a CEO with 100,000 descendants, batch the lookup:

SELECT * FROM employees
WHERE manager_id = ANY($1)
  AND status = 'ACTIVE'

and expand level by level. Still O(N), but O(depth) round trips instead of O(N).

Do not cache the whole company tree as one blob. It is large, changes often enough, and is easy to rebuild from manager_id. Cache individual employees, not the assembled graph.

Nested sets or a closure table make subtree reads cheaper at the cost of expensive moves. For an interview directory, adjacency list plus BFS is the right default. Mention the alternatives; do not start there.

13. List and search start as SQL

List is a filtered primary-key scan:

GET /employees?department_id=d_eng&status=ACTIVE&limit=50&cursor=...

PostgreSQL can do this with (department_id, status) and a cursor on id.

Search looks similar at 100,000 rows:

WHERE status = 'ACTIVE'
  AND name ILIKE '%alice%'

That works in a demo. It starts to hurt when:

• people want prefix and fuzzy name match;
• filters combine department, designation, and status;
• relevance ranking matters;
• other applications query the same directory shape.

That is when OpenSearch becomes a read model, not before. Until then, keep search in SQL so the product works with one database.

14. Cursor pagination

Offset pagination is easy and wrong for a mutating directory:

page 2 = OFFSET 50

A row inserted on page 1 shifts everyone, and a large offset still makes the database walk discarded rows.

Use a cursor on a unique, stable order, typically id or (created_at, id):

WHERE department_id = $dept
  AND status = 'ACTIVE'
  AND id > $cursor
ORDER BY id
LIMIT 50

Return an opaque next_cursor. Clients must not invent offsets. If a filter changes, the cursor is invalid; start over.

Org-chart responses can page breadth-first as well, but a typical team subtree fits in one payload. Cap it. If someone asks for the CEO tree of 100,000 people, return a paginated expansion, not a 20 MB JSON blob.

15. Cache employee details

GET /employees/{id} is the hottest path: other services, the org-chart hydrator, and the profile page all need the same object.

Cache-aside is enough:

GET employee:{id}
        │
        ├── hit  → return
        │
        └── miss → PostgreSQL
                     │
                     ▼
                   Redis SET employee:{id}
                     │
                     ▼
                   return

On every successful mutation:

PostgreSQL commit
    ↓
DELETE employee:{id}

Invalidate, do not update-in-place. A failed cache write after a successful DB update would leave a stale object if we had written the new value from a racing request.

Redis is not the source of truth. If Redis is down:

Read     go to PostgreSQL
Write    still commit in PostgreSQL; log the failed invalidation

A missed invalidation means a stale profile until TTL expiry. Use a modest TTL, for example 5–15 minutes, as a safety net, not as the primary consistency mechanism.

Hot keys such as the CEO are real even in a company directory. Read replicas and a local in-process cache with a very short TTL can protect PostgreSQL. Do not shard Redis for 100,000 keys; that is theater.

16. Why search cannot share the write transaction

Suppose Priya updates Alice and we also update OpenSearch in the request:

BEGIN PostgreSQL update
update OpenSearch
COMMIT

OpenSearch is not in the PostgreSQL transaction.

Postgres succeeds, OpenSearch fails  → directory true, search stale
OpenSearch succeeds, Postgres fails  → search lies, directory did not change

That is the dual-write problem. The employee write must not depend on search being up. Search is a projection.

The same argument applies to audit if audit lives in another store, and to any downstream system.

17. Transactional outbox

The fix is to write the event in the same database transaction as the employee:

BEGIN
  UPDATE employees SET ... WHERE id = $id AND version = $v
  INSERT INTO outbox_events (
    aggregate_type, aggregate_id, event_type, payload
  )
COMMIT

If the update affects zero rows, insert no event and return 409.

A relay worker then publishes:

Outbox worker
  SELECT unpublished events
  publish to Kafka
  mark processed_at

If Kafka is down, rows stay unpublished. They are not lost. The worker retries. Publishing is at-least-once, so consumers must be idempotent.

Event types worth having:

EMPLOYEE_CREATED
EMPLOYEE_UPDATED
EMPLOYEE_DEACTIVATED
MANAGER_CHANGED
DEPARTMENT_CHANGED

MANAGER_CHANGED is a specialized update. It lets the org-chart and audit consumers avoid parsing every field-level diff.

Poll the outbox in created-at order, or use LISTEN/NOTIFY plus a safety poll. Skip fancy CDC unless the interviewer pushes for it. CDC is another way to get the same “commit, then publish” property; the outbox table is easier to explain and to inspect.

18. Kafka consumers: search and audit

Two consumers are enough for the interview.

PostgreSQL
    ↓
Outbox
    ↓
Kafka topic employee.events
    ├── Search Indexer  → OpenSearch
    └── Audit Service   → employee_audit_logs

Partition by employee_id so updates for Alice stay ordered. Global order across employees is unnecessary.

Make both consumers idempotent:

Search   upsert by employee id; a duplicate EMPLOYEE_UPDATED is the same document
Audit    unique (event_id) or (employee_id, event_id); ignore a second insert

Store event_id from the outbox row in the payload. That is the idempotency key. Processing twice must not create two audit rows or a corrupt document.

If the indexer is behind, CRUD still works. Search is stale. That is the product trade-off you tell the interviewer.

A second topic employee.audit.events is optional. One topic and two consumer groups is simpler unless audit needs a different retention or ACL.

19. OpenSearch as a projection

Index roughly:

employee_id
name
email
designation
department_id
department_name
manager_id
status
joining_date
updated_at

Queries can then mix free text and filters:

GET /employees/search?q=alice&department=engineering&status=ACTIVE

PostgreSQL remains authoritative. If OpenSearch is unavailable, GET /employees/search can:

return 503 with a clear code, or
fall back to a narrower SQL search for exact email / id

Do not fail POST /employees because search is down.

Rebuild is a feature, not an emergency-only tool. Replay from PostgreSQL, or from retained Kafka, and upsert. Because documents are keyed by employee id, rebuilds are idempotent.

This is the same search-as-projection idea used in a booking catalog or a tweet index: fast, denormalized, eventually consistent, and never the write path.

20. RBAC

Authorization lives in the backend. A hidden button in the UI is not a security control.

Roles:

EMPLOYEE      view own profile; update limited personal fields
MANAGER       EMPLOYEE + view direct reports
HR_ADMIN      create, update, deactivate, change manager/department, search all
SUPER_ADMIN   everything

Keep the implementation boring:

1. Gateway/middleware authenticates and attaches actor id + roles
2. Handler calls the service with the actor
3. Service checks permission before mutation or sensitive read

Examples:

Alice GET /employees/e_alice           allowed
Alice GET /employees/e_bob             denied unless HR/manager relationship
Bob   GET /employees/e_alice/reports   allowed if Alice reports to Bob
Bob   PATCH Alice.department           denied
Priya PATCH Alice.department           allowed

A manager’s “view direct reports” should use the source graph, not a cached org document, so a reassignment is visible immediately for authorization.

Do not build a general policy engine. A small matrix of role × action, plus an ownership check, is enough to explain in an interview. Map failures to 403 FORBIDDEN without saying which other records exist if that would leak data.

21. Consistency model

Say this out loud; it is the Senior-level spine of the design.

Strong, same transaction
  employee row, version, manager rules, outbox insert

Read-your-write for the actor
  after a successful PATCH, GET by id reads PostgreSQL or a request-local value

Eventual
  Redis cache, OpenSearch, audit rows, other services consuming Kafka

Unacceptable
  lost updates, cycles, self-manager, inactive manager, duplicate email

Cache invalidation after commit means a reader can still see the old Alice for a short window. That is allowed for a directory. It is not allowed for the version check on the next write: writes always go to PostgreSQL with id + version.

22. Failure scenarios

Degraded behavior should be explicit in logs and metrics. A cached profile during a database outage is a product decision: I would not serve it for HR mutations, and I would be cautious even for reads, because an inactive employee could still appear active.

23. Observability

Keep this light.

Structured logs     request_id, actor_id, employee_id, version, error code
Latency             handler duration
Errors              mapped domain code vs unexpected 500
Cache               hit / miss / error
Outbox              unpublished age, publish failures
Kafka               consumer lag, handler failures
Search             indexer lag, OpenSearch errors

The operational metric that matters for this design is not QPS. It is event lag: time from employee commit to search document and audit row. If Priya changes a manager and the org search is 10 minutes behind, the directory looks wrong even though PostgreSQL is fine.

24. Security and abuse

• Authenticate every route; authorization is server-side RBAC.
• Rate-limit search and org-chart expansion; they are the expensive reads.
• Do not put salaries or government ids on this record if the product does not need them.
• Audit performed_by from the authenticated actor, never from a client-supplied field.
• Treat email as unique and case-normalized.
• Do not return stack traces or SQLSTATE to clients.

25. Scaling

You will almost certainly not shard employees by id in this interview. Vertical scale, indexes, and read replicas cover 100,000 rows.

If the company is 10 million people, or this directory is a multi-tenant SaaS:

Partition employees by tenant_id, not by employee_id
Keep a person's manager in the same tenant
Do not put two tenants' org walks on one hot partition by accident

Cache before you split the write database. Split search from OLTP before you split OLTP from itself.

Multi-region: active-active reads are easy; active-active writes on the same employee are not. A home-region for HR mutations avoids split-brain version numbers. Directory reads can follow a replica with a few seconds of lag.

26. Where logic lives

This is the LLD the interview actually wants: transactional boundaries, not a framework dump.

cmd/server                 HTTP main, wiring
internal/employee          handler, service, repository, model
internal/department        handler, service, repository, model
internal/auth              actor, roles, permission checks
internal/cache             Redis adapter; failures are errors, not panics
internal/outbox            writer used inside employee transactions; relay worker
internal/search            indexer consumer; query adapter
internal/audit             consumer that inserts immutable rows
migrations/                SQL only

A manager change is one service method:

ChangeManager(actor, employeeID, newManagerID, version):
  authorize actor can change manager
  return inTransaction:
    emp = lockOrRead employeeID
    if emp.version != version: conflict
    mgr = read newManagerID
    reject if mgr missing, inactive, or emp.id
    reject if wouldCreateCycle(emp, mgr)
    update employee manager_id, version+1
    insert outbox MANAGER_CHANGED
    return emp
after commit:
  invalidate employee:{id}

Handlers never open transactions. Repositories never decide whether a cycle is legal. Tests hit the service with a fake repository for rules, and an integration database for the version WHERE clause.

Go fits this layout well: interfaces for repository and cache, concrete Postgres and Redis types, and standard net/http. The same layering works in any language. The point is the boundary, not the syntax.

27. Final architecture

Only now do the extra boxes earn their place:

 Client
   │
   ▼
API Gateway  (authn, request id, rate limit)
   │
   ▼
Employee / Department Service
   │
   ├── PostgreSQL   employees, departments, outbox
   ├── Redis        employee:{id}   (optional, cache-aside)
   └── invalidate on write

PostgreSQL outbox
   │
   ▼
Outbox worker
   │
   ▼
Kafka employee.events
   ├── Search indexer → OpenSearch
   └── Audit service  → employee_audit_logs

Read profile:

GET /employees/{id}
  → Redis
      hit  → authorize → return
      miss → PostgreSQL → fill Redis → return

Change manager:

PATCH manager
  → authorize
  → SQL transaction: rules + version + row + outbox
  → commit
  → invalidate cache
  → return version+1

Search:

GET /employees/search
  → OpenSearch
  → if down, degrade; never write here

PostgreSQL owns truth. Redis and OpenSearch speed up reads. Kafka moves committed facts. Audit is a consumer, not a second write in the HTTP request.

28. Interview-ready summary

Key decisions to remember

1. Scope the interview to directory, departments, hierarchy, search, and audit — not payroll.
2. PostgreSQL is the source of truth; 100,000 employees fit easily.
3. Soft-delete with ACTIVE / INACTIVE; never physically delete.
4. Optimistic locking on id + version; conflicts return 409.
5. Adjacency-list managers; prevent self, inactive, and cyclic assignments in the write transaction.
6. Walk subtrees with BFS; complexity follows subtree size, not the whole company.
7. Cache-aside Redis for GET by id; invalidate after commit; ignore Redis on the write path.
8. Write outbox events in the same transaction as the employee mutation.
9. Kafka consumers for search and audit are idempotent and lag-tolerant.
10. RBAC is enforced in the service layer.

Likely interviewer follow-up questions

• Why not delete the row on DELETE /employees/{id}?
• How do you prevent lost updates without locking the row for the whole HTTP call?
• How do you detect a manager cycle, and what is the complexity?
• Why not store the org tree as a nested JSON document?
• What happens if OpenSearch is down during create?
• Why not update Redis and PostgreSQL in one request without an outbox?
• How is audit immutable if it is just another table?
• How would you authorize a manager who should see skip-level reports?
• When would you replace adjacency list with a closure table?
• How do you rebuild the search index?

Senior-level points that differentiate the answer

• State that Kafka and OpenSearch are for access patterns and downstream consumers, not because SQL ran out of space.
• Put hierarchy rules and version checks in the same transaction as the row update.
• Distinguish source of truth, cache, search projection, and audit consumer.
• Treat dual-write as the reason for an outbox, not as a style preference.
• Make consumers idempotent because at-least-once delivery is the default.
• Fail closed on cycle detection and authorization; fail open on cache and search.
• Monitor commit-to-index lag, not only HTTP latency.
• Keep one service until a real ownership or scale boundary appears.

A 1–2 minute verbal answer

I would scope this to an internal employee directory: create, update, soft-deactivate, departments, manager assignment, direct reports, org subtree, search, and audit. At 100,000 employees the data fits in PostgreSQL. The hard parts are concurrent updates, hierarchy invariants, and keeping derived systems honest.

Employees are never physically deleted; status is ACTIVE or INACTIVE. Updates use optimistic locking on id and version and return 409 on conflict. manager_id is an adjacency list. In the same transaction I reject self-assignment, inactive managers, and cycles by walking the proposed manager chain. Direct reports are an indexed lookup; the organization endpoint BFS-walks the subtree.

PostgreSQL is the source of truth. The write transaction also inserts an outbox event. A worker publishes to Kafka; a search indexer upserts OpenSearch and an audit consumer inserts immutable rows, both idempotently. Redis caches employee:{id} with cache-aside and invalidate-on-write. If Redis, Kafka, or OpenSearch is down, CRUD continues. RBAC is enforced in the service: employees see themselves, managers see reports, HR mutates records.

For the broader interview framework around this problem, see the System Design Interview Complete Guide.