making a shoebox

29 files changed, 3234 insertions, 0 deletions

diff --git a/src/app/constants.js b/src/app/constants.js
new file mode 100644
index 0000000..50aa57e
--- /dev/null
+++ b/src/app/constants.js
@@ -0,0 +1,38 @@
+/**
+ * Constants and enums.
+ * @module app/constants
+ */
+
+// Dotenv is loaded here for the benefit of command-line scripts
+import dotenv from "dotenv";
+dotenv.config();
+
+import { enumLookup } from "app/utils/data_utils";
+
+// The Knex configuration file lives outside the main source tree.
+export { default as knexfile } from "../../knexfile";
+
+export const ROLE_ENUM = {
+  [-1]: "system",
+  0: "guest",
+  1: "analyst",
+  2: "moderator",
+  3: "admin",
+};
+
+export const ROLES = enumLookup(ROLE_ENUM);
+
+export const PERMISSIONS_ENUM = {
+  0: "DENY",
+  1: "ALLOW_FOR_OWNER",
+  2: "ALLOW",
+};
+
+export const PERMISSIONS = enumLookup(PERMISSIONS_ENUM);
+
+export const CRUD_VERBS = {
+  get: "read",
+  post: "create",
+  put: "update",
+  delete: "destroy",
+};
diff --git a/src/app/db/configure.js b/src/app/db/configure.js
new file mode 100644
index 0000000..1581901
--- /dev/null
+++ b/src/app/db/configure.js
@@ -0,0 +1,58 @@
+/**
+ * Configure our database.
+ * @module app/db/configure
+ */
+
+import _knex from "knex";
+import _bookshelf from "bookshelf";
+import jsonColumns from "bookshelf-json-columns";
+import cascadeDelete from "bookshelf-cascade-delete";
+
+import { knexfile } from "app/constants";
+import { registerModels } from "app/db/models";
+
+import { types } from "pg";
+
+/**
+ * PostgreSQL type conversion
+ */
+types.setTypeParser(types.builtins.INT8, (value) => parseInt(value));
+types.setTypeParser(types.builtins.FLOAT8, (value) => parseFloat(value));
+types.setTypeParser(types.builtins.NUMERIC, (value) => parseFloat(value));
+
+/**
+ * Memoized copies of the current knex and bookshelf objects.
+ */
+let knex, bookshelf;
+
+/**
+ * Configure a knex instance based on the knexfile.
+ */
+function configureKnex() {
+  const environment = process.env.NODE_ENV ?? "development";
+
+  if (!knexfile[environment]) {
+    throw new Error("Please specify a valid NODE_ENV");
+  }
+
+  knex = _knex(knexfile[environment]);
+  return knex;
+}
+
+/**
+ * Configure knex and bookshelf. By default, memoizes the
+ * knex object so other methods can call `configure()` and get it.
+ * The database is configured this way to be interoperable with the test environment.
+ *
+ * @param {Object} __knex  An existing knex instance, mainly used in testing.
+ */
+export default function configure(__knex) {
+  if (!knex || (__knex && knex !== __knex)) {
+    knex = __knex || configureKnex();
+    bookshelf = _bookshelf(knex);
+    bookshelf.plugin(jsonColumns);
+    bookshelf.plugin(cascadeDelete);
+    registerModels(bookshelf);
+  }
+  return { bookshelf, knex };
+}
diff --git a/src/app/db/helpers.js b/src/app/db/helpers.js
new file mode 100644
index 0000000..0b19833
--- /dev/null
+++ b/src/app/db/helpers.js
@@ -0,0 +1,248 @@
+/**
+ * Helper functions for database queries.
+ * @module app/db/helpers
+ */
+
+import { capitalizeWord } from "app/utils/text_utils";
+
+/**
+ * Build an index query.
+ * @param {knex.builder}    builder       the Knex query builder
+ * @param {bookshelf.Model} Model         the affected model
+ * @param {Object}          query         the query parameters
+ * @param {Object}          paginate      pagination settings
+ * @param {User}            user          the request.user
+ * @param {function}        queryBuilder  optional, function to build the query
+ * @param {Object}          columns       valid columns on the model
+ * @returns {knex.builder}                the supplied Knex query builder object
+ */
+export function buildIndexQuery({
+  builder,
+  Model,
+  query,
+  paginate,
+  queryBuilder,
+  columns,
+}) {
+  const { offset, limit } = getOffsetAndLimit(query, paginate);
+  const { sortField, sortDirection, sortCount } = getSort(query, Model);
+
+  const tableName = Model.prototype.tableName;
+  const idAttribute = Model.prototype.idAttribute;
+  const sortTableName = tableName + "_" + sortField;
+  const sortOrderName = sortField + "_count";
+
+  // Get the filtered columns
+  let filters = getQueryFilters(query);
+
+  if (sortCount) {
+    // Sort by a pivot relation
+    builder.select("*", function () {
+      // Note: Knex syntax requires "this"
+      return this.from(sortTableName)
+        .count("*")
+        .whereRaw("?? = ??", [
+          `${sortTableName}.${idAttribute}`,
+          `${tableName}.${idAttribute}`,
+        ])
+        .as(sortOrderName);
+    });
+  }
+
+  // If there are filters, use the supplied query builder
+  if (Object.keys(filters).length > 0) {
+    if (queryBuilder) {
+      queryBuilder(builder, columns, filters);
+    } else {
+      builder.where(filters);
+    }
+  }
+
+  // Sorting
+  if (sortCount) {
+    builder.orderBy(sortOrderName, sortDirection);
+  } else {
+    builder.orderBy(sortField, sortDirection);
+  }
+
+  // Pagination
+  if (limit) {
+    builder.limit(limit);
+  }
+  if (offset) {
+    builder.offset(offset);
+  }
+
+  // console.log(builder)
+  return builder;
+}
+
+/**
+ * Build a count query.
+ * @param {knex.builder}    builder       the Knex query builder
+ * @param {Object}          query         the query parameters
+ * @param {User}            user          the request.user
+ * @param {function}        queryBuilder  optional, function to build the query
+ * @param {Object}          columns       valid columns on the model
+ * @returns {knex.builder}                the supplied Knex query builder object
+ */
+export function buildCountQuery({ builder, query, queryBuilder, columns }) {
+  let filters = getQueryFilters(query);
+
+  if (Object.keys(filters).length > 0) {
+    if (queryBuilder) {
+      queryBuilder(builder, columns, filters);
+    } else {
+      builder.where(filters);
+    }
+  }
+
+  return builder;
+}
+
+/**
+ * Build a pagination response for a count request.
+ * @param {number}          rowCount  the total number of response returned from count()
+ * @param {Object}          query     the query parameters
+ * @param {Object}          paginate  the service pagination
+ * @returns {knex.builder}            the response object
+ */
+export function buildPaginationResponse({ rowCount, query, paginate }) {
+  const { page, limit } = getOffsetAndLimit(query, paginate);
+  return {
+    rowCount: rowCount || 0,
+    page: page,
+    pageSize: limit,
+    pages: limit ? Math.ceil(rowCount / limit) : 1,
+  };
+}
+
+/**
+ * Query builder for index queries. Override in subclasses.
+ * @param  {knex.builder}  Knex query builder
+ * @param  {Object}        query parameters, minus offset/sort parameters
+ */
+export function indexQueryBuilder(builder, columns, filters) {
+  const validFilters = Object.keys(columns).reduce((valid, name) => {
+    if (name in filters) {
+      valid[name] = filters[name];
+    }
+    return valid;
+  }, {});
+  builder.where(validFilters);
+}
+
+/**
+ * Get the offset and limit for a given query.
+ * @param {Object} query                   the query object
+ * @param {number} query.offset            numeric offset into results
+ * @param {number} query.limit             maximum number of results to return
+ * @param {number} query.pageSize          size of a page of results (synonym for limit)
+ * @param {number} query.page              which page to return, zero-indexed
+ * @param {Object} paginate                pagination settings
+ * @param {number} paginate.pageSize       default page size
+ * @param {number} paginate.pageSizeLimit  maximum results to return, caps limit
+ * @return {Object}                        query offset, limit, and page
+ */
+export function getOffsetAndLimit(query, paginate) {
+  let { offset, limit, pageSize, page } = query;
+
+  page = parseInt(page) || 0;
+
+  if (paginate) {
+    limit = parseInt(limit) || parseInt(pageSize) || paginate.pageSize;
+    if (paginate.pageSizeLimit) {
+      limit = Math.min(limit, paginate.pageSizeLimit);
+    }
+  } else if (limit) {
+    limit = parseInt(limit);
+  } else {
+    limit = 0;
+  }
+
+  if (page) {
+    offset = page * limit;
+  } else if (offset) {
+    offset = parseInt(offset);
+  } else {
+    offset = 0;
+  }
+  return { offset, limit, page };
+}
+
+/**
+ * Get the sort options for a query.
+ * @param {Object}          query          the query object
+ * @param {string}          query.sort     field to sort by
+ * @param {string}          query.order    sort direction, can be asc or desc
+ * @param {bookshelf.Model} Model the model to query, used to get the primary key
+ */
+export function getSort(query, Model) {
+  let { sort, order } = query;
+  let sortParts, sortField, sortDirection;
+  let sortCount = false;
+  if (order) {
+    sortField = sort;
+    sortDirection = order;
+  } else {
+    sortParts = (sort || "id desc").split(" ", 1);
+    sortField = sortParts[0] || "id";
+    sortDirection = sortParts[1] || "desc";
+  }
+  if (sortField === "id") {
+    sortField = Model.prototype.idAttribute;
+  }
+  if (sortField.match("_count")) {
+    sortField = sortField.split("_count")[0];
+    sortCount = true;
+  }
+  return { sortField, sortDirection, sortCount };
+}
+
+/**
+ * Get the columns to filter by, without offset/sort options
+ * @param {Object}  query  the query object
+ * @return {Object}        fields unrelated to offset/sort
+ */
+export function getQueryFilters(query) {
+  // eslint-disable-next-line no-unused-vars
+  let { offset, limit, pageSize, page, sort, order, ...filters } = query;
+  return filters;
+}
+
+/**
+ * Load a table's columns from the database
+ * @param {bookshelf.Model} Model the model to query
+ * @return {Object} columns the columns
+ */
+export async function loadColumns(bookshelf, Model) {
+  if (!Model) {
+    return {};
+  }
+  const columns = await bookshelf.knex(Model.prototype.tableName).columnInfo();
+  return columns;
+}
+
+/**
+ * Filter the data in an object, leaving only prdetermined columns
+ * @param {Object} data          data to filter, typically a request.body
+ * @param {Object} columns       columns lookup returned from loadColumns
+ * @param {Array}  privateFields columns not to include in the returned data
+ * @return {Object} the filtered data
+ */
+export function reduceValidColumns(data, columns, privateFields = []) {
+  return Object.keys(columns).reduce((item, columnName) => {
+    if (columnName in data && !privateFields.includes(columnName)) {
+      item[columnName] = data[columnName];
+    }
+    return item;
+  }, {});
+}
+
+/**
+ * Convert a table name to a modelname (camel case, capitalized)
+ * @param  {string} tableName  the table name
+ * @return {string}            the model name
+ */
+export const tableNameToModelName = (tableName = "") =>
+  tableName.split("_").map(capitalizeWord).join("");
diff --git a/src/app/db/models/Shoe.js b/src/app/db/models/Shoe.js
new file mode 100644
index 0000000..a35da40
--- /dev/null
+++ b/src/app/db/models/Shoe.js
@@ -0,0 +1,17 @@
+/**
+ * Shoe model.
+ * @module app/db/models/Shoe
+ */
+
+export default function registerModel(bookshelf) {
+  bookshelf.model(
+    "Shoe",
+    {
+      tableName: "shoe",
+      idAttribute: "shoe_id",
+    },
+    {
+      jsonColumns: ["metadata"],
+    }
+  );
+}
diff --git a/src/app/db/models/index.js b/src/app/db/models/index.js
new file mode 100644
index 0000000..424be66
--- /dev/null
+++ b/src/app/db/models/index.js
@@ -0,0 +1,18 @@
+/**
+ * Register the models used by our application.
+ * @module app/db/models/index
+ */
+
+import registerShoe from "./Shoe";
+
+/**
+ * Register our models with a Bookshelf instance.
+ *
+ * Some modules define multiple models for pivot tables.  In these cases,
+ * the models should be stored in the file for the closest related concept.
+ *
+ * @param {Object} bookshelf   The bookshelf instance to bind to
+ */
+export function registerModels(bookshelf) {
+  registerShoe(bookshelf);
+}
diff --git a/src/app/db/query.js b/src/app/db/query.js
new file mode 100644
index 0000000..65a9157
--- /dev/null
+++ b/src/app/db/query.js
@@ -0,0 +1,148 @@
+/**
+ * Define standard database queries
+ * @module app/db/query
+ */
+
+import {
+  buildIndexQuery,
+  buildCountQuery,
+  buildPaginationResponse,
+} from "app/db/helpers";
+
+/**
+ * Fetch a set of database objects matching a query
+ * @param {bookshelf.Model} Model         the model to query
+ * @param {Object}          query         query options
+ * @param {Object}          paginate      optional pagination options
+ * @param {User}            user          the current `request.user`
+ * @param {function}        queryBuilder  a custom function to build the query
+ * @param {Object}          columns       valid columns on the model
+ * @param {Array}           withRelated   a custom function to build the query
+ * @returns {Promise}                     a query which resolves to the desired objects
+ */
+export function index({
+  Model,
+  query,
+  paginate,
+  user,
+  columns,
+  queryBuilder,
+  withRelated,
+}) {
+  return Model.query((builder) =>
+    buildIndexQuery({
+      builder,
+      Model,
+      query,
+      paginate,
+      user,
+      columns,
+      queryBuilder,
+    })
+  ).fetchAll({ withRelated });
+}
+
+/**
+ * Count database objects matching a query
+ * @param {bookshelf.Model} Model         the model to query
+ * @param {Object}          query         query options
+ * @param {Object}          paginate      optional pagination options
+ * @param {User}            user          the current `request.user`
+ * @param {function}        queryBuilder  a custom function to build the query
+ * @param {Object}          columns       valid columns on the model
+ * @returns {Promise}                     a query which resolves to the count
+ */
+export function count({ Model, query, paginate, user, queryBuilder, columns }) {
+  return Model.query((builder) =>
+    buildCountQuery({
+      builder,
+      Model,
+      query,
+      paginate,
+      user,
+      queryBuilder,
+      columns,
+    })
+  )
+    .count()
+    .then((rowCount) => {
+      return buildPaginationResponse({ rowCount, query, paginate });
+    });
+}
+
+/**
+ * Fetch single object by ID
+ * @param {bookshelf.Model} Model     the model to query
+ * @param {number}          objectID  the primary key of the object
+ * @param {string}          field     optional, the field to query
+ * @param {Object}          criteria  additional criteria for the show query (when specifying a pivot table by its relations)
+ * @returns {Promise}                 a query which resolves to the desired object
+ */
+export function show({ Model, objectID, field, criteria, withRelated }) {
+  return new Model({
+    ...criteria,
+    [field || Model.prototype.idAttribute]: objectID,
+  }).fetch({ withRelated });
+}
+
+/**
+ * Fetch several objects by ID
+ * @param {bookshelf.Model} Model    the model to query
+ * @param {Array}           ids      a list of keys to query
+ * @param {string}          field    optional, the field to query
+ * @returns {Promise}                a query which resolves to the desired objects
+ */
+export function showIDs({ Model, ids, field, queryBuilder, withRelated }) {
+  return Model.query((builder) => {
+    builder.whereIn([field || Model.prototype.idAttribute], ids);
+    if (queryBuilder) {
+      queryBuilder(builder);
+    }
+    return builder;
+  }).fetchAll({ withRelated });
+}
+
+/**
+ * Create a record
+ * @param {bookshelf.Model}       Model        the model to create
+ * @param {Object}                data         the data to insert
+ * @param {bookshelf.transaction} transaction  optional transaction
+ * @returns {Promise}                          a query which resolves to the new object
+ */
+export function create({ Model, data, transaction }) {
+  return new Model(data).save({}, { transacting: transaction });
+}
+
+/**
+ * Update a complete record
+ * @param {Object}                instance     an instance of a bookshelf model
+ * @param {Object}                data         the complete updated record.
+ * @param {bookshelf.transaction} transaction  optional transaction
+ * @returns {Promise}                          a query which resolves to the updated object
+ */
+export function update({ instance, data, transaction }) {
+  return instance.save(data, { transacting: transaction });
+}
+
+/**
+ * Partially update a record by ID
+ * @param {bookshelf.Model} Model    the model to affect
+ * @param {number}          objectID the primary key of the object
+ * @param {Object}          data     the fields to update.
+ * @returns {Promise}                a query which resolves to the updated object
+ */
+export function patch({ Model, objectID, data }) {
+  return Model.forge({ [Model.prototype.idAttribute]: objectID }).save(data, {
+    patch: true,
+  });
+}
+
+/**
+ * Destroy a record by ID
+ * @param {bookshelf.Model} Model    the model to affect
+ * @param {number}          objectID the primary key of the object
+ * @returns {Promise}                a query which resolves when the object is destroyed
+ */
+export function destroy({ Model, objectID }) {
+  return new Model({ [Model.prototype.idAttribute]: objectID }).destroy();
+}
diff --git a/src/app/db/service/base/helpers.js b/src/app/db/service/base/helpers.js
new file mode 100644
index 0000000..9ab1119
--- /dev/null
+++ b/src/app/db/service/base/helpers.js
@@ -0,0 +1,240 @@
+/**
+ * Service construction helpers
+ * @module app/db/service/base/helpers
+ */
+
+import {
+  checkPermission,
+  checkParentPermission,
+} from "app/services/permission/helpers";
+import { sendResponse } from "app/db/service/base/response";
+
+/**
+ * Returns a helper function used by an iterator when creating custom routes.
+ * @param {Service} service   the affected service
+ * @param {Service} methods   the CRUD methods associated with the service
+ * @returns {Function}        the method called on the iterable
+ */
+export function createCustomRoute(service, methods) {
+  const { parent } = service.options;
+  /**
+   * Create a custom route.  If the route contains `/:id`, the record will be fetched before calling the handlers.
+   * @param {string} method      the method name
+   * @param {string} route       the route to mount on
+   * @param {Array}  handlers    a list of middleware
+   * @param {Object} permissions a permissions object
+   */
+  return function addCustomRouteHandler({
+    method,
+    route,
+    handlers,
+    permissions,
+  }) {
+    service.router[(method || "get").toLowerCase()](
+      route,
+      (parent ? [checkParentPermission(parent)] : [])
+        .concat(permissions ? [checkPermission(permissions)] : [])
+        .concat(route.match(/^\/:id/) ? methods.show(service) : [])
+        .concat(handlers)
+        .concat([sendResponse])
+    );
+  };
+}
+
+/**
+ * Returns a helper function used by an iterator when creating CRUD routes.
+ * @param {Service} service   the affected service
+ * @param {Object}  methods   a set of methods to use
+ * @returns {Function}        the method called on the iterable
+ */
+export function createCrudRoute(service, methods) {
+  const { parent, permissions, hooks, logging } = service.options;
+  const loggingHook = logging ? [logging] : [];
+  /**
+   * Create a CRUD route
+   * @param {string} type  the type of route to create
+   */
+  return function addEnabledRoute(type) {
+    const beforeHooks = hooks?.before[type] || [];
+    const afterHooks = hooks?.after[type] || [];
+    let verb, route, handlers;
+    switch (type) {
+      case "index":
+        verb = "get";
+        route = "/";
+        handlers = [
+          checkPermission(permissions, "read"),
+          ...beforeHooks,
+          methods.index(service),
+        ];
+        break;
+      case "show":
+        verb = "get";
+        route = "/:id";
+        handlers = [
+          checkPermission(permissions, "read"),
+          ...beforeHooks,
+          methods.show(service),
+        ];
+        break;
+      case "create":
+        verb = "post";
+        route = "/";
+        handlers = [
+          checkPermission(permissions, "create"),
+          ...beforeHooks,
+          methods.create(service),
+          ...loggingHook,
+        ];
+        break;
+      case "update":
+        verb = "put";
+        route = "/:id";
+        handlers = [
+          checkPermission(permissions, "read"),
+          ...(hooks?.before?.show || []),
+          methods.show(service),
+          ...beforeHooks,
+          checkPermission(permissions, "update"),
+          methods.update(service),
+          ...loggingHook,
+        ];
+        break;
+      case "updateMany":
+        verb = "put";
+        route = "/";
+        handlers = [
+          checkPermission(permissions, "read"),
+          methods.showMany(service),
+          ...beforeHooks,
+          checkPermission(permissions, "update"),
+          methods.updateMany(service),
+          ...loggingHook,
+        ];
+        break;
+      case "sort":
+        verb = "post";
+        route = "/sort";
+        handlers = [
+          checkPermission(permissions, "read"),
+          ...beforeHooks,
+          checkPermission(permissions, "update"),
+          methods.sort(service),
+          // ...loggingHook,
+        ];
+        break;
+      case "destroy":
+        verb = "delete";
+        route = "/:id";
+        handlers = [
+          checkPermission(permissions, "read"),
+          methods.show(service),
+          ...beforeHooks,
+          checkPermission(permissions, "destroy"),
+          methods.destroy(service),
+          ...loggingHook,
+        ];
+        break;
+      case "destroyMany":
+        verb = "delete";
+        route = "/";
+        handlers = [
+          checkPermission(permissions, "read"),
+          ...beforeHooks,
+          // above, i'm using show to see if the records exist before deleting them
+          // but if deleting many, this is probably being done on the pivot table,
+          // and where the check is accomplished implicitly by the destroy middleware.
+          // if delete permission is ALLOW_FOR_OWNER, we still have to make the check.
+          // methods.showMany(service),
+          checkPermission(permissions, "destroy"),
+          methods.destroyMany(service),
+          ...loggingHook,
+        ];
+        break;
+      default:
+        console.error(`Unknown route: ${type}`);
+        return;
+    }
+    let routeHandlers = []
+      .concat(
+        parent
+          ? [formatParentParameters(service), checkParentPermission(parent)]
+          : []
+      )
+      .concat(handlers)
+      .concat(afterHooks)
+      .concat(type === "destroy" ? [onDestroyCleanupResponse] : [])
+      .concat([sendResponse]);
+    service.router[verb](
+      route,
+      routeHandlers.filter((handler) => !!handler)
+    );
+  };
+}
+
+/**
+ * Helper function to convert request.params to integers where appropriate
+ */
+export function formatParentParameters(service) {
+  const parentIdAttributes = service.idAttributes.slice(1);
+  return function formatParentParametersMiddleware(request, response, next) {
+    const hasInvalidParams = parentIdAttributes.some((parentIdAttribute) => {
+      const value = parseInt(request.params[parentIdAttribute]);
+      if (!value) {
+        return true;
+      }
+      request.params[parentIdAttribute] = value;
+      return false;
+    });
+    if (hasInvalidParams) {
+      return next(new Error("malformed parent ID"));
+    }
+    next();
+  };
+}
+
+/**
+ * Helper function to clean up local data.  We make the deleted object available to middleware, but not to the client.
+ * @param  {express.request}  request  the request object
+ * @param  {express.response} response the response object
+ * @param  {function}         next     function to proceed to the next middleware
+ */
+export function onDestroyCleanupResponse(request, response, next) {
+  delete response.locals.data;
+  delete response.locals.child;
+  next();
+}
+
+/**
+ * For each returned object, fetch the count of a particular relation
+ * @param  {string} relation   name of relation
+ * @param  {string} valueName  (optional) name of the value to set on the output object, defaults to `{relation}_count``
+ * @return {Function}          middleware function (`after` hook)
+ */
+export function getRelationCount(relation, as, where) {
+  return async function getRelationCountMiddleware(request, response, next) {
+    if (!response.locals.data) {
+      return next();
+    }
+    const countField = relation + "_count";
+    const items =
+      "length" in response.locals.data
+        ? response.locals.data
+        : [response.locals.data];
+    const getCountPromises = items.map(async (item) => {
+      if (!item || !item.related || countField in item) return;
+      const itemRelation = item.related(relation);
+      if (where) {
+        itemRelation.where(where);
+      }
+      const count = await itemRelation.count();
+      item.set(as || countField, count);
+    });
+    try {
+      await Promise.all(getCountPromises);
+    } catch (error) {
+      console.error(error);
+    }
+    next();
+  };
+}
diff --git a/src/app/db/service/base/index.js b/src/app/db/service/base/index.js
new file mode 100644
index 0000000..188d86a
--- /dev/null
+++ b/src/app/db/service/base/index.js
@@ -0,0 +1,137 @@
+/**
+ * Abstract API service
+ * @module app/db/service/base/index
+ */
+
+import { Router } from "express";
+import {
+  checkAccessToken,
+  checkUserIsActive,
+} from "app/services/authentication/helpers";
+import { indexQueryBuilder, loadColumns } from "app/db/helpers";
+
+import * as serviceMethods from "app/db/service/base/methods";
+import { handleError } from "app/db/service/base/response";
+import {
+  createCustomRoute,
+  createCrudRoute,
+} from "app/db/service/base/helpers";
+
+/**
+ * By default, no API routes are enabled.
+ */
+// const enableAll = ["index", "show", "create", "update", "destroy"];
+const noRoutesEnabled = [];
+
+/**
+ * Create an API service.
+ * @param {Object}        options                          the options dictionary
+ * @param {bookshelf}     options.bookshelf                the Bookshelf instance created by the server
+ * @param {Service}       options.parent                   the Service that this inherits from
+ * @param {Model|string}  options.Model                    the name of a Bookshelf model, or the model itself
+ * @param {string}        options.name                     optional, the name of this resource. Used for logging
+ * @param {string[]}      options.enabled[]                specific CRUD APIs to enable
+ * @param {Object}        options.hooks                    extra middleware functions for each endpoint
+ * @param {Object}        options.hooks.before             middleware called before a DB call
+ * @param {function[]}    options.hooks.before.index[]     middleware called before index queries
+ * @param {function[]}    options.hooks.before.show[]      middleware called before show queries
+ * @param {function[]}    options.hooks.before.create[]    middleware called before create queries
+ * @param {function[]}    options.hooks.before.update[]    middleware called before update queries
+ * @param {function[]}    options.hooks.before.destroy[]   middleware called before destroy queries
+ * @param {function}      options.logging                  middleware hook which runs after create/update/destroy queries, to record events
+ * @param {Object}        options.hooks.after              middleware called after a DB call, similar to `before`
+ * @param {boolean}       options.authenticate             set to false to disable authentication for this service
+ * @param {Object}        options.permissions              per-API permissions
+ * @param {Object}        options.permissions.read         permissions applied when reading records
+ * @param {number[]}      options.permissions.read.roles[] allow access with matching user role
+ * @param {boolean}       options.permissions.read.owner   allow access with matching `user_id`
+ * @param {Object}        options.permissions.create       permissions applied when creating records
+ * @param {Object}        options.permissions.update       permissions applied when editing records
+ * @param {Object}        options.permissions.destroy      permissions applied when destroying records
+ * @param {Object[]}      options.routes[]                 additional list of non-CRUD routes
+ * @param {string}        options.routes[].method          method to use, get/post/put/delete
+ * @param {route}         options.routes[].route           endpoint to use, e.g. `/login`
+ * @param {function[]}    options.routes[].handlers[]      list of middleware functions
+ * @param {privateFields} options.privateFields            list of fields which should not be editable through the API
+ * @param {Object}        methods                          optional method lookup, for alternative CRUD middleware
+ * @return {Service} the service object
+ */
+export default async function Service(options, methods = serviceMethods) {
+  const service = { type: "base", options, children: {} };
+
+  const { bookshelf, parent, Model, enabled, routes, authenticate } = options;
+
+  /** Locate the Model specified in the configuration */
+  service.Model =
+    Model && typeof Model === "string" ? bookshelf.model(Model) : options.Model;
+
+  /** Use the model to identify the service's resource name */
+  service.resource = options.name || service.Model?.prototype.tableName;
+  if (!service.resource) {
+    throw new Error("No name or model defined for resource");
+  }
+
+  /** Get the ID attributes of this model, and any parent models */
+  if (service.Model) {
+    if (parent) {
+      service.idAttributes = [service.Model.prototype.idAttribute].concat(
+        parent.idAttributes
+      );
+    } else {
+      service.idAttributes = [service.Model.prototype.idAttribute];
+    }
+  }
+
+  /** Load the column names for this service's Model */
+  service.columns =
+    service.Model && (await loadColumns(bookshelf, service.Model));
+
+  /** Specify the `index` queryBuilder */
+  service.queryBuilder = options.queryBuilder || indexQueryBuilder;
+
+  // console.log("* Creating", service.resource, "service");
+
+  /** Instantiate the Express router. If this is a sub-router, merge the parent's params. */
+  service.router = Router({ mergeParams: !!parent });
+
+  /** Associate the parent resource with this resource */
+  if (parent) {
+    service.parent = parent;
+  }
+
+  /** Most APIs need access to the database, so supply this on the request object */
+  service.router.use((request, response, next) => {
+    request.service = service;
+    request.bookshelf = bookshelf;
+    next();
+  });
+
+  /** Most APIs authenticate, but this can be disabled (for the authentication service) */
+  if (authenticate !== false) {
+    service.router.use(checkAccessToken(options.authenticationOptions || {}));
+    service.router.use(checkUserIsActive);
+  }
+
+  /** Add custom routes first */
+  if (routes) {
+    routes.forEach(createCustomRoute(service, methods));
+  }
+
+  /** Add all enabled CRUD routes, if this service has a Model. */
+  const enabledRoutes = (service.Model && enabled) || noRoutesEnabled;
+  enabledRoutes.forEach(createCrudRoute(service, methods));
+
+  /** Add error-handling middleware */
+  service.router.use(handleError);
+
+  /** Method to attach a child service to this service's router
+   * @param {string}  route         base route of this service
+   * @param {Service} childService  instance of the child service
+   */
+  service.use = (route, childService) => {
+    service.router.use(`${route}`, childService.router);
+    service.children[route] = childService;
+  };
+
+  return service;
+}
diff --git a/src/app/db/service/base/many.js b/src/app/db/service/base/many.js
new file mode 100644
index 0000000..1791694
--- /dev/null
+++ b/src/app/db/service/base/many.js
@@ -0,0 +1,205 @@
+/**
+ * Service API methods that affect multiple records
+ * @module app/db/service/base/methods
+ */
+
+import * as db from "app/db/query";
+import { reduceValidColumns } from "app/db/helpers";
+import { PERMISSIONS } from "app/constants";
+import debugModule from "debug";
+
+/**
+ * Debug logger
+ */
+const debug = debugModule("shoebox:service");
+
+/**
+ * API to query for multiple records by ID
+ */
+export function showMany(service) {
+  const { Model, parent, resource } = service;
+  const { childRelation } = service.options;
+  const idAttribute = service.idAttributes[0];
+  return async function showManyMiddleware(request, response, next) {
+    const { user, permission, body } = request;
+    const ids = body.map((item) => item[idAttribute]).filter((id) => !!id);
+    let data;
+    try {
+      if (parent) {
+        // Fetch the immediate parent of the pivot table based on the name of the parent resource.
+        // This instance is added to the `request.parents` object when performing the permissions check.
+        const parentInstance = request.parents[parent.resource];
+        data = await parentInstance
+          .related(childRelation)
+          .query((builder) => builder.whereIn(idAttribute, ids));
+      } else {
+        data = await db.showIDs({
+          Model,
+          ids,
+        });
+      }
+    } catch (error) {
+      debug(`${resource} Show error`);
+      debug(error);
+      return next(error);
+    }
+    if (!data) {
+      response.locals = { data: [] };
+      next();
+    } else if (
+      permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+      data.some((item) => item.get("user_id") !== user.user_id)
+    ) {
+      next(new Error("PermissionsError"));
+    } else {
+      response.locals = { data };
+      next();
+    }
+  };
+}
+
+/**
+ * API to update multiple records
+ */
+export function updateMany(service) {
+  return async function updateManyMiddleware(request, response, next) {
+    const data = await handleUpdateManyWithTransaction(
+      service,
+      request,
+      response
+    );
+    response.locals.data = data;
+    next();
+  };
+}
+
+/**
+ * Update multiple records using a transaction
+ */
+export async function handleUpdateManyWithTransaction(
+  service,
+  request,
+  response
+) {
+  const { Model, idAttributes, columns } = service;
+  const { bookshelf, privateFields } = service.options;
+  const idAttribute = idAttributes[0];
+  return await bookshelf.transaction((transaction) => {
+    const { data: instances } = response.locals;
+    const { params, user, permission, body } = request;
+    const instanceLookup = instances.reduce((lookup, instance) => {
+      lookup[instance.id] = instance;
+      return lookup;
+    }, {});
+    const promises = body.map((item) => {
+      const itemId = item[idAttribute];
+      if (!itemId) {
+        return handleCreate({
+          Model,
+          body: item,
+          idAttributes,
+          params,
+          columns,
+          privateFields,
+          transaction,
+        });
+      } else if (itemId in instanceLookup) {
+        return handleUpdate({
+          instance: instanceLookup[itemId],
+          body: item,
+          user,
+          permission,
+          columns,
+          privateFields,
+          transaction,
+        });
+      } else {
+        throw new Error("item id not found on this record");
+      }
+    });
+    return Promise.all(promises);
+  });
+}
+
+/**
+ * API to destroy multiple records
+ */
+export function destroyMany(service) {
+  const { Model, idAttributes } = service;
+  const [idAttribute, ...parentIdAttributes] = idAttributes;
+  return async function destroyManyMiddleware(request, response, next) {
+    const idsToDelete = request.body[idAttribute];
+    let instances;
+    try {
+      instances = await db.showIDs({ Model, ids: idsToDelete });
+      instances.forEach((instance) => {
+        parentIdAttributes.forEach((parentIdAttribute) => {
+          if (
+            instance.get(parentIdAttribute) !==
+            request.params[parentIdAttribute]
+          ) {
+            throw new Error("parent mismatch");
+          }
+        });
+      });
+      await Promise.all(instances.map((instance) => instance.destroy()));
+    } catch (error) {
+      debug(`${service.resource} destroy many error`);
+      debug(error);
+      return next(new Error(error));
+    }
+    response.locals.data = instances;
+    response.locals.success = true;
+    response.locals.id = idsToDelete;
+    next();
+  };
+}
+
+/**
+ * Insert a single record
+ */
+export function handleCreate({
+  idAttributes,
+  params,
+  columns,
+  body,
+  Model,
+  privateFields,
+  transaction,
+}) {
+  body = reduceValidColumns(body, columns, privateFields);
+  if (idAttributes) {
+    idAttributes.forEach((idAttribute) => {
+      if (idAttribute in params && idAttribute in columns) {
+        body[idAttribute] = parseInt(params[idAttribute]);
+      }
+    });
+  }
+  return db.create({ Model: Model, data: body, transaction });
+}
+
+/**
+ * Update a single record
+ */
+export function handleUpdate({
+  instance,
+  body,
+  user,
+  permission,
+  columns,
+  privateFields,
+  transaction,
+}) {
+  if (
+    permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+    instance.get("user_id") !== user.user_id
+  ) {
+    throw new Error("PermissionsError");
+  }
+  body = reduceValidColumns(body, columns, privateFields);
+  return db.update({
+    instance,
+    data: body,
+    transaction,
+  });
+}
diff --git a/src/app/db/service/base/methods.js b/src/app/db/service/base/methods.js
new file mode 100644
index 0000000..be84d0c
--- /dev/null
+++ b/src/app/db/service/base/methods.js
@@ -0,0 +1,217 @@
+/**
+ * Service API methods
+ * @module app/db/service/base/methods
+ */
+
+import * as db from "app/db/query";
+import { reduceValidColumns } from "app/db/helpers";
+import { PERMISSIONS } from "app/constants";
+import debugModule from "debug";
+
+/**
+ * Export methods for dealing with multiple records
+ */
+export { showMany, updateMany, destroyMany } from "app/db/service/base/many";
+
+/**
+ * Debug logger
+ */
+const debug = debugModule("shoebox:service");
+
+/**
+ * API to filter and paginate lists of resources
+ */
+export function index(service) {
+  const { columns } = service;
+  return async function indexMiddleware(request, response, next) {
+    const { query: userQuery, params, permission, user } = request;
+    const { paginate } = service.options;
+    const queryBuilder = service.queryBuilder;
+    const query = {
+      ...userQuery,
+      ...params,
+    };
+    const withRelated = userQuery.related ? userQuery.related.split(",") : [];
+    if (permission === PERMISSIONS.ALLOW_FOR_OWNER) {
+      query.user_id = user.user_id;
+    }
+    let data, pagination;
+    let promises = [
+      db.index({
+        Model: service.Model,
+        query,
+        paginate,
+        user,
+        columns,
+        queryBuilder,
+        withRelated,
+      }),
+    ];
+    if (paginate) {
+      promises.push(
+        db.count({
+          Model: service.Model,
+          query,
+          paginate,
+          user,
+          columns,
+          queryBuilder,
+        })
+      );
+    } else {
+      promises.push(new Promise((resolve) => resolve({})));
+    }
+    try {
+      [data, pagination] = await Promise.all(promises);
+    } catch (error) {
+      debug(`${service.resource} Index error`);
+      debug(error);
+      console.error(error);
+      return next(error);
+    }
+    response.locals = {
+      data,
+      pagination,
+      query,
+    };
+    next();
+  };
+}
+
+/**
+ * API to query for a single record by ID
+ */
+export function show(service) {
+  return async function showMiddleware(request, response, next) {
+    const { user, permission, query } = request;
+    const withRelated = query.related ? query.related.split(",") : [];
+    let data;
+    try {
+      data = await db.show({
+        Model: service.Model,
+        objectID: parseInt(request.params.id),
+        withRelated,
+      });
+    } catch (error) {
+      debug(`${service.resource} Show error`);
+      debug(error);
+      return next(error);
+    }
+    if (!data) {
+      response.locals = {};
+      next();
+    } else if (
+      permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+      data.get("user_id") !== user.user_id
+    ) {
+      next(new Error("PermissionsError"));
+    } else {
+      response.locals = { data };
+      next();
+    }
+  };
+}
+
+/**
+ * API to insert a new record
+ */
+export function create(service) {
+  return async function createMiddleware(request, response, next) {
+    const body = reduceValidColumns(
+      request.body,
+      service.columns,
+      service.options.privateFields
+    );
+    if (service.options.parent) {
+      service.idAttributes.forEach((idAttribute) => {
+        if (idAttribute in request.params && idAttribute in service.columns) {
+          body[idAttribute] = request.params[idAttribute];
+        }
+      });
+    }
+    let data;
+    try {
+      data = await db.create({ Model: service.Model, data: body });
+    } catch (error) {
+      debug(`${service.resource} create error`);
+      console.error(error);
+      return next(error);
+    }
+    response.locals = { data };
+    next();
+  };
+}
+
+/**
+ * API to update a single record
+ */
+export function update(service) {
+  return async function updateMiddleware(request, response, next) {
+    const { data: instance } = response.locals;
+    const { user, permission } = request;
+    if (
+      permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+      instance.get("user_id") !== user.user_id
+    ) {
+      return next(new Error("PermissionsError"));
+    }
+    const body = reduceValidColumns(
+      request.body,
+      service.columns,
+      service.options.privateFields
+    );
+    let data;
+    try {
+      data = await db.update({
+        instance,
+        data: body,
+      });
+    } catch (error) {
+      debug(`${service.resource} update error`);
+      debug(error);
+      next(new Error(error));
+    }
+    response.locals = { data };
+    next();
+  };
+}
+
+/**
+ * API to sort records
+ */
+export function sort(service) {
+  return async function sortMiddleware(request, response, next) {
+    const { ids } = request.body;
+    const items = await db.showIDs({
+      Model: service.Model,
+      ids,
+    });
+    for (let item of items) {
+      item.set("sort_order", ids.indexOf(item.id));
+    }
+    await Promise.all(items.map((item) => item.save()));
+    response.locals.success = true;
+    next();
+  };
+}
+
+/**
+ * API to destroy a single record
+ */
+export function destroy(service) {
+  return async function destroyMiddleware(request, response, next) {
+    try {
+      await db.destroy({
+        Model: service.Model,
+        objectID: request.params.id,
+      });
+    } catch (error) {
+      debug(`${service.resource} destroy error`);
+      console.error(error);
+      return next(new Error(error));
+    }
+    response.locals.success = true;
+    response.locals.id = request.params.id;
+    next();
+  };
+}
diff --git a/src/app/db/service/base/response.js b/src/app/db/service/base/response.js
new file mode 100644
index 0000000..a4bac60
--- /dev/null
+++ b/src/app/db/service/base/response.js
@@ -0,0 +1,118 @@
+/**
+ * Service API responses
+ * @module app/db/service/base/response
+ */
+
+import { zipCSVs, stringifyCSV, sanitizeCSV } from "app/utils/file_utils";
+import debugModule from "debug";
+
+/**
+ * Debug logger
+ */
+const debug = debugModule("shoebox:service");
+
+/**
+ * Middleware to return the response as JSON or CSV
+ */
+export async function sendResponse(request, response) {
+  if (response.locals.csv) {
+    if (response.locals.csv.files) {
+      await sendCSVZipResponse(response);
+    } else {
+      await sendCSVResponse(response);
+    }
+  } else {
+    await sendJSONResponse(response);
+  }
+}
+
+/**
+ * Respond with a ZIP file containing multiple CSVs
+ * @param  {Response} response  the response object
+ */
+async function sendCSVZipResponse(response) {
+  const zipData = await zipCSVs(response.locals.csv.files);
+  response.set("Content-Type", "application/zip");
+  response.set(
+    "Content-Disposition",
+    `attachment; filename=${response.locals.csv.filename}`
+  );
+  response.set("Content-Length", zipData.length);
+  response.set("Access-Control-Expose-Headers", "Content-Disposition");
+  response.write(zipData, "binary");
+  response.end(null, "binary");
+}
+
+/**
+ * Respond with a single CSV
+ * @param  {Response} response  the response object
+ */
+async function sendCSVResponse(response) {
+  const csvData = await stringifyCSV(sanitizeCSV(response.locals.csv.data));
+  response.set("Content-Type", "text/csv; charset=utf-8");
+  response.set(
+    "Content-Disposition",
+    `attachment; filename=${response.locals.csv.filename}`
+  );
+  response.set("Content-Length", csvData.length);
+  response.set("Access-Control-Expose-Headers", "Content-Disposition");
+  response.send(csvData);
+}
+
+/**
+ * Respond with JSON
+ * @param  {Response} response  the response object
+ */
+async function sendJSONResponse(response) {
+  response.json(response.locals);
+}
+
+/**
+ * Error-handling middleware
+ * @param  {Error}            error    an error object
+ * @param  {express.request}  request  the request object
+ * @param  {express.response} response the response object
+ * @param  {function}         next     function to proceed to the next middleware
+ */
+export function handleError(error, request, response, next) {
+  const { errors } = request.service.options;
+  debug("Error", error.name, error.message);
+  let message;
+
+  if (errors) {
+    Object.keys(errors).some((key) => {
+      if (error.message.match(key)) {
+        message = errors[key];
+        return true;
+      }
+      return false;
+    });
+    if (message) {
+      response.status(403).send({ code: 403, error: message });
+      return;
+    }
+  }
+
+  if (error.name === "UnauthorizedError") {
+    debug("Unauthorized");
+    response.status(401).send({ code: 401, error: error.message });
+  } else if (error.message === "EmptyResponse") {
+    debug("Not found");
+    response.status(404).send({ code: 404, error: error.message });
+  } else if (error.message === "UserNotActive") {
+    debug("User not active");
+    response.status(401).send({ code: 401, error: error.message });
+  } else if (error.message === "PermissionsError") {
+    debug("Insufficient permissions");
+    response.status(401).send({ code: 401, error: error.message });
+  } else {
+    debug("Unexpected error");
+    // debug(error);
+    response.status(403).send({
+      code: 403,
+      error: error.name || error.message,
+      message: error.message,
+    });
+    next();
+  }
+}
diff --git a/src/app/db/service/pivot/helpers.js b/src/app/db/service/pivot/helpers.js
new file mode 100644
index 0000000..1d8f7f2
--- /dev/null
+++ b/src/app/db/service/pivot/helpers.js
@@ -0,0 +1,113 @@
+/**
+ * Pivot Table Helpers
+ * @module app/db/service/pivot/helpers
+ */
+
+import * as db from "app/db/query";
+
+/**
+ * Fetch a set of database objects from a pivot table via relations
+ * @param {bookshelf.Model} Model              the pivot table model to query
+ * @param {string}          parentIdAttribute  the parent ID attribute on the pivot
+ * @param {number}          parentId           the parent ID to query
+ * @param {string}          childIdAttribute   the child ID attribute on the pivot
+ * @param {number}          childId            the child ID to query
+ */
+export function indexPivotTable({
+  Model,
+  parentIdAttribute,
+  parentId,
+  childIdAttribute,
+  childId,
+}) {
+  return Model.query((builder) => {
+    builder.where(parentIdAttribute, parentId);
+    if (Array.isArray(childId)) {
+      builder.whereIn(childIdAttribute, childId);
+    } else {
+      builder.andWhere(childIdAttribute, childId);
+    }
+  }).fetchAll();
+}
+
+/**
+ * Get the parent, child, and pivot models from a service, as well as their ID attribute names
+ * @param  {Service} service  the pivot table service
+ * @return {object}           the models and ID attribute names
+ */
+export function getPivotModels(service) {
+  const {
+    parent,
+    Model,
+    ChildModel,
+    childRelation,
+    parentPivotRelation,
+    pivotChildRelation,
+  } = service.options;
+  const { Model: ParentModel } = service.options.parent;
+  const parentIdAttribute = ParentModel.prototype.idAttribute;
+  const pivotIdAttribute = Model.prototype.idAttribute;
+  const childIdAttribute = ChildModel.prototype.idAttribute;
+  const parentTableName = ParentModel.prototype.tableName;
+  const pivotTableName = Model.prototype.tableName;
+  const childTableName = ChildModel.prototype.tableName;
+  return {
+    parent,
+    Model,
+    ParentModel,
+    ChildModel,
+    parentTableName,
+    pivotTableName,
+    childTableName,
+    parentIdAttribute,
+    pivotIdAttribute,
+    childIdAttribute,
+    parentPivotRelation,
+    pivotChildRelation,
+    childRelation,
+  };
+}
+
+/**
+ * Create a single instance
+ * @param  {bookshelf.Model} options.Model     the model
+ * @param  {Object}          options.data      data to add
+ * @param  {Array}           options.instances fetched instances to dedupe
+ * @return {Object}                            result
+ */
+export async function handleCreateOne({ Model, data, instances }) {
+  if (instances.length) {
+    throw new Error("pivot relation already exists");
+  }
+  return await db.create({ Model, data });
+}
+
+/**
+ * Create multiple instances
+ * @param  {bookshelf.Model} options.Model             the model
+ * @param  {Object}          options.data              data to add
+ * @param  {Array}           options.instances         fetched instances to dedupe
+ * @param  {string}          options.childIdAttribute  the child ID, found on the body, which should point to an array
+ * @return {Object}                                    result
+ */
+export async function handleCreateMany({
+  Model,
+  data,
+  childIdAttribute,
+  instances,
+}) {
+  let { [childIdAttribute]: child_ids, ...columns } = data;
+  const matched = new Set(
+    instances.map((instance) => instance.get(childIdAttribute))
+  );
+  return await Promise.all(
+    Array.from(new Set(child_ids))
+      .filter((child_id) => !matched.has(child_id))
+      .map((child_id) =>
+        db.create({
+          Model,
+          data: { ...columns, [childIdAttribute]: child_id },
+        })
+      )
+  );
+}
diff --git a/src/app/db/service/pivot/index.js b/src/app/db/service/pivot/index.js
new file mode 100644
index 0000000..ded69b8
--- /dev/null
+++ b/src/app/db/service/pivot/index.js
@@ -0,0 +1,47 @@
+/**
+ * Pivot Table API Service
+ * @module app/db/service/pivot/index
+ */
+
+import Service from "app/db/service/base";
+import * as pivotMethods from "app/db/service/pivot/methods";
+import { loadColumns } from "app/db/helpers";
+
+/**
+ * Create a relational API service to access models via a pivot table.
+ * All options are the same as on a normal service, but with a few changes:
+ * @param {Model|string}  options.Model                    the Bookshelf pivot model
+ * @param {string}        options.ChildModel               the Bookshelf model inheriting from the pivot
+ * @param {string}        options.parentPivotRelation      relation method on the parent that will access the pivot
+ * @param {string}        options.pivotChildRelation       relation method on the pivot that will access the child
+ * @param {string}        options.childRelation            relation method on the parent that will access the children
+ * @return {Service} the service object
+ */
+export default async function PivotTableService(options) {
+  const { ChildModel, bookshelf } = options;
+
+  /** Locate the Model specified in the configuration */
+  options.ChildModel =
+    ChildModel && typeof ChildModel === "string"
+      ? bookshelf.model(ChildModel)
+      : ChildModel;
+
+  /** Due to the way English is inflected, sometimes these are the same and only one needs to be specified */
+  options.pivotChildRelation =
+    options.pivotChildRelation || options.childRelation;
+
+  options.pivotColumns =
+    ChildModel && (await loadColumns(bookshelf, ChildModel));
+
+  // /** Use the model to identify the service's resource name */
+  // options.parentResource = options.name || service.Model?.prototype.tableName;
+  // if (!service.resource) {
+  //   throw new Error("No name or model defined for resource");
+  // }
+
+  const service = await Service(options, pivotMethods);
+  service.type = "pivot";
+  service.ChildModel = options.ChildModel;
+
+  return service;
+}
diff --git a/src/app/db/service/pivot/methods.js b/src/app/db/service/pivot/methods.js
new file mode 100644
index 0000000..1f54345
--- /dev/null
+++ b/src/app/db/service/pivot/methods.js
@@ -0,0 +1,330 @@
+/**
+ * Pivot Table Service API Methods
+ * @module app/db/service/pivot/methods
+ */
+
+import * as db from "app/db/query";
+import {
+  buildPaginationResponse,
+  getOffsetAndLimit,
+  getSort,
+  getQueryFilters,
+  reduceValidColumns,
+  tableNameToModelName,
+} from "app/db/helpers";
+import {
+  indexPivotTable,
+  getPivotModels,
+  handleCreateOne,
+  handleCreateMany,
+} from "app/db/service/pivot/helpers";
+import { PERMISSIONS } from "app/constants";
+import debugModule from "debug";
+
+/**
+ * Debug logger
+ */
+const debug = debugModule("shoebox:service:pivot");
+
+/**
+ * API to index a model via a pivot table.  Allows pagination, filtering.
+ */
+export function index(service) {
+  const { queryBuilder } = service;
+  const { pivotColumns, paginate } = service.options;
+  const {
+    parent,
+    Model,
+    ChildModel,
+    pivotTableName,
+    childTableName,
+    parentIdAttribute,
+    childIdAttribute,
+    parentPivotRelation,
+    childRelation,
+  } = getPivotModels(service);
+
+  const pivotAttribute = Model.prototype.idAttribute.replace(/_id$/, "");
+  // console.log(pivotIdAttribute, pivotAttribute);
+
+  return async function indexPivotMiddleware(request, response, next) {
+    const { query } = request;
+    const withRelated = query.related ? query.related.split(",") : [];
+
+    const filters = getQueryFilters(query);
+    const hasFilters = Object.keys(filters).length > 0;
+    const { offset, limit } = getOffsetAndLimit(query, paginate);
+    const { sortField, sortDirection, sortCount } = getSort(query, ChildModel);
+
+    // Fetch the immediate parent of the pivot table based on the name of the parent resource.
+    // This instance is added to the `request.parents` object when performing the permissions check.
+    const parentInstance = request.parents[parent.resource];
+
+    // Fetch the children of the pivot table.
+    let childData;
+
+    if (sortCount) {
+      // Sort by the count of a secondary pivot relation on the child
+      const sortTableName = childTableName + "_" + sortField;
+      const sortOrderName = sortField + "_count";
+      const sortTableModel = request.bookshelf.model(
+        tableNameToModelName(childTableName + "_" + sortField)
+      );
+      if (!sortTableModel) {
+        return next(new Error("No such pivot table"));
+      }
+      let query = request.bookshelf.knex
+        .from(childTableName)
+        .select(
+          childTableName + ".*",
+          request.bookshelf
+            .knex(sortTableName)
+            .count("*")
+            .whereRaw("?? = ??", [
+              sortTableName + "." + childIdAttribute,
+              childTableName + "." + childIdAttribute,
+            ])
+            .as(sortOrderName)
+        )
+        .leftJoin(
+          pivotTableName,
+          pivotTableName + "." + childIdAttribute,
+          childTableName + "." + childIdAttribute
+        )
+        .where(
+          pivotTableName + "." + parentIdAttribute,
+          "=",
+          parentInstance.id
+        );
+
+      if (hasFilters) {
+        query.andWhere((builder) =>
+          queryBuilder(builder, pivotColumns, filters)
+        );
+      }
+
+      childData = await query
+        .orderBy(sortOrderName, sortDirection)
+        .offset(offset)
+        .limit(limit);
+    } else {
+      // Typical sort
+      childData = await parentInstance
+        .related(childRelation)
+        .query((builder) => {
+          if (hasFilters) {
+            builder.where((builder) =>
+              queryBuilder(builder, pivotColumns, filters)
+            );
+          }
+          builder.orderBy(sortField, sortDirection);
+          if (limit) {
+            builder.limit(limit);
+          }
+          if (offset) {
+            builder.offset(offset);
+          }
+          return builder;
+        })
+        .fetch({ withRelated });
+
+      // Fetch the pivot table in case there are any necessary values on it
+      await Promise.all(
+        childData.map(async (item) => {
+          item.set(pivotAttribute, await item.pivot.fetch());
+        })
+      );
+    }
+
+    // Count the pivot table and generate pagination
+    let rowCount;
+    if (hasFilters) {
+      rowCount = await parentInstance
+        .related(childRelation)
+        .where((builder) => queryBuilder(builder, pivotColumns, filters))
+        .count();
+    } else {
+      rowCount = await parentInstance.related(parentPivotRelation).count();
+    }
+    const pagination = buildPaginationResponse({ rowCount, query, paginate });
+
+    response.locals = { data: childData, pagination, query };
+    next();
+  };
+}
+
+/**
+ * API to fetch a single relation via the pivot table.
+ */
+export function show(service) {
+  const {
+    parent,
+    Model,
+    parentIdAttribute,
+    childIdAttribute,
+    pivotChildRelation,
+  } = getPivotModels(service);
+
+  return async function showPivotMiddleware(request, response, next) {
+    const { query, parents } = request;
+    const parentInstance = parents[parent.resource];
+    const withRelated = query.related ? query.related.split(",") : [];
+
+    let child, data;
+
+    try {
+      data = await db.show({
+        Model: Model,
+        field: childIdAttribute,
+        objectID: parseInt(request.params.id),
+        criteria: {
+          [parentIdAttribute]: parentInstance.get(parentIdAttribute),
+        },
+        withRelated,
+      });
+    } catch (error) {
+      debug(`${service.resource} Error fetching pivot`);
+      debug(error);
+      return next(error);
+    }
+
+    if (!data) {
+      response.locals = {};
+      next();
+    }
+
+    try {
+      child = await data.related(pivotChildRelation).fetch();
+    } catch (error) {
+      debug(`${service.resource} Error fetching child`);
+      debug(error);
+      return next(error);
+    }
+
+    response.locals = { child, data };
+    next();
+  };
+}
+
+/**
+ * API to insert a new record
+ */
+export function create(service) {
+  const { Model, parentIdAttribute, childIdAttribute } = getPivotModels(
+    service
+  );
+  return async function createPivotMiddleware(request, response, next) {
+    const { params } = request;
+    let data;
+    const body = reduceValidColumns(
+      request.body,
+      service.columns,
+      service.options.privateFields
+    );
+    if (service.options.parent) {
+      service.idAttributes.forEach((idAttribute) => {
+        if (idAttribute in params && idAttribute in service.columns) {
+          body[idAttribute] = parseInt(params[idAttribute]);
+        }
+      });
+    }
+    try {
+      const instances = await indexPivotTable({
+        Model,
+        parentIdAttribute,
+        parentId: body[parentIdAttribute],
+        childIdAttribute,
+        childId: body[childIdAttribute],
+      });
+      if (Array.isArray(body[childIdAttribute])) {
+        data = await handleCreateMany({
+          Model: service.Model,
+          data: body,
+          childIdAttribute,
+          instances,
+        });
+      } else {
+        data = await handleCreateOne({
+          Model: service.Model,
+          data: body,
+          instances,
+        });
+      }
+    } catch (error) {
+      debug(`${service.resource} create error`);
+      console.error(error);
+      return next(error);
+    }
+    response.locals = { data };
+    next();
+  };
+}
+
+/**
+ * API to update a single record
+ */
+export function update(service) {
+  return async function updatePivotMiddleware(request, response, next) {
+    const { data: instance } = response.locals;
+    const { user, permission } = request;
+    if (
+      permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+      instance.get("user_id") !== user.user_id
+    ) {
+      return next(new Error("PermissionsError"));
+    }
+    const body = reduceValidColumns(
+      request.body,
+      service.columns,
+      service.options.privateFields
+    );
+    let data;
+    try {
+      data = await db.update({
+        instance,
+        data: body,
+      });
+    } catch (error) {
+      debug(`${service.resource} update error`);
+      debug(error);
+      next(new Error(error));
+    }
+    response.locals = { data };
+    next();
+  };
+}
+
+/**
+ * API to destroy a pivot table relation.
+ */
+export function destroy(service) {
+  const { Model, parentIdAttribute, childIdAttribute } = getPivotModels(
+    service
+  );
+  return async function destroyPivotMiddleware(request, response, next) {
+    const idsToDelete = request.params.id || request.body[childIdAttribute];
+    try {
+      const instances = await indexPivotTable({
+        Model,
+        parentIdAttribute,
+        parentId: request.params[parentIdAttribute],
+        childIdAttribute,
+        childId: idsToDelete,
+      });
+      await Promise.all(instances.map((instance) => instance.destroy()));
+    } catch (error) {
+      debug(`${service.resource} destroy error`);
+      debug(error);
+      return next(new Error(error));
+    }
+    response.locals.success = true;
+    response.locals.id = idsToDelete;
+    next();
+  };
+}
+
+/**
+ * API to destroy many records. Note that the normal destroy API accomplishes this.
+ * @type {Function}
+ */
+export const destroyMany = destroy;
diff --git a/src/app/index.js b/src/app/index.js
new file mode 100644
index 0000000..ee78c16
--- /dev/null
+++ b/src/app/index.js
@@ -0,0 +1,48 @@
+/**
+ * Backend Express HTTP server.
+ * @module app/index
+ */
+
+import express from "express";
+import http from "http";
+import bodyParser from "body-parser";
+import compression from "compression";
+import cors from "cors";
+import morgan from "morgan";
+// import multer from 'multer'
+
+import services from "app/services";
+
+/**
+ * Create the API server.
+ * @param  {Knex}   knex  an existing Knex instance (optional, used in test scripts)
+ * @return {Object}       an Express app and HTTP server
+ */
+export default async function createServer(knex) {
+  const app = new express();
+  const server = http.createServer(app);
+
+  app.disable("x-powered-by");
+  app.use(
+    morgan("dev", {
+      skip: (request) => request.method === "OPTIONS",
+    })
+  );
+  app.use(bodyParser.json({ limit: "100mb" }));
+  app.use(bodyParser.urlencoded({ extended: false, limit: "100mb" }));
+  app.use(express.query());
+  app.use(compression());
+  app.set("trust proxy", true);
+
+  if (process.env.NODE_ENV === "development") {
+    const corsMiddleware = cors({
+      origin: ["http://localhost:3000", "http://0.0.0.0:3000"],
+    });
+    app.use(corsMiddleware);
+    app.options("*", corsMiddleware);
+  }
+
+  await services.configure(app, knex);
+
+  return { app, server };
+}
diff --git a/src/app/services/authentication/helpers.js b/src/app/services/authentication/helpers.js
new file mode 100644
index 0000000..49f1e2a
--- /dev/null
+++ b/src/app/services/authentication/helpers.js
@@ -0,0 +1,83 @@
+/**
+ * Authentication helper functions.
+ * @module app/services/authentication/helpers
+ */
+
+import jsonwebtoken from "jsonwebtoken";
+import expressJwt from "express-jwt";
+import { createHmac, createHash, randomBytes } from "crypto";
+
+/**
+ * Generate a random secret
+ * @return {string} a random 64-byte secret token
+ */
+export const generateSecret = () => randomBytes(64).toString("hex");
+
+/**
+ * Store a password as SHA256 using the secret token
+ * @param  {string} plainPassword  the plaintext password
+ * @return {string} the sha256 of the password
+ */
+export const encryptPassword = (plainPassword) =>
+  createHmac("sha256", process.env.TOKEN_SECRET)
+    .update(plainPassword, "utf8")
+    .digest("hex");
+
+/**
+ * Generate a JSON web token with the desired payload.
+ * @param  {Object} payload  the object to attach to the JWT
+ * @return {string} a JSON web token
+ */
+export const generateAccessToken = (payload) =>
+  jsonwebtoken.sign(payload, process.env.TOKEN_SECRET);
+
+/**
+ * Middleware to encrypt any passwords before they hit the database.
+ */
+export const storeEncryptedPassword = (request, response, next) => {
+  if (request.body.password) {
+    request.body.password = encryptPassword(request.body.password);
+  }
+  next();
+};
+
+/**
+ * Hash a password, used in testing and when seeding the databse.
+ * Typically the passwords are hashed on the client side before being transmitted,
+ * and then hashed again before being inserted into the database.
+ * @param  {string}  plaintext  the plaintext password
+ * @return {string}  the SHA256 of the password
+ */
+export function hashPassword(plaintext) {
+  return createHash("sha256").update(plaintext, "utf8").digest("base64");
+}
+
+/**
+ * Express JWT middleware.
+ * @param {object} options  options to pass to express-jwt
+ * @return {Function} the express-jwt middleware
+ */
+export function checkAccessToken(options = {}) {
+  if (!process.env.TOKEN_SECRET) return null;
+  return expressJwt({
+    secret: process.env.TOKEN_SECRET,
+    algorithms: ["HS256"],
+    credentialsRequired: true,
+    ...options,
+  });
+}
+
+/**
+ * Middleware to check if a user is active before performing an API call.
+ */
+export async function checkUserIsActive(request, response, next) {
+  const User = request.bookshelf.model("User");
+  const user = await new User({
+    user_id: request.user.user_id,
+  }).fetch();
+  if (!user.get("is_active")) {
+    next(new Error("UserNotActive"));
+  } else {
+    next();
+  }
+}
diff --git a/src/app/services/authentication/index.js b/src/app/services/authentication/index.js
new file mode 100644
index 0000000..df8d7d0
--- /dev/null
+++ b/src/app/services/authentication/index.js
@@ -0,0 +1,140 @@
+/**
+ * Authentication API service.
+ * @module app/services/authentication/index
+ */
+
+import {
+  encryptPassword,
+  generateAccessToken,
+  checkAccessToken,
+  checkUserIsActive,
+} from "app/services/authentication/helpers";
+import { ROLES } from "app/constants";
+import { randomFruit } from "app/utils/random_utils";
+
+import Service from "app/db/service/base";
+
+/**
+ * Service API for authenticating users
+ */
+export default async function AuthenticationService(bookshelf) {
+  const service = await Service({
+    bookshelf,
+    name: "authentication",
+    authenticate: false,
+    routes: [
+      {
+        method: "post",
+        route: "/login",
+        handlers: [login, sendAccessToken],
+        summary: "Generate a JWT for a user",
+        requestBody: {
+          username: {
+            type: "string",
+            description: "The username",
+          },
+          password: {
+            type: "string",
+            description: "The SHA256 of the user's password",
+          },
+        },
+      },
+      {
+        route: "/check",
+        handlers: [checkAccessToken(), checkUserIsActive, sendIsActive],
+        summary:
+          "Check if the user is still active. Simply queries using the bearer token",
+        requestBody: {
+          type: "object",
+          properties: {
+            active: {
+              type: "boolean",
+              description: "Will be true if the user is active",
+            },
+          },
+        },
+      },
+      {
+        route: "/generate",
+        handlers: [checkAccessToken(), checkUserIsActive, generatePassword],
+        summary:
+          "Generate a temporary random password.  Passwords are long and memorable, but are not guaranteed to be cryptographically secure.",
+        requestBody: {
+          type: "object",
+          properties: {
+            password: {
+              type: "string",
+              description: "A temporary password.",
+            },
+          },
+        },
+      },
+    ],
+  });
+
+  /**
+   * Login API handler
+   */
+  async function login(request, response, next) {
+    const User = bookshelf.model("User");
+    const { username, password: plainPassword } = request.body;
+    const password = encryptPassword(plainPassword);
+    let user, error;
+    try {
+      user = await new User({
+        username,
+        password,
+      }).fetch();
+    } catch (dbError) {
+      error = dbError;
+    }
+    if (user && user.get("is_active")) {
+      request.user = user;
+    } else {
+      error = new Error("UserNotActive");
+    }
+    next(error);
+  }
+
+  /**
+   * Generate a JWT if the login was successful, and set it on the response object.
+   */
+  function sendAccessToken(request, response, next) {
+    const token = {
+      user_id: request.user.get("user_id"),
+      username: request.user.get("username"),
+      role: request.user.get("role"),
+    };
+    if (token.role < ROLES.moderator) {
+      token.can_search = request.user.get("can_search");
+    }
+    response.locals.token = generateAccessToken(token);
+    next();
+  }
+
+  /**
+   * Generate a random passphrase
+   */
+  async function generatePassword(request, response, next) {
+    // this emits a weird error when using `import` syntax
+    response.locals.password = [
+      randomFruit("en", { maxWords: 1 }),
+      randomFruit("en", { maxWords: 1 }),
+      randomFruit("en", { maxWords: 1 }),
+      randomFruit("en", { maxWords: 1 }),
+    ]
+      .join(" ")
+      .toLowerCase();
+    next();
+  }
+
+  /**
+   * Indicate that a user is active.  Should have already been interrupted if the user is inactive.
+   */
+  function sendIsActive(request, response, next) {
+    response.locals.active = true;
+    next();
+  }
+
+  return service;
+}
diff --git a/src/app/services/index.js b/src/app/services/index.js
new file mode 100644
index 0000000..ea0175d
--- /dev/null
+++ b/src/app/services/index.js
@@ -0,0 +1,70 @@
+/**
+ * Configure services and mount them.
+ * @module app/services/index
+ */
+
+import AuthenticationService from "app/services/authentication";
+import ShoeService from "app/services/shoe";
+
+import configureDatabase from "app/db/configure";
+
+/**
+ * A cache of registered services
+ * @type {Object}
+ */
+const services = {};
+
+/**
+ * Function to get a service by resource name
+ * @param {String} resourceName  name of the service to fetch
+ */
+function get(resourceName) {
+  return services[resourceName];
+}
+
+/**
+ * List services
+ * @return {object}  the services object
+ */
+function list() {
+  return Object.entries(services);
+}
+
+/**
+ * Configure the API endpoints
+ * @param {Express} app   Express application
+ * @param {Knex}    knex  Knex instance (optional)
+ */
+async function configure(app, knex) {
+  const { bookshelf } = configureDatabase(knex);
+
+  /**
+   * Connect a service
+   * @param  {Service} RoutableService  the service to connect
+   * @return {express.Router}           the service's router
+   */
+  async function connect(RoutableService) {
+    const service = await RoutableService(bookshelf);
+    services[service.resource] = service;
+    return service.router;
+  }
+
+  app.use("/api/v1/auth", await connect(AuthenticationService));
+  app.use("/api/v1/shoe", await connect(ShoeService));
+
+  app.get("/api/v1/", describeApplication);
+  // app.get("/", describeApplication);
+}
+
+/**
+ * Canary middleware to indicate that the API is working
+ */
+function describeApplication(request, response) {
+  response.json({ application: "Shoebox" });
+}
+
+export default {
+  configure,
+  get,
+  list,
+};
diff --git a/src/app/services/permission/helpers.js b/src/app/services/permission/helpers.js
new file mode 100644
index 0000000..46a7871
--- /dev/null
+++ b/src/app/services/permission/helpers.js
@@ -0,0 +1,133 @@
+/**
+ * Permission helpers.
+ * @module app/services/permission/helpers
+ */
+
+import { PERMISSIONS } from "app/constants";
+
+/**
+ * When assigning a permission (any type), store the ID of the user granting the permission
+ */
+export function setGrantedByUser(request, response, next) {
+  request.body.granted_by_user_id = request.user.user_id;
+  next();
+}
+
+/**
+ * Return a middleware function that checks permissions for a specific API method
+ * @param  {object} permissions  Service API permission dictionary. See Service API documentation.
+ * @param  {string} action       which action to check, read/create/update/destroy
+ * @return {function} middleware function that checks permissions
+ */
+export function checkPermission(permissions, action) {
+  const routePermission = permissions
+    ? action
+      ? permissions[action]
+      : permissions
+    : null;
+  return async function checkPermissionsMiddleware(request, response, next) {
+    const { user } = request;
+    if (!routePermission) {
+      // API access must be explicitly enabled.
+      request.permission = PERMISSIONS.DENY;
+      next(new Error("PermissionsError"));
+    } else if (routePermission.roles?.includes(user.role)) {
+      request.permission = PERMISSIONS.ALLOW;
+      next();
+    } else if (routePermission.owner) {
+      request.permission = PERMISSIONS.ALLOW_FOR_OWNER;
+      next();
+    } else if (routePermission.check) {
+      request.permission = await routePermission.check({
+        request,
+        user,
+        id: request.params?.id,
+      });
+      if (request.permission === PERMISSIONS.DENY) {
+        next(new Error("PermissionsError"));
+      } else {
+        next();
+      }
+    } else {
+      request.permission = PERMISSIONS.DENY;
+      next(new Error("PermissionsError"));
+    }
+  };
+}
+
+/**
+ * Return a middleware function that recursively checks permissions on the parent service of an API method.
+ * Also checks if the parent object exists to begin with.
+ * @param  {Service} parentService  the service to check.
+ * @return {function}               the Express middleware
+ */
+export function checkParentPermission(parentService) {
+  return async function checkParentMiddleware(request, response, next) {
+    let permission;
+    request.parents = {};
+    try {
+      permission = await checkServicePermission(request, parentService);
+    } catch (error) {
+      console.error(error);
+      permission = PERMISSIONS.DENY;
+    }
+    request.permission = permission;
+    if (permission === PERMISSIONS.DENY) {
+      next(new Error("PermissionsError"));
+    } else {
+      next();
+    }
+  };
+}
+
+/**
+ * Helper function to check permissions on a service
+ * @param  {Request} request  the Express request we're checking
+ * @param  {Service} service  the Service we're checking
+ * @return {permission}       the evaluated permissions
+ */
+async function checkServicePermission(request, service) {
+  const user = request.user;
+  const readPermissions = service.options.permissions.read;
+  if (!readPermissions) {
+    return PERMISSIONS.DENY;
+  }
+
+  /** Check read permissions on any parent objects first. */
+  if (service.parent) {
+    const parentPermission = await checkServicePermission(
+      request,
+      service.parent
+    );
+    if (parentPermission === PERMISSIONS.DENY) {
+      return PERMISSIONS.DENY;
+    }
+  }
+
+  /** Make sure this service's object exists before proceeding. */
+  const idAttribute = service.Model.prototype.idAttribute;
+  const objectId = request.params[idAttribute];
+  const object = await new service.Model({ [idAttribute]: objectId }).fetch();
+  request.parents[service.resource] = object;
+
+  if (!object) {
+    /** If there is no object, deny */
+    return PERMISSIONS.DENY;
+  } else if (readPermissions.roles?.includes(user.role)) {
+    /** Pass role permission check */
+    return PERMISSIONS.ALLOW;
+  } else if (readPermissions.owner && object.get("user_id") === user.user_id) {
+    /** Pass owner permission check */
+    return PERMISSIONS.ALLOW;
+  } else if (readPermissions.check) {
+    /** Pass custom permission check */
+    return await readPermissions.check({
+      request,
+      user,
+      id: objectId,
+    });
+  } else {
+    /** No checks passed - deny */
+    return PERMISSIONS.DENY;
+  }
+}
diff --git a/src/app/services/shoe/index.js b/src/app/services/shoe/index.js
new file mode 100644
index 0000000..fc8bbe4
--- /dev/null
+++ b/src/app/services/shoe/index.js
@@ -0,0 +1,34 @@
+/**
+ * Shoe API service.
+ * @module app/services/shoe/index
+ */
+
+import Service from "app/db/service/base";
+
+/**
+ * Service API for listing shoes
+ */
+export default async function ShoeService(bookshelf) {
+  const service = await Service({
+    bookshelf,
+    Model: bookshelf.model("Shoe"),
+    authenticate: false,
+    paginate: {
+      pageSize: 25,
+      pageSizeLimit: 100,
+    },
+    enabled: ["index", "create"],
+    hooks: {
+      before: {
+        index: [],
+        show: [],
+      },
+      after: {
+        index: [],
+        show: [],
+      },
+    },
+  });
+
+  return service;
+}
diff --git a/src/app/utils/data_utils.js b/src/app/utils/data_utils.js
new file mode 100644
index 0000000..327e594
--- /dev/null
+++ b/src/app/utils/data_utils.js
@@ -0,0 +1,34 @@
+/**
+ * Data manipulation utilities.
+ * @module app/utils/data_utils
+ */
+
+/**
+ * Convert an enum to a lookup
+ * @param  {Object}  dictionary: number -> string
+ * @return {Object}  dictionary: string -> number
+ */
+export const enumLookup = (dict) =>
+  Object.entries(dict).reduce((lookup, pair) => {
+    lookup[pair[1]] = parseInt(pair[0]);
+    return lookup;
+  }, {});
+
+/**
+ * Divide an array into N-item chunks
+ * @param  {Array}        arr   array to be chunked
+ * @param  {number}       size  items per chunk
+ * @return {Array} array of array chunks
+ */
+export const chunk = (arr, size) =>
+  arr.length > size
+    ? [arr.slice(0, size), ...chunk(arr.slice(size), size)]
+    : [arr];
+
+/**
+ * Delay during async operation
+ * @param  {number}  delay time to delay
+ * @return {Promise}       promise that resolves after a delay
+ */
+export const delay = (delay = 1) =>
+  new Promise((resolve) => setTimeout(resolve, delay));
diff --git a/src/app/utils/file_utils.js b/src/app/utils/file_utils.js
new file mode 100644
index 0000000..39e8cd5
--- /dev/null
+++ b/src/app/utils/file_utils.js
@@ -0,0 +1,222 @@
+/**
+ * File system utilities.
+ * @module app/utils/file_utils
+ */
+
+import filesystem from "fs";
+import parseCSV from "csv-parse";
+import stringifyCSVCallback from "csv-stringify";
+import archiver from "archiver";
+import { streamToBuffer } from "@jorgeferrero/stream-to-buffer";
+
+import StreamArray from "stream-json/streamers/StreamArray";
+import { chain } from "stream-chain";
+import zlib from "zlib";
+
+// eslint-disable-next-line no-control-regex
+const SANITIZE_CSV_INITIALS = new RegExp("^[-=+@\\x09\\x0D]");
+
+/**
+ * Helper function to load CSV from a file
+ * @param  {string} inputFile  path to the file
+ * @return {Promise} promise which will resolve with the parsed CSV
+ */
+
+const csvReadOptions = {
+  columns: true,
+};
+
+export const loadCSV = (inputFile, options = {}) =>
+  new Promise((resolve, reject) => {
+    if (!filesystem.existsSync(inputFile)) {
+      return reject("inputFile does not exist");
+    }
+    const csvOptions = {
+      ...csvReadOptions,
+      ...options,
+    };
+    filesystem.readFile(inputFile, "utf8", (error, text) => {
+      if (error) {
+        return reject(`Error reading file: ${error}`);
+      }
+      try {
+        parseCSV(text, csvOptions, function (err, data) {
+          if (err) {
+            return reject("Error parsing JSON");
+          }
+          resolve(data);
+        });
+      } catch {
+        reject("Error parsing JSON");
+      }
+    });
+  });
+
+/**
+ * Helper function to stringify an array-of-arrays to a CSV (with Promise interface)
+ * @param  {Array[]} rows  array of arrays
+ * @return {Promise} promise which will resolve with the stringified CSV
+ */
+
+export const stringifyCSV = (rows) =>
+  new Promise((resolve, reject) => {
+    stringifyCSVCallback(rows, (error, output) => {
+      if (error) {
+        reject(error);
+      } else {
+        resolve(output);
+      }
+    });
+  });
+
+/**
+ * Helper function to attempt to mitigate malicious CSV data
+ * @param {Array[]} rows array of arrays
+ * @return {Array[]} the sanitized input
+ */
+export const sanitizeCSV = (rows) =>
+  rows.map((row) =>
+    row.map((cell) =>
+      cell && typeof cell === "string" && cell.match(SANITIZE_CSV_INITIALS)
+        ? "'" + cell
+        : cell
+    )
+  );
+
+/**
+ * Given a list of files containing CSV, return a binary ZIP
+ * @param {array} files            list of files
+ * @param {array} files[].data     CSV data (array of arrays)
+ * @param {array} files[].filename filename corresponding to this CSV
+ * @return {string} the output ZIP file
+ */
+export const zipCSVs = async (files) => {
+  const archive = archiver("zip", {
+    zlib: { level: 9 }, // Compression level. 0 = store uncompressed, 9 = most compression
+  });
+
+  await Promise.all(
+    files.map(async (file) => {
+      const csvString = await stringifyCSV(sanitizeCSV(file.data));
+      archive.append(csvString, { name: file.filename });
+    })
+  );
+
+  archive.finalize();
+
+  return await streamToBuffer(archive);
+};
+
+/**
+ * Helper function to load JSON from a file
+ * @param  {string} inputFile  path to the file
+ * @return {Promise} promise which will resolve with the parsed JSON
+ */
+export const loadJSON = (inputFile) =>
+  new Promise((resolve, reject) => {
+    if (!filesystem.existsSync(inputFile)) {
+      return reject("inputFile does not exist");
+    }
+    filesystem.readFile(inputFile, "utf8", (error, text) => {
+      if (error) {
+        return reject(`Error reading file: ${error}`);
+      }
+      try {
+        const data = JSON.parse(text);
+        resolve(data);
+      } catch {
+        reject("Error parsing JSON");
+      }
+    });
+  });
+
+const writeFileOptions = {
+  replace: true,
+};
+
+/**
+ * Helper to write a string to a file
+ * @param {string}          outputFile the file to write to
+ * @param {string|string[]} data       the data to write
+ * @param {Object}          options    options, by default will overwrite the existing file
+ * @return {Promise}                   promise which will resolve when the file is saved
+ */
+export const writeFile = (outputFile, data, options = {}) => {
+  options = { ...writeFileOptions, ...options };
+  return new Promise((resolve, reject) => {
+    if (filesystem.existsSync(outputFile) && !options.replace) {
+      return reject("outputFile exists");
+    }
+    if (Array.isArray(data)) {
+      data = data.join("\n");
+    }
+    filesystem.writeFile(outputFile, data, { encoding: "utf8" }, (error) => {
+      if (error) {
+        return reject(`Error writing file: ${error}`);
+      }
+      resolve();
+    });
+  });
+};
+
+const writeJSONOptions = {
+  ...writeFileOptions,
+  indent: true,
+};
+
+/**
+ * Helper to write JSON data to a file
+ * @param {string} outputFile the file to write to
+ * @param {Object} data       the data to write
+ * @param {Object} options    options, by default will overwrite the existing file
+ * @return {Promise}                   promise which will resolve when the file is saved
+ */
+export const writeJSON = (outputFile, data, options = {}) => {
+  options = { ...writeJSONOptions, ...options };
+  return new Promise((resolve, reject) => {
+    let json;
+    try {
+      if (options.indent) {
+        json = JSON.stringify(data, false, 2);
+      } else {
+        json = JSON.stringify(data);
+      }
+    } catch {
+      return reject("couldn't stringify JSON");
+    }
+    writeFile(outputFile, json, options).then(resolve).catch(reject);
+  });
+};
+
+/**
+ * Helper function to map over a JSON file, possibly gzipped
+ * @param  {string}   input   the input file to be loaded
+ * @param  {function} mapper  the mapper function
+ * @param  {object}   state   the master state object
+ */
+export async function mapJSONFile(input, mapper, state) {
+  return new Promise((resolve, reject) => {
+    let pipeline = chain(
+      [filesystem.createReadStream(input)]
+        .concat(input.match(/\.gz$/) ? [zlib.createGunzip()] : [])
+        .concat([
+          StreamArray.withParser(),
+          async (record) => {
+            await mapper(record.key, record.value, state);
+          },
+        ])
+    );
+
+    pipeline.on("data", () => {});
+
+    pipeline.on("end", () => {
+      console.log(`Loaded ${input} ${state.dryrun ? " (dryrun)" : ""}`);
+      resolve();
+    });
+
+    pipeline.on("error", (error) => {
+      console.error(error);
+      reject();
+    });
+  });
+}
diff --git a/src/app/utils/math_utils.js b/src/app/utils/math_utils.js
new file mode 100644
index 0000000..f5482d3
--- /dev/null
+++ b/src/app/utils/math_utils.js
@@ -0,0 +1,94 @@
+/**
+ * Mathematical utilities.
+ * @module app/utils/math_utils
+ */
+
+/**
+ * RegExp to check if a string contains only hexadecimal values.
+ * The string should only contain the characters [0-9] or [a-f], case-insensitive.
+ * @type {RegExp}
+ */
+const HEXADECIMAL_REGEXP = new RegExp("^[a-f0-9]+$", "i");
+
+/**
+ * Check if a string is a valid hexadecimal number.
+ * @param  {String}  text  the string to check
+ * @return {Boolean}       true if the string is a valid hexadecimal number
+ */
+export const isHexadecimal = (text) => !!text?.match(HEXADECIMAL_REGEXP);
+
+/**
+ * Constrain a number between an upper or lower bound.
+ * @param  {Number} value the value to clamp
+ * @param  {Number} low   the lower bound
+ * @param  {Number} high  the upper bound
+ * @return {Number}       the clamped value
+ */
+export const clamp = (value, low = 0, high = 1) =>
+  value < low ? low : value < high ? value : high;
+
+/**
+ * Implementation of mod that supports negative numbers (unlike JavaScript % operator)
+ * @param  {Number} numerator   the modulo numerator
+ * @param  {Number} denominator the modulo denominator
+ * @return {Number}             `numerator mod denominator`
+ */
+export const mod = (numerator, denominator) =>
+  numerator - denominator * Math.floor(numerator / denominator);
+
+/**
+ * Get the mean of a list of numbers (where non-null)
+ * @param  {Array} numbers  list of numbers
+ * @return {number}         arithmetic mean
+ */
+export const arrayMean = (numbers) => {
+  const nonZero = (numbers || []).filter((number) => !!number);
+  if (!nonZero.length) return 0;
+  const sum = nonZero.reduce((a, b) => {
+    return a + b;
+  }, 0);
+  return sum / nonZero.length;
+};
+
+/**
+ * Find the (planar) centroid of a set of points
+ * @param  {Array} items    list of location-like items having { lat, lng }
+ * @return {Object}         object with averaged lat/lng
+ */
+export const centroid = (items) => ({
+  lat: arrayMean(items.map((item) => item.lat)),
+  lng: arrayMean(items.map((item) => item.lng)),
+});
+
+/**
+ * Returns a gaussian (normal) random function with the given mean and stdev.
+ * @param  {Number} mean    center value
+ * @param  {Number} stdev   standard deviation (radius around mean)
+ * @return {Function}       function generating numbers with a normal distribution
+ */
+export function gaussian(mean, stdev, random = Math.random) {
+  let y2;
+  let use_last = false;
+  return () => {
+    let y1;
+    if (use_last) {
+      y1 = y2;
+      use_last = false;
+    } else {
+      let x1, x2, w;
+      do {
+        x1 = 2.0 * random() - 1.0;
+        x2 = 2.0 * random() - 1.0;
+        w = x1 * x1 + x2 * x2;
+      } while (w >= 1.0);
+      w = Math.sqrt((-2.0 * Math.log(w)) / w);
+      y1 = x1 * w;
+      y2 = x2 * w;
+      use_last = true;
+    }
+
+    return mean + stdev * y1;
+    // if (retval > 0) return retval;
+    // return -retval;
+  };
+}
diff --git a/src/app/utils/random_utils.js b/src/app/utils/random_utils.js
new file mode 100644
index 0000000..fc3c0f7
--- /dev/null
+++ b/src/app/utils/random_utils.js
@@ -0,0 +1,171 @@
+/**
+ * Test data generation utilities.
+ * @module app/utils/random_utils
+ */
+
+import { LoremIpsum } from "lorem-ipsum";
+import seedrandom from "seedrandom";
+
+export const rand = (scale) => scale * (Math.random() * 2 - 1);
+export const randint = (scale) => Math.floor(scale * Math.random());
+export const randsign = () => (Math.random() < 0.5 ? -1 : 1);
+export const rand_seq = seedrandom("shoebox");
+export const rand_seq_norm = () => Math.pow(rand_seq(), 2) * randsign() * 0.5;
+export const choice = (list) => list[randint(list.length)];
+export const randomFruit = () => choice(fruits);
+
+export const lorem = new LoremIpsum({
+  sentencesPerParagraph: {
+    max: 4,
+    min: 2,
+  },
+  wordsPerSentence: {
+    max: 10,
+    min: 4,
+  },
+});
+
+export const fruits = `agave
+allspice
+almond
+apple
+apricot
+artichoke
+arugula
+asparagus
+avocado
+balsamic
+banana
+basil
+blackbean
+blueberry
+brandy
+broccoli
+buckwheat
+cacao
+cachaca
+cantaloupe
+caper
+caramel
+caraway
+cardamom
+carrot
+cashew
+cauliflower
+cava
+cayenne
+celery
+chambord
+champagne
+chickpea
+chili
+chipotle
+chive
+cilantro
+cinnamon
+citrus
+clementine
+clove
+coconut
+coffee
+cornbread
+cucumber
+cumin
+daikon
+dill
+eggplant
+espresso
+fennel
+fenugreek
+flower
+garlic
+ghee
+ginger
+grape
+grapefruit
+greentea
+habanero
+hazelnut
+hibiscus
+honey
+honeydew
+horseradish
+huckleberry
+jalapeno
+jasmine
+juniper
+kiwi
+kohlrabi
+kumquat
+lavender
+leek
+lemon
+lemongrass
+macadamia
+mango
+maple
+marjoram
+mint
+miso
+nori
+nutmeg
+olive
+onion
+orange
+oregano
+papaya
+parmesan
+parsley
+parsnip
+peach
+peanut
+pear
+pecan
+pepper
+pernod
+pinenut
+pineapple
+plum
+pluot
+poblano
+pomegranate
+pomelo
+potato
+pumpkin
+quinoa
+raisin
+raspberry
+rhubarb
+rosemary
+saffron
+sage
+sake
+sauerkraut
+savory
+seitan
+sesame
+shallot
+paprika
+soy
+spinach
+squash
+strawberry
+tempei
+tequila
+thyme
+tofu
+tomatillo
+tomato
+turmeric
+vanilla
+vermouth
+violet
+walnut
+wasabi
+watermelon
+wheat
+yam
+yuzu
+zucchini`
+  .split("\n")
+  .map((text) => text.trim());
diff --git a/src/app/utils/set_utils.js b/src/app/utils/set_utils.js
new file mode 100644
index 0000000..88e8fea
--- /dev/null
+++ b/src/app/utils/set_utils.js
@@ -0,0 +1,81 @@
+/**
+ * Operations on sets.
+ * @module app/utils/set_utils
+ */
+
+/**
+ * Determine if `set` contains `subset`
+ * @param  {Set}  set       the superset
+ * @param  {Set}  subset    the subset
+ * @return {Boolean}        true if set contains subset
+ */
+export function isSuperset(set, subset) {
+  for (let elem of subset) {
+    if (!set.has(elem)) {
+      return false;
+    }
+  }
+  return true;
+}
+
+/**
+ * Return the union (A or B) of two sets
+ * @param  {Set}  setA      a set
+ * @param  {Set}  setB      a set
+ * @return {Boolean}        the union of the sets
+ */
+export function union(setA, setB) {
+  let _union = new Set(setA);
+  for (let elem of setB) {
+    _union.add(elem);
+  }
+  return _union;
+}
+
+/**
+ * Return the intersection (A and B) of two sets
+ * @param  {Set}  setA      a set
+ * @param  {Set}  setB      a set
+ * @return {Boolean}        the intersection of the sets
+ */
+export function intersection(setA, setB) {
+  let _intersection = new Set();
+  for (let elem of setB) {
+    if (setA.has(elem)) {
+      _intersection.add(elem);
+    }
+  }
+  return _intersection;
+}
+
+/**
+ * Return the symmetric difference (A xor B) of two sets
+ * @param  {Set}  setA      a set
+ * @param  {Set}  setB      a set
+ * @return {Boolean}        the symmetric difference of the sets
+ */
+export function symmetricDifference(setA, setB) {
+  let _difference = new Set(setA);
+  for (let elem of setB) {
+    if (_difference.has(elem)) {
+      _difference.delete(elem);
+    } else {
+      _difference.add(elem);
+    }
+  }
+  return _difference;
+}
+
+/**
+ * Return the difference (A not B) of two sets
+ * @param  {Set}  setA      a set
+ * @param  {Set}  setB      a set
+ * @return {Boolean}        the difference of the sets
+ */
+export function difference(setA, setB) {
+  let _difference = new Set(setA);
+  for (let elem of setB) {
+    _difference.delete(elem);
+  }
+  return _difference;
+}
diff --git a/src/app/utils/text_utils.js b/src/app/utils/text_utils.js
new file mode 100644
index 0000000..1f987ec
--- /dev/null
+++ b/src/app/utils/text_utils.js
@@ -0,0 +1,93 @@
+/**
+ * Text and sorting utilities.
+ * @module app/utils/text_utils
+ */
+
+/**
+ * Remove HTML-unfriendly characters from a string
+ * @param  {string} text   string to sanitize
+ * @return {string} the sanitized string
+ */
+export const sanitize = (text) =>
+  String(text || "").replace(new RegExp("[<>&]", "g"), "");
+
+/**
+ * Remove non-alphanumerics from a string
+ * @param  {string} text   string to sanitize
+ * @return {string} the sanitized string
+ */
+export const sanitizeName = (text) =>
+  String(text || "").replace(new RegExp("[^-_a-zA-Z0-9]", "g"), "");
+
+/**
+ * Remove HTML from a string
+ * @param  {string} text   string to sanitize
+ * @return {string} the sanitized string
+ */
+export const stripHTML = (text) => String(text || "").replace(/<[^>]+>/g, "");
+
+/**
+ * Replace HTML special characters with entitites
+ * @param  {string} text   string to sanitize
+ * @return {string} the sanitized string
+ */
+export const sanitizeHTML = (text) =>
+  String(text || "")
+    .replace(/&/g, "&amp;")
+    .replace(/</g, "&lt;")
+    .replace(/>/g, "&gt;");
+
+/**
+ * Capitalize a single word
+ * @param  {string} text   string to capitalize
+ * @return {string} the capitalized word
+ */
+export const capitalizeWord = (text) =>
+  text.charAt(0).toUpperCase() + text.slice(1);
+
+/**
+ * Capitalize all words in a string
+ * @param  {string} text   string to capitalize
+ * @return {string} the capitalized string
+ */
+export const capitalize = (text) =>
+  String(text || "")
+    .split(" ")
+    .map(capitalizeWord)
+    .join(" ");
+
+/**
+ * Convert a phrase to a slug
+ * @param  {string} text   string to slugify
+ * @return {string} the slugified string
+ */
+export const slugify = (text) =>
+  String(text || "")
+    .toLowerCase()
+    .replace(/\s/g, "-")
+    .replace(/[^-_a-zA-Z0-9]/g, "-")
+    .replace(/-+/g, "-");
+
+/**
+ * Sort function, sort strings ascending (a -> z)
+ */
+// eslint-disable-next-line id-length
+export const sortString = (a, b) => a[0].localeCompare(b[0]);
+
+/**
+ * Sort function, sort strings descending (z -> a)
+ */
+// eslint-disable-next-line id-length
+export const sortStringDescending = (a, b) => b[0].localeCompare(a[0]);
+
+/**
+ * Sort function, sort numbers ascending (0 -> 1)
+ */
+// eslint-disable-next-line id-length
+export const sortNumeric = (a, b) => a[1] - b[1];
+
+/**
+ * Sort function, sort numbers descending (1 -> 0)
+ */
+// eslint-disable-next-line id-length
+export const sortNumericDescending = (a, b) => b[1] - a[1];
diff --git a/src/app/utils/unicode_utils.js b/src/app/utils/unicode_utils.js
new file mode 100644
index 0000000..c6a2253
--- /dev/null
+++ b/src/app/utils/unicode_utils.js
@@ -0,0 +1,63 @@
+/**
+ * Functions for dealing with Arabic text.
+ * Based on https://github.com/ahmads/arabicString/
+ * For Farsi, it is sufficient to use the `persianRex` NPM module
+ * @module utils/ar_utils
+ */
+
+import makeEmojiRegexp from "emoji-regex/RGI_Emoji.js";
+
+const rtlPunctuation = "،|؟|«|»|؛|٬";
+const ltrPunctuation = "\\.|:|!|-|\\[|\\]|\\(|\\)|\\\\|/";
+
+const punctuationRegexp = new RegExp(
+  "(" + rtlPunctuation + "|" + ltrPunctuation + ")",
+  "gu"
+);
+const arabicRegexp = new RegExp("[\u0621-\u0652]", "gu");
+const arabicLettersRegexp = new RegExp("[\u0621-\u064A]", "gu");
+const arabicDiacriticsRegexp = new RegExp("[\u064B-\u0652]", "gu");
+const emojiRegexp = makeEmojiRegexp();
+
+/**
+ * The percentage of Arabic letters in the `String`.
+ * @param {String} text  Text to process
+ * @returns {Float} percentage from `0.0`` - `1.0`
+ */
+
+export const howArabic = (text) => {
+  if (!text) return 0.0;
+  // strip punctuation, digits and spaces
+  text = text.replace(punctuationRegexp, "").replace(emojiRegexp, "");
+  const match = text.match(arabicRegexp) || "";
+  return match.length / text.length;
+};
+
+/**
+ * Is the `String` Arabic, based on
+ * a given `threshold` between `0` and `1`. Defaults to `0.79`.
+ * @param {string} text  Text to process
+ * @param {Float} [threshold=0.79]
+ * @returns {Boolean}
+ */
+
+export const isArabic = (text, threshold) => {
+  threshold = threshold || 0.79;
+  return howArabic(text) >= threshold;
+};
+
+/**
+ * Does the `String` have _any_ Arabic letters.
+ * @param {String} text  Text to process
+ * @returns {Boolean}
+ */
+
+export const hasArabic = (text) => !!arabicLettersRegexp.test(text);
+
+/**
+ * Remove the Arabic tashkil -diacritics- from the 'String'.
+ * @param {String} text  Text to process
+ * @returns {String}
+ */
+
+export const removeTashkel = (text) => text.replace(arabicDiacriticsRegexp, "");
diff --git a/src/bin/cli_server.js b/src/bin/cli_server.js
new file mode 100644
index 0000000..823ae00
--- /dev/null
+++ b/src/bin/cli_server.js
@@ -0,0 +1,16 @@
+/**
+ * Start the Express HTTP server
+ * @module bin/server
+ */
+
+import createServer from "app";
+
+const EXPRESS_PORT = parseInt(process.env.EXPRESS_PORT) || 5000;
+
+createServer().then(({ server }) => {
+  server.listen(EXPRESS_PORT, () => {
+    console.log(
+      "Shoebox listening on http://localhost:" + server.address().port
+    );
+  });
+});
diff --git a/src/migrations/20211017023049_create_shoe.js b/src/migrations/20211017023049_create_shoe.js
new file mode 100644
index 0000000..50f6add
--- /dev/null
+++ b/src/migrations/20211017023049_create_shoe.js
@@ -0,0 +1,18 @@
+/**
+ * Migration: Create shoe table.
+ */
+
+exports.up = function (knex) {
+  return knex.schema.createTable("shoe", function (table) {
+    table.increments("shoe_id").primary().unsigned();
+    table.string("shoelace", 16).nullable();
+    table.string("shoebox", 16).nullable();
+    table.string("type", 16).nullable();
+    table.jsonb("metadata").defaultTo({});
+    table.timestamp("created_at").defaultTo(knex.fn.now());
+  });
+};
+
+exports.down = function (knex) {
+  return knex.schema.dropTable("shoe");
+};