Building a Serverless Appointment Scheduler Using Twilio, Express, and AWS: Designing DynamoDB Appointments Table

5 min readMay 17, 2024

Secretary Writing Appointments in a Notebook: An image depicting a secretary sitting at a desk, writing appointments in a large, open notebook. The desk could be filled with office supplies like pens, a calendar, a phone, and a computer, symbolizing the organization and management of scheduling tasks.

In this article, we will implement a persistent mechanism using AWS DynamoDB as a NoSQL database. We’ll save information about callers, ensuring time zone considerations are taken into account. Once appointments are saved, we’ll check if the time slot is available for new callers who want to schedule an appointment.

Designing the Database Schema

To effectively manage appointment data and ensure efficient querying, we need to design our DynamoDB schema based on the following access patterns:

New users would like to know if they can schedule a new appointment at a given date and time in a specific time zone.
Callers would like to know when the soonest appointment is available.
Callers would like to know if there are any appointments available for a given date.

Appointments Table

Primary Key

Hash Key: Date (String) — The date of the appointment.
Range Key: Time (String) — The time of the appointment.

Additional attributes:

CallerID (String): Unique identifier for the caller.
TimeZone (String): Time zone of the appointment.
Status (String): Status of the appointment (e.g., “Scheduled”, “Cancelled”).

Indexes

Global Secondary Index (GSI):

Index Name: StatusIndex
Hash Key: Status (String) — Allows querying appointments by their status (e.g., to find all available or scheduled appointments).
Range Key: Date (String) — Enables sorting appointments by date within the status.

Local Secondary Index (LSI):

Index Name: TimeZoneIndex
Hash Key: Date (String) — Same as the primary hash key.
Range Key: TimeZone (String) — Allows querying appointments by time zone for a given date.

Global Secondary Index (GSI):

Index Name: CallerIndex
Hash Key: CallerID (String) — Query appointments by caller ID.
Range Key: Date (String) — Sort appointments by date within the caller.

Appointments Table Definition

We’ll add the DynamoDB table and indexes to our serverless.yml file as follows:

service: twilio-app

provider:
  name: aws
  runtime: nodejs18.x
  region: us-east-1
  profile: rocketeast

functions:
  app:
    handler: handler.app
    events:
      - http:
          path: /
          method: any
      - http:
          path: /{proxy+}
          method: any

resources:
  Resources:
    AppointmentsTable:
      Type: AWS::DynamoDB::Table
      Properties:
        TableName: Appointments
        AttributeDefinitions:
          - AttributeName: Date
            AttributeType: S
          - AttributeName: Time
            AttributeType: S
          - AttributeName: Status
            AttributeType: S
          - AttributeName: TimeZone
            AttributeType: S
          - AttributeName: CallerID
            AttributeType: S
        KeySchema:
          - AttributeName: Date
            KeyType: HASH
          - AttributeName: Time
            KeyType: RANGE
        LocalSecondaryIndexes:
          - IndexName: TimeZoneIndex
            KeySchema:
              - AttributeName: Date
                KeyType: HASH
              - AttributeName: TimeZone
                KeyType: RANGE
            Projection:
              ProjectionType: ALL
        GlobalSecondaryIndexes:
          - IndexName: StatusIndex
            KeySchema:
              - AttributeName: Status
                KeyType: HASH
              - AttributeName: Date
                KeyType: RANGE
            Projection:
              ProjectionType: ALL
          - IndexName: CallerIndex
            KeySchema:
              - AttributeName: CallerID
                KeyType: HASH
              - AttributeName: Date
                KeyType: RANGE
            Projection:
              ProjectionType: ALL
        ProvisionedThroughput:
          ReadCapacityUnits: 1
          WriteCapacityUnits: 1

Explanation of Design Decisions

Primary Key (Date + Time):

This combination uniquely identifies each appointment and allows efficient querying for specific dates and times.

Local Secondary Index (LSI) on TimeZone:

Hash Key: Date — Keeps the partition key the same for efficient querying.
Range Key: TimeZone — Enables querying appointments by time zone within a specific date, useful for time zone-specific queries.

Global Secondary Index (GSI) on Status:

Hash Key: Status — Allows querying all appointments by their status, such as finding all scheduled or available appointments.
Range Key: Date — Allows sorting appointments by date within a status, which is useful for determining the soonest available appointment.

By designing the schema with these keys and indexes, we ensure efficient querying and flexibility to handle different appointment-related queries.

Global Secondary Index (GSI) on CallerID:

Hash Key: CallerID — Enables querying all appointments for a specific caller.
Range Key: Date — Allows sorting by date within a caller’s appointments.

Conclusion

In this article, we designed a robust schema for our DynamoDB database to handle various appointment-related queries efficiently. By carefully considering access patterns and choosing the appropriate indexes, we ensured that our system can handle queries for scheduling, status checking, and caller-specific appointments effectively.

Using the right tools at the right time is crucial. We started with a quick and simple local setup, then transitioned to a scalable, managed AWS infrastructure. This approach allows us to leverage Amazon’s capabilities to scale our application infinitely, ensuring reliability and performance. By following these steps, we can build a powerful, scalable scheduling system that meets the needs of our users efficiently.

What’s Next?

In the next article, we will design the persistent layer and add interactions with DynamoDB to:

1. Retrieve the list of available spots for a given date.

2. Create and save new appointments.

3. Test our changes locally using serverless-offline.

Stay tuned as we continue to build a comprehensive scheduling system that leverages serverless architecture for maximum efficiency and scalability.

On this series:

Initial Configuration: This article covers setting up a phone number for clients to schedule meetings is straightforward with the right tools. This guide uses Twilio for phone number management and AWS for hosting in a Serverless Architecture. For local development and testing, we’ll use Express and ngrok.
AWS API Capturing User’s Interactions: we explored how to collect user input from a phone conversation using the twilio package and migrate our local Express application to a serverless architecture using AWS Lambda, API Gateway, and the Serverless Framework.

Empower Your Tech Journey:

Explore a wealth of knowledge designed to elevate your tech projects and understanding. From safeguarding your applications to mastering serverless architecture, discover articles that resonate with your ambition.

New Projects or Consultancy

For new project collaborations or bespoke consultancy services, reach out directly and let’s transform your ideas into reality. Ready to take your project to the next level?

Email me at one@upskyrocket.com
Visit My Partner In Tech for custom solutions

Protecting Routes With AWS

Mastering Serverless Series

Advanced Serverless Techniques