ERP-node/테이블_변경_이력_로그_시스템_구현_계획서.md

# 테이블 변경 이력 로그 시스템 구현 계획서

## 1. 개요

테이블 생성 시 해당 테이블의 변경 이력을 자동으로 기록하는 로그 테이블 생성 기능을 추가합니다.
사용자가 테이블을 생성할 때 로그 테이블 생성 여부를 선택할 수 있으며, 선택 시 자동으로 로그 테이블과 트리거가 생성됩니다.

## 2. 핵심 기능

### 2.1 로그 테이블 생성 옵션

- 테이블 생성 폼에 "변경 이력 로그 테이블 생성" 체크박스 추가
- 체크 시 `{원본테이블명}_log` 형식의 로그 테이블 자동 생성

### 2.2 로그 테이블 스키마 구조

```sql
CREATE TABLE {table_name}_log (
  log_id SERIAL PRIMARY KEY,                    -- 로그 고유 ID
  operation_type VARCHAR(10) NOT NULL,          -- INSERT, UPDATE, DELETE
  original_id {원본PK타입},                      -- 원본 테이블의 PK 값
  changed_column VARCHAR(100),                  -- 변경된 컬럼명 (UPDATE 시)
  old_value TEXT,                               -- 변경 전 값
  new_value TEXT,                               -- 변경 후 값
  changed_by VARCHAR(50),                       -- 변경한 사용자 ID
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 변경 시각
  ip_address VARCHAR(50),                       -- 변경 요청 IP
  user_agent TEXT,                              -- 변경 요청 User-Agent
  full_row_before JSONB,                        -- 변경 전 전체 행 (JSON)
  full_row_after JSONB                          -- 변경 후 전체 행 (JSON)
);

CREATE INDEX idx_{table_name}_log_original_id ON {table_name}_log(original_id);
CREATE INDEX idx_{table_name}_log_changed_at ON {table_name}_log(changed_at);
CREATE INDEX idx_{table_name}_log_operation ON {table_name}_log(operation_type);
```

### 2.3 트리거 함수 생성

```sql
CREATE OR REPLACE FUNCTION {table_name}_log_trigger_func()
RETURNS TRIGGER AS $$
DECLARE
  v_column_name TEXT;
  v_old_value TEXT;
  v_new_value TEXT;
  v_user_id VARCHAR(50);
  v_ip_address VARCHAR(50);
BEGIN
  -- 세션 변수에서 사용자 정보 가져오기
  v_user_id := current_setting('app.user_id', TRUE);
  v_ip_address := current_setting('app.ip_address', TRUE);

  IF (TG_OP = 'INSERT') THEN
    INSERT INTO {table_name}_log (
      operation_type, original_id, changed_by, ip_address,
      full_row_after
    ) VALUES (
      'INSERT', NEW.{pk_column}, v_user_id, v_ip_address,
      row_to_json(NEW)::jsonb
    );
    RETURN NEW;

  ELSIF (TG_OP = 'UPDATE') THEN
    -- 각 컬럼별로 변경사항 기록
    FOR v_column_name IN
      SELECT column_name
      FROM information_schema.columns
      WHERE table_name = TG_TABLE_NAME
    LOOP
      EXECUTE format('SELECT ($1).%I::TEXT, ($2).%I::TEXT',
        v_column_name, v_column_name)
      INTO v_old_value, v_new_value
      USING OLD, NEW;

      IF v_old_value IS DISTINCT FROM v_new_value THEN
        INSERT INTO {table_name}_log (
          operation_type, original_id, changed_column,
          old_value, new_value, changed_by, ip_address,
          full_row_before, full_row_after
        ) VALUES (
          'UPDATE', NEW.{pk_column}, v_column_name,
          v_old_value, v_new_value, v_user_id, v_ip_address,
          row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb
        );
      END IF;
    END LOOP;
    RETURN NEW;

  ELSIF (TG_OP = 'DELETE') THEN
    INSERT INTO {table_name}_log (
      operation_type, original_id, changed_by, ip_address,
      full_row_before
    ) VALUES (
      'DELETE', OLD.{pk_column}, v_user_id, v_ip_address,
      row_to_json(OLD)::jsonb
    );
    RETURN OLD;
  END IF;

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
```

