minaChoi/o2o-negosium-original
1
0
Fork 0
Code Pull Requests Activity
design/frontend
o2o-negosium-original/lps/docs/database.md
민헌 74db218410 feat(lps): 2단계 — price_history 에 몰별 확인 상태 저장 
1단계에서 만든 SourceState 가 job.result 에만 있어 화면까지 못 갔다. by_mall 은 **가격이 있는
몰만** 담으므로, 빠진 몰이 '거기엔 없더라'인지 '거기를 못 봤다'인지 구분할 자리가 없었다.

- price_history.sources (JSONB): 몰별 상태를 그대로 담는다.
  {"naver": {"state": "matched", "count": 40}, "coupang": {"state": "blocked", "error": "..."}}
  열린 스키마라 몰이 늘거나 근거를 덧붙여도 마이그레이션이 필요 없다(by_mall 과 같은 방침).
- price_history.partial (bool): 결과가 완전한가. sources 에서 유도 가능하지만 컬럼으로 둔다 —
  소비자가 '어떤 상태가 확인된 것인가'라는 판단 규칙까지 알아야 하면 **상태 정의가 두 곳으로
  흩어진다**. 판단은 LPS 가 끝내고 소비자(negodata·lps-admin)는 사실 하나만 읽는다.
- 부분 인덱스 ix_price_history_partial — partial=true 행만 담아 작게 유지(운영 점검·알림용).
- _record_history 가 per_source 를 받아 partial 을 계산해 기록한다. 네거티브 캐시 히트 경로는
  sources 없이 남긴다(부분 결과는 애초에 캐시하지 않으므로 항상 확정).
- 마이그레이션: postgres-init/dbeaver/7_lps_source_state_dbeaver.sql (재실행 안전, **운영 적용 필요**)

검증(로컬 실 DB): 쿠팡 차단 vs 쿠팡 0건은 by_mall 이 둘 다 ['naver'] 로 같지만
partial(true/false)·sources.coupang.state(blocked/empty)가 두 경우를 갈라낸다.
JSONB 는 ensure_ascii=False 로 한글 사유가 깨지지 않는 것도 테스트로 고정.

테스트 3건 추가, 전체 289 passed. 진행 상황은 docs/result-states.md 4절.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
2026-08-07 10:57:19 +09:00

190 lines
9.5 KiB
Markdown
Raw Permalink Blame History

