nuMetrix

nuMetrix for
Software Engineers

The code around the pipeline: SvelteKit Explorer, YAML-to-SQL compilers,
synthetic data generators, and contract validation

March 2026

Overview

What You’ll See Today

Act I — The DSL compilers: YAML → SQL for probes, hypotheses, and diagnoses
Act II — The Explorer: SvelteKit, read-only DuckDB, runtime discovery, i18n
Act III — Data generation: synthetic tenants, column mapping, defect injection
Act IV — Testing and visualization: Vitest, CI validation, Evidence.dev, Cytoscape
Act V — Architecture patterns and what’s next

Companion to nuMetrix for Data Engineers. That deck covered dbt models, medallion layers, and dispatch macros. This one covers the TypeScript, Python, and DSL code that surrounds the pipeline.

Stack

The Software Stack

SvelteKit 5

Explorer frontend, API routes, file-based routing

Python 3.12

YAML-to-SQL compilers, data generators, validators

DuckDB

In-process analytics DB. Single file, zero config

TypeScript 5.9

Type-safe server queries, DuckDB result typing

Tailwind 4

Utility-first CSS for Explorer UI

Vitest

Unit tests with mocked DuckDB connections

Cytoscape + Dagre

Pipeline DAG rendering and hierarchical layout

Tauri

Desktop app shell (Rust + WebView)

One monorepo. Three runtimes (Node, Python, Rust). Zero cloud services — everything runs on a single laptop with an in-process DuckDB.

nuMetrix

Act I

The DSL Compilers

YAML in, SQL out. Three compilers, one pattern.

Architecture

Three Compilers, One Pattern

YAML
definition

→

Compiler
Python

→

.sql
dbt model

probecompile.py — Probes & assessments → probe_findings__*.sql
hypothesiscompile.py — Hypotheses → hypothesis_verdicts.sql
diagnosiscompile.py — Diagnoses → diagnosis_verdicts.sql

Shared pipeline: load YAML → validate against contract → resolve entity refs → generate CTEs → emit uniform schema. Domain authors write YAML; dbt sees SQL.

Contracts

Contract Resolution

scripts/probecompile.py
def resolve_ref(contract: dict, entity_name: str) -> str:
    """Resolve entity name to dbt ref."""
    entity = contract["entities"][entity_name]
    return "{{ ref('" + entity["dbt_model"] + "') }}"

contracts/gold_contract.v1.json (excerpt)
{
  "entities": {
    "BillingEvent": {
      "dbt_model": "gold_billing_events",
      "fields": { "billing_id": "varchar", "billed_amount": "numeric", ... }
    },
    "CaseMaterialUsage": {
      "dbt_model": "gold_case_material_usage", ...
    }
  }
}

Probes reference BillingEvent, not gold_billing_events. The contract is the indirection layer — if a dbt model renames, only the contract JSON changes.

Balance

Compiling a Balance Probe

scripts/probecompile.py — compile_balance() (abridged)
def compile_balance(probe, contract):
    left   = probe["left"]      # e.g. CaseMaterialUsage.quantity
    right  = probe["right"]     # e.g. BillingEvent.quantity

    left_ref  = resolve_ref(contract, left["entity"])
    right_ref = resolve_ref(contract, right["entity"])

    left_cte = f"""left_agg as (
    select {join_key},
           {left['aggregate']}({left['expression']}) as left_value
    from {left_ref}
    group by {join_key}
)"""
    # ... right_cte, time_buckets, compared CTE ...

    compared = f"""compared as (
    select l.{join_key}, t.time_bucket,
           l.left_value, coalesce(r.right_value, 0) as right_value,
           100.0 * (l.left_value - coalesce(r.right_value, 0))
                 / l.left_value as pct_diff
    from left_agg l
    left join right_agg r on l.{join_key} = r.{join_key}
    left join time_buckets t on l.{join_key} = t.{join_key}
    where abs(pct_diff) > {tol}
)"""

Every probe type follows this pattern: domain-specific CTEs → compared CTE → uniform findings_select() output.

Output

The Findings Contract

