ADR — Findings Runtime Persistence

Formerly docs/architecture/ADR-FINDINGS-RUNTIME-PERSISTENCE.md.

FieldValue
StatusAccepted (signed off 2026-04-29 — Architecture lead via user approval)
Date2026-04-28
AuthorsFindings Ledger Runtime sub-team
Modulesrc/Findings/StellaOps.Findings.Ledger/Infrastructure/Postgres/Runtime/
RelatedSPRINT_20260429_011_FindingsLedger_runtime_persistence, SPRINT_20260429_015_FindingsLedger_runtime_documentation, SPRINT_20260429_023_Findings_runtime_contract_corrections
Migrationssrc/Findings/StellaOps.Findings.Ledger/migrations/011_runtime_traces.sql, 012_runtime_scores_timeline.sql, plus the Sprint 023 corrective migration for runtime_trace_aggregates.has_entry_point

In one line: the Stella Ops Findings Ledger stores runtime instrumentation in four purpose-built Postgres tables — append-only runtime_traces, denormalized runtime_trace_aggregates, one-row-per-finding runtime_scores, and append-only runtime_timeline_events — each indexed for its own read shape, applied via startup auto-migration, with tenant scoping in the repository layer (no RLS).

Audience: Findings Ledger persistence engineers and reviewers of the runtime schema. Pairs with ADR-015 (what reaches these tables) and ADR-016 (what runtime_scores holds).

Context

Runtime trace observations arrive at modest-but-non-trivial volumes (thousands per finding per day in heavy-traffic services), and the read-side use cases are different in shape from the write-side:

Storing all of this in a single runtime_observations JSONB blob table forces every read path to scan the same JSON column. Storing each path in its own normalized table is cheap and makes each query hit a purpose-built index.

We also need persistence to be disabled-friendly: when the runtime toggle is off, no rows should be touched at all (see ADR-013 Findings Runtime Disabled Mode).

Decision

Four tables under the existing findings Postgres schema, applied via the standard AddStartupMigrations startup hook (CODE_OF_CONDUCT §2.7):

1. findings.runtime_traces — append-only raw observations

