Semantic Change Detection Guide

This guide describes how SQLQueryHelperjs should reason about schema changes that are not purely syntactic.

Why Semantic Detection Matters

The hardest schema decisions are usually not:

• create table
• add column
• drop index

The hard cases are:

• was this column renamed or deleted?
• is this type change equivalent enough to avoid a rebuild?
• is this default change meaningful or cosmetic?
• is this index actually different or only reordered textually?

Without semantic detection, the runtime becomes too eager to rebuild and too noisy to trust.

First-Class Goals

Semantic change detection should aim to:

• reduce unnecessary destructive rebuilds
• surface rename candidates explicitly
• preserve safety when equivalence is uncertain
• remain explainable in plans, artifacts, and runtime events

This is not a place for hidden magic. Any semantic decision should stay reviewable.

Recommended Detection Categories

The first useful categories are:

• exact-match
• equivalent-change
• rename-hint
• destructive-change
• unknown-risk

These categories are more operationally useful than raw “changed / not changed” flags.

Example Cases

Exact Match

No material difference exists between the current database object and the requested model.

Result:

• no action
• no warning

Equivalent Change

The representation changed, but operational semantics are likely the same.

Examples:

• index metadata reordered without real column change
• casing or formatting differences that do not change behavior
• dialect-normalized defaults that render differently but evaluate the same

Result:

• prefer no-op or narrow alter
• explain the equivalence in plan or trace output when useful

Rename Hint

The runtime sees evidence that a drop-plus-add may really be a rename.

Examples:

• case-only column rename
• column removed and another added with similar type, nullability, and position

Result:

• do not auto-claim safety beyond what the engine can prove
• emit a rename hint for review
• keep destructive risk visible unless explicitly approved or modeled safely

When the rename is intentional, approve it explicitly with runtime configuration such as approvedColumnRenames: [{ table, from, to }]. That keeps the operation reviewable while allowing the rebuild copy step to preserve data.

Destructive Change

The change clearly requires rebuild or destructive action.

Examples:

• column removal with no safe mapping
• incompatible key change
• dependency-impacting object replacement

Result:

• keep the destructive reason explicit
• require policy checks and approvals as configured

Unknown Risk

The runtime cannot safely classify the change as equivalent or destructive with confidence.

Result:

• prefer explicit review over optimistic automation
• surface uncertainty in artifacts and observability events

Recommended Rule Order

Semantic detection should prefer this order:

1. exact match
2. safe equivalence
3. rename hint
4. destructive classification
5. unknown-risk fallback when certainty is too low

This order keeps the runtime conservative without being needlessly destructive.

Relationship To Plan Artifacts

Plan artifacts should expose semantic signals directly.

• equivalent changes should not look like destructive noise
• rename hints should appear as hints, not hidden assumptions
• unknown-risk cases should be visible to reviewers

That keeps semantic reasoning debuggable outside runtime code.

Relationship To Observability

Observability events should capture semantic outcomes, not only final actions.

Recommended future event fields:

• semanticCategory
• renameHintCount
• equivalenceNotes
• uncertaintyReason

That will make it possible to explain why a rebuild was avoided, chosen, or deferred.

Engine-Specific Expectations

Semantic detection will not be identical across engines.

• SQLite already has a real case-only rename approval shape
• PostgreSQL can usually support the deepest object and dependency reasoning
• MySQL must stay careful where native behavior is narrower or emulated

The detection model should be cross-engine, but the confidence level may differ by engine.

Minimum First Version

The minimum useful first version should provide:

• explicit rename hints
• explicit equivalence detection for obvious non-destructive cases
• clear fallback to destructive or unknown-risk states
• traceability through plan artifacts and observability events

That is enough to improve trust without pretending to solve every schema-diff problem.

Future Extensions

Useful later expansions would be:

• confidence scoring for rename hints
• richer equivalence rules for defaults and generated columns
• object-level semantic detection for views, routines, and triggers
• policy hooks that react differently to unknown-risk versus confirmed destructive changes