B2B Field-Sales & Order Platform

Problem

Dal Santo's sales reps visit bulk-buying distributors in person and load orders on the spot. They were doing it on an aging desktop tool, and the company wanted a modern replacement — plus, critically, GPS verification of each order. Reps are paid travel expenses, so ownership needed a reliable way to confirm that client visits actually happened. The new system also had to write into the existing legacy factory database without being allowed to modify it, and keep working even when that database was unavailable.

My Role

Sole developer, end-to-end: the data model, the sync layer against the legacy factory database, the field-sales web app, business rules, and deployment.

Architecture & Key Decisions

• Durable outbox decoupled from the factory DB. Orders are written first to my own PostgreSQL store (durable, marked pending), and a background worker later syncs them into the legacy SQL Server. This means reps keep taking orders even during factory-side outages (a power cut at the plant no longer stops sales).
• Integrated into a legacy system I couldn't modify. The original system's owner required its schema stay untouched, so the sync maps my model onto the exact historical contract of the legacy orders table — one row per item, the legacy ID/date formats, the exact column types — instead of reinventing it. All of this without breaking a live operation.
• Idempotency without being able to index the legacy table. That table has no key to dedupe on, so I embed a client-generated order id inside the record and check for it before inserting, backed by a UNIQUE constraint on my side.
• Order-ID generation with pessimistic locking. The per-vendor order number is computed as MAX+1 under a row lock inside a SERIALIZABLE transaction, because the new system controls the numbering but the sequence lives in the shared legacy database.
• Concurrent queue claim. The sync worker claims pending orders with FOR UPDATE SKIP LOCKED, marks them syncing, and a later run automatically releases anything stuck too long — designed for multiple workers with no double-send.
• Permanent vs. transient errors. Orders that can't fit the legacy contract (non-numeric client, decimal quantity, over-long code) are flagged for review and never retried; everything else goes to a retry path.
• GPS as a server-side business rule. No order is saved without valid GPS: the server checks accuracy, freshness and timestamp coherence. IP-geolocation signals flag anomalies for later review but never block a sale.
• Device binding with admin approval. A device cookie signed with HMAC-SHA256 (constant-time comparison); the rep's first mobile auto-approves, later devices stay pending until an admin validates them.

Trade-Offs

• Two databases instead of one. Running my own Postgres alongside the legacy SQL Server adds moving parts, but it's what makes the system resilient to factory-side outages and gives me my own audit trail. Writing straight to the legacy DB would have been simpler and more fragile.
• Deduplication by scanning a JSON marker instead of an index. Since I couldn't add an index to the legacy table, dedup on that side relies on searching a marker inside the record — slower than an index, but it was the only option that didn't touch the schema I wasn't allowed to change.
• Anti-fraud that flags but never blocks. IP/GPS mismatch and VPN signals are recorded for review rather than rejecting the order outright — a deliberate choice to never block a legitimate sale over a false positive.
• Schema auto-provisioned in place. New columns are added with IF NOT EXISTS at runtime rather than through a formal migration tool — pragmatic for this setup, at the cost of less formal migration history.

Stack

• TypeScript
• Next.js
• React
• PostgreSQL
• SQL Server
• Zod
• Tailwind

Internal system — not public

Also built for Dal Santo:KTM e-commerceRaleighFire Bird