summaryrefslogtreecommitdiff
path: root/db/schema
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema')
-rw-r--r--db/schema/evaluationTarget.ts2
-rw-r--r--db/schema/index.ts1
-rw-r--r--db/schema/menu.ts55
-rw-r--r--db/schema/users.ts72
4 files changed, 97 insertions, 33 deletions
diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts
index a2b1a46e..66f3dc04 100644
--- a/db/schema/evaluationTarget.ts
+++ b/db/schema/evaluationTarget.ts
@@ -527,7 +527,7 @@ export const reviewerEvaluationDetails = pgTable("reviewer_evaluation_details",
}));
// 1. periodicEvaluations relations
-export const periodicEvaluationsRelations = relations(periodicEvaluations, ({ one, many }) => ({
+export const periodicEvaluationsRelations2 = relations(periodicEvaluations, ({ one, many }) => ({
// 평가 대상
evaluationTarget: one(evaluationTargets, {
fields: [periodicEvaluations.evaluationTargetId],
diff --git a/db/schema/index.ts b/db/schema/index.ts
index cb5a42d8..f406a4d5 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -22,6 +22,7 @@ export * from './evaluation';
export * from './evaluationTarget';
export * from './evaluationCriteria';
export * from './projectGtc';
+export * from './menu';
// MDG SOAP 수신용
export * from './MDG/mdg'
diff --git a/db/schema/menu.ts b/db/schema/menu.ts
new file mode 100644
index 00000000..8491366b
--- /dev/null
+++ b/db/schema/menu.ts
@@ -0,0 +1,55 @@
+// db/schema.ts에 추가할 테이블 정의
+import { pgTable, serial, varchar, text, timestamp, boolean, integer ,uniqueIndex, index} from "drizzle-orm/pg-core";
+import { userDomainEnum, users } from "./users";
+import { relations } from "drizzle-orm";
+
+export const menuAssignments = pgTable("menu_assignments", {
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+ menuPath: varchar("menu_path", { length: 255 }).notNull().unique(), // href 값
+ menuTitle: varchar("menu_title", { length: 255 }).notNull(),
+ menuDescription: text("menu_description"),
+ menuGroup: varchar("menu_group", { length: 100 }), // 그룹 정보
+ sectionTitle: varchar("section_title", { length: 255 }), // 섹션명
+ domain: userDomainEnum("domain").notNull().default("evcp"), // evcp, partners
+
+ // 담당자 정보
+ manager1Id: integer("manager1_id")
+ .references(() => users.id, { onDelete: "set null" }),
+ manager2Id: integer("manager2_id")
+ .references(() => users.id, { onDelete: "set null" }),
+
+ // 메타 정보
+ isActive: boolean("is_active").default(true).notNull(),
+ createdAt: timestamp("created_at", { withTimezone: true })
+ .defaultNow()
+ .notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true })
+ .defaultNow()
+ .notNull(),
+ }, (table) => {
+ return {
+ menuPathIdx: uniqueIndex("menu_assignments_path_idx").on(table.menuPath),
+ manager1Idx: index("menu_assignments_manager1_idx").on(table.manager1Id),
+ manager2Idx: index("menu_assignments_manager2_idx").on(table.manager2Id),
+ domainIdx: index("menu_assignments_domain_idx").on(table.domain),
+ };
+ });
+
+ // Relations 추가
+ export const menuAssignmentsRelations = relations(menuAssignments, ({ one }) => ({
+ manager1: one(users, {
+ fields: [menuAssignments.manager1Id],
+ references: [users.id],
+ relationName: "manager1",
+ }),
+ manager2: one(users, {
+ fields: [menuAssignments.manager2Id],
+ references: [users.id],
+ relationName: "manager2",
+ }),
+ }));
+
+ export const usersRelations2 = relations(users, ({ many }) => ({
+ managedMenus1: many(menuAssignments, { relationName: "manager1" }),
+ managedMenus2: many(menuAssignments, { relationName: "manager2" }),
+ })); \ No newline at end of file
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()`),
});
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 14:11:51 +0000