CREATE TABLE findings.runtime_traces (
    id              UUID        PRIMARY KEY,
    tenant_id       TEXT        NOT NULL,
    finding_id      UUID        NOT NULL,
    captured_at     TIMESTAMPTZ NOT NULL,
    artifact_digest TEXT        NOT NULL,
    component_purl  TEXT        NOT NULL,
    container_id    TEXT,
    frames          JSONB       NOT NULL,
    correlation_id  UUID,
    ingested_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_runtime_traces_tenant_finding_captured
    ON findings.runtime_traces (tenant_id, finding_id, captured_at DESC);
CREATE INDEX idx_runtime_traces_artifact
    ON findings.runtime_traces (tenant_id, finding_id, artifact_digest);
CREATE INDEX idx_runtime_traces_correlation
    ON findings.runtime_traces (correlation_id)
    WHERE correlation_id IS NOT NULL;

frames is JSONB because the privacy filter has already stripped address tokens by the time the writer touches Postgres — the JSON is the redacted frame stream, not raw eBPF output.

runtime_traces persists container_id as the durable container identifier. The ingest wire shape may carry containerName and agent metadata, but this ADR does not expand storage for those fields: they are not first-class persisted columns in the corrected contract.

2. findings.runtime_trace_aggregates — denormalized per-function rollups

CREATE TABLE findings.runtime_trace_aggregates (
    tenant_id           TEXT        NOT NULL,
    finding_id          UUID        NOT NULL,
    artifact_digest     TEXT        NOT NULL,
    vulnerable_function TEXT        NOT NULL,
    hit_count           BIGINT      NOT NULL DEFAULT 0,
    first_seen          TIMESTAMPTZ NOT NULL,
    last_seen           TIMESTAMPTZ NOT NULL,
    container_count     INTEGER     NOT NULL DEFAULT 0,
    has_entry_point     BOOLEAN     NOT NULL DEFAULT FALSE,
    PRIMARY KEY (tenant_id, finding_id, artifact_digest, vulnerable_function)
);
CREATE INDEX idx_runtime_aggregates_tenant_finding_hits
    ON findings.runtime_trace_aggregates (tenant_id, finding_id, hit_count DESC);
CREATE INDEX idx_runtime_aggregates_tenant_finding_recent
    ON findings.runtime_trace_aggregates (tenant_id, finding_id, last_seen DESC);

Composite primary key on the bucket dimensions makes the upsert path trivial (INSERT ... ON CONFLICT DO UPDATE). Two purpose-built indices serve the two sortBy query modes (hits and recent). has_entry_point persists whether any contributing trace contained an entry-point frame. Conflict updates MUST preserve truth with logical OR semantics, so once an aggregate observes an entry point it remains true unless the aggregate row is rebuilt from source traces. Migration 013 backfills true from retained runtime_traces.frames where the source trace is still available; aggregates without recoverable source traces remain at the default false value.

3. findings.runtime_scores — one row per (tenant, finding)

CREATE TABLE findings.runtime_scores (
    tenant_id            TEXT          NOT NULL,
    finding_id           UUID          NOT NULL,
    score                NUMERIC(5,2)  NOT NULL,
    derivation_window_s  INTEGER       NOT NULL,
    components           JSONB         NOT NULL,
    updated_at           TIMESTAMPTZ   NOT NULL,
    PRIMARY KEY (tenant_id, finding_id),
    CONSTRAINT chk_runtime_scores_range CHECK (score >= 0 AND score <= 100),
    CONSTRAINT chk_runtime_scores_window_positive CHECK (derivation_window_s >= 0)
);

score is NUMERIC(5,2) (not DOUBLE PRECISION) for byte-stable storage. The four pre-weight component values are stored together in the components JSONB column for explainability — same shape as RuntimeScore.Components in the domain model.

4. findings.runtime_timeline_events — append-only event log

CREATE TABLE findings.runtime_timeline_events (
    event_id        UUID        PRIMARY KEY,
    tenant_id       TEXT        NOT NULL,
    finding_id      UUID        NOT NULL,
    kind            TEXT        NOT NULL,
    occurred_at     TIMESTAMPTZ NOT NULL,
    correlation_id  UUID,
    payload         JSONB       NOT NULL
);
CREATE INDEX idx_runtime_timeline_tenant_finding_occurred
    ON findings.runtime_timeline_events (tenant_id, finding_id, occurred_at DESC);

Correction note (Sprint 023): earlier sprint/report wording called this a “3-table schema” because it counted traces / aggregates / scores as the instrumentation read/write surface. The persistence contract is four tables: traces, trace aggregates, scores, and timeline events.

Tenant column, not row-level security

All four tables expose tenant_id as a regular TEXT column. We do not enable Postgres RLS for these tables; tenant scoping is enforced at the repository layer (Postgres*Repository) and at the authorization middleware (RequireTenant + IStellaOpsTenantAccessor). This matches the existing Findings Ledger persistence pattern; runtime tables are not special.

Auto-migration

Migrations 011, 012, and the Sprint 023 corrective migration for runtime_trace_aggregates.has_entry_point ship as embedded resources in the Findings Ledger assembly. Program.cs calls AddStartupMigrations(...) per CODE_OF_CONDUCT §2.7, so the schema converges on a fresh database without any manual psql step. The corrective migration is idempotent: it adds the column if needed and backfills entry-point evidence from retained runtime traces without deleting or rewriting trace rows.

Architecture-lead sign-off

Rationale

Why four tables instead of one? Each read path has a distinct query shape. Co-locating them in one wide table forces every read to scan a JSONB column with PII-ish frame data. The denormalized aggregate table is purposefully lossy — it does not let you reconstruct individual traces — which matches the privacy posture (see ADR-015 Findings Runtime Privacy Filter).

Why JSONB for frames and components? The frame schema is intentionally extensible (more probe types over time) and we don’t run queries against individual frame fields. JSONB gives us schema evolution without migrations. components is the same story — the score-formula ADR may grow new components, and the persistence layer shouldn’t care.

Why NUMERIC(5,2) for score? Byte-stable on disk, deterministic under cross-version migration. DOUBLE PRECISION would introduce representation-dependent jitter that breaks the determinism guarantee of the score calculator.

Why are aggregates eventually consistent with traces? The ingest service writes the raw trace, then the aggregator updates the aggregate, then the score recomputes. All three happen in one transaction in the current implementation (see RuntimeInstrumentationService.IngestTraceAsync). If we ever need to move the aggregate update to a background job, the design tolerates a short window where the aggregate lags the trace; clients that want “all traces visible” should query the raw table.

Why no row-level security? Consistent with the rest of the Findings Ledger persistence layer. RLS would force every non-application reader to set session vars; the application layer already enforces tenant scoping.

Alternatives considered

Consequences

References

Sign-off Window

Accepted on 2026-04-29 by Architecture lead. The 14-day default-accept window from sprint 024 (deadline 2026-05-13) closed early via explicit approval rather than the timeout path. Original window text retained below for history.

Following the project’s RFC default-accept process: if this ADR remains Proposed with no objection logged in this document by 2026-05-13 (14 days from sprint 024 commit), it is automatically promoted to Accepted. Architecture-lead may flip status earlier on review. Objections should be added under “Open Issues” below with name + date.

Open Issues

No objections logged during the sign-off window.