# Materialized SQL data product

## Request and classification

**Request:** "Build a 30-day service-health dashboard from the certified daily relation maintained by Data Operations." The upstream relation is artifact-adjacent; the artifact owns only a bounded read and UI.

## Preconditions and topology

Start from `scripts/artifacts/templates/multi-source-dashboard`, then replace its route, normalization, UI, and structured tests with `service-health`. Do not create, refresh, or migrate the upstream relation from artifact code or `refreshSchedules[]`.

Before using the illustrative `analytics.service_health_daily_v1` name, require the data owner and catalog to prove:

- the platform read-only service can access it and its tenant isolation/RLS is enforced independently of browser parameters;
- stable branch identity and certified metric definitions are documented;
- the daily completion deadline, timezone, retention, owner, and escalation path are known;
- `source_updated_at` represents the upstream data-product as-of time.

If tenant isolation is not proven for this custom relation, stop. `viewerPolicy: "private"` is page authorization, not a SQL tenant predicate.

## Metric and freshness contract

The dataset exposes numerators and denominators rather than pre-averaged rates. Compute 30-day completion as `sum(completedVisitCount) / sum(eligibleVisitCount)` and average duration as `sum(totalDurationMinutes) / sum(durationObservationCount)`, handling zero denominators explicitly. Validate the same weighted formulas with independent control queries.

Display `generatedAt` as delivery time and `sourceUpdatedAt` as data-product time. Define a visible stale state relative to the documented daily deadline. Retain last successful rows during refresh, show errors/empty states, and surface runtime `truncated` as partial data. Do not use a SQL-side `LIMIT` that hides truncation.

## Validation and controls

- Confirm catalog ownership, tenant isolation, metric formulas, cadence, and column types; run the exact final bounded SQL.
- Adapt template tests for the route, weighted KPI math, zero denominators, stale/partial/error/empty states, and no sample fallback.
- Compare all-company and one representative branch against direct weighted controls.
- Run `node scripts/artifacts/validate-local --dir <appDir>`, source sync, draft deploy, desktop/mobile rendered smoke, validation preparation, and validation request.

## Limitations and graduation boundary

The artifact cannot repair, schedule, or silently replace an upstream materialization. Data-quality or cadence failures remain with the documented producer. If this artifact read becomes too slow after upstream optimization, precompute only the artifact's bounded read with `refresh_live_cache`; upstream ownership does not move.