From 4210c9b38f9053f6720a6bebaadefd24c542eaa9 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Thu, 31 Oct 2024 22:06:00 +0300
Subject: backend: better auth check, audit logging

---
 backend/database/functions.sql | 14 +++++++++++++
 backend/database/init.sql      | 45 ++++++++++++++++++++++++++++++++----------
 2 files changed, 49 insertions(+), 10 deletions(-)

(limited to 'backend/database')

diff --git a/backend/database/functions.sql b/backend/database/functions.sql
index ca33a60..6a6f6d2 100644
--- a/backend/database/functions.sql
+++ b/backend/database/functions.sql
@@ -1,3 +1,17 @@
+CREATE OR REPLACE FUNCTION log_audit() RETURNS TRIGGER AS $$
+BEGIN
+    INSERT INTO audit (table_name, operation_type, old_data, new_data, changed_by)
+    VALUES (
+        TG_TABLE_NAME,
+        TG_OP,
+        CASE WHEN TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN row_to_json(OLD) ELSE NULL END,
+        CASE WHEN TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN row_to_json(NEW) ELSE NULL END,
+        current_setting('app.user_id')::TEXT
+    );
+    RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
 CREATE OR REPLACE FUNCTION get_rankings_singleplayer()
 RETURNS TABLE (
     steam_id TEXT,
diff --git a/backend/database/init.sql b/backend/database/init.sql
index 77a88f5..51bf2af 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -12,6 +12,10 @@ CREATE TABLE users (
   PRIMARY KEY (steam_id)
 );
 
+CREATE TRIGGER "users"
+AFTER INSERT OR UPDATE OR DELETE ON "users"
+FOR EACH ROW EXECUTE FUNCTION log_audit();
+
 CREATE TABLE games (
   id SERIAL,
   name TEXT NOT NULL,
@@ -72,6 +76,10 @@ CREATE TABLE map_history (
   UNIQUE (map_id, category_id, score_count)
 );
 
+CREATE TRIGGER "map_history"
+AFTER INSERT OR UPDATE OR DELETE ON "map_history"
+FOR EACH ROW EXECUTE FUNCTION log_audit();
+
 CREATE TABLE map_ratings (
   id SERIAL,
   map_id SMALLINT NOT NULL,
@@ -98,6 +106,10 @@ CREATE TABLE map_discussions (
   FOREIGN KEY (user_id) REFERENCES users(steam_id)
 );
 
+CREATE TRIGGER "map_discussions"
+AFTER INSERT OR UPDATE OR DELETE ON "map_discussions"
+FOR EACH ROW EXECUTE FUNCTION log_audit();
+
 CREATE TABLE map_discussions_comments (
   id SERIAL,
   discussion_id INT NOT NULL,
@@ -109,6 +121,10 @@ CREATE TABLE map_discussions_comments (
   FOREIGN KEY (user_id) REFERENCES users(steam_id)
 );
 
+CREATE TRIGGER "map_discussions_comments"
+AFTER INSERT OR UPDATE OR DELETE ON "map_discussions_comments"
+FOR EACH ROW EXECUTE FUNCTION log_audit();
+
 CREATE TABLE map_discussions_upvotes (
   id SERIAL,
   discussion_id INT NOT NULL,
@@ -140,6 +156,10 @@ CREATE TABLE records_sp (
   FOREIGN KEY (demo_id) REFERENCES demos(id)
 );
 
+CREATE TRIGGER "records_sp"
+AFTER INSERT OR UPDATE OR DELETE ON "records_sp"
+FOR EACH ROW EXECUTE FUNCTION log_audit();
+
 CREATE TABLE records_mp (
   id SERIAL,
   map_id SMALLINT NOT NULL,
@@ -159,6 +179,10 @@ CREATE TABLE records_mp (
   FOREIGN KEY (partner_demo_id) REFERENCES demos(id)
 );
 
+CREATE TRIGGER "records_mp"
+AFTER INSERT OR UPDATE OR DELETE ON "records_mp"
+FOR EACH ROW EXECUTE FUNCTION log_audit();
+
 CREATE TABLE titles (
   id SERIAL,
   title_name TEXT NOT NULL,
@@ -179,13 +203,14 @@ CREATE TABLE countries (
   PRIMARY KEY (country_code)
 );
 
-CREATE TABLE logs (
-  id SERIAL,
-  user_id TEXT NOT NULL,
-  type TEXT NOT NULL,
-  description TEXT NOT NULL,
-  message TEXT NOT NULL DEFAULT,
-  date TIMESTAMP NOT NULL DEFAULT now(),
-  PRIMARY KEY (id),
-  FOREIGN KEY (user_id) REFERENCES users(steam_id)
-);
\ No newline at end of file
+CREATE TABLE audit (
+    id SERIAL,
+    table_name TEXT NOT NULL,
+    operation_type TEXT NOT NULL, -- 'INSERT', 'UPDATE', or 'DELETE'
+    old_data JSONB,
+    new_data JSONB,
+    changed_by TEXT NOT NULL,
+    changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
+    PRIMARY KEY (id),
+    FOREIGN KEY (changed_by) REFERENCES users(steam_id)
+);
-- 
cgit v1.2.3