# fusion-query-mcp

An MCP server that lets an AI agent explore Oracle Fusion Cloud (SaaS) schema
metadata, run **pre-built BI Publisher reports** with bind parameters, and — the
part that matters — **validate the results against ground truth you already
trust**, iterating until the answer is provably right.

> **Read §1 before anything else.** This server does *not* run arbitrary SQL,
> and that is not a gap waiting to be filled. It is a boundary the Fusion pod
> enforces. Every design decision below follows from it.

---

## 1. The boundary that shapes everything

### 1.1 Fusion has no database

Oracle Fusion SaaS gives **no direct database access**. The only sanctioned way
to get SQL results out is a BI Publisher **Data Model** of type *SQL Query*,
executed through the BI Publisher **Run Report** API. The SQL inside a data
model is fixed at design time; only *parameter values* travel over the API.

That much is documented Oracle behaviour. The interesting part is what happens
next.

### 1.2 The obvious design, and why it is dead

The obvious way to build a query server on top of that is to create **one**
data model whose SQL is a *lexical* parameter holding the whole statement:

```sql
SELECT * FROM (&p_query)      -- Plan A
SELECT &p_select FROM &p_from &p_where &p_group &p_order   -- Plan B
```

An `&`-prefixed BI Publisher parameter is substituted into the SQL text before
parsing, so in principle it can carry anything. This is the widely repeated
community pattern, and it is what this project was originally built around.

**It does not work on a current Fusion pod.** Controlled experiments against the
customer's live pod, not guesses:

| # | Experiment | Result |
|---|---|---|
| 1 | Run the report with `p_query = SELECT 1 AS N FROM DUAL`, parameter proven to arrive | `ORA-00903: invalid table name` |
| 2 | Inspect what the engine executed | `SELECT * FROM ()` — the lexical was replaced by an **empty string** |
| 3 | Same with a *bind* parameter (`:p_x`) and a sentinel value | Sentinel came back **verbatim** |

So: **binds are substituted, lexicals are not.** Not "malformed", not
"mis-declared" — substituted with nothing, silently, every time. Plan B fails
identically because it also uses `&`. This is near-certainly deliberate Oracle
hardening: arbitrary SQL text arriving over a SaaS reporting API is exactly the
thing a multi-tenant vendor must prevent, and a lexical parameter is the hole
that would allow it.

**Consequences for anyone tempted to "fix" this:**

- There is no clever escaping, encoding or wrapper that restores lexicals. The
  substitution happens (or does not) inside BI Publisher, before your text is
  ever SQL.
- `View Data` in the data-model editor also renders the lexical empty, so you
  cannot distinguish "my parameter is wrong" from "this pod does not do
  lexicals" by looking at the editor. Only the round trip tells you, and it
  tells you `ORA-00903` either way. That ambiguity is what costs the day.
- The failure mode is *identical* to a beginner mistake, which is why the
  original design survived so long before being disproved.

### 1.3 What the server does instead

**Pre-built reports, run with bind values.**

An administrator creates each report's data model once, with real SQL and
`:bind` variables. The server never sends SQL — it sends *values* for the binds
of a report that already exists in the catalog. The registry of runnable reports
lives in `config.yaml` under `fusion.reports`.

Schema exploration survives in full, because its SQL is fixed and only the LIKE
patterns vary. That is what the three shipped reports are.

```
┌──────────────┐   MCP (stdio)   ┌──────────────────────────┐   HTTPS   ┌──────────────────────────────┐
│ Claude Code /│ ───────────────▶│  fusion-query-mcp        │ ─────────▶│ Oracle Fusion Cloud          │
│ Claude agent │                 │  ├ report registry       │           │  BI Publisher                │
└──────────────┘                 │  ├ execution backends    │           │  ├ XX_MCP_LIST_TABLES_RPT    │
                                 │  │  ├ SOAP (this pod)    │           │  ├ XX_MCP_DESCRIBE_TABLE_RPT │
                                 │  │  └ REST (404s here)   │           │  ├ XX_MCP_SEARCH_COLUMNS_RPT │
                                 │  ├ xml result parser     │           │  └ …your own reports         │
                                 │  ├ validation engine     │           │     SQL fixed, :binds vary   │
                                 │  ├ fixtures store        │           └──────────────────────────────┘
                                 │  ├ fusion hints KB       │
                                 │  └ audit log             │
                                 └──────────────────────────┘
```

This costs flexibility and buys three things worth having:

