Repository Data Model — tables, views & datasets¶
Data model of the ELTMaestro audit / metadata database (the sqlmaestro
PostgreSQL instance), scoped to the objects the application code actually
touches. Rather than list the whole initdb.sql catalog, this inventory is
derived from a reference scan of the engine, meta-service, and WPF client
code — an object appears here only if it is referenced by one of those
layers. Objects with no code reference are listed separately under
Excluded.
For the column-level model of the execution core — full ER diagram, column dictionaries, the complete foreign-key map, and structural gotchas — see DATA-MODEL-SPINE.md.
In the model: 72 objects (67 tables + 5 views), each with ≥1 reference in engine / service / WPF code. Excluded: 41 — 22 dashboard views + 19 tables (8 reached only via dynamically-built SQL, 11 with no code path at all). Derived from a word-boundary scan of
.java/.cs/.xaml; see Methodology.
The database is the control plane, not the job store¶
Job, batch, and control-test definitions are XML files on disk under
/sqlmdata/sqlm_deployed_mappings/{Jobs,Batches,ControlTests}/. That is why
t_job_meta has no XML column. The tables here hold two things:
- the catalog — names, types, tags, edit-lock state, version pointers (owned by the meta-service; edited by the WPF client through it), and
- the execution record — runs, per-step status, logs, metrics (written by the batch engine at runtime; read back by the client for monitoring).
WPF client ──SOAP──▶ meta-service ──JDBC──▶ Postgres ◀──JDBC── batch engine
(reads both, (owns the (catalog + (writes the
edits catalog catalog) execution execution
via service) record) record)
How to read the inventory¶
- Phase —
DESIGN(authored/read while building),RUNTIME(written by the engine during a batch),BOTH(definition authored, results written at run), orSERVICE(written by the meta-service only). - Owner — the primary writer:
engine,service,wpf, orinstall(seeded at install time, then read). - Eng / Svc / WPF — how many lines of that layer's source mention the object's name. See What the numbers mean below.
Layers: Eng = root-engine-core + root-engine-shared (the Java batch
engine); Svc = root-engine-service (the Java SOAP meta-service); WPF =
root-win-client (the C#/XAML desktop client).
What the Eng / Svc / WPF numbers mean¶
Each number is a count of source lines that reference the object's name in
that layer, produced by a word-boundary, case-insensitive grep over
.java / .cs / .xaml (build dirs excluded):
# the count in the WPF column for t_step_status:
grep -rwni "t_step_status" root-win-client \
--include=*.cs --include=*.xaml --exclude-dir=obj --exclude-dir=bin
# ... 16 matching lines -> WPF = 16
Read them as a "where does this table live in the code" signal, not a query
count. Worked example — t_step_status is Eng 7 · Svc 1 · WPF 16:
- The engine writes it (7 refs in the batch runner) and the WPF reads it
heavily (16 refs — the LogViewer and Runtime Status tab both query it),
while the service barely touches it (1). That pattern is how each row's
Owner and Phase were assigned (here:
RUNTIME, ownerengine). - The numbers are also the filter: a row is in this model if any column is
> 0(or the table is FK-reachable — see below).
Three caveats, all of which apply to the table above:
- It counts mentions, not queries. Of
t_step_status's 16 WPF hits, some are real SQL (from public.t_step_status) but several are comments and one is commented-out code. So16means "referenced in 16 places," not "16 queries." - Common English words inflate.
job= 1144 andconnection= 485 in the engine because those words appear in variable names, method names, and prose throughout the code — not that many SQL statements. Treat large counts on such names as "heavily used," not literal. 0does not always mean unused. SQL assembled by string concatenation at runtime never contains the table name as a single token, sogrepcan't see it. Those objects are still real — they are the Excluded → dynamic-SQL-only category (e.g.t_mpp_routines,control_test_point), listed there rather than silently dropped, but they are not in the code-referenced model above.
1. Batch execution — RUNTIME¶
The engine inserts a batch_cycle_run per launch and a batch_cycle_run_job
per job leg; the WPF LogViewer and Runtime Status tab read these back.
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
batch_cycle |
RUNTIME | engine | 6 | 1 | 19 | Batch header. PK batch_cycle_id (seq seq_batch_cycle_id). |
batch_cycle_run |
RUNTIME | engine | 13 | 9 | 22 | One row per run. PK batch_run_num (seq seq_batch_run_num); carries batch_status_cd. Root of nearly every runtime join. |
batch_cycle_run_job |
RUNTIME | engine | 26 | 13 | 21 | Job leg within a run. PK (batch_run_num, job_id) → FK to both. |
batch_cycle_type |
RUNTIME | install | 0 | 0 | 2 | Lookup of batch types. PK batch_cycle_type_cd. |
job_batch_cycle_run_msg |
RUNTIME | service | 0 | 1 | 0 | Per-run messages/warnings. PK (msg_num, batch_run_num, job_id). |
2. Step execution & logs — RUNTIME¶
The per-step record the LogViewer and Runtime Status mermaid tab consume.
Not FK-linked to the run tables — keyed logically on
(batch_run_num, job_id, step_id).
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
t_step_status |
RUNTIME | engine | 7 | 1 | 16 | Latest status per step of a run. Historical — retains deleted steps; read by Runtime Status + LogViewer. |
t_step_run_detail |
RUNTIME | engine | 9 | 5 | 12 | Per-step log lines (log_ordinal, message, stack, affected rows/bytes). |
t_step_watermark |
RUNTIME | engine | 10 | 0 | 0 | Delta/CDC high-water marks; also holds a full S3 folder path (PushFile). See VARIABLES.md. |
t_workflow_state_history |
RUNTIME | engine | 4 | 1 | 4 | State transitions. Unique index on (run_seq_number, run_number, job_id). |
t_console |
RUNTIME | engine | 0 | 1 | 3 | Captured console output stream. |
t_event_log |
RUNTIME | service | 2 | 4 | 0 | System event log (service-side operation auditing). |
3. Process & session control — RUNTIME¶
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
t_pid_sessions |
RUNTIME | engine | 10 | 1 | 0 | Running JVM/SSH sessions per run_number (PK). Basis for HALT. |
v_pid_sessions |
RUNTIME | engine | 2 | 0 | 0 | View over active sessions. |
4. Job & workflow catalog — DESIGN¶
Definitions are XML on disk; these hold identity, metadata, edit-lock, versions.
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
job |
DESIGN | service | 1144 | 85 | 232 | Central job registry. PK job_id (seq seq_job_id). Count inflated by the word "job" — but genuinely the busiest entity. |
t_job_meta |
DESIGN | service | 0 | 3 | 14 | Job catalog row. PK job_name; type, platform, tags, lock_id (edit lock). No XML column — file-backed. |
t_job_meta_history |
DESIGN | service | 0 | 1 | 5 | Version log. PK version_key (seq seq_job_version_id), version_path → file. Backs Change History + XML diff. |
t_job_alert_setting |
DESIGN | service | 2 | 0 | 3 | Per-job email alert config; pairs with the SMTP row in t_connection_general. |
5. Connections — DESIGN¶
See DATA-MODEL-SPINE.md for the two-registry
gotcha (connection vs t_jdbc / t_connection_general).
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
connection |
DESIGN | service | 485 | 165 | 462 | Legacy connection master. PK connection_id (seq seq_connection_id), char(1) type code. Count inflated. |
connection2 |
DESIGN | service | 10 | 5 | 15 | Name-keyed connection variant (adds port + secondary host). PK connection_nm. |
connection_type |
DESIGN | install | 22 | 3 | 20 | Type lookup. PK connection_type_cd char(1). |
t_connection_general |
DESIGN | service | 8 | 6 | 31 | Name-keyed general/SMTP connection settings (param blob). PK connection_name. |
t_jdbc |
DESIGN | service | 12 | 8 | 41 | Modern JDBC registry. PK connection_id — char(32), not the bigint; no FK to connection. |
t_hdfs_settings · t_redshift_settings · t_netezza_settings · t_xtremedata_settings · t_dashdb_settings · t_dataquality_settings |
DESIGN | service | — | — | — | Per-vendor param/value setting tables (each referenced by the WPF admin panels; t_hdfs_settings also engine + service). |
6. JDBC metadata cache — DESIGN¶
Crawled catalog of remote databases, populated when the designer browses a source; read back to build pick-lists and preview queries.
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
t_jdbc_database |
DESIGN | service | 0 | 3 | 8 | Cached databases per connection. |
t_jdbc_schema |
DESIGN | service | 0 | 2 | 2 | Cached schemas. |
t_jdbc_table |
DESIGN | service | 0 | 3 | 2 | Cached tables. |
t_jdbc_column |
DESIGN | service | 0 | 3 | 1 | Cached columns + java.sql.Types ids. |
7. Control tests — BOTH¶
Definitions authored in the designer; results written per run. Column detail in DATA-MODEL-SPINE.md.
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
control_test |
DESIGN | service | 5 | 7 | 4 | Test master. PK control_test_id (seq seq_control_test_id). |
control_test_type |
DESIGN | install | 0 | 0 | 2 | Type lookup. PK control_test_type_cd. |
control_test_hierarchy |
DESIGN | service | 0 | 0 | 3 | Parent/child rollup (self-referential). |
control_test_h |
DESIGN | service | 0 | 1 | 0 | History/expiry variant. PK (control_test_id, expiration_ts). |
control_test_run |
RUNTIME | engine | 0 | 1 | 3 | Result per run. PK (batch_run_num, control_test_id, grouping_key); FK → batch_cycle_run, control_test. |
8. Data-quality metrics — BOTH¶
In the metrics schema. Definitions on the design side; metric values written
at run and read by the Runtime Metrics grid.
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
data_quality_metrics |
RUNTIME | engine | 9 | 0 | 17 | Metric rows per run. Seq seq_data_quality_metrics. Loosely typed — see G6. |
dq_definition |
DESIGN | service | 1 | 3 | 22 | DQ rule definitions. |
dq_group_schema |
DESIGN | service | 1 | 2 | 4 | DQ grouping. Seqs seq_dq_group, seq_dq_id. |
dq_result |
RUNTIME | engine | 2 | 0 | 0 | DQ evaluation results. |
data_quality_chain |
DESIGN | engine | 4 | 0 | 1 | DQ chain container. |
data_quality_chain_node |
DESIGN | engine | 2 | 0 | 0 | Nodes within a chain. |
default_metrics |
DESIGN | install | 0 | 0 | 2 | Seed set of default metric types. |
9. Profiling — BOTH¶
Column/table profiler. See SCHEMA-PROFILE.md.
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
t_column_profile_sql |
DESIGN | engine | 1 | 0 | 5 | Profiler SQL catalog. PK (platform_type, profile_name). |
t_column_profile_results |
RUNTIME | engine | 2 | 0 | 0 | Per-column profile output. |
t_sql_profile_table |
RUNTIME | engine | 2 | 0 | 0 | Table-level profile. PK profile_id (seq seq_profile_id). |
t_sql_profile_column |
RUNTIME | engine | 1 | 0 | 0 | Column-level profile. Unique idx on (profile_id, column_name). |
v_sql_profile_table |
BOTH | service | 0 | 0 | 4 | Client-facing profile view. |
v_sql_profile_column |
BOTH | service | 1 | 0 | 3 | Client-facing profile view. |
10. Scheduling & alerting — DESIGN¶
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
cron_schedule_meta |
DESIGN | service | 0 | 0 | 7 | Cron entries. PK cron_tab_entry. Edited in the scheduler UI. |
object_trigger |
DESIGN | service | 0 | 1 | 0 | Object-based triggers. Seq seq_object_trigger. |
11. Security & RBAC — DESIGN¶
The service enforces owner-or-developer(≥1) guards on save/delete.
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
t_user |
DESIGN | service | 4 | 4 | 5 | User accounts (MD5 crypt passwords). |
t_user_session |
DESIGN | service | 0 | 3 | 0 | Auth sessions/tokens. |
t_component_role_permission |
DESIGN | service | 0 | 0 | 2 | Component-level perms. |
v_component_role_permission |
DESIGN | service | 0 | 4 | 1 | Effective component permissions. |
12. Service audit — SERVICE¶
SOAP request/response logging and auth auditing — written by the meta-service. See SERVICE.md.
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
t_meta_request |
SERVICE | service | 5 | 3 | 0 | Inbound SOAP request record. |
t_meta_response |
SERVICE | service | 3 | 1 | 0 | Outbound response record. |
t_service_audit |
SERVICE | service | 0 | 2 | 0 | Operation audit trail. |
t_service_audit_invalid_auth |
SERVICE | service | 2 | 3 | 0 | Failed-auth log. |
13. Config, system & lookups — DESIGN¶
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
t_system_settings |
DESIGN | service | 6 | 12 | 4 | Global param/value settings. Read across all layers. |
license |
DESIGN | service | 0 | 4 | 30 | License records. Count partly inflated. |
t_maestro_servers |
DESIGN | wpf | 3 | 1 | 3 | Server registry; also the master→edge-node map (EDGE-NODE-SETUP.md). |
t_sql_types |
DESIGN | install | 0 | 0 | 1 | java.sql.Types mapping catalog. |
t_step_help / v_step_help |
DESIGN | install | 0 | 0 | 3 | Step help text shown in the designer. |
hd_functions / hd_table_meta |
DESIGN | engine | 1 | 0 | 2 | Function catalog + HD table metadata (see FUNCTION-CATALOG-MIGRATION.md). |
14. Machine learning — BOTH¶
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
ml_models |
BOTH | engine | 2 | 0 | 5 | Trained-model registry (ML feature-trainer/predictor steps). |
ml_feature_transformation |
BOTH | engine | 3 | 0 | 10 | Feature-engineering definitions/results. |
15. Deprecated¶
| Object | Phase | Owner | Eng | Svc | WPF | Notes |
|---|---|---|---|---|---|---|
pipe_schema2s3 |
RUNTIME | engine | 7 | 0 | 4 | schema→S3 pipe state. Step is @Deprecated — prefer JdbcTarget* / onstage loaders. |
pipe_schema2s3_loads |
RUNTIME | engine | 4 | 0 | 0 | Per-load records for the deprecated pipe. |
Excluded from the model¶
41 schema objects are not in the model above because no engine / service / WPF code references them. Two categories:
Referenced only via dynamically-built SQL (real, but not literally grep-able)¶
These carry no literal token in the source (the SQL is assembled at runtime), so they fall outside a code-reference scan — but they are genuinely used. Listed here so they are not mistaken for dead objects.
| Object | What it is |
|---|---|
t_mpp_routines · t_mpp_system |
Per-platform MPP dialect routines + system vars (base64-tagged); read by the engine to build target SQL. See MPP-SETUP.md. |
t_workflow_item |
Workflow registry (name/type/tags/enabled). PK workflow_name. |
t_jdbc_metadata |
Driver capability metadata. PK driver_class. |
file_load_history |
Load-file audit written by loaders. |
t_agent_sessions · t_agent_session_response |
Agent session registry + responses. |
control_test_point |
Control-test expected/actual points; PK (control_test_id, actual_or_expected), FK → control_test + connection. FK-central to the control-test model but reached only via dynamic SQL. |
No code path — candidates to retire¶
Genuinely unreferenced. 11 tables: click_house_tmp (transient staging,
not a real entity), connection_meta_onstage, cron_schedule_current,
object_alert_rule, sqlm_config, sqlm_reserved, t_configuration_options,
t_job_permissions, t_role_type, t_app_role_permission,
t_mailserver_settings (legacy, superseded by t_connection_general).
22 dashboard/reporting views with no code reference — resolved by external
BI or dormant: v_batch_run_status, v_last_batch_cycle_run,
v_last_run_job_detail, v_running_batch_progress, v_longest_batch_runtimes,
v_longest_job_runtimes, v_high_runtime_growth_batches,
v_high_runtime_growth_jobs, v_high_failure_rate_batchs,
v_high_failure_rate_jobs, v_high_failure_rate_control_tests,
v_lastload_test_failures, v_missing_control_test_points, v_test_statistics,
v_tune_test_tolerances, v_control_test_hierarchy1, v_components,
v_app_role_permission, v_jdbc_database, v_jdbc_schema, v_jdbc_table,
v_jdbc_metadata.
These are candidates for a decision: wire the useful views into the client's dashboards, or drop them from
initdb.sql.
Methodology¶
- Starting point — a word-boundary, case-insensitive
grepfor every object name defined ininitdb.sql(source/root-engine-install/db/initdb.sql) across.java/.cs/.xamlin the three layers (build dirs excluded). - In the model — object referenced by ≥1 layer. 72 objects.
- Excluded — zero references across all three layers (41 objects), split
into "dynamic-SQL only" (real, but the name is assembled at runtime so
grepcan't see it — includescontrol_test_point) vs "no code path" above. - Phase and owner — assigned from each object's role plus known engine/service/client responsibilities; architectural judgments, not derived from the counts.
- Caveat — counts are a reference-frequency signal, not exact query counts. Common English names inflate; dynamic SQL under-counts (hence the excluded "dynamic-SQL only" category — those tables are real, just not literally referenced).
Keys/FKs (15 declared FKs, 47 PK/unique constraints) and sequences are covered in DATA-MODEL-SPINE.md.
See also: ARCHITECTURE.md · ENGINE.md (who writes the runtime tables) · SERVICE.md (who owns the catalog) · SCHEMA-PROFILE.md (profiling tables).