Plugin databases and migrations

Plugins store their own data in prefixed tables in the NetSeller datasource. Keep SQL in plugin-owned services or repositories, not in providers or views.

Table names and migrations

For plugin ID example.hello, use the table prefix plg_example_hello_. NetSeller builds this prefix from the plugin ID by replacing runs of non-alphanumeric characters with underscores and adding plg_ and a trailing underscore.

Place SQL files under db/migrations/:

example.hello/
  netseller-plugin.json
  db/migrations/
    V1__create_settings.sql
    V2__add_description.sql
  src/

Names follow V<number>__<description>.sql. Use a unique integer for each migration; files run in numeric order. Migration numbers are separate from the manifest’s plugin version.

For a shop-specific setting, V1__create_settings.sql can contain:

CREATE TABLE IF NOT EXISTS plg_example_hello_settings (
    int_setting_id INT NOT NULL AUTO_INCREMENT,
    fk_shop_id INT NOT NULL,
    str_setting_key VARCHAR(120) NOT NULL,
    str_setting_value VARCHAR(255) NOT NULL DEFAULT '',
    PRIMARY KEY (int_setting_id),
    UNIQUE KEY idx_plg_example_hello_settings_scope
        (fk_shop_id, str_setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Migrations may create, alter or drop plugin-owned tables and indexes, and insert, update or delete their data. They cannot modify Core tables. Cross-table SELECT/JOIN migrations, stored routines and references to tables outside the plugin prefix are not supported.

When migrations run

Enabling a plugin applies pending migrations before importing translations and running an optional onPluginEnable(context) provider hook. Only after these steps succeed is the plugin marked enabled.

Applied migrations are recorded with checksums. Never edit an applied migration. Add a new file, such as V2__add_description.sql, instead:

ALTER TABLE plg_example_hello_settings
    ADD str_description VARCHAR(255) NOT NULL DEFAULT '';

A changed checksum blocks activation. Statements run sequentially; a failed migration can leave earlier statements applied. Do not assume a failed activation rolls back schema changes. Inspect the reported error and database state before retrying.

An uploaded update runs activation automatically. For a locally managed package, disable the plugin, deploy the update, reinitialize and enable it again. A page reload alone does not apply migrations. See Installation and updates.

Read and write data

Pass the datasource into your plugin’s service from the controller using request.database.datasourceName. An activation hook receives it as context.datasource. Do not hardcode a deployment’s datasource name or database credentials.

The following method belongs in a plugin-owned service. The caller must supply a shop ID it is authorized to access:

CFML

public string function getGreeting(
    required string datasource,
    required numeric shopId
) {
    if (arguments.shopId lte 0) {
        throw(message = "A valid shop context is required.");
    }

    var rows = queryExecute(
        "SELECT str_setting_value
         FROM plg_example_hello_settings
         WHERE fk_shop_id = :shop_id AND str_setting_key = :setting_key",
        {
            'shop_id' = {
                'value' = arguments.shopId,
                'cfsqltype' = "cf_sql_integer"
            },
            'setting_key' = {
                'value' = "greeting",
                'cfsqltype' = "cf_sql_varchar"
            }
        },
        { 'datasource' = arguments.datasource }
    );

    return rows.recordCount ? rows.str_setting_value[1] : "";
}

Use the same scope and named parameters when saving:

CFML

queryExecute(
    "INSERT INTO plg_example_hello_settings
        (fk_shop_id, str_setting_key, str_setting_value)
     VALUES (:shop_id, :setting_key, :setting_value)
     ON DUPLICATE KEY UPDATE str_setting_value = VALUES(str_setting_value)",
    {
        'shop_id' = { 'value' = arguments.shopId, 'cfsqltype' = "cf_sql_integer" },
        'setting_key' = { 'value' = "greeting", 'cfsqltype' = "cf_sql_varchar" },
        'setting_value' = { 'value' = arguments.greeting, 'cfsqltype' = "cf_sql_varchar" }
    },
    { 'datasource' = arguments.datasource }
);

This save fragment assumes a service method with validated datasource, shopId and greeting arguments. Limit the greeting to 255 characters. The calling route must enforce permission and CSRF protection. Never interpolate submitted values into SQL.

Shop and customer scope

• Store a shop identifier for shop-specific records; apply it to reads, updates and deletes.
• Validate the selected shop against the caller’s allowed context. A positive ID alone is not authorization.
• For customer-owned records, include the customer or company scope as well. A record ID alone is not an ownership check.
• Keep global installation settings separate from shop-specific settings.

For customer data, store the shop and owner identifiers with each record and verify that scope before making changes.

For an admin shop selector, the documented pluginShopContextService.getActiveShops(datasource) supplies shopId, key, name and defaultLanguageId; it does not replace your route’s access checks. Access shared commerce data through documented plugin services rather than querying Core tables.

Disable and delete

Disabling a plugin keeps its stored data. Deleting plugin data is a separate operation, available only while the plugin is disabled. It drops tables with the plugin’s prefix and removes migration records, plugin permissions, translations and installation state.

Do not use data deletion as an update procedure. Keep the plugin ID and table prefix stable across releases.

Example: an accounting integration

The bexio integration illustrates how these rules fit together:

Data	Storage and scope
OAuth connection	A plugin-owned connection record for each shop.
Access and refresh tokens	Encrypted values in the plugin’s connection table.
Exported documents	A mapping keyed by shop, order and document type, containing the external document ID.
Order contents	Read through the public order-export snapshot service, rather than direct queries against Core order tables.

A stored document mapping lets a later export find and update the existing external document instead of creating another one. Keep the shop in the lookup even when the order ID is already known.

Encrypt credentials before storing them

NetSeller exposes a credential vault to plugins. This fragment runs inside a request-bound plugin service method receiving a validated plainTextToken argument:

CFML

var vault = request.getApplicationService("pluginCredentialVaultService");
if (!vault.isConfigured()) {
    throw(message = "Credential encryption is not configured.");
}
var encryptedToken = vault.encryptCredential(arguments.plainTextToken);

Pass encryptedToken to your plugin-owned repository for storage. When making an authorized outbound request, read that stored value and use vault.decryptCredential(encryptedToken). The vault encrypts and decrypts; it does not persist data. Do not put plaintext tokens in views or logs.

The installation operator configures the vault’s CONFIG_ENCRYPTION_KEY. Keep that deployment key outside plugin packages and source control.