Database & migrations

A module that stores data brings its own tables — via Laravel migrations inside the module directory. This page explains where migrations live, how tables must be named, and why a migration can pass the test suite and still fail on the real database.

Which database runs where

The application runs on MySQL / MariaDB in production and staging. All modules share this single database.

SQLite is used only in the test environment (:memory:), so the test suite needs no external server. The split matters: SQLite is considerably more permissive than MySQL. A migration that passes vendor/bin/phpunit can still fail on the live database. The most common trap is the 64-character limit below — write migrations against MySQL’s rules, not SQLite’s.

Where migrations live

Place migrations in your module’s database/migrations/ directory:

modules/my-module/
  database/
    migrations/
      2026_06_01_000001_create_mod_my_module_documents_table.php
      2026_06_01_000002_create_mod_my_module_settings_table.php

You do not need to run migrations manually: schneespur:modules-sync runs them automatically after installation and updates, and schneespur:modules-remove rolls them back before deletion. The lifecycle behind this is described under Module lifecycle.

Naming tables: the mod prefix

Prefix every table with mod_{module_slug}_ to avoid collisions with core tables and other modules:

Schema::create('mod_documents_entries', function (Blueprint $table) {
    // ...
});

Good	Bad
mod_documents_entries	documents
mod_telegram_messages	telegram_messages
mod_billing_invoices	invoices

The prefix is not just cosmetic: it makes each table unambiguously attributable to a module, and allows the system to cleanly remove exactly that module’s tables on uninstall.

The 64-character identifier limit (MySQL/MariaDB)

MySQL and MariaDB cap every identifier — tables, columns, and indexes — at 64 characters. Exceeding it throws:

SQLSTATE[42000]: ... 1059 Identifier name '...' is too long

The danger is auto-generated index names. When you do not name an index explicitly, Laravel builds the name as {table}_{col1}_{col2}_..._{type}. With the mandatory mod_{slug}_ table prefix, a two-column index on a long table easily overshoots 64 characters — and SQLite silently accepts it, so your tests stay green while production breaks.

A real example — the failure this guide was written for:

mod_telegram_enrollment_tokens_notifiable_type_notifiable_id_index
└──────────────── 66 characters → rejected by MySQL (errno 1059) ──┘

Rule: always pass an explicit, short index or constraint name as the last argument whenever the auto-name could be long — on compound indexes, unique, morphs, and foreign keys on prefixed tables:

// ❌ Auto-name → 66 chars → fails on MySQL, passes on SQLite
$table->index(['notifiable_type', 'notifiable_id']);
$table->unique(['event_type', 'recipient_group']);

// ✅ Explicit short name → safe everywhere
$table->index(['notifiable_type', 'notifiable_id'], 'tg_enroll_notifiable_idx');
$table->unique(['event_type', 'recipient_group'], 'tg_rules_event_group_unq');

Short-name convention: {abbrev}_{purpose}_{idx|unq|fk} — drop the verbose mod_{slug}_ prefix and use a recognisable module abbreviation (tg_ for telegram, doc_ for documents, …). Single-column indexes auto-named like {table}_{col}_index are usually safe; it is the compound ones that bite.

Tip: add a guard test that lists every created index and asserts strlen($name) <= 64. SQLite exposes them via SELECT name FROM sqlite_master WHERE type='index', so the check runs in the normal test suite and catches the limit before a real MySQL deploy does.

A complete migration

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('mod_documents_entries', function (Blueprint $table) {
            $table->id();
            $table->foreignId('customer_id')->constrained('customers')->cascadeOnDelete();
            $table->foreignId('uploaded_by')->nullable()->constrained('users')->nullOnDelete();
            $table->string('title');
            $table->string('file_path');
            $table->string('mime_type');
            $table->unsignedBigInteger('file_size');
            $table->string('category')->default('general');
            $table->string('visibility')->default('admin'); // admin, accountant, customer
            $table->date('expires_at')->nullable();
            $table->timestamps();

            // Compound index: explicit short name to stay under MySQL's 64-char limit
            $table->index(['customer_id', 'visibility'], 'doc_customer_visibility_idx');
            $table->index('category'); // single column → auto-name is safe
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('mod_documents_entries');
    }
};

Core tables reference

These tables exist in the core application. Your module can reference them via foreign keys:

