Oracle Materialized View Stale Refresh Guard

id: oracle-materialized-view-stale-refresh-guard
namespace: company.team

description: |
  Find Oracle materialized views on REFRESH ON DEMAND that are no longer fresh, compile
  the ones whose freshness Oracle cannot even determine, refresh the ones that are really
  stale with DBMS_MVIEW.REFRESH behind a dry run plus an approval pause, then read
  dba_mviews again and fail if a target did not actually refresh.

inputs:
  - id: jdbc_url
    type: STRING
    displayName: Oracle JDBC URL
    defaults: "jdbc:oracle:thin:@//localhost:1521/FREEPDB1"

  - id: username
    type: STRING
    displayName: Database user
    description: A user that can read dba_mviews (SELECT_CATALOG_ROLE or SELECT ANY
      DICTIONARY). Compiling or refreshing another schema's materialized view
      needs ALTER ANY MATERIALIZED VIEW. A DBA role covers both.
    defaults: SYSTEM

  - id: password
    type: STRING
    displayName: Database password
    defaults: "{{ secret('ORACLE_PASSWORD') }}"

  - id: mview_owners
    type: ARRAY
    itemType: STRING
    displayName: Schema owners to check
    description: Application schemas whose materialized views are checked. System
      schemas are left out so an internal view is never touched.
    defaults:
      - APP

  - id: max_age_hours
    type: FLOAT
    displayName: Maximum hours since the last refresh
    description: A view that reads FRESH but was last refreshed longer ago than this
      is also treated as a target. Set it to the freshness your reports promise.
      0 reports every on demand view.
    defaults: 24.0

  - id: refresh_method
    type: SELECT
    displayName: DBMS_MVIEW.REFRESH method code
    description: C is a complete rebuild and always works. F is a fast refresh and
      needs a materialized view log on every base table, so it fails with
      ORA-23413 without one. ? is force, which tries fast then falls back to
      complete. A refreshes all, which for one view behaves like complete.
    values:
      - C
      - F
      - "?"
      - A
    defaults: C

  - id: atomic_refresh
    type: BOOL
    displayName: Atomic refresh
    description: True keeps the view queryable for the whole refresh because the
      delete plus insert runs in one transaction. False lets Oracle truncate
      then direct path load, which is much faster but leaves the view empty to
      readers until it finishes.
    defaults: true

  - id: auto_remediate
    type: BOOL
    displayName: Auto remediate
    description: Allow ALTER MATERIALIZED VIEW COMPILE and DBMS_MVIEW.REFRESH to
      run. Off by default.
    defaults: false

  - id: dry_run
    type: BOOL
    displayName: Dry run
    description: Must be false, together with auto_remediate, for any change to run.
    defaults: true

