Reconciled every run, on our own books

The problem

Agency pipeline data decays quietly. Duplicate contacts, stages that mean different things to different people, automations fighting each other — until the exec stops believing the number on the screen. We sell the fix for exactly this, so the first system it had to work on was ours. A CRM system we recommend to clients had to survive being run on ourselves first.

The economics make it sharper. The CRM discipline most agencies need — agreed definitions, dedup, lifecycle rules, reporting you can defend — is usually framed as requiring a paid platform tier. The bet this build tests: the features that actually matter are API-side — automatable on HubSpot's free tier with n8n, and verifiable with a reconciliation report. The repo's own README states it as the project's whole claim: run real RevOps automation against HubSpot's API with n8n instead of buying the tier — and show the reconciliation report.

What we did

Wrote the definitions register — pipeline stages, lifecycle states, ownership and dedup keys — before the first sync ran, because metrics defined after data exists are unrecoverable. Both the register (docs/01) and the metric formulas (docs/02) were founder-ratified on 2026-08-06 and now change only by written amendment in the decision log. Then four n8n workflows (self-hosted, version 2.31.7 digest-pinned in Docker): ingest-and-validate on a manual trigger, dedup-and-upsert with explicit merge rules, register-driven stage transitions with a sprint handoff, and an enrichment-triage sweep that can only write to a human review queue. HubSpot Free is the CRM of record; a SQLite ledger in WAL mode is the audit trail. Every processed item appends an immutable log line; nothing updates without a dedup verdict; anything that fails validation or the API lands in a dead-letter path with the reason attached; and a reconcile command compares a full read of the CRM against the register's seven rules and prints the drift report we would hand a client.

1. The definitions register was written before the first sync ran.
2. Workflow one: scheduled ingest.
3. Workflow two: dedup-and-upsert with explicit merge rules.
4. Workflow three: register-driven stage transitions, with sprint handoff.
5. Workflow four: the ops sweep.
6. HubSpot rejects land in the dead-letter queue with the reason attached.
7. Every sync appends immutable log lines to the SQLite WAL ledger.
8. Reconcile compares ledger against HubSpot and prints the drift report.

The four workflows

• P2 · 01 ingest-validate (13 nodes). Manual and API-triggered on purpose — inbound data is validated against the register before anything else can touch it: malformed items are quarantined, valid items are handed to the dedup workflow. Its production schedule ships disabled in the export.
• P2 · 02 dedup-upsert (20 nodes). Checks dedup keys D1–D4 before any create. On a match it patches only fields empty on the remote record — update-missing-only, never overwriting a filled field — and a complete remote record produces an empty patch, which skips the API call entirely.
• P2 · 03 lifecycle-automation (15 nodes). A 15-minute sweep (schedule ships disabled until production go-live) that applies the register's lifecycle transitions verbatim, creates follow-up tasks, and opens the sprint deal when a contact qualifies.
• P2 · 04 enrichment-triage (16 nodes). A 6-hour sweep over borderline-fit contacts that drafts a suggested verdict and a ≤40-word evidence note — and is structurally unable to apply it. Output lands in a review queue; only a human can accept, edit, or reject.

The write path, honestly

n8n 2.x ships no first-party SQLite node — verified before building on the assumption, not after. So each workflow appends JSONL lines to an inbox directory via the Execute Command node, and a Node script sweeps them into the ledger: schema-validated on entry, quarantined to a .bad file with the reason when a line violates the contract. The quarantine is not a theory — the repo carries a real .bad file from a demo run where one of two demo lines carried an object type outside the contract and was rejected. A community SQLite node existed and was declined by default: a third-party dependency for the audit trail was the wrong trade.

Reporting you can defend

Three formulas — data completeness, sync success, reporting integrity — were ratified before the first sync and are the only numbers the study may quote. The quotable figures live in the ledger's metric rows, never in screenshots; the HubSpot dashboard is the operational view only, and its three weekly metric cards are typed by hand from ledger rows with the row id in the card description, character-for-character. The custom dashboard was cut before build and stays cut — reporting is HubSpot-native where Free can express it, and where it cannot (dedup visibility is a paid-tier feature) the spec names the real instrument instead of pretending a chart covers it.

