pliceclogs-og/docs/superpowers/plans/2026-04-12-inventory-sku-lookup.md

Inventory SKU Lookup — Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: When the popup opens on a Discogs release page with the feature toggled on, automatically SSH-tunnel to the VPS MariaDB, look up inventory SKUs for the release, and fill the Discogs collection SKU field(s) — or show a manual picker if row/box counts don't match.

Architecture: A new /inventory-lookup endpoint is added to the existing rfid-daemon (port 7790). It opens an SSH tunnel via ssh2 to mrpadmin@<dbHost>, queries wp_rmp_disc_inventory via mysql2, and returns sorted rows. popup.js auto-triggers this on load when enabled, auto-fills matching boxes, or renders a click-to-assign picker on mismatch.

Tech Stack: Node.js (ssh2, mysql2), Chrome Extension MV3 (chrome.storage.local, chrome.tabs.sendMessage), existing updateDiscogsSku content-script message.

---

File Map

File	Action	What changes
rfid-daemon/package.json	Modify	Add ssh2, mysql2 deps
rfid-daemon/index.js	Modify	Add /inventory-lookup endpoint
popup.html	Modify	Add inventory settings fields in Connect section + #inventory-sku-panel div in Discogs tab
popup.js	Modify	Save/load inventorySettings, auto-trigger inventorySkuLookup(), picker render/interaction

---

Task 1: Add npm dependencies to rfid-daemon

Files:

• Modify: rfid-daemon/package.json

• Step 1: Add ssh2 and mysql2 to package.json

Replace the dependencies block in rfid-daemon/package.json:

{
  "name": "rfid-daemon",
  "version": "1.0.0",
  "description": "Local HTTP daemon bridging Chafon H102 UHF RFID reader to browser extension",
  "main": "index.js",
  "scripts": {
    "start": "node index.js"
  },
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.18.2",
    "serialport": "^12.0.0",
    "ssh2": "^1.16.0",
    "mysql2": "^3.9.0"
  }
}

• Step 2: Install the new dependencies

cd /Users/jingk/Documents/pliceclogs-ultra-yt-omega-999999-truth/rfid-daemon
npm install

Expected: added N packages with no errors. node_modules/ssh2 and node_modules/mysql2 now exist.

• Step 3: Verify imports load without error

cd /Users/jingk/Documents/pliceclogs-ultra-yt-omega-999999-truth/rfid-daemon
node -e "require('ssh2'); require('mysql2/promise'); console.log('OK')"

Expected output: OK

• Step 4: Commit

cd /Users/jingk/Documents/pliceclogs-ultra-yt-omega-999999-truth/rfid-daemon
git add package.json package-lock.json
git commit -m "feat: add ssh2 and mysql2 deps for inventory lookup"

---

Task 2: Add /inventory-lookup endpoint to rfid-daemon

Files:

• Modify: rfid-daemon/index.js

The endpoint receives release_id, dbHost, dbName, dbUser, dbPass, sshKeyPath as query params. It SSH-tunnels to mrpadmin@dbHost, connects mysql2 through the tunnel stream, queries wp_rmp_disc_inventory, and returns rows sorted ASC by sku.

• Step 1: Add require statements at the top of rfid-daemon/index.js

After the existing require lines at the top of rfid-daemon/index.js (after the const cors = require('cors'); line), add:

const { Client: SshClient } = require('ssh2');
const mysql = require('mysql2/promise');
const fs   = require('fs');

• Step 2: Add the inventoryLookup helper function

Add this function anywhere before the app.listen(...) call at the bottom of rfid-daemon/index.js:

// ── Inventory lookup via SSH tunnel ──────────────────────────────────────────

