(대표님) 20250627 오전 10시 작업사항

4 files changed, 183 insertions, 8 deletions

diff --git a/db/schema/evaluation.ts b/db/schema/evaluation.ts
index 4db3a25a..fd27ec9b 100644
--- a/db/schema/evaluation.ts
+++ b/db/schema/evaluation.ts
@@ -1,6 +1,7 @@
 import { pgView , pgTable, serial, varchar, text, timestamp, integer, decimal, boolean, uuid } from 'drizzle-orm/pg-core';
 import { relations , sql} from 'drizzle-orm';
 import { Vendor, vendors } from './vendors';
+import { evaluationTargets, periodicEvaluations } from './evaluationTarget';
 
 // 1. 일반 평가표 테이블
 export const generalEvaluations = pgTable('general_evaluations', {
@@ -145,6 +146,9 @@ export type EsgEvaluationsView = typeof esgEvaluationsView.$inferSelect;
 export const evaluationSubmissions = pgTable('evaluation_submissions', {
   id: serial('id').primaryKey(),
   submissionId: uuid('submission_id').defaultRandom().notNull().unique(),
+  periodicEvaluationId: integer('periodic_evaluation_id')
+  .references(() => periodicEvaluations.id, { onDelete: 'cascade' })
+  .notNull(),
   companyId: integer('company_id').references(() => vendors.id, { onDelete: 'cascade' }).notNull(),
   evaluationYear: integer('evaluation_year').notNull(),
   evaluationRound: varchar('evaluation_round', { length: 50 }),
@@ -226,6 +230,10 @@ export const evaluationSubmissionsRelations = relations(evaluationSubmissions, (
     fields: [evaluationSubmissions.companyId],
     references: [vendors.id],
   }),
+  periodicEvaluation: one(periodicEvaluations, {
+    fields: [evaluationSubmissions.periodicEvaluationId],
+    references: [periodicEvaluations.id],
+  }),
   generalEvaluationResponses: many(generalEvaluationResponses),
   esgEvaluationResponses: many(esgEvaluationResponses),
   vendorEvaluationAttachments: many(vendorEvaluationAttachments),
@@ -311,4 +319,12 @@ export type EsgEvaluationResponseWithDetails = EsgEvaluationResponse & {
     esgEvaluation: EsgEvaluation;
   };
   esgAnswerOption: EsgAnswerOption;
-};
\ No newline at end of file
+};
+
+export const periodicEvaluationsRelations = relations(periodicEvaluations, ({ many, one }) => ({
+  evaluationSubmissions: many(evaluationSubmissions),
+  evaluationTarget: one(evaluationTargets, {
+    fields: [periodicEvaluations.evaluationTargetId],
+    references: [evaluationTargets.id],
+  }),
+}));
\ No newline at end of file
diff --git a/db/schema/evaluationCriteria.ts b/db/schema/evaluationCriteria.ts
index 788d0f3e..232c4b69 100644
--- a/db/schema/evaluationCriteria.ts
+++ b/db/schema/evaluationCriteria.ts
@@ -38,7 +38,7 @@ const REG_EVAL_CRITERIA_CATEGORY2 = [
 ];

 

 const REG_EVAL_CRITERIA_CATEGORY_ENUM = REG_EVAL_CRITERIA_CATEGORY.map(c => c.value) as [string, ...string[]];

-const REG_EVAL_CRITERIA_CATEGORY_ENUM2 = REG_EVAL_CRITERIA_CATEGORY2.map(c => c.value) as [string, ...string[]];

+const REG_EVAL_CRITERIA_CATEGORY2_ENUM = REG_EVAL_CRITERIA_CATEGORY2.map(c => c.value) as [string, ...string[]];

 const REG_EVAL_CRITERIA_ITEM_ENUM = REG_EVAL_CRITERIA_ITEM.map(c => c.value) as [string, ...string[]];

 

 // ----------------------------------------------------------------------------------------------------

@@ -47,7 +47,7 @@ const REG_EVAL_CRITERIA_ITEM_ENUM = REG_EVAL_CRITERIA_ITEM.map(c => c.value) as
 const regEvalCriteria = pgTable('reg_eval_criteria', {

   id: serial('id').primaryKey(),

   category: varchar('category', { enum: REG_EVAL_CRITERIA_CATEGORY_ENUM, length: 32 }).default('quality').notNull(),

-  category2: varchar('category2', { enum: REG_EVAL_CRITERIA_CATEGORY_ENUM2, length: 32 }).default('processScore').notNull(),

+  category2: varchar('category2', { enum: REG_EVAL_CRITERIA_CATEGORY2_ENUM, length: 32 }).default('processScore').notNull(),

   item: varchar('item', { enum: REG_EVAL_CRITERIA_ITEM_ENUM, length: 32 }).default('quality').notNull(),

   classification: varchar('classification', { length: 255 }).notNull(),

   range: varchar('range', { length: 255 }),

@@ -124,13 +124,16 @@ type RegEvalCriteriaView = typeof regEvalCriteriaView.$inferSelect;
 /* Export */

 export {

   REG_EVAL_CRITERIA_CATEGORY,

+  REG_EVAL_CRITERIA_CATEGORY_ENUM,

+  REG_EVAL_CRITERIA_CATEGORY2,

+  REG_EVAL_CRITERIA_CATEGORY2_ENUM,

   REG_EVAL_CRITERIA_ITEM,

+  REG_EVAL_CRITERIA_ITEM_ENUM,

   regEvalCriteria,

   regEvalCriteriaDetails,

   regEvalCriteriaDetailsRelations,

   regEvalCriteriaRelations,

   regEvalCriteriaView,

-  REG_EVAL_CRITERIA_CATEGORY2,

   type NewRegEvalCriteria,

   type NewRegEvalCriteriaDetails,

   type RegEvalCriteriaView,

diff --git a/db/schema/users.ts b/db/schema/users.ts
index f6a66a8f..ad1224d2 100644
--- a/db/schema/users.ts
+++ b/db/schema/users.ts
@@ -1,4 +1,5 @@
-import { integer, boolean,serial, pgTable, varchar,timestamp,pgEnum ,pgView, text, primaryKey} from "drizzle-orm/pg-core";
+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";
@@ -11,17 +12,168 @@ export const users = pgTable("users", {
   name: varchar("name", { length: 255 }).notNull(),
   email: varchar("email", { length: 255 }).notNull().unique(),
   companyId: integer("company_id")
-  .references(() => vendors.id, { onDelete: "set null" }),
+    .references(() => vendors.id, { onDelete: "set null" }),
   techCompanyId: integer("tech_company_id")
-  .references(() => techVendors.id, { onDelete: "set null" }),
+    .references(() => techVendors.id, { onDelete: "set null" }),
   domain: userDomainEnum("domain").notNull().default("partners"),
   createdAt: timestamp("created_at", { withTimezone: true })
     .defaultNow()
     .notNull(),
   imageUrl: varchar("image_url", { length: 1024 }),
-  language: varchar("language", { length: 10 }).default("en"), // 언어 필드 추가 (기본값: 영어)
+  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 }),
+  failedLoginAttempts: integer("failed_login_attempts").default(0).notNull(),
+  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' 등
+   
+}, (table) => {
+  return {
+    emailIdx: uniqueIndex("users_email_idx").on(table.email),
+    phoneIdx: index("users_phone_idx").on(table.phone),
+  };
+});
+
+// 패스워드 테이블 (현재 활성 패스워드)
+export const passwords = pgTable("passwords", {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  userId: integer("user_id")
+    .references(() => users.id, { onDelete: "cascade" })
+    .notNull(),
+  passwordHash: varchar("password_hash", { length: 255 }).notNull(), // bcrypt hash
+  salt: varchar("salt", { length: 255 }).notNull(), // 추가 salt
+  createdAt: timestamp("created_at", { withTimezone: true })
+    .defaultNow()
+    .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(),
+  hasLowercase: boolean("has_lowercase").notNull(),
+  hasNumbers: boolean("has_numbers").notNull(),
+  hasSymbols: boolean("has_symbols").notNull(),
+  length: integer("length").notNull(),
+}, (table) => {
+  return {
+    userIdIdx: index("passwords_user_id_idx").on(table.userId),
+    activeIdx: index("passwords_active_idx").on(table.isActive),
+  };
+});
+
+// 패스워드 히스토리 (재사용 방지)
+export const passwordHistory = pgTable("password_history", {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  userId: integer("user_id")
+    .references(() => users.id, { onDelete: "cascade" })
+    .notNull(),
+  passwordHash: varchar("password_hash", { length: 255 }).notNull(),
+  salt: varchar("salt", { length: 255 }).notNull(),
+  createdAt: timestamp("created_at", { withTimezone: true })
+    .defaultNow()
+    .notNull(),
+  replacedAt: timestamp("replaced_at", { withTimezone: true }), // 언제 교체되었는지
+}, (table) => {
+  return {
+    userIdIdx: index("password_history_user_id_idx").on(table.userId),
+    createdAtIdx: index("password_history_created_at_idx").on(table.createdAt),
+  };
+});
+
+// 로그인 시도 로그 (보안 감사)
+export const loginAttempts = pgTable("login_attempts", {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  email: varchar("email", { length: 255 }).notNull(),
+  userId: integer("user_id").references(() => users.id, { onDelete: "set null" }),
+  success: boolean("success").notNull(),
+  ipAddress: varchar("ip_address", { length: 45 }).notNull(), // IPv6 지원
+  userAgent: text("user_agent"),
+  failureReason: varchar("failure_reason", { length: 100 }), // 실패 이유
+  attemptedAt: timestamp("attempted_at", { withTimezone: true })
+    .defaultNow()
+    .notNull(),
+  
+  // 지리적 정보 (옵셔널)
+  country: varchar("country", { length: 2 }),
+  city: varchar("city", { length: 100 }),
+}, (table) => {
+  return {
+    emailIdx: index("login_attempts_email_idx").on(table.email),
+    attemptedAtIdx: index("login_attempts_attempted_at_idx").on(table.attemptedAt),
+    ipAddressIdx: index("login_attempts_ip_address_idx").on(table.ipAddress),
+  };
+});
 
+// MFA 토큰 테이블 (SMS, TOTP 등)
+export const mfaTokens = pgTable("mfa_tokens", {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  userId: integer("user_id")
+    .references(() => users.id, { onDelete: "cascade" })
+    .notNull(),
+  token: varchar("token", { length: 255 }).notNull(), // SMS 코드나 TOTP
+  type: varchar("type", { length: 20 }).notNull(), // 'sms', 'totp', 'backup'
+  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
+  usedAt: timestamp("used_at", { withTimezone: true }),
+  isActive: boolean("is_active").default(true).notNull(),
+  createdAt: timestamp("created_at", { withTimezone: true })
+    .defaultNow()
+    .notNull(),
+  
+  // SMS 관련 추가 정보
+  phoneNumber: varchar("phone_number", { length: 20 }), // 전송된 전화번호
+  attempts: integer("attempts").default(0).notNull(), // 시도 횟수
+}, (table) => {
+  return {
+    userIdIdx: index("mfa_tokens_user_id_idx").on(table.userId),
+    tokenIdx: index("mfa_tokens_token_idx").on(table.token),
+    expiresAtIdx: index("mfa_tokens_expires_at_idx").on(table.expiresAt),
+  };
+});
 
+// 보안 설정 테이블
+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(),
+  requireLowercase: boolean("require_lowercase").default(true).notNull(),
+  requireNumbers: boolean("require_numbers").default(true).notNull(),
+  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(),
+  updatedAt: timestamp("updated_at", { withTimezone: true })
+    .defaultNow()
+    .notNull(),
 });
 
 
@@ -105,6 +257,7 @@ export const userView = pgView("user_view").as((qb) => {
 
       // 2) userName: string
       user_name: sql<string>`${users.name}`.as("user_name"),
+      user_phone: sql<string>`${users.phone}`.as("user_phone"),
 
       // 3) userEmail: string
       user_email: sql<string>`${users.email}`.as("user_email"),
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index 3b56022e..1953d5ac 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -166,6 +166,9 @@ export const revisions = pgTable(
     comment: varchar("comment", { length: 500 }),
     createdAt: timestamp("created_at").defaultNow().notNull(),
     updatedAt: timestamp("updated_at").defaultNow().notNull(),
+
+    registerId: varchar("register_id", { length: 50 }), // 상대 시스템에서 생성한 ID
+
   },
   (table) => {
     return {