Silver And Gold Analytics Projection

This slice covers the medallion analytics path: keeping the current history tables as Bronze source-of-truth records, projecting standardized Silver rows for tank/element time-series analytics, and rebuilding Gold current-state summaries for dashboard and operational reads.

flowchart LR
    subgraph Bronze["Bronze source tables"]
        TESTH["DynamoDB: env-test-history"]
        DOSEH["DynamoDB: env-dose-history"]
        PROGH["DynamoDB: env-program-history"]
        TARGET["DynamoDB: env-element-target-override"]
    end

    subgraph Writers["Bronze write paths"]
        TESTSAVE["Lambda: test-save-test-history"]
        EVAL["Lambda: corrections-eval-element-test-result"]
        DOSESAVE["Lambda: dose-save-dose-history-apigw / sqs"]
        DAILY["Lambda: dose-save-daily-dose"]
        PROGSAVE["Lambda: dose-save-program-history"]
        DELETE["Lambda: dose/program delete handlers"]
    end

    subgraph Events["EventBridge"]
        REQUESTED["Rule: env-silver-projection-requested"]
        RECON["Schedule: env-silver-reconciliation"]
    end

    subgraph Projection["Projection Lambdas"]
        SILVER["Lambda: user-silver-projection"]
        REPLAY["Lambda: user-silver-reconciliation"]
        STANDARD["Shared: SilverElementStandardizer"]
    end

    subgraph Silver["Silver analytics tables"]
        OBS["DynamoDB: env-silver-element-observation"]
        DOSE["DynamoDB: env-silver-dose-event"]
        PROGRAM["DynamoDB: env-silver-program-snapshot"]
    end

    subgraph Gold["Gold analytics tables"]
        SUMMARY["DynamoDB: env-gold-tank-element-summary"]
    end

    subgraph Readers["Analytics readers"]
        TREND["Lambda: test-get-icp-element-trend"]
        DASH["Lambda: user-dashboard-list"]
        HOME["Home dashboard / element trends"]
    end

    TESTSAVE --> TESTH
    EVAL --> TESTH
    EVAL --> PROGH
    DOSESAVE --> DOSEH
    DAILY --> DOSEH
    PROGSAVE --> PROGH
    DELETE --> TESTH
    DELETE --> DOSEH
    DELETE --> PROGH

    TESTSAVE -->|"reefamatic.bronze TEST_HISTORY upsert"| REQUESTED
    EVAL -->|"completed ICP TEST_HISTORY + draft PROGRAM_HISTORY upsert"| REQUESTED
    DOSESAVE -->|"DOSE_HISTORY upsert"| REQUESTED
    DAILY -->|"DOSE_HISTORY upsert"| REQUESTED
    PROGSAVE -->|"PROGRAM_HISTORY upsert"| REQUESTED
    DELETE -->|"source delete"| REQUESTED

    REQUESTED --> SILVER
    RECON --> REPLAY

    SILVER --> TESTH
    SILVER --> DOSEH
    SILVER --> PROGH
    SILVER --> TARGET
    SILVER --> STANDARD
    REPLAY --> TESTH
    REPLAY --> DOSEH
    REPLAY --> PROGH
    REPLAY --> SILVER

    STANDARD --> OBS
    STANDARD --> DOSE
    STANDARD --> PROGRAM

    SILVER --> SUMMARY
    REPLAY --> SUMMARY
    OBS --> TREND
    DOSE --> TREND
    PROGRAM --> TREND
    SUMMARY --> DASH
    DASH --> HOME

Projection Rules

• Bronze remains the source of truth. Silver rows are derived and can be rebuilt.
• Each bronze source id is projected idempotently. The projection Lambda deletes existing Silver rows for that source id and writes the current normalized set.
• In-progress ICP upload placeholders are not projected. TEST_HISTORY sources project only when history_processing_status=COMPLETE.
• Test observations normalize element aliases, measured values, measured units, sample type (TANK or RODI), target range, and status.
• Dose events normalize element aliases, dose-product aliases, amount, unit, frequency, and ICP-correction metadata.
• Program snapshots normalize element aliases, dose-product aliases, effective start/end dates, selected weekdays, and average daily dose.
• Deletes are mirrored by deleting Silver rows for the source id.
• After each near-real-time Silver projection, RaM rebuilds Gold summaries for the affected tank so dashboards can read current state without reparsing Bronze.
• The scheduled reconciliation Lambda is a repair pass. It scans bronze test, dose, and program tables once, sends every record through the same projection service used by near-real-time events, and then rebuilds Gold summaries for each affected tank.

Gold Summary Rules

• Gold rows are derived from Silver only. They can be deleted and rebuilt without changing user-entered Bronze records.
• Each tank/element/sample-type row stores the latest and previous measured value, delta, trend direction, target range, latest dose date, 7/30/90 day dose totals, current active-program dose, and a compact response status.
• Gold is for current-state dashboards and admin summaries. Detail/edit pages still read Bronze records, and time-series chart modals read Silver.

Why This Exists

• Dashboard and trend queries read consistent tank/element rows instead of repeatedly parsing nested history documents.
• Canonical element keys let RaM compare equivalent names such as Manganese-X and Manganese, Iron-X and Iron, and phosphate/phosphorus families without rewriting bronze records.
• Normalized units, target ranges, and summary rows create a clean foundation for HILT scoring, predictive guidance, and future cost-efficient dashboard reads.