summaryrefslogtreecommitdiff
path: root/db/schema
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema')
-rw-r--r--db/schema/evaluation.ts18
-rw-r--r--db/schema/evaluationCriteria.ts9
-rw-r--r--db/schema/users.ts161
-rw-r--r--db/schema/vendorDocu.ts3
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 {
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 05:54:04 +0000