etl-sync-job-builder

Store a watermark timestamp in a SyncWatermark table, query only records where updated_at is greater than the last watermark, process them in batches of 100, then upsert the watermark to the latest record's timestamp. This avoids full table scans on every run.

Use upsert operations keyed on the record's primary ID instead of plain inserts. In PostgreSQL, batch upserts with INSERT ... ON CONFLICT (id) DO UPDATE make re-running the same job safe, and Prisma's createMany with skipDuplicates also prevents duplicate rows.

Prisma can execute raw SQL to create a logical replication publication, but subscribing to changes requires the pg library directly. Use client.query('LISTEN channel') and handle notification events to sync inserted or updated rows in near real time.

Save a checkpoint after every 100 processed records and log individual failed records without aborting the job. On restart, the job reads the last checkpoint and resumes from that point, while transient errors are handled with exponential backoff retries.

Common strategies are source-wins, destination-wins, latest-wins, and merge. Latest-wins compares updated_at timestamps and keeps the newer record, while merge combines non-null fields from the source into the destination record.

Use node-cron to schedule jobs with standard cron expressions, such as '0 * * * *' for hourly runs or '0 2 * * *' for a nightly full sync at 2 AM. Each scheduled trigger instantiates and runs the ETL job class.