MemberJunction / MemberJunction/MJ

Sync engine: no lookback overlap for MonotonicWatermark connectors (late-committing rows can be permanently missed)

Open
#3,085 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
2d 1h
Merged PRs (30d)
323

Description

## Problem

`BaseExternalDataSourceConnector` (added in #3070) declares `MonotonicWatermark = true` and fetches strictly in watermark order, using the last batch's max watermark as the narrowing bound for the next incremental sync (`WHERE watermark >= NewWatermarkValue`).

This is correct for the common case, but has a data-completeness gap: a row committed **mid-run**, whose transaction-commit watermark ends up **below** `NewWatermarkValue` (a long-running transaction that started before the sync window, or clock skew between the DB server and the watermark column's source), will never be picked up by a subsequent incremental — the next sync's lower bound has already advanced past it.

Confirmed via code search (`packages/Integration/engine/src/IntegrationEngine.ts`, `WatermarkService.ts`, `BaseIntegrationConnector.ts`): there is no lookback-overlap mechanism anywhere in the sync engine today. `MonotonicWatermark=true` connectors narrow strictly to the last-seen high-water mark with no configurable overlap window.

## Proposed fix

Add a configurable lookback window (e.g. `Integration.WatermarkLookbackSeconds` or a per-connector override) that the engine subtracts from the narrowing bound on the next incremental — so a sync re-scans a small overlap window and re-processes (idempotently, since ingestion is upsert-by-`ExternalID`) any records that committed with a lagging watermark.

## Origin

Raised by @rkihm-BC in review of #3070: https://github.com/MemberJunction/MJ/pull/3070#pullrequestreview- (approval comment, "One gentle pushback on the watermark-lookback deferral").

Not a blocker for #3070 (which introduced the EDS-consuming ingestion connectors this affects) — this is a pre-existing gap in the shared sync engine that predates and extends beyond that PR's scope.

Contributor guide

Open the contributing guide

Research direction

Start with packages/Integration/engine/src/IntegrationEngine.ts, WatermarkService.ts, and BaseIntegrationConnector.ts to trace how the last batch's max watermark becomes the next incremental bound. Review the proposed global or per-connector configuration, then verify that a sync re-scans the configured overlap and can re-process late-committing rows idempotently.

Written by the indexing model from the issue text.

Assessment

Tech stack
typescript
Domain
backend, data-engineering
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.