Findings Ledger storage and historical schema design

Owners: Findings Ledger Guild • Vuln Explorer Guild
Currency (2026-09-21): the column sketches below are the historical LEDGER-29-001 design, not an executable specification. Current schema authority is the embedded consolidated src/Findings/__Libraries/StellaOps.Findings.Persistence/Migrations/ chain. See retention and custody for current disposal behavior. Re-verify with ConsolidatedFindingsSchemaTests and FindingsTenantLifecycleHandlerTests.

Snapshot storage retired (2026-10-06, PCD-316): forward startup migration 024 removes the unused empty findings.ledger_snapshots table. It refuses to delete historical rows. The snapshot EF entity, mapping and Findings audit actions are withdrawn; there is no current snapshot or time-travel API. Applied migration SQL remains historical authority.

1. Storage profile

2. Historical design sketch — verify columns against the consolidated migration chain

Events are immutable append-only records representing every workflow change. Records capture the original event payload, cryptographic hashes, and actor metadata.

2.1 ledger_events

Constraints & indexes

PRIMARY KEY (tenant_id, chain_id, sequence_no);
UNIQUE (tenant_id, event_id);
UNIQUE (tenant_id, chain_id, event_hash);
CHECK (event_hash ~ '^[0-9a-f]{64}$');
CHECK (previous_hash ~ '^[0-9a-f]{64}$');
CREATE INDEX ix_ledger_events_finding ON ledger_events (tenant_id, finding_id, policy_version);
CREATE INDEX ix_ledger_events_type ON ledger_events (tenant_id, event_type, recorded_at DESC);
CREATE INDEX ix_ledger_events_finding_evidence_ref ON ledger_events (tenant_id, finding_id, recorded_at DESC) WHERE evidence_bundle_ref IS NOT NULL;

Partitions: top-level partitioned by tenant_id (list) with a default partition. The current chain has no monthly retention sub-partitions. PostgreSQL requires the partition key in unique constraints; global uniqueness for event_id is enforced as (tenant_id, event_id) with application-level guards maintaining cross-tenant uniqueness.

2.2 Event types

CREATE TYPE ledger_event_type AS ENUM (
  'finding.created',
  'finding.status_changed',
  'finding.severity_changed',
  'finding.tag_updated',
  'finding.comment_added',
  'finding.assignment_changed',
  'finding.accepted_risk',
  'finding.remediation_plan_added',
  'finding.attachment_added',
  'finding.closed',
  'nis2.incident.opened',
  'nis2.incident.classified',
  'nis2.incident.reported',
  'nis2.incident.closed',
  'cra.incident.opened',
  'cra.incident.classified',
  'cra.incident.reported',
  'cra.incident.closed'
);

Additional types can be appended via migrations; canonical JSON must include event_type key.

2.3 Canonical ledger JSON

Canonical payload envelope (before hashing):

{
  "event": {
    "id": "3ac1f4ef-3c26-4b0d-91d4-6a6d3a5bde10",
    "type": "finding.status_changed",
    "tenant": "tenant-a",
    "chainId": "5fa2b970-9da2-4ef4-9a63-463c5d98d3cc",
    "sequence": 42,
    "policyVersion": "sha256:5f38...",
    "finding": {
      "id": "artifact:sha256:abc|pkg:cpe:/o:vendor:product",
      "artifactId": "sha256:abc",
      "vulnId": "CVE-2025-1234"
    },
    "actor": {
      "id": "user:alice@tenant",
      "type": "operator"
    },
    "occurredAt": "2025-11-03T15:12:05.123Z",
    "payload": {
      "previousStatus": "affected",
      "status": "triaged",
      "justification": "Ticket SEC-1234 created",
      "ticket": {
        "id": "SEC-1234",
        "url": "https://tracker/sec-1234"
      }
    }
  }
}

Canonicalisation rules:

1. Serialize using UTF-8, no BOM.
2. Sort object keys lexicographically at every level.
3. Represent enums/flags as lower-case strings.
4. Timestamps formatted as yyyy-MM-ddTHH:mm:ss.fffZ (millisecond precision, UTC).
5. Numbers use decimal notation; omit trailing zeros.
6. Arrays maintain supplied order.

2.4 NIS2 incident event payloads

The NIS2 incident lifecycle uses the standard ledger.event.v1 envelope with payload schema nis2.incident.ledger.v1. The local Findings Ledger slice owns the replay-safe event shape and hash-chain append behavior for:

