SEPO Engine / SepoDesk – Developer & Operations Guide

This document captures the architectural decisions, common pitfalls, and operational best practices for the SepoDesk WordPress plugin / standalone application engine. It is the reference for maintaining, extending, and troubleshooting the system.

1. System Overview

SepoDesk is a modular application engine built to run inside WordPress (or standalone). It provides:

• A role‑based JSON API (/sepodesk/api/v2/data) with dynamic table and record operations.
• Server‑rendered resources (admin panels, dashboards) via ResourceController.
• Client‑side apps that use the same API through AppDataManager and the EcmaScriptController.
• Multi‑database support with logical handles (sp_db_auth, sp_db_apps, etc.) that map to physical database names via environment variables.
• Payment processing (mobile money, PawaPay) integrated with shop, booking, and LMS modules.

All connections are managed centrally to avoid duplication and ensure resilience.

2. Architecture Components

3. Database Layer – Connection Management

3.1 DbConnectionManager (core)

• Lazy loads – connections are established only when first requested.
• Caches – once a PDO is created, it is reused for the duration of the request.
• Retry logic – up to 5 attempts with exponential backoff (100, 200, 400, 800, 1600 ms) for transient errors (codes 2002, 2006, 1040, 1203 and message patterns like “Operation not permitted”).
• Forced host – always uses 127.0.0.1 to avoid Unix socket path mismatches on shared hosting.
• Environment‑driven – reads POPULATOR_DB_{1..4}_HOST, _NAME, _USER, _PASS_ENC from .env.
• Logging – detailed debug logs for every connection attempt, retry, and failure.

3.2 PDO Factory & Error Classification

• PdoFactory::get() builds the DSN and calls new PDO().
• Throws ConnectionFailedException (network / credentials) or DatabaseNotFoundException (unknown database).
• The manager catches these and decides whether to retry based on the driver error code (errorInfo[1]) and message content.

3.3 Connection Reuse Across Components

• AppShell and ServerAppShell delegate to DbConnectionManager.
• DbConnectionSelectorTrait (used by PaymentNotifier, etc.) also calls the manager.
• All components share the same static connection pool, eliminating duplicate connections.

4. Authentication & Authorisation

4.1 API Key → Roles

• Each API key stores a JSON array of roles (sp_api_roles_name).
• ApiRoleResolver reads this JSON, caches the result (via ApiKeyRoleCache), and returns the array.
• The connector (SpEngineConnector) then iterates over the roles to find one that can resolve both a target database and credentials.

4.2 Role → Target Database

• RoleTargetDbResolver uses tsp_sys_role_db_access (populated by the installer) to map roles to databases.
• Supports both explicit database override and table‑based routing (sysTable or tableText).

4.3 Role → Credentials

• RoleCredentialsResolver reads tsp_sys_core_roles_map for the role’s host, user, and encrypted password.
• The password is decrypted using CryptoAES and APP_SECRET_KEY.

4.4 Fallback & Error Handling

• If a role fails (no mapping or invalid credentials), the connector moves to the next role.
• If no role succeeds, a clear exception is thrown with context.

5. Frontend API Client (EcmaScriptController)

5.1 Circuit Breaker

• Automatically opens after repeated 5xx errors or network failures.
• Blocks all further requests for 30 seconds (for transient errors) or 5 minutes (for server errors).
• Auto‑resets after the timer expires.
• Auth requests bypass the breaker so token refresh can still happen.

5.2 Request Throttling

• Configurable requestDelay (e.g., 200 ms) between consecutive requests.
• Prevents bursts that could overload the server or hit connection limits.
• Debug logs show the delay being applied.

5.3 Retry & Idempotency

• Only idempotent actions (select, fetchData, getSchema, etc.) are retried.
• Mutations (insertMultiple, updateMultiple, delete) are not retried to avoid duplicate rows.
• Retries use exponential backoff (500, 1000, 2000 ms) up to maxRetries (default 3).

5.4 Caching

• Response cache with TTL (default 30 seconds).
• staleWhileRevalidate – serves stale data while refreshing in the background.
• Cache stored in memory or sessionStorage (opt‑in).

5.5 Token Refresh

• Single‑flight (_refreshPromise) so multiple 401s do not trigger parallel refreshes.
• Refresh token is HttpOnly, never exposed to JS.

6. WordPress Integration

6.1 Plugin Bootstrap

• The plugin’s index.php checks for ABSPATH and WORDPRESS_MODE to determine runtime.
• Autoloader (Url::registerAutoloader()) is registered before any hooks, so all classes are available when hooks fire.
• Safe mode (SepoEngine\WordPress\SafeMode) prevents the engine from loading if a previous fatal error occurred.

6.2 Kernel & Routing

