The Seven-Step Operator Workflow

Take a legacy application's code, documentation and database through analysis, estate documentation, review and sign-off, requirements, extraction, silver and approved marts.

This guide takes one application estate (its source tree, its documentation and its live database) through EKOS's analysis into a lakehouse. It was built and measured on iDempiere, an open-source ERP with 903 tables, running on Oracle; every number below comes from that testbed. Steps 1–3 belong to EKOS core. Steps 4–7 belong to the EKOS Lakehouse extension, whose binary ekos-lakehouse is EKOS plus the lakehouse commands (Oracle and steps 4–7 are on its main, not yet in a release). How the steps, the step bar and the sign-off gate are designed is in Operator Workflow; this guide is the hands-on path.

The step bar at the top of every project page in the web console, and ekos workflow status, shows each step as todo, partial, done or blocked. Steps 4–7 are blocked until every step-3 subject has a valid sign-off. Agents read the same state through the MCP tool ekos_signoff_status. No MCP tool can decide, sign off or approve: those are human-only, and the console refuses them to API tokens.

Before you start

• The application's source tree (a git checkout), and its documentation if you have it.
• A read-only database user. For Oracle: CREATE SESSION, SELECT on the application schema and SELECT_CATALOG_ROLE. EKOS reports a user that could write.
• For Oracle: a build with the oracle feature and Oracle Instant Client on LD_LIBRARY_PATH.
• For steps 5–7: the lakehouse stack (Trino with a catalog over the source and an Iceberg catalog, object storage, Dagster). ekos-lakehouse lakehouse install sets up a sandbox stack.

A project lives in the EKOS home (ekos project add). Its ekos.toml, cut to what this guide uses:

[recover.sql]
default-dialect = "oracle"
[[recover.sql.dialect-rules]]          # each engine's copy of a script is that engine's SQL
path-glob = "**/postgresql/**"
dialect = "postgres"

[tickets]
keys = ["IDEMPIERE"]

[[sources]]
name = "idempiere"
kind = "git"
path = "/path/to/idempiere"

[[databases]]
name = "idempiere-db"
kind = "oracle"
connect = "localhost:51521/XEPDB1"
user = "ekos_ro"
password-env = "EKOS_ORACLE_PASSWORD"  # the variable's NAME, never the password
owners = ["ADEMPIERE"]
profile = "p2"
dictionary = "idempiere"

[docs-estate]
licences = { "idempiere-db" = "GPL-2.0" }

[extensions.lakehouse]
source-engine = "oracle"
source-database = "idempiere-db"       # reuses the [[databases]] entry
source-catalog = "idempiere"           # the Trino catalog over Oracle
namespace-prefix = "bronze_idem_"
application-watermark = "UPDATED"
require-signoff = true                 # steps 4–7 refuse to write without step 3 signed off
require-plan-approval = true           # generate builds only the approved plan; gold only approved requirements

[llm.tasks.requirements]               # step 4's drafting model; local, so never metered
provider = "ollama"
model = "llama3"

ekos init --detect writes most of the top half from what it finds in the tree.

Step 1 — Analyse sources

In the console, the Sources tab adds directories and databases, keeps a database password in the server's keychain, tests the connection, and shows the database's census next to what was compiled. Init & compile runs the pipeline. From a terminal:

ekos database add idempiere-db --connect localhost:51521/XEPDB1 --user ekos_ro \
    --password-env EKOS_ORACLE_PASSWORD --owner ADEMPIERE --profile p2 --dictionary idempiere
ekos database test idempiere-db
ekos build && ekos recover && ekos resolve && ekos compile && ekos commit

Everything is recovered deterministically: Java types and the application model (Indexing Java Applications); Oracle SQL, PL/SQL and migration history, the live catalog with profiles, code-vs-database conflicts and each table's write behaviour (Indexing a Database); tickets and documentation sections (Indexing Enterprise Applications). Every command that changes something writes a JSON-lines run log ending with its model-call count.

On iDempiere: 763 model tables with 16,660 columns; census equal to compiled for tables, views, routines and triggers; 903 tables profiled in 6 s; 444 code-vs-database conflicts, 239 explained by the tree; 4,597 tickets linked to 3,074 change sets. Zero model calls.

Step 2 — Documentation

ekos docs generate --layout estate --output docs/estate     # EKOS core
ekos-lakehouse lakehouse docs publish --dry-run             # what would change in the wiki; nothing is sent
ekos-lakehouse lakehouse docs publish

