[Bug] Journal appends stamp the event timestamp before the commit while the by-tag `Offset` is ordered by it, so the events of a slow-committing writer land behind the projection cursor and are dropped permanently

[pathosDev]

Problem

A by-tag projection's cursor is an Offset ordered on (timestamp, persistenceId, sequenceNr), and timestamp is wall-clock time captured before the write commits. Every journal does it the same way: RelationalJournal.append reads Date.now() on the line above pool.withTransaction, MongoJournal.append on the line above readHead, CassandraJournal.append before it claims the sequence range. The row becomes visible at commit; the value it carries was fixed at call time.

So the visibility order and the offset order are two different orders, and nothing reconciles them. Two writers overlap:

1. Writer A calls append at t=1000. Its transaction is slow — a lock wait, a fsync, a retried round-trip.
2. Writer B calls append at t=1005 and commits immediately.
3. The projection polls, sees only B, hands it to the handler, and commits the cursor (1005, 'order-b', 1) to the OffsetStore.
4. A commits. Its row carries timestamp = 1000.

A is now behind the cursor forever. Every indexed backend pre-filters on timestamp >= fromOffset.timestamp in storage — SQLite (t.timestamp >= ?), Cassandra (WHERE tag = ? AND timestamp >= ?), MongoDB (timestamp: { $gte: fromOffset.timestamp }) — so A's row is not even fetched. On the paths that do fetch it (InMemoryQuery, and every backend that has no query implementation and falls back to it — see #532), refineTaggedRows drops it with if (offsetCompare(offset, fromOffset) < 0) continue. Not on the next poll, not on the poll after that, not after a restart: the OffsetStore holds the advanced cursor, and a restart resumes from it.

There is no eventual-consistency delay, no watermark, no offset backtracking and no dedup-by-(persistenceId, sequenceNr) anywhere in the query layer or in ProjectionActor. The window is exactly the spread between two concurrent appends' commit latencies, which under contention is the normal case rather than the pathological one — and on a multi-node deployment it is also the clock skew between writers, which nothing bounds.

The docs state the opposite guarantee, in both languages.

Evidence

The timestamp is fixed before the transaction opens:

