문서 번호 참조와 외래 키: 되돌리기 코드를 번호로 짤 때의 문제

제조업 ERP 데모에서 시드로 만든 「수금 완료」 매출 전표에서 수금 취소를 누르면 이상한 일이 생기는 구조를 찾았습니다. 원 전표는 미수로 돌아오는데 수금 전표는 지워지지 않고 남습니다. 수금 합계에는 받지 않은 돈이 이중으로 잡힙니다.

수금 취소 코드는 이렇게 수금 전표를 찾았습니다.

const pay = await ctx.db.voucher.findFirst({ where: { refNo: v.no, kind: { in: ["RECEIPT", "PAYMENT"] } } });

refNo 는 사람이 읽는 원 전표 번호를 담는 글자 칸입니다. 앱에서 수금하면 이 칸에 원 전표 번호가 들어갑니다. 시드가 만든 수금과 지급 전표 330건은 이 칸이 비어 있었습니다. 찾을 글자가 없으니 수금 전표를 못 찾았고, 원 전표만 되돌렸습니다.

시드 버그로 보이는 첫 증상

겉으로는 시드 버그입니다. 실제로 시드가 앱과 다른 규칙으로 데이터를 만든 것이 직접 원인이었고, 그 330건은 시드를 보강해서 원 전표 번호로 채웠습니다. 같은 수주, 같은 합계, 같은 정산일로 하나로 정해지는 원 전표만 이었습니다.

그것으로 끝낼 일은 아니었습니다. 시드가 칸 하나를 비웠다고 수금 취소가 조용히 반만 동작했다면, 그 칸은 표시용 글자가 아니라 사실상 외래 키입니다. 외래 키인데 데이터베이스는 그 사실을 모릅니다. 비어 있어도, 오타가 있어도, 없는 번호를 가리켜도 받아 줍니다.

같은 방식으로 이어진 곳을 세어 보니 13곳이었습니다. 출하와 매출 전표, 입고와 매입 전표, 수금과 원 전표가 모두 번호 글자로 이어져 있었고, 되돌리기 코드가 그 글자로 서로를 찾았습니다.

번호 글자가 외래 키를 대신할 때 약한 곳

번호로 잇는 설계가 끌리는 이유는 분명합니다. SH-2026-0012 는 사람이 읽을 수 있고, 화면에 그대로 보여 주고 링크로도 씁니다. id 칸을 따로 두면 같은 관계를 두 번 적는 것처럼 느껴집니다.

그 대가로 잃는 것이 있습니다.

• 데이터베이스가 관계를 검사하지 않습니다. 비어 있거나 틀린 번호가 들어가도 저장됩니다.
• 번호는 바뀔 수 있습니다. 번호 규칙을 고치거나 다시 발급하면 가리키던 쪽이 끊깁니다.
• 번호는 법인 안에서만 유일합니다. 여러 회사가 한 데이터베이스를 쓰면 다른 회사의 같은 번호가 걸릴 수 있어서, 찾을 때마다 회사 조건을 빠뜨리지 않아야 합니다.
• 원 문서가 지워질 때 어떻게 할지 정할 수 없습니다. Restrict 도 SetNull 도 걸 곳이 없습니다.

첫 증상은 이 중 첫 번째였습니다. 나머지는 아직 증상이 없었을 뿐입니다.

전표에 원 문서 id 를 두는 방법

전표에 원 문서를 가리키는 외래 키 세 개를 더했습니다.

shipmentId String?
shipment   Shipment? @relation(fields: [shipmentId], references: [id], onDelete: SetNull)
receiptId  String?
receipt    GoodsReceipt? @relation(fields: [receiptId], references: [id], onDelete: SetNull)
settlesId  String?
settles    Voucher?  @relation("Settles", fields: [settlesId], references: [id], onDelete: SetNull)