async function inventoryLookup({ releaseId, dbHost, dbName, dbUser, dbPass, sshKeyPath }) {
  const keyPath = (sshKeyPath || '~/.ssh/id_rsa').replace(/^~/, os.homedir());

  let privateKey;
  try {
    privateKey = fs.readFileSync(keyPath);
  } catch (e) {
    throw new Error(`Cannot read SSH key at ${keyPath}: ${e.message}`);
  }

  return new Promise((resolve, reject) => {
    const ssh = new SshClient();

    ssh.on('ready', () => {
      ssh.forwardOut('127.0.0.1', 0, '127.0.0.1', 3306, async (err, stream) => {
        if (err) { ssh.end(); return reject(new Error(`SSH forward failed: ${err.message}`)); }

        try {
          const conn = await mysql.createConnection({
            host: '127.0.0.1',
            user: dbUser,
            password: dbPass,
            database: dbName,
            stream
          });

          const [rows] = await conn.execute(
            'SELECT sku, price, media_condition, sleeve_condition FROM wp_rmp_disc_inventory WHERE release_id = ? ORDER BY sku ASC',
            [parseInt(releaseId, 10)]
          );

          await conn.end();
          ssh.end();
          resolve(rows);
        } catch (e) {
          ssh.end();
          reject(e);
        }
      });
    });

    ssh.on('error', (e) => reject(new Error(`SSH error: ${e.message}`)));

    ssh.connect({
      host: dbHost || '100.123.123.64',
      port: 22,
      username: 'mrpadmin',
      privateKey
    });
  });
}

• Step 3: Add the /inventory-lookup express route

Add this route block immediately after the inventoryLookup function (still before app.listen):

app.get('/inventory-lookup', async (req, res) => {
  const { release_id, dbHost, dbName, dbUser, dbPass, sshKeyPath } = req.query;

  if (!release_id) {
    return res.status(400).json({ error: 'release_id is required' });
  }
  if (!dbUser || !dbPass || !dbName) {
    return res.status(400).json({ error: 'dbUser, dbPass, and dbName are required' });
  }

  try {
    const rows = await inventoryLookup({
      releaseId: release_id,
      dbHost: dbHost || '100.123.123.64',
      dbName,
      dbUser,
      dbPass,
      sshKeyPath: sshKeyPath || '~/.ssh/id_rsa'
    });

    res.json({
      rows: rows.map(r => ({
        sku:             r.sku,
        price:           r.price != null ? String(r.price) : null,
        media_condition:  r.media_condition  || null,
        sleeve_condition: r.sleeve_condition || null
      }))
    });
  } catch (e) {
    console.error('[inventory-lookup]', e.message);
    res.status(500).json({ error: e.message });
  }
});

• Step 4: Smoke-test the endpoint (daemon must be running)

Start the daemon in one terminal:

cd /Users/jingk/Documents/pliceclogs-ultra-yt-omega-999999-truth/rfid-daemon
node index.js -q

In another terminal, substitute real values for DB_USER, DB_PASS, DB_NAME, RELEASE_ID:

curl "http://localhost:7790/inventory-lookup?release_id=RELEASE_ID&dbHost=100.123.123.64&dbName=DB_NAME&dbUser=DB_USER&dbPass=DB_PASS"

Expected: {"rows":[...]} — either an array of objects or an empty array. A JSON error object means SSH/DB credentials are wrong. A connection refused means the daemon isn't running.

• Step 5: Commit

cd /Users/jingk/Documents/pliceclogs-ultra-yt-omega-999999-truth
git add rfid-daemon/index.js
git commit -m "feat: add /inventory-lookup SSH-tunnel endpoint to rfid-daemon"

---

Task 3: Add settings UI to popup.html

Files:

• Modify: popup.html

Two changes: (A) inventory settings fields inside the Connect collapsible, and (B) a status/picker panel in the Discogs tab.

• Step 1: Add inventory settings fields in the Connect section

In popup.html, locate the line:

                    <button id="save-connect-settings">Save Connect Settings</button>

