Freshmark replication server

freshmark-canonical

Markdown document

Freshmark Report Runner Phase

report-runner-phase.md

Freshmark Report Runner Phase

Why this phase exists

The menu simulator has already proven the core chain:

  1. Read a menu row from ZM_MENU
  2. Parse ZM_PGM
  3. Resolve the target to a report key in ZE_ENQ
  4. Build preview SQL from report metadata
  5. Query the replicated fms data schema
  6. Return sample rows

That means we are past the "is this metadata real?" stage.

The next phase is to move from a limited report preview to a usable report runner.

Current state

What works today:

  • menu items can be classified as submenu, program, report, or unknown
  • report-backed menu items can load ZE_ENQ metadata
  • the simulator can build live preview SQL from:
  • ZE_TABLE
  • ZE_JOIN
  • ZE_SELECTION
  • the preview can execute against the configured data schema when live access is enabled

What is still missing:

  • runtime parameter capture
  • selector-driven prompts from ZS_SEL
  • wrapper-program decoding from ZP_PGMS and ZX_PGMS_FIELDS
  • explicit handling of ZE_ORDER
  • column projection and heading logic from report metadata
  • a stable execution model for reports that are more than SELECT *

Current execution flow

flowchart LR A[ZM_MENU row] --> B[Parse ZM_PGM] B --> C{Target kind} C -->|submenu| D[Open submenu] C -->|program| E[Show program metadata] C -->|report| F[Load ZE_ENQ definition] F --> G[Build preview SQL] G --> H["SELECT * FROM ZE_TABLE<br/>+ ZE_JOIN<br/>+ ZE_SELECTION<br/>+ LIMIT"] H --> I[(fms data schema)] I --> J[Return columns and sample rows]

Target execution flow

flowchart LR A[Menu launch] --> B[Resolve report key] B --> C[Load ZE_ENQ report definition] C --> D[Resolve prompts and selectors] D --> E[Collect runtime values] E --> F[Compile report query plan] F --> G[Validate table join filter order] G --> H[Execute parameterized SQL] H --> I[Apply report headings and layout rules] I --> J[Rendered report result]

Runtime components we need

1. Report launcher resolution

We already resolve ZM_MENU.ZM_PGM to a report key in the happy path.

What still needs work:

  • identify wrapper launch patterns that do not point straight at ZE_ENQ.ZE_KEY
  • understand whether ZM_PGMBEFORE and ZM_PGMAFTER inject state, prompts, or side effects
  • separate "direct report launch" from "program opens a prompt screen and then launches report"

2. Prompt and selector model

This is the biggest missing layer.

Likely metadata sources:

  • ZS_SEL for reusable lookup and selector definitions
  • ZX_PGMS_FIELDS for prompt-screen fields and embedded runtime logic
  • ZP_PGMS for the wrapper program that hosts a prompt screen

We need to answer:

  • which report launches require user input before execution
  • how selector keys like S:DEBTOR_CR2 map into SQL conditions
  • whether parameter values are injected into ZE_SELECTION, ZE_JOIN, or both
  • which prompts are optional versus required

Shortest path to a usable runner

Slice 1. Query plan endpoint

Add a backend layer that compiles a report into a structured query plan before execution.

Suggested output:

  • report key
  • base table
  • join fragment
  • where fragment
  • order fragment
  • required runtime inputs
  • unresolved tokens
  • compiled SQL preview

Why first:

  • it gives us a stable debugging artifact
  • it separates metadata decoding from execution
  • it makes the next UI step much easier

Slice 2. Explicit ZE_ORDER support

The preview path currently exposes ZE_ORDER in the UI but does not compile it into SQL.

This is the smallest high-value upgrade because it:

  • improves fidelity immediately
  • requires no new UI concepts
  • keeps the query planner honest

Slice 3. Promptless report runner

Support reports that can run with no extra user input.

Definition of done:

  • choose report from menu simulator or reports list
  • compile the query plan
  • execute with table, join, selection, and order
  • return rows plus the final SQL

This gets us from "preview" to "runner" for the simplest class of reports.

Slice 4. Prompt schema extraction

Build a metadata model for prompts even before the UI is complete.

Suggested normalized shape:

  • prompt key
  • label
  • source table or selector
  • field name
  • operator
  • required flag
  • multi-select flag
  • default value

This model should come from ZS_SEL plus relevant ZX_CODE token families.

Slice 5. Parameterized runner UI

Once the prompt schema exists, add a runtime form that:

  • renders prompt fields
  • runs selectors
  • submits values to the query-plan compiler
  • executes the report with those values bound

Slice 6. Layout fidelity

After execution works, improve presentation.

Likely sources:

  • ZE_FIELDS
  • ZE_HDG
  • wrapper program metadata
  • field definitions referenced by the launching screen

This is where we move from "correct rows" to "report that looks like Freshmark".

sequenceDiagram participant U as User participant UI as Simulator or Report UI participant API as FastAPI participant COMP as Query Plan Compiler participant DB as MariaDB Replica U->>UI: Choose menu item or report UI->>API: Request report plan API->>COMP: Load ZE_ENQ and related metadata COMP-->>API: Query plan + required prompts API-->>UI: Plan response U->>UI: Enter prompt values UI->>API: Execute report with inputs API->>COMP: Compile final SQL COMP-->>API: SQL + resolved metadata API->>DB: Execute parameterized query DB-->>API: Rows API-->>UI: Rows + headings + diagnostics

Metadata dependencies by responsibility

Responsibility Likely source Notes
menu entry point fmsx.ZM_MENU starting point for user navigation
direct report definition fmsz.ZE_ENQ base table, joins, filters, headings, ordering
selector definitions fmsz.ZS_SEL lookup dialogs and reusable selection rules
wrapper program fmsz.ZP_PGMS identifies report-launch programs and prompt screens
field and logic model fmsz.ZX_PGMS_FIELDS prompt fields, ZX_CODE, validation, derived logic
table ownership fmsz.ZT_TABLE tells us which schema a referenced table belongs to

Success criteria for this phase

We should consider the report-runner phase successful when we can do all of the following for a representative subset of reports:

  • start from a real menu item
  • determine whether the launch is direct or wrapper-based
  • identify all required runtime inputs
  • compile the final SQL deterministically
  • execute it safely against the replica
  • display returned rows with meaningful headings
  • explain which metadata rows were used to build the result

Suggested first report samples

Use a small set of representative reports instead of trying to support all ZE_ENQ rows at once.

Recommended buckets:

  • one report with no prompts
  • one report with a simple ZS_SEL selector
  • one report launched through a wrapper program
  • one report with non-trivial joins
  • one report where ZX_CODE clearly injects runtime behavior

Immediate next implementation tasks

  1. Add a query-plan function that returns table, join, where, order, sql, and unresolved tokens.
  2. Extend the current preview SQL builder to compile ZE_ORDER.
  3. Add a "plan only" API route so we can inspect report compilation without executing it.
  4. Choose three sample reports and document their launcher path from ZM_MENU to ZE_ENQ.
  5. Start a token inventory for prompt-related ZX_CODE and selector references.