Hightouch

Promptfree, for everyone, and the only version there is

Copy the prompt for Claude CodeCopy the prompt for CodexCopy the prompt for Cursor

Copy

Build me the reverse ETL I actually need instead of Hightouch.

The pattern is simple and the difficulty is entirely in the destinations. **Build it for the two or three destinations you actually use**, and it is a weekend rather than a platform.

STACK
- Node 20+ with Fastify
- SQLite through better-sqlite3, WAL mode, for the sync state
- A worker process
- Caddy in front

THE DATA MODEL
- models: id, name, sql, primary_key, source_ref
- syncs: id, model_id, destination, mapping_json, mode, schedule, is_active — mode is upsert, update-only, or mirror
- rows_state: sync_id, primary_key, row_hash, remote_id, last_synced_at, status — **the whole trick lives here**
- runs: id, sync_id, started_at, finished_at, added, changed, removed, failed
- failures: id, run_id, primary_key, payload_json, error, at

CHANGE DETECTION, THE CORE IDEA
- Run the query, hash each row, compare with the stored hash. Send only what changed
- **This is what makes reverse ETL affordable**: destinations charge by call and rate-limit hard, and sending everything every hour is what breaks a naive version on day one
- A removed primary key means the row left the query result. Decide per sync whether that deletes at the destination or does nothing — and default to doing nothing, because a bad query should not empty your CRM

WRITING TO A DESTINATION SAFELY
- Batch to the destination's own maximum, respect its rate limit, and back off on 429 with the Retry-After header rather than a guess
- **Idempotency on every write**, keyed by the primary key. A retry after a timeout must not create a duplicate contact
- Partial batch failures are the norm: record the failing rows individually and continue. One bad email address must not stop the other four thousand
- Store the remote id when the destination returns one, so the next run updates instead of searching

GUARD RAILS THAT PREVENT DISASTERS
- **A change threshold: if a run would modify more than a set share of the rows, stop and ask.** A query edited at five in the afternoon that suddenly matches everybody is the incident this prevents, and it will happen
- A dry run showing exactly what would be added, changed and removed
- A per-sync daily write cap

OBSERVABILITY
- Every run recorded with counts; a failure list with the payload and the destination's own error text
- Alert on a run failing twice, on a run that syncs zero rows when it normally syncs thousands, and on a rejection rate above a threshold
- **Zero rows is the failure nobody alerts on**, and it looks exactly like success in a log

WHAT MATTERS MOST
Row hashing and the change threshold. The first makes it cheap enough to run; the second stops one wrong query rewriting a whole system.