scripts/probecompile.py — findings_select()
def findings_select(pid, version, scope, entity_id_expr,
                    time_bucket_expr, severity_expr,
                    money_at_risk_expr, evidence_expr):
    return f"""select
    md5('{pid}' || '|' || '{{{{ var("tenant_id") }}}}' || '|'
        || cast({entity_id_expr} as varchar)) as finding_id,
    '{{{{ var("tenant_id") }}}}' as tenant_id,
    '{pid}'       as probe_id,
    '{version}'   as probe_version,
    {severity_expr}       as severity,
    '{scope["entity_type"]}'  as entity_type,
    cast({entity_id_expr} as varchar) as entity_id,
    {time_bucket_expr}    as time_bucket,
    {money_at_risk_expr}  as money_at_risk,
    {evidence_expr}       as evidence"""

Every probe — balance, duplicate, outlier, temporal, assessment — calls this function. 11 uniform columns. Deterministic finding_id via MD5. Evidence is inline JSON for drill-down.

Registry

The Reverse Compiler

scripts/proberegistry.py — YAML structure → human language
# Type-dispatched: each probe type has a text generator
TYPE_GENERATORS = {
    "balance": gen_balance,  "duplicate": gen_duplicate,
    "mandatory_item": gen_mandatory_item,  "assessment": gen_assessment, ...
}

def gen_balance(probe, glossary):
    ent = entity_label(entity, lang, glossary)   # "Case" → "Fall" (de)
    return {
      f"display_name_{lang}": f"{humanize(left_alias)} vs {humanize(right_alias)}",
      f"description_{lang}":  f"Compares {left_alias} against {right_alias} per {ent}.",
      f"interpretation_{lang}": f"{ent} {{entity_id}} shows a gap of {{money_at_risk}}."
    }

scripts/registry_glossary.yaml — tri-lingual vocabulary
entities:
  BillingEvent:
    en: billing event    de: Abrechnungsereignis    fr: événement de facturation
fields:
  billed_amount:
    en: billed amount    de: Abrechnungsbetrag       fr: montant facturé

The forward compiler turns YAML → SQL. The reverse compiler turns YAML → human language. Auto-generated display names, descriptions, and per-finding interpretation templates in DE/EN/FR. Manual registry: overrides win per-field, per-language.

Hypotheses

Hypothesis Compiler

scripts/hypothesiscompile.py — evidence + scoring + verdict
-- Evidence CTE: UNION ALL of per-probe aggregates
for ev in hyp["evidence"]:
    f"""select '{pid}' as probe_id, '{role}' as role,
        {weight} as weight, count(*) as finding_count,
        coalesce(sum(money_at_risk), 0) as money_at_risk
    from {{{{ ref('probe_findings__{pid}') }}}}"""

-- Weighted scoring: SUM(weight * signal * direction) / SUM(weight)
signal = "case when finding_count > 0 then 1 else 0 end"      # binary
signal = "least(1.0, ln(1 + count) / ln(1 + saturation))"  # graduated
direction = "case when role = 'counter' then -1 else 1 end"

-- Verdict: primary gate + score thresholds
case
    when primary_has_findings = 0 then 'not_observed'
    when evidence_score >= 0.7      then 'confirmed'
    when evidence_score >= 0.3      then 'plausible'
    else 'not_observed'
end as status

The primary probe is a hard gate: zero primary findings = not_observed, regardless of score. Counter-evidence probes subtract from the score. Binary or log-curve signal scaling.

Diagnoses

Diagnosis Compiler

scripts/diagnosiscompile.py — three-stage pattern
-- Stage 1: Hypothesis gate (only confirmed)
__hypothesis as (
    select * from {{ ref('hypothesis_verdicts') }}
    where hypothesis_id = 'hyp_revenue_leakage'
      and status = 'confirmed'
),
-- Stage 2: Condition checks (all must pass)
__conditions as (
    select 'probe_revenue_leakage', count(*), sum(money_at_risk)
    from {{ ref('probe_findings__probe_revenue_leakage') }}
    having count(*) > 10
    -- HAVING filters: no row emitted = condition not met
),
-- Stage 3: Confidence = base + conditional boosts, capped at 1.0
__confidence as (
    select least(1.0,
        0.6                                  -- base
        + case when ... > 50 then 0.15 else 0 end  -- boost_if
        + case when ... > 5000 then 0.1 else 0 end  -- boost_if
    )
)
-- Final: emit row only if ALL conditions passed
where (select count(*) from __conditions) = 2  -- expected count

