PNUAICMS - AI기업 산학협력 관리 통합 시스템
부산대 AI융합교육원이 협력하는 AI기업의 인턴십·취업연계·산학협력 성과를 관리하던, 여러 엑셀로 흩어진 업무를 하나의 웹 시스템으로 통합했습니다. 현재 실무에서 운영 중입니다.
개요
AI융합교육원과 협력하는 기업 정보, 학생 인턴십 매칭, 기업-졸업과제 연계, 산학협력 성과 지표가 각각 다른 엑셀 파일로 흩어져 관리되고 있었습니다. 같은 기업이 인턴십 시트와 산학협력 시트에 따로 적히고, 학생 한 명의 인턴·졸업과제 이력이 서로 다른 문서에 나뉘어, “이 기업과 얼마나 협력했나”, “이 학생은 무슨 실적이 있나”를 한눈에 보기 어려운 상태였습니다.
PNUAICMS는 이 흩어진 업무를 하나의 웹 시스템으로 통합한 관리 시스템입니다. 데이터 모델 설계부터 기능 개발, 클라우드→교내 서버 이전, 운영까지 단독으로 수행했으며, 현재 부산대학교 AI융합교육원에서 실제로 운영되고 있습니다. 담당 교수님이 기업과의 미팅·컨택 내용을 기록하고 인턴십·산학협력 진행을 추적하는 일상 업무 도구로 쓰고 있으며, 기존의 수기 엑셀 작업을 대체했습니다.
- 운영 주소: aicms.pusan.ac.kr
- 역할: 기획, 데이터 모델 설계, 기능 개발, 배포·운영 (단독)
- 스택: Next.js 14 · TypeScript · PostgreSQL · Prisma · NextAuth · Docker · Caddy
- 규모(운영 데이터): 기업 약 100곳, 학생 약 790명, 산학협력 프로젝트 450여 건, 인턴십 160여 건, 연구실 95곳
어떻게 시작됐나
시작은 한 교수님의 부탁이었습니다. AI융합교육원이 협력·연계 중인 기업들의 정보, 그리고 그 기업들과 주고받은 컨택 이력을 교수님들이 직접 엑셀로 관리하고 계셨는데, 채우고 정리하는 일이 너무 번거로워 “정보 입력을 일부라도 자동화하고, 현황을 보기 좋은 대시보드로 보고 싶다”는 요청이었습니다. 그렇게 기업 관리와 정보 자동 채우기, 대시보드부터 만들기 시작했습니다.
결과가 좋자 범위가 유기적으로 늘었습니다. 다른 교수님은 여기에 학생 이력 관리를 붙여 달라고 했고, 실장님은 산학협력·인턴십 정량실적도 같은 화면에서 보기 좋게 정리하자고 했습니다. 각 요청은 그때그때 부분적으로 전해졌지만, 뒤에 알고 보니 이 조각들은 SW중심대학 사업단 평가를 겨냥한 5년 단위 장기 계획의 일부였습니다. “우리는 이만큼 체계적으로 관리하고 있다”를 지표와 근거로 보여주고 좋은 평가를 받기 위한 것이었고, 나는 그 시작과 그것을 떠받치는 인프라를 세운 셈입니다.
통합이라는 방향 - 흩어진 시트에서 이어지는 그래프로
이 프로젝트의 핵심은 화면을 예쁘게 만드는 것이 아니라, 데이터를 잇는 것입니다. 엑셀에서는 기업·학생·실적이 각자 다른 파일의 행으로 존재했습니다. 이걸 정규화된 관계형 모델로 옮기면서, 각 축이 서로를 참조하도록 설계했습니다.
목표는 이런 순환입니다.
학생 → 졸업과제·인턴십 참여 → 기업 연계 → 채용·산학협력 성과 → 다시 학생- 기업 상세에서 그 기업과 한 산학협력 프로젝트·인턴십·컨택 이력이 한자리에 모입니다.
- 학생 상세에서 그 학생의 인턴십·졸업과제 참여 이력이 이어집니다.
- 연구실(지도교수) 기준으로 어떤 기업과 어떤 과제를 했는지 역으로 추적됩니다.
- 기업에 없던 회사가 실적 엑셀에 등장하면 원본 이름으로 보존해 두고, 나중에 그 기업을 등록하면 과거 실적이 자동으로 연결됩니다.
흩어진 시트를 한 시스템에 “모아 두는” 데 그치지 않고, 모은 데이터가 서로를 가리키게 만드는 것이 통합의 실체였습니다.
주요 기여
- 4개 엑셀로 흩어져 있던 기업·실무자·협업·컨택 데이터를 정규화 관계형 모델로 재설계하고, 기존 기록을 일괄 이관
- 학생·졸업과제·인턴십·산학성과·연구실을 서로 참조하는 축으로 잇는 통합 데이터 모델 구축
- 기업명만으로 DART·네이버 등 공개 API에서 대표자·주소·업종·매출 등 빈 칸을 자동 수집해, 엑셀 수기 입력의 번거로움을 줄임
- SheetJS·exceljs가 못 읽는 비표준 실적 엑셀을 위해 zip + OOXML을 직접 파싱하는 커스텀 리더를 구현
- 실적 재업로드 시 학생·연구실·기업은 보존하고 실적만 교체하는 멱등 적재 + 기업명 정규화 매칭
- DB 기반 3단계 RBAC(일반/관리자/슈퍼)를 설계, 매 요청마다 역할을 강제
- 낙관적 락(version)으로 다중 편집 충돌을 감지, 구글시트의 last-write-wins 문제를 해소
- 학생 실명 마스킹 등 개인정보 보호를 전제로 한 접근 통제
- 클라우드(Vercel + Supabase)에서 교내 자체 서버로 직접 이전, Docker·GHCR·GitHub Actions·Caddy HTTPS로 자동 배포 구성
시스템 구조
엑셀 업로드가 파싱을 거쳐 정규화된 DB로 들어가고, 그 DB를 여러 화면이 함께 참조하는 구조입니다.
[실적/성과 엑셀 업로드]
│ 커스텀 xlsx 리더(zip + OOXML 정규식)
▼
[파싱·정규화] 기업명 매칭 · 실적만 교체(멱등) · 실명 마스킹
▼
[PostgreSQL] 기업 ─ 협업 ─ 실무자 ─ 컨택 · 학생 ─ 졸업과제 ─ 인턴십 ─ 연구실 · 성과지표
│
├─▶ 대시보드(정량실적 현황판 · 목표 대비 달성)
├─▶ 기업/학생/실적 관리 화면(CRUD · 필터 · 엑셀 export)
└─▶ SW중심대학 성과 탭(KMAC 공식 평가지표)배포는 자체 서버에서 CI가 구운 이미지를 받아 교체합니다.
GitHub main ─push▶ GitHub Actions ─build▶ GHCR 이미지(SHA 태그)
교내 서버: deploy.sh ─pull▶ Docker(app + postgres) 교체
Caddy ─HTTPS▶ aicms.pusan.ac.kr핵심 기능 들여다보기
통합 데이터 모델
엑셀의 네 시트(기업기본정보 · 협업정보 · 실무자정보 · 컨택이력)를 1:1·1:N 관계로 정규화하고, 여기에 학생·졸업과제·인턴십·연구실·성과지표 축을 얹었습니다. 외래키와 soft delete로 관계 무결성을 지키고, 사람이 읽는 표시용 코드(C-001 등)는 화면·검색용으로 따로 채번합니다. 기업-학생을 잇는 실적(졸업과제·인턴십)은 다대다로 연결해, 한쪽에서 반대쪽을 항상 되짚을 수 있게 했습니다.
기업 정보 자동 채우기
엑셀로 기업을 관리할 때 가장 번거로운 건 칸을 하나하나 채우는 일이었습니다. 그래서 기업명만 입력하면 공개 API에서 빈 칸을 자동으로 채우는 조회를 붙였습니다. 세 소스를 순서대로 시도하고, 일부가 실패해도 나머지 결과는 유지합니다.
- DART 전자공시: 회사명을 미리 동기화해 둔 corp_code 캐시(약 11.8만 건)와 매칭해 대표자·업종·설립일·매출(범위 라벨)·본사 주소를 가져옵니다.
- 네이버 지역검색: DART가 못 채운 소재지·홈페이지를 보강합니다(지점 결과가 본사 주소를 덮지 않도록 빈 칸만).
- 공개 임금데이터: 번들 데이터로 평균·신입 연봉을 채웁니다(API 키 불필요).
주소에서 지역(부산/울산/경남/수도권)을 추정하고, API 키는 서버에만 둬 클라이언트로 나가지 않게 했습니다. 다만 부산의 AI 스타트업은 어떤 무료 데이터셋에도 없는 경우가 많아, 자동으로는 소재지·홈페이지 정도만 채워지고 나머지는 수동 입력이 필요하다는 한계도 그대로 안고 있습니다.
커스텀 xlsx 파서
정량실적 엑셀은 외부 링크와 비표준 메타데이터 때문에 SheetJS·exceljs가 워크시트를 아예 못 읽었습니다. 그래서 xlsx(zip)를 직접 풀고 시트 XML(OOXML)을 파싱하는 리더를 만들었습니다. 공유 문자열 테이블을 복원하고, 셀 참조(F13)를 열 인덱스로 변환하며, 학번처럼 과학적 표기(2.0221E8)로 저장된 큰 정수를 평문으로 되돌립니다. 정규식으로 XML을 다루는 방식이라 self-closing 빈 셀 같은 함정도 있었는데, 그 사례는 아래 트러블슈팅에 정리해 두었습니다.
멱등 적재와 기업명 매칭
실적 엑셀은 매번 최신본으로 다시 올라옵니다. 그래서 재업로드 시 실적(프로젝트·인턴십)만 삭제 후 재적재하고, 학생·연구실·기업·컨택은 보존합니다. 학생 수백-수천 행도 시간 내에 끝나도록 행마다 처리하지 않고 일괄(createMany)로 넣습니다. 실적에 적힌 기업명은 표기가 제각각이라, 공백·법인격 표기를 정규화한 키로 기존 기업과 매칭하고, 못 찾으면 원본 이름으로 보존해 두었다가 나중에 그 기업이 등록되면 자동으로 이어지게 했습니다.
DB 기반 RBAC
일반·관리자·슈퍼관리자 3단계 역할을 DB 테이블로 관리하고, 매 요청마다 역할을 조회해 강제합니다. 슈퍼관리자가 권한을 바꾸면 재로그인 없이 즉시 반영되고, 최초 부트스트랩 슈퍼관리자는 지정된 계정으로 자동 복구됩니다. 로그인은 NextAuth 기반 구글 OAuth(@pusan.ac.kr)로, 교내 구성원만 진입할 수 있습니다.
동시 편집 안전장치
원본 업무가 구글시트였던 탓에, 두 사람이 같은 기업을 동시에 고치면 나중 저장이 앞 저장을 덮어쓰는 문제가 있었습니다. 각 레코드에 version 컬럼을 두고 낙관적 락으로 충돌을 감지해, “다른 사용자가 먼저 수정했습니다”로 되돌려 데이터가 조용히 사라지지 않게 했습니다.
성과 대시보드
메인 대시보드는 연도별 산학협력·인턴십의 목표 대비 달성률을 게이지와 추이로 보여줍니다(원본 엑셀의 전체현황 기반, 자체 추적치). 이와 별개로 SW중심대학 성과 탭은 KMAC 검증을 거치는 공식 평가지표를 산출근거와 함께 보여줍니다. 두 지표는 출처와 정의가 달라 별개 자료로 명확히 구분해 두었습니다.
인프라와 배포
처음에는 Vercel + Supabase로 운영했지만, 학생 개인정보를 교내에 두자는 방향에 따라 앱과 DB를 교내 물리 서버로 직접 이전했습니다. 자체 서버는 CI가 GHCR에 올린 Docker 이미지를 SHA 태그로 받아 교체하고, Caddy가 Let’s Encrypt로 HTTPS를 자동 관리합니다. 매일 DB를 덤프하는 백업을 걸어 두고, 클라우드에서 자체 호스팅으로 넘어오며 생긴 부수 조건들(Host 신뢰, 인증서 발급 경로, 방화벽)을 하나씩 되짚어 무중단에 가깝게 옮겼습니다.
기술적 도전
이 프로젝트에서 어려웠던 건 화면이 아니라 그 뒤였습니다. 비표준 엑셀을 안정적으로 읽는 것, 흩어진 데이터를 서로 참조하는 그래프로 잇는 것, 그리고 클라우드가 대신 해 주던 운영 조건을 직접 세우는 것. 각 사건은 따라 하며 재현할 수 있게 블로그로 정리했습니다.
- 멀쩡한 코드를 고쳐서 깨뜨렸다 - 정규식 xlsx 파서의 self-closing 셀 - 정규식 기반 xlsx 리더가 self-closing 빈 셀을 여는 태그로 오인해 옆 칸 값을 흡수하던 오프셋. “off-by-one”으로 착각해 고친 커밋이 오히려 회귀를 만든 과정을 DB 조회와 두 파서 비교로 좁혀 되돌린 기록.
- Vercel·Supabase에서 온프레미스로 - 학생 데이터를 교내 서버로 이전하기 - DB 비밀번호의
@가 libpq 파싱을 막고, 인증서 실패의 원인이 서버가 아니라 캠퍼스 방화벽이었으며, self-hosted Auth.js의AUTH_TRUST_HOST와 엉뚱한 구글 프로젝트에 있던 OAuth 클라이언트까지 되짚은 이전기. - 파워서플라이가 죽어 서버가 꺼졌다 - 복구 전에 데이터 손실 범위부터 따지기 - 서버 PSU 고장 시 라이브 볼륨·매일 백업·클라우드 스냅샷의 최신도를 비교해 복구 방식을 정하고, 백업이 전부 같은 디스크에 있던 단일 장애점을 마주한 기록.
- 로그인만 안 됐다 - DNS는 되는데 나가지 못한 컨테이너 - 구글 로그인만 “server configuration” 에러로 막힌 원인이 콜백 등록이 아니라 컨테이너 egress 유실(DNS는 되는데 연결 안 됨)이었던 장애. env·prisma·ufw·MTU·docker0 대 compose 브리지로 좁혀 NAT 유실을 찾고, 엉킨 iptables는 clean 재부팅으로 정리했다.
배운 점
여러 엑셀을 하나의 시스템으로 합치는 일은, 화면을 만드는 것보다 데이터를 어떻게 잇느냐가 본질이었습니다. 같은 기업, 같은 학생이 여러 문서에 흩어져 있던 걸 하나의 그래프로 모으자, 개별 조회로는 안 보이던 관계가 드러났습니다. 동시에 실서비스이자 개인정보를 다루는 시스템이라, 재업로드로 데이터가 사라지지 않게, 동시 편집이 서로를 덮지 않게, 접근이 통제되게 만드는 신중함이 기능 개발만큼 중요하다는 걸 배웠습니다. 클라우드에서 교내 서버까지 직접 옮기며 운영의 밑단까지 책임지는 경험은, 개발이 API를 구현하는 데서 끝나지 않는다는 걸 다시 확인시켜 주었습니다.