Logical model

51 tables in five clusters. Physical definition is migrations/0001_initial.sql, which is commented section by section. This document explains why the shape is what it is and how to get answers out of it.

1. The clusters 

graph LR
  subgraph Identity
    T[tenant] --- SS[source_system]
    SS --- SC[source_scope]
    SC --- EI[external_identity]
    EI --- EP[external_property]
    XR[extension_registration] --- EE[entity_extension]
  end

  subgraph Portfolio
    P[product] --- PC[product_component] --- C[component]
    C --- CW[component_workflow] --- W[build_workflow]
    W --- WA[workflow_asset] --- A[source_asset]
    P --- PR[product_relationship] --- P
    PY[party] --- RA[responsibility_assignment]
    AL[alias]
  end

  subgraph Evidence
    EV[evidence_artifact] --- CE[claim_evidence] --- CL[claim]
    EV --- VAL[evidence_validation]
    CL --- LENS[scope_lens]
    CL --- AUTH[claim_authority_rule]
  end

  subgraph Audit
    CAMP[audit_campaign] --- CS[campaign_subject]
    CAMP --- CHK[audit_check] --- AR[assessment_result]
    AR --- F[finding] --- ACT[action_item] --- VER[verification]
    SCORE[score_snapshot]
    DEC[decision] --- EXC[exception_grant]
  end

  subgraph Jobs
    JD[job_definition] --- JR[job_run] --- JA[job_artifact]
    SR[sync_run] --- SNAP[source_snapshot]
    SR --- CG[coverage_gap]
    RC[reconciliation_conflict]
  end

  EI -.maps to.-> P
  EI -.maps to.-> C
  CL -.about.-> P
  AR -.about.-> P

2. The shapes the portfolio actually has

The requirements call for many to many everywhere. Concretely, these are all legal and all represented without a special case:

Real situation How it is stored
One component ships in several products Several product_component rows for one component_id
A product is sold standalone and also bundled into another product product_relationship with type bundles, plus the product keeping its own product_component rows
A component has several build pipelines Several component_workflow rows for one component_id
A component has no pipeline at all Zero component_workflow rows. A real state, surfaced in the attention queue, never an import error
One pipeline feeds several different components Several component_workflow rows for one build_workflow_id
One pipeline feeds the same component more than once, for different targets Several rows differing by relationship_type and dedupe_key
One repository backs several products One source_asset, several workflow_asset and component_workflow paths reaching different products
Two sources disagree about a relationship Two edge rows with different source_kind and source_system_id, plus a reconciliation_conflict row. Neither is deleted

Fan in and fan out are the same mechanism read in opposite directions. Nothing in the schema privileges a tree.

3. Scope lenses

The mechanism that lets several organizations disagree about what a product is without blocking the inventory.

scope_lens holds one row per definitional authority. Scope status is not a column on product. It is a claim:

-- Scope of every product under the CRA regulatory lens
SELECT p.canonical_name,
       c.value_text AS scope_status,
       c.source_kind,
       c.valid_from
FROM product p
LEFT JOIN claim c
       ON c.subject_type = 'product'
      AND c.subject_id = p.product_id
      AND c.claim_kind = 'scope.status'
      AND c.status = 'active'
      AND c.lens_id = (SELECT lens_id FROM scope_lens WHERE key = 'cra.regulatory')
ORDER BY p.canonical_name;

A product with no row under a lens is undecided under that lens, which is different from being out of scope. The report states both counts separately.

Disagreement between lenses is a query, not an exception:

-- Products where two lenses disagree about scope
SELECT p.canonical_name,
       l1.key AS lens_a, c1.value_text AS status_a,
       l2.key AS lens_b, c2.value_text AS status_b
FROM product p
JOIN claim c1 ON c1.subject_id = p.product_id AND c1.claim_kind = 'scope.status'
             AND c1.status = 'active'
JOIN claim c2 ON c2.subject_id = p.product_id AND c2.claim_kind = 'scope.status'
             AND c2.status = 'active' AND c2.lens_id > c1.lens_id
JOIN scope_lens l1 ON l1.lens_id = c1.lens_id
JOIN scope_lens l2 ON l2.lens_id = c2.lens_id
WHERE c1.value_text <> c2.value_text;

