1. Wellinks Avatar Wellinks
  2. Cinch Software
  3. Cinch Web

Wiki

Clone wiki

Cinch Web / 01 Data and Databases

View History

Databases and Data Sources

Databases

The main database is a Postgres DB which is a SQL (Structured Query Language) database, allowing database requests by both Ruby on Rails' ActiveRecord class syntax as well as more granular interaction bypassing ActiveRecord by the standard database request queries (ie MySQL).

Redis, a secondary, less permanent database utilizing a key/value file store is used for an asynchronous task-based queuing system. Think of this as short-term memory to the Postgres DB's long term memory. Redis is used for numerous small helper tasks -- Sidekiq processing of synced SensorEvent data sent from the devices into saved DailySummaries into the Postgres DB or even smaller pieces of cached JSON data into the saved filestore layer to speed up API communication and ease up on database queries.

##Database Models## Those familiar with Ruby on Rails can easily determine the data within and the relationships between the models of the application (Ruby on Rails is MVC - model / view / controller), but here's a breakdown of the most common models and the data fields associated with each model table:

Table name: patients
id :integer
firstname :string
lastname :string
email :string
birthday :date
created_at :datetime
updated_at :datetime
contact_info_id :integer
encrypted_password :string
reset_password_token :string
reset_password_sent_at :datetime
remember_created_at :datetime
sign_in_count :integer
current_sign_in_at :datetime
last_sign_in_at :datetime
current_sign_in_ip :inet
last_sign_in_ip :inet
username :string
Table name: doctors
id :integer
firstname :string
lastname :string
insurance_code :string
email :string
contact_info_id :integer
created_at :datetime
updated_at :datetime
encrypted_password :string
reset_password_token :string
reset_password_sent_at :datetime
remember_created_at :datetime
sign_in_count :integer
current_sign_in_at :datetime
last_sign_in_at :datetime
current_sign_in_ip :inet
last_sign_in_ip :inet

There are three user type models: doctor, patient and parent. Each are similar enough that they could be derived from the same base model (ie user) with slight changes and different roles assigned -- and at some point there could be a migration to this -- but after much thought I decided to keep the similar models completely separate so that there'd be no possible of bleed over or authentication issues given the HIPPA needs.

Table name: base_products
id :integer
name :string
description :text
created_at :datetime
updated_at :datetime
locations_mask :integer
feedbacks_mask :integer
style_id :integer
Table name: products
id :integer
base_product_id :integer
created_at :datetime
updated_at :datetime
patient_id :integer
feedback :integer
active :boolean
sync_date :date
passkey :string(6)
Table name: sensors
id :integer
serial :string
model :string
created_at :datetime
updated_at :datetime
active :boolean
product_id :integer
location :integer

A product is based upon a base_product template with locations_mask and feedbacks_mask bitmasking enums for what sensor locations and feedback are allowed for the cloned product of a base_product.

Sensors are created and assigned to a product with their location chosen from the possible locations available from a given base_product.

Table name: treatments
id :integer
start :datetime
end :datetime
summary :text
product_id :integer
created_at :datetime
updated_at :datetime
Table name: prescriptions
id :integer
hours :integer
summary :text
prescription_date :datetime
created_at :datetime
updated_at :datetime
treatment_id :integer
Table name: prescribed_tensions
id :integer
prescription_id :integer
created_at :datetime
updated_at :datetime
force :float
position :float
location :integer

A treatment is the 'envelope' that holds prescriptions assigned to a product. Each prescription holds a prescribed tension for every sensor assigned to a brace location within a product (for holding the force and position of each device). So, for example, if one product has 2 straps / sensors, then a prescription for that product will have two prescribed tensions, one for each sensor. If a product has one sensor, then a prescription will have one prescribed tension, etc.

When a prescription is changed during the course of a treatment, a new prescription with the required prescribed tensions will be created and added to the current treatment. So for example, if during the course of a treatment there is a change in the prescription (whether a change to the required hours or position of a brace, etc), then the current treatment will now have two prescriptions, with the newest prescription having today as the prescription_date.

Table name: sensor_events
id :integer
force :float
xaccel :float
yaccel :float
zaccel :float
battery :float
position :float
created_at :datetime
updated_at :datetime
deviceID :string
location :integer
sensor_id :integer
event_id :integer
raw_force :integer
raw_position_1 :integer
raw_position_2 :integer
gyro_x :integer
gyro_y :integer
gyro_z :integer
temperature :integer

Sensor Events are the records sent from the devices by connecting through bluetooth to the mobile application and sent via the API. Once received and saved to the postgres database, daily summaries are processed asynchronously via the Sidekiq threading and saved to the redis key/value store until completely finished.

Table name: daily_summaries
id :integer
product_id :integer
total_hours :float
valid_hours :float
date :date
created_at :datetime
updated_at :datetime
prescribed_hours :float

Daily Summaries are basically a log of wear time processed from the product's sensor events and sent back to the mobile app -- whether the user is a patient viewing his own wear time, or a doctor looking at one of his patient's log.

##JSON Serializers## There are a number of JSON serializers utilized on the backend to combine needed database models into a format best readable by the mobile applications (and at some point, 3rd party applications). These serializers include:

  • doctor
  • patient
  • product
  • treatment
  • prescription
  • prescribed_tension
  • sensor

