Skip to content
Ordermatic Docs

ERP-Unlocked Data Backend - Executive Summary

Decision Required: Should we migrate ERP replica data from PostgreSQL to a lakehouse architecture?

The Proposal in 3 Minutes

Section titled “The Proposal in 3 Minutes”

Current Problem

Section titled “Current Problem”

ERP-Unlocked stores analytical data (2.3M products, 500K cross-references, 5M inventory records per org) in PostgreSQL, a database optimized for transactional workloads. This creates:

  • ❌ Performance issues - Full-text search on millions of rows is slow
  • ❌ No historical analytics - Can’t track pricing trends or inventory patterns
  • ❌ Expensive storage - PostgreSQL SSD storage costs $450/month for 4.5TB
  • ❌ Single-ERP limitation - Hard to query across Prophet21 + NetSuite simultaneously
  • ❌ AI friction - LLMs query raw SQL tables instead of semantic APIs

Proposed Solution

Section titled “Proposed Solution”

Migrate ERP data to a modern data backend:

┌──────────────────────────────────────────────┐
│  POSTGRES (OLTP)                             │
│  • Orders, PDFs, Mappings                    │
│  • Stays at 500 GB (90% reduction)           │
├──────────────────────────────────────────────┤
│  HUDI LAKEHOUSE (OLAP) ← NEW                 │
│  • Products, Customers, Inventory            │
│  • Cloudflare R2 storage ($40/month)         │
│  • Time-travel queries                       │
│  • Partitioned by organization               │
├──────────────────────────────────────────────┤
│  CUBE SEMANTIC LAYER ← NEW                   │
│  • GraphQL/REST API for AI agents            │
│  • Row-level security                        │
│  • Pre-aggregated metrics                    │
│  • Multi-ERP federation                      │
└──────────────────────────────────────────────┘

Key Benefits

Section titled “Key Benefits”
Benefit Impact
42% Cost Reduction $7,500/year savings ($1,500 → $875/month)
Historical Analytics “Show pricing trend for product X over 6 months” ✅
Multi-ERP Queries Search across Prophet21 + NetSuite simultaneously ✅
AI-Ready Semantic layer with RLS for LLM tools ✅
Better Performance 800ms → 200ms for product search (4x faster)
Infinite Scale Postgres limited to ~5TB, Hudi scales to petabytes

What Changes

Section titled “What Changes”

REPLACED:

  • ❌ IERPSyncService (TypeScript) → ✅ dlt pipelines (Python)
  • ❌ PostgreSQL erp_products, erp_customers tables → ✅ Hudi lakehouse
  • ❌ Direct SQL queries → ✅ Cube semantic layer API

STAYS THE SAME:

  • ✅ Trigger.dev orchestration (same scheduled tasks)
  • ✅ Clerk authentication
  • ✅ Astro + React frontend (no UI changes)
  • ✅ PostgreSQL for orders, documents, mappings

Investment Required

Section titled “Investment Required”
Timeline: 6 months (phased)
  Phase 1 (Pilot): 6 weeks - Products only
  Phase 2: 12 weeks - Full migration
  Phase 3: 4 weeks - Cleanup


Resources:
  • 1 Data Engineer (full-time)
  • 1 Backend Engineer (50% time)
  • 1 DevOps Engineer (25% time)


Budget:
  • Engineering time: ~$60K
  • Infrastructure (pilot): ~$1,500
  • Ongoing: $875/month (42% lower than current)


Total Phase 1: $65K

Risk Assessment: MEDIUM

Section titled “Risk Assessment: MEDIUM”
Risk Mitigation
Performance worse than expected 6-week pilot validates before full commitment
Team unfamiliar with Hudi/Spark Hire experienced Data Engineer
Migration data loss Dual-run systems, checksums, rollback plan
Increased complexity Phased approach, comprehensive monitoring

Recommendation

Section titled “Recommendation”

✅ PROCEED WITH PHASED APPROACH

Why:

  1. Strong technical fit for analytical workloads
  2. Enables AI-powered features impossible today
  3. 42% cost savings while improving performance
  4. Low-risk pilot phase validates architecture
  5. Future-proofs for multi-ERP, historical analytics

Conditions:

  • Commit to 6-month timeline (no rushing)
  • Hire/allocate 1 Data Engineer with Hudi expertise
  • Budget $65K for Phase 1
  • Define clear pilot success metrics

Alternative (If Too Complex)

Section titled “Alternative (If Too Complex)”

Option B: Incremental PostgreSQL Improvements

Changes: • Add PostgreSQL read replica for OLAP queries
  • Implement TimescaleDB extension for time-series
  • Redis caching layer for product searches
Investment: ~$15K (3-4 weeks)
Savings: Minimal (~$100/month)
Benefits: Lower complexity, faster delivery
Drawbacks: No multi-ERP, no time-travel, scaling limits

Decision Framework

Section titled “Decision Framework”

Choose Data Backend IF:

  • ✅ Planning 5+ ERP integrations (NetSuite, SAP, Dynamics)
  • ✅ Need historical analytics (pricing trends, inventory patterns)
  • ✅ Scaling to 100+ organizations
  • ✅ Want AI-first semantic layer
  • ✅ Team can hire/train data engineers

Choose Incremental Improvements IF:

  • ✅ Current scale sufficient (<50 orgs)
  • ✅ Limited engineering resources
  • ✅ Risk-averse to architectural changes
  • ✅ Need faster time-to-value (<1 month)

Next Steps (If Approved)

Section titled “Next Steps (If Approved)”
  1. Week 1-2: Architecture review, stakeholder alignment
  2. Week 3-4: Hire Data Engineer, allocate resources
  3. Week 5-10: Phase 1 pilot (products only)
  4. Week 11: Decision gate (proceed or abort)

Additional Documentation

Section titled “Additional Documentation”
  • Full Technical Proposal - Complete 89-page architecture spec
  • Implementation Guide (repo history) - RBAC, Trigger.dev Python, monorepo organization, OpenTelemetry integration
  • Visual Comparison (repo history) - Side-by-side architecture diagrams

Questions? Contact the technical architecture team.