Database

Pinpoint's primary database is AWS DynamoDB, a managed non-relational database by Amazon.

Schema

The plan is to have a primary table for the Pinpoint application, and a separate table for each club.

The primary table, tentatively named ClubsAndUsers, will contain:

• users
• clubs
• event templates

Each club will have a separate table attributed to it. This will contain:

• applicants
• data from events (associated to applicants)
• tag definitions
• tags (associated to applicants)

Some more details about the database schema design can be found in #12 and its corresponding PR #58

Data

Events

generic “entries” e.g.

• a form (filled by applicant)
• a resume review (notes by exec)
• an interview (notes by exec)
• dietary restrictions (filled by applicant) structure (example) (what the user fills in here will go into the applicant example below under data.application_form)

{
  name: application_form
  fields: {
    position:  {
      type: enum
      values: [position_a, position_b, position_c]
      set_tag: true
    }
    written_response_1:  {
      type: long_response
    }
    resume:  {
      type: link
    }
    github:  {
      type: link
    }
  }
}

Application Period:

a bunch of “events” that make up a period e.g. same as above “applicants” are collections of all events are unique to a period

e.g. applicant Jenny filled form, had a resume review done by exec, had an interview

this example does not reflect the database, things like the entries in tags and data will probably their own documents, with primary key as the email ID

{
   name: jeny
   email_id: [email protected]
   period: fall_recruitment
   tags: [
      position_b
      vegan
      accepted
   ]
   data: {
      applicaiton_form: { stuff from form }
      resume_review: { stuff from resume review notes }
      interview_1: { stuff from interview notes }
   }
}

Development

To start up a local DynamoDB instance, make sure you have Docker and docker-compose installed and run:

$> make testenv

The DynamoDB API will be available at http://localhost:8000. Alternatively, you can try out dynamon, a GUI client for DynamoDB.

DynamoDB Shell

Local DynamoDB provides a JavaScript Shell to interact with DynamoDB. Once you have a local instance running, go to localhost:8000/shell/.

The shell also provides API templates for different kinds of functions and queries. Click on this button to switch to API templates view:

For example to view all the tables of the database, pick the ListTables API template and modify the params to be empty and run the query:

var params = {};
dynamodb.listTables(params, function(err, data) {
    if (err) ppJson(err); // an error occurred
    else ppJson(data); // successful response
});