Insert the following block immediately before that line:

                    <!-- Inventory SKU Lookup settings -->
                    <div style="border-top:1px solid #ddd; margin-top:10px; padding-top:10px;">
                        <div style="font-size:12px; font-weight:600; color:#444; margin-bottom:6px;">Inventory SKU Lookup</div>
                        <label class="settings-check-item" style="display:flex; align-items:center; gap:6px; margin-bottom:6px;">
                            <input type="checkbox" id="inventorySkuEnabled">
                            <span style="font-size:12px;">Auto-fill SKU on popup open</span>
                        </label>
                        <div class="input-group">
                            <label for="inventory-db-host">DB Host:</label>
                            <input type="text" id="inventory-db-host" placeholder="100.123.123.64">
                        </div>
                        <div class="input-group">
                            <label for="inventory-db-name">DB Name:</label>
                            <input type="text" id="inventory-db-name" placeholder="wp_rmp_disc_">
                        </div>
                        <div class="input-group">
                            <label for="inventory-db-user">DB User:</label>
                            <input type="text" id="inventory-db-user" autocomplete="off">
                        </div>
                        <div class="input-group">
                            <label for="inventory-db-pass">DB Password:</label>
                            <input type="password" id="inventory-db-pass" autocomplete="off">
                        </div>
                        <div class="input-group">
                            <label for="inventory-ssh-key">SSH Key Path:</label>
                            <input type="text" id="inventory-ssh-key" placeholder="~/.ssh/id_rsa">
                        </div>
                    </div>

• Step 2: Add the inventory status and picker panel in the Discogs tab

In popup.html, locate the line:

                <div id="gemini-sync-indicator" style="font-size:11px; margin-bottom:4px; display:none; transition:opacity 1.5s;"></div>

Insert the following block immediately after that line:

                <!-- Inventory SKU auto-fill: status line + mismatch picker -->
                <div id="inventory-sku-panel" style="display:none; margin-bottom:6px;">
                    <div id="inventory-sku-status" style="font-size:11px; color:#555; margin-bottom:4px;"></div>
                    <div id="inventory-sku-picker" style="display:none;">
                        <div id="inventory-sku-cards" style="display:flex; flex-direction:column; gap:4px; margin-bottom:6px;"></div>
                        <div style="font-size:11px; color:#666; margin-bottom:3px;">Write selected SKU to box:</div>
                        <div id="inventory-sku-box-buttons" style="display:flex; gap:4px; flex-wrap:wrap;"></div>
                    </div>
                </div>

• Step 3: Verify HTML is valid — open the extension popup on any Discogs page

Load the extension in Chrome (chrome://extensions → Load unpacked). Open a Discogs release page, click the extension icon. The popup should open without errors in DevTools console. The Connect settings section should show the new Inventory SKU Lookup fields when expanded.

• Step 4: Commit

cd /Users/jingk/Documents/pliceclogs-ultra-yt-omega-999999-truth
git add popup.html
git commit -m "feat: add inventory SKU lookup settings and picker panel to popup"

---

Task 4: Wire inventory settings save and load in popup.js

Files:

• Modify: popup.js

• Step 1: Add loadInventorySettings function

Find the function loadWpSettings or loadGoogleSettings in popup.js (around line 460 or 736). Add the following new function in the same area (after either of those functions):

function loadInventorySettings() {
    chrome.storage.local.get(['inventorySettings'], (result) => {
        const s = result.inventorySettings || {};
        const el = (id) => document.getElementById(id);
        if (el('inventorySkuEnabled'))  el('inventorySkuEnabled').checked     = !!s.enabled;
        if (el('inventory-db-host'))    el('inventory-db-host').value         = s.dbHost    || '100.123.123.64';
        if (el('inventory-db-name'))    el('inventory-db-name').value         = s.dbName    || 'wp_rmp_disc_';
        if (el('inventory-db-user'))    el('inventory-db-user').value         = s.dbUser    || '';
        if (el('inventory-db-pass'))    el('inventory-db-pass').value         = s.dbPass    || '';
        if (el('inventory-ssh-key'))    el('inventory-ssh-key').value         = s.sshKeyPath || '~/.ssh/id_rsa';
    });
}

• Step 2: Call loadInventorySettings on popup init

Find the lines where loadWpSettings() and loadGoogleSettings() are called (around line 736-737). Add the new call immediately after them:

        loadInventorySettings();

• Step 3: Add inventory settings save to the save-connect-settings handler

Find the end of the save-connect-settings click handler. It ends with:

        } else {
            console.log('No Discogs token to save (field empty)');
        }
    });

