[Ticket] Migration Strategy for Database Schema Versions

[FriedJannik]

User Story

As a BaSyx User,
I want a controlled business-layer migration mechanism,
so that schema changes caused by metamodel evolution or new queryable fields can be applied safely without breaking existing installations.

This migration mechanism should preserve backward compatibility while allowing additive schema evolution.

---

Rules

What

• Migration logic must be implemented in the Go business layer, not as destructive raw SQL transformations.

• JSONB remains the default storage for non-queryable properties.

• Dedicated database columns may be introduced only if:

  • A property becomes queryable.
  • A property requires indexing.
  • Performance requires column-based storage.

• Deprecated fields must not be removed automatically.

• A full SQL dump must be created before executing migration.

When

Migration is required when:

• New persistent fields are introduced in the metamodel.
• API model changes require additional database fields.
• Nested properties become queryable.
• Schema changes are necessary for performance or indexing.

How

• Migration is executed via:

  1. Database backup (SQL dump).
  2. Retrieval of all entities via repository API (GET).
  3. Reinsertion of entities via repository API (PUT/POST).

• Business-layer validation ensures data consistency.

• Migration runs in a controlled maintenance window.

---

Entry Points

Migration can be triggered via:

• A dedicated migration command (e.g., CLI command).

• A startup version check comparing:

  • Stored schema version (e.g., in a schema_version table).
  • Current application schema version.

• A dedicated migration service inside the Go backend (e.g., MigrationService).

Relevant components:

• Go business layer of the AAS Repository
• PostgreSQL persistence layer
• Repository GET and INSERT operations

---

Acceptance Criteria

• A full SQL dump is created before migration execution.
• All entities are retrieved via repository API.
• All entities are successfully reinserted using new schema logic.
• Deprecated fields remain available in the database.
• Newly queryable properties are stored in dedicated columns where required.
• Migration process is documented.
• Migration can be executed in a controlled and repeatable manner.
• Schema version is tracked (e.g., via a version table).

---

Risks and Assumptions

Risks

• Long runtime for large datasets.
• Increased load during reinsert phase.
• Temporary downtime during migration.
• Partial migration if process is interrupted.
• Indexing overhead when extracting JSONB fields into dedicated columns.

Assumptions

• PostgreSQL is the target database.
• Repository API guarantees model validation.
• Schema changes are primarily additive.
• Migration is executed in a controlled maintenance window.
• No automatic removal of deprecated fields.

---

References and Notes

• JSONB remains the flexible storage strategy for non-queryable nested properties.
• Queryable nested properties (e.g., attributes inside AdministrativeInformation) may be extracted into dedicated columns.
• Migration is intentionally API-driven to ensure validation and consistency through business logic.

---

Dependencies and Blockers

Dependencies

• Stable repository GET and POST/PUT operations.
• Clear schema versioning strategy.
• Defined migration trigger mechanism (manual or automatic).

Blockers

• Undefined schema version tracking.
• Lack of backup strategy for PostgreSQL.