(대표님) 파일관리변경, 클라IP추적, 실시간알림, 미들웨어변경, 알림API

1 files changed, 57 insertions, 0 deletions

diff --git a/db/notification.sql b/db/notification.sql
new file mode 100644
index 00000000..1a6ae269
--- /dev/null
+++ b/db/notification.sql
@@ -0,0 +1,57 @@
+-- migrations/001_notification_triggers.sql
+
+-- 1. 알림 생성 시 실시간 NOTIFY 함수
+CREATE OR REPLACE FUNCTION notify_new_notification()
+RETURNS TRIGGER AS $$
+BEGIN
+  -- 새 알림이 생성될 때 NOTIFY 전송
+  PERFORM pg_notify(
+    'new_notification',
+    json_build_object(
+      'id', NEW.id,
+      'user_id', NEW.user_id,
+      'title', NEW.title,
+      'message', NEW.message,
+      'type', NEW.type,
+      'related_record_id', NEW.related_record_id,
+      'related_record_type', NEW.related_record_type,
+      'is_read', NEW.is_read,
+      'created_at', NEW.created_at
+    )::text
+  );
+  
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+-- 2. 알림 읽음 상태 변경 시 NOTIFY 함수
+CREATE OR REPLACE FUNCTION notify_notification_read()
+RETURNS TRIGGER AS $$
+BEGIN
+  -- 읽음 상태가 변경될 때 NOTIFY 전송
+  IF OLD.is_read != NEW.is_read THEN
+    PERFORM pg_notify(
+      'notification_read',
+      json_build_object(
+        'id', NEW.id,
+        'user_id', NEW.user_id,
+        'is_read', NEW.is_read,
+        'read_at', NEW.read_at
+      )::text
+    );
+  END IF;
+  
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+-- 트리거 생성 (알림 테이블에만)
+CREATE OR REPLACE TRIGGER trigger_notify_new_notification
+  AFTER INSERT ON notifications
+  FOR EACH ROW
+  EXECUTE FUNCTION notify_new_notification();
+
+CREATE OR REPLACE TRIGGER trigger_notify_notification_read
+  AFTER UPDATE ON notifications
+  FOR EACH ROW
+  EXECUTE FUNCTION notify_notification_read();
\ No newline at end of file