(대표님) 20250630 16시 - 유저 도메인별 라우터 분리와 보안성검토 대응

1 files changed, 40 insertions, 32 deletions

diff --git a/db/schema/users.ts b/db/schema/users.ts
index ad1224d2..ae97da6f 100644
--- a/db/schema/users.ts
+++ b/db/schema/users.ts
@@ -1,16 +1,20 @@
-import { integer, boolean,serial, pgTable, varchar,timestamp,pgEnum ,pgView, text, primaryKey,   index,
-  uniqueIndex,} from "drizzle-orm/pg-core";
-import { eq , sql} from "drizzle-orm";
+import {
+  integer, boolean, serial, pgTable, varchar, timestamp, pgEnum, pgView, text, primaryKey, index,
+  uniqueIndex,
+} from "drizzle-orm/pg-core";
+import { eq, sql } from "drizzle-orm";
 import { vendors } from "./vendors";
 import { techVendors } from "./techVendors";
 
-export const userDomainEnum = pgEnum("user_domain", ["evcp", "partners"]); 
+export const userDomainEnum = pgEnum("user_domain", ["pending", "evcp", "procurement", "sales", "engineering", "partners"]);
 
 
 export const users = pgTable("users", {
   id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
   name: varchar("name", { length: 255 }).notNull(),
   email: varchar("email", { length: 255 }).notNull().unique(),
+  deptName: varchar("deptName", { length: 255 }),
+
   companyId: integer("company_id")
     .references(() => vendors.id, { onDelete: "set null" }),
   techCompanyId: integer("tech_company_id")
@@ -19,14 +23,18 @@ export const users = pgTable("users", {
   createdAt: timestamp("created_at", { withTimezone: true })
     .defaultNow()
     .notNull(),
+
+  updatedAt: timestamp("updated_at", { withTimezone: true })
+    .defaultNow()
+    .notNull(),
   imageUrl: varchar("image_url", { length: 1024 }),
   language: varchar("language", { length: 10 }).default("en"),
-  
+
   // MFA 관련 새 컬럼들
   phone: varchar("phone", { length: 20 }), // 국제 형식 전화번호 (+82-10-1234-5678)
   mfaEnabled: boolean("mfa_enabled").default(false).notNull(),
   mfaSecret: varchar("mfa_secret", { length: 32 }), // TOTP secret (나중에 사용)
-  
+
   // 계정 보안 관련
   isLocked: boolean("is_locked").default(false).notNull(),
   lockoutUntil: timestamp("lockout_until", { withTimezone: true }),
@@ -34,11 +42,11 @@ export const users = pgTable("users", {
   lastLoginAt: timestamp("last_login_at", { withTimezone: true }),
   passwordChangeRequired: boolean("password_change_required").default(false).notNull(),
 
-   // 비활성화 관련 새 필드들
-   isActive: boolean("is_active").default(true).notNull(),
-   deactivatedAt: timestamp("deactivated_at", { withTimezone: true }),
-   deactivationReason: varchar("deactivation_reason", { length: 50 }), // 'INACTIVE', 'ADMIN', 'GDPR' 등
-   
+  // 비활성화 관련 새 필드들
+  isActive: boolean("is_active").default(true).notNull(),
+  deactivatedAt: timestamp("deactivated_at", { withTimezone: true }),
+  deactivationReason: varchar("deactivation_reason", { length: 50 }), // 'INACTIVE', 'ADMIN', 'GDPR' 등
+
 }, (table) => {
   return {
     emailIdx: uniqueIndex("users_email_idx").on(table.email),
@@ -59,7 +67,7 @@ export const passwords = pgTable("passwords", {
     .notNull(),
   expiresAt: timestamp("expires_at", { withTimezone: true }), // 패스워드 만료일
   isActive: boolean("is_active").default(true).notNull(),
-  
+
   // 패스워드 메타데이터
   strength: integer("strength").notNull(), // 1-5 강도 점수
   hasUppercase: boolean("has_uppercase").notNull(),
@@ -105,7 +113,7 @@ export const loginAttempts = pgTable("login_attempts", {
   attemptedAt: timestamp("attempted_at", { withTimezone: true })
     .defaultNow()
     .notNull(),
-  
+
   // 지리적 정보 (옵셔널)
   country: varchar("country", { length: 2 }),
   city: varchar("city", { length: 100 }),
@@ -131,7 +139,7 @@ export const mfaTokens = pgTable("mfa_tokens", {
   createdAt: timestamp("created_at", { withTimezone: true })
     .defaultNow()
     .notNull(),
-  
+
   // SMS 관련 추가 정보
   phoneNumber: varchar("phone_number", { length: 20 }), // 전송된 전화번호
   attempts: integer("attempts").default(0).notNull(), // 시도 횟수
@@ -146,7 +154,7 @@ export const mfaTokens = pgTable("mfa_tokens", {
 // 보안 설정 테이블
 export const securitySettings = pgTable("security_settings", {
   id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
-  
+
   // 패스워드 정책
   minPasswordLength: integer("min_password_length").default(8).notNull(),
   requireUppercase: boolean("require_uppercase").default(true).notNull(),
@@ -155,19 +163,19 @@ export const securitySettings = pgTable("security_settings", {
   requireSymbols: boolean("require_symbols").default(true).notNull(),
   passwordExpiryDays: integer("password_expiry_days").default(90), // null이면 만료 없음
   passwordHistoryCount: integer("password_history_count").default(5).notNull(),
-  
+
   // 계정 잠금 정책
   maxFailedAttempts: integer("max_failed_attempts").default(5).notNull(),
   lockoutDurationMinutes: integer("lockout_duration_minutes").default(30).notNull(),
-  
+
   // MFA 정책
   requireMfaForPartners: boolean("require_mfa_for_partners").default(true).notNull(),
   smsTokenExpiryMinutes: integer("sms_token_expiry_minutes").default(5).notNull(),
   maxSmsAttemptsPerDay: integer("max_sms_attempts_per_day").default(10).notNull(),
-  
+
   // 세션 관리
   sessionTimeoutMinutes: integer("session_timeout_minutes").default(480).notNull(), // 8시간
-  
+
   createdAt: timestamp("created_at", { withTimezone: true })
     .defaultNow()
     .notNull(),
@@ -177,18 +185,18 @@ export const securitySettings = pgTable("security_settings", {
 });
 
 
-  // 부서 정의 테이블
-  export const departments = pgTable("departments", {
-    id: serial("id").primaryKey(),
-    departmentCode: varchar("department_code", { length: 50 }).notNull().unique(),
-    departmentName: varchar("department_name", { length: 100 }).notNull(),
-    description: text("description"),
-    isActive: boolean("is_active").notNull().default(true),
-    
-    createdAt: timestamp("created_at").defaultNow().notNull(),
-    updatedAt: timestamp("updated_at").defaultNow().notNull(),
-  });
-  
+// 부서 정의 테이블
+export const departments = pgTable("departments", {
+  id: serial("id").primaryKey(),
+  departmentCode: varchar("department_code", { length: 50 }).notNull().unique(),
+  departmentName: varchar("department_name", { length: 100 }).notNull(),
+  description: text("description"),
+  isActive: boolean("is_active").notNull().default(true),
+
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
 
 export const otps = pgTable('otps', {
   email: varchar('email', { length: 256 }).notNull().primaryKey(),
@@ -210,7 +218,7 @@ export const roles = pgTable("roles", {
   name: text("name").notNull(), // admin, manager
   domain: userDomainEnum("domain").notNull(),
   companyId: integer("company_id")
-  .references(() => vendors.id, { onDelete: "cascade" }), 
+    .references(() => vendors.id, { onDelete: "cascade" }),
   description: text("description").default("").notNull(),
   createdAt: timestamp("created_at").default(sql`now()`),
 });