handle session clearing edge case where session may expire prematurely

2 files changed, 24 insertions, 4 deletions

diff --git a/api/app.py b/api/app.py
index 34fd465..e39d9a7 100644
--- a/api/app.py
+++ b/api/app.py
@@ -98,7 +98,11 @@ def verify_answers():
         return jsonify({"error": "Cannot connect to verification database"}), 500
     if server.check_row_exists("sessions", "session_id", session_id) is False:
         return jsonify({"error": "Session ID not found"}), 404
-    correct_answers = server.get_rows("sessions", "session_id", session_id)[0][1].split(",")
+    session_data = server.get_rows("sessions", "session_id", session_id)
+    if not session_data:
+        server.close_connection()
+        return jsonify({"error": "Session expired or not found"}), 404
+    correct_answers = session_data[0][1].split(",")
     server.delete_row("sessions", "session_id", session_id)
     server.close_connection()
     if answer == ",".join(correct_answers):
@@ -111,7 +115,6 @@ def clear_sessions():
     auth_header = request.headers.get("Authorization")
     cron_secret = os.environ.get("CRON_SECRET")
     expected_auth = f"Bearer {cron_secret}"
-    print(f"Received Request to Clear Session: Checking if '{auth_header}' matches '{expected_auth}'")
     if not cron_secret:
         return jsonify({"error": "CRON_SECRET not configured"}), 500
     if auth_header != expected_auth:
@@ -119,7 +122,7 @@ def clear_sessions():
     server = create_database_connection()
     if server.check_health() is False:
         return jsonify({"error": "Cannot connect to verification database"}), 500
-    server.clear_table("sessions")
+    server.clear_old_sessions("sessions")
     server.close_connection()
     return jsonify({"success": True})
 
diff --git a/api/database.py b/api/database.py
index 14ac611..0f31a7d 100644
--- a/api/database.py
+++ b/api/database.py
@@ -20,12 +20,29 @@ class PostgresHandler:
         self._connection.commit()
         cursor.close()
 
-    def clear_table(self, name: str):
+    def clear_table_by_date(self, name: str):
         cursor = self._connection.cursor()
         cursor.execute(f"DELETE FROM {name}")
         self._connection.commit()
         cursor.close()
 
+    def clear_old_sessions(self, table_name: str):
+        """Delete sessions older than 15 minutes"""
+        try:
+            cursor = self._connection.cursor()
+            query = f"DELETE FROM {table_name} WHERE created_at <= NOW() - INTERVAL '15 minutes'"
+            cursor.execute(query)
+            deleted_count = cursor.rowcount
+            self._connection.commit()
+            cursor.close()
+            print(f"Deleted {deleted_count} old sessions")
+            return deleted_count
+        except Error as e:
+            self._connection.rollback()
+            print(f"Failed to delete old sessions from {table_name}")
+            print(e)
+            return False
+
     def check_row_exists(self, table_name: str, column_name: str, value: str):
         cursor = self._connection.cursor()
         query = f"SELECT 1 FROM {table_name} WHERE {column_name} = %s"