Retool ERP Modules

Portfolio context: Extracted from founder-led production systems — multi-marketplace inventory, orders, and warehouse execution. Full portfolio · aspiranted.github.io

Production CI/CD tooling and business logic patterns extracted from a full-scale internal ERP built on Retool (134 SQL queries, 143 JS scripts, 8 Python tools).

Architecture

src/
+-- validator/
¦   +-- sql_validator.py      # SQL query validation for platform deployment
¦   +-- js_validator.py       # JS script validation for platform deployment
+-- sync/
¦   +-- comparator.py         # Deployed state vs local files comparison
+-- patterns/
    +-- sql/
    ¦   +-- order_state_machine.py  # Order transitions + availability management
    +-- js/
        +-- helpers.py         # Shared utility functions (phone, dates, decimals)

Key Technical Features

SQL Validator (src/validator/sql_validator.py)

CI/CD-ready validation for SQL queries deployed to low-code platforms:

• Template params in comments: detects {{ }} inside SQL comments - the platform interprets these as real parameter dependencies even in comments, causing phantom dependency chains and circular reference errors
• Balanced parentheses/quotes: catches syntax errors before deployment
• Parameter format validation: ensures {{ name }} format with proper spacing
• Naming conventions: enforces q_ prefix for queries
• Best practices: warns about SELECT * and missing LIMIT
• Encoding safety: detects accented characters (á, é, ñ) in comments that cause issues between UTF-8/Latin1 systems
• Structured results: ValidationResult dataclass for programmatic CI integration

JS Validator (src/validator/js_validator.py)

Platform-specific JavaScript validation:

• Template params in comments: same phantom dependency issue as SQL
• Function return detection: return myFunction causes "Could not clone result" - the platform cannot serialize function references
• Notification API: detects positional args showNotification("msg", "error") which fail silently - must use object syntax {title, description, notificationType}
• Modal API: only .show() / .hide() work - detects .open(), .close(), .setHidden() which silently fail
• Naming conventions: s_ for scripts, tf_ for transformers

Sync Comparator (src/sync/comparator.py)

Compares deployed platform state against local development files:

• Loads platform inventory JSON (exported from the deployed system)
• Scans local directories for .sql and .js files
• Cross-references with name mapping tables (local path ? platform name)
• Produces structured SyncReport:
  • Synchronized: exist in both places
  • Only in platform: deployed but no local file
  • Only in local: developed but not deployed (ACTION NEEDED)
  • Unmapped: local files missing from mapping table
• Groups UI components by screen and type
• Generates Markdown reports for team review

Order State Machine (src/patterns/sql/order_state_machine.py)

Complex order state transitions with inventory side effects:

• 5 warehouse states: BUSCAR ? ENCONTRADO ? PREPARADO ? ESPERANDO ? CANCELAR
• Availability management:
  • Transition TO CANCELAR: restore item availability (only if no sale AND no other active order uses the item)
  • Transition FROM CANCELAR: reserve item (available=false)
  • Normal transitions: no availability change
• Substitution wizard (multi-step validation):
  1. Same-item check
  2. Original must exist in order
  3. New must not be duplicate in same order
  4. New must not be reserved in another active order
  5. Price difference calculation (>70% triggers warning)
  6. Weight difference tracking (for shipping label updates)
• Health status calculator: OPTIMAL / WARNING / CRITICAL based on overdue search count and expiring-today thresholds

Shared Helpers (src/patterns/js/helpers.py)

Utility functions shared across 20+ ERP scripts:

• Phone formatting: Spain prefix (+34) with intelligent prefix detection
• Date/time SQL formatting: YYYY-MM-DD and HH:MM:SS conversion
• Decimal handling: comma/dot normalization for Spanish locale (4,50 ? 4.50)
• Condition validation: CON_TARA and PARA_PIEZAS require 50+ character descriptions
• Save button state: disable logic for defect condition modals

Testing

pip install -r requirements.txt
python -m pytest tests/ -v

122 tests covering:

• SQL validation (syntax, params, comments, encoding, naming, best practices)
• JS validation (function returns, notification API, modal API, naming)
• Sync comparison (queries, scripts, UI components, mappings, Markdown reports)
• Order state machine (all transition paths, availability side effects, boundary cases)
• Substitution validation (all error paths, price/weight warnings)
• Health status (thresholds, boundary conditions, color mapping)
• Helpers (phone formatting, date/time, decimals, condition validation)