The estate pages cover functional areas with ER diagrams, one page per in-scope table (columns with business names, keys and the application's references, who reads and writes it, write behaviour, schema history, open conflicts), a glossary, routines, schema history by release and findings. Two gates are built into the renderer: no data value reaches a page (a column profile gives counts only), and text is used only from sources whose licence is known (detected from a LICENSE file, or set in [docs-estate] licences; a database has none until you set one). A re-run keeps manual sections and deletes only pages it generated.

The store is set in [extensions.lakehouse.docs] (target = "github" | "azure-devops" | "confluence"), with the publishers' own credential variables (GITHUB_TOKEN, AZDO_PAT, CONFLUENCE_*). The console's Documentation page has Generate, Preview publish and Publish. The step is done only once the latest generation is published.

On iDempiere: 1,060 pages in 26 s, published to the demo repository's wiki.

Step 3 — Review and sign-off

The source model is reviewed as six subjects: scope (which tables the lakehouse takes), PII, conflicts, identity, write behaviour and glossary. A person decides items, then signs the subject off on the digest of what was shown. A sign-off's validity is recomputed on every read: when a fact under it changes, that subject's sign-off becomes stale and names what moved, while the others stay valid.

ekos review list                                  # subjects, counts, sign-off states
ekos review show scope --undecided
ekos review scope AD_ERROR AD_PINSTANCE_LOG --out --reason "runtime logs, not business data"
ekos review pii AD_USER.EMAIL --confirm --mask hash --reason "personal email"
ekos review write-behaviour C_ORDER --set update_class=raw_sql_sets_updated --reason "verified in code review"
ekos review identity "C_ORDER = ORDERS" --confirm
ekos conflicts resolve <name> --both-valid --note "…"
ekos signoff show scope                           # prints the digest
ekos signoff approve scope --digest <digest> --note "reviewed with the app team"
ekos workflow status

The console's Review tab does the same, and signs a subject off only after its name is typed; deciding needs the reviewer role. The decisions flow on: out-of-scope tables are never planned, confirmed PII columns are masked in silver (hash → HMAC, redact → NULL), and write-behaviour overrides change a table's load mode and delete plan. Only subjects with a valid sign-off are applied.

On iDempiere: the review computes in 1.8 s (cached). A full sign-off of the whole estate needs a person to settle 195 conflicts, so the testbed project stays at 4 of 6 subjects signed. The video demo's project scoped the review to a 33-table order-to-invoice slice and signed all six.

Step 4 — Requirements

An operator describes each mart wanted. A model drafts its metadata, EKOS resolves every term deterministically, and whatever does not resolve is a question for the operator, never sent back to the model.

ekos-lakehouse lakehouse requirements draft --mart category_region_sales \
    --text "monthly sales by product category and region for completed orders"
ekos-lakehouse lakehouse requirements draft --mart sales_by_month --file sales.md   # written by hand
ekos-lakehouse lakehouse requirements show --mart category_region_sales
ekos-lakehouse lakehouse requirements edit --mart category_region_sales \
    --term region --value "c_region.name via c_orderline.c_bpartner_location_id, c_location.c_region_id"
ekos-lakehouse lakehouse requirements approve --mart category_region_sales --note "agreed with finance"

A requirement is a YAML header (mart, grain, dimensions, measures, filters) followed by the request as prose:

---
mart: sales_by_month
grain: [month, customer]
dimensions:
  month: month of c_order.dateordered
  customer: c_bpartner.name via c_order.c_bpartner_id
measures:
  total: sum of c_order.grandtotal
  orders: count of c_order
filters:
  - c_order.issotrx is true
  - c_order.docstatus = 'CO'
---
Completed sales orders per month and customer.

Terms match a silver table and column of the deployed plan (an out-of-scope table does not exist here). Tables are joined along declared single-column foreign keys, child to parent only, at most four keys deep; several possible paths are a question, answered with via. approve refuses a requirement with any open question, commits it as marts/<mart>.md and records the approval as a ledger fact. A metered model route is refused unless --allow-metered is given, and in the console unless the tenant allows metered calls.

On iDempiere: a hand-written requirement resolved at once. The plan's example request, drafted by local llama3, came back with three questions, plus two mistakes only a person could see (a dimension called store, a filter selecting purchase orders instead of sales). Nine edits answered it, three of them via choices among 3, 36 and 19 key paths.

Step 5 — Extraction and landing

The planner proposes, per table, how it lands in bronze, from EKOS's facts and step 3's decisions:

• Load mode: incremental on application-watermark where the code always moves it, full where a raw UPDATE leaves it alone.
• Deletes without a marker: checked every run by a bucketed key diff, or audited every delete-audit-days where the code never hard-deletes. A mass-delete guard applies nothing when more than 5% or 10,000 keys vanish at once, until a person accepts it.
• Types: each column's Oracle → Iceberg mapping with its lossiness; a column that cannot land exactly is refused, and its table with it.
• Masks and scope from step 3.

ekos-lakehouse lakehouse plan
ekos-lakehouse lakehouse extraction show --table ADEMPIERE.C_BPARTNER
ekos-lakehouse lakehouse extraction change --table C_BPARTNER --full --reason "order import is being reworked"
ekos-lakehouse lakehouse extraction approve --note "first load"

The approval is a ledger fact with the plan's digest. With require-plan-approval, generate builds only the approved plan, and refuses, naming the tables, when the source, step 3 or a change has moved since. The console's Extraction page shows each table's evidence and takes changes with a typed reason.

On iDempiere: 849 tables planned, 1 refused, 53 out of scope, 1 column masked; 715 incremental and 134 full. A single delete, a delete and re-insert, a 148-key mass delete stopped by the guard and applied once accepted, and a retained purge each reconciled green.

Step 6 — Bronze and silver

ekos-lakehouse lakehouse generate                 # the pipeline repository: bronze SQL, dbt silver, Dagster
ekos-lakehouse lakehouse run --in-process --wait  # the console's Run the pipeline
ekos-lakehouse lakehouse reconcile --table ADEMPIERE.C_ORDER
ekos-lakehouse lakehouse status

Every bronze load is reconciled with the source at V1–V3 with a planted control. Silver reads what the source and EKOS know about each column: Y/N flags become booleans, a check constraint's code list fails the build, and the application model's code lists and logical foreign keys are tested as warnings, since the database does not enforce them.

On iDempiere: the full 849-table run needs more memory than the testbed machine has, so a 165-table subset, closed over its references, ran in one Dagster process: every bronze table reconciled with Oracle, 165 silver models built, and 3,664 of 3,667 tests passed. The 3 warnings are real data outside the application's lists.

Step 7 — Marts

Each approved requirement becomes a gold table:

ekos-lakehouse lakehouse marts propose --mart category_region_sales     # a branch ekos/gold/<mart>/<id>
ekos-lakehouse lakehouse marts merge --mart category_region_sales --note "agreed with finance"
ekos-lakehouse lakehouse marts build --mart category_region_sales --wait
ekos-lakehouse lakehouse marts reconcile --mart category_region_sales

Propose checks the whole statement against the requirement's own atoms (anti-invention), plans it on Trino, and adds a grain-uniqueness test and one reconciliation test per additive measure. Merge is the approval of the generated model and is recorded as a ledger fact. Build runs only that mart through Dagster. Reconcile compares each additive measure's total in the mart with the same aggregate over silver. A term marked drafted was written by a model: read it.

On iDempiere: four marts went from approved to reconciled, every total equal (for example 379.90 = 379.90 and 3 = 3 orders for sales_by_month).

Starting Dagster for a project turns on its generated schedules, which are on by default. On a test machine, decide whether they should run before starting it.

What is guaranteed

• Read-only on the source. Database sessions are read-only transactions; the source tree is never written.
• No secret or data value stored. Passwords stay in environment variables or the keychain. Profiles keep counts and bounds of numbers and dates, never text values. Run logs and estate pages carry no value.
• Human-only decisions. Review decisions, sign-offs, extraction changes and approvals, requirement approvals and merging a mart run as the signed-in person; no MCP tool reaches them.
• No metered model call without consent. Steps 1–3 make none; step 4 refuses a metered route without --allow-metered.

See it end to end

The public demo repository alexeyban/ekos-lakehouse-oracle has the whole iDempiere run: a 7:52 narrated video of a second project from analysis to three reconciled marts, a demo book generated from the console (tools/demo-book in ekos-lakehouse), the published wiki, the generated pipeline and the measurements.

Not done yet: a complete step-3 sign-off of the whole iDempiere estate, relative dates in a requirement ("last two years"), several marts from one request, and CDC.