StellaOps.Vulnerabilities hub — detailed design (v2)

Status: Accepted design baseline (revision 3, 2026-07-22 — tracks ADR-039 rev-4 owner approvals: database stellaops_vuln, rebuild-from-scratch method, early decommission before ingest, no parity vs v1, registry-based doctor model. Execution windows still gate runtime work.) Evidence: investigation (all counts/widths measured 2026-07-22). Sprint: docs/implplan/SPRINT_20260722_001_Concelier_vulnerability_data_plane_separation.md.

1. Why the current store is 91 GB — the simple version

The actual knowledge is small. All raw source documents together are ~6 GB. The rest is multiplication:

1. The same VEX statement is stored ~6 times. Raw document → normalized claim (vex.claims) → a derived VexHub copy (vexhub.statements) → one observation row for the claim → a second observation row for the VexHub copy → an evidence-ref row per observation. 3.5M statements become 14M+ rows before aggregation.
2. Every copy repeats fat JSON (aliases, references, “facts”) instead of pointing at shared rows. An observation averages 1,400 B — two thirds of it JSON duplicated from elsewhere.
3. Every (CVE, product) gate decision stores a ~3.5 kB JSON essay explaining itself — 5M rows × 3.5 kB ≈ 17 GB of explanations, while its actual readers (Policy, CLI) read five short columns.
4. Indexes double everything. vexhub.statements: 1.9 GB of data under 4.0 GB of indexes (13 of them). The four projection tables carry 20+ GB of indexes.
5. Churn, not just size: sweeps rewrite unchanged rows on every pass (dead tuples + WAL), and the retention worker deletes the wrong rows and leaves aggregates stale — so the store grows and degrades at the same time.

Net effect: ~600 bytes of real fact costs ~15 kB stored. The v2 hub stores each fact once, keeps provenance as narrow references, and derives everything else reproducibly.

2. The hub in one paragraph

StellaOps.Vulnerabilities (merged Concelier + Excititor + VexHub; deployables vulnerabilities-web + vulnerabilities-worker) owns the single database stellaops_vuln on the dedicated stellaops-postgres-vulndata cluster. It is a tenant-free global knowledge hub: source-connector plugins stream advisories/VEX/distro data in; mirror plugins import/export offline bundles; lookup APIs answer “what is known about this CVE/purl?”; an outbox feeds control-plane projections. Per-tenant effective decisions (the successor of vuln.issue_gate_decisions) move to Policy’s own database, built from hub events plus tenant overlays. Source policy (tiers, removals — Cisco/MSRC/GHSA obsoleted; KEV/ACSC/CERT-In dropped as region-locked; Tier 0 credential-free) is defined in connectors-architecture.md. EPSS relocates into the hub from the Scanner schema (P14 — consolidation review §3).

3. Data model

Identities (ADR-039 D4):

• Fact identity: fact_id = sha256(issuer_id | source_statement_id | vulnerability_id | product_key | applicability_hash). source_statement_id = stable upstream statement identity (OpenVEX statement id / CSAF tracking id / advisory edge id, deterministic fallback as in the current ResolveSourceStatementId). Disjoint applicability ranges are distinct facts. Revision hash is a value, not identity.
• Issue identity (aggregation): (canonical vulnerability_id, canonical product_key) — no tenant dimension in the hub.

Rules: a claim and its derived distribution statement are one fact with two provenance records; independent issuers stay independent facts; tombstones follow completed source generations, never age; raw documents are immutable and content-addressed; the hub never stores tenant data (P12).

Hard cases are pre-resolved in the design-challenges register: canonical product identity + applicability model (DC-01), applicability-hash canonicalization (DC-02), CSAF product-tree fan-out control (DC-03), alias graph + issue re-keying without touching fact identity (DC-04), and bytea(32) hash keys (DC-05). Tenant-authored VEX is a control-plane overlay with read composition in Policy’s API (DC-10); release gates never synchronously depend on this database (DC-11 / ADR-039 P15).

3.1 Schemas inside stellaops_vuln (one owner, so internal schemas are fine)