nuMetrix

Act II

The Explorer

SvelteKit frontend. Read-only DuckDB. Zero-deploy tenant discovery.

Routes

SvelteKit Route Architecture

/[tenant]/dimensions

Browse 7 Gold entities. Drill down to [entity]/[id].

/[tenant]/findings

All probe findings. Filter by probe_id, severity.

/[tenant]/hypotheses

Evidence chain, verdict badges, diagnosis drill-down.

/[tenant]/taxonomies

Tree views, cardinality analysis, member cross-walk.

/[tenant]/probe-builder

Interactive YAML editor. Live compile via Python subprocess.

/[tenant]/system/pipeline

DAG graph: Cytoscape + Dagre layout engine.

25 route directories under [tenant]/. Every route is tenant-scoped. File-based routing — the filesystem is the API surface.

DuckDB

Read-Only DuckDB Connection

explorer/src/lib/server/db.ts
const { Database, OPEN_READONLY } = require('duckdb-async');

let db: DatabaseType | null = null;
let dbMtimeMs: number = 0;

export async function getDb(): Promise<DatabaseType> {
    const stat = fs.statSync(DB_PATH);
    if (db && stat.mtimeMs !== dbMtimeMs) {
        try { await db.close(); } catch {}  // stale handle
        db = null;
    }
    if (!db) {
        db = await Database.create(DB_PATH, OPEN_READONLY);
        dbMtimeMs = stat.mtimeMs;
    }
    return db;
}

OPEN_READONLY — Explorer never locks the database. dbt and Evidence write concurrently.
mtime reconnect — After dbt build, the next query automatically picks up the fresh file.
createRequire — Workaround for Vite ESM bundling with native Node addons.

Discovery

Runtime Table Discovery

explorer/src/lib/server/queries/findings.ts
async function discoverProbeTables(tenant: string): Promise<string[]> {
    const rows = await safeQuery<{ table_name: string }>(
        `SELECT table_name FROM information_schema.tables
         WHERE table_schema = $1
           AND table_name LIKE 'probe_findings__%'`,
        tenant
    );
    return rows.map(r => r.table_name);
}

function buildUnion(tenant: string, tables: string[]): string {
    return tables
        .map(t => `SELECT * FROM "${tenant}".${t}`)
        .join(' UNION ALL BY NAME ');
}

No hardcoded table list — queries information_schema to discover what probes exist at runtime.
UNION ALL BY NAME — DuckDB-specific: unions by column name, not position. Different probe types have different evidence shapes.
safeQuery — wraps errors into empty arrays for graceful degradation.

i18n

Locale Switching with Svelte Stores

explorer/src/lib/i18n/index.ts
import { writable, derived } from 'svelte/store';
import de from './locales/de.json';
import en from './locales/en.json';
import fr from './locales/fr.json';

export const locale = writable<Locale>('de');

export const t = derived(locale, ($locale) => {
    return (key: string): string => {
        return getNestedValue(messages[$locale], key);
    };
});

/** Fallback: 'dimensions.case_material_usage' → 'Case Material Usage' */
function humanize(path: string): string {
    return path.slice(path.lastIndexOf('.') + 1)
        .replace(/_/g, ' ')
        .replace(/\b\w/g, c => c.toUpperCase());
}

locale.set('fr') triggers reactivity. $t('dimensions.cases') in templates. Missing translations degrade to title-cased snake_case — no crashes, no blank labels.

Probe Builder

Live Probe Builder API

explorer/src/routes/api/probe-builder/compile/+server.ts
const COMPILE_SINGLE = path.join(PROJECT_ROOT, 'scripts', 'probecompile_single.py');
const PYTHON = path.join(PROJECT_ROOT, '.venv', 'bin', 'python3');

export const POST: RequestHandler = async ({ request }) => {
    const { yaml } = await request.json();

    const child = spawn(PYTHON, [COMPILE_SINGLE], {
        cwd: PROJECT_ROOT,
        stdio: ['pipe', 'pipe', 'pipe'],
        timeout: 15000
    });

    child.stdin.write(yaml);  // YAML in via stdin
    child.stdin.end();

    child.on('close', (code) => {
        // JSON out via stdout: { ok, sql?, errors? }
        resolve(JSON.parse(stdout));
    });
};