4. The authority ladder

claim_authority_rule ranks claims. Lower rank wins. Seeded defaults: approved 10, manual 20, observed 40, normalized 45, derived 50.

Two rules the application enforces on write:

  1. A claim of higher rank number never overwrites one of lower rank number. The new claim is stored with status = 'conflicted' and a reconciliation_conflict row is written.
  2. Marking a specific source authoritative for a specific claim kind is an insert with source_system_id set and a rank between 10 and 40. That is how “the catalog is authoritative for display names but not for regulatory scope” is expressed without code changes.

5. Source facts, derived results, and decisions stay distinct

claim.source_kind is the dividing line, and it is never collapsed:

source_kind Meaning
observed Read directly from a source system
normalized The same observation converted to core vocabulary
derived Computed from other claims, carrying derived_from_json and algorithm_version
manual Asserted by the operator, not yet approved
approved Backed by a decision row with an authority and a rationale

An assessment reads claims. It never reads a source directly, so every score can name the claims it used and every claim can name its evidence.

6. Answering the questions that started this

Question Where the answer comes from
How many products do we have product count, always qualified by lens and scope status, because the raw count is not the CRA denominator
How many components component count, plus how many ship in more than one product
How do they relate subject_asset, product_component, product_relationship, rendered by the graph explorer. component_workflow and workflow_asset join the same subjects through a pipeline; the explorer re-expresses those paths as direct edges rather than drawing the pipeline
How does a pipeline flow into a product Walk build_workflow to component_workflow to component to product_component to product, following fan out at each hop
Which products are in scope Scope claims under the named lens, with undecided counted separately from out of scope
Which products are open source derived origin.open_source claims on their components, rolled up through product_component
What is the lifecycle of a product product.lifecycle plus lifecycle.stage claims, and support.end_date for the CRA support period
Do we have an SBOM and is it any good evidence_artifact of kind sbom.*, its evidence_validation rows from ossbomer, and the freshness of the capture
Does it meet NTIA or the CRA profile evidence_validation.policy_key and outcome, stored per profile so several profiles can be answered from one artifact
Where are we overall Campaign progress against a frozen campaign_subject denominator, never against the count of rows discovered

7. Organization and distribution

Two dimensions that are separate from composition, and separate from each other.

Accountability is a hierarchy of parties: division, which is the sub legal entity, then organization at director level, then team. A subject is attached at the most specific level known, and v_subject_org resolves the rest, so a product owned by a team still reports under its division without the division being recorded on the product. Adding a level later is a party with a level, not a migration.

Distribution is where something enters and leaves. Three questions that are routinely collapsed into one and must not be:

Question Where it lives
Where does the code live claim code.hosting: internal, public host public, public host private, vendor
Where does the build run claim build.location: internal pipeline, public host runner, vendor external, manual
Where does the output go distribution rows, one per channel

A component whose source is on a public host, built on that host’s runners, signed internally, and published to a package registry has four different answers, and a model with one flag cannot hold them. The combinations that actually occur include: built internally and published nowhere; built externally by a vendor and imported back as binaries; built on public runners from a public repository; consumed from a public registry and never built at all.

direction is what the CRA cares about. outbound means we make it available, which is what places a product on the market. inbound means we consume it. not_distributed is recorded explicitly as an outbound channel rather than left as an absent row, because an absent row cannot be told apart from a question nobody asked.

is_signed is nullable on purpose. NULL means nobody established it, which is a coverage gap. False means it is known to be unsigned, which is a finding. Collapsing them would turn every gap into a false finding.

channel_type is open vocabulary validated against the registry. A registry nobody has thought of yet needs a registry entry, not a schema change.

8. Deliberate omissions

  • No is_cra_scope boolean. Scope is per lens, and a boolean would erase the lens.
  • No current_score column on product. Scores live in score_snapshot with the policy version that produced them, so history is not overwritten.
  • No cascade deletes. A source deleting an object becomes an observed lifecycle state, not a row disappearing.
  • No enum constraints on open vocabularies. Those are validated against extension_registration so an adapter can add a type without a migration.

Back to top

CRAne. The database is one SQLite file and it is yours.

This site uses Just the Docs, a documentation theme for Jekyll.