• nis2.incident.opened
• nis2.incident.classified
• nis2.incident.reported
• nis2.incident.closed

All NIS2 incident events are written to a deterministic per-incident chain: LedgerChainIdGenerator.FromTenantSubject(tenant, "nis2-incident::<incidentId>"). Callers must supply event IDs plus occurredAt and recordedAt timestamps; the emitter does not create clocks or IDs for these compliance records. The emitter normalizes strings, formats timestamps as UTC milliseconds, and sorts classification criteria, reachability refs, VEX consensus refs, and evidence refs before canonical hashing.

Payload root:

{
  "nis2": {
    "schemaVersion": "nis2.incident.ledger.v1",
    "incident": {
      "id": "incident-001",
      "state": "classified",
      "previousState": "open",
      "timelineState": "OPEN",
      "timelineMilestone": "notification"
    },
    "classification": {
      "significantImpact": true,
      "severity": "high",
      "criteria": ["cross_border", "service_disruption"],
      "impactSummaryRef": "evidence://impact/incident-001",
      "severityClassificationRef": "evidence://classification/incident-001",
      "operatorApprovalRef": "approval://secops/incident-001"
    },
    "correlation": {
      "reachability": [
        {
          "subgraphRef": "reach://tenant-a/subgraphs/a",
          "graphDigest": "sha256:...",
          "subjectDigest": "sha256:...",
          "runtimeEvidenceRef": "runtime://trace/a",
          "replayBundleRef": "cas://replay/incident-001/a"
        }
      ],
      "vexConsensus": [
        {
          "consensusState": "affected",
          "statementRef": "vex://statement/001",
          "documentDigest": "sha256:...",
          "issuer": "vendor-a",
          "justification": "reachable_code_path",
          "lastObservedAt": "2026-04-30T11:00:00.000Z"
        }
      ],
      "evidenceRefs": [
        {
          "key": "severity_classification_ref",
          "ref": "evidence://classification/incident-001",
          "digest": "sha256:..."
        }
      ]
    },
    "report": {
      "milestone": "notification",
      "payloadHash": "sha256:...",
      "signer": "signer://authority/notify",
      "targetIntake": "csirt://de/bsi",
      "sourceLedgerEventRef": "ledger://tenant-a/incident-001/reported-notification",
      "sentAt": "2026-04-30T12:30:00.000Z",
      "evidenceRefs": [
        {
          "key": "operator_approval_ref",
          "ref": "approval://secops/incident-001"
        }
      ]
    },
    "closure": {
      "reason": "final report accepted",
      "closedStatus": "closed",
      "finalReportBundleRef": "bundle://nis2/final-report/incident-001"
    },
    "evidenceRetention": {
      "mode": "incident",
      "evidenceBundleRef": "dsse://evidence/incident-001",
      "snapshotRef": "snapshot://findings/incident-001",
      "retentionPolicyRef": "evidence-locker.incident-mode",
      "retentionExtensionDays": 60
    }
  }
}

nis2.incident.classified requires at least one reachability subgraph ref and one VEX consensus ref so the Article 23 classification can be reconstructed offline without live Graph/VEX queries. nis2.incident.reported carries the Notify timeline milestone, sha256:<64 lowercase hex> payload hash, signer, target intake, source ledger event ref, and milestone-specific evidence refs. Evidence Locker incident-mode retention is cross-linked through both event.evidenceBundleRef and payload.nis2.evidenceRetention.

2.5 CRA incident event payloads

The CRA Article 14 incident lifecycle reuses the NIS2 incident ledger pattern: standard ledger.event.v1 envelope, caller-supplied IDs and timestamps, deterministic per-incident chains, normalized UTC millisecond timestamps, and stable sorting before canonical hashing. CRA events use payload schema cra.incident.ledger.v1 and event types:

• cra.incident.opened
• cra.incident.classified
• cra.incident.reported
• cra.incident.closed

All CRA incident events are written to: LedgerChainIdGenerator.FromTenantSubject(tenant, "cra-incident::<incidentId>"). The payload root is cra; its shape mirrors the NIS2 payload root with CRA classification metadata:

