Background

The JDBC Chat Memory repository currently uses a timestamp column to maintain message ordering. However, this column is semantically misleading—it's not actually storing temporal data but rather functioning as a sequence ID to preserve message order. As @MarkPollack https://github.com/spring-projects/spring-ai/pull/3154#issuecomment-2884724357, the field should have been renamed from its original implementation, and dropping the timestamp column entirely should be considered for future versions.

This has caused some confusion and technical issues:

  1. Misleading semantics: The column name suggests temporal data, but it's actually an ordering sequence
  2. Precision issues: Different databases have different default timestamp precisions, leading to ordering bugs on MySQL/MariaDB (see #3442)

Current State (1.0.x / 1.1.x)

We've implemented a non-breaking fix in #4739 that uses second-level granularity in the Java code. This solves the immediate ordering issue across all databases without requiring schema changes.

Proposed Change for 2.0

Since 2.0 is a major release where breaking changes are acceptable, we should redesign the schema to make the intent explicit:

Current schema: timestamp TIMESTAMP NOT NULL

Proposed schema: sequence_id BIGINT NOT NULL

Benefits

  • Clearer intent: Column name matches its actual purpose
  • Consistent behavior: Works identically across all databases
  • Simpler implementation: Direct setLong() instead of timestamp conversions
  • Future-proof: No range limitations or precision concerns

Implementation Tasks

  • Update all schema files (MySQL, MariaDB, PostgreSQL, SQL Server, HSQLDB, SQLite, Oracle)
  • Update JdbcChatMemoryRepository to use setLong() instead of setTimestamp()
  • Update dialect classes with new column name
  • Create migration scripts for supported databases
  • Document the breaking change in migration guide

Migration Impact

This is a breaking schema change. Users will need to either run migration scripts or recreate the table. Since chat memory typically stores recent conversation context rather than permanent historical data, recreation is often acceptable.

Related Discussions

  • Original issue: #3442 - MySQL/MariaDB message ordering bug
  • Schema change attempt: #3443 - Proposed TIMESTAMP(3) approach (closed)
  • Code fix for 1.1.x: #4739 - Non-breaking second-level granularity fix
  • Background context: #3154 - some related discussions about sequence ID