Table	Model	Key fields
users	User	id, name, email, role
customers	Customer	id, name, email, portal_enabled
customer_objects	CustomerObject	id, customer_id, name, street, lat, lon
service_jobs	Job	id, customer_id, user_id, vehicle_id, type, started_at, ended_at
work_shifts	WorkShift	id, user_id, started_at, ended_at
vehicles	Vehicle	id, name, license_plate
gps_points	GpsPoint	id, user_id, job_id, lat, lon, timestamp
weather_snapshots	WeatherSnapshot	id, job_id, moment, temperature, etc.
job_photos	JobPhoto	id, job_id, file_path
job_audits	JobAudit	id, job_id, action, old_values, new_values
settings	Setting	key, value, type
roles	Role	id, slug, name
permissions	Permission	id, slug, name, group
role_permission	—	role_id, permission_id
role_user	—	role_id, user_id
modules	Module	id, slug, version, enabled
module_api_tokens	ModuleApiToken	id, module_slug, token_hash
mod_logs	ModLog	id, module_slug, level, message
notification_logs	NotificationLog	id, notifiable_type, notifiable_id, channel
alert_dismissals	AlertDismissal	id, job_id, alert_type
monthly_statistics	MonthlyStatistic	id, year, month
driver_dsgvo_confirmations	DsgvoConfirmation	id, driver_id, confirmed_at
owntracks_credential_events	OwntracksCredentialEvent	id, driver_id, event

The core data model is described in more detail under Core data model.

The Setting model

The Setting model provides key-value storage for module configuration, so you do not need a dedicated table for simple settings:

use App\Models\Setting;

// Read (with optional default)
$value = Setting::get('my-module.api_key');
$value = Setting::get('my-module.enabled', false);

// Write (with explicit type)
Setting::set('my-module.api_key', 'abc123');
Setting::set('my-module.enabled', true, 'bool');
Setting::set('my-module.config', ['a' => 1], 'json');
Setting::set('my-module.retries', 5, 'int');

Supported types: string, int, bool, json

Type coercion happens automatically on read:

• 'bool' → casts '1'/'true' to true, else false
• 'int' → casts to integer
• 'json' → JSON-decodes to array

Initialising settings

Define defaults with ModuleManager::registerSettings() in your module’s ServiceProvider. Existing values are never overwritten — an update therefore brings new defaults without destroying the operator’s configuration:

app(ModuleManager::class)->registerSettings('my-module', [
    'api_key' => '',           // string
    'enabled' => true,         // bool
    'max_retries' => 3,        // int
    'config' => ['mode' => 'auto'], // json
]);

Cleaning up settings

When a module is removed, ModuleManager::cleanupSettings() deletes all settings whose key starts with the module’s slug prefix. That prefix is the reason cleanup works cleanly:

$manager->cleanupSettings('my-module');
// Deletes: my-module.api_key, my-module.enabled, my-module.max_retries, etc.

Setting registration in the ServiceProvider is described in context under ServiceProvider.

Eloquent models in modules

Modules can define their own Eloquent models. The $table property points at the prefixed table, and relationships reach into core models:

namespace Schneespur\Module\Documents\Models;

use Illuminate\Database\Eloquent\Model;
use App\Models\Customer;

class Document extends Model
{
    protected $table = 'mod_documents_entries';

    protected $fillable = [
        'customer_id', 'uploaded_by', 'title', 'file_path',
        'mime_type', 'file_size', 'category', 'visibility', 'expires_at',
    ];

    protected $casts = [
        'file_size' => 'integer',
        'expires_at' => 'date',
    ];

    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }

    public function uploader()
    {
        return $this->belongsTo(\App\Models\User::class, 'uploaded_by');
    }
}

MySQL / MariaDB considerations

• 64-character limit on all identifiers — name compound indexes and unique constraints explicitly (see above). This is the most common cause of “works in tests, fails on deploy”.
• Give every indexed string() column a length. An indexed VARCHAR(255) under utf8mb4 can exceed MySQL’s index-byte limit; cap such columns (e.g. $table->string('token', 64)).
• Use the json column type for structured data — MariaDB maps it to LONGTEXT with a JSON check, MySQL has a native type. Do not hand-roll text plus manual encoding.
• Foreign keys require both tables to use the InnoDB engine (the default) and matching column types (unsignedBigInteger ↔ id()).
• ALTER TABLE is supported, but column changes via ->change() still require doctrine/dbal to be installed.

Writing migrations against the test database (SQLite)

The test suite runs on sqlite::memory: for speed, but SQLite is more permissive than MySQL. These cases pass on SQLite and fail in production — watch for them:

• Over-long identifiers — SQLite accepts any length; MySQL rejects anything over 64 characters (see above).
• SELECT ... FOR UPDATE — SQLite ignores row locks, so concurrency bugs hide. Verify locking paths against real MySQL in CI.
• Type strictness — SQLite is loosely typed; MySQL enforces column types and lengths.
• UNIQUE + NULL — how NULL behaves inside a unique index differs across engines; do not rely on it.

Rule of thumb: author migrations to MySQL’s rules, and treat a green SQLite result as necessary but not sufficient.