Update automatically the metrics_table_view and time_series_all_metrics_view views when a new Metric is created

Parent Issue

No response

Problem Statement

Currently, telemetry data is stored in a Metric table, with each Snapshot saved as a JSON object in a snapshot field. This JSON object contains an array of metrics, where each metric is an item within this array.

The views metricstableview and timeseriesallmetricsview generate a table with each metric represented as a column. To achieve this structure (where each metric is a column instead of an array item), we use a PostgreSQL custom type. In this type, each metric is defined as an attribute. Whenever a new metric is added, we need to update both the custom type and these views. Updating the type requires dropping and recreating the views, so we have created a procedure, dailytypeandviewupdate, that updates the type and views based on the data in the Metric table.

To keep everything up to date, we now need this procedure to run automatically each day, ensuring that the views reflect any new metrics that are added.

Steps to Reproduce

• Create any Test metric it can return a hard coding numeric value.
• Run the Telemetry Job.
• Check that the Snapshot isa saved in the metric table.
• Now run a 'SELECT *' Statement for each views and you are going to see that the new Metric is not there

Acceptance Criteria

The views must updated automatically when a new Metric is created

dotCMS Version

latest

Proposed Objective

Core Features

Proposed Priority

Priority 2 - Important

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

No response

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

No response

Start by examining the daily_type_and_view_update procedure, the Metric table, and the metrics_table_view and time_series_all_metrics_view views. Trace how the Telemetry Job creates snapshots and determine where a daily execution can be scheduled. Done means newly created metrics appear in both views automatically, verified by creating a test metric and querying each view.