대규모 AI 시스템 프로젝트

테이블 (11)

p_ai_alert

  • id — UUID — 기본 키 — NOT NULL
  • order_id — UUID — NOT NULL
  • delivery_id — UUID — NOT NULL
  • prompt — UUID — NOT NULL
  • final_deadline — TIMESTAMPTZ — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • error_message — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — UUID — NOT NULL
  • updated_at — TIMESTAMPTZ
  • updated_by — UUID
  • deleted_at — TIMESTAMPTZ
  • deleted_by — UUID — NOT NULL

p_company

  • id — UUID — 기본 키 — NOT NULL
  • company_name — VARCHAR(100) — NOT NULL
  • compnay_type — VARCHAR(50) — NOT NULL
  • hub_id — UUID — NOT NULL
  • company_address — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — UUID — NOT NULL
  • updated_at — TIMESTAMPTZ
  • updated_by — UUID
  • deleted_at — TIMESTAMPTZ
  • deleted_by — UUID — NOT NULL

p_delivery

  • id — UUID — 기본 키 — NOT NULL — 애플리케이션 생성(Apllication Generated)
  • company_delivery_manager_id — UUID — 배송 생성 시에는 아직 담당자가 배정되지 않으므로 NULL 허용, 최종 목적지 허브 도착 후 업체 배송 담당자 배정 시 값이 설정됨
  • order_id — UUID — NOT NULL — Logical FK, Order Service 주문 ID(Order Service.order_id 참조)
  • departure_hub_id — UUID — NOT NULL — Logical FK, Hub Service 출발지 허브 ID(Hub Service.departure_hub_id 참조)
  • destination_hub_id — UUID — NOT NULL — Logical FK, Hub Service 목적지 허브 ID(Hub Service.arrival_hub_id 참조)
  • status — VARCHAR(50) — NOT NULL — check in (WAITING_AT_DEPARTURE_HUB, MOVING_BETWEEN_HUBS, ARRIVED_AT_DESTINATION_HUB, MOVING_TO_COMPANY, DELIVERED)
  • delivery_address — VARCHAR(255) — NOT NULL
  • recipient_name — VARCHAR(50) — NOT NULL — 수령인 이름
  • recipient_slack_id — VARCHAR(50) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — UUID — NOT NULL — 내부로만 이용시 사용자가아닌 xxxx-service
  • updated_at — TIMESTAMPTZ — NOT NULL
  • updated_by — UUID — NOT NULL — 내부로만 이용시 사용자가아닌 xxxx-service
  • deleted_at — TIMESTAMPTZ
  • deleted_by — UUID

p_delivery_manager

  • id — UUID — 기본 키 — NOT NULL — 사용자 ID와 동일해야하기떄문에 직접 사용자 id를 담당자id값으로 주입
  • slack_id — VARCHAR(50) — NOT NULL — Logical FK, User Service 슬랙 ID(User Service.slack_id 참조)
  • hub_id — UUID — 업체 배송담당는 소속허브가 있고, 허브 배송담당자는 허브에 소속되지않는다
  • type — VARCHAR(50) — NOT NULL — check in (HUB, COMPANY)
  • sequence — INTEGER — NOT NULL — CREATE UNIQUE INDEX uq_company_hub_sequence ON delivery_manager (hub_id, sequence) WHERE type = 'COMPANY'; CREATE UNIQU…
  • 삭제되도 — 순번은 — NOT NULL