{
  "cra": {
    "schemaVersion": "cra.incident.ledger.v1",
    "incident": {
      "id": "cra-incident-001",
      "state": "classified",
      "previousState": "open",
      "timelineState": "OPEN",
      "timelineMilestone": "vulnerability_notification_72h"
    },
    "classification": {
      "activelyExploited": true,
      "severity": "critical",
      "criteria": ["remote_exploitation", "safety_impact"],
      "impactSummaryRef": "evidence://impact/cra-incident-001",
      "severityClassificationRef": "evidence://classification/cra-incident-001",
      "operatorApprovalRef": "approval://secops/cra-incident-001"
    },
    "correlation": {
      "reachability": [
        {
          "subgraphRef": "reach://tenant-a/subgraphs/a",
          "graphDigest": "sha256:...",
          "subjectDigest": "sha256:...",
          "runtimeEvidenceRef": "runtime://trace/a",
          "replayBundleRef": "cas://replay/cra-incident-001/a"
        }
      ],
      "vexConsensus": [
        {
          "consensusState": "affected",
          "statementRef": "vex://statement/001",
          "documentDigest": "sha256:...",
          "issuer": "vendor-a",
          "justification": "reachable_code_path",
          "lastObservedAt": "2026-04-30T11:00:00.000Z"
        }
      ],
      "evidenceRefs": [
        {
          "key": "severity_classification_ref",
          "ref": "evidence://classification/cra-incident-001",
          "digest": "sha256:..."
        }
      ]
    },
    "report": {
      "milestone": "vulnerability_notification_72h",
      "payloadHash": "sha256:...",
      "signer": "signer://authority/notify",
      "targetIntake": "market-surveillance://eu/cra",
      "sourceLedgerEventRef": "ledger://tenant-a/cra-incident-001/reported-72h",
      "sentAt": "2026-04-30T12:30:00.000Z",
      "evidenceRefs": [
        {
          "key": "operator_approval_ref",
          "ref": "approval://secops/cra-incident-001"
        }
      ]
    },
    "closure": {
      "reason": "final report accepted",
      "closedStatus": "closed",
      "finalReportBundleRef": "bundle://cra/final-report/cra-incident-001"
    },
    "evidenceRetention": {
      "mode": "incident",
      "evidenceBundleRef": "dsse://evidence/cra-incident-001",
      "snapshotRef": "snapshot://findings/cra-incident-001",
      "retentionPolicyRef": "evidence-locker.incident-mode",
      "retentionExtensionDays": 90
    }
  }
}

cra.incident.classified requires at least one reachability subgraph ref and one VEX consensus ref where CRA reportability depends on reachable/vulnerable product state. cra.incident.reported validates the emitted report payload hash as sha256:<64 lowercase hex> and carries the CRA timeline milestone, signer, target intake, source ledger event ref, and milestone evidence refs.

2.6 Versioning & DSSE linkage (FL1, FL6)

• Canonical schema identifiers are catalogued in schema-catalog.md (ledger.event.v1, ledger.projection.v1, export.v1.*).
• Any change to the envelope, hash recipe, or required fields bumps the catalog version; legacy versions remain frozen.
• DSSE artefacts (anchors, exports, replay reports) must embed policyVersion and schemaVersion (see dsse-policy-linkage.md).

Hash pipeline:

canonical_json = CanonicalJsonSerializer.Serialize(envelope)
sha256_bytes = SHA256(canonical_json)
event_hash = HexLower(sha256_bytes)

merkle_leaf_hash = HexLower(SHA256(event_hash || '-' || sequence_no)).

3. Merkle anchoring

Anchoring batches events per tenant across fixed windows (default: 1,000 events or 15 minutes). Anchors are stored in ledger_merkle_roots.

Indexes: PRIMARY KEY (tenant_id, anchor_id), UNIQUE (tenant_id, root_hash), INDEX ix_merkle_sequences ON ledger_merkle_roots (tenant_id, sequence_end DESC).

4. Projection tables

4.1 findings_projection

Stores the latest verdict/state per finding.

Primary key: (tenant_id, finding_id, policy_version).

Indexes:

• ix_projection_status on (tenant_id, status, severity DESC).
• ix_projection_risk on (tenant_id, risk_severity, risk_score DESC).
• ix_projection_labels_gin using labels GIN for KEV/runtime filters.

4.2 Retired: finding_history and triage_actions

Migration 028_findings_retire_write_only_projections.sql drops both tables and the ledger_action_type enum. The projector wrote them and nothing read them; every row was a function of ledger_events, which stays the record. A finding’s timeline and its triage actions are read from ledger_events (for example, the alert decision audit timeline).

4.4 ledger_projection_offsets

Checkpoint store for the projection background worker, one cursor per tenant. Ensures idempotent replays across restarts. Primary key (worker_id, tenant_id) since migration 018_findings_ledger_tenant_registry.sql, which copied a pre-existing global cursor once to every registered tenant.

