RHEL VEX Vulnerability Collection — Greenfield Design

# RHEL VEX Vulnerability Collection — Greenfield Design

AI Summary

Purpose:

• Design specification for rebuilding RHEL OS-package vulnerability collection

on Red Hat CSAF/VEX from scratch. The existing os_pkg_vuln/rhel_crawler implementation is being discarded, not refactored; this page is the design of record for the replacement, including the table structure 현욱 owes the AI engine team 협의.

• **Confluence 4149674090 (VEX를 이용한 RHEL 취약점 수집 방법) is the base, not

a reference.* It decides what* to collect; this design implements it and answers the items it left as "검토 필요 / 결정 필요". Anything proposed against it is raised as a decision item rather than changed unilaterally. The published Korean page (DT/4209377355) presents the same design as tables and judgment rules in eight sections (구성, 구독과 스트림, CPE 사전, 취약점 본체, 문서 수집 상태, 판정 — including three subscription-judgment scenarios in 6-5 — 수집, 결정 필요); since 2026-08-25 its diagrams render as Mermaid macros with source in expands.

Key points:

• Every number in this page is measured against the full VEX corpus

(csaf_vex_2026-08-09.tar.zst, 273 MB compressed / 16 GB extracted / 63,152 CVE documents), not estimated.

• The whole design rests on one rule: **product_tree.relationships resolves a

product_id to its product_name node, and that node's CPE is the only source of OS version, subscription channel and repository stream.** Measured resolution coverage: 0 unresolved product_ids out of ~7.15M product_status entries. No string guessing anywhere in the pipeline.

• Grain: one row per (CVE, CPE, PACKAGE_NAME, MODULE_NAME, MODULE_STREAM).

Architecture is dropped, which collapses 7.15M raw entries to 4.87M rows. No synthetic minor-version fan-out exists in this design.

• Row counts by scope: strict per the Confluence stream allowlist = 4,866,082;

including ::server/::computenode/::as/::es so RHEL 4–7 servers are covered = 5,177,949. Excluding known_not_affected removes ~2.32M of either. Decided 2026-08-25 (user): server streams are in (scope B) — the working row count is 5,177,949 (~518만).

• Physical model: 3 tables. The CPE string is the key, matching the source

document's own CPE -> package -> fixed version model; an earlier draft with surrogate keys was dropped because the two extra concepts cost more in explainability than the ~200 MB they saved. A dedicated product_id → CPE mapping table was considered and dropped the same day (2026-08-25, user): judgment never sees product_ids and per-row SOURCE_PRODUCT_ID already preserves provenance in gathering.

• Two conflict rules had to be measured rather than assumed. Within one grain

key, 401 cases carry more than one VEX status — a hard primary-key collision that needs a parse-time precedence rule. And 20,176 fixed grain rows carry more than one candidate EVR, split 80.2% release-only / 19.5% version / 0.4% epoch, so the design stores both the first and the latest fixed EVR instead of silently picking one.

• The serving DB is the binlog master that ships to customer on-premise servers,

so write volume there is a product constraint, not just a database one. Measured on republished documents: only 22.7% of a document's rows actually change and 75% of republished documents change nothing at all — so the physical apply must be a diff, not a delete-and-reinsert (≈8.8x fewer row events per day).

• Collection and serving need DIFFERENT table sets. gatheringdb holds all

three tables; labradordb (CDBVDB) gets the CPE dictionary and the fact table minus collection-only columns. The document-state table (hash, parse state, revocation) never leaves gathering, and no serving-side view is needed.

• changes.csv and deletions.csv are both snapshots, not logs: one row

per path, zero duplicates, newest-first. changes.csv lists every currently published document with its last-modified time, so incremental recovery works no matter how far behind the job is — there is no retention cliff. Because a snapshot cannot express removal, deletions.csv (280,797 paths accumulated since 2024-08-20) is the only way to learn a document was withdrawn.

• 5.3% of known_affected references (71,843) are SRPM names (.src). Having

the agent report rpm -qa --qf '%{NAME}|%{SOURCERPM}' removes the need for a binary-to-source mapping table entirely.

• Staleness is solved structurally: parsing recomputes the complete row set for

one document and applies only the row-level diff in one transaction. A CVE moving from known_affected to known_not_affected cannot leave residue, while unchanged rows do not generate customer-facing binlog traffic.

• Cost is small. Measured parse throughput: 786 docs/s single process, 4,526

docs/s on 8 processes — the entire corpus parses in ~1.3 minutes on one core. Full re-collection is dominated by the 273 MB download and the extraction, not by CPU.

Relevant when:

• Implementing the new RHEL VEX collector, writing the OS-package vulnerability

API definition, or reviewing the table structure in 협의.

Do not read full document unless:

• You need the DDL, the scoping rule, the matching spec, or the measured

numbers behind a decision.

Linked documents:

• [[vulnerability-collection.md]] (OSV / Alpine / Debian / Ubuntu siblings)
• [[labrador-platform.md]]
• ai/repo-notes/labrador-scrapers.md, ai/repo-notes/labrador-data-platform.md
• Confluence 4149674090 VEX를 이용한 RHEL 취약점 수집 방법 (design source)
• Confluence 4093706274 OVAL 분석 (per-distro capability matrix)
• Confluence 4191846604 AI엔진팀 협의(2026-08-04) (deadlines, ownership split)
• Confluence DT/4209377355 [os 패키지 취약점 - v4] Red Hat — the Korean design

page published from this document (child of the v4 hub page 4209180746)

• ai/sources/confluence/2026-08-20-rhel-vex-redesign.md — current Confluence

capture and portfolio evidence boundary

• ai/sources/sheets/2026-08-25-vex-manual-collection-verification.md — the

8-CVE / 424-row manual verification sheet (regression baseline; full-row design comparison recorded there)

• Confluence DT/4149674152 [리뷰] Red Hat OS 패키지 취약점 수집 설계 비판 및 개선안

