For Episode 123 of the CollabTalk Podcast, we explored the pivotal role of community in shaping businesses, discussing my guest’s founding of his company and the strategies for building and nurturing open-source communities. We covered the symbiosis between commercial success and community engagement, emphasizing the importance of community feedback in innovation and the challenges and benefits of integrating open-source models into business strategies. You can listen to the podcast above and follow me using your favorite app, such as Spotify, Apple Podcasts, Stitcher, Soundcloud, or the iHeartRadio app. Be sure to subscribe!

Our task today is to request (and obtain approval for) a vacation. But before we can make that request, we need to handle the challenge of building   the vacation requesting system. Along the way, I want to focus a little bit on how to deal with some of the technical issues that may arise, such as concurrency.

In most organizations, the actual details of managing employee vacations are a mess of a truly complicated series of internal policies, labor laws, and individual contracts. For this post, I’m going to ignore all of that in favor of a much simplified workflow.

An employee may Request a Vacation, which will need to be approved by their manager. For the purpose of discussion, we’ll ignore all other aspects and set out to figure out how we can create a backend for this system.

I’m going to use a relational database as the backend for now, using the following schema. Note that this is obviously a highly simplified model, ignoring many real-world requirements. But this is sufficient to talk about the actual issue.

After looking at the table structure, let’s look at the code (again, ignoring data validation, error handling, and other rather important concerns).

app.post('/api/vacations/request', async (req, res) => {
    const { employeeId, dates, reason } = req.body;


    await pgsql.query(`BEGIN TRANSACTION;`);
    const managerId = await pgsql.query(
      `SELECT manager FROM Employees WHERE id = $1;`,
      [employeeId]).rows[0].id;
    const vacReqId = await pgsql.query(
      `INSERT INTO VacationRequests (empId,approver,reason,status)
       VALUES ($1,$2,$3,'Pending') RETURNING id;`,
       [employeeId,managerId,reason]).rows[0].id;


    for(const date of date) {
        await pgsql.query(
          `INSERT INTO VacationRequestDates
           (vacReqId, date, mandatory ,notes)
           VALUES ($1, $2, $3, $4);`, 
          [vacReqId, d.date, d.mandatory, d.notes]);
    }
     
    await pgsql.query(`COMMIT;`);


    res.status(201).json({ requestId: result.rows[0].id });
});

We create a new transaction, find who the manager for the employee is, and register a new VacationRequest for the employee with all the dates for that vacation. Pretty simple and easy, right? Let’s look at the other side of this, approving a request.

Here is how a manager is able to get the vacation dates that they need to approve for their employees.

app.get('/api/vacations/approval', async (req, res) => {
  const { whoAmI } = req.body;
 
  const vacations = await pgsql.query(
    `SELECT VRD.id, VR.empId, VR.reason, VRD.date, E.name,
           VRD.mandatory, VRD.notes
    FROM VacationRequests VR
    JOIN VacationRequestDates VRD ON VR.id = VRD.vacReqId
    JOIN Employees E ON VR.empId = E.id
    WHERE VR.approver = $1 AND VR.status = 'Pending'`,
    [whoAmI]);


  res.status(200).json({ vacations });
});

As you can see, most of the code here consists of the SQL query itself. We join the three tables to find the dates that still require approval.

I’ll stop here for a second and let you look at the two previous pieces of code for a bit. I have to say, even though I’m writing this code specifically to point out the problems, I had to force myself not to delete it. There was mental pressure behind my eyes as I wrote those lines.

The issue isn’t a problem with a lack of error handling or security. I’m explicitly ignoring that for this sort of demo code. The actual problem that bugs me so much is modeling and behavior.

Let’s look at the output of the previous snippet, returning the vacation dates that we still need to approve.

We have three separate entries that we need to approve, but notice that even though two of those vacation dates belong to the same employee (and are part of the same vacation request), they can be approved separately. In fact, it is likely that the manager will decide to approve John for the 1st of August and Jane for the 2nd, denying John’s second vacation day. However, that isn’t how it works. Since the actual approval is for the entire vacation request, approving one row in the table would approve all the related dates.

When examining the model at the row level, it doesn’t really work. The fact that the data is spread over multiple tables in the database is an immaterial issue related to the impedance mismatch between the document model and the relational model.

Let’s try and see if we can structure the query in a way that would make better sense from our perspective. Here is the new query (the rest of the code remains the same as the previous snippet).