### 2.4 트리거 생성

```sql
CREATE TRIGGER {table_name}_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON {table_name}
FOR EACH ROW EXECUTE FUNCTION {table_name}_log_trigger_func();
```

## 3. 데이터베이스 스키마 변경

### 3.1 table_type_mng 테이블 수정

```sql
ALTER TABLE table_type_mng
ADD COLUMN use_log_table VARCHAR(1) DEFAULT 'N';

COMMENT ON COLUMN table_type_mng.use_log_table IS '변경 이력 로그 테이블 사용 여부 (Y/N)';
```

### 3.2 새로운 관리 테이블 추가

```sql
CREATE TABLE table_log_config (
  config_id SERIAL PRIMARY KEY,
  original_table_name VARCHAR(100) NOT NULL,
  log_table_name VARCHAR(100) NOT NULL,
  trigger_name VARCHAR(100) NOT NULL,
  trigger_function_name VARCHAR(100) NOT NULL,
  is_active VARCHAR(1) DEFAULT 'Y',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by VARCHAR(50),
  UNIQUE(original_table_name)
);

COMMENT ON TABLE table_log_config IS '테이블 로그 설정 관리';
COMMENT ON COLUMN table_log_config.original_table_name IS '원본 테이블명';
COMMENT ON COLUMN table_log_config.log_table_name IS '로그 테이블명';
COMMENT ON COLUMN table_log_config.trigger_name IS '트리거명';
COMMENT ON COLUMN table_log_config.trigger_function_name IS '트리거 함수명';
COMMENT ON COLUMN table_log_config.is_active IS '활성 상태 (Y/N)';
```

## 4. 백엔드 구현

### 4.1 Service Layer 수정

**파일**: `backend-node/src/services/admin/table-type-mng.service.ts`

#### 4.1.1 로그 테이블 생성 로직