variables:
  audit_sql: |
    SELECT m.owner AS owner,
           m.mview_name AS mview_name,
           NVL(m.staleness, 'UNKNOWN') AS staleness,
           NVL(m.compile_state, 'UNKNOWN') AS compile_state,
           m.refresh_method AS declared_method,
           m.fast_refreshable AS fast_refreshable,
           NVL(m.last_refresh_type, 'NONE') AS last_refresh_type,
           NVL(TO_CHAR(m.last_refresh_date, 'YYYY-MM-DD HH24:MI:SS'), 'never') AS last_refresh_date,
           NVL(TO_CHAR(m.stale_since, 'YYYY-MM-DD HH24:MI:SS'), 'none') AS stale_since,
           NVL(ROUND((SYSDATE - m.last_refresh_date) * 24, 4), -1) AS hours_since_refresh,
           CASE
             WHEN NVL(m.staleness, 'UNKNOWN') = 'NEEDS_COMPILE' THEN 'COMPILE'
             WHEN NVL(m.staleness, 'UNKNOWN') IN ('UNUSABLE', 'COMPILATION_ERROR', 'ERROR') THEN 'REPORT_ONLY'
             WHEN NVL(m.staleness, 'UNKNOWN') = 'FRESH' THEN 'REFRESH_BY_AGE'
             ELSE 'REFRESH'
           END AS action,
           'BEGIN DBMS_MVIEW.REFRESH(list => ''' || m.owner || '.' || m.mview_name
             || ''', method => ''{{ inputs.refresh_method }}'', atomic_refresh => '
             || '{{ inputs.atomic_refresh ? "TRUE" : "FALSE" }}' || '); END;' AS fix_sql,
           'ALTER MATERIALIZED VIEW "' || m.owner || '"."' || m.mview_name || '" COMPILE' AS compile_sql
      FROM dba_mviews m
     WHERE m.owner IN ('{{ inputs.mview_owners | join("','") | upper }}')
       AND m.refresh_mode = 'DEMAND'
       AND ( NVL(m.staleness, 'UNKNOWN') <> 'FRESH'
             OR m.last_refresh_date IS NULL
             OR (SYSDATE - m.last_refresh_date) * 24 >= {{ inputs.max_age_hours }} )
     ORDER BY m.owner, m.mview_name

  totals_sql: |
    SELECT COUNT(*) AS mview_count,
           NVL(SUM(CASE WHEN m.staleness = 'FRESH' THEN 1 ELSE 0 END), 0) AS fresh_count,
           NVL(SUM(CASE WHEN m.staleness = 'STALE' THEN 1 ELSE 0 END), 0) AS stale_count,
           NVL(SUM(CASE WHEN m.staleness = 'NEEDS_COMPILE' THEN 1 ELSE 0 END), 0) AS needs_compile_count,
           NVL(SUM(CASE WHEN m.staleness IN ('UNUSABLE', 'COMPILATION_ERROR', 'ERROR') THEN 1 ELSE 0 END), 0) AS broken_count,
           NVL(SUM(CASE WHEN NVL(m.staleness, 'UNKNOWN') NOT IN ('FRESH', 'STALE', 'NEEDS_COMPILE', 'UNUSABLE', 'COMPILATION_ERROR', 'ERROR') THEN 1 ELSE 0 END), 0) AS other_count,
           NVL(TO_CHAR(MIN(m.last_refresh_date), 'YYYY-MM-DD HH24:MI:SS'), 'never') AS oldest_refresh
      FROM dba_mviews m
     WHERE m.owner IN ('{{ inputs.mview_owners | join("','") | upper }}')
       AND m.refresh_mode = 'DEMAND'

  compile_check_sql: |
    SELECT m.owner AS owner,
           m.mview_name AS mview_name,
           NVL(m.staleness, 'UNKNOWN') AS staleness,
           NVL(m.compile_state, 'UNKNOWN') AS compile_state
      FROM dba_mviews m
     WHERE INSTR(',{{ outputs.targets.values.compile_names }},', ',' || m.owner || '.' || m.mview_name || ',') > 0
       AND ( NVL(m.staleness, 'UNKNOWN') = 'NEEDS_COMPILE'
             OR NVL(m.compile_state, 'UNKNOWN') IN ('NEEDS_COMPILE', 'ERROR', 'COMPILATION_ERROR') )
     ORDER BY m.owner, m.mview_name

  reverify_sql: |
    SELECT m.owner AS owner,
           m.mview_name AS mview_name,
           NVL(m.staleness, 'UNKNOWN') AS staleness,
           NVL(TO_CHAR(m.last_refresh_date, 'YYYY-MM-DD HH24:MI:SS'), 'never') AS last_refresh_date,
           CASE
             WHEN m.last_refresh_date IS NULL THEN 'never refreshed'
             WHEN m.last_refresh_date < TO_DATE('{{ outputs.mark_time.row.DB_CLOCK }}', 'YYYY-MM-DD HH24:MI:SS') THEN 'refresh did not run'
             ELSE 'refreshed but staleness is ' || NVL(m.staleness, 'UNKNOWN')
           END AS reason
      FROM dba_mviews m
     WHERE INSTR(',{{ outputs.resolved_targets.values.name_list }},', ',' || m.owner || '.' || m.mview_name || ',') > 0
       AND ( NVL(m.staleness, 'UNKNOWN') <> 'FRESH'
             OR m.last_refresh_date IS NULL
             OR m.last_refresh_date < TO_DATE('{{ outputs.mark_time.row.DB_CLOCK }}', 'YYYY-MM-DD HH24:MI:SS') )
     ORDER BY m.owner, m.mview_name

tasks:
  - id: totals_before
    type: io.kestra.plugin.jdbc.oracle.Query
    description: Read-only. How many on demand materialized views the checked
      schemas hold, split by staleness state, plus the oldest last refresh.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH_ONE
    sql: "{{ render(vars.totals_sql) }}"

  - id: audit
    type: io.kestra.plugin.jdbc.oracle.Query
    description: Read-only. Lists the on demand materialized views that are not
      FRESH or whose last refresh is past the age threshold, with the staleness
      value, the compile state, whether a fast refresh is possible at all plus
      the exact COMPILE and DBMS_MVIEW.REFRESH statements for each.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH
    sql: "{{ render(vars.audit_sql) }}"

  - id: targets
    type: io.kestra.plugin.core.output.OutputValues
    description: Split the findings by what the staleness value actually calls for.
      STALE, UNKNOWN, UNDEFINED plus aged FRESH views are refresh targets.
      NEEDS_COMPILE views are compile targets, because their freshness is not
      known yet. UNUSABLE views are reported only.
    values:
      refresh_sql: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "REFRESH" or
        .ACTION == "REFRESH_BY_AGE") | .FIX_SQL]') | first | toJson }}
      refresh_count: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "REFRESH" or
        .ACTION == "REFRESH_BY_AGE")] | length') | first }}
      refresh_detail: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "REFRESH" or
        .ACTION == "REFRESH_BY_AGE") | .OWNER + "." + .MVIEW_NAME + " [" +
        .STALENESS + ", " + .ACTION + ", last refresh " + .LAST_REFRESH_DATE +
        ", " + (.HOURS_SINCE_REFRESH | tostring) + "h ago, fast_refreshable " +
        .FAST_REFRESHABLE + "]"] | join("; ")') | first }}
      compile_sql: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "COMPILE") |
        .COMPILE_SQL]') | first | toJson }}
      compile_count: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "COMPILE")] |
        length') | first }}
      compile_names: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "COMPILE") | .OWNER
        + "." + .MVIEW_NAME] | (if length == 0 then ["__NO_TARGET__"] else .
        end) | join(",")') | first }}
      compile_detail: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "COMPILE") | .OWNER
        + "." + .MVIEW_NAME + " [compile_state " + .COMPILE_STATE + ", last
        refresh " + .LAST_REFRESH_DATE + "]"] | join("; ")') | first }}
      broken_count: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "REPORT_ONLY")] |
        length') | first }}
      broken_detail: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "REPORT_ONLY") |
        .OWNER + "." + .MVIEW_NAME + " [" + .STALENESS + ", last refresh type "
        + .LAST_REFRESH_TYPE + "]"] | join("; ")') | first }}

  - id: summary
    type: io.kestra.plugin.core.log.Log
    message: >-
      Oracle on demand materialized views on {{ inputs.jdbc_url }} for schemas
      {{ inputs.mview_owners | jq('map(ascii_upcase) | join(", ")') | first }}:
      {{ outputs.totals_before.row.MVIEW_COUNT }} total, {{
      outputs.totals_before.row.FRESH_COUNT }} FRESH, {{
      outputs.totals_before.row.STALE_COUNT }} STALE, {{
      outputs.totals_before.row.NEEDS_COMPILE_COUNT }} NEEDS_COMPILE, {{
      outputs.totals_before.row.BROKEN_COUNT }} broken, {{
      outputs.totals_before.row.OTHER_COUNT }} in another state. Oldest last
      refresh {{ outputs.totals_before.row.OLDEST_REFRESH }}. {{
      outputs.audit.size }} view(s) are outside the freshness policy of {{
      inputs.max_age_hours }} hour(s): {{ outputs.targets.values.refresh_count
      }} refreshable now with method {{ inputs.refresh_method }} ({{
      outputs.targets.values.refresh_detail }}), {{
      outputs.targets.values.compile_count }} needing a COMPILE before their
      freshness is even known ({{ outputs.targets.values.compile_detail }}), {{
      outputs.targets.values.broken_count }} broken or unusable and reported
      only ({{ outputs.targets.values.broken_detail }}).

  - id: route
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.audit.size > 0 }}"
    then:
      - id: alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {{ {'text': 'Oracle ' ~ inputs.jdbc_url ~ ': ' ~ outputs.audit.size ~ ' on demand materialized view(s) are outside the ' ~ inputs.max_age_hours ~ 'h freshness policy and may be answering queries from old rows. Stale, refreshable: ' ~ (outputs.targets.values.refresh_detail == '' ? 'none' : outputs.targets.values.refresh_detail) ~ '. Freshness unknown until compiled: ' ~ (outputs.targets.values.compile_detail == '' ? 'none' : outputs.targets.values.compile_detail) ~ '. Broken or unusable, reported only: ' ~ (outputs.targets.values.broken_detail == '' ? 'none' : outputs.targets.values.broken_detail) ~ '.' ~ (inputs.auto_remediate and not inputs.dry_run ? ' Compile plus refresh with method ' ~ inputs.refresh_method ~ ' is armed and waiting for approval.' : ' No change applied (auto_remediate=' ~ inputs.auto_remediate ~ ', dry_run=' ~ inputs.dry_run ~ ').')} | toJson }}

      - id: state_route
        type: io.kestra.plugin.core.flow.If
        description: Route on the staleness value. NEEDS_COMPILE and UNUSABLE are not
          data staleness, so neither is handed straight to DBMS_MVIEW.REFRESH.
        condition: "{{ (outputs.targets.values.compile_count | number) +
          (outputs.targets.values.broken_count | number) > 0 }}"
        then:
          - id: state_explained
            type: io.kestra.plugin.core.log.Log
            level: WARN
            message: >-
              {{ outputs.targets.values.compile_count }} view(s) read
              NEEDS_COMPILE, which is not the same as STALE: Oracle marks a view
              NEEDS_COMPILE after any base object change it cannot track
              incrementally. Until ALTER MATERIALIZED VIEW COMPILE runs,
              dba_mviews cannot say whether the data moved at all ({{
              outputs.targets.values.compile_detail }}). A COMPILE is metadata
              only. It touches no rows and it resolves the state to STALE or
              FRESH. If it errors instead, the base change broke the definition,
              which a blind refresh would have hidden behind a long outage. So
              this flow compiles first, re-reads, then refreshes only what is
              really stale. {{ outputs.targets.values.broken_count }} view(s)
              read UNUSABLE, COMPILATION_ERROR or ERROR ({{
              outputs.targets.values.broken_detail }}). Those are not data
              staleness at all. COMPILATION_ERROR is what a view reads once a
              compile has already been tried and the definition no longer holds,
              which happens when a base column the view selects was dropped or
              retyped. UNUSABLE usually follows a refresh that was interrupted
              with atomic_refresh set to false. Both are reported here rather
              than refreshed, because the fix is a definition change or a
              rebuild, not more data.
        else:
          - id: state_clean
            type: io.kestra.plugin.core.log.Log
            message: "Every finding is plain data staleness. No view in the checked schemas
              is NEEDS_COMPILE or UNUSABLE, so DBMS_MVIEW.REFRESH is the right
              verb for all of them."

      - id: remediate_gate
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run and
          (outputs.targets.values.refresh_count | number) +
          (outputs.targets.values.compile_count | number) > 0 }}"
        then:
          - id: approval_gate
            type: io.kestra.plugin.core.flow.Pause
            description: Hold the run until a human approves. A complete refresh rereads
              every base row, writes the whole container table again plus can
              run for hours on a large view, so it never starts on its own.
            pauseDuration: PT2H
            behavior: FAIL
            onResume:
              - id: approved
                type: BOOL
                defaults: false
                description: Set true to compile the undetermined views then refresh the stale
                  ones with the chosen method. Anything else leaves every view
                  as it is.

          - id: apply
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.approval_gate.onResume.approved ?? false }}"
            then:
              - id: mark_time
                type: io.kestra.plugin.jdbc.oracle.Query
                description: Read the database clock before any change, so the re-read can
                  require a last_refresh_date at or after this moment. That
                  proves the refresh ran rather than trusting a FRESH flag.
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.username }}"
                password: "{{ inputs.password }}"
                fetchType: FETCH_ONE
                sql: "SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS db_clock FROM dual"

              - id: compile_stage
                type: io.kestra.plugin.core.flow.If
                condition: "{{ (outputs.targets.values.compile_count | number) > 0 }}"
                then:
                  - id: compile_pass
                    type: io.kestra.plugin.core.flow.Loop
                    description: One ALTER MATERIALIZED VIEW COMPILE per undetermined view. Metadata
                      only, no rows are read or written. Sequential so a
                      dependent set compiles in a stable order.
                    concurrencyLimit: 1
                    values: "{{ outputs.targets.values.compile_sql }}"
                    tasks:
                      - id: compile_one
                        type: io.kestra.plugin.jdbc.oracle.Query
                        description: allowFailure, so one view whose definition really is broken does
                          not stop the others from being compiled plus reported.
                        allowFailure: true
                        url: "{{ inputs.jdbc_url }}"
                        username: "{{ inputs.username }}"
                        password: "{{ inputs.password }}"
                        fetchType: NONE
                        sql: "{{ item.value }}"

                  - id: compile_check
                    type: io.kestra.plugin.jdbc.oracle.Query
                    description: Read dba_mviews again for the compiled views. Anything still
                      NEEDS_COMPILE or carrying an error compile_state did not
                      compile.
                    url: "{{ inputs.jdbc_url }}"
                    username: "{{ inputs.username }}"
                    password: "{{ inputs.password }}"
                    fetchType: FETCH
                    sql: "{{ render(vars.compile_check_sql) }}"

                  - id: compile_verify
                    type: io.kestra.plugin.core.flow.If
                    condition: "{{ outputs.compile_check.size > 0 }}"
                    then:
                      - id: compile_failed
                        type: io.kestra.plugin.core.execution.Fail
                        errorMessage: >-
                          {{ outputs.compile_check.size }} of {{
                          outputs.targets.values.compile_count }} view(s) are
                          still unresolved after the compile pass: {{
                          outputs.compile_check.rows | jq('map(.OWNER + "." +
                          .MVIEW_NAME + " [" + .STALENESS + ", compile_state " +
                          .COMPILE_STATE + "]") | join(", ")') | first }}. A
                          view that will not compile has a definition its base
                          objects no longer support, so a dropped or retyped
                          base column is the usual cause. Fix the definition
                          before any refresh, because a refresh would empty the
                          container and then fail.
                    else:
                      - id: compiled
                        type: io.kestra.plugin.core.log.Log
                        message: "Compiled {{ fromJson(outputs.targets.values.compile_sql) | length }}
                          view(s). Their real staleness is now readable in
                          dba_mviews, so the re-audit decides which of them
                          actually need data."

              - id: reaudit
                type: io.kestra.plugin.jdbc.oracle.Query
                description: Re-run the audit after the compile pass. A view that was
                  NEEDS_COMPILE now reads STALE (it needs data) or FRESH (the
                  base change did not alter the result set, so no refresh is
                  needed).
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.username }}"
                password: "{{ inputs.password }}"
                fetchType: FETCH
                sql: "{{ render(vars.audit_sql) }}"

              - id: resolved_targets
                type: io.kestra.plugin.core.output.OutputValues
                description: The refresh list after the compile pass resolved the undetermined
                  views.
                values:
                  refresh_sql: >-
                    {{ outputs.reaudit.rows | jq('[.[] | select(.ACTION ==
                    "REFRESH" or .ACTION == "REFRESH_BY_AGE") | .FIX_SQL]') |
                    first | toJson }}
                  refresh_count: >-
                    {{ outputs.reaudit.rows | jq('[.[] | select(.ACTION ==
                    "REFRESH" or .ACTION == "REFRESH_BY_AGE")] | length') |
                    first }}
                  name_list: >-
                    {{ outputs.reaudit.rows | jq('[.[] | select(.ACTION ==
                    "REFRESH" or .ACTION == "REFRESH_BY_AGE") | .OWNER + "." +
                    .MVIEW_NAME] | (if length == 0 then ["__NO_TARGET__"] else .
                    end) | join(",")') | first }}
                  refresh_detail: >-
                    {{ outputs.reaudit.rows | jq('[.[] | select(.ACTION ==
                    "REFRESH" or .ACTION == "REFRESH_BY_AGE") | .OWNER + "." +
                    .MVIEW_NAME + " [" + .STALENESS + ", stale since " +
                    .STALE_SINCE + "]"] | join("; ")') | first }}

              - id: refresh_stage
                type: io.kestra.plugin.core.flow.If
                condition: "{{ (outputs.resolved_targets.values.refresh_count | number) > 0 }}"
                then:
                  - id: remediate
                    type: io.kestra.plugin.core.flow.Loop
                    description: One DBMS_MVIEW.REFRESH call per view. Sequential on purpose, since
                      two complete refreshes at once compete for undo plus temp
                      and can push each other into a tablespace error.
                    concurrencyLimit: 1
                    values: "{{ outputs.resolved_targets.values.refresh_sql }}"
                    tasks:
                      - id: refresh_one
                        type: io.kestra.plugin.jdbc.oracle.Query
                        description: An anonymous PL/SQL block calling DBMS_MVIEW.REFRESH for one view.
                          allowFailure, so a view with no materialized view log
                          under method F does not stop the rest of the pass. The
                          re-read is what decides the run.
                        allowFailure: true
                        url: "{{ inputs.jdbc_url }}"
                        username: "{{ inputs.username }}"
                        password: "{{ inputs.password }}"
                        fetchType: NONE
                        sql: "{{ item.value }}"

                  - id: reverify
                    type: io.kestra.plugin.jdbc.oracle.Query
                    description: Read dba_mviews again for the exact views that were refreshed. A
                      view only passes when it reads FRESH and its
                      last_refresh_date is at or after the clock reading taken
                      before the change.
                    url: "{{ inputs.jdbc_url }}"
                    username: "{{ inputs.username }}"
                    password: "{{ inputs.password }}"
                    fetchType: FETCH
                    sql: "{{ render(vars.reverify_sql) }}"

                  - id: totals_after
                    type: io.kestra.plugin.jdbc.oracle.Query
                    description: Re-read the per state counts so the run reports what the pass
                      actually changed.
                    url: "{{ inputs.jdbc_url }}"
                    username: "{{ inputs.username }}"
                    password: "{{ inputs.password }}"
                    fetchType: FETCH_ONE
                    sql: "{{ render(vars.totals_sql) }}"

                  - id: verify_result
                    type: io.kestra.plugin.core.flow.If
                    condition: "{{ outputs.reverify.size > 0 }}"
                    then:
                      - id: remediation_failed
                        type: io.kestra.plugin.core.execution.Fail
                        errorMessage: >-
                          {{ outputs.reverify.size }} of {{
                          outputs.resolved_targets.values.refresh_count }}
                          refreshed view(s) did not come back fresh: {{
                          outputs.reverify.rows | jq('map(.OWNER + "." +
                          .MVIEW_NAME + " [" + .STALENESS + ", last refresh " +
                          .LAST_REFRESH_DATE + ", " + .REASON + "]") | join(",
                          ")') | first }}. The schemas now hold {{
                          outputs.totals_after.row.FRESH_COUNT }} FRESH, {{
                          outputs.totals_after.row.STALE_COUNT }} STALE, {{
                          outputs.totals_after.row.NEEDS_COMPILE_COUNT }}
                          NEEDS_COMPILE, {{
                          outputs.totals_after.row.BROKEN_COUNT }} broken, from
                          {{ outputs.totals_before.row.FRESH_COUNT }}, {{
                          outputs.totals_before.row.STALE_COUNT }}, {{
                          outputs.totals_before.row.NEEDS_COMPILE_COUNT }}, {{
                          outputs.totals_before.row.BROKEN_COUNT }}. Method F
                          fails with ORA-23413 when a base table has no
                          materialized view log. Any refresh is refused without
                          ALTER ANY MATERIALIZED VIEW on another schema's view.
                          Check the refresh_method input plus the privileges of
                          the username input.
                    else:
                      - id: remediated
                        type: io.kestra.plugin.core.log.Log
                        message: >-
                          Refreshed {{
                          fromJson(outputs.resolved_targets.values.refresh_sql)
                          | length }} materialized view(s) with method {{
                          inputs.refresh_method }}, atomic_refresh {{
                          inputs.atomic_refresh }}: {{
                          outputs.resolved_targets.values.refresh_detail }}.
                          Every one now reads FRESH with a last_refresh_date at
                          or after {{ outputs.mark_time.row.DB_CLOCK }}, the
                          database clock read before the pass started. The
                          checked schemas went from {{
                          outputs.totals_before.row.FRESH_COUNT }} FRESH, {{
                          outputs.totals_before.row.STALE_COUNT }} STALE, {{
                          outputs.totals_before.row.NEEDS_COMPILE_COUNT }}
                          NEEDS_COMPILE to {{
                          outputs.totals_after.row.FRESH_COUNT }} FRESH, {{
                          outputs.totals_after.row.STALE_COUNT }} STALE, {{
                          outputs.totals_after.row.NEEDS_COMPILE_COUNT }}
                          NEEDS_COMPILE. Oldest last refresh now {{
                          outputs.totals_after.row.OLDEST_REFRESH }}.
                else:
                  - id: nothing_to_refresh
                    type: io.kestra.plugin.core.log.Log
                    message: >-
                      The compile pass resolved every undetermined view to
                      FRESH, so no data refresh was needed. The base objects
                      changed in a way that did not alter what the view returns.
                      {{ outputs.totals_before.row.NEEDS_COMPILE_COUNT }}
                      view(s) were NEEDS_COMPILE before the pass, {{
                      outputs.reaudit.size }} finding(s) remain.
            else:
              - id: declined
                type: io.kestra.plugin.core.log.Log
                message: "Not approved. {{ outputs.targets.values.refresh_count }} stale view(s)
                  plus {{ outputs.targets.values.compile_count }} undetermined
                  view(s) are left exactly as they are, still serving the rows
                  they already hold."
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: >-
          Every on demand materialized view in the checked schemas is FRESH with
          a last refresh inside {{ inputs.max_age_hours }} hour(s). {{
          outputs.totals_before.row.MVIEW_COUNT }} view(s) checked, oldest last
          refresh {{ outputs.totals_before.row.OLDEST_REFRESH }}.

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {{ {'text': 'Oracle materialized view stale refresh guard failed on ' ~ inputs.jdbc_url ~ ' (execution ' ~ execution.id ~ '). Materialized views may still be answering queries from old rows. Check the logs.'} | toJson }}

triggers:
  - id: daily
    type: io.kestra.plugin.core.trigger.Schedule
    description: Shipped disabled and always in safe mode (report only).
    cron: "0 6 * * *"
    disabled: true
    inputs:
      auto_remediate: false
      dry_run: true