Table: core.axion.data_shopify_inventory
Purpose: Cache and analyze Shopify product data
Created: February 7, 2026
The data_shopify_inventory table stores a complete snapshot of all Shopify
products retrieved via the GraphQL Admin API. This table is designed for:
CREATE TABLE `core.axion`.data_shopify_inventory (
product_id BIGINT PRIMARY KEY,
graphql_id VARCHAR(255) NOT NULL,
title VARCHAR(500),
description_html TEXT,
status VARCHAR(20) DEFAULT 'ACTIVE',
vendor VARCHAR(255),
product_type VARCHAR(255),
created_at DATETIME,
updated_at DATETIME,
tags TEXT,
variant_count INT DEFAULT 0,
variants JSON,
image_count INT DEFAULT 0,
price_min DECIMAL(10,2),
price_max DECIMAL(10,2),
last_sync DATETIME DEFAULT CURRENT_TIMESTAMP,
promotion_name VARCHAR(100) DEFAULT NULL,
promotion_price VARCHAR(100) DEFAULT NULL,
INDEX idx_graphql_id (graphql_id),
INDEX idx_status (status),
INDEX idx_vendor (vendor),
INDEX idx_last_sync (last_sync)
);
| Field | Type | Description |
|---|---|---|
product_id |
BIGINT | Shopify product ID (numeric, from GID) |
graphql_id |
VARCHAR(255) | Full GraphQL GID (gid://shopify/Product/123) |
title |
VARCHAR(500) | Product title/name |
description_html |
TEXT | Product description (HTML format) |
status |
VARCHAR(20) | ACTIVE, DRAFT, or ARCHIVED |
vendor |
VARCHAR(255) | Brand/vendor name |
product_type |
VARCHAR(255) | Product category/type |
created_at |
DATETIME | When product was created in Shopify |
updated_at |
DATETIME | When product was last updated in Shopify |
tags |
TEXT | Comma-separated product tags |
variant_count |
INT | Number of variants for this product |
variants |
JSON | Full variant data (SKU, price, inventory) |
image_count |
INT | Number of product images |
price_min |
DECIMAL(10,2) | Lowest variant price |
price_max |
DECIMAL(10,2) | Highest variant price |
last_sync |
DATETIME | When this record was last synced from Shopify |
Run the migration script to create the table:
mysql -u username -p core.axion < \
factory.service/package.fullhouse/migration_graphql_2026-01.sql
Use the CLI to retrieve and save products:
# Retrieve all products and save to database
python factory.service/package.fullhouse/ObjServiceFHShopify.py \
list-products --save
# Retrieve first 500 products and save
python factory.service/package.fullhouse/ObjServiceFHShopify.py \
list-products --max 500 --save
# Use larger page size for faster sync
python factory.service/package.fullhouse/ObjServiceFHShopify.py \
list-products --page-size 250 --save
Example queries:
-- Count products by status
SELECT status, COUNT(*) as count
FROM core.axion.data_shopify_inventory
GROUP BY status;
-- Find products by vendor
SELECT product_id, title, variant_count, price_min, price_max
FROM core.axion.data_shopify_inventory
WHERE vendor = 'FULLHOUSE'
ORDER BY title;
-- Products updated in last 7 days
SELECT title, updated_at, status
FROM core.axion.data_shopify_inventory
WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY updated_at DESC;
-- Price range analysis
SELECT
vendor,
COUNT(*) as product_count,
AVG(price_min) as avg_min_price,
AVG(price_max) as avg_max_price
FROM core.axion.data_shopify_inventory
WHERE status = 'ACTIVE'
GROUP BY vendor;
-- Find products with multiple variants
SELECT product_id, title, variant_count
FROM core.axion.data_shopify_inventory
WHERE variant_count > 1
ORDER BY variant_count DESC;
-- Extract variant data from JSON
SELECT
product_id,
title,
JSON_EXTRACT(variants, '$[0].sku') as first_variant_sku,
JSON_EXTRACT(variants, '$[0].price') as first_variant_price
FROM core.axion.data_shopify_inventory
LIMIT 10;
The variants field stores a JSON array of variant objects:
[
{
"id": "gid://shopify/ProductVariant/123",
"sku": "PROD-001",
"price": "999.00",
"compareAtPrice": "1299.00",
"inventoryQuantity": 10
},
{
"id": "gid://shopify/ProductVariant/124",
"sku": "PROD-002",
"price": "899.00",
"compareAtPrice": null,
"inventoryQuantity": 5
}
]
Extract specific variant information:
-- Get all SKUs for a product
SELECT
title,
JSON_EXTRACT(variants, '$[*].sku') as all_skus
FROM core.axion.data_shopify_inventory
WHERE product_id = 12345;
-- Count variants with inventory
SELECT
title,
(
SELECT COUNT(*)
FROM JSON_TABLE(
variants,
'$[*]' COLUMNS(
inventory_qty INT PATH '$.inventoryQuantity'
)
) AS jt
WHERE inventory_qty > 0
) as in_stock_variants
FROM core.axion.data_shopify_inventory;
Re-run the sync to update stale data:
# Full refresh
python factory.service/package.fullhouse/ObjServiceFHShopify.py \
list-products --save
# Check last sync time
SELECT
MIN(last_sync) as oldest_sync,
MAX(last_sync) as newest_sync,
COUNT(*) as total_products
FROM core.axion.data_shopify_inventory;
Remove products that no longer exist in Shopify:
-- Find products not synced in 30 days
SELECT product_id, title, last_sync
FROM core.axion.data_shopify_inventory
WHERE last_sync < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- Delete old records (run carefully!)
DELETE FROM core.axion.data_shopify_inventory
WHERE last_sync < DATE_SUB(NOW(), INTERVAL 90 DAY);
The table includes indexes on:
graphql_id - Fast lookups by GIDstatus - Filter by product statusvendor - Group/filter by vendorlast_sync - Find stale recordsJSON queries can be slower than regular columns. For frequently accessed
variant data, consider extracting to separate columns or tables.
Recommended sync schedule:
from ObjServiceFHShopify import ObjServiceApi
# Retrieve and save products
service = ObjServiceApi()
service.Guid = "VIRTUALTEST"
products = service.retrieve_all_products(page_size=250)
saved = service.save_products_to_inventory(products)
print(f"Saved {saved} products to inventory")
Use this table for reports instead of hitting Shopify API:
# Query local inventory instead of API
sql = """
SELECT product_id, title, price_min, price_max
FROM core.axion.data_shopify_inventory
WHERE status = 'ACTIVE' AND vendor = 'FULLHOUSE'
ORDER BY title
"""
results = service.sql_get_rows(sql)
GRAPHQL_PRODUCTS_QUERY for more variants# Check if table exists
mysql -u username -p -e "SHOW TABLES LIKE 'data_shopify_inventory';" core.axion
# Run sync with debug
python factory.service/package.fullhouse/ObjServiceFHShopify.py \
list-products --save --max 10
-- Validate JSON in variants column
SELECT product_id, title
FROM core.axion.data_shopify_inventory
WHERE NOT JSON_VALID(variants);
Check logs for errors:
Potential improvements:
last_modified_by field for audit traildata_shopify_inventory_history for versioningCreated: February 7, 2026
Updated: February 7, 2026
Status: Production Ready