18092b271e
## Problem
Production was returning:
```json
{"success":false,"data":{"message":"Internal server error: Unknown column 'invoice_email' in 'SELECT'"}}
```
when authenticating as a superuser. The migration that adds
`users.invoice_email` was merged to master in api#381 but never applied
to the production database.
## Fix
- New `GET /api/admin/schema-check` endpoint — returns 503 with explicit
list of missing columns if any are absent (instead of a generic 500)
- `scripts/run-schema-bootstraps.php` — auto-discovers and runs every
`*_schema_bootstrap` class on the live database (additive, idempotent)
- `scripts/schema-health-check.php` — CLI tool for the same check, used
by deploy pipelines
- New Pest contract test `SchemaHealthCheckTest` — verifies the test DB
has every required users column and the schema-check endpoint works
- `deploy.yml`: pre-deploy step runs the bootstrap runner, smoke test
also runs the schema check, Slack alert on failure
## What this prevents
- Future migrations being merged without being applied to production
- Silent failures (generic 500) when a column is missing
- Repeated manual investigation of the same root cause
Refs: TRU-77 (the original bug), api#381 (the original PR)
---------
Co-authored-by: Jeppe B <jeppe@copenhagentruckwash.io>
Co-authored-by: bugfix-subagent <bugfix-subagent@truckwash.local>
Co-authored-by: OpenClaw Bugfix Agent <bugfix@openclaw.local>
97 lines
2.7 KiB
PHP
97 lines
2.7 KiB
PHP
#!/usr/bin/env php
|
|
<?php
|
|
/**
|
|
* Schema health check — verifies all required DB columns exist.
|
|
*
|
|
* Run via:
|
|
* GET /api/admin/schema-check (returns JSON report)
|
|
* php scripts/schema-health-check.php (CLI, exits 0/1)
|
|
*
|
|
* Lists the columns that the code expects to find in each critical
|
|
* table. If a column is missing, the response is 503 (HTTP) or
|
|
* exit code 1 (CLI) — clearly distinct from a generic 500.
|
|
*
|
|
* Add to the list when introducing a new optional column.
|
|
*/
|
|
|
|
namespace scripts;
|
|
|
|
require_once __DIR__ . '/../services/nginx/app/classes/customer_invoice_email_schema_bootstrap.php';
|
|
|
|
use classes\customer_invoice_email_schema_bootstrap;
|
|
|
|
const SCHEMA_REQUIREMENTS = [
|
|
'users' => [
|
|
'invoice_email', // TRU-77 (added 2026-08-16)
|
|
'wash_certificate_email',
|
|
'email',
|
|
'customer_number',
|
|
],
|
|
'invoices' => [
|
|
'po_number',
|
|
'closed_at',
|
|
'customer_number',
|
|
],
|
|
'bookings' => [
|
|
'id',
|
|
'customer_number',
|
|
'department',
|
|
],
|
|
];
|
|
|
|
function check_schema(): array
|
|
{
|
|
global $db;
|
|
$report = [
|
|
'ok' => true,
|
|
'missing' => [],
|
|
'tables_checked' => 0,
|
|
'columns_checked' => 0,
|
|
'timestamp' => date('c'),
|
|
];
|
|
|
|
if (!isset($db) || !is_object($db) || !method_exists($db, 'query')) {
|
|
$report['ok'] = false;
|
|
$report['error'] = 'no_db_connection';
|
|
return $report;
|
|
}
|
|
|
|
// First: run the schema bootstrap (additive, idempotent) so we
|
|
// give the DB a chance to self-heal.
|
|
if (class_exists(customer_invoice_email_schema_bootstrap::class)) {
|
|
customer_invoice_email_schema_bootstrap::ensureSchema();
|
|
}
|
|
|
|
foreach (SCHEMA_REQUIREMENTS as $table => $columns) {
|
|
$report['tables_checked']++;
|
|
|
|
// Confirm the table itself exists
|
|
$tableSafe = str_replace('`', '', $table);
|
|
$result = $db->query("SHOW TABLES LIKE '{$tableSafe}'");
|
|
if (!$result || (int)$result->num_rows === 0) {
|
|
$report['ok'] = false;
|
|
$report['missing'][] = "table `{$table}` does not exist";
|
|
continue;
|
|
}
|
|
|
|
foreach ($columns as $column) {
|
|
$report['columns_checked']++;
|
|
$colSafe = str_replace("'", '', $column);
|
|
$r = $db->query("SHOW COLUMNS FROM `{$tableSafe}` LIKE '{$colSafe}'");
|
|
if (!$r || (int)$r->num_rows === 0) {
|
|
$report['ok'] = false;
|
|
$report['missing'][] = "{$table}.{$column}";
|
|
}
|
|
}
|
|
}
|
|
|
|
return $report;
|
|
}
|
|
|
|
// CLI mode
|
|
if (PHP_SAPI === 'cli') {
|
|
$report = check_schema();
|
|
echo json_encode($report, JSON_PRETTY_PRINT) . "\n";
|
|
exit($report['ok'] ? 0 : 1);
|
|
}
|