Insert the following block inside the handler, immediately before the closing }); of the handler (i.e., after the Discogs token save block):

        // Save Inventory SKU Lookup settings
        const inventorySettings = {
            enabled:    document.getElementById('inventorySkuEnabled').checked,
            dbHost:     document.getElementById('inventory-db-host').value.trim()    || '100.123.123.64',
            dbName:     document.getElementById('inventory-db-name').value.trim()    || 'wp_rmp_disc_',
            dbUser:     document.getElementById('inventory-db-user').value.trim(),
            dbPass:     document.getElementById('inventory-db-pass').value,
            sshKeyPath: document.getElementById('inventory-ssh-key').value.trim()    || '~/.ssh/id_rsa'
        };
        chrome.storage.local.set({ inventorySettings }, () => {
            if (chrome.runtime.lastError) {
                console.error('Error saving inventory settings:', chrome.runtime.lastError);
            } else {
                console.log('Inventory settings saved.');
            }
        });

• Step 4: Manual test — save and reload

1. Open the extension popup, go to Settings → Connect (expand it).
2. Fill in the Inventory SKU Lookup fields with test values and check the toggle.
3. Click Save Connect Settings.
4. Close and reopen the popup, go back to Settings → Connect.
5. The fields should be repopulated with the values you entered.

• Step 5: Commit

cd /Users/jingk/Documents/pliceclogs-ultra-yt-omega-999999-truth
git add popup.js
git commit -m "feat: save and load inventorySettings in Connect settings"

---

Task 5: Auto-trigger inventory lookup on popup load

Files:

• Modify: popup.js

This task adds the inventorySkuLookup(releaseId) function and calls it automatically after fetchAndDisplayReleaseData completes, using state.collectionBoxCount (already populated by that point).

• Step 1: Add the inventorySkuLookup function

Add this function near handleImportInventory (around line 2092 in popup.js):

async function inventorySkuLookup(releaseId) {
    const settings = await new Promise(resolve =>
        chrome.storage.local.get(['inventorySettings'], r => resolve(r.inventorySettings || {}))
    );

    if (!settings.enabled) return;
    if (!settings.dbUser || !settings.dbPass || !settings.dbName) return;

    const panel  = document.getElementById('inventory-sku-panel');
    const status = document.getElementById('inventory-sku-status');
    if (!panel || !status) return;

    panel.style.display = 'block';
    status.textContent  = 'Looking up inventory SKUs…';
    status.style.color  = '#888';

    let rows;
    try {
        const params = new URLSearchParams({
            release_id: releaseId,
            dbHost:     settings.dbHost     || '100.123.123.64',
            dbName:     settings.dbName     || 'wp_rmp_disc_',
            dbUser:     settings.dbUser,
            dbPass:     settings.dbPass,
            sshKeyPath: settings.sshKeyPath || '~/.ssh/id_rsa'
        });
        const resp = await fetch(`http://localhost:7790/inventory-lookup?${params}`);
        const data = await resp.json();
        if (data.error) throw new Error(data.error);
        rows = data.rows || [];
    } catch (e) {
        status.textContent = `Inventory lookup failed: ${e.message}`;
        status.style.color = '#c00';
        return;
    }

    if (rows.length === 0) {
        panel.style.display = 'none';
        return;
    }

    const boxCount   = state.collectionBoxCount || 1;
    const lastBoxIdx = boxCount - 1;

    // ── Auto-fill cases ───────────────────────────────────────────────────────
    if (rows.length === 1) {
        // Single row → write to last collection box silently
        try {
            await chrome.tabs.sendMessage(state.discogsTabId, {
                action: 'updateDiscogsSku',
                sku: rows[0].sku,
                boxIndex: lastBoxIdx
            });
            status.textContent = 'SKU filled';
            status.style.color = '#2a7';
            setTimeout(() => { panel.style.display = 'none'; }, 3000);
        } catch (e) {
            status.textContent = `Failed to fill SKU: ${e.message}`;
            status.style.color = '#c00';
        }
        return;
    }

    if (rows.length === boxCount) {
        // Matching counts → fill each box in order (earliest SKU first, already sorted ASC)
        let ok = true;
        for (let i = 0; i < rows.length; i++) {
            try {
                await chrome.tabs.sendMessage(state.discogsTabId, {
                    action: 'updateDiscogsSku',
                    sku: rows[i].sku,
                    boxIndex: i
                });
            } catch (e) {
                ok = false;
                status.textContent = `Failed to fill box ${i + 1}: ${e.message}`;
                status.style.color = '#c00';
                break;
            }
        }
        if (ok) {
            status.textContent = `${rows.length} SKUs filled`;
            status.style.color = '#2a7';
            setTimeout(() => { panel.style.display = 'none'; }, 3000);
        }
        return;
    }

    // ── Mismatch → show picker ────────────────────────────────────────────────
    status.textContent = `SKU mismatch: ${rows.length} DB rows, ${boxCount} collection box${boxCount !== 1 ? 'es' : ''}`;
    status.style.color = '#b60';
    renderInventoryPicker(rows, boxCount);
}

