commit ed78f3a09cbbeb427f0fba0df9d3ba28717529ba
parent 1d90aa2aeda491f5933d2f53c59cf5d8e1c2af46
Author: Andreas Gruhler <agruhl@gmx.ch>
Date: Mon, 9 Mar 2026 23:09:01 +0100
feat(137): authenticate startlist & score endpoints
Diffstat:
9 files changed, 193 insertions(+), 69 deletions(-)
diff --git a/CHANGELOG.md b/CHANGELOG.md
@@ -1,8 +1,9 @@
## [0.9 Unreleased]
### Added
-* `private` heats only visible to judges
- - DB migration
- [`schema/migrations/01-heats.sql`](schema/migrations/01-heats.sql)
+* `private` heats only visible to judges. DB migrations:
+ - [`schema/migrations/01-heats.sql`](schema/migrations/01-heats.sql)
+ - [`schema/migrations/02-distinct-startlist-private-heats.sql`](schema/migrations/02-distinct-startlist-private-heats.sql)
+ - [`schema/migrations/03-score-summary-function.sql`](schema/migrations/03-score-summary-function.sql)
### Changed
* PostgreSQL 17 in docker-compose dev setup
diff --git a/docs/POSTGRESQL_FUNCTIONS.md b/docs/POSTGRESQL_FUNCTIONS.md
@@ -0,0 +1,39 @@
+# Notes on PostgreSQL Functions
+
+## Reduction of conditional WHERE statement
+
+The WHERE statement for the function `distinct_startlist` was reduced as follows.
+* Source: https://stackoverflow.com/a/73452497
+
+Step 1, function with conditional where (CASE):
+```sql
+where (case when publicOnly = true
+ then -- return public heats only for unauthenticated users
+ s.heat = any(heat_ids) and
+ h.private = false
+ else -- return all heats for authenticated users
+ s.heat = any(heat_ids)
+end);
+```
+
+Step 2:
+```sql
+where s.heat = any(heat_ids) and
+(case when publicOnly = true then h.private = false else true end);
+```
+
+Step 3:
+```sql
+where s.heat = any(heat_ids) and
+(publicOnly = false or h.private = false);
+```
+
+Truth table:
+
+| "Private" selected? | Description | Input variables | Result |
+|---------------------|---------------|------------------------------------|--------------|
+| yes | private heat | publicOnly=true & h.private=true | not selected |
+| no | public heat | publicOnly=true & h.private=false | selected |
+| yes | private heat | publicOnly=false & h.private=true | selected |
+| no | public heat | publicOnly=false & h.private=false | selected |
+```
diff --git a/schema/99-init-db.sql b/schema/99-init-db.sql
@@ -1,14 +1,22 @@
-- 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
+create or replace function score_summary(publicOnly boolean)
+returns table(athlete_id bigint, heat_id bigint, score_summary double precision)
+language plpgsql
+set search_path = ''
+as $$
+begin
+ return query
+ select a.id, s.heat, SUM(s.score)
+ from public.scores s
+ join public.athletes as a on a.id = s.athlete
+ join public.heats as h on s.heat = h.id
+ where (publicOnly = false or h.private = false)
group by a.id, s.heat;
+end;
+$$;
-- Create distinct set of athletes (startlist) from a set of heats
-create or replace function distinct_startlist(heat_ids numeric[])
+create or replace function distinct_startlist(heat_ids numeric[], publicOnly boolean)
returns table(id bigint, athlete bigint, nr bigint, firstname text, lastname text,
birthday date, school text)
language plpgsql
@@ -19,7 +27,9 @@ begin
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);
+ join public.heats as h on s.heat = h.id
+ where s.heat = any(heat_ids) and
+ (publicOnly = false or h.private = false);
end;
$$;
diff --git a/schema/migrations/02-distinct-startlist-private-heats.sql b/schema/migrations/02-distinct-startlist-private-heats.sql
@@ -0,0 +1,20 @@
+-- remove the function with the old signature
+drop function if exists distinct_startlist(heat_ids numeric[]);
+
+-- add function that returns startlists considering the heat visibility
+create or replace function distinct_startlist(heat_ids numeric[], publicOnly boolean)
+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
+ join public.heats as h on s.heat = h.id
+ where s.heat = any(heat_ids) and
+ (publicOnly = false or h.private = false);
+end;
+$$;
+\ No newline at end of file
diff --git a/schema/migrations/03-score-summary-function.sql b/schema/migrations/03-score-summary-function.sql
@@ -0,0 +1,19 @@
+-- remove the old view
+drop view if exists score_summary;
+
+-- create new function with input parameter "publicOnly"
+create or replace function score_summary(publicOnly boolean)
+returns table(athlete_id bigint, heat_id bigint, score_summary double precision)
+language plpgsql
+set search_path = ''
+as $$
+begin
+ return query
+ select a.id, s.heat, SUM(s.score)
+ from public.scores s
+ join public.athletes as a on a.id = s.athlete
+ join public.heats as h on s.heat = h.id
+ where (publicOnly = false or h.private = false)
+ group by a.id, s.heat;
+end;
+$$;
+\ No newline at end of file
diff --git a/src/api/db.cjs b/src/api/db.cjs
@@ -306,7 +306,7 @@ async function toggleHeatVisibility(heatId) {
}
}
-async function distinctStartlist(heatIds) {
+async function distinctStartlist(heatIds, publicOnly) {
try {
const startlist = await sql`
select
@@ -317,7 +317,7 @@ async function distinctStartlist(heatIds) {
lastname,
birthday,
school
- from distinct_startlist(${heatIds}) as athlete
+ from distinct_startlist(${heatIds}, ${publicOnly}) as athlete
`
return startlist
} catch (error) {
@@ -353,16 +353,32 @@ async function startlistWithAthletes(heatId) {
}
}
-async function scoresForHeatAndAthlete(heat, athlete) {
+async function scoresForHeatAndAthlete(heat, athlete, publicOnly) {
try {
- const score = await sql`
- select
- id,
- athlete,
- judge,
- score
- from public.scores where heat = ${heat} and athlete = ${athlete}
- `
+ let score = undefined
+ if (publicOnly) {
+ score = await sql`
+ select
+ s.id,
+ s.athlete,
+ s.judge,
+ s.score
+ from public.scores as s
+ join public.heats as h
+ on s.heat = h.id
+ where s.heat = ${heat} and s.athlete = ${athlete} and h.private = false;
+ `
+ } else {
+ // return all heats (private & public)
+ score = await sql`
+ select
+ id,
+ athlete,
+ judge,
+ score
+ from public.scores where heat = ${heat} and athlete = ${athlete};
+ `
+ }
return score
} catch (error) {
console.error('Error occurred in scoresForHeatAndAthlete:', error);
@@ -370,11 +386,11 @@ async function scoresForHeatAndAthlete(heat, athlete) {
}
}
-async function scoreSummaryForHeatAndAthlete(heat, athlete) {
+async function scoreSummaryForHeatAndAthlete(heat, athlete, publicOnly) {
try {
const summary = await sql`
select score_summary
- from score_summary where heat_id = ${heat} and athlete_id = ${athlete}
+ from score_summary(${publicOnly}) where heat_id = ${heat} and athlete_id = ${athlete}
`
return summary
} catch (error) {
diff --git a/src/api/server.cjs b/src/api/server.cjs
@@ -53,34 +53,34 @@ const transport = nodemailer.createTransport({
// Define API paths and allowed request methods
const paths = [
- '/v1/healthz',
- '/v1/auth/verify',
- '/v1/echo',
- '/v1/auth/requestMagicLink',
- '/v1/auth/invalidateToken', // not implemented
- '/v1/leaderboard/allHeats', // partly authenticated
- '/v1/leaderboard/allJudges', // 🔒 authenticated
- '/v1/leaderboard/allAthletes', // 🔒 authenticated
- '/v1/leaderboard/newHeat', // 🔒 authenticated
- '/v1/leaderboard/getHeat', // 🔒 authenticated
- '/v1/leaderboard/removeHeat', // 🔒 authenticated
- '/v1/leaderboard/toggleHeatVisibility', // 🔒 authenticated
- '/v1/leaderboard/distinctStartlist',
- '/v1/leaderboard/startlistWithAthletes', // 🔒 authenticated
- '/v1/leaderboard/scoresForHeatAndAthlete',
- '/v1/leaderboard/scoreSummaryForHeatAndAthlete',
- '/v1/leaderboard/getScore', // 🔒 authenticated
- '/v1/leaderboard/setScore', // 🔒 authenticated
- '/v1/leaderboard/addAthleteToHeat', // 🔒 authenticated
- '/v1/leaderboard/removeAthleteFromHeat', // 🔒 authenticated
- '/v1/leaderboard/addAthlete', // 🔒 authenticated
- '/v1/leaderboard/removeAthlete', // 🔒 authenticated
- '/v1/leaderboard/addJudge', // 🔒 authenticated
- '/v1/leaderboard/removeJudge', // 🔒 authenticated
- '/v1/leaderboard/allSettings',
- '/v1/leaderboard/getSetting', // 🔒 authenticated
- '/v1/leaderboard/updateSetting', // 🔒 authenticated
- '/v1/leaderboard/removeSetting', // 🔒 authenticated
+ '/v1/healthz', // not authenticated
+ '/v1/auth/verify', // not authenticated
+ '/v1/echo', // not authenticated
+ '/v1/auth/requestMagicLink', // not authenticated
+ '/v1/auth/invalidateToken', // not implemented
+ '/v1/leaderboard/allHeats', // 🔓 partly authenticated
+ '/v1/leaderboard/allJudges', // 🔐 fully authenticated
+ '/v1/leaderboard/allAthletes', // 🔐 fully authenticated
+ '/v1/leaderboard/newHeat', // 🔐 fully authenticated
+ '/v1/leaderboard/getHeat', // 🔐 fully authenticated
+ '/v1/leaderboard/removeHeat', // 🔐 fully authenticated
+ '/v1/leaderboard/toggleHeatVisibility', // 🔐 fully authenticated
+ '/v1/leaderboard/distinctStartlist', // 🔓 partly authenticated
+ '/v1/leaderboard/startlistWithAthletes', // 🔐 fully authenticated
+ '/v1/leaderboard/scoresForHeatAndAthlete', // 🔓 partly authenticated
+ '/v1/leaderboard/scoreSummaryForHeatAndAthlete', // 🔓 partly authenticated
+ '/v1/leaderboard/getScore', // 🔐 fully authenticated
+ '/v1/leaderboard/setScore', // 🔐 fully authenticated
+ '/v1/leaderboard/addAthleteToHeat', // 🔐 fully authenticated
+ '/v1/leaderboard/removeAthleteFromHeat', // 🔐 fully authenticated
+ '/v1/leaderboard/addAthlete', // 🔐 fully authenticated
+ '/v1/leaderboard/removeAthlete', // 🔐 fully authenticated
+ '/v1/leaderboard/addJudge', // 🔐 fully authenticated
+ '/v1/leaderboard/removeJudge', // 🔐 fully authenticated
+ '/v1/leaderboard/allSettings', // not authenticated
+ '/v1/leaderboard/getSetting', // 🔐 fully authenticated
+ '/v1/leaderboard/updateSetting', // 🔐 fully authenticated
+ '/v1/leaderboard/removeSetting', // 🔐 fully authenticated
]
console.log("Backend API:", api_uri);
@@ -324,7 +324,9 @@ server.on('request', async (req, res) => {
input = JSON.parse(b);
console.log(' distinctStartlist request with headIds:', input.heat_ids);
- const startlist = await db.distinctStartlist(input.heat_ids);
+ const user = await verifyToken(req, token)
+ // return public heats only for unauthenticated users
+ const startlist = await db.distinctStartlist(input.heat_ids, user === false ? true : false);
if (startlist.length < 1) {
noContent(res);
@@ -383,9 +385,12 @@ server.on('request', async (req, res) => {
console.log(' scoresForHeatAndAthlete request with heat and athlete:',
input.heat, input.athlete);
+ const user = await verifyToken(req, token)
+ // for unauthenticated users only return public heat scores
const scores = await db.scoresForHeatAndAthlete(
input.heat,
- input.athlete
+ input.athlete,
+ user === false ? true : false
)
if (scores.length < 1) {
@@ -414,9 +419,12 @@ server.on('request', async (req, res) => {
console.log(' scoreSummaryForHeatAndAthlete request with heat and athlete:',
input.heat, input.athlete);
+ const user = await verifyToken(req, token)
+ // for unauthenticated users only return public heat summary
const summary = await db.scoreSummaryForHeatAndAthlete(
input.heat,
- input.athlete
+ input.athlete,
+ user === false ? true : false
)
if (summary.length < 1) {
diff --git a/src/frontend/Leaderboard.jsx b/src/frontend/Leaderboard.jsx
@@ -44,10 +44,13 @@ export async function addAthleteToHeat(athlete, heat, session) {
return data
}
-export async function getStartlistForHeats(heatIds) {
+export async function getStartlistForHeats(heatIds, session) {
const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/distinctStartlist`, {
method: 'POST',
- headers: {'Content-Type': 'application/json'},
+ headers: {
+ 'Content-Type': 'application/json',
+ 'Authorization': `Bearer ${session.auth ? session.auth.token : ""}`,
+ },
body: JSON.stringify({
"heat_ids": heatIds,
}),
@@ -64,13 +67,16 @@ export async function getStartlistForHeats(heatIds) {
}
}
-async function getScoresForHeatAndAthlete(heatId, athleteId) {
+async function getScoresForHeatAndAthlete(heatId, athleteId, session) {
const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/scoresForHeatAndAthlete`, {
method: 'POST',
- headers: {'Content-Type': 'application/json'},
+ headers: {
+ 'Content-Type': 'application/json',
+ 'Authorization': `Bearer ${session.auth ? session.auth.token : ""}`,
+ },
body: JSON.stringify({
"heat": heatId,
- "athlete": athleteId,
+ "athlete": athleteId
}),
})
if (res.status !== 204) {
@@ -82,10 +88,13 @@ async function getScoresForHeatAndAthlete(heatId, athleteId) {
}
}
-async function getScoreSummaryForHeatAndAthlete(heatId, athleteId) {
+async function getScoreSummaryForHeatAndAthlete(heatId, athleteId, session) {
const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/scoreSummaryForHeatAndAthlete`, {
method: 'POST',
- headers: {'Content-Type': 'application/json'},
+ headers: {
+ 'Content-Type': 'application/json',
+ 'Authorization': `Bearer ${session.auth ? session.auth.token : ""}`,
+ },
body: JSON.stringify({
"heat": heatId,
"athlete": athleteId,
@@ -100,9 +109,9 @@ async function getScoreSummaryForHeatAndAthlete(heatId, athleteId) {
}
}
-async function getScoreSummary(heatIds) {
+async function getScoreSummary(heatIds, session) {
const startListWithScores = []
- const startlist = await getStartlistForHeats(heatIds)
+ const startlist = await getStartlistForHeats(heatIds, session)
for (const i of startlist) {
i.heats = []
@@ -110,8 +119,8 @@ async function getScoreSummary(heatIds) {
for (const h of heatIds) {
try {
// this is an array, because the athlete can be scored by multiple judges
- const scores = await getScoresForHeatAndAthlete(h, i.athlete)
- const summary = await getScoreSummaryForHeatAndAthlete(h, i.athlete)
+ const scores = await getScoresForHeatAndAthlete(h, i.athlete, session)
+ const summary = await getScoreSummaryForHeatAndAthlete(h, i.athlete, session)
if (scores && summary) {
// add heat results of athlete to startlist entry
i.heats.push({
@@ -336,7 +345,7 @@ function Leaderboard({session}) {
setLoading(true)
// reload entire leaderboard when heat selection is changed
- const scoreSummary = await getScoreSummary(heatSelection.map(h => h.value))
+ const scoreSummary = await getScoreSummary(heatSelection.map(h => h.value), session)
setLeaderboard(scoreSummary)
setLoading(false)
})();
@@ -346,7 +355,7 @@ function Leaderboard({session}) {
(async() => {
socket.onmessage = async function(event) {
// todo: reload only required scores
- const scoreSummary = await getScoreSummary(heatSelection.map(h => h.value))
+ const scoreSummary = await getScoreSummary(heatSelection.map(h => h.value), session)
setLeaderboard(scoreSummary)
}
setLoading(false)
diff --git a/src/frontend/Score.jsx b/src/frontend/Score.jsx
@@ -80,7 +80,7 @@ function ScoringForm({session}) {
let startlist = undefined
try {
- startlist = await getStartlistForHeats([heatSelection.value])
+ startlist = await getStartlistForHeats([heatSelection.value], session)
} catch (error) {
console.error(error)
setLoading(false)
