Database Schema

Database Schema

The setup wizard creates ten tables with Sequelize migrations from the migrations/ folder. This page lists each table, how the tables relate, and their columns, so you can write SQL queries and connect BI tools.

Records are stored with the same IDs and field names that Selldone uses, so the ERP response structure is a useful reference for the meaning of order, product and variant fields.

Tables

Table	Filled by	Contents
key_values	Clone Sheet	Internal settings: auth_token, selected_shop_id and <resource>_last_updated_at sync times.
users	Save & Connect	Your Selldone user profile.
shops	Save & Connect	The connected shop.
categories	Sync	Product categories.
products	Sync	Products.
product_variants	Sync	Product variants.
customers	Sync	Shop customers.
shop_data	Sync	Shop statistics and counters.
baskets	Sync	Orders (baskets).
basket_items	Sync	Items in each basket.

Relationships

shops ─┬─< categories ─< products (category_id, SET NULL)
       ├─< products ─< product_variants
       ├─< customers
       ├─< shop_data
       └─< baskets ─< basket_items

Foreign key	References	On delete
categories.shop_id	shops.id	CASCADE
products.shop_id	shops.id	CASCADE
products.category_id	categories.id	SET NULL
customers.shop_id	shops.id	CASCADE
shop_data.shop_id	shops.id	—
product_variants.shop_id	shops.id	—
product_variants.product_id	products.id	—
baskets.shop_id	shops.id	—
basket_items.basket_id	baskets.id	—

basket_items.product_id and basket_items.variant_id hold product and variant IDs without a foreign key constraint.

Columns

key_values

id, key, value, created_at, updated_at

users

id, name, email, phone, email_verified_at, socials_login, preferences, interest, subscribed, block_at, block_hours, ban, meta, deleted_at, created_at, updated_at, verified, source

shops

id, user_id, model, name, home, language, title, description, icon, fav, official, active, restriction, license, domain, capacity, expire_at, info, support_mode, support, address_verified, phone_verified, gold, options, penalty, purge_at, currencies, footer, local, sub, countries, lottery, tax, layout_id, layout_version, theme, hyper, avocado, ribbon, marketplace, drop_shipping_score, drop_shipping_sells, drop_shipping_products, drop_shipping, deleted_at, created_at, updated_at, client_id, meta, agency_meta, translations, filters, engine

model is one of normal, wholesaler, marketplace, franchise, dropshipping; license is one of FREE, STARTUP, COMPANY, ENTERPRISE.

categories

id, title, name, description, icon, star, order, visits, products, categories, filters, note, theme, augment, page_id, cluster_id, translations, engine, shop_id, parent_id, connect_id, vendor_id, deleted_at, created_at, updated_at

products

id, title, type, title_en, sku, mpn, status, quantity, price, currency, commission, discount, dis_start, dis_end, unit, unit_float, price_input, price_label, valuation_id, pricing, gtin, gpc, hsn, condition, brand, warranty, spec, spec_order, pros, cons, icon, message, outputs, inputs, variants, blog, category_id, shortcuts, limit_min, limit_max, lead, extra, style, visits, rate, rate_count, for_available, for_auction, sells, return_warranty, original, best_seller, best_content, slug, video, ar, note, badges, repository_id, reselling, parent_id, shipping, reselling_shops, reselling_count, warranty_id, return_policy_id, guide_id, shipping_id, tax_id, map_id, tags, ribbon, meta, audit, audit_at, action, external, theme, augment, page_id, locations, thresholds, cluster_id, translations, attributes, property_set_id, order, shop_id, connect_id, vendor_id, officer, deleted_at, created_at, updated_at

product_variants

id, shop_id, product_id, sku, mpn, gtin, color, style, volume, weight, pack, type, pricing, price, currency, commission, discount, dis_start, dis_end, price_label, quantity, image, enable, lead, extra, parent_id, ar, meta, deleted_at, created_at, updated_at

customers

id, shop_id, user_id, source, name, email, phone, segments, address, billing, level, subscribed, currency, created_at, updated_at, login_at, purchase_at, chips, birthday, sex, access, banned, notes, meta, country, deleted_at

shop_data

Counters per shop, including per product type (virtual, physical, file, service, subscription): total_products_*, sell_products_*, send_products_*, view_products_*, and order_*_added / order_*_checkout. Other columns: id, shop_id, view_avocados, request_avocados, sell_avocados, hyper_views, hyper_new_carts, hyper_checkouts, hyper_pays, hyper_rejects, views, amp, likes, powers, favorites, comments, comments_approved, ratings, wishlist, users, users_add, social, products_add, products_remove, categories_add, categories_remove, customers_add, customers_remove, pages_add, pages_remove, new_visitors, returning_visitors, page_views, ds_count, ds_payments, ds_confirms, ds_sends, ds_deliveries, ds_cancels, ds_rejects, created_at, updated_at

baskets

id, label, shop_id, user_id, customer_id, code, guest_email, type, stack, status, reject, reject_at, receiver_info, delivery_info, billing, reserved_at, price, currency, delivery_price, discount, tax, tax_shipping, tax_included, delivery_state, delivery_at, payment_type, payment_id, session, channel, discount_id, coupon_id, lottery_id, link_id, affiliate_id, email_id, chat, meta, form, subscription_id, language, deleted_at, created_at, updated_at

basket_items

id, basket_id, product_id, variant_id, connect_id, message, preferences, count, count_adjustment, price, currency, dis, offer_id, offer_count, offer_amount, cross_dis, tax, check, fulfillment_id, vendor_id, vendor_product_id, vendor_price, extra_pricing_id, subscription_price_id, created_at, updated_at

Example queries

-- Paid orders per month
SELECT DATE_FORMAT(reserved_at, '%Y-%m') AS month, COUNT(*) AS orders, SUM(price) AS revenue
FROM baskets
WHERE status = 'Payed'
GROUP BY month
ORDER BY month;

-- Best-selling products by quantity
SELECT p.id, p.title, SUM(bi.count) AS units
FROM basket_items bi
JOIN baskets b ON b.id = bi.basket_id AND b.status = 'Payed'
JOIN products p ON p.id = bi.product_id
GROUP BY p.id, p.title
ORDER BY units DESC
LIMIT 20;

-- Low stock
SELECT id, title, sku, quantity FROM products WHERE quantity < 10 ORDER BY quantity;

Next steps

• Exploring and Visualizing
• Syncing Data
• ERP response structure