UCO ATM

Tables (24)

atm

  • id — BIGINT — Primary key — NOT NULL
  • serial_number — VARCHAR(64) — NOT NULL — 시리얼 번호
  • bluetooth_name — VARCHAR(64)
  • status — VARCHAR(20) — NOT NULL — AVAILABLE | IN_USE | FULL | ERROR. FULL = no AVAILABLE lockers
  • latitude — DECIMAL(11, 8)
  • longitude — DECIMAL(11, 8)
  • address — VARCHAR(500)
  • address_eng — VARCHAR(500)
  • company_id — BIGINT — Foreign key → company.id — NOT NULL — 소속 회사
  • image_key — VARCHAR(512) — ATM 대표 이미지 S3 key (atm/{atmId}/avatar.jpg)
  • created_by — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

atm_file_key

  • file_key — VARCHAR(512) — Primary key — NOT NULL — S3 key (atm/{atmId}/{original filename})
  • atm_id — BIGINT — Foreign key → atm.id — NOT NULL — atm.id

atm_locker

  • id — BIGINT — Primary key — NOT NULL
  • atm_id — BIGINT — Foreign key → atm.id — NOT NULL — ATM 기기
  • slot_number — VARCHAR(20) — NOT NULL — UI 표시 번호. 예: 07
  • capacity_kg — DECIMAL(12, 3) — NOT NULL — 최대 수용 중량(kg)
  • locker_type — VARCHAR(32) — CAN_30L | CAN_20L | CAN_10L | BOTTLE_5L | SMALL_BOTTLE
  • status — VARCHAR(32) — NOT NULL — AVAILABLE | DOOR_OPEN | UNAVAILABLE
  • created_by — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

atm_report

  • id — BIGINT — Primary key — NOT NULL
  • atm_id — BIGINT — Foreign key → atm.id — NOT NULL — atm.id
  • content — TEXT — NOT NULL — 현장·주변 상황
  • created_by — BIGINT — NOT NULL — 작성 멤버 member.id (FK 없음)
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT — 감사 (FK 없음)
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

atm_transaction

  • id — BIGINT — Primary key — NOT NULL
  • atm_id — BIGINT — Foreign key → atm.id — NOT NULL — ATM 기기
  • customer_id — BIGINT — Foreign key → customer.id — NOT NULL — ATM 수행 고객
  • atm_locker_id — BIGINT — Foreign key → atm_locker.id — 현재 열린 락커 (DOOR_OPEN). FK atm_locker.id
  • status — VARCHAR(20) — NOT NULL — SCALE | DOOR_OPEN | CONFIRM_PENDING | COMPLETED | CANCELLED | REJECTED
  • measured_weight — DECIMAL(12, 3) — ATM 실측 총 무게(kg). Weigh More 시 누적, Finish 시 확정
  • measured_point — DECIMAL(12, 3) — 실측 포인트. measured_weight × points_per_kg
  • accepted_weight — DECIMAL(12, 3) — 회사 검수 승인 무게(kg). COMPLETED 시
  • accepted_point — DECIMAL(12, 3) — 승인 포인트. accepted_weight × points_per_kg
  • point_value — DECIMAL(12, 2) — 1P 금액 스냅샷 (app.reward.point-value, 계량 시점)
  • currency — VARCHAR(10) — 통화 스냅샷 (app.reward.currency)
  • receipt_key — VARCHAR(512) — 영수증 파일 키 (회사 검수 승인 시)
  • scale_image_key — VARCHAR(512) — 고객 계량 시 촬영 사진 S3 key
  • qc_inspection_image_key — VARCHAR(512) — 회사 QC 검수 시 락커 내용물 사진 S3 key
  • cancel_reason — VARCHAR(50) — CANCELLED/REJECTED 시 사유 (LOCKER_EMPTY | SESSION_TIMEOUT | USER_CANCELLED | DEVICE_ERROR | QUALITY_CHECK_REJECTED)
  • confirm_message — TEXT — 회사 검수 거절 시 고객 안내 메시지 (QUALITY_CHECK_REJECTED)
  • qc_member_id — BIGINT — Foreign key → member.id — QC 락커 개방(open-locker)·승인/거절 멤버
  • confirm_pending_at — TIMESTAMP — CONFIRM_PENDING 시각 (UTC)
  • completed_at — TIMESTAMP — COMPLETED/CANCELLED/REJECTED 확정 시각 (UTC)
  • has_sd_file — TINYINT(1) — NOT NULL — complete 시 연결 PO 전부 유효 SD 있었으면 1
  • created_by — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

atm_transaction_po

  • id — BIGINT — Primary key — NOT NULL
  • atm_transaction_id — BIGINT — Foreign key → atm_transaction.id — NOT NULL — atm_transaction.id
  • po_id — BIGINT — Foreign key → point_of_origin.id — NOT NULL — point_of_origin.id
  • po_pickup_id — BIGINT — Foreign key → po_pickup.id — po_pickup.id when started via collector pickup

