commit 3cd768e8ed9a51d7c6eda4789e94a6873d219d45
parent 76915c567445254e11c4629c6850613abc32c6c0
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date: Fri, 20 Sep 2024 23:08:10 +0200
doc(readme): remove Supabase
Diffstat:
2 files changed, 16 insertions(+), 97 deletions(-)
diff --git a/README.md b/README.md
@@ -5,8 +5,8 @@ This React application was build using [Vite](https://vitejs.dev) (`npm create
vite@latest . --template react`) and the following core componentes:
* [`react-router-dom`](https://reactrouter.com) for routing between the React
components
-* [`@supabase/supabase-js`](https://supabase.com/docs/guides/getting-started/quickstarts/reactjs)
- for connection to the Supabase database
+* [`postgres`](https://github.com/porsager/postgres#realtime-subscribe) for
+ realtime subscription to leaderboard changes with PostgreSQL publication
* [`react-select`](https://react-select.com) for the selectbox widgets
## Running the App
@@ -14,9 +14,7 @@ vite@latest . --template react`) and the following core componentes:
# Install dependencies
npm install
-# Export Supabase URI and database access key
-export VITE_APP_SUPABASE_URL=
-export VITE_APP_SUPABASE_KEY=
+# Export all required environment variables, see example in .env
# Start authentication API backend
npm run api
@@ -35,115 +33,36 @@ function. Following ranking options can be selected. Rank by:
* Total: Ranks by sum of all selected heats
## Database schema
-The Supabase schema is stored in the `schema` folder and can be created using plain psql.
+The PostgreSQL database schema is stored in the `schema` folder and can be
+created using plain psql.
-
-
-To update the schema from the current database, use (example for table `startlist`):
+To update the schema from the current database, use (example for table
+`startlist`):
```bash
-pg_dump -h db.youruniquedb.supabase.co -U postgres -t 'public.startlist' --schema-only > schema/startlist.sql
-```
-
-Additionally, following views and functions are required:
-
-```sql
--- Summarize all scores of a heat
-create or replace view score_summary
- with (security_invoker=on)
- as
- select a.id as athlete_id, s.heat as heat_id, SUM(s.score) as score_summary
- from scores s
- join athletes as a on a.id = s.athlete
- group by a.id, s.heat;
-
--- Create distinct set of athletes (startlist) from a set of heats
-create or replace function distinct_startlist(heat_ids numeric[])
-returns table(id bigint, athlete bigint, nr bigint, firstname text, lastname text,
-birthday date, school text)
-language plpgsql
-set search_path = ''
-as $$
-begin
- return query
- select distinct on (a.id) s.id, a.id, a.nr, a.firstname, a.lastname, a.birthday, a.school
- from public.startlist as s
- join public.athletes as a on s.athlete = a.id
- where s.heat = any (heat_ids);
-end;
-$$;
+pg_dump -h 127.0.0.1 -U postgres -t 'public.startlist' --schema-only > schema/04-startlist.sql
```
-Add a trigger for connecting users with judges:
-```sql
--- https://supabase.com/docs/guides/auth/managing-user-data#using-triggers
--- inserts a row into public.judges
---drop trigger if exists on_auth_user_created on auth.users;
-create or replace function public.handle_new_judge()
-returns trigger
-language plpgsql
-security definer set search_path = ''
-as $$
-begin
- insert into public.judges (id)
- values (new.id);
- return new;
-end;
-$$;
-
--- trigger the function every time a user is created
-create trigger on_auth_user_created
- after insert on auth.users
- for each row execute procedure public.handle_new_judge();
-```
-
-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):
-```sql
-begin;
-
--- remove the supabase_realtime publication
-drop
- publication if exists supabase_realtime;
-
--- re-create the supabase_realtime publication with no tables
-create publication supabase_realtime;
-
-commit;
-
--- add a table to the publication
-alter
- publication supabase_realtime add table scores;
-```
-
-This is required for the leaderboard to automatically update scores when they are created or changed by judges.
+The views and functions from `./schema/99-init-db.sql` are required. Among
+others, this enables [PostgreSQL
+publication](https://www.postgresql.org/docs/current/logical-replication-publication.html)
+so the leaderboard can automatically update scores when they are created or
+changed by judges ("realtime functionality").
## Authentication with magic links
-Authentication of judges is performed using [Supabase Magic
-Links](https://supabase.com/docs/guides/auth/auth-magic-link).
-
-The basic code for the authentication flow was taken from the [official React
-Getting Started
-Tutorial](https://supabase.com/docs/guides/getting-started/tutorials/with-react).
+Authentication of judges is performed using Magic links.
Sign up process for new judges:
* The judge is required to have a valid email address
-* Judges can be invied to the scoring backend by invite only (Click
- ["Invite"](https://app.supabase.com/project/_/auth/users) in Supabase
-backend)
-* The UUID is referenced automatically in the `judges` table, row can be
- amended with name of the judge
+* Judges can be invied to the scoring backend by invite only (table `public.judges`)
Sign in process:
* For sign in, the judge enters her email adress and receives a login link
* The login information is stored in the browser session until sign out
-### Supabase rate limits
-* 4 emails per hour (e.g., sign-in magic links)
-* https://supabase.com/docs/guides/auth/rate-limits
-
## Bulk import/export
Use direct postgres database access:
```bash
-psql -h db.youruniquedb.supabase.co -U postgres
+psql -h 127.0.0.1 -U postgres
```
To import data from local csv:
diff --git a/assets/supabase-schema.png b/assets/supabase-schema.png
Binary files differ.
