1 files changed, 91 insertions, 0 deletions
diff --git a/db/schema/integration.ts b/db/schema/integration.ts
new file mode 100644
index 00000000..02e5b486
--- /dev/null
+++ b/db/schema/integration.ts
@@ -0,0 +1,91 @@
+import {
+ pgTable, serial, integer, varchar, text, jsonb, timestamp,
+ pgEnum, index
+} from "drizzle-orm/pg-core";
+import { users } from "./users";
+
+/* ---------- ① ENUM 정의 ---------- */
+export const integrationTypeEnum = pgEnum("integration_type", [
+ "rest_api",       // REST/JSON
+ "soap",           // SOAP/XML
+ "db_to_db"        // DB Link·ETL·FDW 등
+]);
+
+export const integrationStatusEnum = pgEnum("integration_status", [
+ "active",
+ "inactive",
+ "deprecated"
+]);
+
+export const integrationChangeEnum = pgEnum("integration_change_type", [
+ "create",
+ "update",
+ "status_change",
+ "delete"
+]);
+
+/* ---------- ② 통합 리스트 ---------- */
+export const integrations = pgTable(
+ "integrations",
+ {
+   id:        serial("id").primaryKey(),
+   /** 업무식별용 코드('INT_OPS_001') — URL·로그 등 여러 곳에서 쓰기 좋음 */
+   code:      varchar("code", { length: 50 }).unique().notNull(),
+   name:      varchar("name", { length: 255 }).notNull(),
+   type:      integrationTypeEnum("type").notNull(),
+   description: text("description"),
+   /** 양쪽 시스템 식별자(ERP, WMS 등) */
+   sourceSystem: varchar("source_system", { length: 100 }).notNull(),
+   targetSystem: varchar("target_system", { length: 100 }).notNull(),
+   status:    integrationStatusEnum("status").default("active").notNull(),
+   /** 각 통합별 개별 설정(JSON)—엔드포인트·쿼리·스케줄 등 */
+   metadata:  jsonb("metadata"),
+   /* 생성·수정 메타 */
+   createdBy: integer("created_by")
+                .references(() => users.id, { onDelete: "set null" }),
+   updatedBy: integer("updated_by")
+                .references(() => users.id, { onDelete: "set null" }),
+       createdAt: timestamp("created_at").defaultNow().notNull(),
+   updatedAt: timestamp("updated_at").defaultNow().notNull()
+ },
+ (t) => ({
+   /* 조회용 보조 인덱스 */
+   idxTypeStatus: index("idx_integrations_type_status")
+                    .on(t.type, t.status)
+ })
+);
+
+/* ---------- ③ 이력 테이블 ---------- */
+export const integrationHistory = pgTable(
+ "integration_history",
+ {
+   id:           serial("id").primaryKey(),
+   integrationId: integer("integration_id")
+                    .references(() => integrations.id, { onDelete: "cascade" })
+                    .notNull(),
+   changeType:   integrationChangeEnum("change_type").notNull(),
+   /*
+    * 변경 전·후 diff 또는 전체 스냅샷.
+    * { before: {...}, after: {...} } 구조로 저장해 두면
+    * UI에서 쉽게 '줄바꿈 diff' 로 표시 가능
+    */
+   diff:         jsonb("diff").notNull(),
+   changedBy:    integer("changed_by")
+                   .references(() => users.id, { onDelete: "set null" }),
+   changedAt:    timestamp("changed_at").defaultNow().notNull(),
+   /* 필요 시 다중 변경 그룹핑용 트랜잭션-ID */
+   txId:         varchar("tx_id", { length: 50 })
+ },
+ (t) => ({
+   idxIntegration: index("idx_history_integration")
+                     .on(t.integrationId),
+   idxChangedAt:   index("idx_history_changed_at")
+                     .on(t.changedAt)
+ })
+);
+
+// 타입 정의
+export type Integration = typeof integrations.$inferSelect;
+export type NewIntegration = typeof integrations.$inferInsert;
+export type IntegrationHistory = typeof integrationHistory.$inferSelect;
+export type NewIntegrationHistory = typeof integrationHistory.$inferInsert; 
+\ No newline at end of file