SCHEMORA: Schema Automation & Integration

• SCHEMORA is a family of schema-centric automation frameworks that enable safe relational schema evolution, matching, and semantic refinement.
• It automates impact analysis, recommendation generation, and SQL patch synthesis to reduce manual workload in database schema modifications.
• The approach integrates prompt-based LLMs and reinforcement learning to enhance schema matching, reasoning, and agentic data-source integration.

SCHEMORA is an acronym associated with several closely related lines of database-systems research concerned with schema reasoning, transformation, and interoperability. In current arXiv usage, it denotes at least three distinct but adjacent formulations: “SCHEMA Evolution Management and Orchestration with Recommendations and Automation,” an intelligent framework for safe relational schema evolution; “schema matching via multi-stage recommendation and metadata enrichment using off-the-shelf LLMs,” a prompt-based framework for heterogeneous schema matching; and an envisioned “Schema-Oriented Reasoning & Adaptation” pipeline in which agentic semantic refinement serves as one stage of automated schema understanding, refinement, and data-source integration (Etien et al., 2024, Gungor et al., 18 Jul 2025, Rissaki et al., 2024).

1. Terminological scope and research lineage

The acronym is not monosemous. Rather than naming a single canonical system, it has been used for multiple schema-centric architectures whose common concern is to reduce manual effort in database understanding and restructuring under explicit structural constraints, recommendation logic, and validation.

Source	Expansion or characterization	Primary scope
(Etien et al., 2024)	SCHEMA Evolution Management and Orchestration with Recommendations and Automation	Relational schema evolution, impact analysis, patch generation
(Gungor et al., 18 Jul 2025)	schema matching via multi-stage recommendation and metadata enrichment using off-the-shelf LLMs	Schema matching without labeled training data
(Rissaki et al., 2024)	Schema-Oriented Reasoning & Adaptation, envisioned	End-to-end schema understanding, refinement, and data-source integration
(Yuan et al., 2021)	MORTAL ideas “lifted almost ‘as-is’ into a new system called SCHEMORA”	Reinforcement-learning-based schema design blueprint

This suggests that SCHEMORA is best understood as a family of schema-oriented automation patterns rather than a single fixed software artifact. The papers collectively span schema evolution, semantic layer construction, schema matching, and reinforcement-learning-guided schema design (Etien et al., 2024, Gungor et al., 18 Jul 2025, Rissaki et al., 2024, Yuan et al., 2021).

2. SCHEMORA as schema evolution management and orchestration

In the 2024 formulation, SCHEMORA is “an intelligent framework designed to assist database architects (DBAs) in evolving relational database schemas in a safe, consistent, and efficient manner” (Etien et al., 2024). Its primary goals are to “automatically compute the full impact of a user-specified schema change on all dependent entities,” to “generate actionable recommendations,” and to “compile and order the minimal sequence of SQL statements (‘patch’) that implements both the original change and its corrective actions, ensuring the schema remains valid at every step.”

The framework is built on a unified meta-model $M=(E,R)$, where $E$ is the set of schema entities and $R \subseteq E \times E$ is the reference relation. The entity universe is defined as

$E = T \cup C \cup V \cup P \cup K \cup S \cup X,$

with $T$ the set of tables, $C$ columns, $V$ views, $P$ stored procedures including functions, $K$ constraints, $S$ namespaces, and $E$0 derived-table definitions, query clauses, parameters, and triggers. Reference edges are typed. The paper enumerates ColumnReference, TableReference, StoredProcedureCall, VariableReference, and TypeReference (Etien et al., 2024).

Schema-change operations are formalized as five atomic classes: Add(e), Remove(e), Rename(e, newName), Move(e, newSchema), and Modify(e, delta). The schema is represented as a directed acyclic graph $E$1; “by construction $E$2 has no cycles.” On this graph, SCHEMORA defines the impact function

$E$3

that is, all entities that directly reference the changed entity.

The significance of this formulation lies in its treatment of both structural and behavioral schema artifacts. Tables and columns are handled alongside views, stored procedures, triggers, and constraints, so schema evolution is modeled as a graph transformation problem over reified references rather than as isolated DDL editing. This is particularly relevant when hidden dependencies occur inside stored procedure bodies or query clauses, where operator ordering is often the source of failure (Etien et al., 2024).

3. Impact analysis, recommendation generation, and patch synthesis