Where AI fits — and where it structurally cannot

The enrichment sweep is deliberately the last deliverable, after foundation and automation — the published sequencing rule is foundation, then automation, then AI, in that order, always. For the borderline pile — the records where a human would hesitate — the workflow asks a model to suggest a fit verdict with a short evidence note. It never writes the fit field, never advances a stage, never touches a deal; its entire output is a review queue, and about two minutes per record of human decision-making on top of it.

Two guardrails are worth naming. The response parser was hardened by an offline adversarial test — six deliberately hostile response shapes, including prose-wrapped JSON — and upgraded from two-tier to three-tier extraction; the worst case is an honest "unparseable suggestion — review manually", never a wrong write. And because no API key is configured yet, the workflow runs in a deterministic mock mode: no API call is made, every mock note is prefixed MOCK: at the data level, a _mock flag travels through parse into the ledger line, and the swap to live is one config flag plus one credential — nothing else changes. While the mock runs, this study claims the pipeline, the parser and the review loop — never model results.

What went wrong, and how we caught it

None of these were found by users in production. All were found by instruments before a single live write.

The workflows would not import. n8n v2 disables the executeCommand node by default, and every import died with "Unrecognized node type". Caught at first import against the vendor's own breaking-changes page; fixed with one documented compose line, recorded in the decision log so anyone rebuilding the environment gets the same answer.

A dead configuration key. A run-context switch existed for the test and production lanes, but every log writer had the test lane hardcoded — the documented "flip before production" procedure would have silently measured nothing. Caught by reading all 64 nodes during the audit; seven builders were rewired to actually read the config.

A guaranteed runtime crash. One line in the dedup logger referenced an identifier that does not exist — every dedup match would have thrown. Caught by syntax-compiling every code node in the repository; it was the only such site. The line is deleted.

A field HubSpot rejects, in three places. A register-native value was being written to HubSpot's fixed-picklist dealtype, which returns 400 for anything outside its allowed values — a dead-letter entry on every valid deal. Caught by mapping each outbound write against HubSpot's property documentation. The suppression is staged with evidence, awaiting the owner's sign-off before go-live — the value is still visible in the workflow exports until then.

A patch map that drifted from the create map. The update path skipped one of the property renames the create path performs, and the search layer silently amplified the drift — a match-branch update would have always failed. Caught by diffing the two builders field-by-field. Same staged-fix status.

A false flag on production data. The sprint-handoff body hardcoded synthetic: true, which would have poisoned the seed/real boundary the whole register exists to protect. Found in the same outbound-write review; staged with the two items above.

Validation and testing

Every executable surface has an instrument, and every number below was re-run today (2026-08-13) from the dated final export on Node 24.19.0:

• Dedup logic — 28/28. The test executes the workflow's shipped code against a decision table — never a hand copy — covering create, update, skip, needs-review, unknown-stage and sentinel behaviour.
• Parser — 6/6. The six adversarial response shapes above.
• Mock pipeline — 17/17. Determinism across runs, verdict table, the MOCK: self-labelling contract, swap-safety, and shape parity with the real API response.
• Live-source reconciliation — 17/17. A stubbed, paginated HubSpot API plus five planted violations; all five were caught, each naming the offending record — a report you can check, never an aggregate-only claim. Missing credentials and unmapped stage ids fail loud.
• Repository gate — 24/24. Required files, workflow structure and connection integrity, naming-rule scans, schema-on-temp, register counts, seed byte-determinism against locked hashes, npm script wiring, live ledger views, mock markers, and the schema↔ingest contract.
• Reconciliation on the synthetic corpus — 94 checks, 0 violations, integrity 100.00%. The report is a dated file in the repo (reports/reconciliation-2026-08-05.md).
• Seed determinism. 25 companies, 50 contacts, 17 deals, all on reserved .example TLDs; the gate verifies the fixture hashes every run.

Results

The numbers above are tagged Self-measured, not Measured: they are real, and they come from our own fixtures, staging runs and audits — not from a client's system, because no client engagement exists yet. That distinction is enforced in frontmatter by the same schema every study on this site passes.

