Database & Migrations

Diátaxis: explanation + how-to. How Project NEXUS structures its database, the two migration systems, and how to add schema changes safely.

The platform runs on MariaDB 10.11 (MySQL-compatible), with Redis 7 for cache/queues and Meilisearch for search indexing.

Multi-tenancy is the first invariant

Project NEXUS is multi-tenant: many communities share one database, isolated by a tenant_id column on tenant-scoped tables. Every query, INSERT, UPDATE, and DELETE on a tenant-scoped table must be scoped by tenant_id.

$tenantId = TenantContext::getId();
$stmt = Database::query(
    "SELECT * FROM users WHERE tenant_id = ? AND status = ?",
    [$tenantId, 'active']
);

Never concatenate SQL; always use prepared statements. For IN (...) clauses, build placeholders with implode(',', array_fill(0, count($ids), '?')) — never pass an array as a single parameter. Every DELETE/UPDATE on a tenant-scoped table must include AND tenant_id = ?.

Two migration systems

System	Location	Status	Count
Laravel migrations	database/migrations/	Primary — use for all new changes	~304
Legacy SQL migrations	migrations/	Historical record — do not add new ones	~264

New schema changes use standard Laravel migrations (php artisan make:migration). Make them idempotent with Schema::hasTable() / Schema::hasColumn() guards so they are safe to re-run:

if (! Schema::hasColumn('users', 'preferred_language')) {
    Schema::table('users', function (Blueprint $table) {
        $table->string('preferred_language', 8)->default('en');
    });
}

When adding foreign keys, check column-type consistency (signed vs unsigned int) against the referenced table, or the FK creation will fail.

The schema dump

database/schema/mysql-schema.sql (~830 KB) is the full current schema plus laravel_migrations table data, committed to git so a new contributor can stand up a working database:

docker compose up -d
docker exec nexus-php-app php artisan migrate   # loads the schema dump, then runs any newer migrations

After running migrations that change the schema, refresh the dump and commit it:

bash scripts/refresh-schema-dump.sh
# then commit the updated database/schema/mysql-schema.sql

Before writing code that queries a table, verify the actual column names against the schema dump or live schema — do not assume. (Several past bugs came from assumed column names; see the GDPR guide's "residual schema-drift caveats" for examples.)

Adding a migration — checklist

1. php artisan make:migration add_foo_to_bar_table
2. Use Schema::hasTable() / Schema::hasColumn() guards for idempotency.
3. Run locally: docker exec nexus-php-app php artisan migrate.
4. Refresh the schema dump: bash scripts/refresh-schema-dump.sh.
5. Commit both the migration file and the updated database/schema/mysql-schema.sql.

Running migrations on production

Laravel migrations run automatically during a blue/green deploy (bluegreen-deploy.sh runs php artisan migrate --force against the new colour before the traffic switch). To run them manually, see DEPLOYMENT.md. Raw SQL migrations use the checked-in make migrate* wrappers.