출석 모니터링 시스템 - 백엔드 (데이터 수집)

OpenEG 스마트 출석 수집 시스템 - egatten 프론트엔드에 데이터를 제공하는 백엔드 API

📋 프로젝트 개요

교육 기관의 출석 데이터를 자동으로 수집, 처리, 저장하고 REST API를 통해 실시간으로 제공하는 백엔드 시스템입니다.

시스템 역할

데이터 수집: Google Sheets/Drive에서 출석 데이터 자동 수집
데이터 저장: Redis 캐시 + MariaDB 영구 저장
API 제공: REST API를 통해 프론트엔드(egatten)에 데이터 제공
자동화: 5분마다 자동 수집, 주간 마이그레이션

🎯 해결한 문제

문제

수동 출석 관리의 비효율성: Google Sheets와 Excel 파일에 분산된 출석 데이터를 수동으로 집계
다양한 형식의 출석 시트: 기수별로 다른 Excel/Google Sheets 형식 (Type 1, 2, 4)
데이터 접근성 부족: 실시간 조회 및 통계 생성 시스템 부재
인증 관리의 복잡성: Kubernetes 환경에서 Google OAuth2 토큰 관리

솔루션

스마트 형식 감지: 3가지 Excel/Sheets 형식을 자동으로 감지하여 파싱
중앙화된 API: REST API를 통한 통합 데이터 접근
DB 기반 토큰 관리: Pod 재시작 시에도 DB에서 토큰 자동 로드
백그라운드 자동화: 스케줄러를 통한 무인 데이터 수집

🛠 기술 스택

Backend

Framework: Flask 3.0+ (REST API 서버)
Language: Python 3.11
Scheduler: schedule (5분 간격 자동 수집)

Database

Cache: Redis 4.0+ (TTL: 30분)
Storage: MariaDB 8.0+ (영구 저장)

External APIs

Google Sheets API: 출석 시트 데이터 읽기
Google Drive API: Excel 파일 다운로드
OAuth2: 인증 관리 (DB 기반 토큰 저장)

Infrastructure

Container: Docker
Orchestration: Kubernetes
CI/CD: GitHub Actions
Monitoring: Slack Webhook (주간 알림)

Data Processing

pandas: 데이터 처리 및 분석
openpyxl: Excel 파일 처리
python-dateutil: 유연한 날짜 파싱

💡 주요 구현 내용

1. 스마트 파일 감지 시스템

# Google Sheets ID, Drive 파일 ID 자동 감지
if is_google_sheets_id(sheet_id):
    # Sheets API로 직접 접근
elif is_google_drive_id(sheet_id):
    # Drive API로 Excel 다운로드

지원 형식:

Google Sheets (직접 API 호출)
Google Drive Excel 파일 (자동 다운로드)

2. 형식 자동 감지 및 파싱

3가지 Excel/Google Sheets 형식을 자동으로 감지:

형식	특징	감지 조건
Type 1	날짜 세로 배치	”이름” 레이블 + 세로 날짜
Type 2	날짜 가로 배치	”번호”, “교육생”, “통계” + “출결” 패턴
Type 4	통계 포함	Type 2 유사 + “출결” 패턴 없음

def detect_excel_format(df):
    # 헤더 분석으로 형식 자동 감지
    if has_vertical_dates(df):
        return EXCEL_FORMAT_TYPE1
    elif has_horizontal_dates_with_attendance(df):
        return EXCEL_FORMAT_TYPE2
    else:
        return EXCEL_FORMAT_TYPE4

3. 유연한 날짜 파싱

다양한 날짜 형식 지원:

1/20, 01/20 (M/D)
1월20일 (M월D일)
1/20(월) (M/D(요일))
2025-01-20 (YYYY-MM-DD)
Excel 시리얼 날짜 (숫자)

특징: 연도가 없는 경우 작년/올해/내년 중 오늘과 가장 가까운 날짜 선택

4. OAuth2 DB 토큰 관리

토큰 로드 우선순위:
1. MariaDB 조회 (최우선)
   ↓ (없음)
2. 로컬 파일 조회
   ↓ (없음)
3. 새 인증 (GUI 환경만)

장점:

Kubernetes Pod 재시작 시에도 재인증 불필요
토큰 갱신 시 자동으로 DB + 로컬 파일 동기화
Headless 환경 지원

5. Redis 캐싱 전략

# 파이프라인을 통한 원자적 업데이트
with redis_client.pipeline() as pipe:
    pipe.delete(redis_key)
    pipe.hset(redis_key, mapping=payload)
    pipe.expire(redis_key, TTL_SECONDS)  # 30분
    pipe.execute()

