Etsy Variant Engine

Rule-driven Etsy inventory automation engine with DB-backed SKU generation,

automatic template analysis, scale-property handling, and workshop-safe SKU fallback.

---

🚀 What This Project Is

Etsy Variant Engine is a backend inventory automation system designed for:

• High-variation Etsy shops
• Structured SKU production systems
• Workshop-driven manufacturing workflows
• Multi-shop (profile-based) architecture
• Future SaaS expansion

This is not a bulk editor.

This is a deterministic inventory orchestration engine.

---

🧠 Core Concept

The engine connects:

Etsy Listing Template

⬇

DB Code Tables (i_color, i_length, i_qty, i_type...)

⬇

Structured SKU Generator

⬇

Full Variation Matrix Builder

⬇

Safe Etsy Inventory Overwrite

---

✨ Features (v2)

• Dynamic Etsy template analysis
• Scale property support (length → numeric matching)
• Automatic scale_id / value_ids propagation
• SKU fallback logic (fixed length applied even if template has no length)
• Multi pricing strategies
• Workshop-safe SKU enforcement
• DB-backed deterministic code system
• Component & delimiter overrides
• Display label override engine
• Dry-run safe simulation mode
• FastAPI UI
• JSON CLI support
• Multi-profile architecture (future SaaS ready)

---

🏗 Architecture

• Python 3.9+
• FastAPI
• Etsy API v3
• MySQL (pymysql)
• Profile-driven SKU segmentation
• Rule-based template analyzer
• Deterministic DB resolver layer

---

📦 Installation (Fresh Setup)

1️⃣ Clone

git clone https://github.com/recyalcin/etsy-variant-engine.git
cd etsy-variant-engine

2️⃣ Install dependencies

pip install -r requirements.txt

Recommended requirements:

fastapi
uvicorn[standard]
watchfiles
pymysql
requests
python-dotenv
jinja2
python-multipart
email-validator

---

⚙️ Environment Setup

Create .env file:

ETSY_API_KEY=your_api_key
ETSY_REFRESH_TOKEN=your_refresh_token

MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASS=password
MYSQL_DB=etsy_db

DB_PROFILE=belkymood
WRITE_ENABLED=true

⚠ Never commit your real .env.

---

▶️ Running the App

This is NOT an npm project.

Do NOT run:

npm run dev ❌

Instead run:

uvicorn app:app --reload

Open:

http://127.0.0.1:8000

---

🧪 CLI Usage (Direct Engine Run)

python run_inventory.py input.json --dry-run

Without dry-run:

python run_inventory.py input.json

---

🔐 Dry Run Mode (Important)

Dry-run:

• Does NOT write to Etsy
• Does NOT mutate DB
• Shows:
  • SKU preview
  • Template detection
  • Pricing resolution
  • DB insert plan
  • Mapping trace
  • SKU decode preview

Always dry-run before live overwrite.

---

📝 Workshop-Style Input Example

Type : Necklace Heart
Size : 10mm
Color : GOLD, SILVER, ROSE
Length : 14", 16", 18", 20"
Quantity : 1 heart, 2 heart, 3 heart

Price :
1 heart - $39
2 heart - $52
3 heart - $62

pricing_by : qty

Engine automatically:

• Detects template structure
• Resolves DB codes
• Normalizes display values
• Generates SKU matrix
• Applies pricing rule

---

🏷 SKU Structure

Profile-driven segmentation:

[type][length][color][qty][size][start][space]

Example decoded:

{
  "type": "24",
  "length": "03",
  "color": "7",
  "qty": "00",
  "size": "0",
  "start": "03",
  "space": "0"
}

SKU is always DB-backed and deterministic.

---

🧩 Override System

Overrides allow template correction without changing DB.

---

1️⃣ Component Override

Force template property structure:

{
  "component_overrides": {
    "513": ["color", "qty"]
  }
}

---

2️⃣ Delimiter Override

Force join delimiter:

{
  "delim_overrides": {
    "513": " - "
  }
}

---

3️⃣ Display Value Override (Global)

{
  "display_value_overrides": {
    "color": {
      "ROSE": "Rose Gold"
    }
  }
}

---

4️⃣ Display Override Per Property

{
  "display_value_overrides_by_property": {
    "513": {
      "qty": {
        "1 Taş": "1 Birthstone",
        "2 Taş": "2 Birthstones",
        "3 Taş": "3 Birthstones"
      }
    }
  }
}

---

📏 Scale Property Handling (Length)

Etsy scale properties often return:

"14"

But shop input may be:

14"
14 inches

Engine automatically normalizes:

14" → 14
14 inches → 14

