crawler-lib-dotnet

# crawler-lib-dotnet

AI Summary

Purpose:

• Durable note for the NuGet/.NET package crawler at ~/labrador/crawler/crawler-lib-dotnet.
• Records the 2026-06-05 missing-data investigation and the catalog page/leaf retry fixes.

Key points:

• The crawler uses NuGet V3 catalog flow: catalog index -> catalog page -> catalog leaf.
• TB_COMP_LIB_DOTNET_LIST stores catalog page URLs, not package IDs.
• dataCrawling fetches a page URL, then fetches each leaf @id; the leaf is the metadata source for id, version, dependencyGroups, license*, projectUrl, and published.
• Fixed missing-risk bugs on 2026-06-05: leading whitespace in PACKAGE_UPDATE_URL, empty LIST table not seeded, leaf/page fetch failures being able to close a page as complete, and SOURCE_URL being read from page metadata instead of leaf metadata.
• NuGet package IDs should be treated as case-insensitive identity; preserve display casing if useful, but do not treat casing-only differences as separate packages. BUT NuGet IDs are NOT accent-insensitive (é≠e are distinct packages) — and TB_COMP_LIB_PRODUCT.PRODUCT_KEY collation utf8mb4_0900_ai_ci wrongly folds accents, merging distinct packages into one PK row. See Open Questions (diagnosed 2026-06-30); the NUGET_예외제외.csv "누락" was this merge, not data loss.
• Normal incremental scheduler: every 4 hours (src/ai/labradorlabs/app/main.py, SCHEDULE_INTERVAL_HOURS=4). (2026-06-05 daily→3h, then 2026-06-23 3h→4h to standardize all crawlers at 4h.)
• License fallback policy was narrowed on 2026-06-05: only check/clone GitHub when the NuGet API leaf has no license value; if NuGet provides a license value, do not hit GitHub. libraries.io fallback code was removed.
• Follow-up license fallback change on 2026-06-05: when NuGet has no license and projectUrl is GitHub, the crawler now calls GitHub REST GET /repos/{owner}/{repo}/license once per catalog page/repo and shares that default-branch repo license across same-page versions from that repo. This intentionally avoids tag-specific API calls to reduce rate-limit pressure; clone/licensee remains fallback only when the repo license API returns no license data.
• Dependency rows in TB_COMP_LIB_VERSION_DOTNET_DEPENDENCY remain intentionally disabled in code since commit e0b8086; DEPENDENCIES JSON in the version row is still populated.
• 2026-06-11 (uncommitted): licenseUrl handling added — SPDX URLs

(licenses.nuget.org/<expr>, spdx.org/licenses/<id>) resolve to a license name for normal matching; GitHub licenseUrl routes the existing GitHub license fallback to that repo (instead of projectUrl); any other plain URL is stored in LICENSE only, with LICENSE_IDS matching and the AI license call skipped.

• 2026-06-11 (uncommitted): per-leaf processing extracted to

_processCatalogLeaf(versions_info, ctx); missing-version backfill _collectMissingVersions diffs flatcontainer (v3-flatcontainer/{id}/index.json) against collected+DB versions and collects gaps via registration leaf catalogEntry → catalog leaf (version + license rows only; no product rows). The end-of-batch sort pass now runs for every component (the old len>1 gate skipped single-version updates) and always upserts product LATEST_VERSION = sortVersions(all)[-1], fixing late-backfilled old versions overwriting LATEST_VERSION.

• 2026-06-11 (uncommitted): log-level policy aligned with the ruby crawler —

only confirmed data-loss sites stay error (baseCralwer.setInsertIgnoreDB/ setInsertIntoDB insert failures, which are swallowed so the page can close as processed=1; table name added to the message). Everything else demoted to warning: retryable page/leaf failures (processed=0 requeue), sorting/ LATEST_VERSION correction failures, license/GitHub/AI enrichment, JSON fallbacks, clone cleanup, updatePackageListCrawler, low-level DB (cdbvdb/base raise upward). app/check.py diagnostics CLI untouched.

• 2026-06-17: leaf fetch 재시도 추가 to fix LIST pending (PROCESSED=0) never

draining. Root cause: page completion is all-or-nothing — any single leaf failure sets error_flag=True so the whole catalog page is requeued as PROCESSED=0 (dataCrawling), and getJsonRequests (sw/common.py) had no retry (one requests.get(timeout=30), None on timeout/5xx/429/connection/JSON error). Pages with thousands of leaves hit ≥1 transient failure every run → never complete. Fix: getJsonRequests(url, retries=3, backoff=2) retries transient failures (2/4/6s backoff) but returns None immediately on 404/410. Validated in Docker (built iotcube/dotnetcrawler:0.1.1, ran with live src mount): previously-stuck pages reached PROCESSED=1 and the crawler processed leaves without requeue. Throughput is slow (~0.83 leaf/s, ~55 min/page) due to per-leaf AI license calls — separate from this bug. This partially resolves Open Question #1 (transient case only).