bank_account

  • id — BIGINT — Primary key — NOT NULL
  • holder_type — VARCHAR(20) — NOT NULL — CUSTOMER | COMPANY
  • holder_id — BIGINT — NOT NULL — customer.id 또는 company.id
  • bank_name — VARCHAR(64) — NOT NULL — 은행명
  • account_number — VARCHAR(32) — NOT NULL — 계좌번호
  • account_holder — VARCHAR(100) — NOT NULL — 예금주
  • is_default — TINYINT(1) — NOT NULL — 기본 계좌 여부
  • created_by — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

cash_out

  • id — BIGINT — Primary key — NOT NULL
  • customer_id — BIGINT — Foreign key → customer.id — NOT NULL — 고객
  • bank_account_id — BIGINT — Foreign key → bank_account.id — NOT NULL — 입금 계좌 (holder_type=CUSTOMER)
  • bank_name — VARCHAR(64) — NOT NULL — 요청 시점 은행명 스냅샷
  • account_number — VARCHAR(32) — NOT NULL — 요청 시점 계좌번호 스냅샷
  • account_holder — VARCHAR(100) — NOT NULL — 요청 시점 예금주 스냅샷
  • point — DECIMAL(12, 3) — NOT NULL — 차감 포인트
  • amount — DECIMAL(12, 2) — NOT NULL — 이체 금액 (요청 시 서버 계산)
  • currency — VARCHAR(10) — NOT NULL — USD, VND, KRW 등
  • status — VARCHAR(20) — NOT NULL — PROCESSING | COMPLETED | CANCELLED
  • failure_reason — VARCHAR(500) — CANCELLED 시 USER_CANCELLED | BANK_FAILED
  • idempotency_key — VARCHAR(64) — 중복 요청 방지 (클라이언트 UUID)
  • point_transaction_id — BIGINT — Foreign key → point_transaction.id — COMPLETED 후 생성된 point_transaction.id
  • completed_at — TIMESTAMP — COMPLETED/CANCELLED 확정 시각
  • created_by — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

company

  • id — BIGINT — Primary key — NOT NULL
  • company_name — VARCHAR(200) — NOT NULL — 회사명
  • currency — VARCHAR(10) — NOT NULL
  • timezone — VARCHAR(50) — NOT NULL
  • country — VARCHAR(10) — NOT NULL
  • business_type — VARCHAR(64) — 업종
  • business_number — VARCHAR(20) — 사업자등록번호
  • address — VARCHAR(500)
  • address_eng — VARCHAR(500)
  • postal_code — VARCHAR(10)
  • phone_number — VARCHAR(30) — 고객 비상 연락처 (국번 제외, 국가는 country 컬럼)
  • business_license_file_key — VARCHAR(512) — 사업자등록증 저장 키
  • stamp_file_key — VARCHAR(512) — 회사 직인(스탬프) 이미지 S3 key
  • logo_key — VARCHAR(512) — 회사 로고 이미지 S3 key
  • latitude — DECIMAL(10, 7)
  • longitude — DECIMAL(11, 7)
  • created_by — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

contract

  • id — BIGINT — Primary key — NOT NULL
  • customer_id — BIGINT — Foreign key → customer.id — NOT NULL — 수거자 고객
  • company_id — BIGINT — Foreign key → company.id — NOT NULL — 수거·매입 회사
  • contract_key — VARCHAR(512) — 계약서 저장 키 (presign 업로드 후)
  • expired_at — TIMESTAMP — NOT NULL — 만료 시각 (등록 + 1년)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP

customer

  • id — BIGINT — Primary key — NOT NULL
  • country_code — CHAR(2) — NOT NULL — ISO 3166-1 alpha-2. 예: VN, KR
  • phone_number — VARCHAR(20) — NOT NULL — 로그인 ID (국가번호 제외)
  • password — VARCHAR(255) — NOT NULL
  • username — VARCHAR(255) — 표시 이름
  • email — VARCHAR(255) — 선택; 값이 있으면 유니크. 로그인 미사용
  • avatar_file_key — VARCHAR(512) — 프로필 아바타 저장 키
  • signature_file_key — VARCHAR(512) — 고객 서명 이미지 S3 key
  • is_collector — TINYINT(1) — NOT NULL — 1 = 수거자(Collector) 자격. po_customer.is_collector=1 목록·COLLECTOR 거래 가능
  • vehicle_type — VARCHAR(32) — 수거자(Collector) 차량: MOTORBIKE | CAR | TRUCK | BICYCLE | ON_FOOT
  • is_active — TINYINT(1) — NOT NULL — 1 = 활성, 0 = 비활성
  • last_login — TIMESTAMP — 마지막 로그인
  • created_by — BIGINT — NOT NULL — 감사 (FK 없음)
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT — 감사 (FK 없음)
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