And attaches:

• scale_id
• value_ids

automatically.

---

🛠 SKU Fixed-Length Fallback (v2 Feature)

If template has NO length variation

but input provides a single fixed length:

Engine:

• Does NOT send length to Etsy
• BUT writes length code into SKU

This prevents workshop cutting errors.

If multiple lengths are provided without template support → engine throws error.

---

💰 Pricing Modes

Supported pricing_by:

• fixed
• color
• qty
• length
• size

Size-based example:

Type : Mineli 250 disc kolye
Size : 15mm, 20mm
Color : GOLD, SILVER, ROSE
Length : 14", 16", 18", 20"
Quantity : -
Space : -
Start : ortada

Price :
15mm - $45
20mm - $55

pricing_by : size

Example:

pricing_by : color

Price :
Gold - 32
Silver - 32
Rose - 32

---

🗄 DB Tables

Core tables:

• i_type
• i_color
• i_length
• i_qty
• i_size
• i_start
• i_space

Engine auto-inserts missing values (unless dry-run).

---

🔐 Safety

• Dry-run mode
• WRITE_ENABLED flag
• readiness_state auto-detected
• SKU decode preview before overwrite
• DB plan summary before execution

---

👥 Multi-Shop Ready

Profile-based system:

• belkymood
• future profiles possible

Future SaaS expansion supported.

---

🧪 Typical Workflow

1. Clone project
2. Configure .env
3. Start FastAPI
4. Paste workshop-style input
5. Dry-run
6. Review SKU + DB plan
7. Run live

---

🧠 If You Forget How To Use It

Run:

uvicorn app:app --reload

Then:

• Paste workshop input
• Ensure pricing_by is set
• Add overrides if needed
• Click dry-run first
• Confirm mapping trace
• Then run live

Never use npm.

This is Python.

---

📜 License

MIT License

---

👤 Author

Recep Yalcin

💡 Override Examples

{

  "component_overrides": {
    "54142602013": ["length"]
  },

  "display_value_overrides_by_property": {
    "513": {
      "color": {
        "GOLD": "Gold",
        "SILVER": "Silver",
        "ROSE": "Rose"
      },
      "qty": {
        "1 Option": "1 Stone",
        "2 Options": "2 Stones",
        "3 Options": "3 Stones",
        "4 Options": "4 Stones",
        "5 Options": "5 Stones",
        "6 Options": "6 Stones",
        "7 Options": "7 Stones",
        "8 Options": "8 Stones",
        "9 Options": "9 Stones",
        "10 Options": "10 Stones",
        "11 Options": "11 Stones",
        "12 Options": "12 Stones",
        "13 Options": "13 Stones"
      }
    }
  }

}

Another example:

{

  "component_overrides": {
    "47626759838": ["length"]
  },

  "delim_overrides": {
    "513": " - "
  },

  "display_value_overrides_by_property": {
    "47626759838": {
      "length": {
        "14\"": "14",
        "16\"": "16",
        "18\"": "18",
        "20\"": "20",
        "22\"": "22",

        "14 inches": "14",
        "16 inches": "16",
        "18 inches": "18",
        "20 inches": "20",
        "22 inches": "22"
      }
    }
  }

}

Another example:

{

  "display_value_overrides": {
    "color": {
      "Rose": "Rose Gold",
      "ROSE": "Rose Gold"
    }
  }

}

Another example:

{

  "component_overrides": {
    "513": ["color", "qty"]
  },

  "delim_overrides": {
    "513": " - "
  },

  "display_value_overrides_by_property": {
    "513": {
      "qty": {
        "1 Taş": "1 Birthstone",
        "2 Taş": "2 Birthstones",
        "3 Taş": "3 Birthstones",
        "4 Taş": "4 Birthstones",
        "5 Taş": "5 Birthstones"
      }
    }
  },

  "display_value_overrides": {
    "color": {
      "GOLD": "Gold",
      "SILVER": "Silver",
      "ROSE": "Rose"
    }
  }

}

Another example:

{

  "component_overrides": {
    "513": ["color", "qty"]
  },

  "delim_overrides": {
    "513": " - "
  },

  "display_value_overrides_by_property": {
    "513": {
      "qty": {
        "1 Taş": "1 Birthstone",
        "2 Taş": "2 Birthstones",
        "3 Taş": "3 Birthstones",
        "4 Taş": "4 Birthstones",
        "5 Taş": "5 Birthstones"
      }
    }
  },

  "display_value_overrides": {
    "color": {
      "GOLD": "Gold",
      "SILVER": "Silver",
      "ROSE": "Rose"
    }
  }

}