---
title: "POS to PostgreSQL Migration & Real-Time Sync"
url: https://janarraj.com.my/case-studies/pos-to-postgresql-migration/
date: 2026-09-17
modified: 2026-10-02
lang: en
author: "Janarraj Rajandran"
description: "Home/Case Studies/POS to PostgreSQL Migration { DATA ENGINEERING & AUTOMATION } POS to PostgreSQL Migration & Real-Time Data Pipeline Migrating 100,000+ historical retail point-of-sale transactions into a production-grade relational database..."
word_count: 674
---

# POS to PostgreSQL Migration & Real-Time Sync

[Home](/)/[Case Studies](/case-studies/)/POS to PostgreSQL Migration
{ DATA ENGINEERING & AUTOMATION }

# POS to PostgreSQL Migration & Real-Time Data Pipeline

Migrating 100,000+ historical retail point-of-sale transactions into a production-grade relational database and engineering an automated, resilient sync workflow with zero data loss.

ON THIS PAGE
Overview
The Problem
The Solution
System Architecture
Measured Outcomes
Key Takeaways

## Executive Summary

For a high-volume retail network with nationwide physical branches in Malaysia, point-of-sale (POS) data was trapped in closed cloud endpoints and siloed spreadsheets. This case study details how we extracted, normalized, backfilled, and automated a continuous synchronization pipeline directly into PostgreSQL using self-hosted n8n workflows.

100k+
TRANSACTIONS BACKFILLED

0%
DATA LOSS / DUPLICATES

< 3s
INCREMENTAL SYNC LATENCY

100%
AUTOMATED ETL (ZERO CSVS)

THE PROBLEM

### Fragmented Branch Data & Blind Marketing Attribution

As the business scaled to dozens of branches across multiple states, reporting operations hit a severe bottleneck:

- **Data Trapped in REST Pagination Walls:** The cloud POS system restricted bulk queries to paginated limits (500 records max per request), making comprehensive multi-year historical analysis impossible through manual GUI downloads.
- **Fragile Spreadsheet Chokepoints:** Operations and finance teams relied on weekly CSV exports from disparate branch managers. Files frequently corrupted, crashed Excel due to row volume, and lacked consistent branch ID normalization.
- **No Real-Time Marketing Feedback:** Digital marketing teams running high-spend Google and Meta ad campaigns could not tie online ad clicks to in-store POS customer purchases, creating blind spots in true Customer Acquisition Cost (CAC) and customer lifetime value (LTV).

THE ARCHITECTURAL SOLUTION

### Automated Extraction, Normalization & Resilient PostgreSQL Upsert

Rather than relying on brittle third-party ETL connectors or manual dumps, I architected a custom, deterministic automation pipeline using self-hosted n8n, JavaScript transformation nodes, and PostgreSQL.

- **Configurable Backfill Engine:** Designed a parameterized workflow capable of paging through millions of ringgit in sales data with dynamic start pages, automated batch calculation, and memory-safe garbage collection.
- **Token & Rate-Limit Resilience:** Engineered automated Bearer token renewal and exponential backoff handling to prevent API timeouts during heavy multi-thousand record batches.
- **Structured JSON Decomposition:** Wrote custom JavaScript Code nodes to split batch arrays into individual transaction entities, sanitizing doc numbers, tax amounts, branch UUIDs, line items, and ISO timestamps.
- **Idempotent Relational Database Upserts:** Implemented SQL upsert logic (`ON CONFLICT (id) DO UPDATE`) to guarantee zero duplicated rows regardless of re-runs or interrupted API loops.

## Pipeline Architecture

The workflow separates extraction configuration, batch retrieval, and database upsert operations into discrete, failure-isolated stages:

STEP 01
**Manual / Cron Trigger**
Configurable page sizes (500 records) & run targets.

STEP 02
**Metadata Sniffing**
Fetches Page 1 to determine total pages and active record count.

STEP 03
**Batch Loop Engine**
Iterates sequential pages with error boundaries and delay buffers.

STEP 04
**Payload Normalization**
Code node transforms nested results into flat relational schemas.

STEP 05
**PostgreSQL Ingestion**
Streams into normalized tables with strict foreign key integrity.

n8n Automation
PostgreSQL
REST API ETL
JavaScript Node.js
Docker
Looker Studio BI

THE MEASURED OUTCOME

### Immediate Operational Clarity & True Closed-Loop Attribution

The migration transformed how executive management and marketing operations evaluate company performance:

- **Complete Historical Integrity:** 100% of historical transactions were successfully backfilled and reconciled against bank settlements without a single mismatched ringgit.
- **Zero Manual Labor:** Eliminated an estimated 15+ hours weekly of manual CSV downloading, copy-pasting, and reconciliation across accounting and analytics teams.
- **True Closed-Loop Marketing Attribution:** By querying POS transactions against digital marketing lead IDs in PostgreSQL, the marketing team unlocked multi-touch attribution—proving exactly which campaigns generated profitable in-store visits.
- **Real-Time Executive Dashboards:** Connected PostgreSQL directly to internal dashboards, granting leadership real-time hourly visibility into branch gross margin, basket size, and SKU velocity.

## Strategic Takeaways

Data silos are not a software problem; they are an architectural problem. Standard SaaS platforms will always isolate their data to create vendor lock-in. Building a self-hosted, owned data infrastructure gives growing enterprises complete autonomy, bulletproof analytics, and an unfair competitive advantage in marketing efficiency.

{ READY TO CONNECT YOUR SYSTEMS? }
[Let’s connect↗](/contact/)

Have fragmented data, messy spreadsheets, or disconnected APIs?
Let's turn your operations into a unified, queryable engine.

[Start a conversation ↗](/contact/)