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>

1 files changed, 54 insertions, 0 deletions

diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go
index 6ec3aa4..1d6d11c 100644
--- a/backend/db/query.sql.go
+++ b/backend/db/query.sql.go
@@ -103,6 +103,21 @@ func (q *Queries) CreateProblem(ctx context.Context, arg CreateProblemParams) (i
 	return problem_id, err
 }
 
+const createSession = `-- name: CreateSession :exec
+INSERT INTO sessions (session_id, user_id, expires_at) VALUES ($1, $2, $3)
+`
+
+type CreateSessionParams struct {
+	SessionID string
+	UserID    int32
+	ExpiresAt pgtype.Timestamp
+}
+
+func (q *Queries) CreateSession(ctx context.Context, arg CreateSessionParams) error {
+	_, err := q.db.Exec(ctx, createSession, arg.SessionID, arg.UserID, arg.ExpiresAt)
+	return err
+}
+
 const createSubmission = `-- name: CreateSubmission :one
 INSERT INTO submissions (game_id, user_id, code, code_size, status)
 VALUES ($1, $2, $3, $4, 'running')
@@ -199,6 +214,24 @@ func (q *Queries) CreateUserAuth(ctx context.Context, arg CreateUserAuthParams)
 	return err
 }
 
+const deleteExpiredSessions = `-- name: DeleteExpiredSessions :exec
+DELETE FROM sessions WHERE expires_at < NOW()
+`
+
+func (q *Queries) DeleteExpiredSessions(ctx context.Context) error {
+	_, err := q.db.Exec(ctx, deleteExpiredSessions)
+	return err
+}
+
+const deleteSession = `-- name: DeleteSession :exec
+DELETE FROM sessions WHERE session_id = $1
+`
+
+func (q *Queries) DeleteSession(ctx context.Context, sessionID string) error {
+	_, err := q.db.Exec(ctx, deleteSession, sessionID)
+	return err
+}
+
 const deleteTestcase = `-- name: DeleteTestcase :exec
 DELETE FROM testcases
 WHERE testcase_id = $1
@@ -671,6 +704,27 @@ func (q *Queries) GetUserByID(ctx context.Context, userID int32) (User, error) {
 	return i, err
 }
 
+const getUserBySession = `-- name: GetUserBySession :one
+SELECT users.user_id, users.username, users.display_name, users.icon_path, users.is_admin, users.label, users.created_at FROM sessions
+JOIN users ON sessions.user_id = users.user_id
+WHERE sessions.session_id = $1 AND sessions.expires_at > NOW()
+`
+
+func (q *Queries) GetUserBySession(ctx context.Context, sessionID string) (User, error) {
+	row := q.db.QueryRow(ctx, getUserBySession, sessionID)
+	var i User
+	err := row.Scan(
+		&i.UserID,
+		&i.Username,
+		&i.DisplayName,
+		&i.IconPath,
+		&i.IsAdmin,
+		&i.Label,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
 const getUserIDByUsername = `-- name: GetUserIDByUsername :one
 SELECT user_id FROM users
 WHERE users.username = $1