routes plan

routes plan.md

API Routes Plan

User

Note: /my/* routes requires cognito user token

GET /users # Return list of users, for admin purpose only

GET /users/:userId/profile # For viewing other's profile

GET /my/profile

PATCH /my/profile

GET /users/:userId/pets

GET /my/pets

POST /my/pets # Add new pet

GET /users/:userId/pets/:petId

GET /my/pets/:petId

PATCH /my/pets/:petId

DELETE /my/pets/:petId

GET /users/:userId/services # Return list of services of a user provided

POST /my/services

GET /users/:userId/services/:serviceId

PATCH /my/services/:serviceId

DELETE /my/services/:serviceId

GET /my/settings

PATCH /my/settings

GET /me # Aggregation of user infos for 我的 page, may have some overlap between the above endpoints

Service

GET /service-types # Return all available service types, for Landing Page 找服務

GET /services # Return list of services, support queries filter, for searching services in Top 10, 台中市服務, 離你最近的服務

GET /services/:serviceId # Return service details

implement 喜歡的行程 in local storage, no endpoint

Chat

GET /channels # Return list of chat sessions of a user

POST /channels # Create a new chat session with another user

GET /channels/:channelId # Return details of a specific chat session, including last few messages?

DELETE /channels/:channelId # Just remove the current user from the participants table

GET /channels/:channelId/messages # Return all messages in a specific chat session, use cursor based pagination, only participants are allowed to get the messages

POST /channels/:channelId/messages # Send a new message in a specific chat session, only participants are allowed to create new message

DELETE /messages/:messageId # Delete a specific message, only the sender can remove the message

sample_queries.md

Sample queries

• the erd is generated for ref, not accurate

Retrieve all appointments of a user

Can join appointment_services for calculating the cost of the appointment, or use application join with redis cache

SELECT * FROM appointments WHERE user_id = ?;

Retrieve all appointments of a service provider

SELECT * FROM appointments WHERE provider_id = ?;

Given a start_time, end_time and a service_provider_id, determine whether there is a time conflict with the existing appointments

SELECT COUNT(*) 
FROM appointment_services AS aps
JOIN appointments AS a ON aps.appointment_id = a.id
WHERE a.provider_id = ? 
AND aps.start_time <= ? AND aps.end_time > ?;

schema.dbml

// Cognito JWT example, which is the source of the data for the users table
// https://repost.aws/knowledge-center/decode-verify-cognito-json-token
// {
//   "sub": "aaaaaaaa-bbbb-cccc-dddd-example",
//   "aud": "xxxxxxxxxxxxexample",
//   "email_verified": true,
//   "token_use": "id",
//   "auth_time": 1500009400,
//   "iss": "https://cognito-idp.ap-southeast-2.amazonaws.com/ap-southeast-2_example",
//   "cognito:username": "anaya",
//   "exp": 1500013000,
//   "given_name": "Anaya",
//   "iat": 1500009400,
//   "email": "anaya@example.com"
// }

Table users {
  id varchar [pk]
  username varchar // cognito:username
  name varchar // given_name
  email varchar
  email_verified boolean
  status int [default: 1] // 0: inactive, 1: active

  created_at timestamp [default: `now()`]
  updated_at timestamp

  Indexes {
    username [unique]
    email [unique]
  }
}

Table service_providers {
  id varchar [pk]
  user_id varchar [ref: - users.id]
  status int [default: 0] // 0: pending, 1: approved, 2: banned

  created_at timestamp [default: `now()`]
  updated_at timestamp

  Indexes {
    user_id [unique]
  }
}

Table service_types {
  id int [pk, increment]
  name varchar [unique] // e.g. walking, beauty care, etc.
}

// a user can provide multiple services, 
// each service has a duration and price for estimating the appointment free
Table services {
  id int [pk, increment]
  service_type_id int [ref: > service_types.id]
  service_provider_id varchar [ref: > service_providers.id]
  name varchar
  duration int // in minutes
  price decimal // decimal(10,2)

  Indexes {
    service_type_id
    service_provider_id
  }
}

Table appointments {
  id int [pk, increment]
  user_id varchar [ref: > users.id]
  provider_id varchar [ref: > service_providers.id]

  // payment status
  status int [default: 0] // 0: pending, 1: confirmed, 2: completed, 3: cancelled

  created_at timestamp [default: `now()`]
  updated_at timestamp

  Indexes {
    user_id
    provider_id
  }
}

// an appointment can have multiple services, and a service can be in multiple appointments
// each service in an appointment has a start and end time
Table appointment_services {
  appointment_id int [ref: > appointments.id]
  service_id int [ref: > services.id]
  start_time timestamp
  end_time timestamp

  Indexes {
    (appointment_id, service_id) [unique] // pk
  }
}

Table profiles {
  id int [pk]
  user_id varchar [ref: - users.id]
  first_name varchar(100)
  last_name varchar(100)
  phone_number varchar(15)
  address varchar(255)

  Indexes {
    user_id [unique]
  }
}

Table pets {
  id int [pk]
  user_id varchar [ref: > users.id]

  // temp fields
  name varchar(100)
  type varchar(50)
  breed varchar(50)
  age int

  Indexes {
    user_id
  }
}

Table settings {
  id int [pk]
  user_id varchar [ref: - users.id]
  // notification boolean
  // language varchar(50)

  Indexes {
    user_id [unique]
  }
}

Table channels {
  id int [pk]
}

Table participants {
  id int [pk]
  channel_id int [ref: > channels.id]
  user_id varchar [ref: > users.id]

  Indexes {
    (channel_id, user_id) [unique]
  }
}

Table messages {
  id int [pk]
  channel_id int [ref: > channels.id]
  sender_id varchar [ref: > users.id]
  message text
  timestamp datetime

  Indexes {
    channel_id
    sender_id
  }
}