Relevant when:

• Diagnosing missing NuGet packages or versions.
• Working on updatePackageListCrawler, dataCrawling, LIST processing, or NuGet metadata mapping.

Do not read full document unless:

• You need exact missing-data root causes, verification commands, or open gaps.

Linked documents:

• ai/workspace/repos.md
• ai/worklog/2026/2026-W23.md
• Microsoft NuGet catalog docs: https://learn.microsoft.com/en-us/nuget/api/catalog-resource

Open Questions

• [진단완료 2026-06-30, 수정 미실행] PRODUCT_KEY accent-folding 식별자 병합: TB_COMP_LIB_PRODUCT PK (LANGUAGE,REPOSITORY,PRODUCT_KEY)의 PRODUCT_KEY collation이 utf8mb4_0900_ai_ci(accent+case insensitive). NuGet ID는 case-insensitive는 맞지만 accent-insensitive는 아님(é≠e는 별개 패키지). 결과: 발음부호만 다른 두 패키지(cezanne.core@0.0.2 vs cézanne.core@0.0.1, 둘 다 라이브 실존)가 INSERT...ON DUPLICATE KEY UPDATE에서 한 PK 행으로 병합되어 식별자 구별 불가 + 행 오염(키는 accented인데 NAME/LATEST_VERSION은 ASCII twin 값). 데이터 손실 누락은 아님(folded 식별자별 product 행 + 버전 행 0.0.1/0.0.2 둘 다 TB_COMP_LIB_VERSION_DOTNET에 보존); 단 두 twin이 같은 버전번호일 때만 버전 행 덮어쓰기=진짜 손실(빈도 미확인). 규모: dotnet/NUGET 828,947행 중 PRODUCT_KEY 비ASCII 2,397행=충돌 위험군(대문자 717,003행은 정상). 수정안: collation ai_ci→utf8mb4_0900_as_ci(accent-sensitive, case-insensitive 유지). ⚠️ 공용 테이블이라 전 생태계 영향 — 마이그레이션·검증·twin 백필 필요. 사용자 CSV NUGET_예외제외.csv 20건이 이 충돌의 표본. 상세: W26 worklog(2026-06-30).
• [후속, 미작업] 증분 시 패키지 전체 재정렬·재조회 매번: 버전 데이터 재수집은 누락분만(_collectMissingVersions가 flatcontainer↔DB diff)이라 OK지만, 한 배치에서 touched된 패키지마다 flatcontainer 전체조회 + DB 전체 버전 SELECT(:314) + SORT_ORDER 전체 재upsert(:330-338) + product LATEST 보정을 매번 수행. 누락이 없으면 이 재작업을 skip하거나 resync(packages='resync')로 분리하는 게 "있는 건 재수집 안 함" 원칙에 맞음. (전체 catalog walk 시 HTTP/DB 부하 큼.) W26 worklog 감사 참조.
• [적용 완료 a1aafbc, 미푸시] insert 행별-폴백 추가: 기존엔 setInsertIntoDB/setInsertIgnoreDB가 배치 insert 실패를 swallow→페이지 processed=1로 닫혀 배치 행 유실(한 행 위반이 1000건 배치 전체를 날림). ruby _insertChunked 패턴 이식(배치 실패→행별 재시도→불량행만 skip(error), 나머지 적재). executeSql의 nullify-retry 후 최종 raise를 폴백이 받음(cdbvdb 미변경). unittest 33 OK. 잔여: 지속 outage 시 전 행 실패→무성 손실(ruby와 동일 성질). 조사: W26 worklog.
• Should the LIST worker implement a retry ladder or processing status like the Go V2 crawler instead of leaving transient failures as PROCESSED=0? Partially addressed 2026-06-17: getJsonRequests now retries transient leaf failures, so transient blips no longer requeue a whole page. Still open for the permanent case — a leaf that is genuinely 404/410 still sets error_flag=True and requeues the page forever (no leaf-level partial progress / skip). Full fix needs per-leaf completion state, not page-level all-or-nothing.
• Should dependency table insertion be re-enabled, or is version-row DEPENDENCIES JSON sufficient for downstream consumers?
• Should PackageDelete leaves be represented as deprecated/unlisted instead of ordinary version rows?
• Should GitHub clone/licensee fallback be fully removed after validating that default-branch GitHub license API coverage is good enough for .NET packages?
• No live DB/NuGet integration test was run in the 2026-06-05 fix session; only mocked unit coverage and byte-compile were run.

Details

Repo Info

• Repo ID: crawler-lib-dotnet
• Local path: ~/labrador/crawler/crawler-lib-dotnet
• Language: Python 3 crawler
• Ecosystem: NuGet (LANGUAGE='dotnet', REPOSITORY='NUGET')
• Tests:

- python3 -m unittest discover -s tests -v - python3 -m compileall src tests - git diff --check

NuGet Catalog Source-of-Truth