# 데이터베이스 구조
[← README로](../README.md)
- **DB 이름**: `lps_db` (PostgreSQL, negosium_db와 별개)
- **테이블 정의**: `common/database/model/models.py` (SQLAlchemy) — 이 파일이 스키마의 단일 출처
- **공통 규칙**: 외래키(FK) 안 씀(무결성은 앱에서) · 코드값은 정수(SMALLINT) · 시각은 전부 `TIMESTAMPTZ`(UTC)
## 테이블 6종 한눈에
| 테이블 | 용도 |
|--------|------|
| `job` | 작업 큐 — 검색 요청을 순서대로 보관·처리 |
| `price_history` | 최저가 이력 — 그래프용 시계열 스냅샷 |
| `search_negative` | 네거티브 캐시 — "없음"으로 확인된 상품을 일정 시간 기억 |
| `bot_detection` | 봇 감지 이력 — 쿠팡이 차단한 패턴 기록 |
| `ip_session` | IP 세션 종료 이력 — 요청 예산(선제 회전) 상한 튜닝 데이터 |
| `proxy_port` | 프록시 포트(IP 세션) 임대 장부 — **프로세스 간 공유** 상태 |
---
## 1. `job` — 작업 큐
| 컬럼 | 뜻 |
|------|-----|
| `job_id` | 작업 고유 ID (요청 시 반환되는 접수번호) |
| `job_type` | 작업 종류 (1=검색, 2=외부전송) |
| `status` | 상태 (아래 코드표) |
| `priority` | 우선순위(낮을수록 먼저) |
| `payload` | 요청 내용(상품 정보) JSON |
| `result` | 처리 결과 JSON (최저가·단계·소스별 건수 등) |
| `attempts` / `max_attempts` | 시도 횟수 / 최대 |
| `run_after` | 이 시각 이후 실행(재시도 대기용) |
| `lease_until` / `worker_id` | 점유 만료 시각 / 처리 중인 워커 (죽으면 자동 회수) |
| `last_error` | 마지막 오류 메시지 |
| `created_at` / `updated_at` | 생성/수정 시각 |
**status 코드값** (`JobStatus`)
| 값 | 이름 | 뜻 |
|----|------|-----|
| 1 | PENDING | 대기 중 |
| 2 | RUNNING | 처리 중 |
| 3 | DONE | 완료 (found/not_found 모두 포함) |
| 4 | DEAD | 재시도 소진 실패 (사람 확인 필요) |
**job_type 코드값** (`JobType`): 1=SEARCH(검색), 2=OUTBOX(외부전송)
---
## 2. `price_history` — 최저가 이력 (그래프)
검색할 때마다 1행씩 쌓입니다. 특정 상품의 시계열을 뽑아 그래프로 그립니다.
| 컬럼 | 뜻 |
|------|-----|
| `product_code` | 상품 식별 키(요청의 product_code) |
| `triggered_at` | 검색 실행 시각 (**그래프 X축**) |
| `outcome` | found / not_found |
| `matched_count` | AI가 "같은 상품"으로 판정한 개수 |
| `naver_lowest` / `naver_name` / `naver_url` | 네이버 최저가 + 상품명/링크 |
| `coupang_lowest` / `coupang_name` / `coupang_url` | 쿠팡 최저가 + 상품명/링크 |
| `final_lowest` | 전체 최저가 (**그래프 Y축 핵심**) |
| `final_source` | 최종 최저가가 나온 소스(naver/coupang/gmarket/auction/st11) |
| `by_mall` | 몰별 최저가 스냅샷(JSONB, 열린 스키마) — `[{mall, source, price, shipping_fee, shipping_type, url}, …]`. G마켓·옥션·11번가 등이 늘어도 컬럼 추가 없이 담는다 |
| `sources` | **몰별 확인 상태**(JSONB) — `{"naver": {"state": "matched", "count": 40}, "coupang": {"state": "blocked", "error": "..."}}`. `by_mall` 은 가격이 있는 몰만 담으므로, 빠진 몰이 '거기엔 없더라'인지 '거기를 못 봤다'인지는 이 값에만 있다. state 값은 `SourceState`([정의](result-states.md)) |
| `partial` | 결과가 **완전한가**. `true`=못 본 몰이 있어 최종이 아니다. `sources` 에서 유도 가능하지만 컬럼으로 두어, 소비자가 상태 분류 규칙을 몰라도 되게 한다 |
| `job_id` / `created_at` | 검색 잡 연결 / 생성 시각 |
> 한쪽 소스에 그 상품이 없던 시점은 해당 컬럼이 `null`(그래프 선이 빈다 — 정상).
---
### 최저가 오퍼의 품질 정보 (2026-08-05 추가)
가격만으로는 '실제로 살 수 있는 값인지' 알 수 없어, 최종 최저가 오퍼의 근거를 함께 남긴다.
| 컬럼 | 뜻 |
|------|-----|
| `final_rating` / `final_review_count` | 평점·리뷰 수. **둘 다 NULL 이면 미검증 오퍼**(재고 없는 미끼가격일 수 있음). NULL(정보 없음)과 0(리뷰 0개)은 다른 뜻이라 기본값 없음 |
| `final_shipping_fee` | 0=무료, NULL=미확인(로켓처럼 조건부 무료라 화면에 금액이 없음) |
| `final_shipping_type` | free / paid / rocket / rocket_merchant |
| `final_shipping_label` | 화면 문구 원문(예: `내일(목) 도착 보장 · 와우는 무료배송 ∙ 무료반품 ∙ 새벽도착`) |
**순위는 상품가 기준이다.** 배송 주체가 다르면(쿠팡 로켓 / 판매자로켓 / 네이버 판매자) 배송비
비교가 무의미해서다 — 기록만 남겨 "배송비를 더하면 순위가 뒤집히는 비율"을 나중에 판단한다.
```sql
-- 리뷰·평점 없는 오퍼가 최저가로 잡힌 비율(유령상품 노출도)
SELECT count(*) FILTER (WHERE final_review_count IS NULL) * 100.0 / count(*) AS 미검증_퍼센트
FROM price_history WHERE outcome = 'found';
```
## 3. `search_negative` — 네거티브 캐시
"검색해도 없더라"를 일정 시간(기본 24h) 기억해 **재검색 낭비를 막습니다**.
| 컬럼 | 뜻 |
|------|-----|
| `key` | 상품 식별 키(보통 product_code) |
| `until` | 이 시각까지 "없음"으로 간주 (지나면 다시 검색 허용) |
| `reason` | 사유 메모 |
| `created_at` | 생성 시각 |
---
## 4. `bot_detection` — 봇 감지 이력
쿠팡이 차단(봇 감지)했을 때 기록. "**어떤 IP로 몇 번째 요청에서 걸리나**"를 분석합니다.
| 컬럼 | 뜻 |
|------|-----|
| `source` | 소스(coupang) |
| `query` | 감지 당시 검색어 |
| `ip_request_no` | 현재 IP(브라우저)로 몇 번째 요청이었나 |
| `proxy_port` | 사용 중이던 프록시 포트(=IP 세션) |
| `elapsed_sec` | 브라우저 실행 후 경과(초) |
| `marker` | 감지 근거(차단 페이지 마커) |
| `headless` / `html_len` | 헤드리스 여부 / 응답 크기 |
| `created_at` | 감지 시각 |
**분석 예시**
```sql
-- IP당 평균 몇 요청 만에 감지되는지
SELECT avg(ip_request_no), count(*) FROM bot_detection;
```
---
## 5. `ip_session` — IP(프록시 포트) 세션 종료 이력
브라우저(=IP 세션)가 끝날 때마다 기록. `bot_detection`은 **차단된** 세션만 남지만,
여기엔 **무사 종료**(예산 선제 회전·시간창 만료 등)도 남아 요청 예산(`[DecodoConfig].ip_request_budget`)
상한 튜닝의 원천 데이터가 됩니다.
| 컬럼 | 뜻 |
|------|-----|
| `source` | 소스(coupang 등) |
| `proxy_port` | 사용 포트(=IP 세션). 프록시 미사용이면 NULL |
| `requests` | 이 IP로 보낸 요청 수 |
| `ok_count` / `blocked_count` | 성공 검색 수 / 차단 감지 수 |
| `elapsed_sec` | IP 세션 지속 시간(초) — 브라우저 수명이 아니라 **그 IP 를 쥔 총 시간** |
| `end_reason` | 종료 사유 — `budget`(예산 선제) / `block`(차단) / `proxy_error`(포트 사망) / `window`(sticky 수명 만료) / `shutdown`(종료) |
| `created_at` | 세션 종료 시각 |
> **IP 세션 ≠ 브라우저 수명** (2026-08-05 변경). 유휴 정리(120s)는 브라우저만 닫고 같은 IP 로
> 돌아오므로 세션이 끝나지 않는다 — 그래서 `idle` 사유는 더 이상 기록되지 않는다.
> 예전엔 재기동마다 카운터가 0 으로 리셋돼 **한 IP 를 계속 쓰면서 `requests` 가 항상 1** 로
> 남았다(예산이 영영 발화하지 않던 원인). 아래 튜닝 쿼리는 그 시점 이전 데이터엔 쓸 수 없다.
**예산 튜닝 쿼리** — 차단이 나기 시작하는 요청 수 분포를 보고 상한을 조정:
```sql
-- 종료 사유별 분포(최근 7일): budget 이 대다수 + block 0 이면 예산을 1씩 올려볼 수 있고,
-- block 이 보이면 그 세션들의 requests 최솟값보다 예산을 낮게 유지한다.
SELECT end_reason, count(*), avg(requests)::numeric(5,1) AS avg_req, min(requests), max(requests)
FROM ip_session WHERE created_at > now() - interval '7 days'
GROUP BY end_reason ORDER BY count(*) DESC;
```
---
## 6. `proxy_port` — 프록시 포트(IP 세션) 임대 장부
한 DECODO 계정을 **여러 워커 프로세스**가 나눠 쓰므로 임대 상태를 DB 에 둔다(인메모리면 서로의
임대·차단을 몰라 같은 IP 를 동시에 잡거나 태운 IP 를 곧바로 재사용한다).
| 컬럼 | 뜻 |
|------|-----|
| `host`, `port` | PK. 게이트웨이 + 포트 = sticky IP 세션 1개 (같은 번호라도 게이트웨이가 다르면 다른 IP) |
| `owner` | 현재 임대자(`소스-PID-워커`) |
| `leased_until` | 임대 만료(=sticky 수명). 프로세스가 죽어도 이 시각이 지나면 자동 회수 |
| `rest_until` | 휴식(선제 회전) 만료 — 탄 게 아니라 쉬는 것, 소진 시 가장 먼저 회수 |
| `cooldown_until` | 쿨다운(차단) 만료 |
| `last_used_at` | LRU 회전 기준 — 가장 오래 안 쓴 포트부터 배정 |
| `use_count`, `burn_count` | 누적 임대·차단(상습 불량 IP 슬롯 식별) |
```sql
-- 게이트웨이별 현황(고갈 점검)
SELECT host,
count(*) FILTER (WHERE leased_until > now()) AS 임대,
count(*) FILTER (WHERE rest_until > now()) AS 휴식,
count(*) FILTER (WHERE cooldown_until > now()) AS 쿨다운,
sum(burn_count) AS 누적차단
FROM proxy_port GROUP BY host;
```
## 스키마 생성/관리
- 개발·테스트: SQLAlchemy 모델에서 `create_all`로 자동 생성.
- DB 접속(로컬): `psql -h 127.0.0.1 -U postgres -d lps_db` (자세한 쿼리는 [운영 가이드](operations.md)).
View Git Blame Copy Permalink