README.md (5545B)
1 # My Heats 2 Live heats, scoring and leaderboard for sport events. 3 4 This React application was build using [Vite](https://vitejs.dev) (`npm create 5 vite@latest . --template react`) and the following core componentes: 6 * [`react-router-dom`](https://reactrouter.com) for routing between the React 7 components 8 * [`@supabase/supabase-js`](https://supabase.com/docs/guides/getting-started/quickstarts/reactjs) 9 for connection to the Supabase database 10 * [`react-select`](https://react-select.com) for the selectbox widgets 11 12 ## Running the App 13 ```bash 14 # Install dependencies 15 npm install 16 17 # Export Supabase URI and database access key 18 export VITE_APP_SUPABASE_URL= 19 export VITE_APP_SUPABASE_KEY= 20 21 # Start app for development 22 npm run dev 23 ``` 24 25 ## Scoring and ranking logic 26 The scoring and ranking logic is defined in `utils.js` inside the `rankByHeat` 27 function. Following ranking options can be selected. Rank by: 28 * Start number: Rank by athlete start number 29 * Best heat: Rank by best heat (and by least worst if "best" is equally good) 30 * Worst heat: Ranks worst heats only (no further comparison by best if equally 31 bad) 32 * Total: Ranks by sum of all selected heats 33 34 ## Database schema 35 The Supabase schema is stored in the `schema` folder and can be created using plain psql. 36 37  38 39 To update the schema from the current database, use (example for table `startlist`): 40 ```bash 41 pg_dump -h db.youruniquedb.supabase.co -U postgres -t 'public.startlist' --schema-only > schema/startlist.sql 42 ``` 43 44 Additionally, following views and functions are required: 45 46 ```sql 47 -- Summarize all scores of a heat 48 create or replace view score_summary 49 with (security_invoker=on) 50 as 51 select a.id as athlete_id, s.heat as heat_id, SUM(s.score) as score_summary 52 from scores s 53 join athletes as a on a.id = s.athlete 54 group by a.id, s.heat 55 56 -- Create distinct set of athletes (startlist) from a set of heats 57 create or replace function distinct_startlist(heat_ids numeric[]) 58 returns table(id bigint, athlete bigint, nr bigint, firstname text, lastname text, 59 birthday date, school text) 60 language plpgsql 61 set search_path = '' 62 as $$ 63 begin 64 return query 65 select distinct on (a.id) s.id, a.id, a.nr, a.firstname, a.lastname, a.birthday, a.school 66 from public.startlist as s 67 join public.athletes as a on s.athlete = a.id 68 where s.heat = any (heat_ids); 69 end; 70 $$; 71 ``` 72 73 Add a trigger for connecting users with judges: 74 ```sql 75 -- https://supabase.com/docs/guides/auth/managing-user-data#using-triggers 76 -- inserts a row into public.judges 77 --drop trigger if exists on_auth_user_created on auth.users; 78 create or replace function public.handle_new_judge() 79 returns trigger 80 language plpgsql 81 security definer set search_path = '' 82 as $$ 83 begin 84 insert into public.judges (id) 85 values (new.id); 86 return new; 87 end; 88 $$; 89 90 -- trigger the function every time a user is created 91 create trigger on_auth_user_created 92 after insert on auth.users 93 for each row execute procedure public.handle_new_judge(); 94 ``` 95 96 Lastly, enable [PostgreSQL publication](https://www.postgresql.org/docs/current/logical-replication-publication.html) for the [Supabase realtime feature on Postgres changes](https://supabase.com/docs/guides/realtime/extensions/postgres-changes): 97 ```sql 98 begin; 99 100 -- remove the supabase_realtime publication 101 drop 102 publication if exists supabase_realtime; 103 104 -- re-create the supabase_realtime publication with no tables 105 create publication supabase_realtime; 106 107 commit; 108 109 -- add a table to the publication 110 alter 111 publication supabase_realtime add table scores; 112 ``` 113 114 This is required for the leaderboard to automatically update scores when they are created or changed by judges. 115 116 ## Authentication with magic links 117 Authentication of judges is performed using [Supabase Magic 118 Links](https://supabase.com/docs/guides/auth/auth-magic-link). 119 120 The basic code for the authentication flow was taken from the [official React 121 Getting Started 122 Tutorial](https://supabase.com/docs/guides/getting-started/tutorials/with-react). 123 124 Sign up process for new judges: 125 * The judge is required to have a valid email address 126 * Judges can be invied to the scoring backend by invite only (Click 127 ["Invite"](https://app.supabase.com/project/_/auth/users) in Supabase 128 backend) 129 * The UUID is referenced automatically in the `judges` table, row can be 130 amended with name of the judge 131 132 Sign in process: 133 * For sign in, the judge enters her email adress and receives a login link 134 * The login information is stored in the browser session until sign out 135 136 ### Supabase rate limits 137 * 4 emails per hour (e.g., sign-in magic links) 138 * https://supabase.com/docs/guides/auth/rate-limits 139 140 ## Bulk import/export 141 Use direct postgres database access: 142 ```bash 143 psql -h db.youruniquedb.supabase.co -U postgres 144 ``` 145 146 To import data from local csv: 147 ```sql 148 \copy public.scores from 'scores_rows.csv' DELIMITER ',' CSV HEADER; 149 ``` 150 151 To export data to local csv: 152 ```sql 153 \copy public.scores to 'scores_rows.csv' DELIMITER ',' CSV HEADER; 154 ``` 155 156 ## Development & contributions 157 * All source code is available in this repository. Contributions are always 158 welcome! Pull requests at https://pr.in0rdr.ch/repos/myheats 159 * A [Kanban 160 board](https://board.in0rdr.ch/public/board/c445667f1c999857f6149cad967fa44f196f2e9fef02069c5f5136e036dd) 161 documents plans, todos and decisions for further development of the project. 162 163 ## Community and support 164 For question and support visit 165 [#p0c/libera](https://web.libera.chat/gamja/#p0c) on IRC. 166 167 ## License 168 This work uses the MIT License. See [LICENSE](./LICENSE) for details.