System Migrations

The Kinotic platform uses the same migration SQL system as application developers to manage its own internal Elasticsearch indices. This page covers considerations specific to platform development — for general migration syntax, see App Migrations.

What Are System Migrations?

System migrations define and evolve the indices that the platform itself relies on — things like migration history tracking, system configuration, and other internal data stores. They run during platform startup before any application migrations and are scoped to the system project rather than a per-application project.

System migrations live in kinotic-migration/src/main/resources/migrations/ and follow the same V<N>__<description>.sql naming convention.

Using Composite Types in System Indices

OBJECT, NESTED, and UNION columns work the same way in system migrations as in application migrations. Refer to the Composite Column Types section of the app documentation for syntax and examples.

Deserializing Union Types

Elasticsearch stores UNION fields as a flat merged object — it has no knowledge of which variant a document contains. When reading documents back into Java, the platform needs to route deserialization to the correct subclass.

The standard approach is Jackson's polymorphic type handling, driven by a discriminator field stored in the document. This is the same kind (or similar) KEYWORD field you include in each union variant in the migration SQL.

Example

Given this migration:

CREATE TABLE assets (
    id KEYWORD,
    item UNION (
        Book  (kind KEYWORD, title TEXT, isbn KEYWORD),
        Video (kind KEYWORD, title TEXT, duration INTEGER)
    )
);

The corresponding Java model uses @JsonTypeInfo and @JsonSubTypes to map the kind field value to the correct subclass:

@JsonTypeInfo(use = JsonTypeInfo.Id.NAME, property = "kind")
@JsonSubTypes({
    @JsonSubTypes.Type(value = Book.class,  name = "Book"),
    @JsonSubTypes.Type(value = Video.class, name = "Video")
})
public abstract class Item {}

public class Book extends Item {
    public String kind;
    public String title;
    public String isbn;
}

public class Video extends Item {
    public String kind;
    public String title;
    public Integer duration;
}

When inserting documents, always write the discriminator value:

INSERT INTO assets (id, item) VALUES ('a1', '{"kind":"Book","title":"Clean Code","isbn":"978-0132350884"}') WITH REFRESH;

If the kind field is absent from a stored document, Jackson will fail to deserialize it. Ensure every INSERT that targets a UNION field includes the discriminator.

Conflict validation

If two variants declare the same field name with different scalar types — e.g. title TEXT in one variant and title KEYWORD in another — the migration executor will throw an IllegalArgumentException at mapping time. Fields shared across variants must have identical types.

Known limitation: Conflict detection is shallow for composite types. If two variants share a field name with the same composite type (OBJECT, NESTED, or UNION) but different sub-fields, no exception is thrown — only the first variant's sub-field definitions are used and the second's are silently dropped. Until named type support is added to the migration DSL, avoid sharing composite-typed fields across variants. Scalar shared fields (such as a kind KEYWORD discriminator) are always safe.