### Critique of Direct SQL Injection vs. API Configuration for 9Router

In managing and scaling 9Router (the local model gateway for the Hermes AI OS), there is a tension between executing direct SQLite inserts/updates into `/root/.9router/db/data.sqlite` and using the API. While direct database manipulation is sometimes used to bypass UI-specific credential validations, it constitutes an operations anti-pattern that frequently disrupts the UI synchronization.

---

### 1. Why Direct SQL Injection is an Anti-Pattern in 9Router

*   **Bypassing Application-Layer Validation Rules**: 
    The 9Router backend (a Next.js/Node.js-based app) runs schemas and logic to ensure configurations are sane (e.g., matching provider specific parameters, prefixing custom endpoints with `openai-compatible-`, checking credential patterns, validating JSON configurations). Directly inserting rows bypasses these application-layer validation boundaries, leading to dirty or corrupt configurations.
*   **Decoupled State and Cache Staleness**:
    9Router leverages an in-memory SQLite wrapper (using native bindings like `better-sqlite3` or memory cache). Writes directly to the physical `/root/.9router/db/data.sqlite` file on the host mount are not monitored by 9Router's running process. Because 9Router does not use a database file watcher to invalidate memory caches, the runtime continues to serve and display old configurations.
*   **Database Lock Contention (SQLite WAL Mode issues)**:
    Since SQLite uses file-level locking, writing directly from external scripts while the container is actively writing logs, updating usage statistics (`usageDaily`, `requestDetails`), or conducting token-refresh updates can cause transient database locks or write conflicts.
*   **Missing Side Effects and Events**:
    API actions trigger secondary routines, such as connection testing, key health validation, auto-detection of available model lists, and client-side webhooks. SQL inserts completely miss these vital side-effects, leaving connections in unverified or `unavailable` states.

---

### 2. Why Direct SQL Fails to Sync with the UI

*   **In-Memory UI State**: 
    The browser dashboard relies on API responses generated from 9Router’s active memory cache. Changes written directly to the SQLite tables (like `combos` or `providerConnections`) will not propagate to the frontend.
*   **Missing or Incorrect Schema Fields**: 
    Direct inserts often misconfigure columns that control UI display logic. For example, inserting a combo without specifying the correct `kind` value (such as inserting a string like `'default'` instead of a valid type like `llm`, `fallback`, `round-robin`, or leaving it as `NULL` to match the original default schema) causes the UI rendering to fail silently and display "No combos yet".
*   **Required Service Restarts**: 
    As detailed in the `9router-ops` skill, the only way to synchronize a direct DB write is by physically restarting the Docker container (`docker restart 9router`). This flushes the active memory cache and forces the application to re-read the SQLite file from disk on boot. However, restarting containers in production introduces latency and interrupts active proxy streams.

---

### 3. Suggestions for a Reliable Alternative to 9Router Configuration

Instead of direct SQL injection or manual UI clicks, the gateway configurations should be managed using a declarative, API-first orchestration pattern.

#### A. Automated Browser-Session API Calls (Cookie Auth)
Since the administrative CRUD endpoints (`/api/combos`, `/api/providers`) require a browser session cookie (and reject standard Bearer tokens), you can script session acquisition. 
*   **Mechanism**: Perform a login handshake programmatically via `/api/auth/login` to retrieve the session cookies (`next-auth.session-token` or similar), then pipe those cookies in standard JSON REST commands to `/api/combos` or `/api/providers`.
*   **Example script (Python/Node)**:
    ```python
    import requests
    session = requests.Session()
    # 1. Authenticate to get session cookie
    session.post("http://127.0.0.1:20128/api/auth/login", json={"username": "admin", "password": "..."})
    # 2. Make authenticated API call
    response = session.post("http://127.0.0.1:20128/api/combos", json={
        "name": "profile-r-and-d",
        "kind": "llm",
        "models": ["ag/claude-sonnet-4-6", "cx/gpt-5.4"]
    })
    ```

#### B. API Gateway Declarative Import Endpoints
If 9Router has a CLI import tool or a `/api/settings/database` import handler, this should be used instead of manual database patching. Using programmatic API uploads allows the runtime validation loop to process the files cleanly.

#### C. Programmatic DB Write with Mandatory Process Flush (Safe SQL Fallback)
If raw SQL injection is absolutely necessary to bypass vendor-specific UI limitations (e.g., hardcoded region constraints for custom endpoints), it must always follow a strict transactional flow that enforces cache invalidation:
1.  **Write changes** to `/root/.9router/db/data.sqlite` using a single ACID transaction.
2.  **Verify syntax** and ensure columns like `kind` match valid values.
3.  **Trigger Container Restart**: Run `docker restart 9router` or call the container manager to force-flush the memory buffer.
4.  **Confirm Sync**: Test availability programmatically using the Open-SSE proxy completions check at `GET http://127.0.0.1:20128/v1/models` (which correctly accepts the bearer token `LOCAL9R_KEY`).