OpenCable — Application Design Document

OpenCable is an enterprise cable and electrical infrastructure management application. It is a Single-Page Application (SPA) with a Node.js/Express REST API backend and a Vanilla JavaScript frontend. There is no client-side framework — all UI is built with ES modules, template literals, and standard DOM APIs.

The application manages:

• Electrical infrastructure — buildings, rooms, raceways, cables, buses, generators, loads
• Cable routing — segment-by-segment path tracking through raceway topology
• Cable and raceway analysis — voltage drop, fill calculations, aging assessment
• Work package lifecycle — creation through approval and closure
• Full audit trail — every create, update, and delete is logged to transaction_logs
• Role-based access control — permissions enforced both server-side and in the UI
• Multi-tenant operation — multiple isolated databases served by one Node.js process

JavaScript

• Strictly Vanilla ES6+ — no React, Vue, Angular, or jQuery
• All modules use import / export — no CommonJS require() on the frontend
• Module-level state variables (e.g. let _allRows = []) replace global state
• No inline onclick attributes — all event listeners are attached programmatically
• Event delegation on <tbody> for table row actions — one listener per table, not one per row
• All API calls go through safeFetch() — never raw fetch() in screen modules

SQL Conventions

• No ENUM types — use VARCHAR(N) with a comment listing valid values (Oracle compatibility)
• All parameterized queries use execute() not query() for SQL injection safety
• Pass null not undefined for nullable columns — mysql2 rejects undefined
• Route params validated with isNaN(req.params.id) guard — path-to-regexp (new version) does not support inline regex
• Literal routes (e.g. /active, /types) must be registered before wildcard routes (/:id)
• packages is a MySQL reserved word — always backtick it in SQL strings (not in JS template literals)

CSS Architecture

HTML Template Pattern

Screen modules inject their entire HTML structure via workspace.innerHTML = dedent(`...`) exactly once on load. The dedent() utility (in src/utils/dom.js) strips leading indentation from template literals so the rendered HTML is clean.

Request Flow

Authentication Model

Authentication is header-based, stateless. There are no server-side sessions or cookies.