특징:

원자적 업데이트로 데이터 일관성 보장
TTL 30분으로 자동 갱신

6. 백그라운드 스케줄러

# 5분마다 자동 실행
def run_scheduler_job():
    # 1. 모든 활성 기수 데이터 수집
    # 2. Redis 캐시 업데이트
    # 3. 학생 정보 동기화
    # 4. 주간 마이그레이션 체크 (일요일)

7. 주간 마이그레이션

# 매주 일요일 자동 실행
def check_and_run_weekly_migration():
    if is_sunday() and not_run_this_week():
        # 지난주(월~금) 데이터 수집
        # MariaDB에 일괄 저장
        # Slack 알림 발송

🔧 시스템 아키텍처

[Google Sheets/Drive]
        ↓
    [OAuth2 인증]
        ↓
  [데이터 수집 엔진]
   (형식 자동 감지)
        ↓
   ┌─────┴─────┐
   ↓           ↓
[Redis]    [MariaDB]
(캐시)     (영구 저장)
   ↓           ↓
   └─────┬─────┘
         ↓
   [REST API]
         ↓
   [egatten 프론트엔드]

📊 주요 API 엔드포인트

데이터 조회

GET /api/classes - 활성 기수 목록
GET /api/attendance/{sk_id} - 오늘 출결 (Redis)
GET /api/attendance/{sk_id}/period - 기간별 출결
GET /api/attendance/{sk_id}/stats - 출결 통계

스케줄러 관리

POST /api/scheduler/trigger - 수동 실행
GET /api/scheduler/status - 상태 조회

헬스체크

GET /api/health - 서버 상태 (Redis, DB, Google API)

📈 성과

자동화 효과

수동 집계 시간: 기수당 30분 → 0분 (완전 자동화)
실시간 조회: Redis 캐시로 평균 응답 시간 < 100ms
데이터 정확도: 형식 자동 감지로 파싱 오류 95% 감소

운영 안정성

가동률: 99.9% (Kubernetes 기반)
무인 운영: 5분 간격 자동 수집, 주간 마이그레이션
토큰 관리: DB 기반으로 Pod 재시작에도 안정적

데이터 처리

처리 기수: 현재 활성 기수 자동 감지
형식 지원: 3가지 Excel/Sheets 형식 자동 파싱
데이터 보관: 주간 단위 MariaDB 영구 저장

🔧 기술적 도전 과제

1. 다양한 Excel 형식 처리

문제: 기수별로 서로 다른 3가지 Excel/Sheets 형식 사용 해결:

헤더 분석 기반 자동 감지 알고리즘 구현
각 형식별 전용 파서 개발
pandas를 활용한 유연한 데이터 처리

2. Kubernetes 환경에서 OAuth2 토큰 관리

문제: Pod 재시작 시마다 토큰 재인증 필요 (Headless 환경) 해결:

MariaDB에 토큰 저장 테이블 생성
토큰 로드 우선순위: DB → 로컬 파일 → 새 인증
토큰 갱신 시 자동 DB 동기화

3. 유연한 날짜 파싱

문제: 다양한 날짜 형식 (1/20, 1월20일, 2025-01-20 등) 해결:

정규표현식 기반 패턴 매칭
연도 없는 경우 스마트 연도 추론
Excel 시리얼 날짜 자동 변환

4. 실시간 성능 vs 데이터 신선도

문제: 빠른 응답 vs 최신 데이터 딜레마 해결:

Redis 캐시 (TTL 30분) + 5분 간격 자동 갱신
/api/scheduler/trigger로 수동 즉시 갱신 지원
MariaDB 영구 저장으로 과거 데이터 보존

🗄️ 데이터베이스 설계

Redis 구조

키: attendance:{sk_id}:{today}
값: Hash {
  "학생이름": JSON(출결, 사유, 비고, 서류)
}
TTL: 1800초 (30분)

MariaDB 테이블

openeg_student_attendance

출석 데이터 영구 저장
인덱스: (sk_id, date), (user_id, date)
UNIQUE: (user_id, sk_id, date)

google_oauth_tokens

OAuth2 토큰 저장
토큰 갱신 횟수, 상태 추적
자동 갱신 시 업데이트

openeg_weekly_migration_log

주간 마이그레이션 실행 로그
성공/실패, 처리 레코드 수 기록

🚀 배포 및 운영

Docker 컨테이너화

FROM python:3.11-slim
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY . /app
CMD ["python", "st_status_api.py"]

Kubernetes 배포

Deployment: Flask API 서버
ConfigMap: 환경 변수 관리
Secret: DB 비밀번호, API 토큰
Service: ClusterIP (내부 통신)

