(김준회) Knox API - 임직원 저장 구현

2 files changed, 89 insertions, 0 deletions

diff --git a/db/schema/knox/organization.ts b/db/schema/knox/organization.ts
new file mode 100644
index 00000000..48b13254
--- /dev/null
+++ b/db/schema/knox/organization.ts
@@ -0,0 +1,59 @@
+import { pgSchema, varchar, jsonb, timestamp, index, primaryKey } from "drizzle-orm/pg-core";
+
+// Knox 전용 스키마 네임스페이스 재사용
+export const knoxSchema = pgSchema("knox");
+
+export const organization = knoxSchema.table(
+  "organization",
+  {
+    companyCode: varchar("company_code", { length: 10 }).notNull(),
+    departmentCode: varchar("department_code", { length: 50 }).notNull(),
+
+    companyName: varchar("company_name", { length: 100 }),
+    departmentLevel: varchar("department_level", { length: 10 }),
+    departmentName: varchar("department_name", { length: 255 }),
+    departmentOrder: varchar("department_order", { length: 10 }),
+
+    enCompanyName: varchar("en_company_name", { length: 100 }),
+    enDepartmentName: varchar("en_department_name", { length: 255 }),
+    enManagerTitle: varchar("en_manager_title", { length: 255 }),
+    enSubOrgCode: varchar("en_sub_org_code", { length: 50 }),
+
+    inDepartmentCode: varchar("in_department_code", { length: 50 }),
+    lowDepartmentYn: varchar("low_department_yn", { length: 2 }),
+
+    managerId: varchar("manager_id", { length: 50 }),
+    managerName: varchar("manager_name", { length: 100 }),
+    managerTitle: varchar("manager_title", { length: 255 }),
+
+    preferredLanguage: varchar("preferred_language", { length: 5 }),
+
+    subOrgCode: varchar("sub_org_code", { length: 50 }),
+    subOrgName: varchar("sub_org_name", { length: 255 }),
+
+    uprDepartmentCode: varchar("upr_department_code", { length: 50 }),
+    enUprDepartmentName: varchar("en_upr_department_name", { length: 255 }),
+    uprDepartmentName: varchar("upr_department_name", { length: 255 }),
+
+    hiddenDepartmentYn: varchar("hidden_department_yn", { length: 2 }),
+
+    corpCode: varchar("corp_code", { length: 20 }),
+    corpName: varchar("corp_name", { length: 100 }),
+    enCorpName: varchar("en_corp_name", { length: 100 }),
+
+    // 원본 JSON 전체 보관 – 변경 감지 및 추후 확장 대비
+    raw: jsonb("raw").notNull(),
+
+    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+  },
+  (table) => {
+    return {
+      companyIdx: index("knox_org_company_idx").on(table.companyCode),
+      pk: primaryKey(table.companyCode, table.departmentCode),
+    };
+  }
+);
+
+export type KnoxOrganization = typeof organization.$inferSelect;
+export type NewKnoxOrganization = typeof organization.$inferInsert;
diff --git a/db/schema/knox/titles.ts b/db/schema/knox/titles.ts
new file mode 100644
index 00000000..338ba79b
--- /dev/null
+++ b/db/schema/knox/titles.ts
@@ -0,0 +1,30 @@
+import { pgSchema, varchar, jsonb, timestamp, index, primaryKey } from "drizzle-orm/pg-core";
+
+export const knoxSchema = pgSchema("knox");
+
+export const title = knoxSchema.table(
+  "title",
+  {
+    companyCode: varchar("company_code", { length: 10 }).notNull(),
+    titleCode: varchar("title_code", { length: 20 }).notNull(),
+
+    titleName: varchar("title_name", { length: 100 }),
+    enTitleName: varchar("en_title_name", { length: 100 }),
+    sortOrder: varchar("sort_order", { length: 10 }),
+
+    // 전체 원본 JSON 데이터 저장
+    raw: jsonb("raw").notNull(),
+
+    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+  },
+  (table) => {
+    return {
+      companyIdx: index("knox_title_company_idx").on(table.companyCode),
+      pk: primaryKey(table.companyCode, table.titleCode),
+    };
+  }
+);
+
+export type KnoxTitle = typeof title.$inferSelect;
+export type NewKnoxTitle = typeof title.$inferInsert;