Database Schemas

##Users

  id                  SERIAL     PRIMARY KEY  NOT NULL
  phoneNumber         CHAR(10)                NULL
  confirmCode         CHAR(6)                 NULL
  confirmCodeExpires  TIMESTAMP               NULL
  sponsorCode         TEXT                    NULL

##Schools

  id        SERIAL PRIMARY KEY             NOT NULL
  userId    INT    REFERENCES Users (id)   NOT NULL
  name      TEXT                           NOT NULL
  dueDate   DATE                           NOT NULL
  isActive  BOOLEAN                        NOT NULL DEFAULT TRUE

##Reminders (user's generated reminders)

  id           SERIAL  PRIMARY KEY              NOT NULL
  schoolId     INT     REFERENCES Schools (id)  NOT NULL
  userId       INT     REFERENCES Users (id)    NOT NULL
  timeframe    TEXT                             NOT NULL  *
  dueDate      DATE                             NOT NULL  **
  name         TEXT                             NOT NULL
  message      TEXT                             NOT NULL
  detail       TEXT                             NOT NULL
  lateMessage  TEXT                             NOT NULL
  lateDetail   TEXT                             NOT NULL

• • The display text for the timeframe (To do immediately, By 2/1/2017)

** - A hard date, needed for sorting.

##BaseReminders (the template for generating reminders)

  id           SERIAL  PRIMARY KEY                 NOT NULL
  name         TEXT                                NOT NULL
  categoryId   INT     REFERENCES categories (id)  NOT NULL
  message      TEXT                                NOT NULL
  detail       TEXT                                NOT NULL
  lateMessage  TEXT                                NULL
  lateDetail   TEXT                                NULL

##Categories

  id    SERIAL  PRIMARY KEY  NOT NULL
  name  TEXT                 NOT NULL

##Timeframes

  id       SERIAL  PRIMARY KEY  NOT NULL
  name     TEXT                 NOT NULL
  type     TEXT                 NOT NULL *
  formula  TEXT                 NULL **

• • type of timeframe, must be 'now', 'absolute', or 'relative'.

** - For type 'absolute,' must be a valid ISO-8601 date string. For type 'relative,', must be a positive integer or 0. For 'now', value can be null.

##BaseReminderTimeframe (pivot to facilitate many-to-many relationship of reminders and timeframes

  id               SERIAL  PRIMARY KEY                     NOT NULL
  timeframeId      INT     REFERENCES Timeframes (id)      NOT NULL
  baseReminderId   INT     REFERENCES BaseReminders (id)   NOT NULL

##ContentItems (table to store misc. content items, FAQ page, privacy policy, etc.)

  id       SERIAL  PRIMARY KEY  NOT NULL
  name     TEXT                 NOT NULL
  content  TEXT                 NULL

##Tests

  id       SERIAL  PRIMARY KEY  NOT NULL
  type     TEXT                 NOT NULL
  message  TEXT                 NOT NULL
  detail   TEXT                 NOT NULL

##TestDates

  id                 SERIAL  PRIMARY KEY            NOT NULL
  testId             INT     REFERENCES Tests (id)  NOT NULL
  registrationDate   DATE                           NOT NULL
  adminDate          DATE                           NOT NULL