(대표님) 권한관리, 문서업로드, rfq첨부, SWP문서룰 등

(최겸) 입찰

5 files changed, 232 insertions, 21 deletions

diff --git a/db/schema/consent.ts b/db/schema/consent.ts
index c67f4b7d..613e92f0 100644
--- a/db/schema/consent.ts
+++ b/db/schema/consent.ts
@@ -38,7 +38,7 @@ import { users } from "./users";
   export const policyVersions = pgTable("policy_versions", {
     id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
     policyType: policyTypeEnum("policy_type").notNull(),
-    locale: varchar("locale", { length: 10 }).notNull(), // ko, en
+    locale: varchar("locale", { length: 10 }).default("ko"), // ko, en
     version: varchar("version", { length: 20 }).notNull(),
     content: text("content").notNull(),
     effectiveDate: timestamp("effective_date", { withTimezone: true }).notNull(),
diff --git a/db/schema/index.ts b/db/schema/index.ts
index efd38e71..61d477e6 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -44,6 +44,8 @@ export * from './generalContract';
 export * from './rfqLastTBE';
 export * from './pcr';
 
+export * from './permissions';
+
 export * from './fileSystem';
 
 // 부서별 도메인 할당 관리
diff --git a/db/schema/permissions.ts b/db/schema/permissions.ts
new file mode 100644
index 00000000..5f641f83
--- /dev/null
+++ b/db/schema/permissions.ts
@@ -0,0 +1,204 @@
+// db/schema/permissions.ts
+
+import {unique, pgTable, varchar, text, timestamp, boolean, integer, uniqueIndex, index, primaryKey, pgEnum } from "drizzle-orm/pg-core";
+import { userDomainEnum, users, roles, menuAssignments } from "@/db/schema";
+import { sql } from "drizzle-orm";
+
+// 권한 타입 enum
+export const permissionTypeEnum = pgEnum("permission_type", [
+  "menu_access",      // 메뉴 접근
+  "action",          // 액션 실행 (버튼 클릭 등)
+  "data_read",       // 데이터 읽기
+  "data_write",      // 데이터 쓰기
+  "data_delete",     // 데이터 삭제
+  "approve",         // 승인
+  "export",          // 내보내기
+  "import"           // 가져오기
+]);
+
+// 권한 범위 enum
+export const permissionScopeEnum = pgEnum("permission_scope", [
+  "all",             // 전체 데이터
+  "domain",          // 도메인 내 전체
+  "assigned",        // 담당자 할당된 것만
+  "own",             // 본인 것만
+  "department",      // 부서 내
+  "company"          // 회사 내
+]);
+
+// 확장된 권한 테이블
+export const permissions = pgTable("permissions", {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  permissionKey: varchar("permission_key", { length: 255 }).notNull().unique(),
+  permissionType: permissionTypeEnum("permission_type").notNull(),
+  resource: varchar("resource", { length: 100 }).notNull(),
+  action: varchar("action", { length: 50 }).notNull(),
+  scope: permissionScopeEnum("permission_scope").default("own"),
+  
+  // 메뉴 연관
+  menuPath: varchar("menu_path", { length: 255 })
+    .references(() => menuAssignments.menuPath, { onDelete: "set null" }),
+  
+  // UI 요소 매핑
+  uiElement: varchar("ui_element", { length: 255 }), // 버튼 ID, 컴포넌트 이름 등
+  
+  name: varchar("name", { length: 255 }).notNull(),
+  description: text("description"),
+  isSystem: boolean("is_system").default(false).notNull(), // 시스템 권한 여부
+  isActive: boolean("is_active").default(true).notNull(),
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => ({
+  permissionKeyIdx: uniqueIndex("permissions_key_idx").on(table.permissionKey),
+  resourceActionIdx: index("permissions_resource_action_idx").on(table.resource, table.action),
+  menuPathIdx: index("permissions_menu_path_idx").on(table.menuPath),
+  typeIdx: index("permissions_type_idx").on(table.permissionType),
+}));
+
+// 권한 그룹 (권한 묶음)
+export const permissionGroups = pgTable("permission_groups", {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  groupKey: varchar("group_key", { length: 100 }).notNull().unique(),
+  name: varchar("name", { length: 255 }).notNull(),
+  description: text("description"),
+  domain: userDomainEnum("domain"),
+  isActive: boolean("is_active").default(true).notNull(),
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => ({
+  groupKeyIdx: uniqueIndex("permission_groups_key_idx").on(table.groupKey),
+  domainIdx: index("permission_groups_domain_idx").on(table.domain),
+}));
+
+// 권한 그룹 멤버십
+export const permissionGroupMembers = pgTable("permission_group_members", {
+  groupId: integer("group_id")
+    .references(() => permissionGroups.id, { onDelete: "cascade" })
+    .notNull(),
+  permissionId: integer("permission_id")
+    .references(() => permissions.id, { onDelete: "cascade" })
+    .notNull(),
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+}, (table) => ({
+  pk: primaryKey({ columns: [table.groupId, table.permissionId] }),
+  groupIdx: index("pgm_group_idx").on(table.groupId),
+  permissionIdx: index("pgm_permission_idx").on(table.permissionId),
+}));
+
+// 역할-권한 매핑 (기존 rolePermissions 대체)
+export const rolePermissions = pgTable("role_permissions", {
+  roleId: integer("role_id")
+    .references(() => roles.id, { onDelete: "cascade" })
+    .notNull(),
+  permissionId: integer("permission_id")
+    .references(() => permissions.id, { onDelete: "cascade" }),
+  permissionGroupId: integer("permission_group_id")
+    .references(() => permissionGroups.id, { onDelete: "cascade" }),
+  
+  // 권한 부여 정보
+  grantedBy: integer("granted_by")
+    .references(() => users.id, { onDelete: "set null" }),
+  grantedAt: timestamp("granted_at").defaultNow().notNull(),
+  expiresAt: timestamp("expires_at"), // 권한 만료일
+  
+  isActive: boolean("is_active").default(true).notNull(),
+  notes: text("notes"),
+}, (table) => ({
+  pk: primaryKey({ 
+    columns: [table.roleId, table.permissionId, table.permissionGroupId] 
+  }),
+  roleIdx: index("role_permissions_role_idx").on(table.roleId),
+  permissionIdx: index("role_permissions_permission_idx").on(table.permissionId),
+  groupIdx: index("role_permissions_group_idx").on(table.permissionGroupId),
+}));
+
+// 사용자 직접 권한 (역할 외 추가 권한)
+export const userPermissions = pgTable("user_permissions", {
+  userId: integer("user_id")
+    .references(() => users.id, { onDelete: "cascade" })
+    .notNull(),
+
+  // 직접 부여를 그룹 없이도 가능하게 하려면 permissionId는 PK에 포함되므로 notNull 권장
+  permissionId: integer("permission_id")
+    .references(() => permissions.id, { onDelete: "cascade" })
+    .notNull(),
+
+  // 이제 nullable (직접 부여 시 NULL)
+  permissionGroupId: integer("permission_group_id")
+    .references(() => permissionGroups.id, { onDelete: "cascade" }),
+
+  // 권한 타입
+  isGrant: boolean("is_grant").default(true).notNull(), // true: 부여, false: 제한
+
+  // 권한 부여 정보
+  grantedBy: integer("granted_by")
+    .references(() => users.id, { onDelete: "set null" }),
+  grantedAt: timestamp("granted_at").defaultNow().notNull(),
+  expiresAt: timestamp("expires_at"),
+
+  isActive: boolean("is_active").default(true).notNull(),
+  reason: text("reason"),
+}, (table) => ({
+  // PK에서 group 제거 → (user_id, permission_id)
+  pk: primaryKey({ columns: [table.userId, table.permissionId] }),
+
+  // 그룹이 있는 행에 대해서만 유니크 보장 (NULL은 유니크 고려에서 제외됨)
+  userPermGroupUnique: uniqueIndex("user_permissions_user_perm_group_unique")
+    .on(table.userId, table.permissionId, table.permissionGroupId)
+    .where(sql`permission_group_id IS NOT NULL`),
+
+  userIdx: index("user_permissions_user_idx").on(table.userId),
+  permissionIdx: index("user_permissions_permission_idx").on(table.permissionId),
+  groupIdx: index("user_permissions_group_idx").on(table.permissionGroupId),
+}));
+
+// 메뉴별 기본 필요 권한
+export const menuRequiredPermissions = pgTable("menu_required_permissions", {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  menuPath: varchar("menu_path", { length: 255 })
+    .references(() => menuAssignments.menuPath, { onDelete: "cascade" })
+    .notNull(),
+  permissionId: integer("permission_id")
+    .references(() => permissions.id, { onDelete: "cascade" })
+    .notNull(),
+  isRequired: boolean("is_required").default(true).notNull(),
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+}, (table) => ({
+  uq: unique("uq_menu_path_permission_id").on(table.menuPath, table.permissionId),
+  menuPathIdx: index("mrp_menu_path_idx").on(table.menuPath),
+  permissionIdx: index("mrp_permission_idx").on(table.permissionId),
+}));
+
+
+// 권한 감사 로그
+export const permissionAuditLogs = pgTable("permission_audit_logs", {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  
+  // 대상
+  targetType: varchar("target_type", { length: 50 }).notNull(), // 'user', 'role'
+  targetId: integer("target_id").notNull(),
+  
+  // 권한
+  permissionId: integer("permission_id")
+    .references(() => permissions.id, { onDelete: "set null" }),
+  permissionGroupId: integer("permission_group_id")
+    .references(() => permissionGroups.id, { onDelete: "set null" }),
+  
+  // 액션
+  action: varchar("action", { length: 50 }).notNull(), // 'grant', 'revoke', 'expire'
+  
+  // 실행자
+  performedBy: integer("performed_by")
+    .references(() => users.id, { onDelete: "set null" }),
+  performedAt: timestamp("performed_at").defaultNow().notNull(),
+  
+  // 추가 정보
+  reason: text("reason"),
+  metadata: text("metadata"), // JSON 형태의 추가 정보
+  ipAddress: varchar("ip_address", { length: 45 }),
+}, (table) => ({
+  targetIdx: index("pal_target_idx").on(table.targetType, table.targetId),
+  performedByIdx: index("pal_performed_by_idx").on(table.performedBy),
+  performedAtIdx: index("pal_performed_at_idx").on(table.performedAt),
+}));
\ No newline at end of file
diff --git a/db/schema/rfqLast.ts b/db/schema/rfqLast.ts
index b58341c5..21d94b39 100644
--- a/db/schema/rfqLast.ts
+++ b/db/schema/rfqLast.ts
@@ -260,6 +260,11 @@ export const rfqPrItems = pgTable(
         grossWeight: numeric("gross_weight", { precision: 12, scale: 2 })
             .$type<number>()
             .default(1),
+        // 구매 요구사항: 소수점 3자리로 변경 요청.
+        // 해당 스키마 적용 시 drop prItemsLastView 후 재생성 필요.
+        // grossWeight: numeric("gross_weight", { precision: 12, scale: 3 })
+        //     .$type<number>()
+        //     .default(1),
         gwUom: varchar("gw_uom", { length: 50 }), // 단위
 
         specNo: varchar("spec_no", { length: 255 }),
diff --git a/db/schema/users.ts b/db/schema/users.ts
index f01db8db..1d963228 100644
--- a/db/schema/users.ts
+++ b/db/schema/users.ts
@@ -229,12 +229,12 @@ export const otps = pgTable('otps', {
   otpExpires: timestamp('otp_expires').notNull(), // null 불가능
 });
 
-export const permissions = pgTable("permissions", {
-  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
-  permissionKey: text("permission_key").notNull(),
-  description: text("description"),
-  createdAt: timestamp("created_at").default(sql`now()`),
-});
+// export const permissions = pgTable("permissions", {
+//   id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+//   permissionKey: text("permission_key").notNull(),
+//   description: text("description"),
+//   createdAt: timestamp("created_at").default(sql`now()`),
+// });
 
 export const roles = pgTable("roles", {
   id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
@@ -246,19 +246,19 @@ export const roles = pgTable("roles", {
   createdAt: timestamp("created_at").default(sql`now()`),
 });
 
-export const rolePermissions = pgTable("role_permissions", {
-  roleId: integer("role_id")
-    .references(() => roles.id, { onDelete: "cascade" })
-    .notNull(),
-  permissionId: integer("permission_id")
-    .references(() => permissions.id, { onDelete: "cascade" })
-    .notNull(),
-}, (table) => {
-  return [{
-    pk: primaryKey({ columns: [table.roleId, table.permissionId] }),
-    pkWithCustomName: primaryKey({ name: 'rolePermissions_pk', columns: [table.roleId, table.permissionId] }),
-  }];
-});
+// export const rolePermissions = pgTable("role_permissions", {
+//   roleId: integer("role_id")
+//     .references(() => roles.id, { onDelete: "cascade" })
+//     .notNull(),
+//   permissionId: integer("permission_id")
+//     .references(() => permissions.id, { onDelete: "cascade" })
+//     .notNull(),
+// }, (table) => {
+//   return [{
+//     pk: primaryKey({ columns: [table.roleId, table.permissionId] }),
+//     pkWithCustomName: primaryKey({ name: 'rolePermissions_pk', columns: [table.roleId, table.permissionId] }),
+//   }];
+// });
 
 export const userRoles = pgTable("user_roles", {
   userId: integer("user_id")
@@ -360,4 +360,4 @@ export const roleView = pgView("role_view").as((qb) => {
 
 export type UserView = typeof userView.$inferSelect;
 export type RoleView = typeof roleView.$inferSelect;
-export type RolePermission = typeof rolePermissions.$inferSelect;
\ No newline at end of file
+// export type RolePermission = typeof rolePermissions.$inferSelect;
\ No newline at end of file