From 96fad1a4e78c7209e5a0f3496e8b59d591fbe500 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Sun, 15 Feb 2026 11:12:50 +0900 Subject: refactor(auth): replace JWT authentication with server-side sessions Migrate from stateless JWT tokens to server-side session management backed by PostgreSQL. Sessions are hashed with SHA-256 before storage, cleaned up periodically, and invalidated on logout. This removes the need for JWT_SECRET/COOKIE_SECRET environment variables and the golang-jwt dependency. Co-Authored-By: Claude Opus 4.6 --- backend/query.sql | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'backend/query.sql') diff --git a/backend/query.sql b/backend/query.sql index 0d84652..4297e42 100644 --- a/backend/query.sql +++ b/backend/query.sql @@ -276,3 +276,17 @@ SELECT * FROM testcase_results WHERE submission_id = $1 ORDER BY created_at; + +-- name: CreateSession :exec +INSERT INTO sessions (session_id, user_id, expires_at) VALUES ($1, $2, $3); + +-- name: GetUserBySession :one +SELECT users.* FROM sessions +JOIN users ON sessions.user_id = users.user_id +WHERE sessions.session_id = $1 AND sessions.expires_at > NOW(); + +-- name: DeleteSession :exec +DELETE FROM sessions WHERE session_id = $1; + +-- name: DeleteExpiredSessions :exec +DELETE FROM sessions WHERE expires_at < NOW(); -- cgit v1.3.1