```typescript
/**
 * 로그 테이블 생성
 */
private async createLogTable(
  tableName: string,
  columns: any[],
  connectionId?: number,
  userId?: string
): Promise<void> {
  const logTableName = `${tableName}_log`;
  const triggerFuncName = `${tableName}_log_trigger_func`;
  const triggerName = `${tableName}_audit_trigger`;

  // PK 컬럼 찾기
  const pkColumn = columns.find(col => col.isPrimaryKey);
  if (!pkColumn) {
    throw new Error('PK 컬럼이 없으면 로그 테이블을 생성할 수 없습니다.');
  }

  // 로그 테이블 DDL 생성
  const logTableDDL = this.generateLogTableDDL(
    logTableName,
    pkColumn.COLUMN_NAME,
    pkColumn.DATA_TYPE
  );

  // 트리거 함수 DDL 생성
  const triggerFuncDDL = this.generateTriggerFunctionDDL(
    triggerFuncName,
    logTableName,
    tableName,
    pkColumn.COLUMN_NAME
  );

  // 트리거 DDL 생성
  const triggerDDL = this.generateTriggerDDL(
    triggerName,
    tableName,
    triggerFuncName
  );

  try {
    // 1. 로그 테이블 생성
    await this.executeDDL(logTableDDL, connectionId);

    // 2. 트리거 함수 생성
    await this.executeDDL(triggerFuncDDL, connectionId);

    // 3. 트리거 생성
    await this.executeDDL(triggerDDL, connectionId);

    // 4. 로그 설정 저장
    await this.saveLogConfig({
      originalTableName: tableName,
      logTableName,
      triggerName,
      triggerFunctionName: triggerFuncName,
      createdBy: userId
    });

    console.log(`로그 테이블 생성 완료: ${logTableName}`);
  } catch (error) {
    console.error('로그 테이블 생성 실패:', error);
    throw error;
  }
}

/**
 * 로그 테이블 DDL 생성
 */
private generateLogTableDDL(
  logTableName: string,
  pkColumnName: string,
  pkDataType: string
): string {
  return `
    CREATE TABLE ${logTableName} (
      log_id SERIAL PRIMARY KEY,
      operation_type VARCHAR(10) NOT NULL,
      original_id ${pkDataType},
      changed_column VARCHAR(100),
      old_value TEXT,
      new_value TEXT,
      changed_by VARCHAR(50),
      changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      ip_address VARCHAR(50),
      user_agent TEXT,
      full_row_before JSONB,
      full_row_after JSONB
    );

    CREATE INDEX idx_${logTableName}_original_id ON ${logTableName}(original_id);
    CREATE INDEX idx_${logTableName}_changed_at ON ${logTableName}(changed_at);
    CREATE INDEX idx_${logTableName}_operation ON ${logTableName}(operation_type);

    COMMENT ON TABLE ${logTableName} IS '${logTableName.replace('_log', '')} 테이블 변경 이력';
    COMMENT ON COLUMN ${logTableName}.operation_type IS '작업 유형 (INSERT/UPDATE/DELETE)';
    COMMENT ON COLUMN ${logTableName}.original_id IS '원본 테이블 PK 값';
    COMMENT ON COLUMN ${logTableName}.changed_column IS '변경된 컬럼명';
    COMMENT ON COLUMN ${logTableName}.old_value IS '변경 전 값';
    COMMENT ON COLUMN ${logTableName}.new_value IS '변경 후 값';
  `;
}

/**
 * 트리거 함수 DDL 생성
 */
private generateTriggerFunctionDDL(
  funcName: string,
  logTableName: string,
  originalTableName: string,
  pkColumnName: string
): string {
  return `
    CREATE OR REPLACE FUNCTION ${funcName}()
    RETURNS TRIGGER AS $$
    DECLARE
      v_column_name TEXT;
      v_old_value TEXT;
      v_new_value TEXT;
      v_user_id VARCHAR(50);
      v_ip_address VARCHAR(50);
    BEGIN
      v_user_id := current_setting('app.user_id', TRUE);
      v_ip_address := current_setting('app.ip_address', TRUE);

      IF (TG_OP = 'INSERT') THEN
        INSERT INTO ${logTableName} (
          operation_type, original_id, changed_by, ip_address, full_row_after
        ) VALUES (
          'INSERT', NEW.${pkColumnName}, v_user_id, v_ip_address, row_to_json(NEW)::jsonb
        );
        RETURN NEW;

      ELSIF (TG_OP = 'UPDATE') THEN
        FOR v_column_name IN
          SELECT column_name
          FROM information_schema.columns
          WHERE table_name = '${originalTableName}'
        LOOP
          EXECUTE format('SELECT ($1).%I::TEXT, ($2).%I::TEXT', v_column_name, v_column_name)
          INTO v_old_value, v_new_value
          USING OLD, NEW;

          IF v_old_value IS DISTINCT FROM v_new_value THEN
            INSERT INTO ${logTableName} (
              operation_type, original_id, changed_column, old_value, new_value,
              changed_by, ip_address, full_row_before, full_row_after
            ) VALUES (
              'UPDATE', NEW.${pkColumnName}, v_column_name, v_old_value, v_new_value,
              v_user_id, v_ip_address, row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb
            );
          END IF;
        END LOOP;
        RETURN NEW;

      ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO ${logTableName} (
          operation_type, original_id, changed_by, ip_address, full_row_before
        ) VALUES (
          'DELETE', OLD.${pkColumnName}, v_user_id, v_ip_address, row_to_json(OLD)::jsonb
        );
        RETURN OLD;
      END IF;

      RETURN NULL;
    END;
    $$ LANGUAGE plpgsql;
  `;
}

/**
 * 트리거 DDL 생성
 */
private generateTriggerDDL(
  triggerName: string,
  tableName: string,
  funcName: string
): string {
  return `
    CREATE TRIGGER ${triggerName}
    AFTER INSERT OR UPDATE OR DELETE ON ${tableName}
    FOR EACH ROW EXECUTE FUNCTION ${funcName}();
  `;
}

/**
 * 로그 설정 저장
 */
private async saveLogConfig(config: {
  originalTableName: string;
  logTableName: string;
  triggerName: string;
  triggerFunctionName: string;
  createdBy?: string;
}): Promise<void> {
  const query = `
    INSERT INTO table_log_config (
      original_table_name, log_table_name, trigger_name,
      trigger_function_name, created_by
    ) VALUES ($1, $2, $3, $4, $5)
  `;

  await this.executeQuery(query, [
    config.originalTableName,
    config.logTableName,
    config.triggerName,
    config.triggerFunctionName,
    config.createdBy
  ]);
}
```

