acmucsd-sp20-backend-assessment.md

Hi, thanks for your interest in ACM's backend development team! Your task is to write a design doc for a "quest system" that integrates into the membership portal and then schedule an interview that'll be some mix of design review, technical, and behavioral.

First you'll need to understand how the portal works at the API and database layers—the portal doesn't currently have a service layer—and then design appropriate API and database changes for the required functionality. Your priority should be a working design, not a clever one; bonus points if you explain why yours is definitively the best or if you consider multiple designs and explain the tradeoffs of each. There's no right answer and this is an exercise intended to see how you approach some functionality you'd reasonably be expected to implement as a backend developer for ACM—there's no limit to how deep your changes to the portal can go. The portal's being completely rewritten so don't worry about any current constraints (e.g. what you can do with the models in the portal currently, duplicating logic that should be abstracted away in the service layer that the portal doesn't yet have) and don't worry about syntax.

Feel free to DM Sumeet (shmet#9694) on Discord with questions about the quest system. Your design doc should bear a passing resemblance to the sample and include

1. any modified/added API routes, including permission level (PUBLIC, USER, ADMIN), HTTP method, and REST route. I used Express syntax where :uuid? means uuid is an route parameter (denoted by the preceding colon) and the question mark means it's optional.
2. request/response types for all routes (see CreateOrderRequest for an example). I used a TypeScript-ish syntax.
3. basic database models (including column names, data types, additional modifiers e.g. default values or nullable).
4. any database indexes for optimization and justifications for each.
5. any further database migrations.
6. any other relevant implementation details (e.g. using in-memory data structures or any database logic more complex than a simple SELECT).
7. some feedback on this exercise! I promise anything you say here won't be factored into our decision. Some guiding questions (but any comments are appreciated): How long did it take you (understanding the portal, writing the design doc)? What did you struggle with? Was this unreasonably hard? Do you think this is a fair assessment of your backend abilities?

Required Functionality

• a quest is a set of events
• if a user attends all the events in a quest, they automatically complete the quest (e.g. all Hack School events in a quarter)
• if a user completes a quest, they are awarded some bonus points and a badge
• badges can be awarded for things besides quests (don't worry about creating API routes for badges, just add them to the database for the quest system)

sample-design-doc.md

Merch Store Design Doc

Required Functionality

• for items, create/read/update/delete
• as a user, place an order
• as a user, get all past orders including fulfillment information
• as an admin, get all past orders for users
• as an admin, mark items as fulfilled

API Changes

/api/v1/store/item

• [user] GET /:uuid? (if uuid is given, return that specific item; else return all store items)
• [admin] POST / (create an item)
• [admin] DELETE /:uuid (delete an item)
• [admin] PATCH /:uuid (update an item; increment quantity instead of directly updating to avoid concurrency issues)

/api/v1/store/order

• [user] GET /:uuid? (if uuid is given, return that specific order; else return all orders for a user or all orders for all users if admin)

OrderItem {
  uuid: uuid;
  item: uuid;
  fulfilled: boolean;
  fulfilledAt: timestamp;
}
Order {
  uuid: uuid;
  totalCost: integer;
  orderedAt: timestamp;
  items: OrderItem[];
}
GetOneOrderResponse {
  order: Order
}
GetAllOrderResponse {
  orders: Order[]
}

• [user] POST / (place an order)

CreateOrderItem {
  item: uuid;
  quantity: integer;
}
CreateOrderRequest {
  order: CreateOrderItem[];
}
CreateOrderResponse {
  order: uuid;
}

database: create an Order → create 1 OrderItem per item (e.g. 3 OrderItems for an order with 2 shot glasses and a shirt) + 1 Activity of type ORDER_MERCHANDISE

• [admin] PATCH / (mark items as fulfilled, fails if some items are already fulfilled)

FulfillOrderRequest {
  items: uuid[]; // OrderItem UUIDs
}
FulfillOrderResponse {
  isFulfilled: integer; // number of fulfilled order items
}

database: get all OrderItem by uuid → throw an error if any are fulfilled, else mark each as fulfilled

Database Changes

MERCHANDISE

• id: incrementing int
• uuid: uuid
• itemName: string
• price: int (default 0)
• quantity: int (default 0)
• description: string
• discountPercentage: int (default 0)
• hidden: boolean (default true)

indexes:

• unique BTREE on uuid
• partial BTREE on discountPercentage DESC where discountPercentage > 0 (for getting all discounted items)

ORDERS

• id: incrementing id
• uuid: uuid
• user: uuid
• total_cost: int
• ordered_at: timestamp

indexes:

• unique BTREE on uuid
• BTREE on user (name "orders_per_user_index") (for getting all orders for a user)
• BTREE on orderedAt DESC (name "recent_orders_index")

ORDER_ITEMS

• id: incrementing id
• order: uuid
• item: uuid
• fulfilled: boolean (default false)
• fulfilled_at: timestamp (can be null)

indexes:

• unique BTREE on uuid (for ...)
• BTREE on order (name "items_per_order_index")

Other Migrations

• split user points into total points and spendable credits (diamonds)
• add activity type