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

1 files changed, 38 insertions, 2 deletions

diff --git a/db/schema/history.ts b/db/schema/history.ts
index 13b00196..ad5ac858 100644
--- a/db/schema/history.ts
+++ b/db/schema/history.ts
@@ -6,7 +6,7 @@ import {
     text,
     boolean,
     integer,
-    inet
+    inet, serial
 } from 'drizzle-orm/pg-core';
 import { relations } from 'drizzle-orm';
 import { users } from './users';
@@ -110,4 +110,40 @@ export type PageVisit = typeof pageVisits.$inferSelect;
 export type NewPageVisit = typeof pageVisits.$inferInsert;
 
 export type DailyAccessStats = typeof dailyAccessStats.$inferSelect;
-export type NewDailyAccessStats = typeof dailyAccessStats.$inferInsert;
\ No newline at end of file
+export type NewDailyAccessStats = typeof dailyAccessStats.$inferInsert;
+
+
+export const fileDownloadLogs = pgTable('file_download_logs', {
+    id: serial('id').primaryKey(),
+    fileId: integer('file_id').notNull(),
+    userId: varchar('user_id', { length: 50 }).notNull(),
+    userEmail: varchar('user_email', { length: 255 }),
+    userName: varchar('user_name', { length: 100 }),
+    userRole: varchar('user_role', { length: 50 }),
+    userIP: inet('user_ip'), // PostgreSQL의 inet 타입 사용 (IPv4/IPv6 지원)
+    userAgent: text('user_agent'),
+    fileName: varchar('file_name', { length: 255 }),
+    filePath: varchar('file_path', { length: 500 }),
+    fileSize: integer('file_size'),
+    downloadedAt: timestamp('downloaded_at', { withTimezone: true }).defaultNow().notNull(),
+    success: boolean('success').notNull(),
+    errorMessage: text('error_message'),
+    sessionId: varchar('session_id', { length: 100 }),
+    requestId: varchar('request_id', { length: 50 }), // 요청 추적용
+    referer: text('referer'),
+    downloadDurationMs: integer('download_duration_ms'), // 다운로드 소요 시간
+  });
+  
+  // 사용자별 다운로드 통계 테이블 (선택사항)
+  export const userDownloadStats = pgTable('user_download_stats', {
+    id: serial('id').primaryKey(),
+    userId: varchar('user_id', { length: 50 }).notNull(),
+    date: timestamp('date', { withTimezone: true }).notNull(),
+    totalDownloads: integer('total_downloads').default(0).notNull(),
+    totalBytes: integer('total_bytes').default(0).notNull(),
+    uniqueFiles: integer('unique_files').default(0).notNull(),
+    lastDownloadAt: timestamp('last_download_at', { withTimezone: true }),
+  });
+  
+ 
+  
\ No newline at end of file