환경 변수 관리

# Redis (내부망)
REDIS_HOST=***.***.***.**
REDIS_PORT=6379
REDIS_PASSWORD=***
 
# MariaDB
YOS_DB_HOST=***
YOS_DB_NAME=openeg
YOS_DB_USER=***
 
# API
API_TOKEN=***
SCHEDULER_INTERVAL=5
TTL_SECONDS=1800

📊 모니터링 및 알림

Slack 통합

주간 마이그레이션 완료: 자동 알림
토큰 갱신 실패: 임계값 초과 시 알림
형식: Webhook을 통한 메시지 발송

헬스체크

GET /api/health
{
  "redis": "connected",
  "database": "connected",
  "google_api": "available",
  "scheduler": {
    "enabled": true,
    "last_run": "2025-01-21T10:25:00+09:00",
    "next_run": "2025-01-21T10:30:00+09:00"
  }
}

🔗 관련 프로젝트

메인 시스템

출석 모니터링 시스템 - 전체 아키텍처 및 프론트엔드

연결 방식

본 백엔드 API → REST API 제공
egatten 프론트엔드 → API 호출하여 데이터 표시

📝 배운 점

기술적 학습

유연한 데이터 파싱
- pandas를 활용한 다양한 Excel 형식 처리
- 정규표현식 기반 스마트 날짜 파싱
- 헤더 분석을 통한 형식 자동 감지
OAuth2 토큰 관리
- DB 기반 토큰 영구 저장 전략
- Headless 환경에서의 인증 해결
- 토큰 갱신 자동화 및 모니터링
백그라운드 스케줄링
- Flask와 schedule 라이브러리 통합
- Thread 기반 백그라운드 작업
- 주간 마이그레이션 조건 체크 로직
Redis 활용
- 파이프라인을 통한 원자적 업데이트
- TTL 관리 전략 (실시간 vs 데이터 신선도)
- Hash 구조를 통한 효율적 데이터 저장

시스템 설계 학습

레이어 분리: 데이터 수집 / 저장 / API 제공 계층 명확히 분리
캐싱 전략: Redis + MariaDB 2-tier 구조로 성능과 안정성 확보
자동화 우선: 수동 작업을 최소화하는 스케줄러 설계
장애 대응: 토큰 갱신 실패 모니터링, Slack 알림 자동화

🔮 향후 계획

단기 (1-2개월)

Prometheus 메트릭 추가 (API 요청 수, 처리 시간)
비동기 처리 도입 (asyncio로 여러 시트 병렬 수집)
에러 재시도 로직 강화

중기 (3-6개월)

변경 감지 최적화 (Google Drive API modifiedTime 활용)
출석 데이터 분석 API 추가 (트렌드, 예측)
Grafana 대시보드 구축

장기

실시간 알림 (지각/결석 발생 시 즉시 알림)
다른 교육 시스템과 연동 확장

프로젝트 시작: 2024.08 현재 상태: 운영중 (Kubernetes 배포) 최종 업데이트: 2025-01-21

Quartz 4

Explorer

출석 모니터링 시스템 - 백엔드 (데이터 수집)

출석 모니터링 시스템 - 백엔드 (데이터 수집)

📋 프로젝트 개요

시스템 역할

🎯 해결한 문제

문제

솔루션

🛠 기술 스택

Backend

Database

External APIs

Infrastructure

Data Processing

💡 주요 구현 내용

1. 스마트 파일 감지 시스템

2. 형식 자동 감지 및 파싱

3. 유연한 날짜 파싱

4. OAuth2 DB 토큰 관리

5. Redis 캐싱 전략

6. 백그라운드 스케줄러

7. 주간 마이그레이션

🔧 시스템 아키텍처

📊 주요 API 엔드포인트

데이터 조회

스케줄러 관리

헬스체크

📈 성과

자동화 효과

운영 안정성

데이터 처리

🔧 기술적 도전 과제

1. 다양한 Excel 형식 처리

2. Kubernetes 환경에서 OAuth2 토큰 관리

3. 유연한 날짜 파싱

4. 실시간 성능 vs 데이터 신선도

🗄️ 데이터베이스 설계

Redis 구조

MariaDB 테이블

🚀 배포 및 운영

Docker 컨테이너화

Kubernetes 배포

환경 변수 관리

📊 모니터링 및 알림

Slack 통합

헬스체크

🔗 관련 프로젝트

메인 시스템

연결 방식

📝 배운 점

기술적 학습

시스템 설계 학습

🔮 향후 계획

단기 (1-2개월)

중기 (3-6개월)

장기

Graph View

Table of Contents