SELECT VRD.id, VR.empId, E.name, VR.reason,
    (
        SELECT json_agg(VRD)
        FROM VacationRequestDates VRD
        WHERE VR.id = VRD.vacReqId
    ) AS dates
FROM VacationRequests VR
JOIN Employees E ON VR.empId = E.id
WHERE VR.approver = $1 AND VR.status = 'Pending'

This is a little bit more complicated, and the output it gives is quite different. If we show the data in the same way as before, it is much easier to see that there is a single vacation request and that those dates are tied together.

We are going to ignore the scenario of partial approval because it doesn’t matter for the topic I’m trying to cover. Let’s discuss two other important features that we need to handle. How do we allow an employee to edit a vacation request, and how does the manager actually approve a request.

Let’s consider editing a vacation request by the employee. On the face of it, it’s pretty simple. We show the vacation request to the employee and add the following endpoint to handle the update.

app.post('/api/vacation-request/date', async (req, res) => {
  const { id, date, mandatory, notes, vacReqId } = req.body;
 
 if(id typeof == 'number') {
  await pgsql.query(
    `UPDATE VacationRequestDates
    SET date = $1, mandatory = $2, notes = $3
    WHERE id = $4`,
    [date, mandatory, notes, id]);
 }
 else {
  await pgsql.query(
    `INSERT INTO VacationRequestDates (date, mandatory, notes, vacReqId)
    VALUES ($1, $2, $3, $4)`,
    [date, mandatory, notes, vacReqId]);
 }
 
  res.status(200);
});


app.delete('/api/vacation-request/date', async (req, res) => {
  const { id } = req.query;
 
  await pgsql.query(
    `DELETE FROM VacationRequestDates WHERE id = $1`,
    [id]);


  res.status(200);
});

Again, this sort of code is like nails on board inside my head. I’ll explain why in just a bit. For now, you can see that we actually need to handle three separate scenarios for editing an existing request date, adding a new one, or deleting it. I’m now showing the code for updating the actual vacation request (such as the reason for the request) since that is pretty similar to the above snippet.

The reason that this approach bugs me so much is because it violates transaction boundaries within the solution. Let’s assume that I want to take Thursday off instead of Wednesday and add Friday as well. How would that be executed using the current API?

I would need to send a request to update the date on one row in VacationRequestDates and another to add a new one. Each one of those operations would be its own independent transaction. That means that either one can fail. While I wanted to have both Thursday and Friday off, only the request for Friday may succeed, and the edit from Wednesday to Thursday might not.

It also means that the approver may see a partial state of things, leading to an interesting problem and eventually an exploitable loophole in the system. Consider the scenario of the approver looking at vacation requests and approving them. I can arrange things so that while they are viewing the request, the employee will add additional dates. When the approver approves the request, they’ll also approve the additional dates, unknowingly.

Let’s solve the problem with the transactional updates on the vacation request and see where that takes us:

app.post('/api/vacation-request/update', async (req, res) => {
  const { varRecId, datesUpdates } = req.body;
  await pgsql.query(`BEGIN TRANSACTION;`);


  for (const { op, id, date, mandatory, notes } of datesUpdates) {
    if (op === 'delete') {
      await pgsql.query(`DELETE FROM VacationRequestDates
        WHERE id = $1;`,
        [id]);
    }
    else if (op === 'insert') {
      await pgsql.query(`INSERT INTO VacationRequestDates
        (varRecId, date, mandatory, notes)
        VALUES ($1, $2, $3, $4);`,
        [varRecId, date, mandatory, notes]);
     
    }
    else {
      await pgsql.query(`UPDATE VacationRequestDates
        SET date = $1, mandatory = $2, notes = $3
        WHERE id = $4;`,
        [date, mandatory, notes, id]);
    }
  }


  await pgsql.query(`COMMIT;`);
  res.status(200);
});

That is… a lot of code to go through. Note that I looked into Sequelize as well to see what kind of code that would produce when using an OR/M, it wasn’t meaningfully simpler.

There is a hidden bug in the code above. But you probably won’t notice it no matter how much you’ll look into it. The issue is code that isn’t there. The API code above assumes that the caller will send us all the dates for the vacation requests, but it is easy to get into a situation where we may edit the same vacation requests from both the phone and the laptop, and get partial information.

In other words, our vacation request on the database has four dates, but I just updated three of them. The last one is part of my vacation request, but since I didn’t explicitly refer to that, the code above will ignore that. The end result is probably an inconsistent state.