(prior review that independently argued for the layered model)

• Confluence DT/4222189689 [os 패키지 취약점 - v4] 멀티 OS 공통 스키마 방안 —

multi-OS extension options (internal channel key vs per-OS native ids vs single table) and the Ubuntu OpenVEX/OSV research (2026-08-26)

• ai/sources/web/2026-08-26-ubuntu-vex-osv-format.md — Ubuntu VEX/OSV format

facts with verified example strings

Open Questions

These change the design output and belong in the 협의, not in the code.

1. **~~Repository stream scope.~~ Decided 2026-08-25 (user): include the

RHEL 4–7 server streams** — ::server / ::computenode / ::as / ::es (24,657 + 12,115 + 4,166 + 4,022 product nodes), i.e. scope B, 5,177,949 rows. Desktop streams (::client 16,864, ::workstation 14,597, ::ws 3,913, ::desktop 3,237, ::client_workstation 1,580) stay excluded. Supporting evidence beyond the ServerCare RHEL 7 / CentOS 7 coverage: the manual verification sheet contains rhel_tus:7.4::server rows (CVE-2019-8325, ruby) that only this scope collects. Confluence page updated (v27/v28).

1. Add-on channels. rhel_extras (276,963 fixed entries) and

rhel_software_collections (344,244 fixed entries) are separate CPE products but install onto a RHEL server (rh-mysql80-*, container-tools, etc.). In or out? Same question for ::crb (10,459), ::highavailability (421), ::resilientstorage (285), ::supplementary (135), ::nfv (5,419).

1. RHEL 7 realtime. RHEL 8+ uses cpe:/a:redhat:enterprise_linux:8::realtime,

but RHEL 7 realtime lives under cpe:/a:redhat:rhel_extras_rt:7, which the product allowlist rejects. Include or accept the RHEL 7 rt gap?

1. Store known_not_affected? 2,315,533 rows — roughly half the table, and

now measurable rather than a matter of taste. Because the grain is exact (CPE + package), a missing row already means "not vulnerable", so these rows can only earn their place by contradicting another statement. Measured, they almost never do: at the exact grain key known_not_affected coexists with known_affected in 45 cases and with fixed in 356; widened to the MAIN family at major level it is 78 and 2,539. Against 2.3M rows, the suppression value is negligible — their real value is explanatory ("왜 이 CVE가 우리 서버에 해당 없는가") and distinguishing "not affected" from "not yet analyzed". Options: keep in the fact table, move to a sibling table the API joins only when asked, or drop. Given section 5.1, note that keeping them also doubles what ships to on-premise servers.

1. CentOS / Rocky / Alma mapping. These have no EUS/AUS/TUS/E4S

entitlement, so only SUPPORT_CHANNEL = 'MAIN' rows are valid for them. Is the mapping done at collection time (a separate OS_TYPE) or at match time?

1. Agent CPE availability. The documented extraction needs

