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.

Data Streams

System data that is append-only and time-series — audit trails, events, telemetry — should be backed by an Elasticsearch data stream rather than a regular index, so old data ages out and backing indices roll over automatically. Create one from a migration with CREATE DATA STREAM:

CREATE DATA STREAM system_audit (
    actor  KEYWORD,
    action KEYWORD,
    detail TEXT
) WITH (DATA_RETENTION = '90d') ;

DATA_RETENTION is the native data stream lifecycle — Elasticsearch deletes data past the period (measured per backing index from rollover, not per document). See the Migration SQL Grammar for the full statement and the exact retention semantics.

Every document needs a @timestamp — and Java needs ceremony for it

Elasticsearch requires every document written to a data stream to carry a @timestamp date field, and rejects any that don't with data stream timestamp field [@timestamp] is missing. The migration adds @timestamp to the mapping automatically — you neither declare it as a column nor can you, since @timestamp is not a legal column identifier.

The catch is on the write side: @timestamp is also not a legal Java field name, so a system DTO cannot carry it directly. Bridge it with Jackson's @JsonProperty — the ElasticsearchAsyncClient serializes through a Jackson mapper, so the annotation is honored on both write and read:

public class AuditEvent {
    private String  id;

    @JsonProperty("@timestamp")      // Java field `timestamp` ⇄ Elasticsearch field `@timestamp`
    private Instant timestamp;

    private String  actor;
    private String  action;
}

This renames the field — the stored document has only @timestamp, so any query or sort must use that literal name (timestamp does not exist in the index). For most internal stream DTOs this is the simplest correct choice.

If you instead want a friendly, separately-queryable field name alongside @timestamp, keep your own field and add a serialize-only companion:

@JsonProperty("eventTime")           // stays queryable as eventTime
private Instant eventTime;

// emitted as @timestamp on write, ignored on read (eventTime is restored from its own key)
@JsonProperty(value = "@timestamp", access = JsonProperty.Access.READ_ONLY)
public Instant esTimestamp() { return eventTime; }

This duplicates the value (both eventTime and @timestamp are stored). It is the same document shape that application entities get from the @TimeReference decorator. When a migration creates the stream backing such an entity, declare the pairing so the stream documents which column is the natural time field:

CREATE DATA STREAM sensor_readings (
    id        KEYWORD,
    eventTime DATE,
    value     DOUBLE
) WITH (DATA_RETENTION = '30d', TIME_REFERENCE = 'eventTime') ;

TIME_REFERENCE must name a declared DATE column; the migration validates this and fails fast otherwise. It declares intent and pairs with the entity's @TimeReference — the write-time duplication into @timestamp is performed by the persistence layer, not the migration.

Writing and reading

Data streams are append-only: Elasticsearch accepts only the create op-type and rejects the index op and by-id updates/deletes. Documents are retrieved by search/query, not by id. System services that write to a stream must therefore use a create (append) write, and read through search rather than a find-by-id lookup.