In other words, to reduce the impedance mismatch between my database and the way I work with the user, I leaned too much toward exposing the database to the callers. The fact that the underlying database is storing the data in multiple tables has leaked into the way I model my user interface and the wire API. That leads to a significant amount of complexity.

Let’s go back to the drawing board. Instead of trying to model the data as a set of rows that would be visually represented as a single unit, we need to actually think about a vacation request as a single unit.

Take a look at this image, showing a vacation request form. That is how the business conceptualizes the problem: as a single cohesive unit encompassing all the necessary data for submitting and approving a vacation request.

Note that for real systems, we’ll require a lot more data, including details such as the actual vacation days taken, how they should be recorded against the employee’s leave allowance, etc.

The important aspect here is that instead of working with individual rows, we need to raise the bar and move to working with the entity as a whole. In modeling terms, this means that we won’t work with rows but with Root Aggregate (from DDD terminology).

But I already have all of this code written, so let’s see how far I can push things before I even hit my own limits. Let’s look at the code that is required to approve a vacation request. Here is the first draft I wrote to do so.

app.post('/api/vacation-request/approve', async (req, res) => {
  const { varRecId, approver, status } = req.body;


  const res = await pgsql.query(`UPDATE VacationRequests
   SET status = $1 WHERE id = $2 and approver = $3;`,
    [status, varRecId, approver]);
 
  if (res.rowCount == 0) {
    res.status(400)
      .send({ error: 'No record found or wrong approver' });
  }


  res.status(200);
});

Which will give me the vacation requests that I need to approve:

And then I actually approve it using:

POST /api/vacation-request/approve
{"varRecId": 8483, "approver": 9341, "status": "Approved"}

What is the problem now? Well, what happens if the employee modifies the vacation request between the two requests? The approver may end up approving the wrong details. How do we fix that?

You may think that you can use locking on the approve operation, but we actually have just a single statement executed, so that doesn’t matter. And given that we have two separate requests, with distinct database transactions between them, that isn’t even possible.

What we need to implement here is called Offline Optimistic Concurrency. In other words, we need to ensure that the version the manager approved is the same as the one that is currently in the database.

In order to do that, we need to modify our schema and add a version column to the VacationRequests table, as you can see in the image.

Now, any time that I make any modification on the VacationRequest, I must also increment the value of the Version field and check that it matches my expected value.

Here is an example of how this looks like when the Employee is adding a new date to the vacation request. I shortened the code that we previously looked at to update a vacation request, so you can more clearly see the changes required to ensure that changes in the request will be detected between requests.

app.post('/api/vacation-request/insert-date', async (req, res) => {
  const { varRecId, version,  } = req.body;
  await pgsql.query(`BEGIN TRANSACTION;`);


  const res = await pgsql.query(`UPDATE VacationRequests
   SET version = version + 1
    WHERE id = $1 and version = $2;`,
    [varRecId, version]);


  if (res.rowCount == 0) {
    res.status(400)
      .send({ error: 'No record found or wrong version' });
  }


  await pgsql.query(`INSERT INTO VacationRequestDates
        (varRecId, date, mandatory, notes)
        VALUES ($1, $2, $3, $4);`,
    [varRecId, date, mandatory, notes]);


  await pgsql.query(`COMMIT;`);
  res.status(200);
});

And on the other side, approving the request is now:

app.post('/api/vacation-request/approve', async (req, res) => {
  const { varRecId, approver, version, status } = req.body;


  const res = await pgsql.query(`UPDATE VacationRequests
   SET status = $1 and version = version + 1
   WHERE id = $2 and approver = $3 and version = $4;`,
    [status, varRecId, approver, version]);
 
  if (res.rowCount == 0) {
    res.status(400)
      .send({ 
         error: 'No record found or wrong approver or version'
       });
  }


  res.status(200);
});

We need to send the version to the client when we read it, and when we approve it, we need to ensure that we send the version back, to verify that there have been no changes.

I have to say, given that I set out to do something pretty simple, I’m actually shocked at how complex this all turned out to be. The solution above also requires cooperation from all entities. If I’m ever writing some code that modifies the vacation requests or manages them manually (for maintenance purposes, debugging, etc) I need to also remember to include the version updates.

When I started writing this blog post, I intended to also show you how you can model the same situation differently. But I think that this is quite long enough already, and I’ll complete the proper modeling concerns in the next post.