# Database Function Refactor (`db()`)

- **Date:** 2026-07-15
- **Status:** Planned
- **Status Date:** 2026-07-15
- **Phases:** 5
- **Phases Complete:** 0
- **Notes:** Each phase is independently shippable; the app works between every step. Before Phase 1 ships, complete the error-mode audit (see Risks).

## Plan Phases

1. Add `db()`
2. Migrate endpoints, batch per module
3. Migrate `$pdo` consumers
4. Lock it down
5. Restore strict inspection

## Problem

`www/fn/db.php` defines its connection state as loose file-scope variables
(`$hostname`, `$username`, `$password`, `$dbname`, `$pdo`) that every endpoint
consumes implicitly via `require`. This causes:

1. **No static resolution.** IDEs can't prove the variables exist, so the
   strict "Undefined variable" inspection flags every endpoint. The PhpStorm
   workaround (search project-wide for definitions) weakens the inspection to
   "name exists anywhere in the project", which hides real typos — not
   acceptable.
2. **Duplicated connections.** ~64 files open a *second* PDO handle (`$dbh =
   new PDO("pgsql:host=$hostname;...")`) using the leaked credential
   variables — 84 `new PDO(` call sites across 66 files under `www/` at the
   time of writing. Every request that includes one of these pays for two
   connections.
3. **Credential leakage.** The DB password sits in every endpoint's variable
   scope, and the connection-string boilerplate is copy-pasted everywhere,
   including the commented-out prod RDS variant.
4. **Include-order fragility.** Anything that pre-declares or overwrites one
   of these globals before a `require_once` silently breaks the connection
   (see the 007 migration incident class of problem: state that depends on
   what ran first).

## Design

`db.php` gains one function and keeps its existing side effects (session
start, `ob_start()`, `error_reporting`) unchanged at include time:

```php
/**
 * Shared request-scoped PDO handle. Replaces the loose $pdo/$dbh globals.
 */
function db(): PDO
{
    static $pdo = null;
    if ($pdo === null) {
        $pdo = new PDO("pgsql:host=localhost;port=5432;dbname=netplanner;user=postgres;password=...");
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    return $pdo;
}

// Legacy compatibility — un-migrated files keep working untouched.
$pdo = db();
```

- `static` makes it one connection per request regardless of how many files
  call it.
- The return type is statically resolvable, so strict inspections pass and
  `$dbh->` gets full autocomplete.
- During migration the credential variables stay defined for un-migrated
  files; they are deleted in the final phase.

## Build

### Phase 1 — Add `db()`

Single-file change to `db.php`, plus the error-mode audit (see Risks).
Everything still works via the `$pdo = db();` shim. From this point new code
calls `db()` and never touches the globals.

### Phase 2 — Migrate endpoints, batch per module

In each file: replace `$dbh = new PDO("pgsql:host=$hostname;...")` +
`setAttribute` with `$dbh = db();` and delete nothing else — keeping the
`$dbh` variable name means zero churn in the query code below it. Suggested
batches, UAT after each: wayleave (18 files) → projects → accounts/stocklists
→ opportunities → map/admin/misc. `www/html/html_body_password_*.php`,
`test.php`, and other top-level stragglers go in the last batch.

### Phase 3 — Migrate `$pdo` consumers

`login_check.php`, `global_functions.php` call sites,
`index.php`/`routes.php`, login/logout/register: change `$pdo` reads to
`db()` (or accept the handle as a function parameter, which most
`global_functions.php` helpers already do).

### Phase 4 — Lock it down

When
`grep -rn '\$hostname\|\$username\|\$password\|\$dbname' www/ --include='*.php'`
shows only `db.php`, move the credentials inside `db()` and delete the file-
scope variables and the `$pdo = db();` shim. The password now exists in
exactly one function scope.

### Phase 5 — Restore strict inspection

Re-enable PhpStorm's "Undefined variable" inspection with *no* project-wide
search option, in a project-stored profile (`.idea/inspectionProfiles/`) so
editor and commit analysis agree on every machine.

## Testing checklist

- [ ] Error-mode audit of `$pdo` consumers complete before Phase 1 ships
- [ ] After each Phase 2 batch: exercise the module's list page, editor
      load/save, and one denial path (company gate) in UAT
- [ ] Progress check: `grep -rn 'new PDO(' www/ --include='*.php'` — target
      is `db.php` only (84 across 66 files at time of writing)
- [ ] Phase 4 gate: credential grep above returns only `db.php`

## Risks

### Key risk: error-mode change on `$pdo`

Today the two handles behave differently:

- `$dbh` (per-endpoint) — almost every endpoint sets
  `PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION`.
- `$pdo` (from db.php) — **no error mode set**, i.e. PDO's silent default.
  Failed queries return `false` instead of throwing.

`db()` standardises on exception mode, which means legacy `$pdo` consumers
(`login_check.php`, `global_functions.php` helpers when handed `$pdo`,
`index.php`/`routes.php` bootstrap, login/logout/register) will start
throwing where they previously returned `false`. Before Phase 1 ships, audit
those files for patterns like `if (!$stmt->execute())` or code that relies on
a failed query falling through quietly. Treat any newly-thrown exception
during UAT as a latent bug that silent mode was hiding, not as a regression
to paper over.

### Secondary risks

- **Transactions on a shared handle.** Endpoints that call
  `beginTransaction()` (e.g. `wayleave_save.php`) currently do so on their
  private `$dbh`. After consolidation everything shares one connection, so a
  transaction must not already be open when they start. Today's request flow
  (auth gates → work) is sequential, so this is fine — but any migrated file
  should be checked for a code path that could leave a transaction open.
- **Long-running/streaming endpoints.** `opportunity_process.php` (SSE) and
  `distance_analysis_process.php` hold their connection for the request
  duration. One shared handle is fine; just don't introduce parallelism
  assumptions.
- **Scripts genuinely needing raw credentials** (none known in `www/`; the
  QGIS/Python export scripts have their own config). If one turns up, add
  `dbConfig(): array` beside `db()` rather than re-exposing globals.

## Out of scope

- Composer/autoloading, namespaces, or any framework adoption.
- Connection pooling / persistent connections.
- Moving credentials out of the repo into environment config (worth doing —
  the password is currently committed — but it's a deployment-process change,
  not part of this refactor).
- The prod RDS connection switch (commented out in `db.php`) — unchanged.