fix(sqlitestore): move idempotency from separate table to events column #193

New issue

Closed

opened 2026-03-06 20:25:22 +00:00 by ash · 0 comments

ash commented

2026-03-06 20:25:22 +00:00

Owner

Problem

Postgres stores idempotency keys as a column on the events table — atomic, clean, single lookup. SQLite uses a separate idempotency_keys table with comma-separated event IDs — fragile, non-atomic, inconsistent with pgstore.

The idempotency_keys table is always empty unless someone explicitly uses AppendWithOptions. Store parity rule says both backends must behave the same way.

What to do

Add idempotency_key TEXT column to SQLite events table (migration)
Add index: CREATE INDEX idx_events_idempotency_key ON events(stream_type, stream_id, idempotency_key) WHERE idempotency_key IS NOT NULL
Update AppendWithOptions to write idempotency_key to the events row
Update idempotency check to query events table directly (like pgstore)
Drop idempotency_keys table in migration (or leave it for backward compat)
Update conformance tests to verify both backends behave identically

Store parity

Ref: #139

## Problem Postgres stores idempotency keys as a column on the events table — atomic, clean, single lookup. SQLite uses a separate `idempotency_keys` table with comma-separated event IDs — fragile, non-atomic, inconsistent with pgstore. The `idempotency_keys` table is always empty unless someone explicitly uses `AppendWithOptions`. Store parity rule says both backends must behave the same way. ## What to do 1. Add `idempotency_key TEXT` column to SQLite events table (migration) 2. Add index: `CREATE INDEX idx_events_idempotency_key ON events(stream_type, stream_id, idempotency_key) WHERE idempotency_key IS NOT NULL` 3. Update `AppendWithOptions` to write idempotency_key to the events row 4. Update idempotency check to query events table directly (like pgstore) 5. Drop `idempotency_keys` table in migration (or leave it for backward compat) 6. Update conformance tests to verify both backends behave identically ## Store parity Ref: #139