SCHEMORA performs recursive impact analysis with a queue-driven procedure. Starting from a set of initial operators, it builds a forest of operator-trees. For each operator $E$4, it computes impact(o), partitions the impacted entities into “coherent subsets” $E$5, invokes RecommendFixes(o,S_i), presents recommendations to the DBA, and enqueues the selected corrective operators until no new operators remain (Etien et al., 2024).

Recommendation generation is rule-based. For each pair $E$6, the framework uses a rule table of the form

$E$7

where $E$8 is pre-programmed by the framework designers. The returned operators may be reference-oriented operators such as RenameReferenceInSelectClause or DropConstraintReference, identity operators of the form ID(e): Drop(e); then later Create(e) with no semantic change, or DoNothing and HumanDecision placeholders when no safe automatic resolution exists. Before emission, SCHEMORA applies an optimization strategy that “folds multiple reference-oriented ops on the same actionable entity into a single ALTER or CREATE OR REPLACE command,” “prunes redundant identity operators,” and “detects contradictory user choices” (Etien et al., 2024).

SQL patch compilation proceeds in three stages. First, reference-oriented operators are translated into entity-oriented actions by locating the actionable entity in the containment hierarchy. Second, all operations on the same actionable entity are merged into one combined SQL statement. Third, the actionable entities are ordered through an induced dependency DAG $E$9. The patch is emitted in a reverse-topological drop phase followed by a forward-topological create/alter phase, and wrapped in a BEGIN; … ROLLBACK; template for review (Etien et al., 2024).

The evaluation uses the AppSI PostgreSQL database, with “95 tables, 62 views, 20 triggers, 64 stored procedures, 19 trigger functions.” In a replicated real evolution, the “initial DBA patch” contained “19 statements” and required “1 hour of manual work”; SCHEMORA took “7 initial ops → 15 recommendations → single SQL patch,” yielding an “identical schema dump (except a comment)” and a “75 % reduction in composition time (15 minutes).” In a simple re-modularization, “23 ‘MoveView’ operations” had “no secondary impact,” and SCHEMORA generated “23 ALTER VIEWs.” In an advanced re-modularization, “12 tables, 17 views, 6 procs, 11 triggers, 9 trigger functions” were moved; there were “only 3 recommendation choices on stored procedure calls,” and the “generated patch applied without error.” The summarized metrics are “Time saved: 75 %,” “Patch correctness: 100 % functional equivalence on schema,” and “Human interventions: One recommendation choice per impacted stored procedure/view clause on average” (Etien et al., 2024).

A representative use-case is column renaming from person.uid to person.login. SCHEMORA identifies references in view SELECT clauses, WHERE clauses, and procedure bodies, then recommends either alias-preserving or rename-propagating fixes before generating a correctly ordered patch that drops dependent views, updates the function, renames the column, and recreates the views. The paper also states the system’s main limitations: it is “currently limited to PostgreSQL-style metadata and SQL dialect,” “does not handle data migration (CRUD transformations),” and “lacks high-level refactoring operators” (Etien et al., 2024).

4. SCHEMORA as prompt-based schema matching

In the 2025 formulation, SCHEMORA addresses schema matching for heterogeneous data sources (Gungor et al., 18 Jul 2025). The input is a source schema $R \subseteq E \times E$0 with tables and columns, and a target schema $R \subseteq E \times E$1 with tables and columns. The task is to find, for each source column $R \subseteq E \times E$2, a set of target columns $R \subseteq E \times E$3 that are semantically equivalent, explicitly allowing “1:1, 1:n, m:1, and m:n mappings.” The paper emphasizes three challenges: “heterogeneous naming conventions,” “sparse or ambiguous metadata,” and “very large search spaces (exhaustive pairwise is infeasible),” alongside privacy constraints because there is “no raw data” (Gungor et al., 18 Jul 2025).

The stated goals are to “eliminate need for any labeled training data or domain-specific fine-tuning,” to “avoid exhaustive cross-product of columns,” and to achieve “high matching accuracy and scalability via prompted LLMs plus retrieval.” The framework’s key innovations are a purely prompt-based use of off-the-shelf LLMs, a multi-stage pipeline combining LLM-driven metadata enrichment with hybrid retrieval and LLM reranking, and a two-step enrichment strategy that generates multiple enriched names per column rather than relying on “fragile single-string concatenation.” The paper describes this as the “first public, open-source implementation of an LLM-driven schema matcher,” and reports “new state-of-the-art performance” on MIMIC-OMOP with “+7.49% HitRate@5” and “+3.75% HitRate@3” over previous best results (Gungor et al., 18 Jul 2025).

