Session rows can collapse across different session IDs when creation timestamps collide

Read packages/db/src/code-migrations/8-order-keys.ts around the session sorting key and packages/core/src/buffers/session-buffer.ts around extendSession. Start by checking how the migration defines the session key and how update rows preserve the old key; the issue includes a ClickHouse SQL reproduction to guide regression coverage. Done means a reviewed migration and tests show separate sessions remain correct through updates, FINAL, and physical merges.

Summary

The current sessions sorting key omits the session ID:

ENGINE = VersionedCollapsingMergeTree(sign, version)
PARTITION BY toYYYYMM(created_at)
ORDER BY (project_id, toDate(created_at), created_at)

If two different sessions in the same project share the same millisecond creation timestamp, rows with the same version have the same collapse identity. Updating one session can cancel the other session's row. We reproduced a wrong final result after both sessions had been updated, and it remained wrong after OPTIMIZE ... FINAL.

Versions / code

• OpenPanel main 7f6f7f9e2b978bd0472807f586d65928f793d08c (includes #560).
• ClickHouse 25.10.2.65, standalone.
• Session schema migration.
• Session update implementation: extendSession copies the existing row with sign=-1, increments the version, and writes the new positive row. This happens while ingesting ordinary events; clients do not need to issue explicit SQL updates.

The tested sorting key matches the migrated schema. The reproduction below is an engine-level test using that sorting key, rather than a full SDK regression test. No claim is made that #560 introduced this schema issue.

Reproduction

Use an isolated ClickHouse instance/database. This script creates and finally drops its own diagnostic database. original uses the current sorting key; with_id adds id. Each table receives the same input directly (the control is not copied from already-collapsed data).

1. Create A and B with identical project/time but distinct session IDs.
2. Update both to version 2: both implementations return correct results under FINAL.
3. Update A again to version 3; B legitimately remains at version 2.
4. Query with FINAL and force a physical merge.

CREATE DATABASE session_both_updated_check;
CREATE TABLE session_both_updated_check.original (id String, project_id String, created_at DateTime64(3), sign Int8, version UInt64, event_count Int32, revenue Int64) ENGINE=VersionedCollapsingMergeTree(sign,version) PARTITION BY toYYYYMM(created_at) ORDER BY(project_id,toDate(created_at),created_at);
CREATE TABLE session_both_updated_check.with_id (id String, project_id String, created_at DateTime64(3), sign Int8, version UInt64, event_count Int32, revenue Int64) ENGINE=VersionedCollapsingMergeTree(sign,version) PARTITION BY toYYYYMM(created_at) ORDER BY(project_id,toDate(created_at),created_at,id);
INSERT INTO session_both_updated_check.original VALUES ('A','p','2026-10-20 00:00:00.001',1,1,1,10),('B','p','2026-10-20 00:00:00.001',1,1,1,100),('A','p','2026-10-20 00:00:00.001',-1,1,1,10),('A','p','2026-10-20 00:00:00.001',1,2,2,20),('B','p','2026-10-20 00:00:00.001',-1,1,1,100),('B','p','2026-10-20 00:00:00.001',1,2,2,200);
INSERT INTO session_both_updated_check.with_id VALUES ('A','p','2026-10-20 00:00:00.001',1,1,1,10),('B','p','2026-10-20 00:00:00.001',1,1,1,100),('A','p','2026-10-20 00:00:00.001',-1,1,1,10),('A','p','2026-10-20 00:00:00.001',1,2,2,20),('B','p','2026-10-20 00:00:00.001',-1,1,1,100),('B','p','2026-10-20 00:00:00.001',1,2,2,200);
SELECT 'both_v2' scenario,'original' variant,id,version,sign,event_count,revenue FROM session_both_updated_check.original FINAL ORDER BY id,version FORMAT JSONEachRow;
SELECT 'both_v2' scenario,'with_id' variant,id,version,sign,event_count,revenue FROM session_both_updated_check.with_id FINAL ORDER BY id,version FORMAT JSONEachRow;
INSERT INTO session_both_updated_check.original VALUES ('A','p','2026-10-20 00:00:00.001',-1,2,2,20),('A','p','2026-10-20 00:00:00.001',1,3,3,30);
INSERT INTO session_both_updated_check.with_id VALUES ('A','p','2026-10-20 00:00:00.001',-1,2,2,20),('A','p','2026-10-20 00:00:00.001',1,3,3,30);
SELECT 'a_v3_b_v2' scenario,'original' variant,id,version,sign,event_count,revenue FROM session_both_updated_check.original FINAL ORDER BY id,version FORMAT JSONEachRow;
SELECT 'a_v3_b_v2' scenario,'with_id' variant,id,version,sign,event_count,revenue FROM session_both_updated_check.with_id FINAL ORDER BY id,version FORMAT JSONEachRow;
OPTIMIZE TABLE session_both_updated_check.original FINAL;
OPTIMIZE TABLE session_both_updated_check.with_id FINAL;
SELECT 'after_physical_merge' scenario,'original' variant,id,version,sign,event_count,revenue FROM session_both_updated_check.original ORDER BY id,version FORMAT JSONEachRow;
SELECT 'after_physical_merge' scenario,'with_id' variant,id,version,sign,event_count,revenue FROM session_both_updated_check.with_id ORDER BY id,version FORMAT JSONEachRow;
DROP DATABASE session_both_updated_check;

Actual versus expected

Stage	Original key	Key with id
Both reach v2	A(v2), B(v2) — correct	A(v2), B(v2) — correct
A reaches v3, B remains v2; SELECT ... FINAL	A(v2), A(v3); B missing	A(v3), B(v2)
After OPTIMIZE TABLE ... FINAL	A(v2), A(v3); B still missing	A(v3), B(v2)

The final expected revenue values are A=30 and B=200. The wrong original result retains A=20 and A=30 instead. Thus even the total row count and total event_count can look correct while session attribution and other metrics are wrong.

This is not an ID collision: IDs A and B are different. Nor is the demonstrated unequal-version result fixed simply by waiting for a merge.

Important qualification: an earlier synthetic run with all sessions ending at the same version produced incorrect per-ID signed aggregates without FINAL, but reconciled correctly with FINAL. We do not use that equal-version run as evidence of permanent loss. The unequal-final-version SQL above is the evidence for this issue.

Candidate correction / questions

Adding id to the sorting key fixes this reproduction:

ORDER BY (project_id, toDate(created_at), created_at, id)

Separate test tables using this key also passed event/session/profile reconciliation in 100k and 1m ingestion/replay tests. This is a candidate correction, not a completed production migration or proof covering every lifecycle edge case.

Migration and query performance need review. Affected historical rows already physically collapsed across IDs cannot be reconstructed merely by changing the sorting key; they may require rebuilding from retained source data.

Regarding late events: created_at can move earlier, but the implementation preserves the old timestamp in the cancellation row. A separate engine-level test using the candidate key successfully moved a session across a day and across a month/partition, under both FINAL and physical merge. We therefore do not claim that changing created_at is itself another bug, or that freezing business time is required. Correctly retaining the old sorting/partition key in the negative row is essential; application-level regression coverage for this path would still be useful.

Suggested regression cases: equal versus unequal final versions at identical timestamps, interleaved updates across batches, physical merge, and late-event moves across partition boundaries. This issue is independent of Kafka rebalance/redelivery duplicates.