• Kernel handles bootstrapping (session, environment, service registration, routing).
• For WordPress, the kernel is run via KernelBridge (in wp:activate and template_redirect).
• Routes are defined in Routes.php and PagesRoutes.php; mountFiles serves documentation pages.

6.3 Case Sensitivity

• On Linux (Hostinger), folder and file names are case‑sensitive.
• All namespaces use PascalCase (e.g., SepoEngine\WordPress\Enqueue\AssetsManager). Folders must match exactly.
• The installer and autoloader are case‑insensitive in their maps to avoid fatal errors.

7. Installation & Migrations

7.1 Installer (Installer/run.php)

• Explicitly loads only required leaf classes (no full framework).
• Checks IS_PRODUCTION, INSTALLER_ENABLED, INSTALLER_KEY, and IP allow‑list.
• Creates databases (if not SQLite) and tables from Migration/Versions folder.
• Runs data hydration from Migration/Data.

7.2 Populators

• RoleDbAccessPopulatorController builds the tsp_sys_role_db_access table.
• SchemaPopulatorController deploys table schemas from blueprints.
• MigrationPopulatorController applies versioned migrations.

7.3 SQLite Support

• Enabled via SQLITE_ENABLED=true.
• Connections use .sqlite files in storage/database/.
• The installer skips database creation and user management in SQLite mode.

8. Logging & Debugging

8.1 Logger (SepoEngine\Helpers\Logger)

• Writes to Storage/Logs/sepo-YYYY-MM-DD.log.
• Rotates files at 5 MB, retains 14 days.
• Redacts sensitive keys (password, secret, token, etc.) from context.
• Supports levels: debug, info, warning, error, critical.

8.2 Debug Mode

• Set APP_DEBUG=true in .env to see detailed error messages in JSON responses and stack traces.
• Use IS_PRODUCTION=true to suppress user‑facing error details.

8.3 Monitoring

• All connections, retries, and failures are logged with full context.
• Request metrics (time, memory, URI) are logged in debug mode.

9. Common Issues & Solutions

9.1 SQLSTATE[HY000] [2002] Operation not permitted

• Cause: MySQL connection refused – usually because the host is localhost (socket mismatch) or server overloaded.
• Fix: Force 127.0.0.1 (already done in DbConnectionManager). Enable retry logic (now with 5 attempts). Increase max_connections if needed.

9.2 Class "SepoEngine\WordPress\Enqueue\AssetsManager" not found

• Cause: Folder name mismatch (case‑sensitive). wordpress vs WordPress or enqueue vs Enqueue.
• Fix: Rename folders to match namespace exactly. Use the installer’s case‑insensitive map as a fallback.

9.3 Circuit breaker opens (frontend requests blocked)

• Cause: Server returning 500s (often due to connection timeouts or missing tables).
• Fix: Fix the underlying 500 (check PHP logs). The breaker auto‑resets after 30 s (for 401) or 300 s (for 500). You can also call engine.resetCircuit() in JS.

9.4 Role‑credential mismatch (wrong user for database)

• Cause: tsp_sys_role_db_access maps a role to a database, but tsp_sys_core_roles_map has credentials for a different database.
• Fix: Ensure each role has a matching entry in both tables. The populator now assigns roles only to their own databases.

9.5 Token refresh deadlock on 401

• Fixed: _execute() is decoupled from _request(); the refresh promise is single‑flighted. Auth requests bypass the circuit breaker.

10. Development & Production Best Practices

10.1 Environment Variables

• Use IS_PRODUCTION=true on live servers to hide debug info.
• Set SQLITE_ENABLED=false for MySQL; true for SQLite.
• Use ALLOW_PLAINTEXT_SECRETS=true only during migration; turn off after encryption.

10.2 Database Credentials

• Encrypt passwords with CryptoAES::encrypt() and store the ciphertext in .env.
• Use APP_SECRET_KEY (64 hex chars) as the master key.

10.3 Frontend Configuration

• requestDelay should be at least 150 ms to avoid overloading the server.
• concurrency should be low (2–3) on shared hosting.
• Cache TTL can be increased for read‑heavy resources.

10.4 Deployment Checklist

1. Delete installer/ folder after installation.
2. Set INSTALLER_ENABLED=false.
3. Ensure .env is not web‑accessible (.htaccess denies it).
4. Run DbConnectionManager::reset() after changing credentials? (Not needed – restart PHP).
5. Clear opcache and restart PHP after code updates.

10.5 Monitoring

• Enable APP_DEBUG=true temporarily to diagnose issues.
• Monitor Storage/Logs/sepo-*.log for errors.
• Use SHOW PROCESSLIST to check for connection leaks.

11. Troubleshooting Guide (Quick Reference)

12. Useful Code Snippets

12.1 Manually test a database connection

$pdo = DbConnectionManager::ensure('sp_db_auth');
var_dump($pdo->query('SELECT 1')->fetch());