No row is seeded. A tenant without a row replays from the beginning of its own events (1970-01-01T00:00:00Z with the empty GUID).

4.4a ledger_tenants

The registry of tenants that have ledger events (migration 018). It holds tenant ids only and carries no row-level security: it is the one relation the projector reads before it binds a tenant. The row-level AFTER INSERT trigger ledger_events_register_tenant on ledger_events fills it; PostgreSQL clones the trigger to every partition. ATTACH PARTITION of a pre-filled table fires no trigger; re-run the backfill from the deploy runbook afterwards.

4.5 ledger_attestations

Deterministic view of DSSE verification results used by /v1/ledger/attestations. Rows are written by the provenance/verification pipeline and keyed per tenant.

Ordering and pagination: ORDER BY recorded_at ASC, attestation_id ASC with cursor token {recordedAt, attestationId, filtersHash}. Indexes: PK (tenant_id, attestation_id), paging index (tenant_id, recorded_at, attestation_id), lookups on (tenant_id, artifact_id, recorded_at DESC) and (tenant_id, verification_status, recorded_at DESC).

4.6 asset_registry_events

Stores a Findings-owned visibility projection of the Graph Asset Registry v1 append-only source table (graph.asset_registry_events). Graph remains the source of truth and Findings never writes the graph schema. The projection preserves Graph payloads and hashes unchanged so auditors can compare the Findings row back to the Graph event source.

Primary key: (tenant_id, graph_event_id). Deterministic timeline reads order by graph_occurred_at ASC, event_hash ASC. The projection worker checkpoints source append order in asset_registry_projection_offsets using graph_recorded_at ASC, graph_event_id ASC so late-arriving source rows are not missed while replay views still use the Graph event ordering contract.

Migration: src/Findings/StellaOps.Findings.Ledger/migrations/017_asset_registry_event_visibility.sql.

5. Hashing & verification

1. Canonical serialize the envelope (§2.3).
2. Compute event_hash and store along with previous_hash.
3. Build Merkle tree per anchoring window using leaf hash SHA256(event_hash || '-' || sequence_no).
4. Persist root in ledger_merkle_roots and, when configured, submit to external transparency log (Rekor v2). Store receipt/UUID in anchor_reference (see merkle-anchor-policy.md).
5. Projection rows compute cycle_hash = SHA256(canonical_projection_json) where canonical projection includes fields {tenant_id, finding_id, policy_version, status, severity, labels, current_event_id} with sorted keys.

Verification flow for auditors:

• Fetch event, recompute canonical hash, validate previous_hash chain.
• Reconstruct Merkle path from stored leaf hash; verify matches recorded root.
• Service callers can use GET /api/findings/v1/ledger/ledger/{ledgerId}/chain-verify to run the tenant-scoped chain verifier without exporting raw ledger rows. The response is limited to status (verified, tampered, or missing), rootHash, verifiedAt, entryCount, and firstTamperedSeq when the first divergent sequence is known.
• Cross-check projection cycle_hash matches ledger state derived from last event.
• Alert evidence bundle download produces a deterministic gzip tar archive with manifest.json, alert.json, and evidence.json. The verify endpoint regenerates the archive under the resolved request tenant and compares SHA-256 hashes so offline callers can prove bundle byte integrity without crossing tenant boundaries. Composite alert IDs that include image path slashes must use the query-form endpoints /api/findings/v1/alerts/summary?alert_id=..., /api/findings/v1/alerts/decisions?alert_id=..., /api/findings/v1/alerts/audit?alert_id=..., /api/findings/v1/alerts/bundle?alert_id=..., and /api/findings/v1/alerts/bundle/verify?alert_id=... instead of the single path segment routes. If callers supply a signature, it must be DSSE envelope JSON whose payload either hashes to the regenerated bundle hash or contains a sha256/bundleHash binding to it. The verifier checks the signature against configured PEM trust roots under findings:ledger:evidenceBundles:dsseVerification; malformed envelopes, missing trust roots, payload mismatches, and signature mismatches fail closed.
• Notify delivery correlation for the production assurance seed uses the Notify-owned findings.production_alert.evidence_seeded producer event. The event payload carries the alert id, artifact, vulnerability, component, environment, evidence bundle ref, evidence hash, recipient, and configured Notify channel id; Notify persists a queued delivery row keyed by the event id and queryable by the alert/evidence correlation id. Findings remains the source for the alert and evidence content; Notify owns channel readiness and delivery state.

