Replace `timestamp` column with `sequence_id` in JDBC chat memory schema

[sobychacko]

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 #3154 (comment), 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 MySQL/MariaDB schema TIMESTAMP precision causes message ordering issues in JdbcChatMemoryRepository #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: MySQL/MariaDB schema TIMESTAMP precision causes message ordering issues in JdbcChatMemoryRepository #3442 - MySQL/MariaDB message ordering bug
• Schema change attempt: Fix: MySQL/MariaDB TIMESTAMP precision for message ordering (#3442) #3443 - Proposed TIMESTAMP(3) approach (closed)
• Code fix for 1.1.x: GH-3442: Fix MySQL/MariaDB message ordering in JdbcChatMemoryRepository #4739 - Non-breaking second-level granularity fix
• Background context: Fixed the issue with executing the saveAll method in JdbcChatMemoryRepository #3154 - some related discussions about sequence ID

[saranshbansal]

Apart from that, there is no easy way to get that information back via code and display it to clients ex. showing a list of old messages on screen in chronological order with timestamp information.

Unless I fire a manual query each time against spring_ai_chat_memory table which is a performance killer. Or create a manual chat metadata table which is simply overkill.

Please correct me if I'm wrong.