# Recreate the staging database and initialize Laravel's key

This procedure uses the structure-only production SQL supplied on **9 September 2026**. It creates schema, not production records. No live database was changed while implementing it.

## What the migrations cover

Run the normal Laravel migration chain against a new **MySQL 8** database. The existing migrations plus three new forward migrations reproduce all **19 tables**, including Laravel's own `migrations` tracking table:

| New migration | Purpose |
| --- | --- |
| [Complete legacy users schema](../database/migrations/2026_09_09_000000_complete_legacy_users_schema.php) | Adds the missing user fields, makes name/email nullable, and aligns `users.id` with production's signed `INT`. |
| [Create legacy reference tables](../database/migrations/2026_09_09_000001_create_legacy_reference_tables.php) | `config`, `person`, `tense`, `tense_mode`, `verbs`, `verbs_test`. |
| [Create legacy application tables](../database/migrations/2026_09_09_000002_create_legacy_application_tables.php) | `conjugation`, `list`, `list_verbs`, `phrases`, `purchases`, `user_conjugaison`, `user_verbs`, including the supplied indexes and foreign keys. |

Existing migrations still create `users`, `password_resets`, `failed_jobs`, `personal_access_tokens` and `news`. Laravel manages the `migrations` table itself; no custom migration creates it. The original migration history is not rewritten.

The snapshot's names, column types, signedness, nullability, defaults, charsets/collations, indexes and foreign-key rules are preserved. This includes legacy `utf8mb3`, the string `list.user_id`, duplicate indexes and the absence of foreign keys on `phrases`/`purchases`. Changing these would be a separate schema improvement, not a reconstruction. Physical column order and existing auto-increment counters are not copied from production.

The migrations use Laravel's configured database connection; no database hostname, password, application key or business data is embedded in them. MySQL-specific ID checks mean this baseline is not an SQLite/PostgreSQL migration set.

## Existing staging: run it from GitHub

Staging already has a working release and application key. **Keep that key and the current `.env`. Do not clear or regenerate it.**

1. Back up the staging database before applying schema changes, especially if it already has users or test data. Avoid concurrent app writes during the schema update.
2. Publish the new migrations and deployment tooling to GitHub's default branch. Deploying a commit without these files cannot run the new migrations.
3. In **Actions → OVH backend → Run workflow**, select **Use workflow from: main**, **Environment: staging**, **Operation: deploy**, and the `ref` containing these changes.
4. **Check Migrate** and keep **Verify public URL checked**. The staging domain must remain on `apidev.appconjugate/current/public`.
5. Wait for `succeeded`. A failed or timed-out deployment does not imply its migrations were reversed; inspect the reported ID before retrying.
6. Run **Operation: artisan** with **Artisan arguments:** `["migrate:status"]`. To read the command's output, run **status** with that job ID and **Show output checked**.

Alternatively, deploy the new commit with Migrate unchecked, then run **artisan** with `["migrate", "--force"]`. The command reads migrations from the **current deployed release**, not directly from GitHub's latest code. [Laravel migration commands](https://laravel.com/docs/8.x/migrations#running-migrations).

No `migrate:fresh`, `migrate:reset`, table deletion or production import is required.

## New staging environment: generate APP_KEY from GitHub