Some of these serializers pull in other data (ie the doctor serializer adds a representation of possible base_products a doctor can choose from to create a product to assign to a new or existing patient. A doctor serializer also includes all the JSON representations of a patient under that doctor's care.

#####Examples of JSON serializers:#####

######Patient:##### 

{
  "role": "patient",
  "id": 26,
  "firstname": "",
  "lastname": "",
  "email": "33@test.com",
  "birthday": null,
  "username": "33",
  "treatment_start": "2016-05-01T16:06:00.000-04:00",
  "products": [
    {
      "id": 33,
      "base_product_id": 1,
      "active": true,
      "passkey": "654321",
      "feedback_states": [
        {
          "name": "light",
          "value": 1,
          "active": true
        },
        {
          "name": "vibration",
          "value": 2,
          "active": true
        }
      ],
      "active_feedback": {
        "light": 1,
        "vibration": 2
      },
      "treatment": {
        "id": 27,
        "product_id": 33,
        "start": "2016-05-01T16:06:00.000-04:00",
        "end": null,
        "summary": "",
        "prescriptions": [
          {
            "id": 25,
            "hours": 14,
            "summary": "",
            "prescription_date": "2016-05-01T16:06:00.000-04:00",
            "prescribed_tensions": [
              {
                "id": 45,
                "force": 25,
                "position": 25,
                "location": 1,
                "location_string": "upper"
              },
              {
                "id": 46,
                "force": 25,
                "position": 25,
                "location": 2,
                "location_string": "middle"
              },
              {
                "id": 47,
                "force": 25,
                "position": 25,
                "location": 3,
                "location_string": "lower"
              }
            ]
          }
        ],
        "latest_prescription": {
          "id": 25,
          "hours": 14,
          "summary": "",
          "prescription_date": "2016-05-01T16:06:00.000-04:00",
          "prescribed_tensions": [
            {
              "id": 45,
              "force": 25,
              "position": 25,
              "location": 1,
              "location_string": "upper"
            },
            {
              "id": 46,
              "force": 25,
              "position": 25,
              "location": 2,
              "location_string": "middle"
            },
            {
              "id": 47,
              "force": 25,
              "position": 25,
              "location": 3,
              "location_string": "lower"
            }
          ]
        }
      },
      "sensors": [
        {
          "id": 70,
          "serial": "33_01",
          "active": true,
          "location": 1,
          "location_string": "upper",
          "last_event_id": 715,
          "missing_event_ids": []
        },
        {
          "id": 71,
          "serial": "33_02",
          "active": true,
          "location": 2,
          "location_string": "middle",
          "last_event_id": 720,
          "missing_event_ids": []
        },
        {
          "id": 72,
          "serial": "33_03",
          "active": true,
          "location": 3,
          "location_string": "lower",
          "last_event_id": 720,
          "missing_event_ids": []
        }
      ]
    }
  ],
  "parents": []
}

######Doctor:######

{
  "role": "doctor",
  "id": 5,
  "firstname": "",
  "lastname": "",
  "email": "doctor@test.com",
  "product_styles": [
    {
      "id": 1,
      "name": "Rigo",
      "description": ""
    },
    {
      "id": 2,
      "name": "Boston",
      "description": ""
    }
  ],
  "base_products": [
    {
      "id": 4,
      "name": "calibration",
      "description": "",
      "style_id": 1,
      "sensor_locations": {
        "middle": 2,
        "lower": 3
      },
      "product_feedbacks": {}
    },
    ...[other base_products]...
  ],
  "patients": [
    {
      "role": "patient",
      "id": 25,
      "firstname": "",
      "lastname": "",
      "email": "30@test.com",
      "birthday": null,
      "username": "30",
      "treatment_start": "2016-04-28T13:15:00.000-04:00",
      "products": [
        {
          "id": 30,
          "base_product_id": 3,
          "active": true,
          "passkey": null,
          "feedback_states": [
            {
              "name": "light",
              "value": 1,
              "active": true
            },
            {
              "name": "vibration",
              "value": 2,
              "active": true
            }
          ],
          "active_feedback": {
            "light": 1,
            "vibration": 2
          },
          "treatment": {
            "id": 26,
            "product_id": 30,
            "start": "2016-04-28T13:15:00.000-04:00",
            "end": null,
            "summary": "",
            "prescriptions": [
              {
                "id": 24,
                "hours": 14,
                "summary": "",
                "prescription_date": "2016-04-29T13:16:00.000-04:00",
                "prescribed_tensions": [
                  {
                    "id": 44,
                    "force": 25,
                    "position": 40,
                    "location": 1,
                    "location_string": "upper"
                  }
                ]
              }
            ],
            "latest_prescription": {
              "id": 24,
              "hours": 14,
              "summary": "",
              "prescription_date": "2016-04-29T13:16:00.000-04:00",
              "prescribed_tensions": [
                {
                  "id": 44,
                  "force": 25,
                  "position": 40,
                  "location": 1,
                  "location_string": "upper"
                }
              ]
            }
          },
          "sensors": [
            {
              "id": 65,
              "serial": "30_01",
              "active": true,
              "location": 1,
              "location_string": "upper",
              "last_event_id": 7199,
              "missing_event_ids": []
            }
          ]
        }
      ],
      "parents": []
    },
    ...[other patients]...
  ]
}

Updated