Table of Contents

• Appointment Booking Challenge
• Overview
• Requirements
• NOTES:
• Database Schema
• Table: sales_managers
• Table: slots
• Getting Started
• Setup the database
• Setup tests
• Start coding
• How to submit the solution
• Evaluation Criteria
• Solution
• How to launch
• Used framework
• The main task
• DB optimization
• Other assumptions

Appointment Booking Challenge

Overview

We want to build an appointment booking system that allows customers to schedule appointments with our sales managers to discuss one or more of our products. For the MVP, we will have a website that displays available appointment slots that a customer can choose from.

The goal of this project is to implement the backend for this system. We need an endpoint that returns the available appointment slots for a customer.

There are a few rules we need to consider when checking for available appointment slots for a customer:

• Each slot corresponds to a one-hour appointment
• Slots can have overlapping time ranges. For example, it is possible to have the following three slots:
  • 10:30 - 11:30
  • 11:00 - 12:00
  • 11:30 - 12:30
• A sales manager CANNOT be booked for two overlapping slots at the same time. For example, if a sales manger has a slot booked at 10:30 - 11:30, then the 11:00 - 12:00 cannot be booked anymore.
• Customers are matched to sales managers based on specific criteria. A slot CANNOT be booked by a customer if the sales manager does not match any of these three criteria:
  • Language. Currently we have 2 possible languages: German, English
  • Product(s) to discuss. Currently we have 2 possible products: SolarPanels, Heatpumps
  • Internal customer rating. Currently we have 3 possible ratings: Gold, Silver, Bronze.
• Customers can book one appointment to discuss multiple products

Requirements

Design and implement a REST endpoint in any language of your choice that:

• Listens for POST requests on this route: http://localhost:3000/calendar/query
• Connects to the provided Postgres database instance
• Receives a request body in this format:

  {
    "date": "2024-05-03",
    "products": ["SolarPanels", "Heatpumps"],
    "language": "German",
    "rating": "Gold"
  }
  

• Returns a response with an array of available slots that can be booked by the customer in this format

  [
    {
      "available_count": 1,
      "start_date": "2024-05-03T10:30:00.00Z"
    },
    {
      "available_count": 2,
      "start_date": "2024-05-03T12:00:00.00Z"
    }
  ]
  

NOTES:

• You can use any language, framework, library of your choice for this challenge.
• The system should not book appointments in this challenge; your focus is returning available slots.
• We provide you with a docker database already populated with data. You are not allowed to modify the database structure of the database in any way but you can create indexes or views if you think it is necessary. The docker database can be downloaded here: https://[redacted]/Take_Home_Challenge_Resources.zip

Database Schema

The provided database has the following schema:

Table: sales_managers

Column Name	Column Type	Comment
id (PK)	serial	ID of the sales manager
name	varchar(250)	Full name of sales manager
languages	array(varchar(100))	List of languages spoken by sales manager
products	array(varchar(100))	List of products the sales manager can work with
customer_ratings	array(varchar(100))	List of customer ratings the sales manager can work with

Table: slots

Column Name	Column Type	Comment
id (PK)	serial	ID of the slot
start_date	timestampz	Start date and time of the slot
end_date	timestampz	End date and time of the slot
booked	bool	Value indicating whether the slot has already been booked
sales_manager_id (FK)	integer	ID of the sales manager the slot belongs to

Getting Started

Download the resources for this challenge here https://[redacted]/Take_Home_Challenge_Resources.zip. This is a zip file that contains two folders:

• database: This folder contains a Dockerfile that can be used to start a Postgres database server and an init.sql file that initializes the database and preloads it with data.
• test-app: This folder contains a node application that can be used to run test scenarios to verify your application.

Setup the database

Extract the Take_Home_Challenge_Resources.zip file and run the following commands in the database folder. This requires that you have docker installed on your local environment

docker build -t enpal-coding-challenge-db .
docker run --name enpal-coding-challenge-db -p 5432:5432 -d enpal-coding-challenge-db

Once the docker container is up and running, ensure you can connect to it using your favourite DB query tool (e.g.: DBeaver or pgAdmin).

The default connection string is postgres://postgres:mypassword123!@localhost:5432/coding-challenge

If you want to use a local database installation instead, you also can get the init.sql file and run it in your local database.

Setup tests

Extract the Take_Home_Challenge_Resources.zip file and run the following commands in the test-app folder. This requires that you have node installed on your local environment

npm install
npm run test

The tests try to connect to an endpoint running on http://localhost:3000/calendar/query and run several test scenarios. Since that is not probably running yet the tests will fail.

You can inspect the test.js file and see some example requests and the expected responses.