#### 4.1.2 테이블 생성 메서드 수정

```typescript
async createTable(params: {
  tableName: string;
  columns: any[];
  useLogTable?: boolean;  // 추가
  connectionId?: number;
  userId?: string;
}): Promise<void> {
  const { tableName, columns, useLogTable, connectionId, userId } = params;

  // 1. 원본 테이블 생성
  const ddl = this.generateCreateTableDDL(tableName, columns);
  await this.executeDDL(ddl, connectionId);

  // 2. 로그 테이블 생성 (옵션)
  if (useLogTable === true) {
    await this.createLogTable(tableName, columns, connectionId, userId);
  }

  // 3. 메타데이터 저장
  await this.saveTableMetadata({
    tableName,
    columns,
    useLogTable: useLogTable ? 'Y' : 'N',
    connectionId,
    userId
  });
}
```

### 4.2 Controller Layer 수정

**파일**: `backend-node/src/controllers/admin/table-type-mng.controller.ts`

```typescript
/**
 * 테이블 생성
 */
async createTable(req: Request, res: Response): Promise<void> {
  try {
    const { tableName, columns, useLogTable, connectionId } = req.body;
    const userId = req.user?.userId;

    await this.tableTypeMngService.createTable({
      tableName,
      columns,
      useLogTable: useLogTable === 'Y' || useLogTable === true,
      connectionId,
      userId
    });

    res.json({
      success: true,
      message: useLogTable
        ? '테이블 및 로그 테이블이 생성되었습니다.'
        : '테이블이 생성되었습니다.'
    });
  } catch (error) {
    console.error('테이블 생성 오류:', error);
    res.status(500).json({
      success: false,
      message: '테이블 생성 중 오류가 발생했습니다.'
    });
  }
}
```

### 4.3 세션 변수 설정 미들웨어

**파일**: `backend-node/src/middleware/db-session.middleware.ts`

```typescript
import { Request, Response, NextFunction } from "express";

/**
 * DB 세션 변수 설정 미들웨어
 * 트리거에서 사용할 사용자 정보를 세션 변수에 설정
 */
export const setDBSessionVariables = async (
  req: Request,
  res: Response,
  next: NextFunction
): Promise<void> => {
  try {
    const userId = req.user?.userId || "system";
    const ipAddress = req.ip || req.socket.remoteAddress || "unknown";

    // PostgreSQL 세션 변수 설정
    const queries = [
      `SET app.user_id = '${userId}'`,
      `SET app.ip_address = '${ipAddress}'`,
    ];

    // 각 DB 연결에 세션 변수 설정
    // (실제 구현은 DB 연결 풀 관리 방식에 따라 다름)

    next();
  } catch (error) {
    console.error("DB 세션 변수 설정 오류:", error);
    next(error);
  }
};
```

## 5. 프론트엔드 구현

### 5.1 테이블 생성 폼 수정

**파일**: `frontend/src/app/admin/tableMng/components/TableCreateForm.tsx`

