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/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