I finished modelling the database. Took me some time to figure out all the necessary building blocks, had to scrap the diagrams at least three times and start it over until I reached something that was not confusing & complex. I used https://drawsql.app/ to draw the initial database diagram.

I had to first figure out the the main entities/objects of this project before getting into drawing diagrams. So I decided to write down in simple words, explaining what I wanted to accomplish. For example, at the very minimum a user should be able to create goals. This introduces the user and goal objects. A user is able to contribute towards a metric that is set for the goal by performing an action. So that introduced two more objects a metric and an action. Writing these out in simple words helped me model the tables & relationships.

The initial database model I ended up with is this:

I am sure certain things may change over time & some columns will be added and adjusted, but I want to start simple & then adjust/extend as I work on it. I will probably not be focusing on the roles, permissions & teams in the beginning. I want to get the core working which is goals & metrics. The goal_participants table may be confusing at first but its a polymorphic pivot table, a user can participate in a goal but also an entire role or team can also participate in a goal. In the future there may be other types of participants, so I decided not to make it strictly dependent on the user. The goal_metric_contributions table is basically list of actions that user takes which gets recorded via the api, it contributes the points towards the target value of the goal_metrics table.

Aside from creating the diagram, I also set up my local environment in order to create migrations, models & set up relationships. I like to work with docker so I decided to create a simple docker-compose.yml with just three containers to start with. Here is what my local docker-compose.yml looks like:

version: '3'

services:
  app:
    container_name: gamification_app
    build:
      context: ./
      dockerfile: Dockerfile
    environment:
      DOCKER_DB_HOST: db
      DOCKER_DB_DATABASE: gamification
      DOCKER_DB_USER: root
      DOCKER_DB_PASSWORD: root
    depends_on:
      - db
      - redis
    volumes:
      - ../:/var/www
      - ./scripts/init.sh:/usr/local/bin/init.sh
      - ./config/vhost.conf:/etc/apache2/sites-enabled/000-default.conf
      - ./config/scheduler.conf:/etc/supervisor/conf.d/scheduler.conf
      - ./config/horizon.conf:/etc/supervisor/conf.d/horizon.conf
    restart: always
    ports:
      - 80:8080
    entrypoint:
      - /usr/local/bin/init.sh

  db:
    container_name: gamification_db
    image: mysql:5.7
    volumes:
      - ./storage/mysql:/var/lib/mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
    ports:
      - 3306:3306

  redis:
    container_name: gamification_redis
    image: redis:latest
    restart: always
    command: redis-server --requirepass foobar

My Dockerfile is simply based on PHP 7.4 & the init.sh just creates the database if it doesnt exist, runs the migrations & runs few php artisan commands. Now I'm all set & ready to begin coding the core.