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/schema.sql | 10 ++++++++++
 1 file changed, 10 insertions(+)

(limited to 'backend/schema.sql')

diff --git a/backend/schema.sql b/backend/schema.sql
index 5e427ce..4a4b1ac 100644
--- a/backend/schema.sql
+++ b/backend/schema.sql
@@ -94,3 +94,13 @@ CREATE TABLE testcase_results (
     CONSTRAINT uq_submission_id_testcase_id UNIQUE(submission_id, testcase_id)
 );
 CREATE INDEX idx_testcase_results_submission_id ON testcase_results(submission_id);
+
+CREATE TABLE sessions (
+    session_id  VARCHAR(64) PRIMARY KEY,
+    user_id     INT         NOT NULL,
+    expires_at  TIMESTAMP   NOT NULL,
+    created_at  TIMESTAMP   NOT NULL DEFAULT NOW(),
+    CONSTRAINT fk_sessions_user_id FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
+);
+CREATE INDEX idx_sessions_user_id ON sessions(user_id);
+CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
-- 
cgit v1.3.1