```typescript
const TableCreateForm = () => {
  const [useLogTable, setUseLogTable] = useState<boolean>(false);

  return (
    <div className="table-create-form">
      {/* 기존 폼 필드들 */}

      {/* 로그 테이블 옵션 추가 */}
      <div className="form-group">
        <label className="flex items-center gap-2">
          <input
            type="checkbox"
            checked={useLogTable}
            onChange={(e) => setUseLogTable(e.target.checked)}
            className="w-4 h-4"
          />
          <span>변경 이력 로그 테이블 생성</span>
        </label>
        <p className="text-sm text-gray-600 mt-1">
          체크 시 데이터 변경 이력을 기록하는 로그 테이블이 자동으로 생성됩니다.
          (테이블명: {tableName}_log)
        </p>
      </div>

      {useLogTable && (
        <div className="bg-blue-50 p-4 rounded border border-blue-200">
          <h4 className="font-semibold mb-2">로그 테이블 정보</h4>
          <ul className="text-sm space-y-1">
            <li>• INSERT/UPDATE/DELETE 작업이 자동으로 기록됩니다</li>
            <li>• 변경 전후 값과 변경자 정보가 저장됩니다</li>
            <li>
              • 로그는 별도 테이블에 저장되어 원본 테이블 성능에 영향을
              최소화합니다
            </li>
          </ul>
        </div>
      )}
    </div>
  );
};
```

### 5.2 로그 조회 화면 추가

**파일**: `frontend/src/app/admin/tableMng/components/TableLogViewer.tsx`

```typescript
interface TableLogViewerProps {
  tableName: string;
}

const TableLogViewer: React.FC<TableLogViewerProps> = ({ tableName }) => {
  const [logs, setLogs] = useState<any[]>([]);
  const [filters, setFilters] = useState({
    operationType: "",
    startDate: "",
    endDate: "",
    changedBy: "",
  });

  const fetchLogs = async () => {
    // 로그 데이터 조회
    const response = await fetch(`/api/admin/table-log/${tableName}`, {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify(filters),
    });
    const data = await response.json();
    setLogs(data.logs);
  };

  return (
    <div className="table-log-viewer">
      <h3>변경 이력 조회: {tableName}</h3>

      {/* 필터 */}
      <div className="filters">
        <select
          value={filters.operationType}
          onChange={(e) =>
            setFilters({ ...filters, operationType: e.target.value })
          }
        >
          <option value="">전체</option>
          <option value="INSERT">추가</option>
          <option value="UPDATE">수정</option>
          <option value="DELETE">삭제</option>
        </select>

        {/* 날짜 필터, 사용자 필터 등 */}
      </div>

      {/* 로그 테이블 */}
      <table className="log-table">
        <thead>
          <tr>
            <th>작업유형</th>
            <th>원본ID</th>
            <th>변경컬럼</th>
            <th>변경전</th>
            <th>변경후</th>
            <th>변경자</th>
            <th>변경시각</th>
          </tr>
        </thead>
        <tbody>
          {logs.map((log) => (
            <tr key={log.log_id}>
              <td>{log.operation_type}</td>
              <td>{log.original_id}</td>
              <td>{log.changed_column}</td>
              <td>{log.old_value}</td>
              <td>{log.new_value}</td>
              <td>{log.changed_by}</td>
              <td>{log.changed_at}</td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
};
```

## 6. API 엔드포인트

### 6.1 로그 조회 API

```
POST /api/admin/table-log/:tableName
Request Body:
{
  "operationType": "UPDATE",      // 선택: INSERT, UPDATE, DELETE
  "startDate": "2024-01-01",     // 선택
  "endDate": "2024-12-31",       // 선택
  "changedBy": "user123",        // 선택
  "originalId": 123              // 선택
}

Response:
{
  "success": true,
  "logs": [
    {
      "log_id": 1,
      "operation_type": "UPDATE",
      "original_id": "123",
      "changed_column": "user_name",
      "old_value": "홍길동",
      "new_value": "김철수",
      "changed_by": "admin",
      "changed_at": "2024-10-21T10:30:00Z",
      "ip_address": "192.168.1.100"
    }
  ]
}
```

### 6.2 로그 테이블 활성화/비활성화 API

