Case Study • Database Design • Data Migration

ERP Database Design & Migration

Ko&Clo • Enterprise • Private Repository

Executive Summary

Key Metrics

112
Designed Tables
55
Modeled Relations
16
Scheduled Batches
144
Tuned Indexes

Data Flow Architecture

Excel / CSV → format gate → staged idempotent loads → PostgreSQL, governed from a DB admin console.

PostgreSQL 16 Python ETL cron Docker
Excel · CSV NAS shared folders daily / accumulated Format Gate header anchor check fail-fast on drift Staged Ingest 9 stages · UPSERT failure isolation Batch Scheduler 16 cron jobs PostgreSQL 16 112 tables · 5 domains 41 FK · 14 logical rels products.id canonical key DB Console usage · relation map 32 ERP Tabs reports · agents Schema workshop → migration backfill → scheduled incremental load → freshness monitoring

Schema Design with Domain Owners

Recurring Design Workshops

Relation & Cascade Policy

Domain Grouping & Keys

Domain Model

Master

products · pm_suppliers · stores · product_master_sheets

Transaction

purchase_daily · sales_daily · trade_ledger · returns_ledger

Order

order_build_runs · order_scores · best_integrated

Report

*_snapshots · *_snapshot_items · build_runs

System

users · role_tab_permissions · batch_runs · import_log

Migration Execution

Migration Codebase

Historical Backfill

Parity Verification

Batch Design

Schedule Map

Time Batch Target Tables
02:00 Daily trade daily_trade_ledger
03:00 Sample ingest sample_products · sample_ledger
03:30 Returns ledger returns_ledger · backorder_*
04:00 Backorder trade backorder_trade_history
06:00 Inventory snapshot inventory_snapshot · inventory_flow
06:10 Master product products · product_master_sheets
06:30 Wholesale daily wholesale_* (7 tables)
20:15 Incremental sales / purchase sales_daily · purchase_daily
22:00 Purchase price snapshot current_purchase_price
23:00 Trade ledger / history trade_ledger · trade_history

DB Governance Console

Table Usage & Growth

Relation Map

Tab ↔ Table Traceability

Freshness & Storage Alerts

Technology Stack

Database
PostgreSQL 16 DDL / Constraints Index Design
Migration / ETL
Python pandas openpyxl psycopg
Batch & Ops
cron Docker Compose NAS Mount
Serving
FastAPI SQLAlchemy Vue 3

Impact

Single Source of Truth

Integrity

Automation

Operability