let's goHEADmain

11 files changed, 859 insertions, 0 deletions

diff --git a/src/db/auth.sql b/src/db/auth.sql
new file mode 100644
index 0000000..b3e4c50
--- /dev/null
+++ b/src/db/auth.sql
@@ -0,0 +1,120 @@
+-- https://github.com/jeremyevans/rodauth
+GRANT CREATE ON SCHEMA :schema TO :password_role;
+
+CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);
+
+CREATE TABLE account_statuses(
+    account_status_id smallint,
+    name text NOT NULL,
+
+    UNIQUE (name),
+    CONSTRAINT account_statuses_pk PRIMARY KEY (account_status_id)
+);
+INSERT INTO account_statuses (account_status_id, name) VALUES (1, 'Unverified');
+INSERT INTO account_statuses (account_status_id, name) VALUES (2, 'Verified');
+INSERT INTO account_statuses (account_status_id, name) VALUES (3, 'Closed');
+
+CREATE TABLE accounts(
+    account_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    status_id smallint NOT NULL DEFAULT 1,
+    -- email citext UNIQUE,
+    email text UNIQUE,
+    name text,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    UNIQUE (email),
+    CONSTRAINT accounts_pk PRIMARY KEY (account_id),
+    CONSTRAINT accounts_account_statuses_fk FOREIGN KEY (status_id) REFERENCES account_statuses(account_status_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+
+    CONSTRAINT accounts_email_ck CHECK (char_length(email) <= 255)
+);
+
+CREATE INDEX accounts_email_uniq ON accounts (email) WHERE status_id IN (1, 2);
+
+CREATE TABLE account_active_session_keys(
+    session_id text NOT NULL,
+    account_id bigint NOT NULL,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    last_use timestamptz NOT NULL DEFAULT now(),
+
+    CONSTRAINT account_active_session_keys_pk PRIMARY KEY (session_id, account_id),
+    CONSTRAINT account_active_session_keys_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+
+);
+
+CREATE OR REPLACE FUNCTION create_account(_email text, _password text)
+RETURNS int
+AS $$
+    INSERT INTO accounts (email) VALUES (lower(_email)) RETURNING account_id;
+$$
+LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION create_session(account_id bigint)
+RETURNS text
+AS $$
+    INSERT INTO account_active_session_keys (session_id, account_id) VALUES 
+    (pgsodium.crypto_auth_hmacsha256_keygen(), account_id) RETURNING session_id;
+$$
+LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION get_account_by_session_id(_session_id text)
+RETURNS SETOF accounts
+AS $$
+SELECT accounts.* FROM account_active_session_keys JOIN accounts USING(account_id) WHERE session_id = _session_id;
+$$
+LANGUAGE SQL;
+
+GRANT SELECT, INSERT, UPDATE, DELETE ON account_statuses TO :password_role;
+GRANT SELECT, INSERT, UPDATE, DELETE ON accounts TO :password_role;
+
+SET ROLE teamdraft_password;
+\c :database_name
+
+DROP TABLE IF EXISTS account_password_hashes;
+CREATE TABLE account_password_hashes(
+    account_id bigint NOT NULL,
+    password_hash int NOT NULL,
+
+    CONSTRAINT account_password_hashes_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE OR REPLACE FUNCTION get_salt(_account_id int8) RETURNS text AS $$
+DECLARE salt text;
+BEGIN
+SELECT
+CASE
+    WHEN password_hash ~ '^\\$argon2id'
+      THEN substring(password_hash from '\\$argon2id\\$v=\\d+\\$m=\\d+,t=\\d+,p=\\d+\\$.+\\$')
+    ELSE substr(password_hash, 0, 30)
+  END INTO salt
+END
+FROM account_password_hashes
+WHERE _account_id = account_id;
+RETURN salt;
+END;
+$$ LANGUAGE plpgsql
+SECURITY DEFINER
+SET search_path = :schema, public;
+
+CREATE OR REPLACE FUNCTION valid_password_hash(_account_id int8, hash text) RETURNS boolean AS $$
+DECLARE valid boolean;
+BEGIN
+SELECT password_hash = hash INTO valid
+FROM account_password_hashes
+WHERE _account_id = account_id;
+RETURN valid;
+END;
+$$ LANGUAGE plpgsql
+SECURITY DEFINER
+SET search_path = :schema, public;
+
+REVOKE ALL ON account_password_hashes FROM :schema;
+REVOKE ALL ON FUNCTION get_salt(int8) FROM :schema;
+REVOKE ALL ON FUNCTION valid_password_hash(int8, text) FROM :schema;
+GRANT INSERT, UPDATE, DELETE ON account_password_hashes TO :password_role;
+GRANT SELECT(account_id) ON account_password_hashes TO :password_role;
+GRANT EXECUTE ON FUNCTION get_salt(int8) TO :password_role;
+GRANT EXECUTE ON FUNCTION valid_password_hash(int8, text) TO :password_role;
+
+SET ROLE teamdraft;
diff --git a/src/db/database.sql b/src/db/database.sql
new file mode 100644
index 0000000..91ca755
--- /dev/null
+++ b/src/db/database.sql
@@ -0,0 +1,64 @@
+\set rw_name :database_name '_rw'
+\set ro_name :database_name '_ro'
+\set password_role :database_name '_password'
+
+DROP SCHEMA IF EXISTS :schema CASCADE;
+DROP DATABASE IF EXISTS :database_name;
+
+-- Uncomment for non-development environments
+-- DROP ROLE IF EXISTS :owner;
+-- CREATE ROLE :owner WITH LOGIN PASSWORD :'owner';
+
+DROP ROLE IF EXISTS :password_role;
+CREATE ROLE :password_role; 
+ALTER ROLE :password_role SET search_path TO :schema, pgsodium, public;
+
+GRANT ddl TO :owner;
+
+DROP ROLE IF EXISTS :rw_name;
+CREATE ROLE :rw_name WITH LOGIN PASSWORD :'rw_name';
+
+GRANT dml TO :rw_name;
+
+DROP ROLE IF EXISTS :ro_name;
+CREATE ROLE :ro_name WITH LOGIN PASSWORD :'ro_name';
+
+GRANT read_only TO :ro_name;
+
+ALTER ROLE :owner SET search_path TO :schema, public;
+ALTER ROLE :rw_name SET search_path TO :schema, pgsodium, public;
+ALTER ROLE :ro_name SET search_path TO :schema, public;
+
+CREATE DATABASE :database_name OWNER :owner;
+
+REVOKE CREATE ON SCHEMA public FROM PUBLIC;
+
+REVOKE ALL ON DATABASE :database_name FROM PUBLIC;
+
+GRANT CONNECT, TEMPORARY ON DATABASE :database_name TO ddl, dml, read_only;
+
+\c :database_name
+CREATE EXTENSION pgsodium;
+GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA pgsodium TO dml;
+
+SET ROLE :owner;
+
+CREATE SCHEMA :schema AUTHORIZATION :schema;
+
+GRANT USAGE, CREATE ON SCHEMA :schema TO ddl;
+
+GRANT USAGE ON SCHEMA :schema TO dml, read_only;
+
+REVOKE ALL ON DATABASE :database_name FROM PUBLIC;
+
+GRANT CONNECT, TEMPORARY ON DATABASE :database_name TO ddl, dml, read_only;
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dml;
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT SELECT ON TABLES TO read_only;
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT USAGE, SELECT ON SEQUENCES TO dml, read_only;
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT UPDATE ON SEQUENCES TO dml;
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT EXECUTE ON ROUTINES TO dml, read_only;
diff --git a/src/db/drafts.sql b/src/db/drafts.sql
new file mode 100644
index 0000000..4fff19f
--- /dev/null
+++ b/src/db/drafts.sql
@@ -0,0 +1,385 @@
+CREATE TABLE draft_statuses(
+    draft_status_id smallint NOT NULL,
+    name text NOT NULL,
+    UNIQUE (name),
+    CONSTRAINT draft_statuses_pk PRIMARY KEY (draft_status_id)
+);
+
+INSERT INTO draft_statuses(draft_status_id, name)
+    VALUES (1, 'Scheduled');
+
+INSERT INTO draft_statuses(draft_status_id, name)
+    VALUES (2, 'Running');
+
+INSERT INTO draft_statuses(draft_status_id, name)
+    VALUES (3, 'Complete');
+
+INSERT INTO draft_statuses(draft_status_id, name)
+    VALUES (4, 'Manual');
+
+CREATE TABLE drafts (
+    draft_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    season_id bigint NOT NULL,
+    draft_status_id smallint NOT NULL DEFAULT 1,
+    name text,
+    account_id bigint NOT NULL,
+    started_at timestamptz,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    CONSTRAINT drafts_pk PRIMARY KEY (draft_id),
+    CONSTRAINT drafts_seasons_fk FOREIGN KEY (season_id) REFERENCES seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT drafts_name_ck CHECK (char_length(name) <= 255),
+    CONSTRAINT drafts_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE TABLE draft_users (
+    draft_user_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    draft_id bigint NOT NULL,
+    name text NOT NULL,
+    account_id bigint,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    CONSTRAINT draft_users_pk PRIMARY KEY (draft_user_id),
+    CONSTRAINT draft_users_drafts_fk FOREIGN KEY (draft_id) REFERENCES drafts(draft_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT draft_users_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE OR REPLACE FUNCTION gen_invite_code()
+    RETURNS  varchar
+    LANGUAGE plpgsql
+AS $$
+DECLARE
+    base10 bigint;
+    base36 varchar := '';
+    intval bigint;
+    char0z char[]  := regexp_split_to_array('0123456789abcdefghijklmnopqrstuvwxyz', '');
+BEGIN
+    SELECT floor(random() * 78364164095 + 2176782336) INTO base10;
+    intval := abs(base10);
+    WHILE intval != 0 LOOP
+        base36 := char0z[(intval % 36)+1] || base36;
+        intval := intval / 36;
+    END LOOP;
+
+    IF base10 = 0 THEN base36 := '0'; END IF;
+    RETURN base36;
+END;
+$$;
+
+CREATE TABLE draft_user_invites (
+    draft_user_invite_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    draft_user_id bigint NOT NULL,
+    name text NOT NULL,
+    code text NOT NULL DEFAULT gen_invite_code(),
+    created_at timestamptz NOT NULL DEFAULT now(),
+
+    CONSTRAINT draft_user_invites_pk PRIMARY KEY (draft_user_id),
+    CONSTRAINT draft_user_invites_draft_users_fk FOREIGN KEY (draft_user_id) REFERENCES draft_users(draft_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT draft_user_invites_name_ck CHECK (char_length(name) <= 255)
+);
+
+CREATE TABLE picks (
+    pick_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
+    draft_id integer NOT NULL,
+    draft_user_id integer NOT NULL,
+    team_id int NOT NULL,
+    auto boolean NOT NULL DEFAULT FALSE,
+    picked_at timestamptz NOT NULL DEFAULT now(),
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    UNIQUE (draft_id, team_id),
+    CONSTRAINT picks_pk PRIMARY KEY (pick_id),
+    CONSTRAINT picks_drafts_fk FOREIGN KEY (draft_id) REFERENCES drafts(draft_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT picks_draft_users_fk FOREIGN KEY (draft_user_id) REFERENCES draft_users(draft_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT picks_teams_fk FOREIGN KEY (team_id) REFERENCES teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+-- TODO: support more than 2 players by persisting draft order and current_picker
+CREATE OR REPLACE FUNCTION current_picker_name(_draft_id bigint)
+    RETURNS text
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+BEGIN
+    RETURN (SELECT name FROM draft_users WHERE draft_user_id = current_picker_id(_draft_id));
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION current_picker_account_id(_draft_id bigint)
+    RETURNS bigint
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    pick_count smallint;
+    player_1 integer;
+    player_2 integer;
+BEGIN
+    SELECT count(1) FROM picks WHERE draft_id = _draft_id INTO pick_count;
+
+    SELECT account_id
+    FROM draft_users
+    WHERE draft_id = _draft_id
+    ORDER BY draft_user_id 
+    LIMIT 1 INTO player_1;
+
+    SELECT account_id
+    FROM draft_users
+    WHERE draft_id = _draft_id
+    ORDER BY draft_user_id DESC
+    LIMIT 1 INTO player_2;
+
+    IF pick_count % 2 = 0 THEN
+        RETURN player_1;
+    ELSE
+        RETURN player_2;
+    END IF;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION current_picker_id(_draft_id bigint)
+    RETURNS integer
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    pick_count smallint;
+    player_1 integer;
+    player_2 integer;
+BEGIN
+    SELECT count(1) FROM picks WHERE draft_id = _draft_id INTO pick_count;
+
+    SELECT draft_user_id
+    FROM draft_users
+    WHERE draft_id = _draft_id
+    ORDER BY draft_user_id 
+    LIMIT 1 INTO player_1;
+
+    SELECT draft_user_id
+    FROM draft_users
+    WHERE draft_id = _draft_id
+    ORDER BY draft_user_id DESC
+    LIMIT 1 INTO player_2;
+
+    IF pick_count % 2 = 0 THEN
+        RETURN player_1;
+    ELSE
+        RETURN player_2;
+    END IF;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION pick(_draft_id integer, _team_id integer)
+    RETURNS boolean
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+BEGIN
+    INSERT INTO picks (draft_id, league_user_id, team_id) VALUES (_draft_id, current_picker_id(_draft_id), _team_id);
+END;
+$$;
+
+-- create missing draft picks if any
+CREATE OR REPLACE PROCEDURE auto_draft(_draft_id integer)
+LANGUAGE plpgsql
+SECURITY INVOKER
+AS $$
+DECLARE
+    latest_pick_time timestamptz;
+    latest_pick picks%ROWTYPE;
+    pick_count integer;
+    next_auto_draft_team integer;
+    draft drafts%ROWTYPE;
+    draft_league_id integer;
+BEGIN
+    SELECT * FROM drafts WHERE draft_id = _draft_id INTO draft;
+    SELECT league_id FROM seasons WHERE season_id = draft.season_id INTO draft_league_id;
+
+    latest_pick_time = draft.started_at;
+    
+    IF now() >= latest_pick_time AND draft.draft_status_id = 1 THEN
+        UPDATE drafts SET draft_status_id = 2, modified_at = now() WHERE draft_id = _draft_id;
+    END IF;
+
+    LOOP
+        SELECT count(1) FROM picks WHERE draft_id = _draft_id INTO pick_count;
+
+        IF pick_count = (SELECT count(1) FROM teams JOIN league_divisions USING(league_division_id) WHERE league_id = draft_league_id) THEN
+            UPDATE drafts SET draft_status_id = 3, modified_at = now() WHERE draft_id = _draft_id;
+            RETURN;
+        END IF;
+
+        SELECT * FROM picks WHERE draft_id = _draft_id ORDER BY picked_at DESC LIMIT 1 INTO latest_pick;
+
+        IF latest_pick IS NOT NULL THEN
+            latest_pick_time = latest_pick.picked_at;
+        END IF;
+
+        IF (date_part('epoch', now())::integer - date_part('epoch', latest_pick_time)::integer) / 30 >= 1 THEN
+            SELECT
+                current_rankings.team_id
+            FROM
+                current_rankings
+            LEFT JOIN picks ON picks.team_id = current_rankings.team_id
+            WHERE
+                pick_id IS NULL
+                AND league_id = draft_league_id
+            ORDER BY
+                rank ASC
+            LIMIT 1 INTO next_auto_draft_team;
+
+            INSERT INTO picks(draft_id, draft_user_id, team_id, auto, picked_at)
+                VALUES (_draft_id, current_picker_id(_draft_id),
+                    (SELECT
+                        current_rankings.team_id
+                    FROM
+                        current_rankings
+                    LEFT JOIN picks ON picks.team_id = current_rankings.team_id
+                        AND picks.draft_id = _draft_id
+                    WHERE
+                        pick_id IS NULL
+                    ORDER BY
+                        rank ASC
+                    LIMIT 1),
+                TRUE,
+                latest_pick_time + interval '30 seconds');
+        ELSE
+            EXIT;
+        END IF;
+    END LOOP;
+END;
+$$;
+
+CREATE OR REPLACE VIEW draft_picks AS
+SELECT
+    picks.draft_id,
+    teams.team_id,
+    teams.name AS team,
+    accounts.name AS player
+FROM
+    picks
+    JOIN teams USING (team_id)
+    JOIN draft_users USING (draft_user_id)
+    JOIN accounts USING (account_id)
+ORDER BY
+    picks.created_at;
+
+CREATE OR REPLACE VIEW draft_info AS
+WITH latest_draft_pick AS (
+    SELECT
+        draft_id,
+        MAX(picked_at) AS picked_at
+    FROM
+        picks
+    GROUP BY
+        draft_id
+)
+SELECT
+    draft_id,
+    current_picker_account_id(draft_id) as current_player_id,
+    current_picker_name(draft_id) as current_player_name,
+    draft_status_id,
+    CASE WHEN draft_status_id IN (1, 3) THEN
+        0
+    ELSE
+        date_part('epoch',(coalesce(latest_draft_pick.picked_at, started_at) + interval '30 seconds') - now())::integer
+    END AS round_time_remaining,
+    CASE WHEN draft_status_id = 1 THEN
+        'The draft will start in ' || to_char(started_at - now(), 'MI:SS')
+    WHEN draft_status_id = 2 THEN
+        'It''s ' || current_picker_name(draft_id) || '''s turn!'
+    WHEN draft_status_id = 3 THEN
+        'The draft is over, good luck!'
+    END AS message,
+    draft_status_id = 2 AS can_pick
+FROM
+    drafts
+    LEFT JOIN latest_draft_pick USING (draft_id);
+
+CREATE OR REPLACE FUNCTION accept_invite(_code text)
+    RETURNS table (session_id text, invite_draft_id bigint)
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    new_account_id bigint;
+    invite_draft_user_id bigint;
+    invite_draft_id bigint;
+    new_session_id text;
+BEGIN
+    SELECT draft_user_id FROM draft_user_invites WHERE code = _code INTO invite_draft_user_id;
+    SELECT draft_id FROM draft_users WHERE draft_user_id = invite_draft_user_id INTO invite_draft_id;
+    
+    INSERT INTO accounts DEFAULT VALUES RETURNING account_id INTO new_account_id;
+
+    UPDATE draft_users SET account_id = new_account_id WHERE draft_user_id = invite_draft_user_id;
+    SELECT create_session(new_account_id) INTO new_session_id;
+
+    RETURN QUERY SELECT new_session_id, invite_draft_id;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION create_draft(league_id int, player_1 text, player_2 text, started_at timestamptz)
+    RETURNS table (session_id text, new_draft_id bigint)
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    new_account_id bigint;
+    new_draft_id bigint;
+    current_season_id int := current_season_id(league_id);
+    new_session_id text;
+    player_2_id bigint;
+BEGIN
+    INSERT INTO accounts DEFAULT VALUES RETURNING account_id INTO new_account_id;
+    SELECT create_session(new_account_id) INTO new_session_id;
+
+    IF started_at IS NULL THEN
+        INSERT INTO drafts (season_id, account_id, draft_status_id) VALUES (current_season_id, new_account_id, 4) RETURNING draft_id INTO new_draft_id;
+    ELSE
+        INSERT INTO drafts (season_id, account_id, started_at) VALUES (current_season_id, new_account_id, started_at) RETURNING draft_id INTO new_draft_id;
+    END IF;
+
+    INSERT INTO draft_users (draft_id, name, account_id) VALUES (new_draft_id, player_1, new_account_id);
+    INSERT INTO draft_users (draft_id, name) VALUES (new_draft_id, CASE WHEN player_2 = '' THEN 'Player 2' ELSE player_2 END) RETURNING draft_user_id INTO player_2_id;
+    INSERT INTO draft_user_invites (draft_user_id, name) VALUES (player_2_id, player_2);
+
+    RETURN QUERY SELECT new_session_id, new_draft_id;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION current_draft_picks(_draft_id int)
+    RETURNS TABLE (team_id      int,
+                   rank         smallint,
+                   name         text,
+                   picked       boolean,
+                   pick_user    text)
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    draft_season_id integer;
+BEGIN
+    SELECT season_id FROM drafts WHERE draft_id = _draft_id INTO draft_season_id;
+RETURN QUERY
+    WITH season_rankings AS (
+        SELECT
+            teams.team_id,
+            rankings.rank,
+            teams.name
+        FROM
+            teams
+            LEFT JOIN rankings ON rankings.team_id = teams.team_id
+                AND rankings.season_id = draft_season_id
+            ORDER BY
+                rank
+    )
+    SELECT r.*, draft_users.name IS NOT NULL AS picked, draft_users.name AS pick_user 
+    FROM season_rankings r
+    LEFT JOIN picks ON r.team_id = picks.team_id AND picks.draft_id = _draft_id
+    LEFT JOIN draft_users ON draft_users.draft_user_id = picks.draft_user_id;
+END;
+$$;
diff --git a/src/db/init.sql b/src/db/init.sql
new file mode 100644
index 0000000..8d2645f
--- /dev/null
+++ b/src/db/init.sql
@@ -0,0 +1,12 @@
+\set database_name teamdraft
+\set schema :database_name
+\set owner :database_name
+
+\i ./src/db/server.sql
+\i ./src/db/database.sql
+\i ./src/db/auth.sql
+\i ./src/db/leagues.sql
+\i ./src/db/teams.sql
+\i ./src/db/seasons.sql
+\i ./src/db/drafts.sql
+\i ./src/db/scores.sql
diff --git a/src/db/leagues.sql b/src/db/leagues.sql
new file mode 100644
index 0000000..9407de8
--- /dev/null
+++ b/src/db/leagues.sql
@@ -0,0 +1,29 @@
+CREATE TABLE leagues (
+    league_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
+    name text,
+
+    CONSTRAINT leagues_pk PRIMARY KEY (league_id),
+    CONSTRAINT leagues_name_ck CHECK (char_length(name) <= 64)
+);
+
+CREATE TABLE league_divisions (
+    league_division_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
+    league_id bigint NOT NULL,
+    name text,
+
+    CONSTRAINT league_divisions_pk PRIMARY KEY (league_division_id),
+    CONSTRAINT league_divisions_leagues_fk FOREIGN KEY (league_id) REFERENCES leagues(league_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT league_divisions_name_ck CHECK (char_length(name) <= 64)
+);
+
+INSERT INTO leagues (name) VALUES ('NFL') RETURNING league_id nfl_league_id \gset
+
+INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC East');
+INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC North');
+INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC South');
+INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC West');
+
+INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC East');
+INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC North');
+INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC South');
+INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC West');
diff --git a/src/db/schema.sql b/src/db/schema.sql
new file mode 100644
index 0000000..ad01727
--- /dev/null
+++ b/src/db/schema.sql
@@ -0,0 +1,5 @@
+\i ./src/db/auth.sql
+\i ./src/db/leagues.sql
+\i ./src/db/teams.sql
+\i ./src/db/seasons.sql
+\i ./src/db/drafts.sql
diff --git a/src/db/scores.sql b/src/db/scores.sql
new file mode 100644
index 0000000..8572a66
--- /dev/null
+++ b/src/db/scores.sql
@@ -0,0 +1,106 @@
+CREATE TABLE score_categories (
+    score_category_id smallint NOT NULL,
+    league_id int NOT NULL,
+    name text NOT NULL,
+    value int NOT NULL,
+
+    UNIQUE (league_id, name),
+    CONSTRAINT score_categories_pk PRIMARY KEY (score_category_id)
+);
+SELECT league_id AS nfl_league_id FROM leagues where name = 'NFL' LIMIT 1 \gset
+
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (1, :nfl_league_id, 'Win', 1);
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (2, :nfl_league_id, 'Playoffs', 5);
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (3, :nfl_league_id, 'Divisional', 5);
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (4, :nfl_league_id, 'Conference', 10);
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (5, :nfl_league_id, 'Superbowl', 10);
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (6, :nfl_league_id, 'Champion', 5);
+
+CREATE TABLE scores (
+    score_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    season_id bigint NOT NULL,
+    team_id integer NOT NULL,
+    game int NOT NULL,
+    score_category_id smallint NOT NULL DEFAULT 1,
+    scored_at timestamptz NOT NULL DEFAULT now(),
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    UNIQUE (season_id, team_id, game, score_category_id),
+    CONSTRAINT scores_pk PRIMARY KEY (score_id),
+    CONSTRAINT scores_seasons_fk FOREIGN KEY (season_id) REFERENCES seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT scores_teams_fk FOREIGN KEY (team_id) REFERENCES teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT scores_score_categories_fk FOREIGN KEY (score_category_id) REFERENCES score_categories(score_category_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE OR REPLACE FUNCTION record_score(team_id int, game int, season_id int, score_category_id int, scored_at timestamptz)
+    RETURNS int
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    new_score_id integer;
+BEGIN
+    INSERT INTO scores(season_id, team_id, game, score_category_id, scored_at)
+        VALUES (season_id, team_id, game, score_category_id, scored_at)
+    ON CONFLICT ON CONSTRAINT scores_season_id_team_id_game_score_category_id_key
+        DO UPDATE SET modified_at = NOW(), scored_at = EXCLUDED.scored_at
+    RETURNING
+        score_id INTO new_score_id;
+    RETURN new_score_id;
+END;
+$$;
+
+CREATE OR REPLACE VIEW season_scores AS
+WITH score_totals AS (
+    SELECT
+        scores.season_id,
+        scores.team_id,
+        sc.name,
+        sum(value) AS total
+    FROM
+        scores
+        JOIN score_categories sc USING(score_category_id)
+    GROUP BY
+        scores.season_id,
+        scores.team_id,
+        sc.name
+)
+SELECT
+    st.season_id,
+    teams.name,
+    array_agg(st.name || ': ' || st.total ORDER BY st.total DESC) AS scores
+FROM
+    teams
+    LEFT JOIN score_totals st USING(team_id)
+GROUP BY
+    st.season_id, teams.name;
+
+-- CREATE OR REPLACE VIEW draft_scores AS
+-- WITH season_scores AS (
+--     SELECT
+--         season_id,
+--         team_id,
+--         coalesce(SUM(value), 0)::smallint AS total
+--     FROM
+--         scores
+--     JOIN score_categories ON category_id = score_categories.score_category_id
+--     GROUP BY
+--         season_id,
+--         team_id
+-- )
+-- SELECT
+--     season_id,
+--     draft_id,
+--     team,
+--     player,
+--     coalesce(win, 0) AS win,
+--     playoffs,
+--     divisional,
+--     conference,
+--     superbowl,
+--     champion,
+--     coalesce(total, 0) AS total
+-- FROM
+--     draft_picks
+--     LEFT OUTER JOIN season_scores USING (team_id);
diff --git a/src/db/seasons.sql b/src/db/seasons.sql
new file mode 100644
index 0000000..ce492f0
--- /dev/null
+++ b/src/db/seasons.sql
@@ -0,0 +1,69 @@
+CREATE TABLE seasons (
+    season_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    league_id integer NOT NULL,
+    started_at timestamptz NOT NULL,
+    ended_at timestamptz NOT NULL,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    CONSTRAINT seasons_pk PRIMARY KEY (season_id),
+    CONSTRAINT seasons_leagues_fk FOREIGN KEY (league_id) REFERENCES leagues(league_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE TABLE rankings(
+    ranking_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
+    season_id integer NOT NULL,
+    team_id integer NOT NULL,
+    rank smallint NOT NULL,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    UNIQUE(season_id, team_id, rank),
+    CONSTRAINT rankings_pk PRIMARY KEY (ranking_id),
+    CONSTRAINT rankings_seasons_fk FOREIGN KEY (season_id) REFERENCES seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE OR REPLACE VIEW current_rankings AS
+SELECT teams.team_id, rankings.season_id, seasons.league_id, rank, name
+FROM teams
+JOIN rankings ON rankings.team_id = teams.team_id
+JOIN seasons ON rankings.season_id = seasons.season_id
+ORDER BY rank;
+
+CREATE OR REPLACE FUNCTION current_season_id(_league_id int)
+    RETURNS int
+    AS $$
+    SELECT
+        season_id
+    FROM
+        seasons
+    WHERE
+        started_at <= now()
+        AND ended_at > now()
+        AND league_id = _league_id
+    ORDER BY started_at DESC
+    LIMIT 1;
+$$
+LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION create_season(_league_id int, started_at timestamptz, ended_at timestamptz)
+    RETURNS int
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    new_season_id int;
+    team record;
+    rank smallint := 1;
+BEGIN
+    INSERT INTO seasons (league_id, started_at, ended_at) VALUES (_league_id, started_at, ended_at) RETURNING season_id INTO new_season_id;
+
+    FOR team in SELECT team_id FROM teams JOIN league_divisions USING (league_division_id) WHERE league_id = _league_id
+    LOOP
+        INSERT INTO rankings (season_id, team_id, rank) VALUES (new_season_id, team.team_id, rank);
+        rank := rank + 1;
+    END LOOP;
+
+    RETURN new_season_id;
+END;
+$$;
diff --git a/src/db/seed.sql b/src/db/seed.sql
new file mode 100644
index 0000000..1f4f9de
--- /dev/null
+++ b/src/db/seed.sql
@@ -0,0 +1,4 @@
+\c teamdraft
+SET ROLE teamdraft_rw;
+
+select create_season(1, now(), now() + interval '1 day');
diff --git a/src/db/server.sql b/src/db/server.sql
new file mode 100644
index 0000000..66410a9
--- /dev/null
+++ b/src/db/server.sql
@@ -0,0 +1,13 @@
+-- this should only be needed per postgresql instance
+
+-- can modify the database schema
+DROP ROLE IF EXISTS ddl;
+CREATE ROLE ddl WITH NOLOGIN;
+
+-- can modify database data
+DROP ROLE IF EXISTS dml;
+CREATE ROLE dml WITH NOLOGIN;
+
+-- can only read database data
+DROP ROLE IF EXISTS read_only;
+CREATE ROLE read_only WITH NOLOGIN;
diff --git a/src/db/teams.sql b/src/db/teams.sql
new file mode 100644
index 0000000..5f08434
--- /dev/null
+++ b/src/db/teams.sql
@@ -0,0 +1,52 @@
+CREATE TABLE teams(
+    team_id int NOT NULL GENERATED ALWAYS AS IDENTITY,
+    league_division_id int NOT NULL,
+    name text NOT NULL,
+    external_id text,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    CONSTRAINT teams_pk PRIMARY KEY (team_id),
+    CONSTRAINT teams_league_divisions_fk FOREIGN KEY (league_division_id) REFERENCES league_divisions(league_division_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT teams_name_ck CHECK (char_length(name) <= 255)
+);
+
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Buffalo', 1, '134918');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Miami', 1, '134919');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('New England', 1, '134920');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('NYJ', 1, '134921');
+
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Baltimore', 2, '134922');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Cincinnati', 2, '134923');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Cleveland', 2, '134924');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Pittsburgh', 2, '134925');
+
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Houston', 3, '134926');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Indianapolis', 3, '134927');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Jacksonville', 3, '134928');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Tennessee', 3, '134929');
+
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Denver', 4, '134930');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Kansas City', 4, '134931');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Las Vegas', 4, '134932');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('LAC', 4, '135908');
+
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Dallas', 5, '134934');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('NYG', 5, '134935');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Philadelphia', 5, '134936');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Washington', 5, '134937');
+
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Chicago', 6, '134938');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Detroit', 6, '134939');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Green Bay', 6, '134940');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Minnesota', 6, '134941');
+
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Atlanta', 7, '134942');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Carolina', 7, '134943');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('New Orleans', 7, '134944');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Tampa Bay', 7, '134945');
+
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Arizona', 8, '134946');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('LAR', 8, '135907');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('San Francisco', 8, '134948');
+INSERT INTO teams(name, league_division_id, external_id) VALUES ('Seattle', 8, '134949');