Investigate using a NoSQL backend

[MacND]

Description

Currently Voluspa uses MariaDB as its backend data store. While this has its advantages (well developed drivers, I know standard SQL pretty well, performance is great), I frequently find myself pulling data out of the database in one format and translating it to another to use (comma separated IDs in fireteams get .split() in the codebase).

Utilising a NoSQL backend would mean consistency between the actual data and the variables used in the code, and would make it much easier to provide an API. We don't particularly need JOINs to get the data we want, as we'd move to new data structure;

• Fireteams would become a property of an event
• Notification preferences would become much more manageable as objects
• Activities would be much easier to provision and edit

User Story

I think once we have OAuth registration built and working in at least a basic form, this is something worth looking into. Voluspa is unlikely to reap any of the traditional benefits of NoSQL (I don't forsee large volumes of data being created), it just appears to solve a lot of my day-to-day gripes with MariaDB. I think it also makes the project itself easier to maintain by keeping data formats consistent.

I've never used a NoSQL database in a project like this so would love outside opinions.

[MacND]

Have been doing some research on this the last few days and it looks like Postgres would actually be the solution for what I want, plus with the architecture move to AWS I can use Postgres on RDS.

[MacND]

While moving to Postgres, the current auto-increment IDs should be changed to randomly generated UUIDs. We'll need 4 tables; users, events, activities, and notifications.

Events - UUID, and a JSON blob:

{ 
  "activity_id":"smallint",
  "created_time":"datetime",
  "start_time":"datetime",
  "end_time":"datetime",
  "fireteam": [ 
    { 
      "discord_id":"string",
      "admin":"boolean",
      "streaming":"boolean",
      "reserve":"boolean",
      "joined":"datetime"
    }
  ],
  "raid_report":[ 
    "string"
  ],
  "note":"string",
  "private":"boolean"
}

Users - Standard table:

{
  "discord_id": "string",
  "timezone": "string",
  "stream": {
    "platform": "string",
    "username": "string"
  },
  "discord_access_key": "",
  "discord_refresh_token": ""
}

I do Not like storing the access key and refresh token like this but not sure of a better way really.

Still need to work out how notifications will be structured.

Activities table will basically be what it is now, been thinking about moving this to a config file instead but we'll see.