Census

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 warehouse-to-tools sync I actually need instead of Census.

Same engine as any reverse ETL, and the idea worth taking from this one is **defining the entity once and sending it to several places**, rather than writing a query per destination.

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

THE ENTITY LAYER, WHICH IS THE POINT
- entities: id, name, sql, primary_key, fields_json — one definition of 'customer', 'account', 'subscription'
- audiences: id, entity_id, name, predicate_sql — a filtered slice, defined once
- syncs: id, audience_id, destination, mapping_json, mode, schedule
- **A field renamed in one place propagates everywhere.** Without this you have twelve nearly identical queries and one of them is always out of date

SYNC MECHANICS
- rows_state with a row hash per primary key; send only what changed
- Batching to the destination's maximum, its rate limit respected, back off on Retry-After
- Idempotent writes keyed by primary key, remote ids stored
- Partial batch failures recorded per row and skipped, never aborting the batch

MAPPING THAT DOES NOT SILENTLY CORRUPT
- Fetch the destination's own schema and validate the mapping against it before the first run: types, lengths, picklist values, required fields
- **A string longer than the destination allows is truncated silently by many APIs.** Check lengths yourself and fail loudly instead
- Nulls: decide explicitly per field whether null clears the destination value or leaves it alone. Getting this wrong wipes fields your sales team filled in by hand, and they will notice

GUARD RAILS
- A change threshold per run, and a dry run showing adds, changes and removes with sample rows
- Deletes disabled by default. A query returning nothing must never be interpreted as 'remove everybody'
- A per-sync daily write cap

OBSERVABILITY
- Runs with counts, failures with payloads and destination errors
- Alert on repeated failure, on a rejection rate rising, and on a run that suddenly syncs zero rows
- A per-field freshness view: when did this field last successfully update at this destination. **That is the question an operations team asks and no log answers**

WHAT MATTERS MOST
The shared entity definition and explicit null handling. One stops the queries diverging; the other stops the sync quietly deleting other people's work.