Core platform: reusable metrics store & exporter (per-tenant minute buckets + central hourly rollups)

[SamuelMcAravey]

Summary
Promote the metrics persistence and export logic into the core platform library so any application built on the platform can emit and persist its own metrics (platform- and app-level). Ship small, opinionated primitives: DB schema, exporter, registration APIs, and scheduled jobs. Exclude any UI/charting.

---

Goals

• Provide a reusable metrics substrate that apps can plug into:

  • Per-tenant minute buckets in tenant DBs.
  • Optional central hourly rollups in the stamp/global DB.

• Support platform metrics (outbox/inbox/DLQ/etc.) and app-defined metrics without schema edits.

• Safe with multi-instance deployments (InstanceId-aware; additive upserts).

• Keep cardinality controlled via tag whitelist and registration.

• Integrate with .NET Meter sources and optionally a lightweight scrape endpoint.

Non-Goals

• No UI, no charting, no alert rule editor.
• Not a replacement for Prometheus/Grafana (short/medium-term retention only).

---

High-level design

1) Storage (SQL Server; infra schema; PascalCase)

(Definitions are brief here; full scripts live in the platform DB project)

• infra.MetricDef
  Catalog of metrics (Name, Unit, AggKind, Description).

• infra.MetricSeries
  Identity for a time series: (MetricDefId, Service, InstanceId, TagsJson, TagHash).
  Unique: (MetricDefId, Service, InstanceId, TagHash).

• infra.MetricPointMinute (tenant DB)
  One row per SeriesId + minute, with ValueSum, ValueCount, ValueMin, ValueMax, ValueLast, P50, P95, P99, BucketStartUtc, BucketSecs=60.

• infra.MetricSeries + infra.MetricPointHourly (central DB)
  Hourly rollups per tenant (TenantId on series), BucketSecs=3600.

• infra.ExporterHeartbeat (central DB)
  Tracks per-instance exporter freshness (InstanceId, LastFlushUtc, LastError).

Retention: minute → 7–14 days per tenant; hourly → 90–180 days central.

2) Export model (two modes)

• Push (default): a per-instance exporter aggregates Meter data into minute buckets and persists to the tenant DBs every 60s; also accumulates and writes hourly aggregates to central. This is required because Meter is in-process memory.
• Pull (optional): a tiny metrics snapshot endpoint (disabled by default). A scheduled platform job can “scrape” every reachable instance and write on its behalf. Useful in constrained topologies; not required to meet goals.

3) Extensibility for app metrics

• Apps may register additional metrics (counters/gauges/histograms) at startup.
• Apps can whitelist tags per metric (e.g., event_type, area), avoiding cardinality blowups.
• No schema changes needed; new metrics are catalog entries in MetricDef.

4) Multi-tenant

• The exporter routes each measurement to the correct tenant connection (tag or ambient tenant context).
• Central rollup includes TenantId on series to enable cross-tenant views.

---

Public APIs (platform library)

// Metric identity & registration
public record MetricRegistration(
    string Name,              // e.g. "outbox.published.count"
    string Unit,              // "count" | "ms" | "seconds"
    string AggKind,           // "counter" | "gauge" | "hist"
    string Description,
    string[] AllowedTags);    // e.g. ["event_type"]

public interface IMetricRegistrar
{
    void Register(MetricRegistration metric);
    void RegisterRange(IEnumerable<MetricRegistration> metrics);
}

// Export configuration
public sealed class MetricExportOptions
{
    public TimeSpan MinuteFlushInterval { get; set; } = TimeSpan.FromSeconds(60);
    public bool EnableCentralRollup { get; set; } = true;
    public TimeSpan HeartbeatInterval { get; set; } = TimeSpan.FromSeconds(60);
    public int HistogramReservoirCap { get; set; } = 2000; // per-series per-minute
    public IReadOnlySet<string> GlobalAllowedTags { get; set; } = new HashSet<string>{ "event_type", "service", "tenant_id" };
}

// Exporter (push mode)
public interface IMeterExporter
{
    Task StartAsync(CancellationToken ct);
    Task StopAsync(CancellationToken ct);
}

// Optional: scrape endpoint (pull mode)
public interface IMeterSnapshotProvider
{
    // Returns the current minute's in-process aggregates for this instance.
    Task<IReadOnlyList<MinuteAggregate>> SnapshotAsync(CancellationToken ct);
}

// DI extensions
public static class MetricsPlatformServiceCollectionExtensions
{
    public static IServiceCollection AddPlatformMetrics(
        this IServiceCollection services,
        Action<MetricExportOptions> configure);

