Database Schema

Complete database schema overview for the Ecomdrop IA Connector

Overview

The Ecomdrop IA Connector uses MySQL 8.0 as its database engine, hosted on a VPS. The schema is managed through Prisma ORM, providing type-safe database access and automatic migrations.

Database connection is configured via the DATABASE_URL environment variable pointing to MySQL 8.0 on VPS (31.97.135.241:3306).

Entity Relationship Diagram

Database Tables

Session

Stores Shopify OAuth session data for authenticated store connections.

id · string · required

Primary key - Unique session identifier

shop · string · required

Shop domain (max 255 chars) - Indexed for fast lookups

state · string · required

OAuth state parameter (max 255 chars)

isOnline · boolean · default: "false"

Whether this is an online (user) or offline (app) session

scope · text

OAuth scopes granted to the app

expires · datetime

Session expiration timestamp (for online sessions)

accessToken · text · required

Shopify access token for API calls

userId · bigint

Shopify user ID (for online sessions)

firstName · string

User’s first name (max 255 chars)

lastName · string

User’s last name (max 255 chars)

email · string

User’s email address (max 255 chars)

accountOwner · boolean · default: "false"

Whether the user is the shop owner

locale · string

User’s locale preference (max 10 chars)

collaborator · boolean

Whether the user is a collaborator

emailVerified · boolean

Whether the user’s email is verified

Indexes:

• Session_shop_idx on shop - For efficient shop-based queries

---

ShopConfiguration

Stores platform configuration and API credentials for each connected shop.

id · string · required

Primary key - UUID generated automatically

shop · string · required

Shop domain (max 255 chars) - Unique constraint

ecomdropApiKey · text

Ecomdrop API key for flow automation

nuevoPedidoFlowId · string

Ecomdrop flow ID for new orders (max 255 chars)

carritoAbandonadoFlowId · string

Ecomdrop flow ID for abandoned carts (max 255 chars)

dropiStoreName · string

Dropi store identifier (max 255 chars)

dropiCountry · string

Dropi country code (max 10 chars)

dropiToken · text

Dropi authentication token

createdAt · datetime · default: "now()"

Record creation timestamp

updatedAt · datetime

Record last update timestamp (auto-updated)

Indexes:

• ShopConfiguration_shop_key on shop - Unique constraint
• ShopConfiguration_shop_idx on shop - For efficient lookups

---

ProductAssociation

Tracks product mappings between Dropi and Shopify catalogs.

id · string · required

Primary key - UUID generated automatically

shop · string · required

Shop domain (max 255 chars)

dropiProductId · string · required

Dropi product identifier (max 255 chars)

shopifyProductId · string · required

Shopify product ID (max 255 chars)

dropiProductName · string

Product name from Dropi (max 500 chars)

shopifyProductTitle · string

Product title in Shopify (max 500 chars)

importType · string · required

Type of import performed (max 50 chars)

dropiVariations · text

JSON data storing variant mappings

saveDropiName · boolean · default: "true"

Whether to save Dropi product name to Shopify

saveDropiDescription · boolean · default: "true"

Whether to save Dropi description to Shopify

customPrice · string

Custom pricing rule or value (max 50 chars)

useSuggestedBarcode · boolean · default: "false"

Whether to use Dropi’s suggested barcode

saveDropiImages · boolean · default: "true"

Whether to import product images from Dropi

createdAt · datetime · default: "now()"

Record creation timestamp

updatedAt · datetime

Record last update timestamp (auto-updated)

Indexes:

• ProductAssociation_shop_dropiProductId_shopifyProductId_key - Composite unique constraint
• ProductAssociation_shop_idx on shop
• ProductAssociation_dropiProductId_idx on dropiProductId
• ProductAssociation_shopifyProductId_idx on shopifyProductId

The composite unique constraint ensures each Dropi product can only be associated with a specific Shopify product once per shop.

---

AIConfiguration

Stores AI agent configuration and knowledge base for each shop.

id · string · required

Primary key - UUID generated automatically

shop · string · required

Shop domain (max 255 chars) - Unique constraint

agentName · string

Custom name for the AI agent (max 255 chars)

companyName · string

Store/company name (max 255 chars)

companyDescription · text

Business description for AI context

paymentMethods · text

Accepted payment methods information

companyPolicies · text

Store policies (returns, shipping, etc.)

faq · text

Frequently asked questions for pre-sale

postSaleFaq · text

Post-sale support questions and answers

rules · text

Custom rules and guidelines for AI behavior

notifications · text

Notification preferences and settings

createdAt · datetime · default: "now()"

Record creation timestamp

updatedAt · datetime

Record last update timestamp (auto-updated)

Indexes:

• AIConfiguration_shop_key on shop - Unique constraint
• AIConfiguration_shop_idx on shop - For efficient lookups

Database Configuration

Prisma Schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

Character Sets and Collation

All tables use:

• Character Set: utf8mb4
• Collation: utf8mb4_unicode_ci

This ensures full Unicode support including emojis and international characters.

Migration History

20250105000000_init_mysql - Initial Schema

Initial database schema creation including all four core tables:

• Session table with OAuth data
• ShopConfiguration for platform settings
• ProductAssociation for product mappings
• AIConfiguration for AI agent setup

All indexes and constraints were created in this migration.

Best Practices

Always use Prisma Client for database operations to ensure type safety and proper connection pooling.

Indexing Strategy: The schema includes indexes on frequently queried fields (shop domain) and foreign key relationships for optimal query performance.

Related Documentation

• Prisma Models - Detailed model documentation