• On login, the server returns a user object stored as JSON in sessionStorage.currentUser
• Every subsequent API call includes X-User-ID (the user's numeric database ID)
• The src/middleware/contextLoader.js middleware reads this header, queries the users table, and populates req.user for every request
• Closing the browser tab clears sessionStorage — the user is effectively logged out

Multi-Tenant Architecture

One Node.js process serves multiple tenants. Each tenant has its own isolated MariaDB database.

• Tenant is selected via ?tenant=xxx URL parameter on first load
• Stored as sessionStorage.tenantId and sent as X-Tenant-ID on every request
• contextLoader reads X-Tenant-ID and attaches the correct pool to req.dbPool
• Tenant UI config (title, logo, colors) served via GET /api/public/config/:tenantId — no auth required
• Tenant database credentials and licensed modules defined in src/config/tenants.json

Data Model

Permission Enforcement

Permissions are enforced at two independent levels — both must pass:

Permissions are fetched once after login and after every context switch via GET /api/auth/permissions, stored as a flat JSON array in sessionStorage.userPermissions.

Context Switching

Users with multiple department/role assignments can switch context via the sidebar dropdowns at any time. Switching calls POST /api/auth/set-context, which persists the new selection to the users table, then refreshes permissions and rebuilds the sidebar navigation — no page reload.

Navigation Filtering

The sidebar nav is built dynamically from GET /api/auth/nav?department_id=X&role_id=Y. The server returns only screens assigned to the user's department via department_screens. Screens are grouped by module and rendered as collapsible accordion sections, all collapsed by default.

Core Tables

Schema Constraints

• All tables use InnoDB, utf8mb4 charset
• Foreign keys enforced at DB level — dependent data blocks deletes (returns 409 via friendly pre-check)
• No ENUM types — VARCHAR with comment lists valid values
• created_at / updated_at timestamps on every table with DEFAULT current_timestamp() and ON UPDATE current_timestamp()

Physical Hierarchy

Raceways

Raceways are the universal container for all physical routing elements. Type is determined by record_code_id FK to the record_codes table — not a separate type table.

Raceway topology is stored in raceway_connections as bidirectional pairs (raceway_a_id, raceway_b_id). A duplicate guard prevents both (a,b) and (b,a) from existing.

Cable Register

Cable Segment Routing

Stored in cable_segment_routing. Each row is one step in the cable's path:

Segment status values: ACTIVE REMOVED (soft-deleted, can be restored) REPLACED (superseded by a replacement at same seq).

The Approve action purges all REMOVED and REPLACED rows, resequences ACTIVE intermediates as 10/20/30…, sets routing_status = APPROVED, and sets is_autoroute_locked = 1.

Cable Types

Stored in cable_types. Key analysis fields:

Cable Analysis

The cable_problems table stores detected issues per cable. Problem types include:

Loadflow

Buses, generators, and loads form the electrical loadflow model. Loads link to buses via bus_id. The loadflow_cable_links and loadflow_link_cables tables connect load records to cable register entries for linker analysis.

Work packages (formerly called "workorders") track authorized changes through a formal lifecycle. The active package is selected in the sidebar Active Package dropdown and sent as X-Workorder-Name on every API request.

Lifecycle Status

Key Fields

requireWorkorder()

Screens that require a package for changes call requireWorkorder() before write operations. It checks sessionStorage.currentUser.work_id and shows a toast error if no active package is selected.

logTransaction()

Every successful create, update, and delete operation calls logTransaction(req, options) from src/utils/logger.js. It writes one row to transaction_logs capturing:

Staged Change Management

Some screens support a "stage then apply" workflow using stagePendingTransaction(). This writes a log entry with execution_status = 'PENDING' and a JSON proposed_payload without touching the live data tables. The execution engine applies PENDING logs when a package reaches Approved status.

Key behaviors:

• Pessimistic locking: isRecordLocked() checks if a different package already has a PENDING entry for the same record. If so, the operation is blocked.
• Upsert logic: If the current package already has a PENDING entry for the same record, stagePendingTransaction() updates it (net-change) rather than creating a duplicate.

generateChangeLog()

Produces a field-level diff string for UPDATE operations: "field: 'old' -> 'new' | field2: 'old' -> 'new'". Skips created_at and updated_at timestamps.

Frontend Audit Viewer

The renderTransactionLogs component (imported in admin screens) can be embedded in any modal to show a filtered history for a specific record name or table. Every maintenance screen that supports history shows a 📋 icon button per row.

The following electrical screens write changes directly to the database on every save — no staging, no package required:

Single-Page Application Model

The application never performs a full page reload during normal use. All screen transitions are handled by navigate(route) in src/modules/app/app.js:

1. Clears #workspace-content with a "Loading…" placeholder
2. Looks up the route in the static routeRegistry map
3. Dynamically imports the screen's ES module: import(path)
4. Derives the render function name: "cable-register-maintenance" → renderCableRegisterMaintenance
5. Calls module[funcName](workspaceElement)

Route Registry

Located in the navigate() function body in src/modules/app/app.js. Every screen JS file must have a corresponding entry. Unregistered routes display a 404 panel.

Cable Routing Engine

Two separate routing algorithms are available via the Link Routing screen:

All maintenance screens follow a consistent pattern. Use src/modules/electrical/screens/bus-maintenance.js as the canonical reference.

Module Structure

HTML Scaffold

Action Buttons

Row action buttons use data-action and data-id attributes. A single event listener on the <tbody> handles all row actions via event delegation:

Create vs Edit Modal

One modal serves both create and edit. A hidden <input type="hidden" id="record-id"> field determines which path runs on save:

• Empty ID → POST to /api/resource (create)
• Populated ID → PUT to /api/resource/:id (update)

Table Sorting & Selection

After rendering rows call makeTableSortable(tableEl) from src/utils/sort.js. Clicking a sortable <th> cycles asc → desc → original. For screens with bulk delete, also call makeTableSelectable(tableEl) from src/utils/table-select.js — adds row checkboxes and a select-all header checkbox.

Client-Side Filtering

Screens with filter dropdowns cache the full dataset in a module-level variable (e.g. _allRows) and re-render with renderRows() on filter change — no additional API call. The full list is only re-fetched on Refresh.

PDF Generation — CSS Print Isolation

SMECO uses the browser's native print engine. No third-party PDF library is needed.

• Strategy: @media print rules in reports.css set visibility: hidden on the sidebar, header, and all controls
• Report isolation: Only .rpt-container is un-hidden; absolute positioning snaps it to the top-left of the printed page
• Filter summary: A hidden #print-filter-summary block becomes visible only in print — permanently records active filters on the PDF
• Trigger: A "Print / Save PDF" button calls window.print()

CSV Export — Client-Side Blob

CSV is generated entirely in the browser without a backend call:

1. The screen maps its current filtered data array to a comma-separated string
2. The string is wrapped in a Blob with MIME type text/csv
3. A temporary <a> tag with a Blob URL is programmatically clicked, triggering the browser's download prompt
4. The anchor and Blob URL are immediately revoked and removed

Step 1 — Create the screen JS module

The export function name must be the route string converted to PascalCase: "my-screen" → renderMyScreen.

Step 2 — Register in app.js route registry

Step 3 — Insert screen record in the database

Step 4 — Assign screen to a department

Navigate to Admin → Department Screen Maintenance and assign the new screen to the appropriate department(s). The screen will automatically appear in the sidebar accordion for users in those departments on next login or context switch.

Login & Navigation Initialization

Screen Navigation (SPA)

Direct-Write Edit Flow

Cable Routing — Link Walk Flow

DigitalOcean App Platform

The application is deployed on DigitalOcean App Platform (Node.js web service) connected to a DO Managed MySQL cluster. All configuration is injected via environment variables — no credentials are stored in committed files.

Environment Variables

Database Connection — DATABASE_URL

src/config/db.js checks for DATABASE_URL first and parses host, port, user, password, and database name from it. Falls back to individual env vars for local development.

PREVIEW_MODE

Set PREVIEW_MODE=true to put the application in read-only preview mode. Used on opencable.org until a production tenant database is confirmed stable.

DNS & Domains

Deploy Process

DO App Platform auto-deploys on every push to the v26-development branch (connected in the DO app settings). Build command: npm install. Run command: node server.js.

The src/docs/ folder is served as static files at /docs/ — no separate deployment step. Pushing a new file to src/docs/samples/ makes it live at https://opencable.org/docs/samples/filename after the next auto-deploy.