Procedure get_user_jwt_info #61

New issue

Closed

opened 2026-02-28 20:51:31 +00:00 by charlie · 1 comment

charlie commented

2026-02-28 20:51:31 +00:00

Owner

Create stored procedure `get_user_jwt_info(userid)`

Description

We need a new stored procedure to fetch the information required to build a JWT payload for a given user.

Requirements

Procedure name: get_user_jwt_info
Input parameter:
- userid (integer) — the ID of the user we want information for.
Output: A single row containing:
- userid — the user’s ID
- role — the user’s role name
- clubid — the ID of the club the user is coaching, or NULL if they are not a coach
Behavior:
- Determine the user’s role using existing tables (roles/users).
- If the role contains “coach”, fetch the clubid of the club they are coaching.
- If the user is not a coach, return NULL for clubid.
- Only return one row per user.
Usage: This procedure will be called by the backend to generate JWT payloads.

Acceptance criteria

Calling CALL get_user_jwt_info(42) returns a row like:

userid | role  | clubid
42     | coach | 7

Calling CALL get_user_jwt_info(43) for an admin returns:

userid | role       | clubid
43        | admin  | NULL

The procedure must never return multiple rows for a single user.

The procedure must handle users with no assigned club gracefully by returning NULL for clubid.

# Create stored procedure `get_user_jwt_info(userid)` ## Description We need a new stored procedure to fetch the information required to build a JWT payload for a given user. ## Requirements 1. **Procedure name:** `get_user_jwt_info` 2. **Input parameter:** - `userid` (integer) — the ID of the user we want information for. 3. **Output:** A single row containing: - `userid` — the user’s ID - `role` — the user’s role name - `clubid` — the ID of the club the user is coaching, or `NULL` if they are not a coach 4. **Behavior:** - Determine the user’s role using existing tables (roles/users). - If the role contains “coach”, fetch the `clubid` of the club they are coaching. - If the user is not a coach, return `NULL` for `clubid`. - Only return **one row** per user. 5. **Usage:** This procedure will be called by the backend to generate JWT payloads. ## Acceptance criteria - Calling `CALL get_user_jwt_info(42)` returns a row like: ```text userid | role | clubid 42 | coach | 7 ``` - Calling CALL get_user_jwt_info(43) for an admin returns: ```text userid | role | clubid 43 | admin | NULL ``` The procedure must never return multiple rows for a single user. The procedure must handle users with no assigned club gracefully by returning NULL for clubid.

charlie added the

enhancement

label

2026-02-28 20:51:31 +00:00