# Deployment — clinic.tmsoagency.com Live since 2026-08-03 on the Laragon/Apache host at `186.190.220.98`, the same box that already serves `taxpilot.tmsoagency.com`. DNS for the subdomain points straight at that IP (the apex `tmsoagency.com` sits behind Cloudflare; this subdomain does not). ## Moving parts | Piece | Where | |---|---| | Document root | `C:/laragon/www/Clinic/public` | | vhost | `C:/laragon/etc/apache2/sites-enabled/clinic.tmsoagency.com.conf` | | TLS certificate | `C:/laragon/etc/ssl/clinic-le/` (Let's Encrypt, via win-acme) | | Renewal | win-acme scheduled task; runs `C:\win-acme\reload-apache.bat` after each renewal | | Database | MySQL `clinic` on the same host | The `:80` vhost 301s everything to HTTPS **except** `/.well-known/acme-challenge/`, which must stay reachable or renewal fails silently 60 days later. `FORCE_HTTPS` alone was not enough: it only rewrites URLs the app *generates*, so before the redirect existed a visitor typing the bare hostname was served the login form over plaintext. HSTS comes from the application's `SecurityHeaders` middleware, not the vhost — setting it in both emitted two conflicting headers. Note that the app's header carries `includeSubDomains`, so it instructs browsers to require HTTPS across *all* of `tmsoagency.com`. ## Installing a new clinic The seeders create branches, roles and settings but deliberately no user, and every screen sits behind `auth` — so a fresh installation needs its first administrator created explicitly. Shipping a default account with a known password would mean a fleet of clinics sharing one documented login. ```bash php artisan db:seed # branches, roles, settings php artisan install:admin # prompts for name, email, password ``` Options: `--name`, `--email`, `--password`, `--branch=CODE` (defaults to the primary branch), `--must-change` to force a password change at first sign-in. Omit `--password` to be prompted without echo — a password passed as an option is visible in shell history and the process list, which is fine for an automated installer but a poor default for a person. The command **refuses to run once a Super Admin exists**; further administrators are added from Users in the UI, where the action is authorised and audited. Left runnable it would be a standing privilege-escalation route to a fully permissioned account. It lives in the Auth module (`app/Modules/Auth/Console/Commands/`), not core. It reads that module's `PasswordPolicy`, and core may not import a module — the architecture test caught exactly that when it was first written in `app/Console/Commands/`. Clinics on cPanel without SSH can run it from Terminal or a one-off cron entry; a future web installer should call this command rather than reimplement it. ## Redeploying after a code change ```bash git pull # or however the change arrives composer dump-autoload --optimize npm run build php artisan view:cache php artisan event:cache php artisan module:cache php artisan migrate --force ``` **Do not run `php artisan config:cache` on this box.** See below. **`route:cache` is not in that list on purpose.** A cached route table is loaded in place of route registration, and module routes registered by `BaseModuleServiceProvider::loadModuleRoutes()` do not survive it — they simply stop existing, on the site and in `route:list` alike. This box keeps no route cache; check `bootstrap/cache/routes-v7.php` first if a route ever 404s straight after being added. **`module:cache` is in it, and matters.** Anything read from a `module.json` — the navigation menu especially — is served from `bootstrap/cache/modules.php` until that file is rebuilt. Editing a manifest without this leaves the change invisible with nothing to suggest why. ## The config:cache trap This directory is simultaneously the development checkout, the test working copy, and the live document root. `config:cache` bakes the resolved config — including `DB_DATABASE=clinic`, the live database — into `bootstrap/cache/config.php`, and Laravel then skips `.env` entirely. The `` entries in `phpunit.xml` and the values in `.env.testing` cannot override a cached config, so the whole test suite silently retargets the **live clinical database**, and the first `RefreshDatabase` test drops every table in it. Two defences are in place: 1. No config cache is left on this machine. `route`, `view` and `event` caches are kept — they carry no database identity and are safe. 2. `tests/TestCase::createApplication()` refuses to build the application unless the resolved database is `clinic_test`. It hangs off application creation rather than `setUp()` deliberately: the base `setUp()` boots the app and *then* runs `setUpTraits()`, which is where `RefreshDatabase` does its dropping — a check placed after `parent::setUp()` would fire only once the tables were already gone. If the site is ever moved to a host that does not also run the test suite, `config:cache` is fine and worth having. ## Known gaps — read before real patients These are deliberate and outstanding, not oversights: - **The database contains demo data.** Factory-generated patients and the seeded catalogues are live and world-reachable behind the login. A real clinic needs a fresh database, not this one. - **`APP_KEY` is the development key.** It encrypts `national_id` at rest. Before real patient data, generate a new key — and note that rotating it makes existing encrypted columns unreadable, so it must happen *before* data exists, not after. - Backups are configured (see below), but they are **local to this machine**. A disk or host failure loses the database and its backups together — copying `C:\laragon\backups\daily\` off-box is the remaining gap. - `.env.backup-predeploy` holds the pre-deployment configuration. ## MySQL hardening (done 2026-08-03) `root` previously had no password and MySQL listened on `0.0.0.0`. Both are fixed: - `root@localhost` now has a password, stored in each app's `.env`. - `my.ini` sets `bind-address=127.0.0.1` and `mysqlx-bind-address=127.0.0.1`, so 3306 and 33060 no longer listen on the public interface. Backup of the original at `C:\laragon\backups\my.ini.backup-predeploy`. Worth recording: only `root@localhost` ever existed — there was no `root@%` — so remote authentication was already impossible even with the open listener. The bind change removes the listener itself rather than closing a live hole. Nine files carried the credential and were all updated in the same pass: `Clinic/.env`, `Clinic/.env.testing`, `Clinic/.env.backup-predeploy`, `ccpilot/.env`, `license-server/.env`, `life associate/.env`, `life associate/.env.e2e`, `waverdp/.env`, plus the `.env` inside the `.claude/worktrees/` checkouts under `license-server` and `life associate`. Two traps this exposed, both worth remembering: 1. **Stale config caches.** `ccpilot` and `waverdp` had `bootstrap/cache/config.php` with the old empty password baked in, and a cached config wins over `.env` — so both were broken by the change until their caches were cleared. Any app on this box that caches config needs `php artisan config:clear` after a credential change. 2. **Stale `.env` backups.** These still hold the old empty password and would break their app if restored: `ccpilot/.env.backup-20260720`, `life associate/.env.backup-20260727_115854`. ## Per-application MySQL users (done 2026-08-03) No application connects as `root` any more. Each has an account whose grants stop at its own schema, so a compromise in one app cannot read another's data — most importantly, nothing else on this box can read `clinic`. | User | Schemas | Used by | |---|---|---| | `clinic_app` | `clinic` | Clinic (live) | | `ccpilot_app` | `ccpilot` | ccpilot | | `license_app` | `license_server` | license-server / taxpilot | | `lifeassoc_app` | `life_associate`, `life_associate_e2e` | life associate | | `waverdp_app` | `waverdp` | waverdp | Each holds `ALL PRIVILEGES` on its own schema and nothing global — no `FILE`, no `SUPER`, no `PROCESS`, and no visibility of any other database. Schema-wide rather than statement-level because Laravel migrations legitimately need `CREATE`/`ALTER`/`DROP`; the boundary being enforced is *between applications*, which is where the real exposure was. `root` keeps the password set earlier and is now an administration account only. ### The test suite and its own account (history: dropped 2026-08-03, restored since) A sixth account, `clinic_test_app`, is scoped to `clinic_test` so the suite can never reach live data. It was briefly dropped by request on 2026-08-03; it has since been recreated and the suite runs on this host again (verified 2026-08-12: full run green). `.env.testing` carries its credentials. The account holds rights on `clinic_test` **only**. Rights over `clinic` would let a misconfigured run drop live clinical data — if the account ever needs recreating, this is the whole of it: ```sql CREATE USER 'clinic_test_app'@'localhost' IDENTIFIED BY ''; GRANT ALL PRIVILEGES ON `clinic_test`.* TO 'clinic_test_app'@'localhost'; ``` Two standing rules follow from the shared database: - **One test run at a time.** `clinic_test` is one schema; two concurrent runs wipe each other's tables mid-flight and fail with `0 assertions`, which reads as broken migrations and is not. - **`tests/TestCase::createApplication()` stays as it is**, and `config:cache` is never run here — the guard in that method and the account's own scoping are the two layers keeping a cached production config out of a test run. ### Runtime vs migration split (Clinic only, 2026-08-03) `clinic_app` no longer holds DDL. It has `SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW, CREATE TEMPORARY TABLES, LOCK TABLES, REFERENCES` on `clinic` and nothing more, so the running application can write rows but cannot `DROP` or `ALTER` the clinical schema. Schema changes use a second account, `clinic_migrate`, through a dedicated connection: ```bash php artisan migrate --database=mysql_migrations ``` `config/database.php` defines `mysql_migrations` as a copy of `mysql` with `DB_MIGRATION_USERNAME` / `DB_MIGRATION_PASSWORD`. Both **fall back to the runtime credentials when unset**, so an installation that has not split its users keeps working with the plain `php artisan migrate`. Safe because DDL is confined to migrations: `Schema::` appears nowhere outside `Database/Migrations`, and the schema has no triggers, views, routines or events. Verified after the change against the live site — login, reads, and the write paths that matter (token issue, walk-in admission with gapless reference allocation, two status transitions, token revoke) all succeeded. Rollback, if a future migration or feature needs DDL at runtime: ```sql GRANT ALL PRIVILEGES ON `clinic`.* TO 'clinic_app'@'localhost'; FLUSH PRIVILEGES; ``` Only Clinic is split. The other four applications still connect with a single schema-scoped account each; doing the same for them is worthwhile but touches projects outside this one. ## Daily backups (set up 2026-08-03) | | | |---|---| | Script | `C:\laragon\backups\backup-databases.ps1` | | Credentials | `C:\laragon\backups\backup.cnf` (ACL: SYSTEM read, Administrators full) | | Output | `C:\laragon\backups\daily\.zip` | | Log | `C:\laragon\backups\backup.log` | | Schedule | Task `MySQL Daily Backup`, 02:30 daily, runs as SYSTEM | | Retention | 14 days | | Account | `backup_user` — read-only | Every database on the host is discovered at run time rather than listed, so a new application is backed up the day it is created rather than the day somebody remembers to edit the script. Each database goes to its **own** file, so restoring one app is `mysql clinic < clinic.sql` instead of an exercise in text editing. A full set is ~1.9 MB zipped (16 MB raw). `backup_user` holds `SELECT, RELOAD, PROCESS, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER` globally and nothing else — it cannot modify or drop anything. Credentials live in `backup.cnf` rather than on the command line, so the password never appears in the process list. ### What is actually verified Backups fail quietly, so the script does not trust its own success: - `--result-file` is used instead of PowerShell redirection. `>` re-encodes (UTF-16/BOM depending on version) and silently corrupts a dump — which is only discovered during a restore, at the worst possible moment. - Each dump must exceed 1 KB **and** end with mysqldump's `-- Dump completed` marker. A dump truncated by a full disk or a dropped connection otherwise looks perfectly healthy. - Compression happens only after verification, so a corrupt dump is never buried inside an archive that looks fine from outside. - Any failure leaves the set uncompressed for inspection and exits non-zero, so Task Scheduler records a failure instead of reporting success over a broken backup. All three paths were tested: a good dump is accepted, a truncated one is rejected for the missing marker, an empty one for its size. ### Restore Proven on 2026-08-03 by restoring `clinic.sql` into a scratch database and comparing against live — 57 tables, and patient/user/invoice counts all matched. ```bash # from C:\laragon\backups\daily unzip -o 2026-08-03_081556.zip -d restore mysql -uroot -p clinic_restore_test < restore/clinic.sql ``` Dumps contain no `CREATE DATABASE`/`USE`, so they restore into whichever database you name — restore to a scratch schema and verify before pointing anything at live. ### Off-site copies Each run also writes `.zip.enc` — the same archive encrypted with AES-256 — and copies **only that file** off-site. The plaintext `.zip` never leaves this machine. Local copies are deliberately left unencrypted while the off-site copy is encrypted. A plaintext dump on this disk is no more sensitive than the live database sitting beside it, so local encryption would buy nothing while making every restore depend on a passphrase — the one thing most likely to be lost. The off-site copy leaves our control entirely, so it is encrypted first. | | | |---|---| | Passphrase | `C:\laragon\backups\backup-encryption.key` (ACL: SYSTEM read, Administrators full) | | Cipher | `aes-256-cbc`, `-pbkdf2 -iter 200000 -salt` | | Tool | `C:\Program Files\Git\usr\bin\openssl.exe` | OpenSSL is used rather than a PowerShell AES routine so the archives remain decryptable by any standard tool on any machine years from now. A bespoke format that only this script understands is a poor thing to discover at the far end of a disaster. **Losing the passphrase makes every off-site copy unrecoverable.** It is the only secret with no other source — the databases can be re-dumped, this cannot be re-derived. Keep a copy somewhere other than this server. To decrypt (the iteration count and cipher must match exactly): ```bash openssl enc -d -aes-256-cbc -pbkdf2 -iter 200000 \ -in 2026-08-03_082521.zip.enc -out restored.zip \ -pass file:backup-encryption.key ``` Verified 2026-08-03: the encrypted archive decrypts byte-identically to the original (matching SHA-256), still unzips to seven valid dumps, and a wrong passphrase fails loudly with `bad decrypt` rather than producing silent garbage. ### Turning the off-site copy on **It is not on yet.** `offsite.cnf` does not exist, so every run logs: ``` [WARN] Off-site copy SKIPPED - no C:\laragon\backups\offsite.cnf yet ``` That is intentional, not a fault: the encryption half runs and is proven, and the transfer half stays dormant until a destination is supplied. Copy `offsite.cnf.example` to `offsite.cnf` and complete one section — rclone (object storage), sftp, or a UNC share. The template carries the exact commands plus the traps for each, notably that the task runs as **SYSTEM**, which does not share the Administrator profile where `rclone config` writes by default. That single detail is the usual reason an off-site backup works by hand and silently fails on a schedule. Exit codes make the state diagnosable without opening the log: `0` all good, `1` a dump failed (the backup is not trustworthy), `2` dumps are fine and stored locally but the off-site copy failed (data safe, redundancy missing). ### Email alerts Configured in `C:\laragon\backups\alerts.cnf` (ACL: SYSTEM read, Administrators full). Currently mailing **mimran11366@gmail.com**. | Trigger | Subject | Meaning | |---|---|---| | exit 1 | `[BACKUP FAILED] …` | A dump failed. Restore capability is compromised | | exit 2 | `[BACKUP WARNING] Off-site copy failed …` | Data is safe locally; redundancy is missing | | weekly | `[backup ok] …` | Heartbeat — the job is still running | SMTP goes through Brevo, **borrowed from `waverdp`'s relay** — the only working relay on this host. The credential is copied into `alerts.cnf` rather than read from `waverdp/.env` at run time, so the two cannot silently break each other; if the Brevo password is ever rotated, update it in both places. Two details that matter in the implementation: - TLS 1.2 is forced explicitly. Windows PowerShell 5.1 still negotiates TLS 1.0 by default, which Brevo refuses — the failure surfaces as a vague "unable to read data from the transport connection". - `Send-Alert` never throws. A mail problem must not change the outcome of a backup that succeeded, nor mask one that failed; a send failure is written to the log instead. ### The heartbeat, and why it exists Alert-on-failure cannot report the failure that matters most: **the job never running at all** — task disabled, host powered off, script moved. Nothing fires, and silence reads as success. So a success heartbeat goes out at most once every `HEARTBEAT_DAYS` (7). Silence for more than a week is then itself the alarm. Set `HEARTBEAT_DAYS=0` to disable. This is a weak dead-man's switch: it still depends on somebody noticing an email that *stopped* arriving. The external monitor below is the strong version. ### External uptime monitor (dead-man's switch) Everything else here runs *inside* the script — log, emails, heartbeat — and they share one blind spot: if the job never runs, no code executes and nothing reports anything. A disabled task, a powered-off host, a moved script and a healthy system are indistinguishable from outside. All look like silence. An external monitor inverts that. It expects a ping on a schedule and raises the alarm when one does not arrive. | Ping | When | |---|---| | `PING_URL/start` | job begins (lets the monitor spot a run that hangs) | | `PING_URL` | success, exit 0 | | `PING_URL/fail` | exit 1 (dump failed) **and** exit 2 (off-site failed) | Exit 2 pings `/fail` too. The emails distinguish "data unsafe" from "redundancy missing"; the monitor answers the cruder question — is the backup system fully healthy — and there it is not. Pings never affect the backup: wrapped, 15-second timeout, and a failed ping logs a `WARN` without changing the exit code. An unreachable monitor is a monitoring problem, not a backup problem. **Considered and declined (2026-08-03).** No external monitor is configured and none is planned. The ping mechanism above is built and tested but stays dormant and silent — it logs nothing while `monitor.cnf` is absent, because this is a settled decision rather than an oversight. If that is revisited, copy `monitor.cnf.example` to `monitor.cnf` and set `PING_URL`; the template walks through healthchecks.io (free tier, no card) with period and grace values matching the 02:30 schedule. No code change is needed. Do **not** self-host the monitor on this server: one that dies with the machine it watches is not a monitor. The consequence of declining is worth stating once, plainly: **nothing will report a backup that never runs.** A disabled task, a powered-off host or a moved script produces no failure email, because no code executes to send one. The only signal is the weekly `[backup ok]` heartbeat failing to arrive — so that email is now load-bearing, and noticing its absence is a human responsibility. TLS is negotiated once at the top of the script as TLS 1.2 **or 1.3** for all outbound calls. PowerShell 5.1 defaults to TLS 1.0, which most endpoints refuse; and this host's own Apache turned out to reject a 1.2-only handshake from .NET Framework, so 1.2 alone is not a safe assumption either. ### Verified - SMTP delivery to the configured recipient — confirmed accepted by Brevo. - The failure path: `MinBytes` was temporarily raised so every dump was judged undersized; the run correctly reported failure, sent `[BACKUP FAILED] MySQL dump failed on CROWNVPS`, and exited `1`. Threshold restored to 1024 afterwards. - The heartbeat fired on a clean run and correctly suppressed itself on the next run within the window. - Monitor pings, against a temporary test endpoint: the success ping logged `Monitor ping OK`, and forcing a failure produced both the alert email and a `/fail` ping before exiting `1`. The test config was removed afterwards, so no pings leave this host until `monitor.cnf` exists. - Ping isolation: when the test endpoint returned `503`, the run logged a `WARN` and still exited `0` — a broken monitor does not fail a good backup. ### Known limits - **Off-site is dormant, and this host has nowhere to send backups.** Checked 2026-08-03: one physical disk (disk 0, 175 GB VirtIO), `C:` is the only volume, `D:` is an empty CD-ROM. No second drive, no network share, no cloud credentials. Backups therefore sit on the same disk as the data and a host failure loses both. Copying to another folder on `C:` was considered and rejected: it survives an accidental delete and nothing else, while creating the impression that redundancy exists. The script now logs a prominent `WARN` if the configured destination resolves to the same volume as the backups. Order of preference to fix it: attach a second volume to the VM (then `METHOD=path`, `DEST_PATH=D:\clinic-backups\` — a one-line change); or point it at a NAS/another host; or object storage via rclone. - **The `path` transfer method is tested; rclone and sftp are not.** `METHOD=path` was exercised end to end on 2026-08-03 — the encrypted archive copied, decrypted at the destination and was byte-identical to the source. The rclone and sftp branches still cannot be exercised without real credentials; run the script by hand once after configuring either and confirm it exits `0`. - **Alert delivery depends on a borrowed relay.** If waverdp's Brevo account lapses, alerts stop — and stopped alerts look exactly like healthy backups until the weekly heartbeat is missed.