EKOSdocs
Docs / Guides Build

Indexing a Database

Recover tables, keys, views, routines and history from SQL files and dbt projects, and read live Oracle, PostgreSQL and ClickHouse catalogs.

EKOS reads a database two ways, and a project can use both:

Route Reads Configured by
Files checked-in DDL, views, routines, migration scripts, dbt projects [observe] paths + [recover.sql] dialect rules
Live catalog the catalog of a running Oracle, PostgreSQL or ClickHouse database, read-only [[databases]] / ekos database add

Neither route reads a row value into the ledger.

SQL files

  1. Put the SQL where [observe] paths covers it.
  2. Set the dialect. ekos init --detect writes one rule per engine-named directory (oracle/, postgresql/, …); otherwise write them yourself:
[recover.sql]
default-dialect = "generic"

[[recover.sql.dialect-rules]]          # first path-glob match wins
path-glob = "db/legacy/**/*.sql"
dialect = "mysql"

[[recover.sql.dialect-rules]]
path-glob = "db/warehouse/**/*.sql"
dialect = "snowflake"

Dialects: generic, postgres, mysql, mssql (also tsql, synapse), snowflake, databricks, clickhouse, trino, oracle.

The dialect still matters. When a whole file fails to parse, each statement is tried alone: a failing one is skipped and reported as SQL005, and SQL001 names the dialect when a file states tables and yields none. A wrong dialect can therefore cost you most of a schema with only warnings to show for it.

  1. Compile as usual and check that tables exist:
ekos ekl "FIND Object WHERE kind = 'Table' LIMIT 20"
ekos coverage

Zero tables for a repo that has a schema means: check the dialect, then read the recover diagnostics in .ekos/diagnostics/ for SQL001 and SQL005.

Each Table records its dialect, and definitions are compared only within one dialect, so a project that ships every script for Oracle and for PostgreSQL does not see the two copies as conflicting.

What the files give you

Source Recovered
DDL tables, columns, keys, CHECK constraints; ALTER TABLE … ADD … FOREIGN KEY as an edge
COMMENT ON … author-written descriptions of tables, columns, views and routines, ranked above LLM text
CREATE [MATERIALIZED] VIEW View objects with the tables and routines their query reads
PL/pgSQL and PL/SQL routines, triggers, Oracle packages Procedure objects with one ProcedureStatement per statement and what each reads, writes and calls; triggers classified as validation, derived column, audit, cascade, mixed or unknown, with reasons
migration scripts named <yyyyMMddHHmm>_<description>.sql dated SchemaChangeSets with one classified SchemaChange per operation, linked to their table
SELECT, VIEW, stored procedures Transformation IR (source → filter → join → aggregate → sink)
dbt projects one Table per models/**/*.sql and per declared source — from the project's own files, never manifest.json
Pentaho .ktr/.kjb Transformation IR, exportable to dbt with ekos dbt

Oracle scripts are read with SQL*Plus lines (SET, PROMPT, @file) set aside and Oracle-only clauses rewritten, keeping the file's line numbers. On iDempiere release-13, 9 of 62,251 Oracle migration statements were rejected, 60 of 61 routines were recovered completely, and 2,154 change sets yielded 26,060 classified changes, with no model calls.

Live databases

A project can name its databases next to its sources. ekos build reads each catalog once per build, through a read-only session, and checks that a write is refused. The password is never in ekos.toml, only the name of the variable that holds it:

ekos database add erp --connect localhost:51521/XEPDB1 --user ekos_ro \
    --password-env EKOS_ORACLE_PASSWORD --owner ADEMPIERE --profile p2     # --kind oracle is the default
ekos database add shop --kind postgres --connect db.local:5432/shop --user shop_ro \
    --password-env SHOP_PW --owner public
ekos database add wh --kind clickhouse --connect https://ch.local:8443 --user ro \
    --password-env WH_PW --owner analytics
ekos database test shop          # read-only connection, write refused, census next to what was compiled
ekos database list

Each add writes a [[databases]] entry:

[[databases]]
name = "erp"
kind = "oracle"                        # oracle | postgres | clickhouse
connect = "localhost:51521/XEPDB1"     # postgres: host[:port]/database · clickhouse: http[s]://host[:port]
user = "ekos_ro"
password-env = "EKOS_ORACLE_PASSWORD"  # the variable's NAME, never the password
owners = ["ADEMPIERE"]                 # schemas to read (ClickHouse: databases)
profile = "p2"                         # Oracle only
dictionary = "idempiere"               # Oracle only: also read AD_Table's flags
Kind How it is read Read-only user
oracle DBA_* views inside SET TRANSACTION READ ONLY, reconciled against *_OBJECTS so a user who cannot see some objects is an error, not a smaller answer CREATE SESSION, SELECT on the schema, SELECT_CATALOG_ROLE
postgres a default_transaction_read_only session with timeouts, reconciled against the server's own counts GRANT USAGE + GRANT SELECT
clickhouse system.tables / system.columns, every query with readonly=1 SETTINGS readonly = 1, GRANT SELECT ON db.*, system.*

ekos recover turns the catalog into ordinary Table, View, Procedure and trigger objects marked origin: live; routines go through the same readers as files. Live objects never merge with tables from scripts, and a dropped object leaves the model on the next build. In ClickHouse, sorting and primary keys are table properties, not unique keys.

Oracle needs a build with the oracle feature (cargo build --release -p ekos --features oracle) and Oracle Instant Client on LD_LIBRARY_PATH at run time. The prebuilt binaries are built without it, and a kind = "oracle" entry then fails ekos build with a message saying so. PostgreSQL and ClickHouse need nothing installed.

Profiles (Oracle only). profile decides how much build measures: p0 optimizer statistics (the default), p1 exact row counts, p2 one aggregate scan per table for null counts, number and date bounds and text lengths. No tier stores a text value, and bounds are withheld for columns named like personal data. On iDempiere, 903 tables were profiled at p2 in 6 s.

In the web console, a project's Sources tab does the same: add a database (the password goes to the server's keychain), test it, see the census next to the compiled counts, and Init & compile.

Code against database

When a project has both the code and the live database, ekos commit compares them and records each disagreement as a conflict that cites both sides: a model table or column the database lacks, a table no model describes, a mandatory column that is nullable, a routine file whose text differs from the installed one. When the tree explains a difference (a migration newer than the database, a translation table), the conflict says so.

ekos conflicts list --type model_column_missing
ekos conflicts show <name>
ekos conflicts resolve <name> --pick 1 --note "the migration is pending"     # human only

On iDempiere: 444 conflicts, 239 explained by the tree itself.

ekos commit also writes one WriteBehaviour per table: whether every update moves the timestamp column, and every way rows disappear (code, users, cascades, purges), each cited. On iDempiere, 144 of 903 tables have a raw UPDATE that skips Updated.

ekos ekl "FIND Object WHERE kind = 'WriteBehaviour' AND update_class = 'raw_sql_skips_updated'"

Explain a legacy pipeline

Through MCP, ekos_transformation_explain walks a pipeline step by step with evidence per step; ekos_transformation_diff compares an old pipeline with a new one to check a migration preserved the logic. Unmapped steps are reported, never guessed.

To find the names that point at nothing — a routine writing a table no script creates, Java SQL against a dropped table — see Checking Reference Integrity.

The older ClickHouse observer configured by EKOS_CLICKHOUSE_* environment variables (RFC 0056) still works, and its gated live NL-to-SQL tool is off by default. For a project database, prefer [[databases]].