subscription-manager (rct cat-cert /etc/pki/product/*.pem). Unregistered RHEL and all RHEL derivatives will not have it, so the matcher needs a documented fallback (/etc/os-release CPE_NAME → MAIN channel only). Confirm what the agent can actually return. Also decide the degraded behaviour when the agent cannot report enabled modules at all: reporting modular rows anyway reintroduces the v3 false positives, suppressing them hides real findings. Proposal — suppress, and mark the host as "모듈 정보 없음" so the gap is visible rather than silent.

1. ~~Match key: CPE string or OS version?~~ Settled by measurement — see

section 5.3. Neither: the key is (OS_MAJOR, SUPPORT_CHANNEL) derived from the host CPE, expanded to the repo streams the host has. What remains open is only whether the agent can read the product certificates on every fleet host.

1. Gathering → serving publish mechanism. Today the same crawler is simply

pointed at either database via DAG parameters, which is why the two copies hold 3,709,299 and 4,167,167 rows. v4 needs an explicit answer: keep that, or build once in gathering and publish a verified diff. Section 5.1 explains why this is a customer-facing question and not only an internal one.

1. Cutover method and retention for the current serving table.
2. Multi-OS schema and the CPE-as-PK objection (opened 2026-08-26). The

v4 design passed review, but with the comment that CPE is an external vendor identifier and should not be the fact-table PK when the same shape extends to other OSes (Ubuntu/Debian have no CPE; the module concept is RHEL-only). Production already collects 14 OS families whose de-facto PK is (OS_TYPE, OS_VERSION, PACKAGE_NAME, CVE) with no vendor strings (measured 2026-08-26), so Confluence DT/4222189689 [os 패키지 취약점 - v4] 멀티 OS 공통 스키마 방안 (child of the RHEL page) proposes extending that house key to (OS_TYPE, OS_VERSION, CHANNEL, PACKAGE_NAME, MODULE, CVE_ID) (option 1) vs a numeric channel-dictionary key (option 2), with CPE/purl demoted to the dictionary's NATIVE_ID column; split-vs-single fact table and OS_TYPE code unification are open. Ubuntu source research (OpenVEX, purl-based, no CPE, Ubuntu:Pro rail in OSV) is in ai/sources/web/2026-08-26-ubuntu-vex-osv-format.md. Decision belongs to the team meeting; if A or C is chosen, the RHEL v4 rework scope must be estimated.

Details

1. Source corpus (measured 2026-08-18)

Both index files are snapshots, not append-only logs. Measured: neither file contains a repeated path, and both are sorted newest-first. changes.csv is "every document that currently exists, with the timestamp it was last modified"; deletions.csv is "every path that has been withdrawn, with the timestamp it was removed", accumulated since 2024-08-20.

Two consequences:

• There is no retention cliff. The oldest timestamp in changes.csv

(2025-08-22) is simply the last-modified date of the least-recently-touched document, not a window boundary. An incremental job that is years behind still recovers by taking every row newer than its watermark.

• A snapshot cannot express removal — a withdrawn document just stops appearing.

deletions.csv is therefore mandatory, not optional.

Row counts also reconcile: 64,176 paths in changes.csv against 63,152 files in the 2026-08-09 archive. 1,031 of the extra paths are documents published between the archive snapshot and 2026-08-18, and a further 1,878 archived documents were republished in that window — so a bootstrap started from this archive has to fetch about 2,909 documents to reach current state. The 7 files present in the archive but absent from changes.csv are paths that were deleted and later restored.

2. Scoping rule — CPE decides everything

Parse the CPE as cpe:/{part}:redhat:{product}:{major}[.{minor}][::{stream}].

Product allowlist (SUPPORT_CHANNEL in parentheses):

Everything else is rejected: openshift, openstack, jboss*, satellite, ansible_automation_platform, ceph_storage, rhel_ai, enterprise_mrg, quay, service_mesh, and so on. rhel_extras and rhel_software_collections are rejected by default pending open question 2.

Stream allowlist — Confluence-strict vs server-inclusive, both measured:

The delta is entirely in fixed (1,244,667 → 1,556,189); known_affected is identical because the whole-major products (cpe:/o:redhat:enterprise_linux:7) carry no stream and are already in A.

Rejected streams and their node counts, for the record: client 16,864, workstation 14,597, crb 10,459, nfv 5,419, ws 3,913, desktop 3,237, client_workstation 1,580, hypervisor 1,039, aw 732, highavailability 421, resilientstorage 285, fastdatapath 156, supplementary 135, sap 8, sap_hana 8.

3. Parsing algorithm

Deterministic, six steps, no inference from RPM release strings.

1. Walk product_tree.branches recursively; every node carrying

product.product_identification_helper.cpe becomes a product_name_id -> cpe entry.

1. Read product_tree.relationships; each entry gives

full_product_name.product_id -> (product_reference, relates_to_product_reference). This is the authoritative decomposition of the composite product_id into (package NEVRA, owning product). Measured: every product_id appearing in product_status resolves — 0 unresolved out of ~7.15M. A product_id that appears directly as a product_name id (the whole-major "unfixed" bucket, e.g. red_hat_enterprise_linux_9:bubblewrap) resolves through the same map.

1. Look up the owning product's CPE and apply section 2. Reject and move on if

it fails; never fall back to guessing.

1. Parse product_reference in this exact order:

a. split off a trailing ::{module}:{stream} and keep it — this must happen before NEVRA parsing, otherwise the trailing mysql:8.0 corrupts the architecture and release fields; b. apply ^(?P<name>.+?)-(?P<epoch>\d+):(?P<ver>[^-]+)-(?P<rel>.+)\.(?P<arch>[^.]+)$; c. if it does not match, the reference is a bare package name (known_affected / known_not_affected form) — strip a trailing .src and take the whole string as the package name. Measured with this order: 0 NEVRA parse failures among fixed entries.

1. Drop the architecture. Measured collapse: 7,149,555 product_status entries →

4,822,211 distinct grain rows in scope-A terms (32.6% reduction); 3,931,148 grain rows have a single architecture, the rest 2–9. Product references carrying a SHA256 instead of an EVR (container payloads) are skipped — only 73 corpus-wide.

1. Emit one row per (CVE, CPE, PACKAGE_NAME, MODULE_NAME, MODULE_STREAM).

EVR conflict rule. A MAIN CPE carries no minor, so many Z-stream product_ids collapse onto it — cpe:/o:redhat:enterprise_linux:8::baseos maps to 21 distinct product_ids (BaseOS-8.0.0.Z … BaseOS-8.10.0.Z.MAIN.EUS), and cpe:/a:redhat:enterprise_linux:8::appstream to 27. Measured, 20,176 fixed grain rows end up with more than one candidate EVR (12,578 under scope A).

Inspecting them changes the rule. The conflicts are not mostly Z-stream collapse:

and the dominant sample is a single legacy product listing successive builds for one CVE, not several products sharing a CPE:

CVE-2004-1073 | cpe:/o:redhat:enterprise_linux:3::as | kernel
    0:2.4.21-20.0.1.EL    <- ['3AS']
    0:2.4.21-27.0.4.EL    <- ['3AS']
CVE-2004-0079 | cpe:/o:redhat:enterprise_linux:3::as | openssl096b
    0:0.9.6b-16           <- ['3AS']
    0:0.9.6b-16.42        <- ['3AS']

For that shape the semantically correct "patched version" is the earliest build that contains the fix — a host at 2.4.21-20.0.1.EL is patched, and demanding 27.0.4.EL would be a false positive, which is exactly what this project exists to remove.

For the genuine multi-Z-stream shape there is no single correct answer in one row: the earliest under-reports a host still sitting on an older minor, the latest over-reports one that is fully patched on that minor. Comparing an el8_1 build against an el8_10 build with rpmvercmp is not a meaningful ordering in the first place.

Decided 2026-08-25 (user): store the minimum only. An earlier draft kept both a minimum and a FIXED_EVR_LATEST column; the LATEST column was dropped because any build at or after the first fixed build contains the fix, so the minimum alone answers vulnerable/not-vulnerable, and remediation guidance is phrased as "FIXED_EVR 이상으로 업데이트" (customers update via dnf, not to a pinned build):

• FIXED_EVR — the minimum (first fixed) EVR, used for the

vulnerable/not-vulnerable decision. Biased toward fewer false positives, consistent with the project goal. Multi-candidate rows collapse to the minimum at load.

• FIXED_EVR_COUNT — how many candidates were seen; > 1 marks the 20,176

rows (0.4% of fixed), so the collapse stays auditable and the decision is reversible only by re-parsing (raw JSON is kept on disk).

Duplicate product_ids that share a CPE for a different reason — the optional channels (7Server-7.9 and 7Server-optional-7.9 both map to cpe:/o:redhat:enterprise_linux:7::server) — are simply deduplicated. Measured EVR conflict among them in the sampled documents: 0.

Status conflict rule. Dropping the architecture also collapses rows that VEX reported under different statuses. Measured at the exact grain key: fixed + known_not_affected 356 cases, known_affected + known_not_affected 45. Small, but 401 hard primary-key collisions that would abort the load, so the rule must be explicit:

fixed  >  known_affected  >  known_not_affected  >  under_investigation

fixed wins over known_not_affected because the usual cause is one architecture being fixed while another was never vulnerable; keeping fixed lets the host's own EVR decide, whereas keeping not_affected would suppress a real finding. These rows carry STATUS_CONFLICT = 1 so the 401 can be reviewed rather than disappearing.

4. Normalization vocabulary

Closed vocabularies, derived from the measured corpus rather than invented.

VULN_STATUS ← product_status key:

FIX_STATE ← remediations[].category + details for FIXED / AFFECTED (measured occurrence counts across the corpus):

vendor_fix details is free text with many variants, so classify on category and only pattern-match details for the four no_fix_planned / none_available values above, which are stable enumerations.

FIX_STATE for NOT_AFFECTED ← vulnerabilities[].flags[].label:

SEVERITY ← threats[] where category = 'impact' (47,843 occurrences); category = 'exploit_status' (260) is out of scope for now.

Modules: 96 distinct module:stream pairs, 663,205 references. Top: virt:rhel 112,459, mysql:8.0 70,737, container-tools:rhel8 56,086, mariadb:10.3 29,859, mariadb:10.5 28,220, postgresql:15 21,738.

5. Physical model

Three tables. An earlier draft used surrogate PRODUCT_KEY / MODULE_KEY dimensions; those were dropped after re-reading the source document. The source models the data as CPE -> package -> fixed version, so using the CPE string itself as the key matches that model, removes two joins, and removes the two concepts that made the design hardest to explain. The cost is the CPE repeated across ~5.2M rows (~200 MB), which is acceptable against 14.7 GB today.

A fourth table (TB_OS_PKG_VULN_RHEL_PRODUCT, product_id → CPE map) was added and then dropped on 2026-08-25 after discussing it with the user. The facts that motivated it stand — CPE:product_id is 1:N (enterprise_linux:8::baseos alone has 21 product_ids), and the strings are not derivable from each other (AppStream-8.6.0.Z.MAIN.EUS resolves to the MAIN appstream CPE; 7Server-7.9 / 7Server-optional-7.9 share one CPE) — but judgment never sees a product_id (hosts do not report one), customer-facing evidence is ADVISORY_ID, and per-row provenance already survives in SOURCE_PRODUCT_ID (collection-only), so a dedicated table adds upkeep without a consumer. The "do not trust product_id version/EUS tokens" warning stays on the published page (section 3).

The CPE dictionary is small enough (~90 rows) for the API to cache whole, so resolving a host to its candidate CPE list never touches the fact table.

-- CPE 사전
CREATE TABLE TB_OS_PKG_VULN_RHEL_CPE (
  CPE               VARCHAR(64)  NOT NULL,          -- 실측 최대 47
  OS_FAMILY         VARCHAR(20)  NOT NULL DEFAULT 'RHEL',
  OS_MAJOR          TINYINT UNSIGNED     NULL,
  OS_MINOR          TINYINT UNSIGNED     NULL,      -- 구독상품 CPE에만
  SUPPORT_CHANNEL   VARCHAR(10)      NULL,          -- MAIN/EUS/AUS/TUS/E4S/E2S/ELS
  REPO_STREAM       VARCHAR(20)      NULL,          -- none/baseos/appstream/realtime/server
  IS_TARGET         TINYINT(1)   NOT NULL DEFAULT 0,
  SAMPLE_PRODUCT_ID VARCHAR(100)     NULL,
  FIRST_SEEN_AT     DATETIME     NOT NULL,
  LAST_SEEN_AT      DATETIME     NOT NULL,
  PRIMARY KEY (CPE),
  KEY IX_MATCH (IS_TARGET, OS_MAJOR, SUPPORT_CHANNEL)
);

-- 취약점 본체. 컬럼 폭은 실측(패키지명 110 / EVR 62 / CPE 47 / module:stream 25)
CREATE TABLE TB_OS_PKG_VULN_RHEL (
  CPE                   VARCHAR(64)  NOT NULL,
  PACKAGE_NAME          VARCHAR(150) NOT NULL,
  MODULE                VARCHAR(100) NOT NULL DEFAULT '',  -- 'mysql:8.0' 형태, 없으면 빈 값
  CVE_ID                VARCHAR(30)  NOT NULL,
  PKG_KIND              VARCHAR(8)   NOT NULL DEFAULT 'BINARY',   -- BINARY / SOURCE(.src)
  VULN_STATUS           VARCHAR(16)  NOT NULL,   -- FIXED/AFFECTED/NOT_AFFECTED/INVESTIGATING
  FIX_STATE             VARCHAR(48)      NULL,
  FIXED_EVR             VARCHAR(120)     NULL,   -- 판정 기준, 아키텍처 없음
  FIXED_EVR_COUNT       SMALLINT     NOT NULL DEFAULT 0,
  STATUS_CONFLICT       TINYINT(1)   NOT NULL DEFAULT 0,
  SEVERITY              VARCHAR(12)      NULL,
  ADVISORY_ID           VARCHAR(30)      NULL,
  ADVISORY_URL          VARCHAR(255)     NULL,
  SOURCE_PRODUCT_ID     VARCHAR(100)     NULL,   -- 대표 제품 product_id. 배포에도 포함(2026-08-25 결정)
  SOURCE_PRODUCT_PKG_ID VARCHAR(300)     NULL,   -- 행을 만든 원문 합성 항목(제품ID:패키지NEVRA), 1~수개 쉼표. 수집에만
  DOC_RELEASE_DATE      DATETIME     NOT NULL,   -- 수집에만
  RECORD_CREATED        DATETIME     NOT NULL,
  RECORD_UPDATED        DATETIME     NOT NULL,
  PRIMARY KEY (CPE, PACKAGE_NAME, MODULE, CVE_ID),
  KEY IX_CVE (CVE_ID),
  KEY IX_PKG (PACKAGE_NAME),
  KEY IX_STATUS (VULN_STATUS)
);

-- VEX 문서별 수집 상태 (수집 전용)
CREATE TABLE TB_OS_PKG_VULN_RHEL_DOC (
  CVE_ID               VARCHAR(30)  NOT NULL,
  FILE_PATH            VARCHAR(64)  NOT NULL,
  SHA256               CHAR(64)     NOT NULL,
  DOC_STATUS           VARCHAR(12)      NULL,
  CURRENT_RELEASE_DATE DATETIME     NOT NULL,
  HAS_OS_PRODUCT       TINYINT(1)   NOT NULL DEFAULT 0,
  IS_REVOKED           TINYINT(1)   NOT NULL DEFAULT 0,
  PARSE_STATE          TINYINT      NOT NULL DEFAULT 0,   -- 0 대기 / 1 완료 / 9 오류
  PARSE_ERROR          TEXT             NULL,
  EMITTED_ROWS         INT          NOT NULL DEFAULT 0,
  RECORD_CREATED       DATETIME     NOT NULL,
  RECORD_UPDATED       DATETIME     NOT NULL,
  PRIMARY KEY (CVE_ID),
  KEY IX_STATE (PARSE_STATE, CURRENT_RELEASE_DATE)
);

Design notes:

• The PK reads directly as the source document's model: one CPE, one package,

one module condition, one CVE. The source document's own bubblewrap example is three rows differing only in CPE.

• MODULE is one column (mysql:8.0) rather than a name/stream pair, because

it is only ever compared whole against the agent's enabled-module list.

• Match query is `WHERE CPE IN (<5~10 from the dictionary>) AND PACKAGE_NAME IN

(<installed>)`, a clustered range scan.

• IX_CVE supports the document-scoped diff (read the current row set for one

CVE). Average 4.87M ÷ 36,130 ≈ 135 rows per CVE.

• No ARCHITECTURE, no OS_VERSION string, no per-row references JSON. The

advisory id and URL replace the JSON blob; the old design's 10.4 GB for 3.7M rows was mostly that blob.

• FIXED_EVR_COUNT and STATUS_CONFLICT keep the collapsed cases auditable and

let the decision rule change without re-collecting.

• The dictionary stores every Red Hat CPE encountered, not only in-scope

ones, with IS_TARGET marking the allowlist — otherwise a newly added Red Hat product cannot be noticed.

Expected size: ~4.87M rows at roughly 150–200 bytes/row plus indexes → on the order of 1.5 GB, against 14.7 GB today. Confirm on the first trial load.

5.1 Collection vs serving table split

gatheringdb (data part) and labradordb / CDBVDB (AI analysis engine part) serve different jobs, so they do not get the same tables. This follows the v3 convention where columns were annotated "수집DB에만 필요한 컬럼".

Collection-only columns: SOURCE_PRODUCT_PKG_ID (SOURCE_PRODUCT_ID ships to serving too — decided 2026-08-25), FIXED_EVR_COUNT, STATUS_CONFLICT, DOC_RELEASE_DATE.

For reference, today's serving copy in labradordb is TB_OS_PKG_VULN_RHEL at 4,167,167 rows / 9.9 GB, alongside TB_CONTAINER_OS_VULN_V2_RHEL (1,555,420 rows / 464 MB) which the container scan path uses. Note that the two copies do not agree with the gathering side (3,709,299 rows) — consistent with them being populated by independent crawler runs with no delete path.

The serving DB is a binlog master, and that constrains the design. labradordb on dist-m is the source of the binlogs shipped to customer on-premise servers (see [[distribution-db.md]] and [[bts.md]]). Growth in that binlog stream has already caused a production problem once: low-spec on-premise servers could not import binlogs in time, which then delayed unrelated data, and the fix was to split instances. So write volume on the serving side is a product constraint, not just a database one.

Two consequences for this design:

1. The physical apply must be a diff, not delete-and-reinsert (section 6).

~61,600 vs ~7,000 row events per day is the difference between the two, and every one of those events is replicated to every customer.

1. **The bootstrap load must not be replayed into the serving DB as one

transaction stream.** Roughly 4.9M inserts would ship verbatim to every on-premise server. Section 9 covers the options.

How rows get from gathering to serving today is worth stating plainly because the current answer is not a copy step: the os_pkg_rhel_vuln DAG takes db_address (GATH_M_ADDRESS / DIST_M_ADDRESS) and db_schema (gatheringdb / labradordb) as parameters, so the same crawler image is simply pointed at whichever database. That is how the two copies drifted apart. Whether v4 keeps that pattern or introduces a real gathering→serving publish step is decision 7.

No serving-side view is needed now that the fact table carries the CPE directly: the API reads the ~90-row dictionary once, keeps it in memory, and queries the fact table with a CPE list.

5.2 Diagrams

Mermaid sources, kept here so the Confluence page and this document cannot drift. Confluence Cloud converts a ``mermaid`` code block into a native Mermaid diagram macro on publish.

flowchart LR
  subgraph SRC["Red Hat CSAF/VEX 원천"]
    A1["archive_latest.txt / csaf_vex.tar.zst"]
    A2["changes.csv - 현재 문서 스냅샷"]
    A3["deletions.csv - 철회 경로 누적 · 별도 워터마크"]
  end

  subgraph FS["크롤러 파일시스템 /resources"]
    F1["rhel_vex/{year}/cve-*.json"]
  end

  subgraph GDB["수집 DB - gatheringdb"]
    G1["TB_OS_PKG_VULN_RHEL_DOC<br>문서상태 SHA256 PARSE_STATE 철회"]
    G2["TB_OS_PKG_VULN_RHEL_CPE<br>CPE 사전 전체 + IS_TARGET"]
    G4["TB_OS_PKG_VULN_RHEL<br>사실 테이블 전체 컬럼"]
  end

  subgraph DDB["배포·분석 DB - labradordb / CDBVDB"]
    D2["TB_OS_PKG_VULN_RHEL_CPE"]
    D4["TB_OS_PKG_VULN_RHEL<br>수집전용 컬럼 제외"]
  end

  subgraph SVC["AI분석엔진 / ServerCare"]
    P2["에이전트 수집값<br>CPE 활성모듈 kernel-rt<br>설치 NEVRA SOURCERPM"]
    P1["OS 패키지 취약점 API"]
    P3["판정 결과"]
  end

  ONP["고객 온프레미스 서버<br>binlog 수신"]

  A1 -->|최초 1회| F1
  A2 -->|증분| F1
  A3 -->|철회 감지| G1
  F1 -->|SHA256 비교| G1
  G1 -->|PARSE_STATE=0 문서만| G4
  G1 --> G2
  G2 -.CPE.-> G4
  G2 -->|diff 발행| D2
  G4 -->|diff 발행| D4
  D2 --> P1
  D4 --> P1
  P2 --> P1
  P1 --> P3
  D4 -->|binlog| ONP

erDiagram
  TB_OS_PKG_VULN_RHEL_DOC ||--o{ TB_OS_PKG_VULN_RHEL : "CVE_ID"
  TB_OS_PKG_VULN_RHEL_CPE ||--o{ TB_OS_PKG_VULN_RHEL : "CPE"

  TB_OS_PKG_VULN_RHEL_DOC {
    varchar CVE_ID PK
    char SHA256
    tinyint IS_REVOKED
    tinyint PARSE_STATE
  }

  TB_OS_PKG_VULN_RHEL_CPE {
    varchar CPE PK
    tinyint OS_MAJOR
    tinyint OS_MINOR
    varchar SUPPORT_CHANNEL
    varchar REPO_STREAM
    tinyint IS_TARGET
  }

  TB_OS_PKG_VULN_RHEL {
    varchar CPE PK
    varchar PACKAGE_NAME PK
    varchar MODULE PK
    varchar CVE_ID PK
    varchar PKG_KIND
    varchar VULN_STATUS
    varchar FIX_STATE
    varchar FIXED_EVR
    varchar SEVERITY
    varchar ADVISORY_ID
  }

flowchart TD
  S1["에이전트 입력<br>CPE 모듈 kernel-rt 설치NEVRA SOURCERPM"] --> S2["메이저·구독상품 추출 후<br>사전에서 후보 CPE 목록 산출"]
  S2 --> S3["TB_OS_PKG_VULN_RHEL 조회<br>CPE IN ... AND PACKAGE_NAME IN ..."]
  S3 --> S4{"MODULE 값이 있는가"}
  S4 -->|Yes| S5{"해당 module:stream 활성인가"}
  S4 -->|No| S6
  S5 -->|No| X1["대상 아님 - 제외"]
  S5 -->|Yes| S6{"REPO_STREAM 이 realtime 인가"}
  S6 -->|"Yes 이고 kernel-rt 없음"| X1
  S6 -->|아니오| S6B["여러 행이 걸리면 우선순위 적용<br>1 채널: 마이너고정 > MAIN<br>2 상태: FIXED > AFFECTED"]
  S6B --> S7{"VULN_STATUS"}
  S7 -->|NOT_AFFECTED| X2["영향 없음 - 억제"]
  S7 -->|INVESTIGATING| X3["미보고 - 참고만"]
  S7 -->|FIXED| S8{"설치 EVR 대 FIXED_EVR<br>rpmvercmp"}
  S7 -->|AFFECTED| S9{"FIX_STATE"}
  S8 -->|"설치 >= 패치"| X4["조치 완료"]
  S8 -->|"설치 < 패치"| R1["취약 - 패치 필요<br>FIXED_EVR 이상으로 업데이트"]
  S9 -->|"WILL_NOT_FIX / OUT_OF_SUPPORT_SCOPE"| R2["취약 - 패치 없음"]
  S9 -->|"FIX_DEFERRED / AFFECTED"| R3["취약 - 패치 미정"]

5.3 Conformance to the source design document

Confluence 4149674090 is the design of record for what to collect. Its "취약점 수집" section also links a working spreadsheet (17qUUkBLsysQMHTCZs6rpJUuOSmcBoARuVdxkfZDtjMw) carrying the author's intended 15-column target schema worked through two CVEs — 335 rows, read in full. Both were checked against this design.

The document's own worked example reproduces exactly. Running the section 3 rules over CVE-2024-42472 for bubblewrap:

Every one of those CPEs resolves to exactly one candidate EVR (FIXED_EVR_COUNT = 1), which also corrects the framing in section 3-1: the MAIN-CPE Z-stream aggregation used to motivate the tie-break does not materialise here. The 20,176 conflicts sit elsewhere, mostly in the RHEL 3/4/5 single-product multi-build shape.

Spreadsheet column mapping. All 15 columns are covered:

The sheet settles two things the prose left open. Version holds both 8 and 8.4 — bare major for enterprise_linux, major.minor for rhel_e4s:8.4 — which is exactly the OS_MAJOR / nullable OS_MINOR split used here. And known_not_affected is not hypothetical in the sheet: 97 of its 330 data rows carry it, so the author's working assumption was to collect it. Open question 4 stays open, but that context belongs beside it.

Deliberate departures.

Outside the document's scope, added here: deletions.csv handling, EVR and status conflict rules, match-time row precedence, the physical table design, diff-based incremental apply, and the serving-side binlog constraint. The document scopes what to collect, not how to store, update or publish it.

The "CPE가 직접적으로 필요하지는 않음" line is right, and the earlier draft of section 7 was wrong about it. Measured, the host CPE cannot be a literal join key, for two independent reasons.

Reason 1 — one host CPE does not cover the host's packages. In-scope fixed rows split across repo streams like this:

The document's own agent command output was a single value, cpe:/o:redhat:enterprise_linux:9::baseos. Joining literally on that string matches 27.3% of fixed rows and silently drops the 38.2% under AppStream — where most userspace packages live. Every RHEL host has both BaseOS and AppStream enabled, so the stream half of the host CPE carries no information about what the host can install; it only reflects which product certificate the agent happened to read.

Reason 2 — the part that does matter is the subscription channel. For the same (major, package, module), the MAIN EVR and the entitled channel's EVR differ in 323,741 of 334,206 cases (96.9%). Example: RHEL 8 platform-python for CVE-2007-4559 — MAIN 0:3.6.8-56.el8_9, EUS 0:3.6.8-47.el8_6.4. Judging an EUS host against the MAIN version demands a build that is not in its stream, which is a false positive by construction.

So the matching key is not the CPE string. It is the pair derived from it:

host CPE set  ->  (OS_MAJOR, SUPPORT_CHANNEL)  ->  candidate CPE list
                                                   = all in-scope REPO_STREAMs
                                                     the host actually has

OS_MAJOR is available from /etc/os-release alone. SUPPORT_CHANNEL is the one thing that genuinely requires /etc/pki/product/*.pem, and it is worth requiring precisely because of the 96.9% figure. A host whose subscription cannot be determined must be marked "구독 미확인" and judged against MAIN with that caveat attached — not silently assumed to be MAIN.

6. Pipeline

Four stages, each independently re-runnable.

Feeds. Three index files are published beside the archive. Both CSVs are snapshots keyed by path, not append-only logs (section 1).

Only 7 of the 280,797 deleted paths exist in the current archive (deleted then restored), so these are genuine withdrawals rather than churn. This is the concrete form of the "CVE가 발급됐다가 없어지는 케이스" problem raised in v3.

S1 — raw sync. Bootstrap: archive_latest.txt → download the .tar.zst (273 MB) → stream-decompress into /resources/rhel_vex/<year>/ → then apply changes.csv rows newer than the archive's build date, because the archive is a dated snapshot that already lags. Measured on the 2026-08-09 archive read on 2026-08-18, that catch-up is ~2,909 documents (1,031 new plus 1,878 republished). Finally apply deletions.csv.

Steady state: fetch changes.csv, take rows newer than MAX(CURRENT_RELEASE_DATE) in TB_OS_PKG_VULN_RHEL_DOC, download only those files, then apply deletions.csv rows newer than the same watermark. Because changes.csv is a full current-state snapshot, this converges regardless of how long the job was down; a forced archive resync is an operator convenience, not a correctness requirement.

S2 — document registration. For each file: compute SHA256 locally, upsert into TB_OS_PKG_VULN_RHEL_DOC. If the hash differs from the stored one (or the row is new), set PARSE_STATE = 0. Never key on the hash — one row per CVE, so the table stays at ~63K rows instead of accumulating a row per revision.

S3 — parse and apply as a diff. Select PARSE_STATE = 0 in CURRENT_RELEASE_DATE order, batch by document. Per document, in one transaction: recompute the document's complete row set, read the stored row set for that CVE_ID, and apply only the difference — insert what is new, update what changed, delete what disappeared — then set PARSE_STATE = 1 with EMITTED_ROWS. On exception, PARSE_STATE = 9 with the message; the document is retried next run rather than silently dropped.

The scope is still the whole document, which is what makes withdrawal, status transition and product removal self-correcting — the property the previous pipeline lacked. What changed from the earlier draft is the physical apply: DELETE + re-INSERT rewrites every row of a document even when nothing about it changed, and that difference is not academic.

Measured on 120 documents that were republished between the 2026-08-09 archive and 2026-08-18, comparing the archived version against the current one:

Three quarters of republished documents change no fact row at all — the revision was in scores, notes, or products outside RHEL OS scope. Section 5.1 explains why the ~8.8x difference matters beyond database load.

S4 — revocation. Apply deletions.csv rows newer than the deletion watermark: set IS_REVOKED = 1 on the document row and delete that CVE's fact rows. This needs its own watermark, separate from the changes.csv one — the two files carry unrelated timestamps and a single watermark would skip deletions whenever changes ran ahead.

S5 — dimension upkeep + validation. Upsert any newly seen CPE / module into the dimensions and run the section 8 invariants. A new CPE that does not match the section 2 allowlist is stored with IS_TARGET = 0 and surfaced as an alert, not silently dropped — Red Hat adds products (RHEL 10 rhel_e2s, enterprise_linux_eus:10.0) and the allowlist must be able to notice.

Orchestration: one Airflow DAG, check_running → resolve_db → s1_sync → s2_register → s3_parse → s4_revoke → s5_validate, with max_active_runs=1. A 4-hourly schedule is comfortable against a 268 documents/day change rate; the bootstrap path runs as a manually triggered force_resync variant.

7. Matching specification (input to the API 정의서)

Agent must report:

CPE normalization at match time:

• The host CPE is read for two fields only: OS_MAJOR and SUPPORT_CHANNEL.

The repo-stream suffix on the host CPE is discarded — it reflects which product certificate was read, not what the host can install.

• RHEL ≤ 8.4 reports cpe:/o:redhat:enterprise_linux:8.4:GA (minor included);

RHEL ≥ 8.5 reports cpe:/o:redhat:enterprise_linux:8::baseos. Normalize the old form by discarding the minor and treating it as MAIN (Red Hat solution 6564691).

• SUPPORT_CHANNEL is the only field that requires /etc/pki/product/*.pem,

and it is load-bearing: MAIN and the entitled channel disagree on the fixed EVR in 96.9% of comparable cases. When it cannot be read, judge against MAIN and mark the host "구독 미확인" rather than asserting MAIN.

• Expand a bare major into the candidate set: {:N, :N::baseos, :N::appstream},

plus ::realtime when kernel-rt is installed, plus ::server for RHEL ≤ 7 (subject to open question 1).

• Any entitlement CPE (rhel_eus, rhel_aus, rhel_tus, rhel_e4s) found in

the product certificates is added to the candidate set as well.

Both extra fields come from the same rpm -qa invocation the agent already runs, so the cost is two format specifiers.

The source-RPM item removes the need for a binary-to-source mapping table. Measured, 5.3% of known_affected references in scope (71,843 of 1,368,320) are SRPM names — VEX may say vim while the host only has vim-minimal installed. With SOURCERPM reported, PKG_KIND = SOURCE rows join on the source name and BINARY rows join on the binary name.

Decision order:

1. Build the candidate CPE list. Do not use the host's own CPE string as

the filter. Derive (OS_MAJOR, SUPPORT_CHANNEL) from the host CPEs, then take every IS_TARGET CPE in the dictionary with that major and channel whose REPO_STREAM the host actually has — baseos and appstream always, (none) always, realtime only with kernel-rt, server / computenode / as / es for RHEL ≤ 7. Section 5.3 has the measurements: filtering on the host's literal CPE matches 27.3% of fixed rows and drops the 38.2% under AppStream. The dictionary is ~90 rows, so this step is a memory lookup.

1. Look up (CPE, PACKAGE_NAME) with that list.
2. A row with a non-empty MODULE is gated on the **installed package's

MODULARITYLABEL**, not on the system's enabled-module list. Both sides are name:stream:version:context; compare the first two segments only — the last two identify a specific build, which the EVR comparison already covers.

/etc/dnf/modules.d alone is not sufficient. It answers "which modules does this host have enabled", not "which module did this installed package come from". Measured, multi-module cases split two ways:

In the second shape both rows pass a modules.d gate, so the one installed PyYAML gets compared against both streams' fixed EVRs (…+19598+4a60c7b7 vs …+19644+d68f775d) and the correctly-patched host is reported vulnerable. This is the single largest remaining false-positive source in module handling — 96 module streams, 663,205 references overall.

1. NOT_AFFECTED suppresses.
2. FIXED → compare installed EVR against FIXED_EVR using rpm semantics

(epoch → version → release, rpmvercmp segment rules). Never string compare.

1. AFFECTED + WILL_NOT_FIX / OUT_OF_SUPPORT_SCOPE → report as "패치 없음",

not as "패치 필요". FIX_DEFERRED / AFFECTED → "패치 예정 없음/미정".

1. INVESTIGATING → not reported as a finding.
2. When one package matches rows from several candidate products, resolve in

two steps, in this order: a. Channel. The minor-pinned channel (EUS/AUS/TUS/E4S/E2S/ELS) wins over MAIN, because the host demonstrably holds that entitlement and the channel build is the one it can install. b. Status. Within the surviving rows, FIXED wins over AFFECTED. This is not a corner case: measured 27,079 (major, package, module) combos in the MAIN family carry both a FIXED row (from a repo-stream CPE such as enterprise_linux:5::as) and an AFFECTED row (from the whole-major CPE enterprise_linux:5). Without this rule every one of them would be reported as "패치 없음" even though a patch exists — wrong guidance to the customer and the single largest remaining source of bad output after the parsing fixes. FIXED + NOT_AFFECTED at major level is 2,539 combos and resolves the same way (section 3-1 precedence).

8. Validation

Automated invariants, run after every parse batch and gating the initial load:

• unresolved product_id count = 0
• VULN_STATUS = 'FIXED' with FIXED_EVR IS NULL = 0
• PACKAGE_NAME matching %.src, containing :, or containing -0: = 0
• every fact-row CPE present in the dictionary, and every IS_TARGET = 1

dictionary row matching the section 2 allowlist

• FIXED_EVR_COUNT > 1 share of FIXED rows within tolerance of the measured

baseline (20,176 rows under scope B, 12,578 under scope A)

• STATUS_CONFLICT = 1 count within tolerance of the 401 baseline
• no duplicate grain keys — the 401 status conflicts must have been resolved by

the section 3-1 precedence, not by an arbitrary last-writer-wins

• document coverage: COUNT(DOC WHERE PARSE_STATE = 1) equals the file count,

and IS_REVOKED = 1 documents have zero fact rows

Golden-set documents, each pinned to a property the old pipeline got wrong:

The 2026-08-25 manual verification sheet (ai/sources/sheets/2026-08-25-vex-manual-collection-verification.md, 8 CVEs / 424 rows) is the row-level regression baseline on top of these property tests. A comparison script must forward-fill the sheet's CVE and CPE cells, normalize CVE URLs to ids, correct the 13 status/remediation-swapped rows, and treat the two enterprise_linux:7::client rows as intended exclusions.

Field validation: replay real ServerCare host package inventories through the new table, diff against current findings, and hand-verify a sample against dnf updateinfo or the Red Hat portal. Report the confirmed false-positive reduction as a measured number, not an assertion.

9. Re-collection cost (measured)

Parsing is not the bottleneck; bulk insert of ~5M rows is. Re-collection into the gathering DB is a single-session operation measured in tens of minutes, so it can be repeated freely whenever the scoping decisions change — which is the main reason to keep the raw JSON tree on disk rather than only in the database.

Loading the serving DB is a different problem, because every insert there becomes a binlog event shipped to customer on-premise servers (section 5.1). Options, in increasing order of operational care:

The third is the only one whose volume is bounded by how much the data actually differs, and it is measurable ahead of time by diffing the built table against labradordb.TB_OS_PKG_VULN_RHEL before touching production. Recommend measuring that diff during W36 and choosing on the number, not in advance.

10. Phase plan

Meeting constraints (2026-08-04): API implemented by end of August, mock acceptable if unavoidable; feature complete by end of September. Data collection sits with the data part; the OS-package API sits with the AI analysis engine part; 현욱 brings the table structure to 협의.

The critical path is open question 1 and 4, not the code: both change the row count and the DDL semantics, and both are cheap to re-run once decided because of section 9.