What they are not: business outcomes. No revenue, conversion, time-saved or efficiency figure is claimed anywhere on this page — no before-state exists, and inventing one is exactly the genre of case-study fiction this site refuses. The production measurement window for the three ratified metrics opens at the production go-live of the host (scheduled, founder-side); until then, any number about live sync success would be unquotable by the project's own rules.

Important implementation decisions

• No silent defaults. A binding process rule: any choice not named in a lock is surfaced to the owner with a recommendation first. It exists because a silent implementer default was caught and corrected on day one.
• Node.js for the scripting layer, with a .npmrc that disables all install lifecycle hooks — the database driver's prebuilt binary loads directly, and the install can never demand a compiler, Python, or platform toolchains. Proven with a dead-Python install run.
• One write contract between code and database. The schema's enums and the ingest validator are the same list; the gate fails if they drift.
• Keep-all backups. The backup script is online and WAL-safe, then restore-reads what it wrote (integrity check, table and view counts) and never auto-deletes — deleting founder data is a founder decision, not a script default.
• Auth via HubSpot's current Service Key mechanism — verified against the vendor's 2026 docs, with the legacy fallback documented in case the beta surface changes; the swap cost is one credential edit.
• Two lanes, by construction. Every row the system writes carries a run context; the metric views physically exclude local-test rows from study numbers. The ledger ships to production with its local-test history intact — deleting history to look clean is what this project exists to refuse.
• Three pre-production fixes staged, not rushed. The three staged findings above sit in the workflows with evidence until the owner signs off — the same discipline we'd ask a client to respect.

What this demonstrates

The build is mapped one-to-one onto the six deliverables published on the CRM & RevOps service page — implementation and rebuild, data migration and deduplication with a reconciliation report you can check, pipeline architecture that matches how the agency actually sells, workflow automation, reporting on agreed definitions, and AI-assisted enrichment once the foundation is sound — so the study reads like the engagement a client would get, not a separate demo. It demonstrates that the agency runs its own pipeline on the same discipline it sells: definitions written before sync, dedup before write, a dead-letter path instead of silent skips, a failure log kept in real time (including corrections owned openly — an off-by-one in a test count and a mechanism claim that was directionally right but mechanically wrong are both corrected in the decision log), and tests that execute shipped code rather than descriptions of it.

What it does not prove — yet

• No production numbers exist. The measurement window opens at production go-live (scheduled this month, founder-side). Every figure on this page is build-phase evidence from fixtures, staging and audit — and the project's own rules make local rows unquotable as study numbers.
• No live model output exists. Enrichment runs in labelled mock mode; what is proven is the pipeline, the parser and the review loop.
• Retry metrics are honest not-available. The retry rate reports n/a until the workflow engine emits execution rollups — a post-cutover task, named in the runbook.
• One reconciliation rule is dormant by design. Stage-aging (R7) needs real timestamps; it activates on the first production run and reports n/a on the synthetic corpus until then.
• A known scaling edge is documented, not hidden. The two sweep queries are built for a ≤100-record portal; the runbook names the >100-contact paging loop as the first scheduled refactor, and the script layer already auto-pages (stub-verified).
• It is not client work. No client, no client results, no public demo URL, no repository link — the repo is private and this page is the visible record. When captures exist they will show only the synthetic dataset, by rule.

Evidence this page is still owed

The screenshot slot above renders nothing today, on purpose. The capture list is fixed in the build record and all of it is synthetic-window-only: the HubSpot contacts view, the pipeline board, the native dashboards, all four n8n workflow canvases, one JSONL inbox terminal shot, and one reconciliation report — captured before any real prospect enters the portal, because the moment real data exists, screenshot mode ends permanently. Those captures are founder-side: the portal and the n8n instance live on his machines.

What we still run

This is the system this agency runs its own pipeline on. The operating cadence is the same one a client would inherit: weekly reconciliation against the live portal, a weekly metrics snapshot, a daily verified backup with the off-box copy scheduled, and a 15-minute Monday review of the guard widgets and the reconciliation report. The reconciliation report a client would receive after a migration runs against our own ledger first — the same command, the same report, the same definition of "matched".