The application encryption key belongs in **`shared/.env`**, not in a database migration. Laravel uses it for encryption, so changing an established key can invalidate encrypted data and cookies. [Laravel encryption configuration](https://laravel.com/docs/8.x/encryption#configuration).

The workflow now offers a separate **`initialize-key`** operation:

1. Create the private staging `shared/.env` with actual DB credentials, `APP_ENV=staging`, `DB_HOST=taxiboucmydev.mysql.db` and `APP_URL=https://apidev.appconjugate.com`. For a genuinely new environment, leave **`APP_KEY=` blank** (or omit that line). Do not use a placeholder such as `REPLACE_WITH...`.
2. Publish the updated tooling, run **install-runner** for staging, and configure the temporary web root / run **health** as in the [staging setup guide](staging-setup.md). Rerun install-runner if an older worker was already installed: merging alone does not update OVH's private worker.
3. Run **Operation: initialize-key**, **Environment: staging**. It does not need a Laravel release or Composer on OVH and does not access the database.
4. Expect `status: succeeded` and `key_status: initialized`. The worker writes a cryptographically random 32-byte, base64-encoded key directly into `shared/.env` with private permissions. The key is never returned in workflow output, status or the HTTP response.
5. Run the first **deploy** with **Migrate checked** and **Verify public URL unchecked** while the domain points at `runner-public`. After success, switch to `current/public`, run health, then deploy the same SHA with verification checked and migrations unchecked, following the setup guide.

If a valid key already exists, `initialize-key` reports **`key_status: preserved`** and leaves the entire file unchanged. Repeating it does not rotate the key. If an active release exists but its key is missing, initialization refuses: recover the original key from backup. Invalid keys, duplicate assignments, symlinked environment files and production targets are also refused. Automatic editing accepts only single-line environment assignments; multiline secrets or unusual syntax require private manual review. `artisan key:generate` remains blocked by the remote runner to prevent accidental key rotation.

The HTTPS initializer is deliberately staging-only, with the existing staging host/path checks. For a future environment on another host, the migrations remain reusable, but review/update the deployment target guards and configure its `.env` separately. Do not bypass those guards to target production. On a new local installation with shell access, normal Laravel key generation is also available; never regenerate a key on an established environment merely to run migrations.

## Existing or manually managed databases: safeguards and limits

- The new table migrations skip tables already present; they do not replace tables or delete their data. They do **not** claim to reconcile every possible partial/custom table definition. Inspect pre-existing schema differences separately.
- Missing user columns are added without overwriting existing values. The older Laravel migration created `BIGINT UNSIGNED` IDs; narrowing them to production's signed `INT` is allowed only if IDs and the next auto-increment value fit and no existing foreign keys reference that old ID type. Otherwise migration stops for review without dropping those keys or truncating IDs.
- A hand-managed database whose **original migration history is already recorded** can adopt the new migrations. A schema-only SQL import does not provide that history: old migrations may fail with “table already exists.” Do not delete tables or invent history rows to hide that error; audit schema and migration history first. For a new environment, prefer an empty database and the full migration chain.
- These three baseline migrations are **forward-only**: rollback refuses because they may have adopted pre-existing user/reference tables. This prevents a routine rollback from deleting data they did not originally create. Use reviewed forward fixes or a database backup when recovery is needed.
- MySQL schema changes are not an all-or-nothing transaction. A failure can leave earlier DDL applied. Keep backups, inspect partial results, and do not assume restoring a code release restores the database.

Production migration remains a separate, unperformed task. This document does not authorize running the new chain on the live production database.

## Data still needed after creating the schema

The supplied SQL contains **no INSERT statements**. These migrations cannot recreate actual verbs, conjugations, translations, configuration values, official lists, accounts or purchases. Use reviewed seeders or an approved/sanitized data export for reference data. Do not copy production personal data or purchases into staging by default. Migration success alone does not make the application fully usable.

## Verification

[schema.test.php](../tests/Deployment/schema.test.php) runs the real Laravel migrator and compares the result to the independent [structure-only fixture](../tests/Fixtures/production-schema-2026-09-09.sql) through MySQL's metadata. It checks all 19 tables, columns/defaults/nullability, collation, indexes and foreign keys; reruns; preservation of existing rows; refusal of unsafe ID changes; and refusal of destructive baseline rollback.

Run this only against a disposable, isolated MySQL 8 instance. The test has an explicit opt-in and a fixed loopback connection; do not point it at a real database or publish that test database's port. Example from the repository root, with Docker and a compatible PHP image/vendor directory available:

```bash
docker run -d --name conjugate-schema-test --network none --read-only \
  --tmpfs /var/lib/mysql:rw,size=1g --tmpfs /var/run/mysqld:rw --tmpfs /tmp:rw \
  -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:8.0 --skip-log-bin
```

Wait until this **new test container** reports that MySQL is ready. Then run PHP sharing only its isolated loopback network:

```bash
docker run --rm --network container:conjugate-schema-test --read-only --tmpfs /tmp \
  -e CONJUGATE_SCHEMA_TEST_ISOLATED=yes \
  -v "$PWD:/app:ro" -v /ABSOLUTE/PATH/TO/VENDOR:/reference/vendor:ro \
  --entrypoint php YOUR_COMPATIBLE_PHP_IMAGE /app/tests/Deployment/schema.test.php
```

The test creates only randomly named `conjugate_test_*` databases in that disposable instance. After testing, stop and remove **only the test container you created**; its temporary databases are discarded. Do not reuse an existing application database container.

Also run the deployment Python tests, `worker.test.php`, `http.test.php`, PHP syntax checks and actionlint. The key tests cover initialization before Laravel exists, private permissions, preserving existing keys, refusing unsafe replacement and redacted HTTP results.