# ERD — PID 157450 Export Ops (파일럿 실스키마)

> 본 문서는 템플릿이 아니라 `src/lib/db.ts` 마이그레이션·시드와 동일한 테이블 구조의 발췌입니다.
> 본개발 시 PostgreSQL로 이관하며 타입(TEXT→timestamptz 등)만 조정합니다.

## 논리 ERD

```mermaid
erDiagram
  users ||--o{ sessions : has
  users ||--o{ assignments : acts
  users ||--o{ leads : assigns
  users ||--o{ auctions : opens
  users ||--o{ bids : places
  leads ||--o{ assignments : history
  leads ||--o| market_quotes : has
  leads ||--o{ photos : has
  leads ||--o{ auctions : has
  auctions ||--o{ bids : receives
  leads ||--o{ outbox : purchase_push

  users {
    text id PK
    text email UK
    text password_hash
    text role "manager|sales|buyer"
    text name
    int active
  }
  sessions {
    text id PK
    text user_id FK
    text expires_at
  }
  leads {
    text id PK
    text ref_code UK
    text status
    text owner_name
    text owner_phone
    text plate_number
    text vehicle_json
    text assignee_id FK
    text internal_memo
    text buyer_notes_json
    text checklist_json
    int hope_price
    int purchase_amount
    text purchase_confirmed_by
    text purchase_confirmed_at
  }
  market_quotes {
    text lead_id PK_FK
    int compare_low
    int compare_high
    int scrap_price
    text source
  }
  photos {
    text id PK
    text lead_id FK
    text path
    text mime
    int sort_order
    text kind
    text caption
    int is_cover
  }
  auctions {
    text id PK
    text lead_id FK
    int round
    text status
    text ends_at
    text winning_bid_id
    int winning_amount
    text opened_by FK
  }
  bids {
    text id PK
    text auction_id FK
    text buyer_id FK
    int amount
    text memo
    text status "active|superseded"
  }
  outbox {
    text id PK
    text channel
    text idempotency_key UK
    text payload_json
    text status
    int attempts
  }
  audit_logs {
    text id PK
    text actor_id
    text action
    text target
    text detail_json
  }
```

## 상태 머신 (lead.status)

`received → assigned → consulting → in_auction → negotiation → purchase_confirmed`

- `in_auction` 재진입: negotiation 후 재입찰 open
- 매입 확정: `negotiation` 에서만 `confirmPurchase` 허용

## 바이어 경계

바이어 응답 DTO(`buyer-dto.ts`)는 leads 원행을 직렬화하지 않습니다.
허용: 정제 제원·사진·체크리스트 요약·endsAt·본인 입찰.
차단: owner/phone/plate 원문·compare/scrap·internal_memo.