SvelteKit API route → Python subprocess. stdin YAML, stdout JSON. 15-second timeout. The project’s .venv ensures contract libraries are available. Stderr becomes structured error messages in the UI.

Evidence

Hypothesis Detail Page

explorer/src/routes/[tenant]/hypotheses/[hypothesis_id]/+page.server.ts
export const load: PageServerLoad = async ({ params }) => {
    // Parallel fetch: hypothesis detail + probe catalog
    const [hypothesis, probeCatalog] = await Promise.all([
        getHypothesisDetail(params.tenant, params.hypothesis_id),
        getProbeCatalog(params.tenant)
    ]);

    // Parse evidence string: "probe_rev_leakage(primary), ..."
    const evidenceEntries = hypothesis.evidence_probes
        .split(', ')
        .map(entry => {
            const match = entry.match(/^(.+)\((\w+)\)$/);
            return { probe_id: match[1], role: match[2] };
        });

    // Merge three data sources: roles + catalog names + stats
    const evidenceStats = await getHypothesisEvidence(params.tenant, probeIds);

    // Lazy-load diagnoses only for confirmed hypotheses
    const diagnoses = hypothesis.status === 'confirmed'
        ? await getDiagnosesForHypothesis(params.tenant, params.hypothesis_id)
        : [];

    return { hypothesis, evidence, diagnoses };
};

nuMetrix

Act III

Data Generation & Contracts

Deterministic synthetic data. Three ERP column mappings. Injected defects.

Generator

Synthetic Data Architecture

scripts/generate_synthetic_data.py — TENANT_CONFIGS
TENANT_CONFIGS = {
    "hospital_alpha": {
        "source_system": "opale",  "seed": 42,
        "n_cases": 2000,
        "cost_centers": [("CC_OR1", "Operating Room 1", "OR"), ...],
        "leakage_pct": 0.10,  "price_multiplier": 1.0,
    },
    "hospital_beta": {
        "source_system": "sap",  "seed": 99,
        "n_cases": 1500,
        "cost_centers": [("KST_OP1", "OP-Saal 1", "OR"), ...],  # German
        "price_multiplier": 0.95,  # EUR vs CHF
    },
    "hospital_delta": {
        "source_system": "opale",  "seed": 512,
        "n_cases": 150_000,    # stress-test tenant
        "orphan_billing_count": 5000,  # 7 advanced defect types
        ...
    },
}

Fully deterministic (seeded PRNG). Each tenant varies seed, time range, case volume, procedure mix, cost center language (EN/DE/FR), and defect injection rates. hospital_zeta has skip_synthetic: True — real data only.

Multi-Source

Column Mapping: Three ERPs

OPALE (canonical)

          case_token

          start_time

          material_id

          quantity

          billed_amount

          cost_center_id

SAP MM

          AUFNR

          ERDAT

          MATNR

          MENGE

          DMBTR

          KOSTL

Navision

          No_

          Starting_Date_Time

          Item_No_

          Quantity

          Amount

          Cost_Center_Code

Generators create data using canonical field names. At write time, SAP/Navision tenants remap columns through dictionaries. The dbt Bronze layer reverses this with source-system dispatch macros.

Defects

Defect Injection for Calibration

scripts/generate_synthetic_data.py — hospital_delta advanced defects
# 7 advanced defect types — probe calibration targets
"orphan_billing_count":   5000,     # billing for non-existent cases
"duplicate_billing_pct":  0.02,     # same material billed twice
"phantom_usage_pct":     0.01,     # usage refs non-existent material
"cross_cc_billing_pct":  0.04,     # billing CC ≠ case CC
"stale_pricing_pct":     0.08,     # unit_price >20% off standard
"timing_anomaly_pct":    0.03,     # billing before usage timestamp
"partial_billing_pct":   0.05,     # qty billed < qty used

Calibration Loop

Inject known defects at known rates → run probes → verify probe catches match expected counts. If a probe misses injected defects, the probe logic has a bug.

