[Bug]: SQL schema stores redefined properties in several unsynchronised columns #410

What type of issue is this?

• Bug report
• Feature request

Prerequisites

• I have written a descriptive issue title
• I have verified that I am running the latest version of the SysML2.NET
• I have searched open and closed issues to ensure it has not already been reported

Description

SqlSchemaExtensions.QueryStoredOwnProperties (SysML2.NET.CodeGenerator/Extensions/SqlSchemaExtensions.cs:74-77) skips only same-name redefinitions. A redefinition under a new name gets its own column, so one value is stored once per level of the redefinition chain, with nothing keeping the copies equal:

• A Redefinition's target is written to specialization_version.general, subsetting_version.subsetted_feature, redefinition_version.redefined_feature and relationship_target (KerML: redefinedFeature redefines subsettedFeature, which redefines general, which redefines Relationship::target).
• The same applies to nearly every relationship end redefining Relationship::source/target: annotatedElement, memberElement, importedNamespace, Dependency::client/supplier and others.
• requirement_usage_version.req_id and requirement_definition_version.req_id duplicate element_version.declared_short_name (reqId redefines Element::declaredShortName).

The generated DTOs keep one slot per chain: in Redefinition, ISubsetting.SubsettedFeature, ISpecialization.General and IRelationship.Target all read and write RedefinedFeature.

Investigation

The per-kind columns may be deliberate. The §7 banner of schema.generated.sql calls the specialization-graph indexes "load-bearing for derived-property impact analysis": typed reverse indexes per relationship kind (ix_specialization_version_general, ix_namespace_import_version_imported_namespace, ix_annotation_version_annotated_element, …) let the service find the elements a change affects without scanning all of relationship_target.

Analysis to confirm:

• Plausibly justified: one typed column per relationship kind beside the generic relationship_source / relationship_target, for example specialization_version.general/specific, annotation_version.annotated_element, namespace_import_version.imported_namespace.
• Not justified by that reason: the deeper levels of a chain. Every FeatureTyping, Subclassification, Subsetting, Redefinition, ReferenceSubsetting and CrossSubsetting already has a specialization_version row, so ix_specialization_version_general alone covers a walk over the specialization graph. feature_typing_version.type, subclassification_version.superclassifier, subsetting_version.subsetted_feature, redefinition_version.redefined_feature, reference_subsetting_version.referenced_feature, cross_subsetting_version.crossed_feature, conjugated_port_typing_version.conjugated_port_definition and port_conjugation_version.original_port_definition repeat a column higher up the chain.
• Not justified: req_id, which repeats declared_short_name, an input of the derived shortName and qualifiedName.
• Raises the stakes: an impact analysis walking ix_specialization_version_general is complete only if general is written for every subtype. Copies that can disagree make derived values silently stale.
• The DTO argument does not hold: the generator's doc comment says renamed redefinitions get their own storage "exactly as it does in the generated DTOs". The DTOs alias them instead (Membership's IRelationship.Target returns [this.MemberElement]). The API payloads do carry both names (MembershipSerializer writes memberElement and target), but the payload is already stored whole in element_version.stored_json.
• SysML2.NET.CodeGenerator.Tests/Sql/schema.golden.sql:702-703 keeps the Specialization endpoints "stored independently" because they subset Relationship::source/target. In KerML they redefine them (Hypha, knowledge base 2026-08).

Tasks:

• For each duplicated column family, record which consumer needs it (impact analysis, validate_references_*, views, the Query service).
• Decide, per duplicated column, whether it stays as a typed index column or becomes a view alias.
• Record the decision and its reason in schema.golden.sql / SysML2.NET.CodeGenerator.Tests/Sql/SQL.md.

Expected

No two stored copies of one value can disagree.

• Keep the relationship-kind columns the impact analysis needs (specialization_version.general/specific, the conjugation, disjoining, featuring, annotation, membership and import columns), and enforce equality with relationship_source / relationship_target, or stop writing those link rows for relationships whose ends are redefined. Document which copy is authoritative.
• Stop storing the deeper redefinitions; expose them as column aliases in the vw_* views (vw_feature_typing.type = general, and so on).
• Drop req_id; expose it as an alias of declared_short_name.
• Check reference types in validate_references_* per class_kind on the shared columns.
• Correct the doc comment in SqlSchemaExtensions.cs, which says renamed redefinitions get their own storage "exactly as it does in the generated DTOs", and the "subset" wording in schema.golden.sql:702-703.
• Regenerate Expected/UML/AutoGenSql/schema.generated.sql.

Steps to Reproduce

1. Open vw_redefinition in schema.generated.sql: it selects general, specific, subsetted_feature, subsetting_feature, redefined_feature and redefining_feature from four tables.
2. Nothing ties these columns together, so a Redefinition whose redefinition_version.redefined_feature differs from its specialization_version.general would be accepted.

System Configuration

• SysML2.NET version: PR [ADD] Sql schema #344 (feat/sql-schema-and-more, d5a90f2), SysML2.NET.CodeGenerator
• Environment (Operating system, version and so on): Windows 11
• .NET Framework version: .NET 10
• Additional information: metamodel facts checked with Hypha (KerML/SysML knowledge base 2026-08)