device_iot_message

  • id — BIGINT — Primary key — NOT NULL
  • message_id — VARCHAR(64) — NOT NULL — envelope message_id (UUID)
  • direction — VARCHAR(16) — NOT NULL — DOWNLINK | UPLINK
  • atm_id — BIGINT — Foreign key → atm.id — NOT NULL — ATM 기기
  • transaction_id — BIGINT — atm_transaction.id (조회용)
  • topic — VARCHAR(255) — NOT NULL — MQTT topic
  • type — VARCHAR(64) — NOT NULL — customer_start | customer_scale_result | ...
  • lang — VARCHAR(8)
  • body_json — TEXT — 전체 envelope JSON
  • iot_status — VARCHAR(16) — NOT NULL — SENT | FAILED
  • iot_published_at — TIMESTAMP — IoT publish 또는 API 수신 시각 (UTC)
  • iot_error — VARCHAR(512)
  • device_status — VARCHAR(16) — NOT NULL — PENDING | RECEIVED | TIMEOUT
  • device_received_at — TIMESTAMP — ATM 수신(ack) 또는 기기 발송 시각 (UTC)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP

iot_latency_test_log

  • id — BIGINT — Primary key — NOT NULL
  • direction — VARCHAR(16) — NOT NULL — DOWNLINK | UPLINK
  • message_id — VARCHAR(64) — NOT NULL
  • message_type — VARCHAR(64) — NOT NULL
  • device_serial — VARCHAR(64) — NOT NULL — RUCO_ATM_001
  • topic — VARCHAR(255) — NOT NULL
  • body_json — TEXT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP

member

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key → company.id — NOT NULL — 소속 회사
  • country_code — CHAR(2) — NOT NULL
  • phone_number — VARCHAR(20) — NOT NULL — 로그인 ID
  • password — VARCHAR(255) — NOT NULL
  • username — VARCHAR(255)
  • email — VARCHAR(255)
  • avatar_file_key — VARCHAR(512)
  • bank_name — VARCHAR(64) — 레거시 단일 은행 필드
  • bank_account — VARCHAR(32)
  • bank_account_holder — VARCHAR(100)
  • is_active — TINYINT(1) — NOT NULL — 1 = 활성, 0 = 비활성
  • last_login — TIMESTAMP
  • created_by — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

member_role

  • role — VARCHAR(32) — Primary key — NOT NULL — ROLE_ADMIN | ROLE_MANAGER | ROLE_STAFF
  • member_id — BIGINT — Foreign key → member.id — NOT NULL

member_task

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key → company.id — NOT NULL — 소속 회사
  • member_id — BIGINT — Foreign key → member.id — NOT NULL — 담당 멤버 (assignee)
  • reference_type — VARCHAR(50) — ATM_TRANSACTION | DEVICE_PROBLEM | COMPLAINT
  • reference_id — BIGINT — 연관 엔티티 ID (DEVICE_PROBLEM → atm.id)
  • status — VARCHAR(20) — NOT NULL — OPEN | IN_PROGRESS | COMPLETED | CANCELLED
  • title — VARCHAR(255) — NOT NULL
  • description — VARCHAR(1000)
  • note — VARCHAR(1000) — 완료/취소 메모
  • completed_at — TIMESTAMP — 완료·취소 시각
  • created_by — BIGINT — NOT NULL — 생성자 member.id (감사, FK 없음)
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT — 감사 (FK 없음)
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

notification

  • id — BIGINT — Primary key — NOT NULL
  • recipient_type — VARCHAR(20) — NOT NULL — CUSTOMER | MEMBER (MEMBER=ADMIN/MANAGER/STAFF, member.id)
  • recipient_id — BIGINT — NOT NULL — customer.id 또는 member.id
  • sender_type — VARCHAR(20) — CUSTOMER | MEMBER. null=시스템 자동 알림
  • sender_id — BIGINT — 발신 customer.id 또는 member.id
  • title — VARCHAR(255) — NOT NULL
  • message — VARCHAR(1000) — NOT NULL
  • reference_type — VARCHAR(50) — CUSTOMER_ATM_TRANSACTION | COMPANY_PICKUP | DEVICE_PROBLEM | COMPLAINT
  • reference_id — BIGINT — 연관 엔티티 ID
  • image_file_key — VARCHAR(512) — 이미지·첨부 저장 키
  • is_read — TINYINT(1) — NOT NULL
  • read_at — TIMESTAMP — 읽은 시각
  • created_by — BIGINT — NOT NULL — 감사 (FK 없음)
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT — 감사 (FK 없음)
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

