Feature: Two-way sync of product status between Shopify and local database
Created: February 7, 2026
Status: Complete and ready for use
When products are manually disabled in Shopify (set to DRAFT or ARCHIVED status), the local database can now automatically reflect these changes by clearing the web flag.
This ensures your local database stays synchronized with manual changes made in the Shopify admin panel.
Scenario: Staff member disables product in Shopify
1. Staff logs into Shopify admin
2. Sets product to DRAFT status (hides from website)
3. Local database still shows product as "cleared for web"
4. Reports and queries show incorrect status
5. Product may get re-enabled on next sync
Result: Inconsistent data between Shopify and database
Scenario: Staff member disables product in Shopify
1. Staff logs into Shopify admin
2. Sets product to DRAFT status
3. Status sync runs (manual or scheduled)
4. Local database web flag automatically cleared
5. Reports and queries show correct status
Result: Database always matches Shopify
Shopify uses three status values for products:
| Status | Meaning | Visible on Website | Database Action |
|---|---|---|---|
| ACTIVE | Published and available | ✅ Yes | No change (keep web flag) |
| DRAFT | Hidden, not published | ❌ No | Clear web flag |
| ARCHIVED | Archived/disabled | ❌ No | Clear web flag |
from package.fullhouse.ObjServiceFHShopify import ObjServiceApi
service = ObjServiceApi()
service.Guid = "SHOPIFY_SYNC"
# Sync by SKU
result = service.sync_product_status_from_shopify(
sku="PROD-001"
)
print(result)
# Output:
# {
# "success": True,
# "shopify_status": "DRAFT",
# "action_taken": "cleared_web_flag",
# "sku": "PROD-001",
# "product_id": "123456"
# }
# Sync all products
summary = service.sync_all_product_statuses()
print(summary)
# Output:
# {
# "total_checked": 500,
# "web_flags_cleared": 12,
# "no_changes": 488,
# "errors": 0
# }
# If your database uses a different field name
result = service.sync_product_status_from_shopify(
sku="PROD-001",
web_status_field="PM_PUBLISHWEB"
)
The default field is PM_WEBACTIVE. If your database uses a different field, specify it:
Common field names:
PM_WEBACTIVE (default)PM_WEBSTATUSPM_PUBLISHWEBPM_ISWEBPM_CLEAREDFORWEBTo find your field name:
-- Check your products table schema
DESCRIBE trader.products;
-- Look for fields related to web/publish/active
SHOW COLUMNS FROM trader.products WHERE Field LIKE '%WEB%';
# Sync single product
python factory.service/package.fullhouse/ObjServiceFHShopify.py \
sync-status --sku PROD-001
# Sync all products
python factory.service/package.fullhouse/ObjServiceFHShopify.py \
sync-all-statuses
# Sync with custom field
python factory.service/package.fullhouse/ObjServiceFHShopify.py \
sync-status --sku PROD-001 --field PM_PUBLISHWEB
# Add to crontab - run daily at 2 AM
0 2 * * * cd /path/to/project && \
python factory.service/package.fullhouse/ObjServiceFHShopify.py \
sync-all-statuses >> /var/log/shopify-status-sync.log 2>&1
Add to your ComputeSql() workflow:
def ComputeSql(self, Param1="", Param2="", Param3=""):
# ... existing sync logic ...
# After syncing product to Shopify
# Sync status back from Shopify
self.sync_product_status_from_shopify(sku=Param1)
# ... rest of method ...
Scenario: Disable Christmas products after December
# Staff disables products in Shopify
# Run sync to update database
summary = service.sync_all_product_statuses()
# Database now shows products as not cleared for web
Scenario: Temporarily hide out-of-stock items
# Staff sets products to DRAFT in Shopify
# Sync updates database
service.sync_product_status_from_shopify(sku="OUT-OF-STOCK-001")
# Database flag cleared, reports show correct status
Scenario: Emergency product removal
# Staff archives recalled product in Shopify
result = service.sync_product_status_from_shopify(sku="RECALLED-PRODUCT")
# Database immediately updated to match
Scenario: Find products disabled in Shopify but active in database
-- Before running sync, find mismatches
SELECT
p.PM_PRODUCTCODE,
p.PM_WEBACTIVE as db_status,
i.status as shopify_status
FROM trader.products p
JOIN core.axion.data_shopify_inventory i
ON p.PM_PRODUCTCODE = i.product_id
WHERE p.PM_WEBACTIVE = 1
AND i.status IN ('DRAFT', 'ARCHIVED');
-- Run sync to fix
-- python ObjServiceFHShopify.py sync-all-statuses
-- Verify cleanup
-- Should return 0 rows
Sync status for a single product.
Parameters:
product_id (str, optional): Shopify product IDsku (str, optional): Product SKUweb_status_field (str, default="PM_WEBACTIVE"): Database field nameReturns:
{
"success": bool,
"shopify_status": "ACTIVE" | "DRAFT" | "ARCHIVED",
"action_taken": "cleared_web_flag" | "no_change" | "none",
"sku": str,
"product_id": str
}
Examples:
# By SKU
result = service.sync_product_status_from_shopify(sku="PROD-001")
# By product ID
result = service.sync_product_status_from_shopify(product_id="123456")
# Custom field
result = service.sync_product_status_from_shopify(
sku="PROD-001",
web_status_field="PM_PUBLISHWEB"
)
Batch sync for all products.
Parameters:
web_status_field (str, default="PM_WEBACTIVE"): Database field namemax_products (int, optional): Limit number of products to syncReturns:
{
"total_checked": int,
"web_flags_cleared": int,
"no_changes": int,
"errors": int
}
Examples:
# Sync all products
summary = service.sync_all_product_statuses()
# Sync first 50 only
summary = service.sync_all_product_statuses(max_products=50)
# Custom field
summary = service.sync_all_product_statuses(
web_status_field="PM_PUBLISHWEB"
)
All status syncs are logged to def_log table:
SELECT *
FROM def_log
WHERE source = 'shopify'
AND event = 'status_sync'
ORDER BY timestamp DESC
LIMIT 20;
Log fields:
source: "shopify"event: "status_sync"class: "trader.products"sku: Product SKUdetail: "Cleared PM_WEBACTIVE - Shopify status: DRAFT"Check recent syncs:
SELECT
DATE(timestamp) as date,
COUNT(*) as syncs,
SUM(detail LIKE '%DRAFT%') as drafted,
SUM(detail LIKE '%ARCHIVED%') as archived
FROM def_log
WHERE source = 'shopify'
AND event = 'status_sync'
AND timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(timestamp)
ORDER BY date DESC;
Find products with cleared flags:
SELECT
l.sku,
l.detail,
l.timestamp,
p.PM_PRODUCTCODE,
p.PM_WEBACTIVE
FROM def_log l
JOIN trader.products p ON l.sku = p.PM_PRODUCTCODE
WHERE l.source = 'shopify'
AND l.event = 'status_sync'
AND l.detail LIKE '%Cleared%'
ORDER BY l.timestamp DESC
LIMIT 50;
Symptoms: Product is DRAFT in Shopify but flag still set in database
Debug steps:
# 1. Check product status in Shopify
result = service.sync_product_status_from_shopify(sku="PROD-001")
print(result)
# 2. Check database field name
# Make sure you're using the correct field
result = service.sync_product_status_from_shopify(
sku="PROD-001",
web_status_field="PM_PUBLISHWEB" # Try different field names
)
# 3. Check logs
service.debug("Checking logs")
# Look at console output for errors
Verify field name:
-- List all columns in products table
DESCRIBE trader.products;
-- Find web-related columns
SHOW COLUMNS FROM trader.products WHERE Field LIKE '%WEB%';
SHOW COLUMNS FROM trader.products WHERE Field LIKE '%ACTIVE%';
SHOW COLUMNS FROM trader.products WHERE Field LIKE '%PUBLISH%';
Symptoms: Error "SKU not found"
Solution:
# Check if product exists in Shopify tables
sql = """
SELECT *
FROM core.axion.data_shopify_variants
WHERE Sku = 'PROD-001'
"""
rows = service.sql_get_rows(sql)
print(rows)
# If no rows, product hasn't been synced to Shopify yet
# Run initial sync first
service.ComputeSql("PROD-001")
# Then try status sync
result = service.sync_product_status_from_shopify(sku="PROD-001")
Symptoms: Batch sync slows down or errors
Solution:
# Sync in smaller batches
summary1 = service.sync_all_product_statuses(max_products=100)
time.sleep(60) # Wait 1 minute
summary2 = service.sync_all_product_statuses(max_products=100)
# Or spread across multiple runs
# Cron: 0 2 * * * (products 1-100)
# Cron: 0 3 * * * (products 101-200)
Recommendation: Run batch sync off-peak hours (e.g., 2 AM)
# Daily at 2 AM
0 2 * * * cd /project && python ObjServiceFHShopify.py sync-all-statuses
# After staff makes bulk changes in Shopify
# Run sync to update database
summary = service.sync_all_product_statuses()
print(f"Updated {summary['web_flags_cleared']} products")
-- Add status sync to your reports
-- Show products disabled in Shopify
SELECT
p.PM_PRODUCTCODE,
p.PM_DESCRIPTION,
i.status as shopify_status,
i.last_sync
FROM trader.products p
JOIN core.axion.data_shopify_inventory i
ON p.PM_PRODUCTCODE = i.product_id
WHERE i.status IN ('DRAFT', 'ARCHIVED')
ORDER BY i.last_sync DESC;
# Test with single product first
result = service.sync_product_status_from_shopify(
sku="TEST-001",
web_status_field="PM_WEBACTIVE"
)
# Check if it worked
if result["success"] and result["action_taken"] == "cleared_web_flag":
print("✅ Field name is correct!")
else:
print("❌ Try a different field name")
What it does:
When to use:
Configuration:
Benefits:
Created: February 7, 2026
Status: ✅ Complete and Production Ready
Version: 1.0