Database setup & migrations

Zodiac Tech CRM stores everything in one MySQL database (default name leadhub, utf8mb4). This page covers creating it on shared hosting, wiring .env, running migrations and seeders, and the separate database the automated tests use.

Who this is for#

• Super-admin / hosting admin — first install or moving hosts.
• Developer — running the app locally or preparing a release.
• Support — "SQLSTATE" connection errors and migration questions.

Prerequisites: cPanel access (MySQL Databases feature) or a local MySQL/MariaDB server.

Step 1 — Create the database and a dedicated user (cPanel)#

1. cPanel → MySQL Databases.
2. Create New Database: leadhub. cPanel will show the real name prefixed with your account, e.g. youruser_leadhub.
3. Add New User: e.g. leadhub with a strong generated password. Real username: youruser_leadhub.
4. Add User to Database → tick ALL PRIVILEGES → Make Changes.

Expected result: you hold four values: database name, username (both account-prefixed), password, and the host (localhost on cPanel).

Step 2 — Point .env at it#

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=youruser_leadhub
DB_USERNAME=youruser_leadhub
DB_PASSWORD=your-strong-password

Notes:

• On cPanel, DB_HOST=localhost and 127.0.0.1 both normally work; if one fails, try the other. Some hosts require localhost:/var/run/mysqld/mysqld.sock.
• The installer writes these values for you — edit .env directly only if you skipped the installer or are changing servers.
• Never commit .env; it is blocked from web access by the shipped .htaccess rules.

Step 3 — Run the migrations#

Via installer (recommended on shared hosting): the browser installer's migrate step does this for you.

Via Terminal:

cd ~/crm-app
php artisan migrate --force

--force is required because APP_ENV=production; without it Laravel asks for interactive confirmation and a cron/CI context would hang.

On a machine with the exact production config, you can also export/import:

# Export (dev machine)
mysqldump -u youruser -p --no-tablespaces youruser_leadhub > leadhub.sql

# Import on the server (phpMyAdmin → Import, or CLI)
mysql -u youruser -p youruser_leadhub < leadhub.sql

For importing a generated dump on cPanel without SSH, phpMyAdmin → Import handles multi-MB files. Files above the upload limit need splitting or CLI.

Expected result: php artisan migrate:status lists every migration as Ran.

Option B — Import an existing database (moving hosts or pre-loaded data)#

Use this path when the database already contains data — a host move, a restore, or an install delivered with pre-loaded content. For a brand-new empty install, stay with the installer (cPanel walkthrough).

1. Dump the source with utf8mb4, compressed:

mysqldump -u youruser -p --no-tablespaces --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 youruser_leadhub | gzip > leadhub.sql.gz

The product's own backups work too: inside storage/app/backups/leadhub_backup_*.zip the file database.sql is exactly this dump.

2. Create the target database and user on the new host — same as Step 1 above.

3. Import it — pick one:

• phpMyAdmin (no Terminal): select the database → Import → choose the file. .sql.gz is accepted and roughly 10× smaller, which usually fits under the host's upload limit. If the file still exceeds upload_max_filesize/post_max_size, split it or use the CLI.

• CLI (fastest, no size limits):

  gunzip -c leadhub.sql.gz | mysql -u youruser -p youruser_leadhub
  

  Sanity count afterwards: mysql … -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='youruser_leadhub'" — a full schema is ~152 tables.

4. Bring .env with the data — APP_KEY above all.

5. Finish on the server:

# .env: set DB_* to the target database and APP_URL to the final URL,
# APP_KEY to the source key. Then:
php artisan optimize:clear
php artisan migrate --force            # applies any migrations newer than the dump
php artisan db:seed --class=DatabaseSeeder --force   # optional; idempotent — syncs new roles/permissions

6. Verify: /api/v1/health shows db: ok; log in with an existing account; open a lead, a call recording and one tenant's settings to confirm the encrypted credentials decrypt (that is the APP_KEY check made visible).

Step 4 — Seed the base data#

A fresh install needs roles, permissions, plans and locales (an imported database already contains them — skip this step, or run it later after pulling in newer code, since it is idempotent):

php artisan db:seed --force

DatabaseSeeder seeds the role/permission baseline (roles super_admin, admin, manager, member; tenant permissions like leads.view, api_keys.manage, whatsapp.send).

Step 5 — Verify#

Open https://your-domain.com/api/v1/health — the checks.db field confirms the connection:

{ "status": "ok", "checks": { "db": "ok", "cache": "ok", "storage": "ok" }, "queue": "ok", "version": "..." }

Character set#

Everything is utf8mb4 — emoji in lead notes, Hindi/Malayalam text in messages and names all round-trip. The installer validates the connection with the utf8mb4 charset before proceeding. If you import a dump made elsewhere, make sure it was dumped with --default-character-set=utf8mb4; a latin1 dump introduces mojibake that cannot be fixed automatically.

The testing database (developers)#

The automated test suite uses its own database, configured in phpunit.xml:

<env name="DB_CONNECTION" value="mysql"/>
<env name="DB_HOST" value="127.0.0.1"/>
<env name="DB_PORT" value="3306"/>
<env name="DB_DATABASE" value="leadhub_testing"/>
<env name="DB_USERNAME" value="root"/>
<env name="DB_PASSWORD" value="your-local-mysql-password"/>

(As shipped, the file carries the usual local dev credentials — swap in your own.)

Create leadhub_testing (or edit those values to your local credentials) before running:

php artisan test
# or
vendor/bin/phpunit

Tests wipe and re-migrate the testing database — never point phpunit.xml at production data. On the local XAMPP install this is root / your MySQL root password.

Backups and restores#

Migration rollback (php artisan migrate:rollback) is for development. In production, restore from the app's own backups instead — see Backups & restore.

Common mistakes#

• Using the unprefixed database name in .env on cPanel (it is youruser_leadhub).
• Granting the user privileges on a different database than .env points to.
• Running migrate without --force on production and thinking it silently failed.
• Seeding demo data on production.
• Editing phpunit.xml to the production database while debugging tests.

Troubleshooting#

Technical details#

• Connection config: config/database.php + .env DB_* keys.
• Installer DB test: public/install.php (PDO, charset=utf8mb4).
• Migrations: database/migrations/; base seeder: database/seeders/DatabaseSeeder.php; demo seeders must not run in production.
• Health probe: App\Http\Controllers\Api\HealthController (checks.db).

Related guides#

• cPanel deployment walkthrough
• Environment variables
• Backups & restore
• Database & migrations