• Step 2: Call inventorySkuLookup at the end of fetchAndDisplayReleaseData

Find this line in popup.js (around line 1875):

        monsterBackgroundSync(releaseId);

Add the call immediately after it:

        inventorySkuLookup(releaseId);

• Step 3: Manual test — auto-trigger fires

1. Enable the inventory lookup toggle in Settings → Connect and save.
2. Open a Discogs release page and click the extension icon.
3. The popup should briefly show "Looking up inventory SKUs…" in the Discogs tab.
4. If the daemon isn't running: shows "Inventory lookup failed: fetch failed" — expected.
5. If the daemon is running with valid creds: auto-fills or shows mismatch depending on data.

• Step 4: Commit

cd /Users/jingk/Documents/pliceclogs-ultra-yt-omega-999999-truth
git add popup.js
git commit -m "feat: auto-trigger inventory SKU lookup on popup load"

---

Task 6: Mismatch picker rendering and interaction

Files:

• Modify: popup.js

• Step 1: Add the renderInventoryPicker function

Add this function immediately after inventorySkuLookup in popup.js:

function renderInventoryPicker(rows, boxCount) {
    const picker       = document.getElementById('inventory-sku-picker');
    const cardsEl      = document.getElementById('inventory-sku-cards');
    const boxButtonsEl = document.getElementById('inventory-sku-box-buttons');

    if (!picker || !cardsEl || !boxButtonsEl) return;

    let selectedSku = null;

    // ── Render row cards ──────────────────────────────────────────────────────
    cardsEl.innerHTML = '';
    rows.forEach((row) => {
        const card = document.createElement('div');
        card.style.cssText = [
            'padding:5px 8px',
            'border:1px solid #ccc',
            'border-radius:4px',
            'font-size:11px',
            'font-family:monospace',
            'cursor:pointer',
            'background:#fff',
            'display:flex',
            'gap:8px',
            'flex-wrap:wrap'
        ].join(';');

        const price = row.price ? `$${row.price}` : '—';
        const media = row.media_condition  || '—';
        const sleeve = row.sleeve_condition || '—';
        card.textContent = `${row.sku}  ·  ${price}  ·  ${media}  ·  ${sleeve}`;
        card.dataset.sku  = row.sku;

        card.addEventListener('click', () => {
            // Deselect all
            cardsEl.querySelectorAll('div').forEach(c => {
                c.style.borderColor  = '#ccc';
                c.style.background   = '#fff';
            });
            // Select this card
            card.style.borderColor = '#2a7';
            card.style.background  = '#f0faf4';
            selectedSku = row.sku;
            // Enable box buttons
            boxButtonsEl.querySelectorAll('button').forEach(b => {
                b.disabled = false;
                b.style.opacity = '1';
            });
        });

        cardsEl.appendChild(card);
    });

    // ── Render box number buttons ─────────────────────────────────────────────
    boxButtonsEl.innerHTML = '';
    for (let i = 0; i < boxCount; i++) {
        const btn = document.createElement('button');
        btn.textContent  = String(i + 1);
        btn.disabled     = true;          // enabled only when a card is selected
        btn.style.cssText = [
            'padding:3px 10px',
            'font-size:12px',
            'border-radius:4px',
            'border:1px solid #aaa',
            'cursor:pointer',
            'opacity:0.4'
        ].join(';');
        btn.dataset.boxIndex = String(i);

        btn.addEventListener('click', async () => {
            if (!selectedSku) return;
            const boxIndex = parseInt(btn.dataset.boxIndex, 10);

            try {
                await chrome.tabs.sendMessage(state.discogsTabId, {
                    action: 'updateDiscogsSku',
                    sku: selectedSku,
                    boxIndex
                });
                // Visual confirmation on the card
                const writtenCard = cardsEl.querySelector(`div[data-sku="${CSS.escape(selectedSku)}"]`);
                if (writtenCard) {
                    writtenCard.style.borderColor = '#2a7';
                    writtenCard.style.background  = '#e8f8ef';
                    writtenCard.textContent       = '✓ ' + writtenCard.textContent.replace(/^✓ /, '');
                }
                // Mark box button as done
                btn.textContent      = `✓${i + 1}`;
                btn.style.background = '#e8f8ef';
                btn.style.borderColor = '#2a7';

                // Reset selection so user must pick next card deliberately
                selectedSku = null;
                cardsEl.querySelectorAll('div').forEach(c => {
                    c.style.borderColor = '#ccc';
                    c.style.background  = '#fff';
                });
                boxButtonsEl.querySelectorAll('button').forEach(b => {
                    b.disabled     = true;
                    b.style.opacity = '0.4';
                });
            } catch (e) {
                document.getElementById('inventory-sku-status').textContent = `Write failed: ${e.message}`;
                document.getElementById('inventory-sku-status').style.color = '#c00';
            }
        });

        boxButtonsEl.appendChild(btn);
    }

    picker.style.display = 'block';
}

• Step 2: Manual test — mismatch picker

To test the picker without needing a real mismatch from the DB, temporarily add this call at the bottom of inventorySkuLookup, right before the renderInventoryPicker call, to force the mismatch branch:

Trigger the mismatch branch by temporarily setting a release that has a different number of DB rows than collection boxes, or by editing the condition rows.length === boxCount to always be false for testing. Verify:

1. The mismatch message appears: e.g. SKU mismatch: 2 DB rows, 1 collection box
2. Row cards are shown with SKU, price, media condition, sleeve condition
3. Box number buttons are grayed out initially
4. Clicking a card highlights it green and enables box buttons
5. Clicking a box number button writes the SKU (check Discogs page), marks the card with ✓, marks the button with ✓N
6. Buttons gray out again — user must select next card before writing to another box
7. Remove any temporary test overrides after confirming behaviour

• Step 3: End-to-end test with real data

1. Ensure rfid-daemon is running (node rfid-daemon/index.js -q)

2. Ensure inventory settings are saved with valid DB creds

3. Open a Discogs release page that is in your collection

4. Click the extension icon

   Case A — matching counts: SKUs should fill silently, "N SKUs filled" appears briefly then hides.

   Case B — 1 row: SKU fills into the last collection box silently.

   Case C — mismatch: Picker appears. Assign manually.

   Case D — 0 rows: Nothing shown (panel stays hidden).

• Step 4: Commit

cd /Users/jingk/Documents/pliceclogs-ultra-yt-omega-999999-truth
git add popup.js
git commit -m "feat: inventory SKU mismatch picker with card select and box-number assignment"