p_delivery_route

  • id — UUID — 기본 키 — NOT NULL — 애플리케이션 생성(Apllication Generated)
  • delivery_id — UUID — NOT NULL — UNIQUE(delivery_id, sequence)
  • hub_delivery_manager_id — UUID — NOT NULL
  • departure_hub_id — UUID — NOT NULL — Logical FK, Hub Service 출발지 허브 ID(Hub Service.departure_hub_id 참조)
  • arrival_hub_id — UUID — NOT NULL — Logical FK, Hub Service 도착지 허브 ID(Hub Service.arrival_hub_id 참조)
  • sequence — INTEGER — NOT NULL — UNIQUE(delivery_id, sequence)
  • status — VARCHAR(50) — NOT NULL — 상태: WAITING_FOR_DEPARTURE, IN_TRANSIT, ARRIVED
  • departed_at — TIMESTAMPTZ
  • arrived_at — TIMESTAMPTZ
  • expected_distance — DECIMAL(10, 2) — NOT NULL — 단위 : km
  • expected_duration — INTEGER — NOT NULL — 단위 : minute(분)
  • actual_distance — DECIMAL(10, 2) — 허브 간 배송 완료 시 기록되는 실제 거리 완료 전에는 NULL 실제 GPS나 실시간 위치 추적 기능이없기때문에 ARRIVED 상태될때 actual 값 추가
  • actual_duration — INTEGER — 허브 간 배송 완료 시 기록되는 실제 소요시간 완료 전에는 NULL 도착 시 출발·도착 시각 차이로 계산
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — UUID — NOT NULL — 내부로만 이용시 사용자가아닌 xxxx-service
  • updated_at — TIMESTAMPTZ — NOT NULL
  • updated_by — UUID — NOT NULL — 내부로만 이용시 사용자가아닌 xxxx-service
  • deleted_at — TIMESTAMPTZ
  • deleted_by — UUID

p_hub

  • id — UUID — 기본 키 — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • address — VARCHAR(255) — NOT NULL
  • latitude — DECIMAL(9,6) — NOT NULL
  • longitude — DECIMAL(9,6) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — UUID — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • updated_by — UUID — NOT NULL
  • deleted_at — TIMESTAMPTZ
  • deleted_by — UUID

p_hub_route

  • id — UUID — 기본 키 — NOT NULL
  • departure_hub_id — UUID — NOT NULL
  • arrival_hub_id — UUID — NOT NULL
  • duration_minutes — INTEGER — NOT NULL
  • distance_km — DECIMAL(10,2) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — UUID — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • updated_by — UUID — NOT NULL
  • deleted_at — TIMESTAMPTZ
  • deleted_by — UUID

p_order

  • id — UUID — 기본 키 — NOT NULL
  • receiver_company_id — UUID — NOT NULL
  • supplier_company_id — UUID — NOT NULL
  • delivery_id — UUID — NOT NULL
  • quantity — INTEGER — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • request_message — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — UUID — NOT NULL
  • updated_at — TIMESTAMPTZ
  • updated_by — UUID
  • deleted_at — TIMESTAMPTZ
  • deleted_by — UUID — NOT NULL

p_product

  • id — UUID — 기본 키 — NOT NULL
  • id3 — UUID — NOT NULL
  • hub_id — UUID — NOT NULL
  • company_id — UUID — NOT NULL
  • hud_id — UUID — NOT NULL
  • product_name — VARCHAR(100) — NOT NULL
  • product_description — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — UUID
  • updated_at — TIMESTAMPTZ — NOT NULL
  • updated_by — UUID
  • deleted_at — TIMESTAMPTZ' — NOT NULL

p_slack_message

  • id — UUID — 기본 키 — NOT NULL
  • ai_alert_id — UUID — NOT NULL
  • slack_receiver_id — UUID — NOT NULL
  • message — TEXT — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • sent_at — TIMESTAMPTZ
  • error_message — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — UUID — NOT NULL
  • updated_at — TIMESTAMPTZ
  • updated_by — UUID
  • deleted_at — TIMESTAMPTZ
  • deleted_by — UUID — NOT NULL

p_user

  • id — UUID — 기본 키 — NOT NULL
  • username — VARCHAR(50) — NOT NULL
  • slack_id — VARCHAR(50) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • email — VARCHAR(30) — NOT NULL
  • nickname — VARCHAR(20) — NOT NULL
  • role — VARCHAR(20) — NOT NULL
  • hub_id — UUID
  • company_id — UUID
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • created_by — UUID — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • updated_by — UUID — NOT NULL
  • deleted_at — TIMESTAMP
  • deleted_by — UUID