From 96fad1a4e78c7209e5a0f3496e8b59d591fbe500 Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
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 <noreply@anthropic.com>
---
 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