This version of SCHEMORA is therefore not a schema-evolution engine. Its focus is source-to-target semantic correspondence under retrieval constraints, with LLMs operating as metadata generators, table filters, and rerankers rather than as direct exhaustive matchers (Gungor et al., 18 Jul 2025).

5. Retrieval architecture, prompting strategy, and empirical behavior

The schema-matching pipeline has two main phases: indexing the target schema and querying for each source column (Gungor et al., 18 Jul 2025). During indexing, SCHEMORA collects the target table name, table description, original column name, and column description, then runs two enrichment prompts. The first is “context-aware” and uses table and column context to expand the name into a semantically rich phrase; the second is “context-agnostic” and generates synonyms “without reusing any words from the original name or table name.” With hyperparameter $R \subseteq E \times E$4 names per prompt, each column receives up to $R \subseteq E \times E$5 enriched names. These names are cleaned by removing punctuation, replacing digits by spaces, stripping parentheses, and splitting snake_case or CamelCase.

Retrieval is hybrid. Each enriched name is embedded with one of five tested embedding models, including text-embedding-3-large and bge-small-en-v1.5, and indexed with FAISS. In parallel, each enriched name is treated as a small document and indexed with BM25 using the bm25s package with the “lucene” variant. At query time, the source column undergoes the same enrichment. Vector search retrieves the top $R \subseteq E \times E$6 neighbors with cosine similarity at least $R \subseteq E \times E$7,

$R \subseteq E \times E$8

while BM25 retrieves the top $R \subseteq E \times E$9 candidates with score at least $E = T \cup C \cup V \cup P \cup K \cup S \cup X,$0,

$E = T \cup C \cup V \cup P \cup K \cup S \cup X,$1

The two candidate sets are unioned; there is “no direct score-level fusion.” An LLM then performs a table-selection filter, answering “keep” or “drop” for candidate tables based on source and candidate table semantics, after which a final reranking prompt produces the ordered list of candidate IDs (Gungor et al., 18 Jul 2025).

The system uses GPT-4.1-2025-04-14 and GPT-4.1-mini-2025-04-14, with Temperature=0 for all prompts. The main evaluation metric is $E = T \cup C \cup V \cup P \cup K \cup S \cup X,$2:

$E = T \cup C \cup V \cup P \cup K \cup S \cup X,$3

with Recall@K used for partial $E = T \cup C \cup V \cup P \cup K \cup S \cup X,$4 mappings (Gungor et al., 18 Jul 2025).

On MIMIC-OMOP, the “best config” is “GPT-4.1 + text-embedding-3-large + 3 names,” giving “HR@1=54.25%, HR@3=72.55%, HR@5=80.39%.” The reproduced baseline comparison reports: SCHEMORA at 54.25 / 72.55 / 80.39, Matchmaker at 62.20 / 68.80 / 71.10, ReMatch at 42.50 / 63.80 / 72.90, and Needle-in-the-Stack at 23.53 / 45.10 / 62.09 for HR@1/3/5. The ablation study is especially diagnostic. Removing query enrichment reduces performance to 31.58 / 40.79 / 42.11; removing document enrichment to 32.68 / 41.83 / 43.14; removing table selection to 34.64 / 56.21 / 68.63; removing embedding search or BM25 search leaves HR@5=76.47 in both cases (Gungor et al., 18 Jul 2025).

A common misconception is that an LLM alone can reliably rank matches over an entire large schema. The paper argues against that interpretation: “Retrieval-free (Needle-in-the-Stack) is weak,” and “Hybrid retrieval drastically reduces candidate noise and boosts precision before reranking.” On SYNTHEA-OMOP, SCHEMORA reports Recall@1=24.33%, Recall@3=66.23%, and Recall@5=80.82%, compared with 18.10%, 47.63%, and 59.96% for Needle-in-the-Stack. The stated limitations concern storage and computational cost from multiple enriched names and the need to optimize index memory, search latency, and prompt design (Gungor et al., 18 Jul 2025).

6. SCHEMORA as envisioned schema-oriented reasoning and semantic refinement

A third usage appears in the work on agentic schema refinement, where SCHEMORA is “envisioned as an end-to-end pipeline for automated schema understanding, refinement, and data-source integration” (Rissaki et al., 2024). In that account, the agentic view-discovery system can slot into SCHEMORA as the “semantic refinement” stage.

The formal problem begins with a relational schema $E = T \cup C \cup V \cup P \cup K \cup S \cup X,$5, its tables and columns, the foreign-key graph $E = T \cup C \cup V \cup P \cup K \cup S \cup X,$6, and a database instance $E = T \cup C \cup V \cup P \cup K \cup S \cup X,$7. The output is a semantic layer

