Schema · Entity relationship diagram
Stay AI tables and relationships
Shopify subscriptions from Stay AI (formerly Retextion): subscriptions with status, cancellation, dunning and churn risk, subscription line items, subscription orders and their line items, customers with LTV, the subscription product catalog and selling plan groups.
ConnectPaste an API key
Default schema
stay_aiSource API docsdocs.stay.ai
DestinationsAmazon Redshift, Google BigQuery, Snowflake, PostgreSQL, Amazon S3
- Column names follow Stay AI's own API field names. Shopify ids (order, customer, product, variant, subscription contract) are stored as numbers, so they join to your Shopify tables.
- Emails, names, phone numbers and street addresses load empty unless you turn on personal data; customer ids, city, state and country are kept.
- Retention (cancel-flow) sessions, churn reports and customer-portal events are not included: Stay AI only offers them as emailed or webhook-delivered exports, not through its read API.
Primary keyForeign keyReferencesJoins on (not a key)
Drag to pan, scroll or pinch to zoom, click a table to see what it joins to.
Table reference
8 tables · click a table for its columnssubscriptions47 columns · Subscriptionsshow in diagram
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. Stay AI's own subscription id; subscription_id is the Shopify subscription contract id. | varchar(64) | PK | Primary key. Stay AI's own subscription id; subscription_id is the Shopify subscription contract id. |
| subscription_id | bigint | ||
| customer_idJoins to customers.shopify_customer_id. | bigint | FK | Joins to customers.shopify_customer_id. |
| varchar(512) | |||
| first_name | varchar(256) | ||
| last_name | varchar(256) | ||
| status | varchar(32) | ||
| billing_status | varchar(64) | ||
| created_at | timestamp | ||
| updated_at | timestamp | ||
| last_charge_date | timestamp | ||
| next_billing_date | timestamp | ||
| paused_until | timestamp | ||
| cancelled_at | timestamp | ||
| churned_at | timestamp | ||
| cancellation_reason | varchar(2048) | ||
| is_in_dunning | boolean | ||
| dunning_started_at | timestamp | ||
| dunning_exited_at | timestamp | ||
| failed_payment_billing_attempts | integer | ||
| out_of_stock_billing_attempts | integer | ||
| price | decimal(18,4) | ||
| delivery_price | decimal(18,4) | ||
| currency | varchar(8) | ||
| order_interval_frequency | integer | ||
| order_interval_unit | varchar(16) | ||
| completed_orders_count | integer | ||
| total_orders_count | integer | ||
| prepaid | boolean | ||
| prepaid_next_delivery_date | timestamp | ||
| prepaid_shipments_remaining | integer | ||
| churn_risk_status | varchar(64) | ||
| churn_risk | decimal(18,6) | ||
| line_item_count | integer | ||
| delivery_first_name | varchar(256) | ||
| delivery_last_name | varchar(256) | ||
| delivery_company | varchar(512) | ||
| delivery_address1 | varchar(512) | ||
| delivery_address2 | varchar(512) | ||
| delivery_city | varchar(256) | ||
| delivery_province | varchar(256) | ||
| delivery_province_code | varchar(16) | ||
| delivery_zip | varchar(64) | ||
| delivery_country | varchar(128) | ||
| delivery_country_code | varchar(8) | ||
| delivery_phone | varchar(128) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
subscription_line_items15 columns · Subscriptionsshow in diagram
Primary key: _jsdata_key
| Column | Type | Key | Description |
|---|---|---|---|
| _jsdata_keyPrimary key. | varchar(32) | PK | Primary key. |
| stay_subscription_idReferences subscriptions.id. | varchar(64) | FK | References subscriptions.id. |
| subscription_id | bigint | ||
| line_id | varchar(128) | ||
| shopify_product_id | bigint | ||
| shopify_variant_idJoins to product_variants.variant_id. | bigint | FK | Joins to product_variants.variant_id. |
| product_title | varchar(1024) | ||
| variant_title | varchar(1024) | ||
| sku | varchar(256) | ||
| quantity | integer | ||
| unit_price | decimal(18,4) | ||
| subtotal_price | decimal(18,4) | ||
| is_one_time | boolean | ||
| subscription_updated_atThe parent subscription's updated_at when this line was last seen. Older than subscriptions.updated_at means the line was removed in Stay AI. | timestamp | The parent subscription's updated_at when this line was last seen. Older than subscriptions.updated_at means the line was removed in Stay AI. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
customers22 columns · Subscriptionsshow in diagram
Primary key: stay_customer_id
| Column | Type | Key | Description |
|---|---|---|---|
| stay_customer_idPrimary key. | varchar(64) | PK | Primary key. |
| shopify_customer_id | bigint | ||
| varchar(512) | |||
| first_name | varchar(256) | ||
| last_name | varchar(256) | ||
| phone | varchar(128) | ||
| status | varchar(32) | ||
| created_at | timestamp | ||
| updated_at | timestamp | ||
| cancelled_at | timestamp | ||
| ltv | decimal(18,4) | ||
| orders_on_stay_count | integer | ||
| subscription_count | integer | ||
| subscription_ids | varchar(65535) | ||
| origin_stay_order_id | varchar(64) | ||
| origin_order_id | bigint | ||
| origin_order_name | varchar(128) | ||
| origin_order_total_price | decimal(18,4) | ||
| origin_order_currency | varchar(8) | ||
| origin_order_processed_at | timestamp | ||
| origin_order_created_at | timestamp | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
orders42 columns · Ordersshow in diagram
Primary key: _jsdata_key
| Column | Type | Key | Description |
|---|---|---|---|
| _jsdata_keyPrimary key. md5(order_id, stay_subscription_id). One row per Stay order record: a checkout that starts two subscriptions appears twice with the same order_id and totals, so count distinct order_id when summing revenue. | varchar(32) | PK | Primary key. md5(order_id, stay_subscription_id). One row per Stay order record: a checkout that starts two subscriptions appears twice with the same order_id and totals, so count distinct order_id when summing revenue. |
| order_id | bigint | ||
| order_name | varchar(128) | ||
| order_number | integer | ||
| customer_idJoins to customers.shopify_customer_id. | bigint | FK | Joins to customers.shopify_customer_id. |
| stay_customer_idReferences customers.stay_customer_id. | varchar(64) | FK | References customers.stay_customer_id. |
| subscription_id | bigint | ||
| stay_subscription_idReferences subscriptions.id. | varchar(64) | FK | References subscriptions.id. |
| source | varchar(64) | ||
| created_at | timestamp | ||
| updated_at | timestamp | ||
| fulfillment_status | varchar(64) | ||
| currency | varchar(8) | ||
| total_price | decimal(18,4) | ||
| cart_discount_amount | decimal(18,4) | ||
| current_total_tax | decimal(18,4) | ||
| total_shipping_price | decimal(18,4) | ||
| tags | varchar(4096) | ||
| line_item_count | integer | ||
| line_items_price | decimal(18,4) | ||
| line_discount | decimal(18,4) | ||
| line_items_price_discounted | decimal(18,4) | ||
| shipping_price | decimal(18,4) | ||
| shipping_discount | decimal(18,4) | ||
| shipping_price_discounted | decimal(18,4) | ||
| order_discount | decimal(18,4) | ||
| total_discount | decimal(18,4) | ||
| total_tax | decimal(18,4) | ||
| shipping_source | varchar(64) | ||
| shipping_first_name | varchar(256) | ||
| shipping_last_name | varchar(256) | ||
| shipping_company | varchar(512) | ||
| shipping_address1 | varchar(512) | ||
| shipping_address2 | varchar(512) | ||
| shipping_city | varchar(256) | ||
| shipping_province | varchar(256) | ||
| shipping_province_code | varchar(16) | ||
| shipping_zip | varchar(64) | ||
| shipping_country | varchar(128) | ||
| shipping_country_code | varchar(8) | ||
| shipping_phone | varchar(128) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
order_line_items17 columns · Ordersshow in diagram
Primary key: _jsdata_key
| Column | Type | Key | Description |
|---|---|---|---|
| _jsdata_keyPrimary key. | varchar(32) | PK | Primary key. |
| order_idJoins to orders.order_id (A checkout that starts two subscriptions is two rows in orders with the same order_id; its lines are stored once.). | bigint | FK | Joins to orders.order_id (A checkout that starts two subscriptions is two rows in orders with the same order_id; its lines are stored once.). |
| line_id | bigint | ||
| shopify_product_id | bigint | ||
| shopify_variant_idJoins to product_variants.variant_id. | bigint | FK | Joins to product_variants.variant_id. |
| product_title | varchar(1024) | ||
| variant_title | varchar(1024) | ||
| sku | varchar(256) | ||
| quantity | integer | ||
| subtotal_price | decimal(18,4) | ||
| original_line_price | decimal(18,4) | ||
| line_discount | decimal(18,4) | ||
| is_one_time | boolean | ||
| subscription_id | bigint | ||
| custom_attributesShopify line item properties as JSON; keys that look personal (email, phone, name, address) are blanked unless personal data is included. | varchar(65535) | Shopify line item properties as JSON; keys that look personal (email, phone, name, address) are blanked unless personal data is included. | |
| order_updated_at | timestamp | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
products9 columns · Catalogshow in diagram
Primary key: product_id
| Column | Type | Key | Description |
|---|---|---|---|
| product_idPrimary key. | bigint | PK | Primary key. |
| title | varchar(1024) | ||
| status | varchar(32) | ||
| image_url | varchar(2048) | ||
| is_bundle_parent | boolean | ||
| carousel_position | integer | ||
| sort_position | integer | ||
| variant_count | integer | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
product_variants19 columns · Catalogshow in diagram
Primary key: variant_id
| Column | Type | Key | Description |
|---|---|---|---|
| variant_idPrimary key. | bigint | PK | Primary key. |
| product_idReferences products.product_id. | bigint | FK | References products.product_id. |
| title | varchar(1024) | ||
| status | varchar(32) | ||
| image_url | varchar(2048) | ||
| price | decimal(18,4) | ||
| is_out_of_stock | boolean | ||
| position | integer | ||
| enable_subscription_in_cp | boolean | ||
| enable_one_time_in_cp | boolean | ||
| subscription_discount_type | varchar(32) | ||
| subscription_discount_amount | varchar(64) | ||
| one_time_discount_type | varchar(32) | ||
| one_time_discount_amount | varchar(64) | ||
| subscription_markdown | decimal(18,4) | ||
| one_time_markdown | decimal(18,4) | ||
| subscription_price | decimal(18,4) | ||
| one_time_price | decimal(18,4) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
selling_plan_groups18 columns · Catalogshow in diagram
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | varchar(64) | PK | Primary key. |
| shopify_selling_plan_group_id | bigint | ||
| custom_label | varchar(1024) | ||
| custom_description | varchar(4096) | ||
| group_type | varchar(32) | ||
| bundle_line_type | varchar(32) | ||
| bundle_minimum | integer | ||
| bundle_maximum | integer | ||
| skip_one_product_enabled | boolean | ||
| cutoff_days | integer | ||
| anchor_calculation_use_billing_interval | boolean | ||
| allow_gifting | boolean | ||
| prepaid_allow_renewal | boolean | ||
| created_at | timestamp | ||
| updated_at | timestamp | ||
| anchors | varchar(65535) | ||
| bundle_map | varchar(65535) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |