OpenCable — New Screen Implementation Guide

A practical developer walkthrough for adding a new module, database table, API route, and screen to OpenCable. Derived from the Project module implementation.

1. Overview & Decision Map

Adding a new screen involves touching up to six files across three layers plus one database migration. The order matters — each step's output feeds the next.

1. Schema — design table, write migration SQL

↓ run migration against dev27 in MySQL Workbench

2. Database — register module, screens, permissions, role_permissions, department_modules

↓

3. Backend — write src/routes/my-domain.js, mount in server.js

↓

4. Frontend — write src/modules/my-module/screens/my-screen.js

↓

5. Registry — add route entry in src/modules/app/app.js

↓

6. Tenant license — add module code to tenants.json licensed_modules

↓

7. Test in browser — verify nav shows screen, API returns data

Before you start — answer these questions:

Question	Drives
Is this a new module or a new screen inside an existing module?	Whether you need a new module row in the DB + tenants.json entry
Does this screen need a new database table, or does it query existing tables?	Whether you write a migration
Who can view? Who can edit?	Permission names you define and which roles receive them
Is data scoped by building, by tenant, or globally?	Table design: FK to buildings table or not
Is this a read-only dashboard or a maintenance (CRUD) screen?	Which route methods and UI patterns to use

Step 1 — Design the Database Table

Create the DDL

File: TableSQL/TableSQL_dev27/my_table.sql (reference copy)

Design the table with these conventions:

CREATE TABLE IF NOT EXISTS `my_table` (
  `id`          int(11) NOT NULL AUTO_INCREMENT,
  `building_id` int(11) DEFAULT NULL -- NULL = project-level; non-NULL = per-building
                COMMENT 'NULL means not building-specific',
  `some_key`    varchar(100) NOT NULL,
  `some_value`  varchar(500) DEFAULT NULL,
  `updated_by`  int(11) DEFAULT NULL,
  `updated_at`  timestamp NOT NULL DEFAULT current_timestamp()
                ON UPDATE current_timestamp(),
  `created_at`  timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_building_key` (`building_id`, `some_key`),
  CONSTRAINT `fk_mt_building` FOREIGN KEY (`building_id`)
    REFERENCES `buildings` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mt_user` FOREIGN KEY (`updated_by`)
    REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
    

Schema Conventions

Rule	Reason
No `ENUM` types — use `VARCHAR` with a COMMENT listing valid values	Oracle and cross-DB compatibility
Always include `created_at` and `updated_at` timestamps	Consistent audit trail across all tables
Use `execute()` not `query()` for all parameterized SQL	Prevents SQL injection; mysql2 enforces prepared statements
Pass `null`, never `undefined`, for nullable columns	mysql2 rejects `undefined` and throws at runtime
All tables InnoDB, utf8mb4	FK support + full Unicode

⚠️ Key/value store pattern: For flexible, extensible settings (like project_settings), use a (building_id, setting_key) unique constraint and a single setting_value column rather than one column per setting. New setting types can then be added without altering the schema. Define valid key names in application code, not the database.

Step 2 — Write the Migration Script

Create the migration SQL file

File: database/migrations/v27_NNN_description.sql

The migration file must be self-contained and idempotent — safe to run more than once. Sequence the statements so each step's output is available to the next.

-- Migration: v27_NNN_description
-- Purpose: ...
-- Target: dev27 (run via MySQL Workbench; never run directly against dev)
-- ── 1. Table ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `my_table` ( ... );

-- ── 2. Seed data (optional) ──────────────────────────────────────────
INSERT IGNORE INTO `my_table` (`some_key`) VALUES ('default_key');

-- ── 3. Module ─────────────────────────────────────────────────────────
INSERT IGNORE INTO `modules` (`code`, `name`, `description`, `sort_order`, `licensed`, `enabled`)
VALUES ('mymodule', 'My Module', 'Description', 10, 1, 1);

-- ── 4. Screens (use sortseq, not sort_order; use enabled, not active) ─
INSERT IGNORE INTO `screens` (`module_id`, `name`, `route`, `sortseq`, `enabled`)
SELECT m.id, 'My Screen', 'my-screen', 10, 1
FROM modules m WHERE m.code = 'mymodule';

-- ── 5. Permissions (use name column, not code) ────────────────────────
INSERT IGNORE INTO `permissions` (`name`, `description`)
VALUES
  ('mymodule.view',   'View My Module'),
  ('mymodule.manage', 'Manage My Module');

-- ── 6. Role grants ────────────────────────────────────────────────────
-- Give view to ALL roles:
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE p.name = 'mymodule.view';

-- Give manage to specific roles only:
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE p.name = 'mymodule.manage'
  AND r.name IN ('Admin', 'Manager');

-- ── 7. Department access (mode values: 'manage' or 'view') ────────────
INSERT IGNORE INTO `department_modules` (`department_id`, `module_id`, `mode`)
SELECT d.id, m.id, 'manage'
FROM departments d, modules m
WHERE m.code = 'mymodule';
    

⛔ CRITICAL: Run migrations against dev27 only. Open MySQL Workbench, connect to dev27, and run the script there. Never paste migration SQL into a Node.js script that calls getDbPool() — pool routing is not guaranteed, and running against the wrong database is a real risk. The rule: dev27 is the development schema; dev is the deployed database. Changes flow dev27 → tested → exported → imported into dev.

Column Name Reality Check

Before writing INSERT statements for system tables, verify the actual column names. The screens and permissions tables have traps:

Table	Correct column	Wrong assumption
`screens`	`sortseq`, `enabled`	`sort_order`, `active`
`permissions`	`name`	`code`
`department_modules` mode	`'manage'` or `'view'`	`'READ_WRITE'`

When in doubt: DESCRIBE tablename; in MySQL Workbench before writing the INSERT.

Step 3 — Register the Module, Screens & Permissions

Execute the migration in MySQL Workbench against dev27

All DB registration happens inside the migration script from Step 2

After running the migration, verify each section landed correctly:

-- Verification queries — run after migration to confirm
SELECT id, code, name, sort_order FROM modules WHERE code = 'mymodule';
SELECT id, module_id, name, route, sortseq FROM screens
  WHERE module_id = (SELECT id FROM modules WHERE code = 'mymodule');
SELECT id, name FROM permissions WHERE name LIKE 'mymodule.%';
SELECT COUNT(*) AS role_perm_rows FROM role_permissions
  WHERE permission_id IN (SELECT id FROM permissions WHERE name LIKE 'mymodule.%');
SELECT COUNT(*) AS dept_mod_rows FROM department_modules
  WHERE module_id = (SELECT id FROM modules WHERE code = 'mymodule');
    

ℹ️ INSERT IGNORE behavior: If a row already exists (e.g. you're re-running the migration during development), INSERT IGNORE silently skips it. A result of 0 rows inserted does not mean the step failed — query the table directly to confirm the data is there.

How the Nav Query Uses These Tables

Understanding the nav pipeline helps you see why all four registrations are required:

GET /api/auth/nav?department_id=X

↓ JOIN screens → modules → department_modules WHERE department_id = X

Filter: module must be in tenants.json licensed_modules AND modules.licensed=1 AND modules.enabled=1

↓

Returns screens ordered by module.sort_order then screens.sortseq

↓

Frontend renders accordion group per module; screens listed inside

If any link in this chain is missing, the screen silently disappears from the nav — no error. The most common misses: forgetting department_modules, or forgetting to add the module to tenants.json.

Step 4 — Write the API Route

Create src/routes/my-domain.js

One file per domain; all routes share one Express router

import express from "express"
import { checkPermission } from "../middleware/rbac.js"

const router = express.Router()

// ── GET /api/my-domain ────────────────────────────────────────────────
router.get("/", checkPermission("mymodule.view"), async (req, res) => {
    try {
        const [rows] = await req.dbPool.execute(
            "SELECT * FROM my_table WHERE ..."
        )
        res.json(rows)
    } catch (e) {
        console.error("GET /api/my-domain", e)
        res.status(500).json({ message: e.message })
    }
})

// ── PUT /api/my-domain ────────────────────────────────────────────────
router.put("/", checkPermission("mymodule.manage"), async (req, res) => {
    try {
        const { field1, field2 } = req.body
        await req.dbPool.execute(
            `INSERT INTO my_table (col1, col2)
             VALUES (?, ?)
             ON DUPLICATE KEY UPDATE col1 = VALUES(col1)`,
            [field1, field2 || null]   // pass null, never undefined
        )
        res.json({ message: "Saved." })
    } catch (e) {
        console.error("PUT /api/my-domain", e)
        res.status(500).json({ message: e.message })
    }
})

export default router
    

Route Design Rules

Rule	Detail
All routes start with `checkPermission()`	This is the authoritative server-side gate — no exceptions for "read-only" routes
Use `req.dbPool` — never import a pool directly	`contextLoader` middleware attaches the correct tenant pool to every request
Literal routes before wildcard routes	Register `/active` and `/types` before `/:id` or Express will match the literal as an id
Validate `req.params.id` with `isNaN()`	path-to-regexp v8 no longer supports inline regex; validate manually
Always `\|\| null` for optional fields	Converts empty string or undefined to SQL NULL — mysql2 chokes on undefined
Log every mutating operation	Call `logTransaction(req, {...})` from src/utils/logger.js after every INSERT/UPDATE/DELETE

⚠️ Backtick conflict in template literals: SQL uses backticks to quote reserved-word table/column names (e.g. `packages`). Inside a JavaScript template literal, a backtick terminates the string. Either use a regular string, or escape as \`tablename\`. The word packages is a MySQL reserved word and must always be backtick-quoted in SQL.

Step 5 — Wire the Route into server.js

Import and mount the router in server.js

Two lines: one import, one app.use()

// At the top of server.js with other route imports
import myDomainRoutes from "./src/routes/my-domain.js"

// In the route-mounting section
app.use("/api/my-domain", myDomainRoutes)
    

The prefix you choose here (/api/my-domain) is the base URL for all routes inside that router. All safeFetch calls in your screen module use this prefix.

ℹ️ Server must be restarted after changing server.js. Node.js does not hot-reload ES modules. Kill the process and run node server.js again before testing.

Step 6 — Write the Screen JS Module

Create src/modules/my-module/screens/my-screen.js

The exported function name must match the route string in PascalCase

Naming Convention — Critical

The route string drives the function name. The navigate() function in app.js derives the render function name automatically:

Route string (DB + registry)	Required export name
`project-dashboard`	`renderProjectDashboard`
`my-screen`	`renderMyScreen`
`cable-register-maintenance`	`renderCableRegisterMaintenance`

Rule: kebab-case route → split on - → capitalize each word → prefix with render.

Module Skeleton — Dashboard / Read-Only Screen

import { safeFetch, hasPermission } from "../../app/app.js"
import { showToast }               from "../../../utils/toast.js"
import { dedent, escHtml }         from "../../../utils/dom.js"

export async function renderMyScreen(workspace) {
    // 1. Check permissions up front
    const canManage = hasPermission("mymodule.manage")

    // 2. Render scaffold immediately (user sees structure, not blank screen)
    workspace.innerHTML = dedent(`
<div class="screen-header">
  <div class="header-title-group"><h2>My Screen</h2></div>
  <div class="controls">
    <button id="btn-refresh">Refresh</button>
    ${canManage ? '<button id="btn-save">Save</button>' : ''}
  </div>
</div>
<div id="screen-body"><p>Loading…</p></div>`)

    // 3. Wire static events
    workspace.querySelector("#btn-refresh").onclick = () => load(workspace)

    // 4. Load data
    await load(workspace)
}

async function load(workspace) {
    const res = await safeFetch("/api/my-domain")
    if (!res.ok) {
        workspace.querySelector("#screen-body").innerHTML =
            `<p style="color:var(--danger-color);">Error: ${escHtml(res.error)}</p>`
        return
    }
    render(workspace, res.data)
}

function render(workspace, data) {
    workspace.querySelector("#screen-body").innerHTML = dedent(`
      <!-- Build your UI from data here -->
    `)
}
    

Module Skeleton — Maintenance (CRUD) Screen

import { safeFetch, hasPermission } from "../../app/app.js"
import { showToast }               from "../../../utils/toast.js"
import { dedent, escHtml }         from "../../../utils/dom.js"
import { makeTableSortable }       from "../../../utils/sort.js"

let _canManage = false
let _allRows   = []

export async function renderMyMaintenance(workspace) {
    _canManage = hasPermission("mymodule.manage")
    workspace.innerHTML = buildScaffold()
    wireEvents(workspace)
    await loadData(workspace)
}

function buildScaffold() {
    return dedent(`
<div class="screen-header">
  <div class="header-title-group"><h2>My Records</h2></div>
  <div class="controls">
    ${_canManage ? '<button id="btn-add" class="btn-primary">+ Add</button>' : ''}
  </div>
</div>
<div class="table-container">
  <table class="data-table">
    <thead><tr>
      <th>Name</th><th>Description</th>
      <th class="col-actions">Actions</th>
    </tr></thead>
    <tbody id="tbody"></tbody>
  </table>
</div>
<div id="modal-placeholder"></div>`)
}

function wireEvents(workspace) {
    workspace.querySelector("#btn-add")?.addEventListener("click", () => openModal(workspace))
    workspace.querySelector("#tbody").addEventListener("click", e => {
        const btn = e.target.closest("[data-action]")
        if (!btn) return
        const id = btn.dataset.id
        if (btn.dataset.action === "edit")   openModal(workspace, id)
        if (btn.dataset.action === "delete") deleteRow(workspace, id)
    })
}
    

Key Frontend Patterns

Pattern	How
Permission-gated controls	`hasPermission("mymodule.manage")` — reads `sessionStorage.userPermissions`; hide buttons in template literal ternary
All API calls via safeFetch	Never use raw `fetch()`; `safeFetch` adds auth headers and normalizes error responses
Event delegation on tbody	One listener handles all row buttons — no per-row event attachment after render
Client-side filtering	Cache full dataset in module-level `_allRows`; filter + re-render without re-fetching
Single modal for create + edit	Hidden `<input id="record-id">`; empty → POST, populated → PUT
Always escHtml() user data	Any value coming from the DB that renders into HTML must go through `escHtml()` to prevent XSS

⚠️ No inline onclick attributes. All event listeners are attached programmatically after workspace.innerHTML is set. Inline onclick="..." attributes do not work with the CSP (script-src 'self') and will be silently blocked.

Step 7 — Register the Screen in app.js

Add the route → file path mapping in the route registry

File: src/modules/app/app.js — inside the navigate() function

// In the routeRegistry object inside navigate() in app.js
"my-screen":      "/src/modules/my-module/screens/my-screen.js",
"my-maintenance": "/src/modules/my-module/screens/my-maintenance.js",
    

The key must exactly match the route column value in the screens database table. The value is the server-relative path to the JS file (no leading . — it is fetched by the browser as a module).

ℹ️ What happens on mismatch: If the route is in the DB but not in the registry, the nav link appears but clicking it renders a "Screen not found" 404 panel. If it's in the registry but not the DB, the screen is unreachable via the nav but could still be loaded by directly calling navigate("my-screen") from the console.

Step 8 — License the Module for Each Tenant

Add the module code to licensed_modules in tenants.json

File: src/config/tenants.json

{
  "dev": {
    "licensed_modules": [
      "mymodule",   // ← add this
      "admin", "reference", "cables" // ... existing entries
    ]
  }
}
    

The nav query filters screens to only those whose module code appears in this array. A missing entry here means no one can see the screens in that tenant, regardless of database permissions — the query silently returns nothing.

⚠️ tenants.json is not a migration. It lives in the application code, not the database. It must be updated separately for each tenant that should have access to the module. The dev tenant is used for local development (pointed to dev27 via DB_NAME in .env).

Step 9 — Test End-to-End

Restart the server and verify in the browser

Test the full path from nav to data

Restart Node: kill server → node server.js

↓ confirm "dev DB → root@localhost/dev27" in startup output

↓ if missing: check department_modules, tenants.json, modules.enabled=1

Click the screen link → confirm screen renders without console errors

↓ if 404 panel: route string in DB doesn't match routeRegistry key

Open DevTools Network tab → confirm API call returns 200 with expected data

↓ if 403: role doesn't have the required permission in role_permissions

If canManage=true: test save/edit path; if canManage=false: confirm controls hidden

Debugging Missing Nav Entries

If the screen doesn't appear in the nav after doing everything above, work through this query in MySQL Workbench to trace where the chain breaks:

-- 1. Does the module exist and is it enabled?
SELECT id, code, name, sort_order, licensed, enabled FROM modules WHERE code = 'mymodule';

-- 2. Are the screens registered?
SELECT id, name, route, sortseq, enabled, module_id FROM screens
WHERE module_id = (SELECT id FROM modules WHERE code = 'mymodule');

-- 3. Does department X have access to this module?
SELECT * FROM department_modules
WHERE module_id = (SELECT id FROM modules WHERE code = 'mymodule');

-- 4. Full nav simulation for department X (replace 5 with actual dept id)
SELECT s.name, s.route, m.code, dm.mode
FROM screens s
JOIN modules m ON m.id = s.module_id
JOIN department_modules dm ON dm.module_id = m.id AND dm.department_id = 5
WHERE s.enabled = 1 AND m.enabled = 1 AND m.code = 'mymodule';
    

Common Pitfalls & Gotchas

Wrong database target. All schema changes go to dev27 only. The .env file sets DB_NAME=dev27, which overrides the dev tenant pool. The server's startup log will show dev DB → root@localhost/dev27 — verify this before running migrations via Node scripts. The safest path: always use MySQL Workbench directly against dev27 for DDL and DML migration work.

Wrong column names in screens/permissions inserts. The screens table uses sortseq (not sort_order) and enabled (not active). The permissions table uses name (not code). Always run DESCRIBE tablename; when writing INSERTs for system tables you haven't touched recently.

Wrong mode value in department_modules. Valid values are 'manage' and 'view'. Using 'READ_WRITE' or anything else will cause the INSERT to silently fail (row count = 0) due to the column's implicit constraint. The screen then disappears from all department navs with no error.

INSERT IGNORE returning 0 looks like failure but isn't. When re-running a migration, INSERT IGNORE skips rows that already exist. Zero rows affected does not mean the data is absent. Always follow up with a SELECT to confirm the expected rows are there.

Backticks inside JavaScript template literals. SQL identifiers like `packages` (a MySQL reserved word) contain backticks. Inside a JS template literal these terminate the string. Use a regular string or escape as \`packages\`. The word packages is a known trap — it must be quoted in every SQL statement that references it.

Function name doesn't match route string. The navigate() function derives the render function name from the route string automatically. "project-dashboard" must export renderProjectDashboard — not renderDashboard, not render. A mismatch causes a silent "function not found" error and nothing renders.

Inline onclick= blocked by CSP. The Content Security Policy enforces script-src 'self'. Inline event handlers set via HTML attributes are blocked silently — the click does nothing. All event listeners must be attached with addEventListener() after the HTML is inserted.

Raw fetch() instead of safeFetch(). safeFetch() adds the X-User-ID and X-Tenant-ID headers required by contextLoader middleware. Without them, every API call returns 401 and req.user is undefined in the route handler.

undefined passed as a SQL parameter. mysql2 throws at runtime if undefined appears in a parameter array. Always use value || null or explicit null for nullable columns. Empty strings from form inputs must also be converted: input.value.trim() || null.

tenants.json not updated. Even with the module correctly registered in the DB, the nav query won't return its screens unless the module code appears in licensed_modules for the active tenant in tenants.json. This is a separate code-side configuration — it is not in the database.

Final Checklist

Use this list to verify every new screen before considering it complete:

Database

Migration script created in database/migrations/v27_NNN_description.sql
Migration executed against dev27 via MySQL Workbench (not via Node script)
modules row exists with correct code, sort_order, licensed=1, enabled=1
screens rows exist with correct route, module_id, sortseq, enabled=1
permissions rows exist with correct name (format: module.action)
role_permissions rows grant view permission to all applicable roles
role_permissions rows grant manage permission to restricted roles only
department_modules rows grant module access to all applicable departments (mode = 'manage' or 'view')

Backend

Route file created in src/routes/my-domain.js
Every route starts with checkPermission()
All parameterized queries use execute() not query()
All nullable parameters pass null not undefined
Mutating routes call logTransaction()
Route imported and mounted in server.js

Frontend

Screen file created in src/modules/my-module/screens/my-screen.js
Export function name matches route string converted to PascalCase with render prefix
Permission check via hasPermission() gates edit controls
All API calls use safeFetch()
All user-sourced data rendered into HTML passes through escHtml()
No inline onclick= attributes — all listeners use addEventListener()
Route key added to src/modules/app/app.js route registry

Tenant Configuration

Module code added to licensed_modules in src/config/tenants.json for each applicable tenant

Smoke Test

Server restarted; startup log confirms correct database (root@localhost/dev27)
Module appears in sidebar nav accordion after login
Screen renders without console errors
API endpoint returns 200 with expected data (check Network tab)
Permissions enforced: manage-only controls hidden for view-only roles
Save/edit path tested (if applicable) — toast confirms success, data reflects change on reload