- Clone this repository
npm install
- Create a development
postgres
database in your development environment using the names and credentials of your choice - Setup your environment variables according to the
.env.example
file (see below) - Run
npm run migrate:up
to bring your dev database up to speed - Run
npm run db:reset
to seed your database with some sample data - Run
npm run dev
to start the development environment
Create a database in your local postgres installation and set variables from .env.example
to a local copy of .env
.
Many variables are preloaded with default configurations, but you'll need to add some:
DATABASE_URL
should be your fully qualified postgres database url, such aspostgresql://username:password@domain:port/databasename
DISCORD_CLIENT_API_KEY
should be a high entroy randomly generated key to allow clients to connect to your development environment. Whether you are building the Discord bot or the Web Portal, this key should match across those platforms.WEBHOOK_DISCORD_BOT
is a receiving URL for webhooks, should you need to work with those. This API sends data to it when events happen to the data. It is designed for use with the Discord Bot and not the web portal.
To create a new migration, run npm run migrate:create my-migration-description -- --sql-file
.
This database makes use of denormalized data to reduce query times. The core tables (season, predictions, users, bets, and votes) are often aggregated together and compared (sometimes all five) which can make nested loops and conditional for counts that drive queries into unacceptable response times.
All denormalized data in this DB are managed using Postgres Triggers and Functions to ensure consistency. This can become difficult to keep track of.
This README aims to keep a running list of denormalized data and the functions which update them for database planning.
These data are triggered to change directly as a result of normalized data. Core data changes trigger them.
predictions.status
refresh_prediction_status()
is called to refresh one prediction's status on INSERT or UPDATE (retired_date, closed_date, judged_date) on predictions
predictions.season_id
refresh_prediction_seasons('prediction')
is called to refresh one prediction's season_id on INSERT or UPDATE (due_date, closed_date) on predictionsrefresh_prediction_seasons('all)
is called to refresh all predictions' season_ids on INSERT, DELETE or UPDATE (start, "end") on seasons
predictions.season_applicable
refresh_prediction_season_applicable()
is called to refresh one prediction's season_applicable ON UPDATE (season_id) on predictions
bets.wager
refresh_wager('prediction')
is called to update all bets for single prediction on UPDATE (due_date, closed_date) topredictions
tablerefresh_wager('bet')
is called to update a single bet on INSERT tobets
table
bets.valid
refresh_valid('prediction')
is called to update all bets for single prediction on UPDATE (closed_date) topredictions
tablerefresh_valid('bet')
is called to update single bet on UPDATE (date) tobets
table
seasons.wager_cap
refresh_wager_cap
is called to update the wager cap on a single season on INSERT or UPDATE (start, 'end') on seasons;
These data are triggered to change as a result of Level 1 De-normalized data. Core data changes may trigger them as well.
predicitions.endorse_ratio
andpredictions.undorse_ratio
refresh_prediction_ratios_from_bet
is called to refresh both of these ratios on UPDATE (wager, valid, endorsed) of betsrefresh_prediction_ratios_from_season
is called to refresh both of these ratios on UPDATE (payout_formula) of seasonsrefresh_prediction_ratios_from_prediction
is called to refresh both of these ratios on UPDATE (season_id) of predictions
These data are triggered to change as a result of LEvel 2 De-normalized data. Core data changes may trigger them as well.
bets.payout
andbets.season_payout
refresh_payouts_from_season
is called to refresh both of these payouts on UPDATE (payout_formula, wager_cap) of seasonsrefresh_payouts_from_prediction
is called to refresh both of these payouts on UPDATE (status, endorse_ratio, undorse_ratio) of predictions!