```
POST /api/admin/table-log/:tableName/toggle
Request Body:
{
  "isActive": "Y"  // Y 또는 N
}

Response:
{
  "success": true,
  "message": "로그 기능이 활성화되었습니다."
}
```

## 7. 테스트 계획

### 7.1 단위 테스트

- [ ] 로그 테이블 DDL 생성 함수 테스트
- [ ] 트리거 함수 DDL 생성 함수 테스트
- [ ] 트리거 DDL 생성 함수 테스트
- [ ] 로그 설정 저장 함수 테스트

### 7.2 통합 테스트

- [ ] 테이블 생성 시 로그 테이블 자동 생성 테스트
- [ ] INSERT 작업 시 로그 기록 테스트
- [ ] UPDATE 작업 시 로그 기록 테스트
- [ ] DELETE 작업 시 로그 기록 테스트
- [ ] 여러 컬럼 동시 변경 시 로그 기록 테스트

### 7.3 성능 테스트

- [ ] 대량 데이터 INSERT 시 성능 영향 측정
- [ ] 대량 데이터 UPDATE 시 성능 영향 측정
- [ ] 로그 테이블 크기 증가에 따른 성능 영향 측정

## 8. 주의사항 및 제약사항

### 8.1 성능 고려사항

- 트리거는 모든 변경 작업에 대해 실행되므로 성능 영향이 있을 수 있음
- 대량 데이터 처리 시 로그 테이블 크기가 급격히 증가할 수 있음
- 로그 테이블에 적절한 인덱스 설정 필요

### 8.2 운영 고려사항

- 로그 데이터의 보관 주기 정책 수립 필요
- 오래된 로그 데이터 아카이빙 전략 필요
- 로그 테이블의 정기적인 파티셔닝 고려

### 8.3 보안 고려사항

- 로그 데이터에는 민감한 정보가 포함될 수 있으므로 접근 권한 관리 필요
- 로그 데이터 자체의 무결성 보장 필요
- 로그 데이터의 암호화 저장 고려

## 9. 향후 확장 계획

### 9.1 로그 분석 기능

- 변경 패턴 분석
- 사용자별 변경 통계
- 시간대별 변경 추이

### 9.2 로그 알림 기능

- 특정 테이블/컬럼 변경 시 알림
- 비정상적인 대량 변경 감지
- 특정 사용자의 변경 작업 모니터링

### 9.3 로그 복원 기능

- 특정 시점으로 데이터 롤백
- 변경 이력 기반 데이터 복구
- 변경 이력 시각화

## 10. 마이그레이션 가이드

### 10.1 기존 테이블에 로그 기능 추가

```typescript
// 기존 테이블에 로그 테이블 추가하는 API
POST /api/admin/table-log/:tableName/enable

// 실행 순서:
// 1. 로그 테이블 생성
// 2. 트리거 함수 생성
// 3. 트리거 생성
// 4. 로그 설정 저장
```

### 10.2 로그 기능 제거

```typescript
// 로그 기능 제거 API
POST /api/admin/table-log/:tableName/disable

// 실행 순서:
// 1. 트리거 삭제
// 2. 트리거 함수 삭제
// 3. 로그 테이블 삭제 (선택)
// 4. 로그 설정 비활성화
```

## 11. 개발 우선순위

### Phase 1: 기본 기능 (필수)

1. DB 스키마 변경 (table_type_mng, table_log_config)
2. 로그 테이블 DDL 생성 로직
3. 트리거 함수/트리거 DDL 생성 로직
4. 테이블 생성 시 로그 테이블 자동 생성

### Phase 2: UI 개발

1. 테이블 생성 폼에 로그 옵션 추가
2. 로그 조회 화면 개발
3. 로그 필터링 기능

### Phase 3: 고급 기능

1. 로그 활성화/비활성화 기능
2. 기존 테이블에 로그 추가 기능
3. 로그 데이터 아카이빙 기능

### Phase 4: 분석 및 최적화

1. 로그 분석 대시보드
2. 성능 최적화
3. 로그 데이터 파티셔닝