Start coding

You can now create an api in your language of choice that fulfils the requirements.

How to submit the solution

The solution should contain:

• your application code
• docker setup that starts the database
• instructions on how to run your application.

You can send this to us as a zip file or push this to a github repository and send us the link.

Your solution must connect to the database in the docker container and then we’ll run the same tests provided to you. We might ALSO run additional tests, such as loading thousands of records in the database to assert the application is performant enough.

Evaluation Criteria

Please note that we will be evaluating your solution not just based on correctness but also on the following criteria. We place a high importance on these criteria, and we strongly encourage you to carefully consider them as you develop your solution:

• Accuracy in adhering to the specified rules.
• Efficiency and performance of the api endpoint.
• Clarity, readability and testability of the code.
• Handling of edge cases and error conditions.

Solution

How to launch

You'll need Node.js and npm to launch it, but then the tests that came with the challenge already required both Node.js and npm.

Solution is in service/; commands are:

• npm ci, to install dependencies;
• npm run start, to typecheck and start the backend (optional PORT and PG_CONNECTION_STRING environment variables are supported, e.g. PORT=4000 PG_CONNECTION_STRING=/run/postgresql npm run start; they default to port and connection string specified in the challenge);
• npm run lint, to lint the backend;
• npm run test, to run unit and integration tests (actually there are no useful tests in this backend right now, because the only interesting thing in it is one huge SQL query);
• npm run test:e2e, to run end-to-end tests, (it too supports optional PG_CONNECTION_STRING environment variable) (there are no useful e2e tests right now as well).

Used framework

I had to spend most of the time on creating an API project and writing boilerplate rather than actually solving the interesting part. Since the challenge says that I can use any language or framework for this challenge, and since it already has tests written in Node.js, I decided to go with Nest.js (+ TypeScript) because it provides somewhat reasonable defaults, and allows one to get to actually implementing the interesting part reasonably fast (although still too slow).

All request validation is done using standard Nest.js approach. If the database is not available at the startup, it will fail.

The main task

Producing the list of matching slots seems like a task for DB (especially with the given DB structure), so everything is happening in one huge query in service/src/db/index.ts.

DB optimization

The challenge also says that I can add indexes to the existing DB structure, and that you are going to evaluate the solution based on efficiency and performance. However, in order to make decisions regarding efficiency and performance, one has to know the actual use cases, and the actual distribution of the data. 20 rows and vague descriptions in the challenge are simply not enough to guess how exactly should the solution scale, and in which directions.

That said, I made several assumptions about the future state of the data, as follows:

• There will not be a lot of managers, so doing full scan on managers table for every query (trying to find suitable managers) is not a performance issue. Even if in the future there will be millions of entries in managers table, presumably not all of them will actually be active (and presumably we're only interested in the slots for the future dates), so perhaps adding some kind of is_active field and indexing by it will provide us with better specificity than indexing by languages / customer ratings / product types. So I did not add any indexes to the managers table.
• Another related assumption is that there will only be a few active managers matching language and product type and customer rating at once.
• For slots, I assumed that there will be a lot of slots per manager; and that old slots are not going to be removed from DB (even the booked ones). So I added an index on manager id + booked flag + start date, to allow for fast retrieval of all free slots for a handful of matching managers for the specified date.
• Since the challenge says that each slot corresponds to one-hour appointment, I assumed that slots can overlap but never be contained in each other. This allowed me to simplify the check whether a overlaps with b to just (a.start_date <= b.start_date < a.end_date) or (a.start_date < b.end_date <= b.end_date), so in order to check if there are any slots overlapping with a, it is enough to just find all slots with either start_date or end_date in a certain range. I added an additional index on manager id + booked flag + end date, meaning that to answer the question "does this free slot overlap with any booked slots of the same manager, it is enough to only query two indexes (and ensure that the results are empty), without having to do a full scan on anything.

Ultimately, I added two indexes (both defined in database/init.sql) which should allow this solution to scale to large number of slots (but still limited number of managers).

Other assumptions

And finally, assumptions about dates:

• The challenge does not say anything about timezones, so I decided not to bother with them either. This would be unacceptable in a production-ready project, but for production-ready project, one would have at least accept the timezone in request, and not just "YYYY-MM-DD" string which doesn't mean a lot by itself. So this solution might contain some timezone-related bugs... but technically they are not bugs, because they still will not violate any requirements in the challenge.
• The challenge does not say anything about the meaning of the requested date. So I decided to treat it as specifying the range for the slot start date (e.g. time slot from 2025-01-12 23:30 to 2025-01-13 00:30 will be returned for requests for 2025-01-12, but not for 2025-01-13).