Deliberately absent from the hub: tenant columns/RLS, per-tenant gate decisions, verbose gate explanation JSON (DC-31: derived on demand, nowhere stored), the observation/evidence-ref double layer (facts + provenance replace it), rebuild timestamps in hot-row equality — and (round 15) sbom_registry / sbom_canonical_match: SBOM↔advisory match results are artifact-scoped consumer data, owned by the Findings family in the control plane (computed there on the corpus artifact from sbom.uploaded events; X18/X19). The exploited-in-the-wild attribute (kev-class flags) is a hub fact attribute delivered via corpus artifact + events — its identified consumer is Findings’ risk aggregation.

3.2 Control-plane side (Policy-owned, in stellaops_policy)

Matcher input: Policy consumes the generation-stamped compact corpus artifact (DC-30) via the VulnDb.Compact reader with atomic A/B generation swap — no advisory-row tables in stellaops_policy; online and offline matching share one code path.

CLI compact vuln-db export reads the hub’s global corpus API (it exports global knowledge; the current implementation reading tenant-scoped gates via SQL was a category error).

4. ERD (hub)

erDiagram
    RAW_DOCUMENT ||--o{ FACT_PROVENANCE : "digest referenced by"
    FACT_CURRENT ||--o{ FACT_PROVENANCE : "1..n origins"
    FACT_CURRENT ||--o{ FACT_REVISION : "history"
    FACT_CURRENT }o--|| ISSUE_LINKSET : "aggregates into"
    ISSUE_LINKSET ||--o{ OUTBOX : "change events"
    FACT_CURRENT ||--o{ DIST_STATEMENT : "rebuildable distribution view"

    FACT_CURRENT {
        text fact_id PK
        text issuer_id
        text source_statement_id
        text vulnerability_id
        text product_key
        text applicability_hash
        jsonb applicability
        text status
        text justification
        text severity
        text fixed_version
        text revision_hash
        timestamptz first_seen
        timestamptz last_seen
        timestamptz tombstoned_at
    }
    FACT_PROVENANCE {
        text fact_id FK
        text origin_kind "connector_claim | derived_distribution | external_submission | advisory_edge | distro"
        text origin_ref
        text document_digest
        text verification_status
        text signing_key_fingerprint
    }
    ISSUE_LINKSET {
        text vulnerability_id PK
        text product_key PK
        bigint fact_count
        jsonb status_summary
        jsonb severity_summary
        text content_hash
    }

5. Data flow

flowchart LR
    SRC["source connector plugins\n(advisories, VEX, distros)"] -->|stream| ING["ingest: raw + DTO"]
    MIRIN["mirror import plugin"] --> ING
    ING --> NORM["normalize → facts + provenance\n(one txn: fact + outbox)"]
    NORM --> CONS["consensus linksets\n(leased projector, no-op guarded)"]
    NORM --> DIST["distribution view (rebuildable)"]
    DIST --> MIROUT["mirror export plugin"]
    CONS --> API["lookup APIs: vuln / VEX / consensus / corpus export"]
    API --> CLI["stella vuln-db export"]
    API --> UI["Console: sources & consensus"]
    CONS -->|outbox events| POL["Policy projector (own DB):\ntenant gates + overlays + explanations"]
    POL --> GATES["release gates / findings"]

6. Reconciliation protocol (unchanged in substance, now hub-internal + hub→Policy)

1. One source transaction commits fact revision/tombstone + ordered outbox row; provider runs write generation open/close markers; only a closed generation authorizes absence-tombstones.
2. Each projection stream (consensus, distribution, Policy’s gate projector) has one leased writer with a fencing token; the token is checked inside the destination transaction.
3. One destination transaction applies content-guarded upserts (… DO UPDATE … WHERE target.revision_hash IS DISTINCT FROM excluded.revision_hash OR tombstone changed), rebuilds exactly the affected linksets, and advances the checkpoint. Failure rolls back all of it.
4. Replay is physically idempotent: zero hot-row versions, bounded checkpoint/audit writes.
5. Deletes: removing one provenance origin keeps the fact while another live origin remains; removing the last tombstones the fact and rebuilds its linkset in the same transaction; disjoint ranges never touch each other. There is no separate retention sweep in v2.
6. Restart/failover: resume from the durable checkpoint; a second instance blocks on the lease; an expired holder is fenced by its stale token.

The event contract (envelope + versioning), transport + inbox dedup, corpus-readiness contract, and linkset rebuild backpressure are specified in DC-06…DC-09 of the design-challenges register.

7. Build & cutover plan (fresh start — replaces side-by-side backfill)

One step per sprint-sized unit; the program deliberately spans many sprints (SPRINT_20260722_001/003–008).

There is no old-vs-new parity or sanity comparison: the old data is deleted before ingest (D8) and the estate is alpha — the owner explicitly waived canonicalization-drift concerns. Coverage gates compare against upstream source inventories only. After B7 there is no old plane to roll back to; recovery is fix-forward + re-ingest (desk-check T11/T12).

8. Router/gateway and Console migration (explicit steps)

Gateway (src/Router/StellaOps.Gateway.WebService/appsettings.json route config + the route-config test suite):

1. Add route group /api/vulnerabilities/v1/* → vulnerabilities-web, with the identity-envelope and scope requirements carried over (advisory:read, vex:read, vuln:* families unchanged — scopes name capabilities, not services, so the catalog does not change).
2. Old prefixes (Concelier jobs/LNM /api/v1/lnm/*, /jobs/*, Excititor, VexHub) are replaced in the B7 execution window — their upstreams cease to exist with the old plane, so there is no dual-run or alias-metric program (owner D8). A thin alias survives only where an external contract (older CLI builds, mirror consumers) demands it, each with a named owner and sunset.
3. Update the OpenAPI aggregation (Services/OpenApiAggregator.cs) source list; regenerate the published API surface (src/Api governance checks).
4. Add/adjust route-config tests (pattern: ConcelierJobsRouteConfigTests.cs) for the new group and the aliases; remove alias tests when B7 removes the aliases.
5. Known trap: regex routes participate in the identity envelope — verify Bearer forwarding on the new group (past incident: regex route stripped Authorization).

Console (src/Web/StellaOps.Web):

1. Repoint the API client layer (the generated/typed clients for concelier/excititor/vexhub) to the /api/vulnerabilities/v1/* group behind one switch (API base map), not per-component edits.
2. Affected surfaces: Sources & consensus (LNM linksets/observations views), VulnCatalog / Vuln DB pages, advisory/VEX source-catalog pages, dashboard badges/counters, evidence drill-downs that deep-link raw documents.
3. Connector-management UI: remove tiles/config for deleted connectors (D13); add the tier model (Tier-1 regional plugins shown as optional).
4. Empty-state honesty (no-mocks rule): during the post-decommission ingest window (B7→B8) every vulnerability surface must show an explicit “corpus rebuilding — N of M sources complete” state driven by the hub’s generation accounting — never fake data, never raw errors.
5. Tests: per-page vitest specs updated alongside (Rule A), layout-guard runs, and the four-persona live gate (tools/scripts/run-console-four-role-live.ps1) as the UI acceptance bar.
6. Sequencing: routes + UI + readers swap together in the B7 window; the four-persona gate runs on the scratch stack before the window and against production after it.

9. Capacity budget (measured basis)

Per-path metrics (P7): rows/bytes per schema, attempted-vs-actual writes, WAL/batch, outbox lag, checkpoint age, lease health, per-source quotas, volume + host headroom; shared guard pauses all writers before the floor.

10. Theoretical validation (desk-check of the plan)

Each scenario walked against the design; the invariant column names the mechanism that holds it. These become the acceptance walkthroughs of the implementation sprints.

Residual risks the desk-check cannot discharge (tracked in sprints): re-ingest wall-clock on upstream rate limits (measured by the B3 scratch rehearsal before the production window), and the release-gate posture while the projection is empty (fail-open-with-warning vs fail-closed — an owner decision recorded in SPRINT_20260722_008 before the B7 window). Purl-drift parity was explicitly waived by the owner (alpha estate, fresh ingest).

11. Test plan

src/Vulnerabilities/__Tests/StellaOps.Vulnerabilities.Persistence.Tests/ (isolated PostgreSQL, frozen fixtures, targeted-run wrapper with ran-vs-suite counts):

Superseded: the rev-1 backfill-parity suite and the 424/424 lineage-repair fixtures (v1 data is not migrated, so its damage is discarded, not repaired).