$E = T \cup C \cup V \cup P \cup K \cup S \cup X,$8

of SQL views, each $E = T \cup C \cup V \cup P \cup K \cup S \cup X,$9 defined by a query $T$0 over $T$1. The goal is to refine $T$2 by “distilling” it into “smaller, semantically coherent, reusable components (views)” that cover as many original columns as possible, preserve or create meaningful column-column relationships, and remain simple enough for interpretability. The paper measures column coverage

$T$3

relation preservation

$T$4

and view width, with median$T$5 intended to stay small, “e.g. $T$6” (Rissaki et al., 2024).

The method is a multi-agent LLM framework with three roles. The Analyst writes SQL queries and suggests candidate views. The Critic reviews whether a proposed view truly refines the query, is minimal, and remains semantically clear. The Verifier executes the views in a live SQL engine to ensure they are syntactically and semantically correct against the database instance. The interaction can be orchestrated “as a single multi-party chat” or as “sequential sessions with memory reuse.” Schema exploration is localized by sampling connected components of the PK–FK graph, guided by a GraphRAG Retriever using embeddings of table and column descriptions. The implementation stack comprises a Schema-graph Indexer, Session Manager, Agent Workers backed by GPT-4 via AutoGen, a SQL Execution Engine (Snowflake or equivalent), a GraphRAG Retriever, and a View Store (Rissaki et al., 2024).

The reported case studies illustrate substantial structural compression. On the “Braze demo database (61 tables, 1 770 columns, 27 601 original relations),” the system “generated 1 146 views covering 80.79 % of columns,” “preserved 54.84 % of original column-column co-occurrences,” “created 7 229 new cross-table relationships,” and achieved “Median view width = 3 vs. median table width = 28.” On “US CMS feeds (113 tables, 6 879 columns, 1 121 976 relations),” it “generated 632 views covering 18.87 % of columns,” “preserved only 0.50 % of original co-occurrences,” “created 1 965 new relationships,” and achieved “Median view width = 4 vs. max table width = 1 130.” The paper explicitly notes that “No direct textual interpretability scores or baseline comparisons are given,” but structural improvement is evidenced by decomposing “wide, cryptic tables” into “small, semantically meaningful views” (Rissaki et al., 2024).

The authors identify three reusable pieces for SCHEMORA: “GraphRAG-based subgraph retrieval,” the “Analyst/Critic/Verifier agent-protocol,” and structural quality measures such as “column coverage, relation preservation, view width.” The orders/staff toy example, where views such as interns and staff_revenue replace a more cumbersome join-and-aggregate query, functions as a concrete illustration of the semantic-layer objective (Rissaki et al., 2024).

7. Reinforcement-learning antecedents and integrative interpretation

A further SCHEMORA lineage is supplied by the MORTAL work, which sketches how its “core ideas—casting schema design as a Markov-Decision Process, driving it with reinforcement learning, and exposing a tight query–feedback loop via an interactive GUI—can be lifted almost ‘as-is’ into a new system called SCHEMORA” (Yuan et al., 2021). Here the current schema under construction is encoded as a fixed-length state vector $T$7, where each micro-table fragment carries features such as model_type, cardinality, avg_size, and joins_so_far. The action space is

$T$8

and the reward balances query latency and storage footprint, for example

$T$9

or symmetrically

$C$0

The proposed workflow is: shred multi-model data into micro-tables, use Double Q-tables to reduce the search explosion to $C$1, inject semantic constraints through a Constraint Pool, execute the workload after each transition, and allow Interactive Steering through an interface that can pause training and manually edit the “best so far” schema. In the “Person-dataset demo (20 episodes),” cumulative workload time drops rapidly over “∼15–20 episodes,” MORTAL “quickly falls well below” a baseline competitor and is “often 2–3× faster,” while storage “typically grows only modestly” (Yuan et al., 2021).

This suggests an integrative reading of SCHEMORA as an umbrella for several automation strata: RL-based schema design, agentic semantic refinement, recommendation-driven schema evolution, and retrieval-augmented schema matching. Across these variants, the shared pattern is not a single algorithm but a staged orchestration of schema-space search, recommendation or ranking, explicit constraints, and grounded validation through dependency graphs, query workloads, or live SQL execution (Yuan et al., 2021, Rissaki et al., 2024, Etien et al., 2024, Gungor et al., 18 Jul 2025).