    public static IServiceCollection AddTenantDbResolver(
        this IServiceCollection services,
        ITenantDbResolver resolver); // you already have this pattern
}

Usage in an app (example):

builder.Services.AddPlatformMetrics(opt =>
{
    opt.EnableCentralRollup = true;
    opt.MinuteFlushInterval = TimeSpan.FromSeconds(60);
});

// Register platform metrics (and app metrics)
services.AddSingleton<IMetricRegistrar>(sp =>
{
    var reg = sp.GetRequiredService<IMetricRegistrar>();
    reg.RegisterRange(PlatformMetricCatalog.All);
    reg.Register(new("app.orders.created.count", "count", "counter", "Orders created", new[]{ "source" }));
    return reg;
});

// Hosted service (per instance)
services.AddHostedService<MeterExporterHostedService>();

---

Metric catalog (initial set)

Ship a static PlatformMetricCatalog the app can import, covering:

• Counters: outbox.published.count, inbox.processed.count, inbox.retry.count, inbox.failed.count
• Gauges: outbox.pending.count, outbox.oldest_age.seconds, dlq.depth, dlq.oldest_age.seconds, recon.gap_aggregates.count
• Histograms: outbox.publish_latency.ms, inbox.processing_latency.ms

Apps can add their own metrics via IMetricRegistrar.

---

Scheduled jobs (platform)

• Metrics.RetentionMinute (tenant DB) – delete rows older than N days.
• Metrics.RetentionHourly (central DB) – delete rows older than N days.
• Metrics.RollupAudit (central) – ensure each tenant produced an hourly bucket in last 2 hours.
• Metrics.ExporterFreshness (central) – alert if any exporter heartbeat is stale.

All jobs implemented using the platform’s job mechanism; ship job handlers in the platform package, disabled by default and enabled via config.

---

Tasks

Database (platform migrations)

• Add infra.MetricDef, infra.MetricSeries, infra.MetricPointMinute (tenant DB).

• Add infra.MetricDef, infra.MetricSeries (with TenantId), infra.MetricPointHourly, infra.ExporterHeartbeat (central DB).

• Procs:

  • infra.SpUpsertSeries (tenant), infra.SpUpsertMetricPointMinute (tenant; additive upsert with SP_GETAPPLOCK).
  • infra.SpUpsertSeriesCentral (central), infra.SpUpsertMetricPointHourly (central).

• Indexes: time + include; columnstore on hourly table; PAGE compression on all metric tables.

Platform runtime

• IMetricRegistrar + in-memory catalog; enforce tag whitelist (reject unknown tags at emission).

• Meter listener exporter (push mode):

  • Subscribe to our meters; minute buckets per (Metric, Service, InstanceId, TagsJson).
  • Tenant routing via existing tenant resolver.
  • Flush minute buckets to tenant DBs; heartbeat to central; optional hourly flush to central.
  • Conservative p95/p99 per instance (reservoir sample).

• (Optional) Scrape endpoint + scrape job (pull mode) behind feature flag.

• Exporter health integration: update infra.ExporterHeartbeat; add IHealthCheck.

Configuration & DI

• AddPlatformMetrics(Action<MetricExportOptions>) DI extension.
• Bind from appsettings (minute interval, retention days, enable central rollup, allowed tags).

Documentation

• README: how to enable metrics in an app; how to add app-specific metrics.
• Migration/versioning policy (backwards-compatible schema changes).
• Tagging guidelines (avoid high-cardinality; examples).
• Runbook: verifying exporter writes; interpreting series aggregation rules.

---

Acceptance criteria

• Platform library can be referenced by any app; with one line of DI plus default options, minute buckets are persisted per tenant and hourly rollups are persisted centrally.
• Adding a new app metric requires no schema change—only IMetricRegistrar.Register.
• With two app instances running, counter totals equal the sum of per-instance contributions for a given minute; gauges reflect latest value; latency charts use MAX(P95) across instances.
• Retention jobs cap tenant minute storage to configured days; central hourly to configured days.
• Exporter heartbeat is visible in central DB with LastFlushUtc within 2 minutes of real time.

---

Notes / Trade-offs

• Why core library? The schema + exporter + registration patterns are generic and useful to all apps; UI is intentionally excluded.
• Why push by default? Meter lives in-process; pull requires service discovery and connectivity. Push is simplest and robust; pull is optional for special cases.
• Percentiles: Per-instance p95 is acceptable for SLOs using MAX across instances. If we later need exact global quantiles, we can add mergeable fixed-bucket histograms behind the same procs without changing callers.

---

Out of scope for this issue

• Admin pages and charting (live in the app repo).
• Alert rule authoring UI (future platform feature).
• Cross-region replication of metrics (future).