hospital_delta Profile

150K cases, 1.68M billing rows, 44 cost centers. All 7 defect types active simultaneously — tests probe interaction and false-positive overlap.

Validation

Contract Validation

scripts/probecheck.py
def validate_entity_ref(contract, entity_name, errors, context):
    entity = get_entity(contract, entity_name)
    if entity is None:
        errors.append(f"{context}: unknown entity '{entity_name}'. "
                      f"Valid: {sorted(contract['entities'].keys())}")
    return entity

SAFE_EXPRESSION_CHARS = set("abcdefghijklmnopqrstuvwxyz_0123456789 *+-/().")

def validate_expression_tokens(expression, entity_def, entity_name, errors, ctx):
    """Check expression only references valid fields and safe operators."""
    clean = expression.lower().strip()
    if not all(c in SAFE_EXPRESSION_CHARS for c in clean):
        bad = [c for c in clean if c not in SAFE_EXPRESSION_CHARS]
        errors.append(f"{ctx}: unsafe characters: {bad}")
    # Tokenize and verify each non-numeric token is a real field
    for token in tokens:
        if not token.replace('.', '').isdigit():
            if token not in entity_def["fields"]:
                errors.append(f"{ctx}: '{token}' not a field of '{entity_name}'")

Lightweight SQL injection guard. YAML authors write expressions like quantity * unit_price. The validator whitelists characters, tokenizes on operators, and verifies every non-numeric token exists in the contract.

Real Data

XLSX to CSV Conversion

scripts/extract_opale_xlsx_csv.py — position-based column extraction
# Sheets have no reliable headers — extract by column index
HOSP_COLS = {
    "admission_code": 1,    # Code Type d'admission
    "o_numero":       3,    # O-Numero de prestation
    "description":    4,    # Prestation DesiD
    "date":           5,    # Date de la prestation
    "cc_demandeur":   6,    # C/C demandeur
    "cc_producteur":  8,    # C/C producteur
    "quantity":       10,   # Quantite
    "montant":        11,   # Montant Facturable
    "sejour_id":      20,   # Identifiant du sejour
}

AMBU_COLS = { ... }  # Same fields, shifted right by 1 (leading empty column)

CASE_TYPE_MAP = {
    "2000": "inpatient",   # Stationar akutsomatisch
    "1000": "outpatient",  # Ambulant
    "1010": "day-case",    # Tagesfall
}

hospital_zeta is real data from SZO (Brig/Visp). 68K cases, 2.4M billing rows. XLSX sheets have different positional offsets for inpatient vs outpatient. Sapheneia codes map to canonical nuMetrix vocabulary.

nuMetrix

Act IV

Testing & Visualization

Vitest mocks, compile-time validation, static BI, and DAG rendering.

Vitest

Unit Tests with Mocked DuckDB

explorer/src/lib/server/queries/taxonomies.test.ts
// Mock the db module before importing the module under test
vi.mock('../db.js', () => ({
    query: vi.fn(),
    validateTenantSlug: vi.fn((s: string) => /^[a-z][a-z0-9_]*$/.test(s))
}));

const mockQuery = vi.mocked(query);

describe('buildTree', () => {
    it('nests children under their parent', () => {
        const nodes = [makeNode('root', null),
                       makeNode('child1', 'root')];
        const tree = buildTree(nodes);
        expect(tree[0].children).toHaveLength(1);
    });
});

describe('getMemberCrossTaxonomy', () => {
    it('returns empty for invalid tenant slug', async () => {
        const result = await getMemberCrossTaxonomy('INVALID!', '0091');
        expect(result).toEqual([]);
        expect(mockQuery).not.toHaveBeenCalled(); // slug rejected before query
    });
});

vi.mock hoists to module top — replaces the real DuckDB connection before the module under test imports it. Pure functions like buildTree need no mocks at all. Error paths tested explicitly.

CI Gate

Compile-Time YAML Validation

YAML
probe definition

→

probecheck.py

→

Pass

Schema validation — Required keys, valid types, correct nesting
Contract validation — Entity names exist in gold_contract.v1.json
Field validation — Every field reference resolves to a real column in the entity
Expression safety — Whitelist characters, tokenize, verify each token is a field or number
Cross-reference — probe_id matches filename. Severity levels are valid enums.