매출 전표는 출하를, 매입 전표는 입고를, 수금과 지급 전표는 원 전표를 가리킵니다. refNo 는 지우지 않았습니다. 화면에 번호를 보여 주고 링크를 거는 일은 계속 글자가 하는 것이 편합니다. 달라진 것은 역할입니다. 글자는 보여 주는 데만 쓰고, 찾는 것은 id 로 합니다.

수금 취소는 이제 이렇게 찾습니다.

const pay = await ctx.db.voucher.findFirst({ where: { settlesId: v.id, kind: { in: ["RECEIPT", "PAYMENT"] } } });

출하 되돌리기, 입고 되돌리기, 입고 반품, 테스트용 정리 API 도 같은 방식으로 바꿨습니다. 새로 만드는 전표는 만들 때 id 를 넣고, 이미 있던 전표 698건은 번호로 한 번 이어 붙였습니다. 번호로 잇는 것은 이 한 번이 마지막입니다.

유일 제약 대신 인덱스로 타협한 부분

출하 하나에는 매출 전표가 하나만 생깁니다. 그렇다면 shipmentId 에 유일 제약을 걸어 일대일로 선언하는 것이 맞습니다.

이번에는 그러지 못했습니다. 이 데모는 스키마를 prisma db push 로 반영하는데, 기존 테이블에 유일 제약을 더하는 변경을 데이터 손실이 날 수 있는 변경으로 보고 확인 없이는 진행하지 않았습니다. 새로 만든 빈 칸이라 실제 손실은 없었지만, 확인 플래그로 밀어붙이지 않고 인덱스만 두고 일대다로 선언했습니다. 출하당 전표 하나는 지금 코드가 지킵니다.

저는 이것을 남은 빚으로 봅니다. 코드가 지키는 제약은 새 경로가 생기면 빠집니다. 마이그레이션 파일로 스키마를 관리하는 단계가 되면 유일 제약으로 바꿔야 할 첫 줄입니다.

재고 이동에 남은 짐작

전표 쪽은 이렇게 정리됐지만, 같은 문제가 재고 이동에는 아직 남아 있습니다.

재고 이동에는 refId 칸이 있어서 앱이 만든 이동은 어느 실적, 출하, 입고에서 왔는지 id 로 압니다. 다만 이 칸은 여러 테이블 중 하나를 가리키는 칸이라 외래 키를 걸 수 없고, 시드가 만든 재고 이동 2,806건은 이 칸이 비어 있습니다.

그래서 생산 실적을 되돌리는 코드는 시드 데이터에 대해 짐작합니다.

const guessed = exact.length ? [] : await ctx.db.stockMove.findMany({
  where: { refId: null, refNo: r.workOrder.no, date: r.date, kind: { in: [...] } },
});

작업 지시 번호, 날짜, 이동 종류가 같으면 그 실적의 이동으로 봅니다. 같은 날 같은 지시에 실적이 두 번 있었다면 두 실적의 이동이 모두 걸립니다. 하나만 되돌렸는데 둘 다 지워질 수 있습니다.

여러 테이블을 가리키는 칸에 외래 키를 거는 방법은 두 갈래입니다. 가리킬 수 있는 테이블마다 칸을 따로 두고(workResultId, shipmentId, receiptId) 그중 하나만 채우는 방법과, 이동을 만든 문서를 한 테이블로 모으는 방법입니다. 저라면 가리킬 곳이 셋 정도인 지금은 첫 번째를 고르겠습니다. 칸이 늘어나는 비용보다, 데이터베이스가 관계를 알게 되는 이득이 큽니다.

번호와 id 를 가르는 기준

이번 일로 제가 정한 기준은 단순합니다. 코드가 그 값으로 다른 행을 찾는다면, 그 값은 id 여야 한다.

화면에 보여 주고 사람이 검색하는 값은 번호가 맞습니다. 되돌리기, 취소, 삭제처럼 코드가 관계를 따라가는 곳에서 번호 글자를 쓰기 시작하면, 그 순간 데이터베이스가 모르는 외래 키가 하나 생깁니다. 그리고 그 외래 키는 칸 하나가 비는 날 처음으로 자기를 드러냅니다.