implement basic sql schema for users and groups

[trriplejay]

in go, write a db init function that will create these tables if they don't exist, and update the tables if alterations are made. can start by using sqlite and move to postgres later.

required tables to start

1. users
2. groups
3. memberships (the join of users with groups)
4. admins (references the id from the "user" table. for now these can be added manually. later can be updated via admin-only pages)

Users

The most basic table we have. when a user logs in, they officially become a "user" on our service. Most fields are copied from the result of the GET call from steam API.

id: auto-increment number
email: string
steam_id: number
persona_name: string
profile_url: link to user profile on steam
profile_state: number
persona_state: number
avatar: string
avatar_medium: string
avatar_full: string
country: string
state: string
city: string
visibility_state: number
clan_id: number
steam_created: number (milliseconds)
created_at: date
updated_at: date

Groups

A group is a collection of users created by a single person (group admin). The group has a name and can own "sessions" (to be added later). Consider imposing limit on how many groups a single person can be admin for, to prevent abuse.

id: auto-increment number
name: string
status: string
created_at: date/time

Memberships

The table that joins users to groups. a membership defines a user's role in a particular group. This allows a single user to be a member of many groups.

id: auto-increment number
user_id: foreign key
group_id: foreign key
is_admin: boolean (group admin can invite/kick and start sessions)
created_at: date/time

Admins

anyone is this table should have "admin" page access... not sure what can be done there yet

id: auto incrementing number
user_id: foreign key