po_customer

  • id — BIGINT — Primary key — NOT NULL
  • po_id — BIGINT — Foreign key → point_of_origin.id — NOT NULL — 배출 장소
  • customer_id — BIGINT — Foreign key → customer.id — NOT NULL — 고객
  • is_collector — TINYINT(1) — NOT NULL — 0=본인 PO 목록, 1=수거자 수거 목록
  • is_default — TINYINT(1) — NOT NULL — 해당 customer·is_collector 구간의 기본 PO
  • customer_role — VARCHAR(32) — OWNER | MANAGER | HEAD_CHEF | STAFF | OTHER (고객↔PO 연결별)
  • created_by — BIGINT — NOT NULL — 감사 (FK 없음)
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT — 감사 (FK 없음)
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

po_pickup

  • id — BIGINT — Primary key — NOT NULL
  • customer_id — BIGINT — Foreign key → customer.id — NOT NULL — 수거자 customer.id
  • po_id — BIGINT — Foreign key → point_of_origin.id — NOT NULL — 배출 장소
  • weight — DECIMAL(12, 3) — NOT NULL — 현장 계량(kg)
  • oil_can_image_key — VARCHAR(512) — 유류 캔 사진 S3 key
  • status — VARCHAR(20) — NOT NULL — PENDING_ATM | PARTIALLY_DEPOSITED | DEPOSITED | CANCELLED
  • created_by — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

point_of_origin

  • id — BIGINT — Primary key — NOT NULL
  • po_name — VARCHAR(255) — 배출 장소명
  • po_type — VARCHAR(32) — NOT NULL — HOME | RESTAURANT
  • owner_name — VARCHAR(100) — 소유자/담당자명
  • phone_number — VARCHAR(32) — Google Maps 등 원본 형식 (+..., 공백 포함 가능)
  • address — VARCHAR(500)
  • address_eng — VARCHAR(500)
  • postal_code — VARCHAR(10)
  • country_code — CHAR(2) — ISO 3166-1 alpha-2. 예: VN, KR
  • latitude — DECIMAL(10, 7)
  • longitude — DECIMAL(11, 7)
  • business_type — VARCHAR(64) — FAST_FOOD, STREET_FOOD, CAFE_BAKERY, etc.
  • business_number — VARCHAR(20)
  • business_license_file_key — VARCHAR(512)
  • signature_file_key — VARCHAR(512) — PO 서명 이미지 S3 key
  • annual_output_oil — DECIMAL(12, 3) — 연간 배출 유량(kg)
  • sellable — DECIMAL(12, 3) — 판매 가능 유량(kg)
  • uco_monthly_output_more_5t — TINYINT(1) — NOT NULL — PO 월 UCO 생산량 5톤 이상 여부
  • uco_animal_origin — TINYINT(1) — NOT NULL — PO UCO 전부/일부 동물성 여부
  • created_by — BIGINT — NOT NULL — 감사 (FK 없음)
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT — 감사 (FK 없음)
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

point_transaction

  • id — BIGINT — Primary key — NOT NULL
  • customer_id — BIGINT — Foreign key → customer.id — NOT NULL — 고객
  • transaction_type — VARCHAR(20) — NOT NULL — POINT_IN | POINT_OUT
  • point — DECIMAL(12, 3) — NOT NULL — 포인트 (양수)
  • reference_type — VARCHAR(50) — POINT_IN: CUSTOMER_ATM_TRANSACTION | POINT_OUT: CASH_OUT
  • reference_id — BIGINT — POINT_IN: atm_transaction.id | POINT_OUT: cash_out.id
  • created_by — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

push_device_token

  • id — BIGINT — Primary key — NOT NULL
  • owner_type — VARCHAR(20) — NOT NULL — CUSTOMER | MEMBER
  • owner_id — BIGINT — NOT NULL — customer.id or member.id
  • fcm_token — VARCHAR(512) — NOT NULL
  • platform — VARCHAR(16) — NOT NULL — ios | android
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP
  • is_deleted — BIT(1) — NOT NULL

sd_file

  • id — BIGINT — Primary key — NOT NULL
  • po_id — BIGINT — Foreign key → point_of_origin.id — NOT NULL — 배출 장소
  • company_id — BIGINT — Foreign key → company.id — NOT NULL — 회사
  • file_key — VARCHAR(512) — 저장 키
  • expired_at — TIMESTAMP — 만료 시각 (파일 업로드 시 +1년, draft는 NULL)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP

uco_price_tracking

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key → company.id — NOT NULL — 회사
  • points_per_kg — DECIMAL(12, 3) — NOT NULL — kg당 포인트 (회사별)
  • created_at — TIMESTAMP — NOT NULL
  • created_by — BIGINT — NOT NULL — 등록 멤버