Use NuGet V3 catalog as an append-like event source:

1. Catalog index: https://api.nuget.org/v3/catalog0/index.json
2. Catalog page: each index items[*].@id.
3. Catalog leaf: each page items[*].@id.

Microsoft documents the hierarchy as index, page, and leaf. Index page entries do not inline leaves; page item entries contain only minimal information and leaf @id; the leaf contains package metadata such as id, version, dependencyGroups, projectUrl, license fields, and published.

NuGet package ID identity is case-insensitive. Microsoft NuGet search docs state that exact packageid matches are case-insensitive. For crawler/storage design, this means Newtonsoft.Json and newtonsoft.json should be considered the same PRODUCT_KEY identity even if the crawler preserves the original casing returned by NuGet metadata.

Missing-Data Root Causes Fixed on 2026-06-05

1. Catalog index URL had leading whitespace

File: src/ai/labradorlabs/input/crawlerInfo.py

Before:

• PACKAGE_UPDATE_URL started with a space.
• In requests, that can fail before any HTTP request is made, so incremental catalog page discovery can silently do nothing.

Fix:

• Removed the leading whitespace.
• Regression: test_package_update_url_has_no_leading_whitespace.

2. Empty LIST table could not seed itself

File: src/ai/labradorlabs/crawler/dotnetCrawler.py

Before:

• updatePackageListCrawler selected the newest LIST row first.
• If LIST was empty, it logged "No package list found in database" and returned before reading NuGet catalog index.
• A fresh or truncated LIST table would stay empty, causing the worker to have no page URLs.

Fix:

• Empty LIST now means initial seed mode: fetch catalog index and insert all page URLs.
• Regression: test_update_package_list_seeds_catalog_when_list_table_is_empty.

3. Leaf fetch failures could close a page as complete

File: src/ai/labradorlabs/crawler/dotnetCrawler.py

Before:

• If a catalog leaf @id returned None or had an unexpected JSON shape, the loop continued.
• error_flag stayed false, so the catalog page was marked PROCESSED=1.
• Result: versions on failed leaves were omitted permanently unless the page was later requeued by another catalog index update.

Fix:

• Leaf fetch/shape failures set error_flag=True.
• A page with any leaf failure remains retryable with PROCESSED=0.
• Version-level exceptions record exactly one retry status for the page.
• Regressions:

- test_leaf_fetch_failure_keeps_catalog_page_retryable - test_version_exception_records_one_retry_status_for_catalog_page

4. Page fetch failures could leave status unpersisted

Before:

• Some early continue branches appended a status but skipped the bottom-of-loop insertPackageList.
• If this happened on the last page in the batch, the status update could remain only in memory.

Fix:

• Page fetch failure or invalid page shape is immediately written as PROCESSED=0.
• Regression: test_catalog_page_fetch_failure_is_recorded_for_retry.

5. Product SOURCE_URL was read from page item metadata

Before:

• product_dict['SOURCE_URL'] used self.component.getGitUrl(item_info).
• A catalog page item only has minimal page-level metadata; projectUrl lives on the leaf.
• Result: product SOURCE_URL was commonly NULL even when NuGet leaf metadata had projectUrl.

Fix:

• Read SOURCE_URL from versions_info (the leaf).
• Regression: test_product_source_url_comes_from_catalog_leaf_metadata.

Remaining Known Gaps

• Dependency table writes are disabled:

- Code block that builds dependency_list and calls insertDependency is commented out. - This was committed as dependency-table collection being disabled (e0b8086). - Version rows still include DEPENDENCIES JSON.

• Retry semantics are still primitive:

- PROCESSED=0 keeps failures retryable but can cause repeated immediate retries if a page/leaf has a persistent bad shape or outage. - A Go-style retry ladder (30-34) or a processing status would be safer for multi-worker or persistent-failure operation.

• LIST table grain is catalog page URL, not (package, version) event grain:

- This follows the current code shape but means reprocessing a page replays all leaves in that page. - Upsert idempotency must be relied on for duplicate catalog leaf processing.

GitHub License Fallback Policy

• Primary source is still NuGet catalog leaf license metadata. When NuGet has

a license value, the crawler does not call GitHub.

• If NuGet has no license and projectUrl points to GitHub, call GitHub REST

GET /repos/{owner}/{repo}/license for the default branch. Do not make tag-specific license API calls in the main fallback path.

• Cache GitHub repo license results per catalog page/repo. This prevents

repeated API calls for one NuGet page that contains many packages from the same repository.

• Use the API response license.spdx_id/license.name to populate version

license records, decode the API content field for LICENSE_AI input, and store ORIGINAL_DATA.GITHUB.LICENSE_API.ref as None for the default-branch API result.

• This trades version-specific license precision for much lower GitHub API

volume, per the 2026-06-05 user decision.

• Existing clone/tag/licensee detection remains fallback only when the GitHub

license API returns no license data. Clone cleanup happens once per catalog page/repo, not per leaf.