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 — 등록 멤버