Runs in CI before probecompile.py. Invalid YAML never reaches the SQL compiler. Shift-left validation — catch contract violations at author time, not at dbt build time.

Evidence

Evidence.dev Static BI

Platform
DuckDB schema

→

npm run sources
Parquet cache

→

npm run build
static HTML

Markdown-based pages with inline SQL queries. No React, no dashboard builder.
Queries run at build time against the Parquet cache. Zero runtime database dependency.
Tenant dropdown — Platform layer provides all tenants in every query via tenant_id column.
Dagster asset evidence_site orchestrates: npm run sources && npm run build after platform gold.

Two BI surfaces: Explorer (interactive, per-tenant) and Evidence (static, cross-tenant). Same DuckDB, different access patterns.

Pipeline

Pipeline Graph: Cytoscape + Dagre

Runtime Discovery

Nodes and edges are discovered from information_schema at request time. The graph shows exactly what exists in the current tenant — no static manifest.

Layout Engine

Dagre computes hierarchical DAG positions. Cytoscape.js renders and handles pan/zoom/select. Switchable between layer mode and kind mode.

Nodes colored by layer: Bronze Silver Gold Probes
Visible-only re-layout — collapse/expand layers without recomputing the entire graph.
Click a node → detail panel with schema, row counts, upstream/downstream links.

nuMetrix

Act V

Architecture & Patterns

Recurring design decisions and what they buy you.

Patterns

Design Patterns Recap

Read-Only Consumption

Explorer opens DuckDB with OPEN_READONLY. No write locks, no contention. dbt writes, Explorer reads — zero coordination.

DSL-to-SQL Compilation

Domain experts write YAML. Compilers generate validated SQL. dbt treats it as native models. Separation of concerns at the authoring boundary.

Contract-First Design

JSON contracts define entity schemas. Probes reference contracts, not tables. Rename a model → update one JSON field → recompile.

Parameterized Generation

Synthetic data uses seeded PRNG + config dicts. Same code, different parameters → OPALE, SAP, or Navision CSV with controlled defect rates.

Status

What’s Built, What’s Next

Built & Working

3 YAML-to-SQL compilers + validators
SvelteKit Explorer with 25 route directories
Live probe builder (TS → Python subprocess)
DE/EN/FR i18n with graceful fallback
5-tenant synthetic data generator
Real data XLSX converter (hospital_zeta)
Pipeline DAG with Cytoscape + Dagre
Evidence.dev static BI site

Road Ahead

Tauri desktop app packaging
API endpoint test coverage
Probe builder: save & deploy from UI
Presentation mode for hypothesis reports
Cross-tenant comparison dashboards
RBAC: tenant-scoped user permissions

nuMetrix

One Codebase, Multiple Surfaces

YAML DSL
probes, hypotheses, diagnoses

→

Python
compilers, generators

→

dbt + DuckDB
pipeline engine

Explorer
SvelteKit

Evidence
static BI

Tauri
desktop

Dagster
orchestration

All surfaces consume the same DuckDB. All probes are compiled from the same YAML. All tenants share the same pipeline. One truth, many views.

nuMetrix

nuMetrix forSoftware Engineers

What You’ll See Today

The Software Stack

The DSL Compilers

Three Compilers, One Pattern

Contract Resolution

Compiling a Balance Probe

The Findings Contract

The Reverse Compiler

Hypothesis Compiler

Diagnosis Compiler

The Explorer

SvelteKit Route Architecture

Read-Only DuckDB Connection

Runtime Table Discovery

Locale Switching with Svelte Stores

Live Probe Builder API

Hypothesis Detail Page

Data Generation & Contracts

Synthetic Data Architecture

Column Mapping: Three ERPs

Defect Injection for Calibration

Contract Validation

XLSX to CSV Conversion

Testing & Visualization

Unit Tests with Mocked DuckDB

Compile-Time YAML Validation

Evidence.dev Static BI

Pipeline Graph: Cytoscape + Dagre

Architecture & Patterns

Design Patterns Recap

What’s Built, What’s Next

One Codebase, Multiple Surfaces

nuMetrix for
Software Engineers