Example Schema

schema.md

Example API DynamoDB Spec

The table design for an Example API service

Table of Contents

• Table Spec
• Access Patterns
  • Get all items reserved for a global cycle, ordered by score
  • Get all items reserved for the user's cycle #, ordered by score
  • Get all items in the back catalogue
  • Get all items currently assigned to a user, ordered by score
  • Get all completed items by a user, ordered by completed date
  • Get in-progress item for a user
  • Get all orphaned items for a user, ordered by orphan time
  • Get stats for a user
• Indexes
  • Main
  • CycleSelector
• Author

Table Spec

Params to create the table using the CLI or the AWS SDK:

{
  "AttributeDefinitions": [
    {
      "AttributeName": "pk",
      "AttributeType": "S"
    },
    {
      "AttributeName": "sk",
      "AttributeType": "S"
    }
  ],
  "TableName": "example-api-table",
  "KeySchema": [
    {
      "AttributeName": "pk",
      "KeyType": "HASH"
    },
    {
      "AttributeName": "sk",
      "KeyType": "RANGE"
    }
  ],
  "ProvisionedThroughput": {
    "ReadCapacityUnits": 5,
    "WriteCapacityUnits": 5
  },
  "GlobalSecondaryIndexes": [
    {
      "KeySchema": [
        {
          "AttributeName": "selector",
          "KeyType": "HASH"
        },
        {
          "AttributeName": "data",
          "KeyType": "RANGE"
        }
      ],
      "IndexName": "CycleSelector",
      "Projection": {
        "ProjectionType": "ALL"
      }
    }
  ]
}

createTable Using the CLI:

$ aws dynamodb create-table --table-name example-api-table --cli-input-json create-table.json

Using the AWS SDK:

const DynamoDB = require("aws-sdk/clients/dynamodb");

const service = new DynamoDB({ region: process.env.AWS_REGION });

service.createTable(tableJson, (err, data) => {
  console.log(data);
});

Access Patterns

Get all items reserved for a global cycle, ordered by score

Provides the ability to release new items to everyone in a specific week

Perform a DocumentClient.query against the CycleSelector index:

{
  "TableName": "example-api-table",
  "KeyConditionExpression": "#selector = :selector",
  "ExpressionAttributeNames": {
    "#selector": "selector"
  },
  "ExpressionAttributeValues": {
    ":selector": "global-cycle:5"
  },
  "ScanIndexFoward": false,
  "IndexName": "CycleSelector"
}

Matching Records

selector (HASH)	data (RANGE)
global-cycle:5	80	pk: item-65	sk: metadata
global-cycle:5	70	pk: item-55	sk: metadata

Get all items reserved for the user's cycle #, ordered by score

Provides the ability to reserve items for a specific week in the user's journey

Perform a DocumentClient.query against the CycleSelector index:

{
  "TableName": "example-api-table",
  "KeyConditionExpression": "#selector = :selector",
  "ExpressionAttributeNames": {
    "#selector": "selector"
  },
  "ExpressionAttributeValues": {
    ":selector": "user-cycle:1"
  },
  "ScanIndexFoward": false,
  "IndexName": "CycleSelector"
}

Matching Records

selector (HASH)	data (RANGE)
user-cycle:1	35	pk: item-84	sk: metadata

Get all items in the back catalogue

Items are duplicated across all write-sharded keys

Perform a DocumentClient.query against the CycleSelector index:

{
  "TableName": "example-api-table",
  "KeyConditionExpression": "#selector = :selector",
  "ExpressionAttributeNames": {
    "#selector": "selector"
  },
  "ExpressionAttributeValues": {
    ":selector": "back-catalogue:4"
  },
  "IndexName": "CycleSelector"
}

Matching Records

selector (HASH)	data (RANGE)
back-catalogue:4	87	pk: item-45	sk: metadata

Get all items currently assigned to a user, ordered by score