6. Fixtures & migrations

• Initial migration script: src/Findings/StellaOps.Findings.Ledger/migrations/001_initial.sql.
• Sample canonical event: src/__Tests/__Datasets/seed-data/findings-ledger/fixtures/ledger-event.sample.json (includes pre-computed eventHash, previousHash, and merkleLeafHash values).
• Sample projection row: src/__Tests/__Datasets/seed-data/findings-ledger/fixtures/finding-projection.sample.json (includes canonical cycleHash for replay validation).
• Golden export fixtures (FL7): src/Findings/StellaOps.Findings.Ledger/fixtures/golden/*.ndjson with checksums in docs/modules/findings/ledger/golden-checksums.json.
• Redaction manifest (FL5): docs/modules/findings/ledger/redaction-manifest.yaml governs mask/drop rules for canonical vs compact exports.

Fixtures follow canonical key ordering and include precomputed hashes to validate tooling.

7. Projection worker

• LedgerProjectionWorker enumerates ledger_tenants, then consumes each tenant’s ledger events via PostgresLedgerEventStream on a connection bound to that tenant (ledger_events is FORCE-RLS and the owner is NOBYPASSRLS), applying deterministic reductions with LedgerProjectionReducer. It reads at most one batch per tenant per round and waits the idle delay only when no tenant had work.
• Checkpoint state is stored per tenant in ledger_projection_offsets, allowing replay from any point in time.
• Batch processing is configurable via findings:ledger:projection (batchSize, idleDelay).
• Each event upserts findings_projection (current state with cycle_hash) and advances the tenant’s checkpoint in ledger_projection_offsets.
• AssetRegistryLedgerProjectionWorker consumes the Graph-owned graph.asset_registry_events source read-only and mirrors created, updated, and removed asset events into findings.asset_registry_events. It uses the same findings:ledger:projection batch/idle settings, preserves Graph hashes unchanged, and performs a zero-row source admission probe before the worker starts. Any PostgreSQL admission failure stops startup with the exact source-recovery procedure; a later missing schema/table or denied privilege fails the worker instead of becoming an empty batch, while unrelated runtime connection/timeout faults retain bounded retry/backoff.

8. Runtime instrumentation persistence

The runtime instrumentation feature persists eBPF/APM trace observations in the existing findings schema. Runtime tables are not Merkle-chain ledger events; they back the runtime trace, aggregate, score, and timeline read models used by the Findings Ledger runtime endpoints.

8.1 runtime_traces

Append-only raw runtime observations.

Indexes cover (tenant_id, finding_id, captured_at DESC), (tenant_id, finding_id, artifact_digest), and sparse correlation_id lookup.

8.2 runtime_trace_aggregates

Denormalized hit counters populated during ingest with atomic INSERT ... ON CONFLICT DO UPDATE.

Primary key: (tenant_id, finding_id, artifact_digest, vulnerable_function). Indexes cover top-N hit and recent-activity queries per (tenant_id, finding_id).

8.3 runtime_scores

One row per (tenant_id, finding_id), upserted by score derivation.

8.4 runtime_timeline_events

Append-only runtime activity timeline.

Indexes cover (tenant_id, finding_id, occurred_at DESC) and sparse correlation_id lookup.

8.5 Migrations and DI

• 011_runtime_traces.sql creates traces and aggregate counters.
• 012_runtime_scores_timeline.sql creates scores and timeline events.
• 013_runtime_aggregates_entry_point.sql promotes entry-point evidence on aggregate rows.
• 014_runtime_traces_container_metadata.sql promotes container name and metadata from accepted input into durable columns.
• AddFindingsLedgerRuntimePersistence(IConfiguration) registers the runtime repositories; disabled runtime mode resolves to null repositories that return empty reads and no-op writes.

The runtime tables are created by the consolidated Findings chain (src/Findings/__Libraries/StellaOps.Findings.Persistence/Migrations/), which findings-web and findings-worker apply through startup migrations. The file names above are the history of that DDL; StellaOps.Findings.Ledger embeds no SQL since its legacy chain was withdrawn (SPRINT_20260928_004 PCD-149).

9. Next steps

• Integrate Policy Engine batch evaluation with the projector (LEDGER-29-004).
• Align Vulnerability Explorer queries with the new projection state and timeline endpoints.
• Externalise Merkle anchor publishing to transparency log once anchoring cadence is finalised. | | | Array of policy rationale references (explain bundle IDs, remediation notes). |