1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
import {
pgTable,
uuid,
varchar,
timestamp,
text,
boolean,
integer,
inet, serial
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { users } from './users';
// 로그인 세션 테이블 (로그인/로그아웃 이력)
export const loginSessions = pgTable('login_sessions', {
id: uuid('id').primaryKey().defaultRandom(),
userId: integer('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
loginAt: timestamp('login_at').defaultNow().notNull(),
logoutAt: timestamp('logout_at'), // 로그아웃 시간 (nullable)
ipAddress: inet('ip_address').notNull(),
userAgent: text('user_agent'),
sessionToken: varchar('session_token', { length: 255 }).unique(), // NextAuth JWT token ID
nextAuthSessionId: varchar('nextauth_session_id', { length: 255 }).unique(), // NextAuth 세션 ID
authMethod: varchar('auth_method', { length: 50 }).notNull(), // 'otp', 'email', 'sgips', 'saml'
isActive: boolean('is_active').default(true).notNull(),
lastActivityAt: timestamp('last_activity_at').defaultNow().notNull(), // 마지막 활동 시간
sessionExpiredAt: timestamp('session_expired_at'), // 세션 만료 시간
createdAt: timestamp('created_at').defaultNow().notNull(),
updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
// 페이지 방문 이력 테이블 (라우트별 접속 이력)
export const pageVisits = pgTable('page_visits', {
id: uuid('id').primaryKey().defaultRandom(),
userId: integer('user_id').references(() => users.id, { onDelete: 'cascade' }), // nullable (비로그인 사용자 추적 가능)
sessionId: uuid('session_id').references(() => loginSessions.id, { onDelete: 'set null' }), // nullable
route: varchar('route', { length: 500 }).notNull(), // 방문한 라우트
pageTitle: varchar('page_title', { length: 200 }), // 페이지 제목
referrer: text('referrer'), // 이전 페이지 URL
ipAddress: inet('ip_address').notNull(),
userAgent: text('user_agent'),
visitedAt: timestamp('visited_at').defaultNow().notNull(),
duration: integer('duration'), // 페이지 체류 시간 (초 단위, nullable)
// 추가 메타데이터
queryParams: text('query_params'), // URL 쿼리 파라미터
deviceType: varchar('device_type', { length: 50 }), // mobile, desktop, tablet
browserName: varchar('browser_name', { length: 50 }),
osName: varchar('os_name', { length: 50 }),
});
// 일별 접속 통계 테이블 (선택사항 - 성능 최적화용)
export const dailyAccessStats = pgTable('daily_access_stats', {
id: uuid('id').primaryKey().defaultRandom(),
date: timestamp('date').notNull(),
totalVisits: integer('total_visits').default(0).notNull(),
uniqueUsers: integer('unique_users').default(0).notNull(),
totalSessions: integer('total_sessions').default(0).notNull(),
avgSessionDuration: integer('avg_session_duration'), // 평균 세션 지속 시간 (초)
createdAt: timestamp('created_at').defaultNow().notNull(),
});
// Relations 정의
export const usersRelationsLogin = relations(users, ({ many }) => ({
loginSessions: many(loginSessions),
pageVisits: many(pageVisits),
}));
export const loginSessionsRelations = relations(loginSessions, ({ one, many }) => ({
user: one(users, {
fields: [loginSessions.userId],
references: [users.id],
}),
pageVisits: many(pageVisits),
}));
export const pageVisitsRelations = relations(pageVisits, ({ one }) => ({
user: one(users, {
fields: [pageVisits.userId],
references: [users.id],
}),
session: one(loginSessions, {
fields: [pageVisits.sessionId],
references: [loginSessions.id],
}),
}));
// NextAuth 연동을 위한 추가 필드
export const tempAuthSessions = pgTable('temp_auth_sessions', {
id: uuid('id').primaryKey().defaultRandom(),
tempAuthKey: varchar('temp_auth_key', { length: 255 }).unique().notNull(),
userId: integer('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
email: varchar('email', { length: 255 }).notNull(),
authMethod: varchar('auth_method', { length: 50 }).notNull(), // 'otp', 'email', 'sgips', 'saml'
expiresAt: timestamp('expires_at').notNull(),
isUsed: boolean('is_used').default(false).notNull(),
createdAt: timestamp('created_at').defaultNow().notNull(),
});
export type TempAuthSession = typeof tempAuthSessions.$inferSelect;
export type NewTempAuthSession = typeof tempAuthSessions.$inferInsert;
export type LoginSession = typeof loginSessions.$inferSelect;
export type NewLoginSession = typeof loginSessions.$inferInsert;
export type PageVisit = typeof pageVisits.$inferSelect;
export type NewPageVisit = typeof pageVisits.$inferInsert;
export type DailyAccessStats = typeof dailyAccessStats.$inferSelect;
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 }),
});
|