Perform a DocumentClient.query against the Main index with a begins_with(#sk, :sk) condition on the sort key:

{
  "TableName": "example-api-table",
  "KeyConditionExpression": "#pk = :pk and begins_with(#sk, :sk)",
  "ExpressionAttributeNames": {
    "#pk": "pk",
    "#sk": "sk"
  },
  "ExpressionAttributeValues": {
    ":pk": "user-8790",
    ":sk": "item:assigned:"
  },
  "ScanIndexFoward": false
}

Matching Records

pk (HASH)	sk (RANGE)
user-8790	item:assigned:350	itemId: item-84
user-8790	item:assigned:87	itemId: item-45

Get all completed items by a user, ordered by completed date

Perform a DocumentClient.query against the Main index with a begins_with(#sk, :sk) condition on the sort key:

{
  "TableName": "example-api-table",
  "KeyConditionExpression": "#pk = :pk and begins_with(#sk, :sk)",
  "ExpressionAttributeNames": {
    "#pk": "pk",
    "#sk": "sk"
  },
  "ExpressionAttributeValues": {
    ":pk": "user-8790",
    ":sk": "item:completed:"
  },
  "ScanIndexFoward": false
}

Matching Records

pk (HASH)	sk (RANGE)
user-8790	item:completed:2019-01-22T11:15:00.000Z	itemId: item-102
user-8790	item:completed:2019-01-22T10:28:49.930Z	itemId: item-55

Get in-progress item for a user

Perform a DocumentClient.get against the Main index:

{
  "TableName": "example-api-table",
  "Key": {
    "pk": "user-8790",
    "sk": "item:in-progress"
  }
}

Matching Records

pk (HASH)	sk (RANGE)
user-8790	item:in-progress	itemId: item-3	progress: 0.87

Get all orphaned items for a user, ordered by orphan time

Perform a DocumentClient.query against the Main index with a begins_with(#sk, :sk) condition on the sort key:

{
  "TableName": "example-api-table",
  "KeyConditionExpression": "#pk = :pk and begins_with(#sk, :sk)",
  "ExpressionAttributeNames": {
    "#pk": "pk",
    "#sk": "sk"
  },
  "ExpressionAttributeValues": {
    ":pk": "user-8790",
    ":sk": "item:orphaned:"
  }
}

Matching Records

pk (HASH)	sk (RANGE)
user-8790	item:orphaned:2018-12-25T11:15:00.000Z	itemId: item-34

Get stats for a user

Perform a DocumentClient.get against the Main index:

{
  "TableName": "example-api-table",
  "Key": {
    "pk": "user-8790",
    "sk": "stats"
  }
}

Matching Records

pk (HASH)	sk (RANGE)
user-8790	stats	completed: 55	correctGuesses: 24	liveCompleted: 4

Indexes

Main

pk (HASH)	sk (RANGE)
item-55	metadata	selector: global-cycle:5	data: 70
item-65	metadata	selector: global-cycle:5	data: 80
item-84	metadata	selector: user-cycle:1	data: 35
item-3	metadata	selector: back-catalogue	data: 99
item-45	metadata	selector: back-catalogue:4	data: 87
user-8790	item:assigned:87	itemId: item-45
user-8790	item:assigned:350	itemId: item-84
user-8790	item:completed:2019-01-22T10:28:49.930Z	itemId: item-55
user-8790	item:completed:2019-01-22T11:15:00.000Z	itemId: item-102
user-8790	item:in-progress	itemId: item-3	progress: 0.87
user-8790	item:orphaned:2018-12-25T11:15:00.000Z	itemId: item-34
user-8790	stats	completed: 55	correctGuesses: 24	liveCompleted: 4

CycleSelector

selector (HASH)	data (RANGE)
global-cycle:5	70	pk: item-55	sk: metadata
global-cycle:5	80	pk: item-65	sk: metadata
user-cycle:1	35	pk: item-84	sk: metadata
back-catalogue	99	pk: item-3	sk: metadata
back-catalogue:4	87	pk: item-45	sk: metadata

Author

Spec authored by Eric Allam and generated by dynamodb-spec-generator