src/persistence/relational/RelationalJournal.ts:101-124
  async append<E>(
    persistenceId: string,
    events: ReadonlyArray<E>,
    expectedSeq: number,
    tags?: ReadonlyArray<string>,
  ): Promise<PersistentEvent<E>[]> {
    if (events.length === 0) return [];
    assertValidTags(tags);
    const pool = await this.ensureOpen();
    const now = Date.now();
    try {
      return await pool.withTransaction(async (transaction) => {
        const actualSeq = await this.readHead(transaction, persistenceId);
        if (actualSeq !== expectedSeq) {
          throw new JournalConcurrencyError(persistenceId, expectedSeq, actualSeq);
        }
        const written: PersistentEvent<E>[] = [];
        const tagString = tags && tags.length ? tags.join(',') : null;
        let seq = actualSeq;
        for (const event of events) {
          seq++;
          await transaction.query(this.statements.insertEvent, [
            persistenceId, seq, encodePayload(event, this.serializer), tagString, now,
          ]);

That value becomes the primary component of the cursor:

src/persistence/query/PersistenceQuery.ts:105-116
export type Offset = {
  /** Wall-clock time of the event's persist call. */
  readonly timestamp: number;
  /**
   * Tiebreaker when two events share `timestamp`.  Set to the empty
   * string for the "from-the-beginning" sentinel; the comparator
   * treats `''` as "before any real persistence id".
   */
  readonly persistenceId: string;
  /** Tiebreaker within a persistence id when timestamps collide. */
  readonly sequenceNr: number;
};

The storage-level pre-filter excludes the late-committing row outright:

src/persistence/query/SqliteQuery.ts:113-123
      fetchByTag: db.prepare(
        // Walk the tags-table PK range for the given tag, JOIN to
        // events to fetch payload + CSV tags column.  ORDER matches
        // the index PK so SQLite doesn't have to sort separately.
        `SELECT e.persistence_id, e.sequence_nr, e.payload, e.tags, e.timestamp
           FROM ${tagsTable} t
           JOIN ${eventsTable} e
             ON e.persistence_id = t.persistence_id AND e.sequence_nr = t.sequence_nr
          WHERE t.tag = ? AND t.timestamp >= ?
          ORDER BY t.timestamp ASC, t.persistence_id ASC, t.sequence_nr ASC`,
      ) as TagStmts['fetchByTag'],

and the shared JS refinement drops it on every path that does fetch it:

src/persistence/query/PersistenceQuery.ts:284-299
export function refineTaggedRows<Row, E>(
  rows: ReadonlyArray<Row>,
  fromOffset: Offset,
  mapMatching: (row: Row) => PersistentEvent<E> | null,
): TaggedEvent<E>[] {
  const refined: TaggedEvent<E>[] = [];
  for (const row of rows) {
    const event = mapMatching(row);
    if (event === null) continue;
    const offset = offsetOfEvent(event);
    if (offsetCompare(offset, fromOffset) < 0) continue;
    refined.push({ event, offset });
  }
  refined.sort((a, b) => offsetCompare(a.offset, b.offset));
  return refined;
}

The cursor is committed per event and saved durably, so the drop survives a restart:

src/persistence/projection/ProjectionActor.ts:123-140
  protected async runOnce(): Promise<void> {
    const events: TaggedEvent<E>[] = await this.config.query.currentEventsByTag<E>(
      this.config.tag, this.cursor,
    );
    for (const te of events) {
      // Skip the event we already committed last round (the cursor
      // is inclusive on load to support fresh-start replay, but on
      // subsequent rounds we want strictly-after).
      if (te.offset.timestamp === this.cursor.timestamp
        && te.offset.persistenceId === this.cursor.persistenceId
        && te.offset.sequenceNr === this.cursor.sequenceNr) continue;
      this.currentHandle = Promise.resolve(this.config.handle(te.event));
      await this.currentHandle;
      this.cursor = te.offset;
      await this.offsetStore.saveOffset(this.config.name, this.cursorKey, this.cursor);
      if (this.stopped) return;
    }
  }

The documentation promises what the code cannot deliver:

docs/src/content/docs/persistence/projections.mdx:98-101
If the handler runs but the cursor isn't persisted, the projection
**re-processes the same event** on restart.  This is **at-least-once
delivery** — the framework guarantees no event is missed, but
duplicates are possible.

The German mirror carries the same sentence at docs/src/content/docs/de/persistence/projections.mdx:100-104 ("das Framework garantiert, dass kein Event verpasst wird"). At-least-once is what the framework claims; what it implements is at-most-once with an unbounded loss window, and the loss is silent — no log line, no counter, nothing to alert on.

Proposal

The cursor needs an ordering that is assigned by the same authority that decides visibility. Two workable shapes, in order of preference:

• A commit-assigned monotonic ordering column. Give the events table a journal-assigned ordering (BIGSERIAL / IDENTITY / a per-journal counter) written inside the same transaction, and make Offset carry it as the primary component with the current triple kept as the tiebreaker for backends that cannot supply one. This is the only variant that is actually correct rather than probabilistically correct, and it is the approach relational event stores generally take.
• Where a backend cannot assign one (Cassandra, DynamoDB, object storage): a watermark plus backtracking. Never advance the committed cursor past now - eventualConsistencyDelayMs, and periodically re-scan a trailing window from cursor - backtrackWindowMs, deduping delivered events by (persistenceId, sequenceNr). Both knobs belong in ProjectionOptions and in reference.conf via ConfigKeys.

Until one of the two lands, the docs sentence in both languages must be corrected — "no event is missed" is not a guarantee this layer provides.

Acceptance sketch

• Two concurrent appends whose commit order is the reverse of their Date.now() order both reach a by-tag projection handler.
• The guarantee holds across a projection restart (cursor reloaded from a DurableStateOffsetStore).
• A contract test in the cross-backend suite stages the skew and asserts delivery for every backend that ships a PersistenceQuery.
• Duplicates produced by a backtracking window are deduped by (persistenceId, sequenceNr) before the handler sees them, or the handler contract explicitly says they are not.
• docs/.../persistence/projections.mdx and its German mirror state the actual delivery guarantee.

Reference issues: #532 and #391 are why the exposure is wider than it looks — 6 of the 10 journal backends have no PersistenceQuery and fall back to InMemoryQuery, which drops the same event through offsetCompare instead of through SQL. #650 covers what happens when a projection handler fails; this is the case where the handler is never called at all. #199 and #156 are pagination ergonomics on the same layer and do not touch offset semantics.

Verification status

Found in the ten-lens production-readiness review of 2026-08-05 (v0.13.0) and re-verified before filing: reproduced by execution. A stub Journal that reveals rows in commit order while carrying their call-time timestamps, driven through InMemoryQuery + a real ProjectionActor.byTag on a live ActorSystem:

after B is visible, delivered = ["B-stamped-1005"]
after A is visible,  delivered = ["B-stamped-1005"]
journal really does hold both events: ["A-stamped-1000"] ["B-stamped-1005"]
currentEventsByTag(from committed cursor) = ["B-stamped-1005"]

The event is in the journal, it matches the tag, and the query will never return it again from the committed cursor. The stub only controls visibility order; the timestamp skew it replays is the one RelationalJournal.append:110 produces on its own.

Part of the production-readiness review batch — tracked in #913.