1. **It works.** The path is verified end to end on a live pod.
2. **A real boundary.** An agent can only run what an administrator built and
   registered. See [§9 Security](#9-security-privacy-audit).
3. **Reviewable SQL.** Every statement lives in a catalog object a functional
   owner can open, read and change, instead of being improvised per request.

**Non-goals:** no data model / catalog object creation via API; no DML or DDL of
any kind; no pagination or streaming; no OTBI logical SQL; no per-end-user
identity mapping (single service account).

### 1.4 The legacy lexical path is still shipped

`fusion_run_query` and `fusion_validate_query` still exist, and
`fusion.datasources` / `engine_mode` still configure them. They are for a pod
that *does* honour lexical substitution. On a pod that does not — and you should
assume yours does not until you have proved otherwise — they will fail with
`ORA-00903` and the server's error `hint` will tell you exactly that and point
you at the report tools. Do not spend a day there.

---

## 2. The tool surface

All tools are read-only against Fusion's *data* and return structured JSON with
a short `summary`. Two write to the BI Publisher catalog rather than to
business records: `fusion_author_report` (opt-in) and `fusion_bootstrap`.

| Tool | Purpose |
|---|---|
| `fusion_list_reports` | What this server may run at all: registered reports, their parameters and defaults. **Start here.** |
| `fusion_run_report` | Run a registered report with bind values |
| `fusion_validate_report` | Run a registered report once, evaluate ground-truth expectations |
| `fusion_list_tables` | Tables by LIKE pattern, biggest first (runs `list_tables`) |
| `fusion_describe_table` | Ordered columns, types, comments (runs `describe_table`) |
| `fusion_search_columns` | Which table holds column X (runs `search_columns`) |
| `fusion_docs_describe_table` | Table docs from the **local OEDM snapshot**: column meanings, PK/FK join edges, view SQL — instant, no pod call (§2b) |
| `fusion_docs_search_columns` | Full-text search over documented column names *and descriptions* in the local snapshot (§2b) |
| `fusion_describe_flexfields` | DFF definitions: which `ATTRIBUTEn` column carries which custom segment (runs `describe_flexfields`) |
| `fusion_adhoc_query` | **Opt-in** (§2a): run one guarded SELECT via an ephemeral authored report |
| `fusion_author_report` | **Opt-in** (§2a): mint a persistent report from SQL and register it |
| `fusion_save_fixture` | Persist expectations as a regression test |
| `fusion_list_fixtures` / `fusion_get_fixture` | Browse saved fixtures |
| `fusion_get_hints` | Fusion schema traps and correct predicates |
| `fusion_list_pods` | Which pods this server can reach (from `pods/*/config.yaml`) and which is active |
| `fusion_use_pod` | Bind the session to one pod — config, credentials, reports, snapshot switch together |
| `fusion_bootstrap` | Create the four **shipped** exploration reports on a pod that lacks them, from SQL inside this package — the fix when the tools above fail on a healthy pod (§3.0) |
| `fusion_health_check` | Probe credentials, transport, parsing, registry |
| `fusion_run_query` / `fusion_validate_query` | **Legacy lexical path** — needs a pod that substitutes lexicals (§1.4) |

Also exposed: the knowledge base as MCP **resources** (`fusion://hints`,
`fusion://hints/{topic}`) and the working protocol as an MCP **prompt**
(`fusion-query-workflow`).

The protocol an agent follows is: **discover reports → explore schema (tables,
columns, flexfields) → run a registered report, or compose SQL and run it
ad-hoc → validate against ground truth.**

### 2a. Agent-composed SQL — `fusion_adhoc_query` and `fusion_author_report`

Both are **off by default** and exist for one goal: hand the agent a report
*specification* and let it produce the numbers — explore the schema, resolve
DFF segments, build the query, run it — or mint the report in the system,
with no human in the loop.

The mechanism is the §1 discovery turned around. The pod refuses to let SQL
travel *through* a report (`&p_query` is never substituted), but nothing
stops SQL from *becoming* one: `fusion_adhoc_query` authors an ephemeral data
model + report via the catalog service (§3.2a), runs it once through the
normal pipeline — binds, parameter-echo verification, redaction, audit — and
deletes both objects in a `finally`. The real SQL enters the audit log
*before* the round trip. `fusion_author_report` does the same authoring but
keeps the pair, verifies it with a probe run, and registers it in
`reports.dynamic.yaml` (never `config.yaml` — see below).

What survives from the report path: the read-only **guard** (now guarding
genuinely caller-supplied SQL), the deny-list, `max_sql_chars`, the
parameter-echo check on every bind, redaction and audit. What changes is the
trust boundary, and this must be said plainly:

> With `allow_adhoc_queries` on, the allow-list is no longer "the registered
> reports" but **"any SELECT the guard admits, as the service account"**. The
> real boundary becomes the service account's Fusion roles and row-level Data
> Security. Scope that account before switching this on:
>
> ```yaml
> fusion:
>   allow_adhoc_queries: true      # ephemeral ad-hoc SELECTs
>   allow_report_authoring: true   # persistent agent-minted reports
> ```

Three rules keep the two registries honest: `config.yaml` is human-owned and
is never written by the server; agent-minted reports live in
`reports.dynamic.yaml` (gitignored), so wiping them is deleting one file; and
on a name collision config.yaml **always wins** — an agent report can never
shadow a human one.

### 2b. The local docs snapshot — `fusion_docs_*`

Oracle publishes full documentation for every Fusion table and view (the OEDM
books on docs.oracle.com): column business descriptions, primary keys, foreign
keys, indexes, flexfield mappings, and for views their defining SQL. None of
that lives in the pod's dictionary, and each live schema tool costs a 12–14 s
BI Publisher round-trip.

Build a local snapshot once:

```bash
pip install fusion-query-mcp[scrape]
fusion-query-oedm --release 26b --books procurement financials
```

Snapshots are **one file per quarterly release**, kept together under
`snapshots/` (`snapshots/oedm_docs_<release>.sqlite3`, the CLI's default
name). On first docs use the server resolves the pod's
release — the `fusion.pod_release` pin if set, else one ad-hoc probe of
`AD_PRODUCT_GROUPS` when `allow_adhoc_queries` is on — and serves the matching
file, falling back to `oedm_db_path` (and then to the newest release-named
sibling) when no exact match exists. Every fallback is echoed in the tools'
`notes`, and `fusion_health_check` reports `pod_release` next to the snapshot
it chose.

When a snapshot is available, `fusion_docs_describe_table`
and `fusion_docs_search_columns` serve exploration from it in milliseconds —
including full-text search over column *descriptions*, so "supplier hold reason"
finds the column without knowing any naming convention. Responses are
slice-shaped (filter with `columns_like`) to keep token cost down.

The division of authority is deliberate: **docs for meaning, pod for truth.**
The snapshot never decides what exists — a column present in the docs but
missing on the pod fails loudly inside `fusion_adhoc_query`'s echo-verified
execution, so drift cannot produce a silent wrong answer. Custom objects and
the pod's case-twin duplicates are only visible to the live tools; in a
10-table trial (release 26c vs a live pod) the docs matched the dictionary
column-for-column on every verified table. Re-runs resume where they stopped;
`--refresh` re-fetches after a release upgrade.

### 2c. The REST catalogue snapshot — `fusion_api_*`

Everything above reads. This snapshot exists to support *doing*: it is what
turns "register a purchase requisition" into a list of questions a person can
answer.

```bash
fusion-query-apicat purchaseRequisitions suppliers --probe-writes
```

Two passes, because the pod tells the truth in two different ways.

**Pass 1 — the map.** `GET /fscmRestApi/resources/latest/<res>/describe` gives
every field with type, business description, length, list-of-values and child
collections. Complete and safe. It is also large: one resource is 0.5–1.3 MB,
so runtime discovery is not an option — this is exactly why a snapshot exists,
the same argument as §2b.

**Pass 2 — the truth (`--probe-writes`).** POST an empty body and read *which
layer* rejects it. HTTP 401/403 means the account cannot write the resource at
all; HTTP 400 means the request cleared authorization and reached business
validation, which then names the fields it wanted. Nothing is created either
way.

Pass 2 exists because the declared metadata is wrong in **both** directions,
measured on the pod 2026-08-15:

| Resource / field | `/describe` says | The pod does |
|---|---|---|
| `purchaseOrders` | advertises `POST` | HTTP 403 — refused |
| `ExternallyManagedFlag` | `mandatory: false` | demanded at POST (POR-2010313) |
| `RequisitionHeaderId` | `mandatory: true` | generated; must not be sent |

The best derivation from the declared flags still missed a genuinely required
field — and that is the failure that hurts, because the agent builds a
confident, incomplete payload. So the snapshot stores declared flags as
`declared_mandatory` *hints*, and `required_fields` separately as what the pod
itself demanded. `required_source` says which you are looking at; when it says
"not probed", do not trust the hints.

`fusion_api_list_actions` lists what this account was **observed** able to
create — never what `/describe` advertises. `fusion_api_describe` returns the
interview script for one resource (25 settable fields on
`purchaseRequisitions`, not the 68 declared; LOV child views separated from
real child collections like `lines`). `fusion_api_search` maps a phrase the
user said onto the field name the API expects.

Unlike the OEDM snapshot, this one is **per pod, not per release**: it records
what a specific service account was observed able to do, and that does not
carry across pods even on the same release.

The safety limit of pass 2, stated plainly: it is safe because a resource with
required fields cannot be created by an empty body. A resource whose fields are
*all* optional would be created. So it never runs unless asked, never sweeps
discovered resources, and treats an HTTP 201 as an incident — deleting the
record immediately and reporting it loudly.

### 2d. Doing things — `fusion_resolve_value`, `fusion_prepare_action`, `fusion_commit_action`

Everything before this reads. These three change data, and they are shaped
around what the pod does *not* provide.

**The loop.** `fusion_api_list_actions` says what this account may create.
`fusion_api_describe` gives the field list. `fusion_resolve_value` turns each
name the user said into the id the API wants. `fusion_prepare_action` renders
exactly what would travel and returns a token — **nothing reaches Fusion yet**.
Only `fusion_commit_action`, with that token and `confirmed=True`, sends it.

**Why the pause is here.** Fusion has no "show a human first": an authorised,
valid POST executes on arrival. Oracle already enforces privilege (HTTP 403 on
a resource this account cannot write) and validation (`PreparerId is required`,
POR-2010313) far better than this server could — see §9 — so what is left for
this layer is confirmation, idempotency, and a record of intent.

**Why refusal is a normal outcome.** There is no dry-run for a Fusion create:
a valid POST commits, so an invalid one is the only safe probe. Being refused
is therefore how missing fields are discovered, and `fusion_commit_action`
returns the pod's own sentence plus the field names parsed out of it rather
than a summary — that sentence is the next question to ask the user.

**Idempotency.** Because retrying is the normal path, accidental
double-submission is the normal risk, and Fusion offers no idempotency key on
these resources. A fingerprint of verb + resource + record + payload is
recorded when a commit succeeds; an identical action returns the first outcome
instead of creating a second requisition. Only *successes* are remembered —
re-running a refused attempt after adding the missing field is the intended
workflow. The guard is per-process and not persisted, and it survives a pod
rebind so rebinding cannot become a way to commit twice.

**Resolving values** runs on the read channel, not the action channel: the
list-of-values views `/describe` advertises are not addressable over REST (all
four candidate paths answered 404, probed 2026-08-15), so the mapping lives in
`knowledge/value_resolvers.yaml` and the lookup is an ordinary guarded,
audited SELECT. The user's text always travels as a bind. When several rows
match, the candidates come back for the *user* to choose between — the tool
never picks one.

Policy lives under `actions:` in config.yaml: a general posture plus exceptions
named one at a time (`actions.overrides`), the same shape as the SQL guard's
forbidden keywords plus explicit allowances.

### 2e. Scheduled processes — `fusion_submit_job`, `fusion_job_status`

The REST channel covers what has a resource. A great deal of Fusion does not:
*Import Payables Invoices*, *Create Accounting*, bulk loads. Those run through
`ErpIntegrationService` (61 operations, verified on the pod 2026-08-15).

```
fusion_submit_job(package, definition, parameters, confirmed=True)  -> request id
fusion_job_status(request_id)                                       -> state
```

Two things to know, both from the service's own contract rather than from
documentation:

**`parameters` is positional.** The WSDL states it plainly: *"The order of the
parameters is maintained as per the list. The corresponding entry in the list
should be blank when a given parameter is not passed."* Pass `""` for a skipped
argument — omitting it shifts every later one by a slot, and the job runs
against the wrong data without complaining.

**A submitted job cannot be recalled.** This is the sharp difference from the
REST channel. A REST create is usually refused and changes nothing, so retrying
is cheap; once an ESS request id exists, the job is queued. Submission returns
when the job is *queued*, not when it has done anything — poll
`fusion_job_status` before telling anyone it worked. `finished` is false for
any state not known to be terminal, deliberately: one extra poll beats losing
track of a running job.

**Bulk loads (FBDI).** `fusion_import_bulk_data` reads a file from disk,
uploads it to UCM and queues the import jobs that consume it;
`fusion_find_uploaded_files` answers "did it land, and in the right place?"
without writing anything.

```
fusion_import_bulk_data(file_path, account, jobs, confirmed=True)
fusion_find_uploaded_files(prefix, account)   # read-only
```

Two things to get right, both taken from the service's own schemas
(`DocumentDetails.xsd`, `EssJob.xsd`) rather than from documentation:

**The UCM account is the quiet failure.** A file filed under the wrong account
uploads *successfully* and is then invisible to the import job — no error
anywhere, on either side. That is why `account` has no default and why
`fusion_find_uploaded_files` exists. Check it against the job's own
documentation (`fin$/payables$/import$` and the like) before confirming.

**Job parameters are encoded two different ways in the same service.** This is
the trap worth memorising:

| Operation | Tool | Encoding |
|---|---|---|
| `submitESSJobRequest.paramList` | `fusion_submit_job` | **repeated elements**, one per argument |
| `EssJob.ParameterList` | `fusion_import_bulk_data` | single **comma-separated** string |
| `exportBulkData.parameterList` | `fusion_export_bulk_data` | single **comma-separated** string |

`paramList` and `parameterList` differ by two characters and take opposite
shapes; all three were read off the schemas rather than inferred. A parameter
containing a comma is refused rather than silently split — the schema defines
no escaping, so splitting would shift every later argument by a slot and the
job would run against the wrong data without complaining. Use
`fusion_submit_job` for such parameters.

**When a job fails, get the log.** `fusion_job_status` reporting `ERROR` is the
least useful true statement available; the reason is in the log.

```
fusion_job_log(request_id, file_type="log", save_to="./logs")
```

File contents are never returned inline — a job log can be megabytes — so pass
`save_to` to write them and get the paths back. One undecodable file in a
response does not lose the others.

**The rest of the channel.** `fusion_export_bulk_data` runs an extract job and
leaves the output in UCM (marked destructive because a queued job cannot be
recalled, not because it changes data). `fusion_update_interface_data` is the
repair path: when an import loads ten thousand rows and rejects forty into the
interface tables, it replaces those forty rather than reloading everything —
`load_request_id` decides *which* load is being corrected, so read it back to
the user before confirming.

### 2f. The worklist — `fusion_list_tasks`, `fusion_task_detail`, `fusion_act_on_task`

Approvals and notifications waiting for the signed-in user. A **third** channel:
neither the REST resources nor `ErpIntegrationService` reach it.

```
fusion_list_tasks(status="ASSIGNED")
fusion_task_detail(number)
fusion_act_on_task(number, outcome, comment, confirmed=True)
```

Finding it took six failed guesses. `fscmRestApi/.../notifications`,
`worklistTasks` under both the FSCM and HCM APIs, `userNotifications` and the
older `TaskQueryService` SOAP endpoint all answer HTTP 404. What answers is
`/bpm/api/4.0/tasks`.

**Available outcomes are per task AND per user.** Each task carries an
`actionList`; entries with `actionType: System` are plumbing (`REASSIGN`,
`ESCALATE`, `ACQUIRE`), and everything else is a business outcome. Observed on
this pod with one account: two e-signature tasks offer `APPROVE`/`REJECT`, two
change-order tasks offer only `OK`, and an absence approval offers **nothing at
all** — it is held by a group the account has not acquired. So
`fusion_act_on_task` reads the live task first and refuses an outcome that is
not on it; an empty `outcomes` list is a real state, not an error.

**No replay guard, deliberately.** An approved task is no longer `ASSIGNED`, so
a second attempt fails at the service. This is the one place in the server
where the pod's own state is the duplicate protection.

**Task payloads need `Accept: application/xml`.** The business content behind a
task (`/tasks/{n}/payload`) is XML only — `application/json` and even
`text/xml` answer HTTP 406, while `application/xml` and `*/*` work.

### 2g. Any Fusion API, not just procurement

Fusion has one REST API per product pillar. Resources may be written bare
(`purchaseRequisitions`, resolving against FSCM) or qualified with a family:

```
hcm:workers          -> /hcmRestApi/resources/latest/workers
crm:accounts         -> /crmRestApi/resources/latest/accounts
purchaseRequisitions -> /fscmRestApi/resources/latest/purchaseRequisitions
```

Aliases (`fscm`, `hcm`, `crm`, `scm`, `fin`, `prc`, `helpdesk`) are a
convenience, not a whitelist: any value ending in `RestApi` passes through, so a
pillar the alias table has never heard of is reachable the day the pod exposes
it. What each account may actually *write* there is still Oracle's decision, and
still discovered by probing rather than declared.

### 2h. Any SOAP service — `fusion_soap_*`

`ErpIntegrationService` has a dedicated backend because it is used constantly
and its quirks are worth encoding. That does not generalise: Fusion exposes
dozens more services, and whole areas of functionality (purchase order change
orders, opportunity management, BI Publisher catalog administration) exist
*only* over SOAP.

```bash
fusion-query-apicat --soap /fscmService/PurchaseOrderService --soap /crmService/OpportunityService
```

```
fusion_soap_list_services()
fusion_soap_describe(service, operation=None)
fusion_soap_call(service, operation, parameters, confirmed=True)
```

**Snapshotted, not discovered per call — and for a correctness reason, not a
speed one.** The envelope shape differs per service. Measured on the pod:

| Service | Shape |
|---|---|
| `/fscmService/ErpIntegrationService` | wrapper in a `/types/` namespace, payload in the service namespace |
| `/xmlpserver/services/v2/CatalogService` | one namespace for everything, no `/types/` |
| `/crmService/OpportunityService` | `/types/` again, unrelated service namespace |

An invoker that hardcoded any one of those would build a perfectly well-formed
envelope that the other two reject. So each service's namespace pair is read
from its own WSDL and stored. (The speed argument holds too — the ERP WSDL is
128 KB and its parameter names live in separately-fetched schemas.)

Both shapes are verified live: `getESSJobStatus` through the generic path
returns the same answer as the dedicated `fusion_job_status`, and
`getFolderContents` on the single-namespace BI Publisher service succeeds.

**Parameters are sent in contract order, not dict order.** A declared parameter
you omit is sent as an empty element rather than dropped, because for
positional operations omitting it shifts every later argument by a slot. A name
that is *not* in the contract is reported back as ignored rather than silently
discarded.

**Everything here is gated as destructive**, including read-only operations.
Maintaining a list of which of several hundred operation names are safe would
be a list that is wrong the moment a service is added; one door with one
confirmation is the honest trade.

### Descriptive flexfields — `fusion_describe_flexfields`

Customer-added fields land in the generic `ATTRIBUTE1..n` columns, and only
the DFF *definition* says what each one means. The tool reads the definition
tables this pod actually has (`fnd_df_segments_b` joined to `_tl` for the
user-facing prompt, language-filtered), so a spec saying "supply duration" is
resolvable to `PO_HEADERS_ALL.ATTRIBUTE2` without a human. The DFF code is
usually the base table name without `_ALL`. Two traps it already encodes: on
this pod mainline definition rows carry `SANDBOX_ID = '1'` — the "obvious"
`SANDBOX_ID IS NULL` filter returns zero rows forever — and segments outside
`Global Data Elements` are only valid where `ATTRIBUTE_CATEGORY` equals their
context code.

### Row limits

A registered report has no row cap of its own — the data model returns what it
returns, and the **tool layer** slices to `max_rows` and reports `truncated`
honestly. Defaults come from `limits.default_max_rows`, ceiling from
`limits.hard_max_rows`.

### The guard

The SQL guard (SELECT/WITH only, DML and `DBMS_*` rejected, deny-list patterns,
row-cap injection, missing-alias warning) protects every path that carries
caller-supplied SQL: the legacy `fusion_run_query`, and now `fusion_adhoc_query`
/ `fusion_author_report` (§2a). It is deliberately **absent** from the
registered-report path: there is no SQL in a report call to guard. The
equivalent control there is that an administrator wrote the SQL and registered
the report.

---

## 3. One-time Fusion setup — once per report

**A Fusion administrator must do this.** The server cannot create catalog
objects for you. Do it once per report you want the agent to be able to run;
the four exploration reports in §3.3 are the minimum for a useful server.

**For those four, it is one command** — they ship as SQL files inside the
package and `fusion-query-bootstrap` creates them (§3.0). Reach for §3.2a only
for a report of your own, and for §3.2b only when you would rather click.

### 3.0 The four core reports — `fusion-query-bootstrap`

`fusion_list_tables`, `fusion_describe_table`, `fusion_search_columns` and
`fusion_describe_flexfields` are not implemented in Python: each one runs a BI
Publisher report that has to exist in *your* catalog first. Until it does, the
tools fail — and they fail *after* `fusion_health_check` passes, because the
connection is fine and only the catalog objects are missing.

```bash
fusion-query-bootstrap --dry-run     # checks the manifest, contacts no pod
fusion-query-bootstrap               # creates and verifies all four
```

The SQL lives in `src/fusion_query_mcp/bootstrap/*.sql` and the rest of each
contract — binds, defaults, and the column aliases the server parses — in
`manifest.yaml` beside it. §3.3 below documents those files; it is no longer
the place you copy them from.

What the command guarantees:

- **Offline-fatal first.** Every check that does not need a pod — the read-only
  guard, declared columns against the SQL's own aliases, declared parameters
  against its `:binds` — runs before the first network call, so a mismatch
  cannot leave half the reports created.
- **Idempotent.** An object already in the catalog is skipped, not overwritten.
  Re-running after a partial failure finishes the job. `--force` is the only
  way to replace one, and it is genuinely destructive: the pod has no upsert,
  so replacing means deleting the pair first (§8.7).
- **Verified, honestly.** Each new report is run once through the real pipeline
  with echo checking on. A failed echo check is an error — the pod ignored the
  binds, so the report would answer the same thing whatever it is asked. Zero
  rows is only a warning: the probe binds (`FND_%`, `CREATED_BY` — chosen to be
  pillar-neutral) may simply match nothing on your pod.
- **Registry-aware.** It ends by naming any report that exists on the pod but
  is missing from `fusion.reports`, with the block to paste. Start from
  `config.example.yaml` and there is nothing to paste: the four are already
  declared there, and a test holds the manifest and that file in step.

Useful flags: `--pod NAME` (multi-pod layouts), `--only NAME` (repeatable),
`--data-source` — `ApplicationDB_FSCM` by default, which an HCM-only pod must
override — and `--folder` for a catalog location other than `/Custom/MCP`.

#### `fusion_bootstrap` — the same thing, without the shell

The agent can do this itself. `fusion_bootstrap` is the MCP tool over the same
manifest: it creates whichever of the four are missing, verifies each one, and
writes the registry entries for any the config does not already declare — so an
agent that meets a bare pod, finds `fusion_list_tables` failing, and calls this,
is querying a minute later with no human in the loop.

It is deliberately much narrower than `fusion_author_report`:

|  | `fusion_bootstrap` | `fusion_author_report` |
|---|---|---|
| SQL | four fixed files in this package | anything the agent composes |
| Existing object | skipped, always | replaced with `force` |
| Deletes anything | never | no, but `--force` in the CLI does |
| Gate | `fusion.allow_bootstrap`, **on** by default | `fusion.allow_report_authoring`, off |

The default differs because the boundaries differ. Authoring widens what
statements can reach the pod, which is a thing an administrator must weigh;
bootstrapping widens nothing — the statements are fixed, reviewed and tested
offline, and no caller-supplied SQL travels through it. What it can write is
eight catalog objects with known names, and only onto paths that are empty.
The real limit is the same one as everywhere else: a service account without BI
authoring roles simply gets refused by Fusion. Set `allow_bootstrap: false` for
a pod whose catalog must not be written to at all.

Replacing an existing report stays human-only, in a shell, with `--force`.

### 3.1 Service account

A Fusion user with BI authoring/consuming roles sufficient to create and run BI
Publisher objects — typically *BI Administrator* for setup, and a narrower
custom role for runtime.

> **Important:** all row-level Data Security is evaluated against **this
> account**, not against whoever talks to the MCP server. Rows this account
> cannot see simply do not exist as far as any agent using this server is
> concerned.

### 3.2a Headless authoring — `fusion-query-author`

The pod exposes `/xmlpserver/services/v2/CatalogService` (verified on this pod
2026-08 alongside `ExternalReportWSSService`), and a data model / report pair
is just two small zip archives — so the whole of §3.2b can be done by a CLI:

```bash
fusion-query-author --name open_pos \
    --sql-file open_pos.sql \
    --param "p_bu_name=%" --param "p_date_from=1900-01-01" \
    --verify "p_bu_name=%" --verify "p_date_from=2026-01-01"
```

That creates `/Custom/MCP/XX_MCP_OPEN_POS_DM.xdm` and `..._RPT.xdo`, runs the
report once with the `--verify` binds through the same pipeline the server
uses (parameter-echo check included), and prints the `config.yaml` registry
block to paste. Rules the CLI enforces so the pod cannot be handed a
silent-failure shape:

- The SQL goes through the same read-only guard as everything else —
  authoring is privileged, not exempt.
- Result columns are derived from the top-level select-list aliases; a `*`
  projection or an unaliased expression is refused rather than guessed
  (`--column` passes them explicitly if you must).
- A statement containing `]]>` is rejected: it would terminate the CDATA
  section inside the data model and truncate the SQL without an error.
- An existing catalog object is never overwritten without `--force`.

Credentials come from the same `FUSION_USER` / `FUSION_PASS` environment
variables as the server. Two wire quirks the client pins (each cost a live
round trip to learn): the three catalog operations address objects by three
*different* element names (`reportAbsolutePath` / `reportObjectAbsolutePathURL`
/ `objectAbsolutePath`), and upload types are `xdmz` / `xdoz`.

> **Why this is a CLI and not an MCP tool.** The report registry is the
> allow-list an agent operates inside. If the agent could author reports, the
> allow-list would guard nothing: any SQL becomes reachable by first minting a
> report for it. Creating a report is a human decision here, exactly as it was
> when a browser did it.

One happy consequence: the "a report needs sample data" rule turned out to be
a UI-wizard gate only — an uploaded report runs fine without any, so the
save-as-sample step (and the native dialog that can freeze the browser)
disappears entirely on this path.

### 3.2b The browser recipe

For each report, in this order. The order is not cosmetic — steps 4 and 5 each
depend on the one before, and the failure messages do not say so.

1. **New Data Model** → **Data Set → SQL Query**.
   - **Data Source:** `ApplicationDB_FSCM` (Financials / SCM / Procurement).
     A data set is bound to exactly one data source, so HCM or CRM needs its own
     data model.
   - **Type of SQL:** Standard SQL.

2. **Write the SQL with `:bind` variables**, not `&lexicals`. Every value the
   caller may vary is a bind:

   ```sql
   WHERE UPPER(t.table_name) LIKE UPPER(:p_pattern)
   ```

   Rules that are not optional:
   - **Alias every selected expression** with a plain uppercase identifier.
     Result column names become **XML element names** in the response; an
     unaliased expression can produce a document the parser cannot read.
   - Prefer `UPPER(x) = UPPER(:p_x)` over `x = :p_x` for anything compared
     against a dictionary name — see [§8.2](#82-the-dictionary-is-lower-case).
   - No trailing semicolon.

3. **Accept BIP's offer to create the parameters.** On OK, the editor notices
   the `:bind` variables and offers to create matching parameters
   automatically. **Say yes.** It creates them with the right names and types,
   which is fiddlier to get right by hand. Then open **Parameters** and give
   each one a **default value that returns rows** (e.g. `p_pattern` →
   `%HEADERS%`, `p_owner` → `FUSION`). You need those defaults for step 4.

   > This is the opposite of the lexical case, where the "enter values for
   > lexical references" prompt does *not* create the parameter and you must add
   > it by hand. Binds are the easy path in every respect.

4. **Click *View Data* and make it succeed.** It runs the SQL with the
   parameter defaults from step 3. Fix the SQL until rows come back. This step
   is a hard prerequisite, not a sanity check — see step 5.

5. **Generate sample data** (*View Data* → set a row count → **Save as Sample
   Data**). **A report cannot be created from a data model that has no sample
   data**, and sample data can only be produced by a *View Data* run that
   succeeded. So a data model whose defaults return nothing, or error, is a dead
   end that only announces itself two steps later when the report wizard refuses
   to proceed.

6. **Save the data model** under `/Shared Folders/Custom/MCP/`.

7. **Create the report** on that data model, any trivial layout. In report
   properties / output formats, **enable Data (XML)** output —
   `attributeFormat: "xml"` is what returns raw row data instead of a rendered
   document.

8. **Note both identifiers** and put them in `config.yaml` (§3.4):
   - **SOAP absolute path:** `/Custom/MCP/XX_MCP_LIST_TABLES_RPT.xdo`
   - **REST path** (relative to Shared Folders), URL-encoded:
     `Custom%2FMCP%2FXX_MCP_LIST_TABLES_RPT`

### 3.3 The four shipped reports

These back `fusion_list_tables`, `fusion_describe_table`, `fusion_search_columns`
and `fusion_describe_flexfields`. **`fusion-query-bootstrap` (§3.0) creates all
four for you** — each statement below is the shipped file
`src/fusion_query_mcp/bootstrap/<name>.sql`, reproduced here with the reasoning
that shaped it. The column aliases are the contract the server parses, so
**keep the aliases exactly as written**; edit one and the manifest beside the
file must move with it, which `tests/unit/test_bootstrap.py` enforces offline.
`p_owner` defaults to `FUSION` and `p_table_pattern` to `%` in the registry, so
a caller may omit them.

#### `list_tables` — `XX_MCP_LIST_TABLES_RPT`

Binds: `:p_pattern`, `:p_owner`.
Returns: `OWNER, TABLE_NAME, APPROX_ROWS, TABLE_COMMENT`.

```sql
SELECT t.owner        AS OWNER,
       t.table_name   AS TABLE_NAME,
       t.num_rows     AS APPROX_ROWS,
       tc.comments    AS TABLE_COMMENT
FROM   all_tables t
LEFT JOIN all_tab_comments tc
       ON tc.owner = t.owner
      AND tc.table_name = t.table_name
WHERE  UPPER(t.table_name) LIKE UPPER(:p_pattern)
AND    UPPER(t.owner)      LIKE UPPER(:p_owner)
ORDER BY t.num_rows DESC NULLS LAST, t.table_name
FETCH FIRST 5000 ROWS ONLY
```

`num_rows` is a stale optimiser statistic, not a live count — which is why it is
surfaced to the agent as `APPROX_ROWS`. Ordering by it puts the business table
above its interface, history and staging cousins, which is the single most
useful ranking for schema discovery.

`p_owner` is compared with `LIKE`, not `=`, so a caller can pass `%` to search
every schema the service account can see.

**The `FETCH FIRST` must stay above `limits.hard_max_rows`** (5000 vs 2000). It
is a safety net against a pathological pattern, not the row cap — the tool layer
does the capping, and it can only report `truncated` honestly if *its* limit is
the one that binds. Set the data model's ceiling at or below `hard_max_rows` and
a broad search silently returns a partial answer that looks complete.

#### `describe_table` — `XX_MCP_DESCRIBE_TABLE_RPT`

Binds: `:p_table`, `:p_owner`.
Returns: `OWNER, TABLE_NAME, COLUMN_ID, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,
DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_COMMENT, TABLE_COMMENT`.

```sql
SELECT c.owner          AS OWNER,
       c.table_name     AS TABLE_NAME,
       c.column_id      AS COLUMN_ID,
       c.column_name    AS COLUMN_NAME,
       c.data_type      AS DATA_TYPE,
       c.data_length    AS DATA_LENGTH,
       c.data_precision AS DATA_PRECISION,
       c.data_scale     AS DATA_SCALE,
       c.nullable       AS NULLABLE,
       (SELECT MAX(cc.comments)
          FROM all_col_comments cc
         WHERE cc.owner       = c.owner
           AND cc.table_name  = c.table_name
           AND cc.column_name = c.column_name) AS COLUMN_COMMENT,
       (SELECT MAX(tc.comments)
          FROM all_tab_comments tc
         WHERE tc.owner      = c.owner
           AND tc.table_name = c.table_name)   AS TABLE_COMMENT
FROM   all_tab_columns c
WHERE  UPPER(c.table_name) = UPPER(:p_table)
AND    UPPER(c.owner)      LIKE UPPER(:p_owner)
ORDER BY c.column_id
```

The comments come from **scalar subqueries, not joins**, and that is deliberate.
`ALL_COL_COMMENTS` can return more than one row for a column when the account
reaches the object through several grants, and a `LEFT JOIN` would turn that into
duplicated columns — the exact fan-out this server exists to detect, in the
server's own SQL. A scalar subquery cannot multiply rows, so the shape is safe by
construction rather than by inspection.

`OWNER` and `TABLE_NAME` are projected for a reason that is not obvious until it
bites you: this pod carries **two objects whose names differ only in case**, so a
case-insensitive lookup returns both, interleaved by `COLUMN_ID`. The server
groups the rows by `(OWNER, TABLE_NAME)` and refuses to present them as one
table — see [§8.3](#83-two-objects-one-name-different-case). Grouping is by key,
not by row order, so the simple `ORDER BY c.column_id` is sufficient.

#### `search_columns` — `XX_MCP_SEARCH_COLUMNS_RPT`

Binds: `:p_pattern`, `:p_owner`, `:p_table_pattern`.
Returns: `OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, APPROX_ROWS`.

```sql
SELECT c.owner       AS OWNER,
       c.table_name  AS TABLE_NAME,
       c.column_name AS COLUMN_NAME,
       c.data_type   AS DATA_TYPE,
       t.num_rows    AS APPROX_ROWS
FROM   all_tab_columns c
LEFT JOIN all_tables t
       ON t.owner = c.owner
      AND t.table_name = c.table_name
WHERE  UPPER(c.column_name) LIKE UPPER(:p_pattern)
AND    UPPER(c.owner)       LIKE UPPER(:p_owner)
AND    UPPER(c.table_name)  LIKE UPPER(:p_table_pattern)
ORDER BY t.num_rows DESC NULLS LAST, c.table_name, c.column_name
FETCH FIRST 5000 ROWS ONLY
```

#### `describe_flexfields` — `XX_MCP_DESCRIBE_FLEXFIELDS_RPT`

Backs `fusion_describe_flexfields`. Binds: `:p_flexfield`, `:p_context`.
Returns one row per DFF segment with the `COLUMN_NAME` mapping. Authored
headless with `fusion-query-author` (§3.2a) — no browser was involved.

```sql
SELECT s.DESCRIPTIVE_FLEXFIELD_CODE  AS FLEXFIELD_CODE,
       f.NAME                        AS FLEXFIELD_NAME,
       s.CONTEXT_CODE                AS CONTEXT_CODE,
       s.SEGMENT_CODE                AS SEGMENT_CODE,
       st.NAME                       AS SEGMENT_PROMPT,
       s.COLUMN_NAME                 AS COLUMN_NAME,
       s.SEQUENCE_NUMBER             AS SEQUENCE_NUMBER,
       s.ENABLED_FLAG                AS ENABLED_FLAG,
       s.REQUIRED_FLAG               AS REQUIRED_FLAG,
       s.DISPLAY_TYPE                AS DISPLAY_TYPE,
       s.DEFAULT_VALUE               AS DEFAULT_VALUE,
       TO_CHAR(s.VALUE_SET_ID)       AS VALUE_SET_ID
FROM   fnd_df_segments_b s
LEFT   JOIN fnd_df_segments_tl st
       ON  st.APPLICATION_ID = s.APPLICATION_ID
       AND st.DESCRIPTIVE_FLEXFIELD_CODE = s.DESCRIPTIVE_FLEXFIELD_CODE
       AND st.CONTEXT_CODE = s.CONTEXT_CODE
       AND st.SEGMENT_CODE = s.SEGMENT_CODE
       AND st.LANGUAGE = USERENV('LANG')
       AND NVL(st.SANDBOX_ID, '~') = NVL(s.SANDBOX_ID, '~')
       AND NVL(st.ENTERPRISE_ID, -1) = NVL(s.ENTERPRISE_ID, -1)
LEFT   JOIN fnd_df_flexfields_tl f
       ON  f.APPLICATION_ID = s.APPLICATION_ID
       AND f.DESCRIPTIVE_FLEXFIELD_CODE = s.DESCRIPTIVE_FLEXFIELD_CODE
       AND f.LANGUAGE = USERENV('LANG')
       AND NVL(f.SANDBOX_ID, '~') = NVL(s.SANDBOX_ID, '~')
       AND NVL(f.ENTERPRISE_ID, -1) = NVL(s.ENTERPRISE_ID, -1)
WHERE  UPPER(s.DESCRIPTIVE_FLEXFIELD_CODE) LIKE UPPER(:p_flexfield)
AND    UPPER(s.CONTEXT_CODE) LIKE UPPER(:p_context)
ORDER  BY s.DESCRIPTIVE_FLEXFIELD_CODE, s.CONTEXT_CODE, s.SEQUENCE_NUMBER
FETCH  FIRST 5000 ROWS ONLY
```

> **Deliberately NO sandbox or seed-set filter.** On this pod the mainline
> definition rows carry `SANDBOX_ID = '1'`, so the "obvious"
> `SANDBOX_ID IS NULL` predicate returns zero rows — forever, without an
> error. Verified empirically before authoring (total = distinct = 10 for the
> `PO_HEADERS` DFF, so no striping duplication either). Re-check both facts
> when recreating this on another pod.

> **Views.** The shipped set covers tables only. Fusion exposes a great deal
> through `_V` / `_VL` views that never appear in `all_tables`, so if you need
> them, build one more report over `all_views` the same way and register it. The
> exploration tools will not invent one for you.

### 3.4 Register the report

Add it to `config.yaml` under `fusion.reports`. Nothing else makes a report
runnable — the registry *is* the allow-list:

```yaml
fusion:
  reports:
    list_tables:
      soap_path: "/Custom/MCP/XX_MCP_LIST_TABLES_RPT.xdo"
      rest_path: "Custom%2FMCP%2FXX_MCP_LIST_TABLES_RPT"
      description: "Tables matching a LIKE pattern, most-populated first."
      parameters: [p_pattern, p_owner]      # accepted binds; anything else is rejected
      defaults: {p_owner: "FUSION"}         # used for whatever the caller omits
```

`description` and `parameters` are what `fusion_list_reports` shows an agent, so
write the description for the agent, not for yourself: say what the report
returns and what the parameters mean.

`parameters` is not optional in practice: an entry declaring none accepts none,
because forwarding an undeclared name is how a typo reaches the pod unnoticed.

**`parameters` is a hand-kept copy of the bind names inside the data model, and
that is a real failure mode.** Rename `:p_pattern` to `:p_name` in the data model
and forget to change it here, and the server sends `p_pattern`, BI Publisher
ignores an unknown parameter, the report runs on its **stored defaults**, and the
pod answers HTTP 200 with rows that look entirely reasonable. Nothing errors.

The server defends against this because the pod hands over a receipt: every bind
it actually received is echoed back at the root of the data XML. `run_report`
compares that echo with what it sent and fails the call when they disagree,
rather than returning rows that answer a different question. Set
`verify_echo: false` on a report only if you have established that it does not
echo — and understand you are switching the check off, not fixing it.

### 3.5 Prove the transport before wiring anything up

**Which transport?** On the pod this was built against, the REST endpoint
`/xmlpserver/services/rest/v1/reports/{path}/run` answers a plain **404** — the
v1 API is simply not exposed — while the SOAP `ExternalReportWSSService` works.
Check yours:

```bash
# REST
curl -u "$FUSION_USER:$FUSION_PASS" -X POST \
  "https://<pod>.oraclecloud.com/xmlpserver/services/rest/v1/reports/Custom%2FMCP%2FXX_MCP_LIST_TABLES_RPT/run" \
  -H "Content-Type: multipart/form-data" \
  -F 'ReportRequest={"byPassCache":true,"attributeFormat":"xml","parameterNameValues":{"listOfParamNameValues":[{"name":"p_pattern","values":["%HEADERS%"]}]}};type=application/json' \
  -o out.bin -v

# SOAP
curl -X POST "https://<pod>.oraclecloud.com/xmlpserver/services/ExternalReportWSSService" \
  -H "Content-Type: application/soap+xml;charset=UTF-8" -H "SOAPAction: submitRequest" \
  -u "$FUSION_USER:$FUSION_PASS" --data @docs/soap_smoke.xml
```

`docs/soap_smoke.xml` ships with this repo. It is addressed at the legacy
lexical report, so edit the `.xdo` path and the parameter block to point at one
of your own reports before using it as a transport probe. SOAP returns the
report bytes **Base64-encoded** inside `<reportBytes>`.

Set `fusion.backend` to `rest` or `soap` accordingly, then run
`fusion_health_check`, which executes the `list_tables` report with a narrow
pattern and reports backend, elapsed time, row count and the registered report
names.

---

## 4. Install and run

Requires **Python 3.12** and [uv](https://docs.astral.sh/uv/). Nothing else —
no clone, no virtualenv, no paths to get right:

```bash
uvx --from git+https://github.com/ruya-grp/Fusion-MCP fusion-query-init
```

It asks four things — pod name, pod URL, service account, password — and then
does the rest: writes the pod under `~/.fusion-query/pods/<name>/`, creates the
four exploration reports on the pod, registers them, runs the health check, and
prints the line that registers the server with your MCP client. A live run ends
like this:

```
Registered 4 report(s) in .../pods/my-pod/reports.dynamic.yaml
PASS  ok: Pod reachable via soap in 10186 ms; 241 row(s) from
      report:list_tables(p_owner=FUSION, p_pattern=%HEADERS%); 4 registered report(s).
```

Everything it does is available separately (`--no-bootstrap`, `--no-verify`,
`--no-input` for scripting), and the pod it writes is a plain directory you can
edit afterwards.

> **Where things live.** An installed copy has no project directory and no
> dependable working directory, so configuration lives in `$FUSION_HOME`, or
> `~/.fusion-query/` when that is unset. An explicitly set `FUSION_HOME` wins
> over anything found by looking around, which is what makes one registration
> line work on every machine.

### Working from a checkout

For developing the server itself:

```bash
uv venv --python 3.12
uv sync --all-groups
```

Then the same commands are on the path as `uv run fusion-query-init`,
`uv run fusion-query-bootstrap`, and so on. A `config.yaml` or `pods/` in the
working directory takes precedence, so a checkout keeps behaving like a
checkout.

### Adding a pod by hand — four facts, nothing else

A pod is a directory under `pods/`. Its name *is* the pod name — what
`fusion_use_pod` binds to — and the whole of its configuration is one line:

```bash
mkdir -p "pods/my pod" && cp docs/pod-template/config.yaml "pods/my pod/"
$EDITOR "pods/my pod/config.yaml"          # set base_url
cp docs/pod-template/.env.example "pods/my pod/.env"
$EDITOR "pods/my pod/.env"                 # set FUSION_USER / FUSION_PASS
```

```yaml
fusion:
  base_url: https://your-pod.fa.ocs.oraclecloud.com
```

That is a complete pod. Everything else has a working default:

- **transport** — `backend: auto` asks the pod which of SOAP/REST it publishes
  and remembers the answer (§8.5). There is one correct value per pod and the
  pod knows it, so it is not something to discover and record by hand.
- **reports** — created and registered by `fusion_bootstrap` on first use
  (§3.0), or by `fusion-query-bootstrap` from a shell.
- **limits, security, redaction** — defaults that work; `config.example.yaml`
  is the annotated reference for tuning them, not a file you must fill in.

> **One global variable, many pods.** If `FUSION_USER` / `FUSION_PASS` are also
> set process-wide, they must name the same account as the pod's `.env`. When
> the two disagree the server refuses rather than picking: answering as the
> wrong service account returns HTTP 200 and plausible rows, with row-level
> Data Security evaluated against the wrong person.

### Single-pod setup

Without a `pods/` directory, one `config.yaml` at the project root does the
same job. Start from the same template rather than from the full example:

```bash
cp docs/pod-template/config.yaml config.yaml
```

`config.example.yaml` is the annotated reference for everything you *may* set:

```bash
cp config.example.yaml config.yaml   # the long form, if you want the comments
```

Export credentials — **never** put them in the YAML:

```bash
export FUSION_USER='svc_mcp_query'
export FUSION_PASS='...'
```

Verify the transport end to end:

```bash
uv run python -c "from fusion_query_mcp.server import fusion_health_check as h; print(h())"
```

Then create the four reports the exploration tools run on (§3.0). This is the
step a passing health check does *not* cover — the connection can be perfect
while the catalog is empty:

```bash
uv run fusion-query-bootstrap
```

### Registering with Claude Code

`uv sync` installs a console script into the venv, so point the client straight
at it and pass the config path explicitly (an MCP server does not inherit the
shell's working directory):

```bash
claude mcp add fusion-query --scope user --env FUSION_MCP_CONFIG=/path/to/OracleMCP/config.yaml -- /path/to/OracleMCP/.venv/bin/fusion-query-mcp
```

On Windows the executable is `.venv\Scripts\fusion-query-mcp.exe`. Verify with
`claude mcp list`, which should report `✓ Connected`.

Or configure any MCP client directly:

```json
{
  "mcpServers": {
    "fusion-query": {
      "command": "/path/to/OracleMCP/.venv/bin/fusion-query-mcp",
      "env": { "FUSION_MCP_CONFIG": "/path/to/OracleMCP/config.yaml" }
    }
  }
}
```

### Supplying credentials to a client-launched server

A server started by an MCP client does not see variables you exported in a
terminal, so `FUSION_USER` / `FUSION_PASS` have to reach it another way. Two
options, and the trade-off is real:

- **User-level environment variables** (preferred). Stored by the OS and
  inherited by every child process, so the password never lands in a file inside
  this repository:

  ```bash
  setx FUSION_USER "svc_mcp_query"    # Windows; sign out and back in to apply
  ```

  On macOS/Linux, set them in your login shell profile or a keychain helper.

- **`claude mcp add --env FUSION_PASS=...`** works, but writes the password in
  clear text into `~/.claude.json`. Use it only for a throwaway sandbox pod.

Either way the password stays with you: nothing in this project reads, stores or
logs it, and `config.yaml` holds only the *names* of the variables.

---

## 5. Configuration reference

See `config.example.yaml` for the annotated template.

| Key | Default | Meaning |
|---|---|---|
| `fusion.base_url` | — | Pod URL, no trailing slash |
| `fusion.username_env` / `password_env` | `FUSION_USER` / `FUSION_PASS` | **Names** of the env vars holding credentials |
| `fusion.backend` | `rest` | `rest` \| `soap` — §3.5. The verified pod needs `soap` |
| `fusion.param_shape` | `auto` | `auto` \| `flat` \| `item` — REST parameter JSON shape |
| `fusion.reports.<key>` | — | **The report registry.** `soap_path`, `rest_path`, `description`, `parameters`, `defaults` |
| `fusion.datasources.<key>` | — | Legacy lexical engine paths (§1.4) |
| `fusion.engine_mode` | `whole_query` | `whole_query` \| `clauses` — legacy path only |
| `fusion.double_encode_path` | `false` | `true` if a load balancer eats `%2F` |
| `limits.default_max_rows` | `100` | Rows returned when the caller does not specify |
| `limits.hard_max_rows` | `2000` | Ceiling the caller cannot exceed |
| `limits.timeout_seconds` | `120` | Per-request timeout |
| `limits.max_sql_chars` | `30000` | Legacy path only — rejects over-long statements |
| `security.denied_table_patterns` | `[]` | SQL-LIKE patterns; legacy path, defence in depth only |
| `security.redact_columns` | `[]` | Values masked in output *and* in saved fixtures |
| `audit.path` | `./audit/queries.jsonl` | JSONL audit log |
| `audit.log_row_data` | `false` | Log the call and counts, not payloads |
| `fixtures_dir` | `./fixtures` | Where fixtures live |

Environment overrides: `FUSION_BASE_URL`, `FUSION_BACKEND`, `FUSION_PARAM_SHAPE`,
`FUSION_ENGINE_MODE`, `FUSION_MAX_ROWS`, `FUSION_HARD_MAX_ROWS`,
`FUSION_TIMEOUT_SECONDS`, `FUSION_MAX_SQL_CHARS`, `FUSION_FIXTURES_DIR`,
`FUSION_AUDIT_PATH`, `FUSION_MCP_CONFIG` (path to the config file itself).

---

## 6. Validating against ground truth

This is the feature that turns "the report ran" into "the answer is *correct*".
You supply facts you already trust — a total read off the Fusion UI, an exported
spreadsheet, a known document number — and the agent checks the result against
them. It works identically on `fusion_validate_report` and (on a lexical-capable
pod) `fusion_validate_query`; only the execution path differs.

### Expectation types

| `type` | Key fields | Catches |
|---|---|---|
| `row_count` | `op: eq\|min\|max\|between`, `value` / `min` / `max` | wrong cardinality |
| `column_set` | `required`, `forbidden` | missing projections, alias mistakes |
| `unique_key` | `columns` | **join fan-out** — the single most common Fusion bug |
| `contains_row` | `match`, `numeric_tolerance` | a known record is present and correct |
| `not_contains_row` | `match` | a record that must be filtered out |
| `aggregate` | `fn`, `column`, `op`, `value`, `tolerance` | totals matching the UI |
| `reference_dataset` | `key`, `mode`, `rows` or `rows_csv`, `allow_duplicate_keys` | a full exported dataset |
| `cross_check` | `oracle_sql`, `bind`, `tolerance` | a complex result vs a simple trusted query |

> **Not every expectation can see a fan-out, and it is worth knowing which.**
> Join fan-out copies rows verbatim, so an expectation only detects it if
> duplication can falsify what it claims:
>
> - **Catches it:** `unique_key` (that is its whole job); `reference_dataset`,
>   which fails when a key *the reference supplied* comes back more than once —
>   an *extra* key still passes in `contains_all` mode, because a wider scope is
>   what that mode is for; `row_count` with `op: eq`; `aggregate` over `sum` or
>   `count`.
> - **Blind to it, by arithmetic:** `aggregate` over `max`, `min`, `avg` or
>   `count_distinct`. Uniform duplication does not move any of them. No fix is
>   possible inside the expectation — pair them with `unique_key` or an exact
>   `row_count`. `cross_check` inherits the same blindness through `bind.left`.
> - **Blind to it, by design:** `contains_row` asserts that a row *exists*, and
>   `match` is often a deliberately non-unique pattern (`{"STATUS": "OPEN"}`).
>   Its `detail` reports how many rows matched, so duplication is visible on the
>   passing result, but the verdict stays green.
>
> This is the concrete form of the caveat below: pick two expectation types that
> fail for *different* reasons, not two spellings of the same claim.

> **`cross_check` needs a SQL-capable pod.** It re-derives a number by running a
> second, simpler SQL statement — which this pod cannot do (§1.2). On a
> report-only pod the expectation does not crash the validation; it comes back
> as a **failed expectation saying so**. To get independent evidence without it,
> register a second, deliberately simpler report and compare the two results.

### Validating a report

```json
{
  "report": "list_tables",
  "params": {"p_pattern": "PO_HEADERS%"},
  "expectations": [
    {"type": "row_count", "op": "min", "value": 1},
    {"type": "column_set", "required": ["OWNER", "TABLE_NAME", "APPROX_ROWS"]},
    {"type": "unique_key", "columns": ["OWNER", "TABLE_NAME"]}
  ]
}
```

Expectations can also come from a saved fixture by name (`fixture:`), which is
how a check becomes a regression test you re-run after a quarterly patch.

### Fixture file

```yaml
name: hhc_open_pos_aug2026
description: >
  Open standard POs for BU HHC-Entity01, August 2026.
  Ground truth from Procurement work area export on 2026-08-10.
datasource: fscm
expectations:
  - type: row_count
    op: between
    min: 1
    max: 500
  - type: column_set
    required: [PO_NUMBER, BU_NAME, SUPPLIER, TOTAL_AMOUNT]
  - type: unique_key
    columns: [PO_NUMBER]
  - type: contains_row
    match: {PO_NUMBER: "PO-2026-000123", TOTAL_AMOUNT: 150000}
    numeric_tolerance: 0.01
  - type: aggregate
    fn: sum
    column: TOTAL_AMOUNT
    op: eq
    value: 4250000.00
    tolerance: 0.50
```

A fuller, annotated version ships at
[`docs/example_fixture.yaml`](docs/example_fixture.yaml) — copy it into
`fixtures/` and edit. It is loaded by the test suite, so it cannot drift out of
sync with the expectation schema. A fixture's `sql:` field is documentation of
the statement the ground truth describes; on a report-only pod, the data model
holds the real SQL and only the `expectations` are evaluated.

### Two honest caveats

1. **A passing fixture proves consistency with the ground truth you supplied,
   not universal correctness.** Use at least two *independent* expectation types
   per fixture — a `contains_row` **and** an `aggregate`, say. Two expectations
   restating the same fact prove almost nothing.
2. **A missing expected row is not always a query bug.** Fusion row-level Data
   Security on the service account can legitimately hide rows. When
   `contains_row` fails with no near miss, widen the parameters until the row
   *should* be in scope; if it is still absent, the account cannot see it, and
   no amount of SQL will change that.

### The working loop

The `fusion-query-workflow` prompt ships the full protocol. In short:
`fusion_list_reports` to see what exists → `fusion_search_columns` /
`fusion_describe_table` to check schema rather than guessing → run the report
with `max_rows <= 20` while iterating → validate → save the fixture. If the
answer needs SQL no registered report provides, the correct outcome is to **say
so and ask an administrator for a new report**, not to improvise.

---

## 7. Fusion schema traps

`fusion_get_hints` covers these in depth, and they matter most to whoever writes
the SQL inside a data model. The three that **corrupt results without raising an
error**:

- **`_TL` translation tables** need `AND t.LANGUAGE = USERENV('LANG')`, or rows
  multiply by the number of installed languages.
- **`_F` / `_M` date-effective tables** need
  `TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date`, or rows
  multiply by the number of history versions.
- **`_ALL` tables** span business units and orgs. The UI's implicit BU context
  does not apply to raw SQL — filter `org_id` / `bu_id` deliberately.

Also: Fusion column names rarely equal UI labels (the UI's "Supplier" is
`VENDOR_NAME` on `POZ_SUPPLIERS_V`), and `all_tables.num_rows` is a stale
optimiser statistic, surfaced as `APPROX_ROWS` for that reason.

---

## 8. What surprised us on this pod

Verified by experiment, not inferred. Every item here cost time to discover.

### 8.1 Lexicals are not substituted; binds are

Covered in §1.2. It is the finding everything else follows from. If you take one
thing from this README: `&p_query` becomes the empty string, `:p_x` arrives
verbatim.

### 8.2 The dictionary is lower case

The pod serves its data dictionary in **lower case** — `po_headers_all`,
`gl_je_headers` — because the objects were created with quoted lower-case names.
Oracle's usual "unquoted identifiers are stored upper case" intuition is wrong
here. Upper-casing a table name before comparing it returns **zero rows**, which
reads as "that table does not exist" rather than as a bug. Every dictionary
predicate therefore compares through `UPPER()` **on both sides**, so it works on
either convention.

### 8.3 Two objects, one name, different case

The pod carries **both** `FUSION.po_headers_all` and `FUSION.PO_HEADERS_ALL` —
two distinct objects with the same name in different case. The lower-case one is
the real table (974 rows, populated comments); the upper-case one has no
statistics and no comments.

A case-insensitive `describe_table` therefore returns **576 rows for a
288-column table, every column name appearing twice**. Presented as one table
that is simply wrong — it looks like a broken join, and the natural "fix"
(dedupe by column name) hides a real fact about the pod. `fusion_describe_table`
groups by `(OWNER, TABLE_NAME)` and makes the ambiguity visible instead of
resolving it silently.

### 8.4 Comments are populated

Table and column comments are genuinely present on this pod, contrary to the
common assumption that Fusion ships them NULL. They are useful enough to an
agent that both `list_tables` and `describe_table` project them.

### 8.5 REST `/run` is not exposed; SOAP is

`/xmlpserver/services/rest/v1/reports/{path}/run` answers a plain 404 here,
while `ExternalReportWSSService` works. Do not read the 404 as an
authentication or path-encoding problem; the endpoint is not there.

This used to mean `backend: soap` in every config, discovered by an
administrator and written down. It no longer does: `backend: auto` is the
default and asks the pod. SOAP is probed first — not as a preference, but
because `ExternalReportWSSService` sits beside the `CatalogService` this server
already requires unconditionally for authoring, ad-hoc queries and
bootstrapping. **A pod where SOAP is missing cannot run this server at all**,
whatever `backend` says, so the transport that might be absent is the one
probed second.

What does *not* trigger a fallback matters more than what does:

| Signal | Falls back? | Why |
|---|---|---|
| 404 / 405 / 501, "no such service" | yes | the endpoint is not published here |
| 401 / 403 | **no** | retrying doubles failed sign-ins, and Fusion locks accounts |
| Timeout | **no** | something answered slowly, so it exists |
| 500 with a fault body | **no** | the service ran; the other one hears the same complaint |
| Connection refused / DNS | **no** | both transports are on the same host |

The choice is remembered for the process, so the probe is paid once. Pinning
`backend: soap` or `rest` still skips it entirely.

### 8.6 BI Publisher echoes your parameters as data

The response document carries every report parameter back as a **leaf element at
the root**, alongside the repeating row groups. A report that matched nothing
returns *only* the echoes:

```xml
<DATA_DS><P_OWNER>FUSION</P_OWNER><P_PATTERN>PO_HEADERS%</P_PATTERN></DATA_DS>
```

and one that matched rows returns them mixed together:

```xml
<DATA_DS><P_OWNER>FUSION</P_OWNER><P_PATTERN>%HEADERS%</P_PATTERN>
  <G_1><OWNER>FUSION</OWNER><TABLE_NAME>po_headers_all</TABLE_NAME></G_1>
  <G_1><OWNER>FUSION</OWNER><TABLE_NAME>gl_je_headers</TABLE_NAME></G_1>
</DATA_DS>
```

A naive parser reads the first document as **one row of parameter values**,
which inverts every row-count check: `row_count eq 0` fails and `min 1` passes
on an empty result. The rule this project uses: **a row is a child of the root
that has children of its own**; root-level leaves are never rows, and dropping
them is reported in `ResultSet.warnings`.

### 8.7a A missing config used to be a silently wrong one

`config.example.yaml` was in the list of filenames treated as "the
configuration", so a checkout with no `config.yaml` quietly ran on the
annotated template — whose `base_url` is the placeholder
`https://<pod>.oraclecloud.com`. The server started, listed reports, and failed
every call with a DNS error that pointed at the network rather than at "there
is no configuration". It is no longer in that list: a missing config is now
simply missing, and the health check says so.

### 8.7 `uploadObject` refuses an occupied path — so `--force` never overwrote

Found by bootstrapping into a scratch folder, twice. `--force` was implemented
as "skip the client-side existence check", which is not the same thing as
overwriting: the pod answers

```
PublicReportServiceImpl::executeUploadReport Failure:
Due to Report with Path [/Custom/.../XX_MCP_DESCRIBE_TABLE_DM.xdm] already exist!
```

There is no upsert. `author_report` therefore takes `replace` as well as
`force`, and both CLIs map `--force` onto the pair: the report is deleted
first, then the data model — that order, because deleting the model out from
under the report would orphan it if the second call failed. `fusion_author_report`
(the MCP tool) still passes `force` only, so nothing an agent can call deletes
a catalog object.

Two smaller facts from the same runs: `uploadObject` **creates the folder** if
it does not exist — `/Custom/MCP_BOOTSTRAP_TEST` was never made by hand — and a
report run through `run_report` is bounded only by its own `FETCH FIRST`, since
that path applies no row cap by design (§2 *Row limits*). The
`describe_flexfields` probe returns exactly 5000 for that reason, so bootstrap
names the ceiling rather than presenting the number as a total.

---

## 9. Security, privacy, audit

- **The report registry is a real boundary.** An agent can only run reports an
  administrator built and registered in `config.yaml`, with only the parameters
  that report declares — an unknown parameter name is rejected *before* any
  round trip. Compared with the original design, where any SELECT the guard
  allowed could reach the pod, this is a genuine improvement: the set of
  possible statements is finite, reviewable, and owned by a human.
- **Credentials** come only from environment variables or the OS keychain. Never
  in `config.yaml`, never in logs.
- **Single-account trust model.** Every caller of this MCP server inherits the
  service account's full read scope. Row-level **Data Security** is evaluated
  against that account, not the human asking. If several people or clients will
  use the server, the report registry, the deny-list and the redaction list are
  the *only* differentiators. **The SQL guard is defence in depth,
  not a security boundary** — the real boundary is the service account's
  Fusion roles. Scope them narrowly.
- **Redaction.** Columns matching `security.redact_columns` are masked in tool
  output *and* in fixtures the server writes (PDPL alignment). Validation warns
  when an expectation references a redacted column, because such a comparison
  can never pass.
- **Audit.** One JSONL line per execution: timestamp, tool, datasource or
  report, backend, the call (`report:list_tables(p_pattern=PO_HEADERS%)` for a
  report, the SQL text on the legacy path), row count, elapsed ms, ORA code. Row
  payloads are **not** logged unless `audit.log_row_data: true`.
- **Fixtures hygiene.** `fixtures/` is gitignored by default. Use synthetic or
  masked ground truth when the repository is shared — real ground truth is
  client data.
- Parameter values travel inside JSON/XML over HTTPS to Oracle. No third
  parties.

---

## 10. Development

```bash
uv run pytest -q                      # unit tests, no network
uv run pytest -q -m "not integration" # same, explicit
FUSION_LIVE=1 uv run pytest tests/integration -q   # live pod, env-gated
```

Unit tests never touch the network: the backends are exercised against canned
multipart and SOAP payloads under `tests/data/`, and the pipeline and tool layers
run against a fake pod. The live suite in `tests/integration/` is the only thing
that can confirm the `[VERIFY-ON-POD]` items in §11.

Agent evaluations are in `tests/evals/` — see
[`tests/evals/README.md`](tests/evals/README.md) for why there are two sets and
why only one of them ships with answers filled in.

Layout:

```
src/fusion_query_mcp/
├── server.py           MCP tool surface
├── pipeline.py         the one execution path (execute + run_report)
├── metadata_sql.py     SQL builders — legacy lexical path only
├── config.py           YAML + env configuration, incl. the report registry
├── models.py           shared types
├── audit.py            JSONL audit log
├── redaction.py        column masking
├── fixtures_store.py   fixture persistence
├── backends/           soap_bip (this pod), rest_bip
├── engine/             sql_guard, limiter, xml_result, errors
├── validation/         models, evaluator, differ
└── knowledge/          fusion_hints.yaml + loader
```

**SDK note:** the design brief named `FastMCP`. In the official `mcp` Python SDK
v2 that class was renamed `MCPServer`; the decorator surface is otherwise the
same, and this project targets the current name rather than pinning to v1.

---

## 11. `[VERIFY-ON-POD]` checklist

Work through these with your Fusion administrator and record the answers in
`config.yaml`. Items 1 and 2 decide whether you get the report architecture or
the legacy one, so do them first.

1. **Are lexicals substituted?** Build one throwaway data model
   `SELECT * FROM (&p_query)`, create the report, run it over the API with
   `p_query = SELECT 1 AS N FROM DUAL`. `ORA-00903` means no — use reports, and
   do not try to make it work. Anything else means your pod is more permissive
   than the one this was built on.
2. **Are binds honoured?** A data model `SELECT :p_x AS ECHO FROM DUAL` must
   return your sentinel verbatim. If this fails too, the server cannot work at
   all on this pod.
3. **REST `/run` functional?** If it 404s → `backend: soap` (§3.5).
4. **`parameterNameValues` shape** (REST only) — flat vs `item`. Auto-detected;
   record the result as `param_shape`.
5. **Dictionary case.** Run `list_tables` with `p_pattern = %HEADERS%` and look
   at the returned names. Lower case, upper case, or both (§8.2, §8.3)?
6. **Service-account roles** confirmed by the Fusion admin; decide which pillars
   (FSCM only, or also HCM/CRM) get their own data models, since a data set is
   bound to exactly one data source.