Paul Dosser
FREN
Back to home

Case study - Freelance client work

Map Align - AI-assisted reconciliation pipeline

A 5-stage pipeline that reconciles external sources (websites, maps, PDFs) with an internal database of thousands of physical spaces. The deterministic engine decides; the LLM reviews and can only downgrade — never escalate.

Pipeline stages
5
Lines of core code
~3300
Proposal types
4
Match threshold
0.72

The brief

The client builds a B2B conversational platform for physical venues (airports, malls) with an internal database of thousands of spaces — shops, restaurants, services. External sources (official websites, interactive maps, PDF exports) constantly drift from that database.

They needed a tool that aligns the two worlds without operators comparing lists by hand — and without the AI silently inventing or deleting spaces.

The challenge

An LLM left alone on this task proposes mass deletions on any incomplete source and hallucinates creations on parsing noise. Lineage also matters: which source produced this proposal, which run, against which version of the internal snapshot.

Every source has its own permissions (some can create, others cannot), and the admin must stay in full control before a proposal touches the real database.

The solution

A 5-stage pipeline: extract, unify, compare, apply (plus an internal db-extract). Each stage produces typed artifacts kept for audit. The deterministic engine decides first; the LLM is only a reviewer that can downgrade an action to noop or drop a field change — it can never introduce a deletion or a creation.

Every decision is traced (decisionSource: rule | ai), runs form a parent-child tree, and the admin validates each proposal before the final apply.

The pipeline

Each stage writes its artifacts and its run row to Postgres. The UI surfaces progress, precondition errors (NoIsmSourcesConfiguredError, NoIsmCompareInputsError, etc.) and proposal review.

  1. 01Extract

    Stage 1

    Internal DB snapshot plus one child run per configured source (web scraping, map parsing, PDF). Falls back to bootstrap metadata if full extraction fails.

  2. 02Unify

    Stage 2

    Candidate clustering via Jaccard name similarity plus weighted hints (terminal, level, zone, area, location). Threshold fixed at 0.72, ambiguity margin at 0.06.

  3. 03Compare

    Stage 3

    A deterministic decision tree yields proposals (create/update/delete/noop) enriched with top-5 lookalike shortlists for creates. An LLM reviews and may only downgrade.

  4. 04Apply

    Stage 4

    Accepted proposals are materialized in a transaction: space creation, parent hierarchy resolution, lazy creation of unknown taxonomies, upserts of labels, brands, audiences.

  5. 05Review

    Cross-cutting

    Every proposal has a reviewStatus (pending / accepted / rejected / applied). Operators accept, reject, or ignore before apply touches production data.

Design patterns

  • Rules first, AI as reviewer: the LLM can only downgrade a decision, never escalate it
  • Sanitization before diff: strip placeholders ('n/a', 'unknown'), extract embedded URLs, drop position keywords from freetext
  • Permission-gated actions: each source can independently forbid create/update/delete
  • Run lineage: parent-child tree with knowledge_resource_id (null = internal DB source)
  • Lazy taxonomy creation: apply creates new space types on the fly if a draft references an unknown code
  • Immutable artifacts: candidates, unified candidates, and proposals are preserved for audit

Tech stack

  • TanStack Router (file-based, nested routes)
  • TanStack Query
  • Drizzle ORM + PostgreSQL
  • OpenAI GPT (structured outputs with Zod schemas)
  • Zod for end-to-end validation
  • Turborepo + Bun workspaces

Outcomes

Pipeline running in production for the client. Operators moved from manual hour-long reconciliation passes to reviewing pre-decided proposals.

Zero hallucinated deletions thanks to the deterministic safety net, and every decision stays auditable via the (run_id, decisionSource) pair.