Add Micron asset picker to work package creator

Adds an optional read-only Micron asset catalog lookup for the WP creator, with searchable asset IDs, CSV import, and graceful fallback to manual asset entry when the catalog is absent or unreachable. This includes the backend /api/assets endpoint, SQL Server connector configuration, Docker network changes for outbound access, and UI updates/documentation to make the catalog read-only and clearly distinguish Micron-vetted assets from manual entries.

Azure-File-Picker-Request.md

@@ -1,60 +0,0 @@
-# Request: Entra (Azure AD) App Registration — WP Suite SharePoint File Picker
-
-**Requested by:** Nick Siegfried (n.siegfried@prime-controls.com)
-**Date:** 2026-06-15
-**Purpose:** Let users of the Work Package (WP) Suite pick files directly from the
-project's SharePoint folders (Drawings / Specs / Data Sheets) and attach them to a
-work package, instead of manually copying and pasting SharePoint links.
-
-To do this the tool needs to call Microsoft's **OneDrive/SharePoint File Picker
-(File Picker v8)** and **Microsoft Graph** on behalf of the signed-in Prime user.
-That requires an Entra app registration. None of this grants the app standalone
-access — every action runs **as the signed-in user**, so it can only see what that
-user already has permission to in SharePoint.
-
----
-
-## What we need created
-
-A single **App registration** in the `prime-controls.com` (primecontrolsdallas) tenant.
-
-| Setting | Value |
-|---|---|
-| **Name** | `WP-Suite-File-Picker` |
-| **Supported account types** | Single tenant — *Accounts in this organizational directory only* |
-| **Platform** | **Single-page application (SPA)** |
-| **Redirect URI(s)** | The URL(s) the tool is hosted at — e.g. `https://<wp-suite-host>/wp-creation-index.html` (and `http://localhost:<port>/...` for testing). *Nick to confirm final host.* |
-
-### API permissions (Microsoft Graph — **Delegated**, not Application)
-| Permission | Why |
-|---|---|
-| `Files.Read.All` | Read files the user selects in the picker |
-| `Sites.Read.All` | Resolve the SharePoint site/folder the picker browses |
-| `User.Read` | Basic sign-in (usually present by default) |
-
-> Delegated = acts as the signed-in user. We are **not** requesting application
-> (app-only) permissions, so the app cannot read anything on its own.
-
-### Admin consent
-- Please **grant admin consent** for the delegated permissions above so pilot users
-  aren't each prompted to consent individually.
-
----
-
-## What we DON'T need
-- No client secret / certificate (SPA uses PKCE, no secret).
-- No application (app-only) permissions.
-- No write/modify permissions — read-only is sufficient for attaching files.
-
-## What to send back to Nick
-1. **Application (client) ID**
-2. **Directory (tenant) ID**
-3. Confirmation that the **redirect URI** was registered and **admin consent** granted.
-
----
-
-### Background / scope
-This is for an internal pilot of the WP Suite (Project SDE). Today users paste
-SharePoint "Copy Link" URLs by hand. The app registration enables a proper
-"Add files" picker scoped to the SOP's folders. Read-only, single-tenant,
-delegated — lowest-privilege configuration that makes the picker work.

docker-compose.yml

@@ -35,12 +35,22 @@ services:
       # default and enabled from the Admin console; this is the only email
       # secret and it is never stored in the DB. Leave unset until configured.
       SMTP_PASSWORD: ${SMTP_PASSWORD:-}
+      # Optional — read-only SQL Server connection to the Micron asset catalog,
+      # which backs the asset picker in the work package creator. Leave unset and
+      # the picker cleanly falls back to manual entry (see server/assets_db.py).
+      # Use a db_datareader login: the app only ever SELECTs.
+      MICRON_DB_URL: ${MICRON_DB_URL:-}
     restart: unless-stopped
     depends_on:
       db:
         condition: service_healthy        # waits for postgres to accept connections
     networks:
       - internal
+      # Reaching the Micron database means leaving this compose project, and
+      # `internal` is deliberately egress-free. `outbound` is attached to the api
+      # container ONLY — the database and backup containers stay sealed. Detach it
+      # again if you are not using the Micron asset picker.
+      - outbound
 
   db:
     image: postgres:16-alpine
@@ -98,4 +108,12 @@ networks:
     name: proxy
     external: true
   internal:
-    internal: true    # no outbound internet access from api/db
+    internal: true    # no route off the host for anything on this network alone
+  outbound:
+    # An ordinary bridge network, i.e. one that HAS a default gateway. `internal`
+    # above removes the gateway entirely, which blocks not just the internet but
+    # the LAN and the VPN too — so the api container needs this second network to
+    # reach the Micron asset database. Attached to `api` alone: `db` and `backup`
+    # remain on `internal` only and still have no way off the host.
+    # Detach it from api if you are not using the Micron asset picker.
+    driver: bridge

html/help.js

@@ -141,7 +141,7 @@
       <h4>Key fields</h4>
       <ul>
         <li><strong>Subject / Title</strong> (required) and <strong>WP Type</strong> (required, from the SOP).</li>
-        <li><strong>Assets</strong> — link each controls.dev asset the package covers.</li>
+        <li><strong>Assets</strong> — search the Micron DB by asset ID and add each asset the package covers. Anything not in the Micron DB can still be typed in by hand.</li>
         <li><strong>Disciplines</strong> — which trades the package covers (see <a data-help-jump="disciplines">Disciplines &amp; Split</a>).</li>
         <li><strong>Scope &amp; Work</strong> — the sequenced steps the crew performs (per-discipline in multi-discipline mode).</li>
         <li><strong>Labor – Est. Hrs.</strong> — drives the sizing check (see <a data-help-jump="sizing">Sizing</a>).</li>
@@ -281,7 +281,7 @@
         <tr><td><strong>Sequence</strong></td><td>SOP-defined construction phases; a WP can name a predecessor step.</td></tr>
         <tr><td><strong>Bagged &amp; tagged</strong></td><td>Materials on site, kitted, and labelled — part of the Materials constraint.</td></tr>
         <tr><td><strong>MIMO</strong></td><td>Material In / Material Out — kitting and staging logistics.</td></tr>
-        <tr><td><strong>Asset</strong></td><td>A controls.dev record (equipment/system) a package is built around.</td></tr>
+        <tr><td><strong>Asset</strong></td><td>An asset ID from the Micron DB that a package is built around. The Micron DB is read-only here — picking an asset never changes it.</td></tr>
         <tr><td><strong>Hold / Witness point</strong></td><td>Hold = work stops until inspection sign-off; Witness = inspection offered but work may proceed.</td></tr>
         <tr><td><strong>Active project</strong></td><td>The currently selected project; all data is scoped to it.</td></tr>
       </table>` },

html/wp-creation-app.js

@@ -105,7 +105,7 @@ function applySOP(){
   applyKind();
   if(!pkgMaterials.length){ pkgMaterials=[{qty:'',unit:'',desc:''}]; buildMaterials(); }
   if(!pkgAttach.length){ pkgAttach=[{doc:'',rev:'',link:''}]; buildAttach(); }
-  if(!pkgAssets.length){ pkgAssets=[{tag:'',desc:'',link:''}]; buildAssets(); }
+  if(!pkgAssets.length){ buildAssets(); }   // renders the "no assets yet" empty state
   if(!pkgWorkSteps.length){ pkgWorkSteps=['']; buildWorkSteps(); }
   updateNumber(); updateReleaseBanner();
 }
@@ -134,7 +134,7 @@ function applyKindVisibility(){
   const show = (id, on) => { const el = document.getElementById(id); if(el) el.style.display = on ? '' : 'none'; };
   show('kind-row', bimProj);
   show('bim-card', ewp);                 // model area / clash + IFF # / scan
-  show('asset-card', !ewp);              // controls.dev assets
+  show('asset-card', !ewp);              // Micron DB assets
   show('material-card', !ewp);           // bill of materials
   show('mimo-card', !ewp);               // kitting / MIMO
   show('bimlink-wrap', bimProj && !ewp); // an IWP references the BIM package that enabled it
@@ -807,20 +807,344 @@ function splitByDiscipline(){
   alert('Created '+children.length+' instances:\n\n• '+children.map(c=>c.number+'  ('+c.disciplines[0]+', '+(c.materials?c.materials.length:0)+' material line'+((c.materials&&c.materials.length===1)?'':'s')+')').join('\n• ')+'\n\nThe master '+baseNumber+' is kept as a roll-up.'+matNote);
 }
 
-// ── ASSETS (controls.dev) ────────────────────────────────────────────────────
+// ── ASSETS (Micron asset catalog) ────────────────────────────────────────────
-// Interim: assets are linked manually back to controls.dev. A future direct
+// Assets are picked from the Micron asset catalog, a SQL Server database this app
-// integration will let the user pick them from a list instead of pasting links.
+// reads through /api/assets. The lookup is strictly read-only — picking an asset
+// never writes to the catalog, and there is no endpoint that could.
+//
+// The catalog can be absent (not configured) or unreachable (VPN/host down), and
+// neither may block someone from writing a work package: in both cases the picker
+// says so and manual entry carries on. Manually entered assets are marked
+// source:'manual' so it stays visible which rows the catalog vouches for.
+// The whole catalog is fetched once when the page loads and searched in memory —
+// it is slow-moving reference data, so a request per keystroke would buy nothing
+// and cost latency on every one.
+let assetCatalog = [];        // the full catalog, loaded once
+let assetCatalogIndex = new Map();   // lowercased id -> the Micron DB's own casing
+let assetCatalogState = 'loading';   // loading | ready | absent | error
+let assetResults = [];        // current matches; the result list indexes into this
+const ASSET_RESULT_MAX = 500;  // results shown at once — the box scrolls, not the search
+const ASSET_IMPORT_MAX = 1000;   // rows accepted from one CSV — see importAssets()
+
+function assetKey(a){ return String((a && a.tag) || '').trim().toLowerCase(); }
+function assetAlreadyAdded(tag){
+  const k = String(tag||'').trim().toLowerCase();
+  return pkgAssets.some(a => assetKey(a) === k && k);
+}
+
 function buildAssets(){
   const tb=document.getElementById('asset-body'); if(!tb) return; tb.innerHTML='';
-  pkgAssets.forEach((a,i)=>{ const tr=document.createElement('tr');
+  if(!pkgAssets.length){
-    tr.innerHTML=`<td><input type="text" value="${(a.tag||'').replace(/"/g,'&quot;')}" placeholder="controls.dev asset tag / ID" oninput="pkgAssets[${i}].tag=this.value"></td>
+    tb.innerHTML = `<tr><td colspan="3" class="asset-empty">No assets yet — search the Micron DB above to add the assets this package covers.</td></tr>`;
-      <td><input type="text" value="${(a.desc||'').replace(/"/g,'&quot;')}" placeholder="what it is (optional)" oninput="pkgAssets[${i}].desc=this.value"></td>
+    return;
-      <td><input type="url" value="${(a.link||'').replace(/"/g,'&quot;')}" placeholder="https://controls.dev/..." oninput="pkgAssets[${i}].link=this.value"></td>
+  }
-      <td class="center"><button class="row-del" onclick="removeAsset(${i})">✕</button></td>`;
+  pkgAssets.forEach((a,i)=>{
-    tb.appendChild(tr); });
+    const tr=document.createElement('tr');
+    // The asset ID on a catalog row is shown as text, not an input: the catalog
+    // is the source of truth for it and a locally edited copy would silently
+    // disagree. The note is always the user's own, so it stays editable either
+    // way. Every interpolation below is esc()'d text content or a numeric index
+    // — never a raw string inside an inline handler, which is the bug pattern
+    // recorded in KNOWN-ISSUES.md §1.
+    const idCell = a.source === 'catalog'
+      ? `<td><span class="asset-tag">${esc(a.tag)}</span> <span class="asset-badge" title="From the Micron DB">Micron DB</span></td>`
+      : `<td><input type="text" value="${esc(a.tag)}" placeholder="asset ID" oninput="pkgAssets[${i}].tag=this.value"></td>`;
+    tr.innerHTML = idCell +
+      `<td><input type="text" value="${esc(a.desc)}" placeholder="what it is / why it's in scope" oninput="pkgAssets[${i}].desc=this.value"></td>
+       <td class="center"><button class="row-del" onclick="removeAsset(${i})" title="Remove">✕</button></td>`;
+    tb.appendChild(tr);
+  });
+}
+
+// Kept for saved packages written before the picker existed: their rows have no
+// `source`, so they would render as read-only catalog rows with no way to fix a
+// typo. Anything that didn't come from the catalog is treated as manual.
+function normaliseAsset(a){
+  const o = Object.assign({ tag:'', desc:'', link:'', source:'manual' }, a||{});
+  if(o.source !== 'catalog') o.source = 'manual';
+  return o;
+}
+
+function addManualAsset(){
+  pkgAssets.push(normaliseAsset({}));
+  buildAssets();
+  track('asset_added',{source:'manual'});
+}
+
+// ── CSV IMPORT ───────────────────────────────────────────────────────────────
+// Bulk-add a list of asset ids. Each imported id is checked against the loaded
+// Micron DB: a hit is added as a catalog row (badge, id locked, stored with the
+// DB's own casing); a miss is added as a manual row so it is visibly NOT vouched
+// for rather than silently dropped. Nothing is ever written back to Micron.
+const ASSET_ID_HEADERS = ['asset id','assetid','asset_id','asset','asset tag','assettag','tag','id'];
+
+function importAssets(ev){
+  const f = ev.target.files && ev.target.files[0];
+  if(!f){ return; }
+  const clear = () => { ev.target.value = ''; };
+  if(/\.xlsx?$/i.test(f.name)){
+    alert('Please save the workbook as CSV first (File → Save As → CSV), then load it here.');
+    clear(); return;
+  }
+  const r = new FileReader();
+  r.onload = () => {
+    let rows;
+    try { rows = parseCSV(r.result); }
+    catch(e){ alert('Could not parse that CSV.'); clear(); return; }
+    if(!rows.length){ alert('That file has no rows.'); clear(); return; }
+    applyImportedAssets(rows);
+    clear();
+  };
+  r.onerror = () => { alert('Could not read that file.'); clear(); };
+  r.readAsText(f);
+}
+
+// Which column holds the ids, and whether row 0 is a header.
+//   - a recognised header name wins outright;
+//   - otherwise pick the column with the most Micron DB hits, so an export with
+//     the ids in column D works without the user rearranging it;
+//   - failing both (nothing matches — e.g. the DB is offline), use column 0.
+function pickAssetColumn(rows){
+  const head = (rows[0] || []).map(c => String(c || '').trim().toLowerCase());
+  const named = head.findIndex(h => ASSET_ID_HEADERS.includes(h));
+  if(named >= 0) return { col: named, start: 1 };
+
+  const width = rows.slice(0, 200).reduce((w, r) => Math.max(w, r.length), 1);
+  let best = 0, bestHits = 0;
+  for(let c = 0; c < width; c++){
+    let hits = 0;
+    for(let i = 0; i < Math.min(rows.length, 200); i++){
+      const v = String((rows[i] || [])[c] || '').trim();
+      if(v && assetCatalogIndex.has(v.toLowerCase())) hits++;
+    }
+    if(hits > bestHits){ bestHits = hits; best = c; }
+  }
+  return { col: best, start: 0 };
+}
+
+function applyImportedAssets(rows){
+  const { col, start } = pickAssetColumn(rows);
+
+  // Collect, trimmed and de-duplicated within the file itself.
+  const seen = new Set(), ids = [];
+  for(let i = start; i < rows.length; i++){
+    const v = String((rows[i] || [])[col] || '').trim();
+    if(!v) continue;
+    const k = v.toLowerCase();
+    if(seen.has(k)) continue;
+    seen.add(k); ids.push(v);
+  }
+  if(!ids.length){ alert('No asset ids found in that file.'); return; }
+
+  // Cap the import rather than building a table with thousands of rows. Reported,
+  // never silent — a truncated import that looked complete would be worse.
+  const capped = ids.length > ASSET_IMPORT_MAX;
+  const take = capped ? ids.slice(0, ASSET_IMPORT_MAX) : ids;
+
+  let matched = 0, unmatched = 0, dupes = 0;
+  take.forEach(id => {
+    if(assetAlreadyAdded(id)){ dupes++; return; }
+    const canonical = assetCatalogIndex.get(id.toLowerCase());
+    if(canonical){
+      pkgAssets.push({ tag: canonical, desc: '', link: '', source: 'catalog' });
+      matched++;
+    } else {
+      pkgAssets.push({ tag: id, desc: '', link: '', source: 'manual' });
+      unmatched++;
+    }
+  });
+
+  buildAssets();
+  renderAssetResults();      // rows just added should now read "added"
+  track('asset_imported', { matched: matched, unmatched: unmatched });
+
+  // Every id matched, nothing skipped, nothing truncated: a toast is enough.
+  // Anything the user needs to act on — unmatched ids, a silent-looking
+  // truncation, an unchecked import — interrupts with the detail instead.
+  const offline = assetCatalogState !== 'ready';
+  if(matched && !unmatched && !dupes && !capped && !offline){
+    toast('Added ' + matched + ' asset' + (matched === 1 ? '' : 's') + ' from the Micron DB');
+    return;
+  }
+
+  const parts = [];
+  if(matched) parts.push(matched + ' found in the Micron DB');
+  if(unmatched) parts.push(unmatched + ' not in the Micron DB (added as manual rows)');
+  if(dupes) parts.push(dupes + ' already on this package (skipped)');
+  let msg = 'Imported ' + (matched + unmatched) + ' asset' + ((matched + unmatched) === 1 ? '' : 's') +
+            (parts.length ? ':\n\n• ' + parts.join('\n• ') : '');
+  if(capped) msg += '\n\nThe list held ' + ids.length.toLocaleString() + ' ids — only the first ' +
+                    ASSET_IMPORT_MAX.toLocaleString() + ' were added.';
+  if(offline) msg += '\n\nNote: the Micron DB was not loaded, so nothing could be ' +
+                    'checked against it — every row was added as manual.';
+  alert(msg);
+}
+
+function removeAsset(i){
+  pkgAssets.splice(i,1);
+  buildAssets();
+  renderAssetResults();     // a removed asset becomes addable again
+}
+
+// ── Catalog lookup ───────────────────────────────────────────────────────────
+function assetSourceNote(msg, tone){
+  const el = document.getElementById('asset-source-note'); if(!el) return;
+  el.textContent = msg || '';
+  el.style.color = tone === 'warn' ? 'var(--red)' : '';
+}
+
+function initAssetPicker(){
+  const box = document.getElementById('asset-search'); if(!box) return;
+  box.addEventListener('input', () => runAssetSearch(box.value));
+  // Re-open on focus only when the list is actually closed. Adding an asset
+  // returns focus to this box, and re-running the search there would rebuild the
+  // list under the cursor and throw away the scroll position mid-multi-add.
+  box.addEventListener('focus', () => {
+    const results = document.getElementById('asset-results');
+    if(results && results.hidden && box.value.trim()) runAssetSearch(box.value);
+  });
+  // Pasting a column of ids straight out of Excel adds them all, rather than
+  // dropping a multi-line blob into a search box that can only match one thing.
+  // Excel gives \r\n between rows and \t between columns — i.e. exactly the CSV
+  // importer's row/cell shape, so it goes through the same matching path.
+  // A single value is left alone: that is an ordinary search, not a bulk add.
+  box.addEventListener('paste', e => {
+    const cb = e.clipboardData || window.clipboardData;
+    const text = cb ? cb.getData('text') : '';
+    if(!text) return;
+    const lines = text.replace(/\r\n?/g, '\n').split('\n').filter(l => l.trim());
+    if(lines.length < 2) return;              // one id — paste it and search as normal
+    e.preventDefault();
+    applyImportedAssets(lines.map(l => l.split('\t')));
+    box.value = '';
+    assetResults = [];
+    openAssetResults(false);
+  });
+  box.addEventListener('keydown', e => {
+    if(e.key === 'Escape'){ openAssetResults(false); box.blur(); }
+    // Enter adds the first result that isn't already on the package — the common
+    // case of typing an exact tag and taking it without reaching for the mouse.
+    if(e.key === 'Enter'){
+      e.preventDefault();
+      const ix = assetResults.findIndex(tag => !assetAlreadyAdded(tag));
+      if(ix >= 0) addCatalogAsset(ix);
+    }
+  });
+  // Click-away closes, matching the .pp-menu pickers elsewhere on this form.
+  // Tested against composedPath() rather than e.target: adding an asset can
+  // re-render the row that was clicked, and a detached target reports itself as
+  // outside every container, which would close the list on every add.
+  document.addEventListener('click', e => {
+    const path = typeof e.composedPath === 'function' ? e.composedPath() : null;
+    const inside = path && path.length
+      ? path.some(n => n && n.id === 'asset-pick')
+      : !!(e.target.closest && e.target.closest('#asset-pick'));
+    if(!inside) openAssetResults(false);
+  });
+  // Delegated so result rows never need an inline handler carrying catalog text.
+  const results = document.getElementById('asset-results');
+  if(results) results.addEventListener('click', e => {
+    const row = e.target.closest('[data-asset-ix]'); if(!row) return;
+    addCatalogAsset(parseInt(row.getAttribute('data-asset-ix'), 10));
+  });
+
+  box.disabled = true;
+  box.placeholder = 'Loading asset IDs from the Micron DB…';
+  assetSourceNote('Loading asset IDs from the Micron DB…');
+
+  fetch('/api/assets', { headers:{ 'Accept':'application/json' } })
+    .then(r => r.ok ? r.json()
+                    : r.json().catch(() => ({})).then(b => Promise.reject(b.detail || 'The Micron DB could not be read.')))
+    .then(body => {
+      if(!body.configured){
+        assetCatalogState = 'absent';
+        box.placeholder = 'Micron DB not configured — add assets manually below';
+        assetSourceNote('The Micron DB is not connected, so assets are entered by hand. Use “+ Add asset not in the Micron DB”.');
+        return;
+      }
+      assetCatalog = (body.assets || []).map(a => String(a.tag || ''));
+      // Lowercased lookup for the CSV importer: it decides whether an imported id
+      // is a real Micron asset, and maps it back to the DB's own casing so an
+      // id typed as "ahu-2p-014" is stored exactly as Micron spells it.
+      assetCatalogIndex = new Map(assetCatalog.map(t => [t.toLowerCase(), t]));
+      assetCatalogState = 'ready';
+      box.disabled = false;
+      box.placeholder = 'Search asset IDs, or paste a column from Excel…';
+      assetSourceNote(assetCatalog.length.toLocaleString() + ' asset IDs loaded from the Micron DB (read-only).');
+    })
+    .catch(err => {
+      assetCatalogState = 'error';
+      box.placeholder = 'Micron DB unavailable — add assets manually below';
+      assetSourceNote(typeof err === 'string' ? err + ' You can still add assets manually.'
+                                              : 'The Micron DB could not be reached. You can still add assets manually.', 'warn');
+    });
+}
+
+// Filters the loaded catalog in memory. Exact match, then prefix, then contains —
+// so typing a full asset ID puts that asset first rather than whichever ID
+// happens to sort first.
+function runAssetSearch(q){
+  q = String(q||'').trim().toLowerCase();
+  if(!q || assetCatalogState !== 'ready'){
+    assetResults = []; renderAssetResults(); openAssetResults(false); return;
+  }
+  const exact=[], prefix=[], other=[];
+  for(const tag of assetCatalog){
+    const t = tag.toLowerCase();
+    if(t === q) exact.push(tag);
+    else if(t.startsWith(q)) prefix.push(tag);
+    else if(t.includes(q)) other.push(tag);
+    if(exact.length + prefix.length + other.length >= ASSET_RESULT_MAX) break;
+  }
+  assetResults = exact.concat(prefix, other).slice(0, ASSET_RESULT_MAX);
+  renderAssetResults();
+  openAssetResults(true);
+}
+
+function renderAssetResults(){
+  const box = document.getElementById('asset-results'); if(!box) return;
+  if(!assetResults.length){
+    box.innerHTML = `<div class="asset-result-note">No matching asset IDs. Add it manually if it isn’t in the Micron DB yet.</div>`;
+    return;
+  }
+  box.innerHTML = assetResults.map((tag,ix) => {
+    const on = assetAlreadyAdded(tag);
+    return `<button type="button" class="asset-result${on?' is-added':''}" ${on?'disabled':''} data-asset-ix="${ix}">
+        <span class="asset-result-tag">${esc(tag)}</span>
+        <span class="asset-result-add">${on ? 'added' : '+ add'}</span>
+      </button>`;
+  }).join('');
+}
+
+function openAssetResults(open){
+  const box = document.getElementById('asset-results');
+  const inp = document.getElementById('asset-search');
+  if(box) box.hidden = !open;
+  if(inp) inp.setAttribute('aria-expanded', open ? 'true' : 'false');
+}
+
+function addCatalogAsset(ix){
+  const tag = assetResults[ix]; if(!tag) return;
+  if(assetAlreadyAdded(tag)){ toast('That asset is already on this package'); return; }
+  pkgAssets.push({ tag: tag, desc: '', link: '', source: 'catalog' });
+  buildAssets();
+  // Mark just this row instead of re-rendering the list: the results stay open
+  // for the next pick, the scroll position holds, and the clicked element is
+  // never detached mid-click (see the composedPath note in initAssetPicker).
+  markAssetResultAdded(ix);
+  const box = document.getElementById('asset-search');
+  if(box) box.focus();        // keep typing straight into the next search
+  track('asset_added',{source:'catalog'});
+}
+
+function markAssetResultAdded(ix){
+  const row = document.querySelector('#asset-results [data-asset-ix="' + ix + '"]');
+  if(!row) return;
+  row.classList.add('is-added');
+  row.disabled = true;
+  const label = row.querySelector('.asset-result-add');
+  if(label) label.textContent = 'added';
 }
-function addAsset(){ pkgAssets.push({tag:'',desc:'',link:''}); buildAssets(); track('asset_added'); }
-function removeAsset(i){ pkgAssets.splice(i,1); if(!pkgAssets.length)pkgAssets=[{tag:'',desc:'',link:''}]; buildAssets(); }
 
 // ── ATTACHMENTS ──────────────────────────────────────────────────────────────
 function buildAttach(){
@@ -1189,8 +1513,8 @@ function renderPackage(pkg){
     ${pkg.bimlink?`<tr><th>Enabled by (BIM)</th><td>${/^https?:\/\//i.test(pkg.bimlink)?linkify(pkg.bimlink):cell(pkg.bimlink)}</td></tr>`:''}
     ${(pkg.lod||pkg.iff||pkg.modelArea||pkg.clash||pkg.scanLink)?`<tr><th>BIM / Model</th><td>${[pkg.modelArea?'Area: '+esc(pkg.modelArea):'', pkg.clash?'Coordination: '+esc(pkg.clash):'', pkg.iff?'IFF #: '+esc(pkg.iff):'', pkg.scanLink?'Scan: '+linkify(pkg.scanLink):'', pkg.lod?'LOD: '+esc(pkg.lod)+' (legacy)':''].filter(Boolean).join('<br>')}</td></tr>`:''}
   </tbody></table>`;
-  if(pkg.assets&&pkg.assets.length){ h+=`<h2>2.0 Assets (controls.dev)</h2><table><thead><tr><th style="width:180px">Asset Tag / ID</th><th>Description</th><th>controls.dev Link</th></tr></thead><tbody>`;
+  if(pkg.assets&&pkg.assets.length){ h+=`<h2>2.0 Assets</h2><table><thead><tr><th style="width:240px">Asset ID</th><th>Note</th></tr></thead><tbody>`;
-    pkg.assets.forEach(a=>h+=`<tr><td>${cell(a.tag)}</td><td>${cell(a.desc)}</td><td>${a.link?linkify(a.link):ns()}</td></tr>`); h+=`</tbody></table>`; }
+    pkg.assets.forEach(a=>h+=`<tr><td>${cell(a.tag)}</td><td>${cell(a.desc)}</td></tr>`); h+=`</tbody></table>`; }
   let scopeHtml;
   if(pkg.scope && Object.keys(pkg.scope).length){   // per-discipline scope sections
     scopeHtml = Object.keys(pkg.scope).map(d=>{
@@ -1688,7 +2012,7 @@ function loadPackageIntoForm(p){
   set('wp_hold', (p.hold&&p.hold.trim())?p.hold:sopValueFor('wp_hold'));
   lockQuality('wp_qc'); lockQuality('wp_photo'); lockQuality('wp_hold');
   // collections
-  pkgAssets=(p.assets&&p.assets.length)?p.assets.map(a=>({...a})):[{tag:'',desc:'',link:''}]; buildAssets();
+  pkgAssets=(p.assets||[]).map(normaliseAsset); buildAssets();
   pkgMaterials=(p.materials&&p.materials.length)?p.materials.map(m=>({...m,unit:(m.unit||'').toUpperCase()})):[{qty:'',unit:'',desc:''}]; buildMaterials();
   pkgAttach=(p.attachments&&p.attachments.length)?p.attachments.map(a=>({...a})):[{doc:'',rev:'',link:''}]; buildAttach();
   pkgWorkSteps=(p.workSteps&&p.workSteps.length)?p.workSteps.slice():(p.work?String(p.work).split('\n').filter(Boolean):['']); if(!pkgWorkSteps.length)pkgWorkSteps=['']; buildWorkSteps();
@@ -1745,7 +2069,7 @@ function newPackage(){
   setRadio('status','Draft');
   numberDims={}; buildNumberDims();
   pkgDisciplines=[]; pkgScope={}; pkgDiscStatus={}; buildDisciplinePicker(); renderScope(); onHoursChange();
-  pkgAssets=[{tag:'',desc:'',link:''}]; buildAssets();
+  pkgAssets=[]; buildAssets();
   pkgMaterials=[{qty:'',unit:'',desc:''}]; buildMaterials();
   pkgAttach=[{doc:'',rev:'',link:''}]; buildAttach();
   pkgWorkSteps=['']; buildWorkSteps();
@@ -2111,6 +2435,7 @@ function bootData(){
   bootSOP();
   setRadio('status','Draft');
   loadMembers();
+  initAssetPicker();
   initWpNavDrawer();
   renderSavedList();
   positionSectionNav();

html/wp-creation-index.html

@@ -181,12 +181,23 @@
     </div>
   </div>
 
-  <!-- ASSETS (controls.dev) -->
+  <!-- ASSETS (Micron asset catalog) -->
   <div class="card" id="asset-card">
-    <div class="sub-heading">Assets</div>
+    <div class="sub-heading">Assets<span class="help-tip" data-tip="Every work package is built around one or more assets. Search the Micron DB by asset ID, paste a column of IDs straight from Excel, or load a CSV. IDs found in the Micron DB are tagged as such; the rest are added as manual rows. The Micron DB is read-only here — picking an asset never changes it.">i</span></div>
-    <div class="notice">Every work package is based on one or more assets managed in <strong>controls.dev</strong>. Paste the controls.dev link for each asset this package covers. <span style="color:var(--text-dim)">A direct integration to pick assets from a list is planned — for now, link them manually.</span></div>
+    <div class="notice">Every work package is based on one or more assets from the <strong>Micron DB</strong>. Search by asset ID, or paste a column of IDs straight from Excel, to add each asset this package covers. <span style="color:var(--text-dim)">The Micron DB is read-only — nothing you do here changes it.</span></div>
-    <div class="table-wrap"><table><thead><tr><th style="width:200px">Asset Tag / ID</th><th>Description</th><th>controls.dev Link <span class="req">*</span></th><th style="width:44px"></th></tr></thead><tbody id="asset-body"></tbody></table></div>
+    <div class="asset-pick" id="asset-pick">
-    <button class="add-btn" onclick="addAsset()">+ Add Asset</button>
+      <input type="search" class="asset-search" id="asset-search" autocomplete="off"
+             placeholder="Search asset IDs, or paste a column from Excel…"
+             aria-label="Search the Micron DB by asset ID" aria-controls="asset-results" aria-expanded="false">
+      <div class="asset-results" id="asset-results" hidden></div>
+    </div>
+    <div class="field-hint" id="asset-source-note"></div>
+    <div class="table-wrap"><table><thead><tr><th style="width:260px">Asset ID</th><th>Note <span style="font-weight:400;color:var(--text-dim)">(what this asset is / why it's in scope)</span></th><th style="width:44px"></th></tr></thead><tbody id="asset-body"></tbody></table></div>
+    <div class="material-actions">
+      <button class="add-btn" onclick="addManualAsset()" title="Add an asset that is not in the Micron DB yet">+ Add asset not in the Micron DB</button>
+      <button class="add-btn" onclick="document.getElementById('asset-import').click()" title="Load a list of asset IDs from a CSV. IDs found in the Micron DB are tagged as such; the rest are added as manual rows.">⤒ Load from CSV</button>
+      <input type="file" id="asset-import" accept=".csv,text/csv" style="display:none" onchange="importAssets(event)">
+    </div>
   </div>
 
   <!-- DISCIPLINES -->

html/wp-creation-styles.css

@@ -645,6 +645,38 @@
     border:1px solid var(--border); border-radius:3px; }
   .pp-free .field-hint { margin-top:4px; }
 
+  /* ── asset picker (Micron asset catalog) ────────────────────────────────────
+     A search box over a read-only catalog. Results drop below the input and are
+     added to the table as rows; the catalog itself is never written to. */
+  .asset-pick { position:relative; margin-bottom:10px; }
+  .asset-search { width:100%; padding:8px 10px; font:inherit; font-size:13px;
+    border:1px solid var(--border-strong); border-radius:4px; background:var(--surface);
+    box-sizing:border-box; }
+  .asset-search:focus { outline:2px solid var(--accent); outline-offset:-2px; }
+  .asset-search:disabled { background:var(--surface2); color:var(--text-dim); cursor:not-allowed; }
+  .asset-results { position:absolute; top:calc(100% + 4px); left:0; right:0; z-index:60;
+    max-height:320px; overflow-y:auto; background:var(--surface);
+    border:1px solid var(--border-strong); border-radius:4px; padding:4px 0;
+    box-shadow:0 8px 24px rgba(20,30,50,.18); }
+  .asset-results[hidden] { display:none; }
+  .asset-result { display:flex; align-items:baseline; justify-content:space-between; gap:10px;
+    width:100%; text-align:left; background:none; border:0;
+    font:inherit; font-size:13px; padding:7px 12px; cursor:pointer; color:var(--text); }
+  .asset-result:hover:not(:disabled) { background:var(--surface2); }
+  .asset-result:disabled { cursor:default; opacity:.55; }
+  .asset-result-tag { font-weight:600; overflow:hidden; text-overflow:ellipsis; white-space:nowrap; }
+  .asset-result-add { color:var(--accent); font-size:11.5px; font-weight:700; white-space:nowrap; }
+  .asset-result.is-added .asset-result-add { color:var(--text-dim); font-weight:400; }
+  .asset-result-note { padding:9px 12px; font-size:12.5px; color:var(--text-muted); }
+  /* Marks rows the catalog vouches for, so a manually typed asset is never
+     mistaken for a looked-up one. */
+  .asset-badge { display:inline-block; margin-left:6px; padding:1px 7px; border-radius:10px;
+    font-size:10px; font-weight:700; letter-spacing:.02em; text-transform:uppercase;
+    color:var(--accent); background:var(--accent-dim); vertical-align:middle;
+    white-space:nowrap; }   /* two words now — must not wrap under the asset ID */
+  .asset-tag { font-weight:600; }
+  .asset-empty { color:var(--text-dim); font-size:12.5px; font-style:italic; }
+
   /* Critical constraint marker (from the SOP) */
   .crit-tag { display:inline-block; margin-left:6px; padding:1px 7px; border-radius:10px; font-size:10px;
     font-weight:700; letter-spacing:.02em; color:var(--red); background:var(--red-dim);

server/.env.example

@@ -30,3 +30,25 @@ AUTH_SECRET_KEY=CHANGE_ME_run_the_command_above
 # notifications are marked "skipped", nothing is sent) until both the toggle is
 # on and SMTP is configured.
 # SMTP_PASSWORD=your-smtp-app-password
+
+# ── Micron asset catalog (optional) ───────────────────────────────────────────
+# Backs the searchable asset picker in the work package creator. READ-ONLY: the
+# app only ever runs the single SELECT in server/assets_db.py, so give it a
+# db_datareader login and nothing more.
+#
+# Leave this unset and the suite works normally — the picker reports that no
+# catalog is configured and people type asset tags in by hand.
+#
+# URL-encode special characters in the password (@ = %40, # = %23, / = %2F …).
+# MICRON_DB_URL=mssql+pymssql://readonly_user:PASSWORD@sqlhost.example.com:1433/MicronDB
+#
+# To use pyodbc instead of pymssql you must also add pyodbc to requirements.txt
+# and install the Microsoft ODBC driver in the image:
+# MICRON_DB_URL=mssql+pyodbc://readonly_user:PASSWORD@sqlhost.example.com/MicronDB?driver=ODBC+Driver+18+for+SQL+Server
+#
+# Two things to check when the picker says the catalog is unreachable:
+#   1. The table/column names in ASSET_QUERY (server/assets_db.py) match the real
+#      Micron schema — that one constant is the whole schema contract.
+#   2. The api container is on the `outbound` network in docker-compose.yml. The
+#      `internal` network has no default gateway, which blocks the VPN as well as
+#      the internet.

server/app.py

@@ -25,7 +25,7 @@ from sqlalchemy import select, delete, func
 from sqlalchemy.orm import Session
 
 from .db import Base, engine, get_db
-from . import models, auth, notify
+from . import models, auth, notify, assets_db
 
 # Schema management:
 #   • Local dev (SQLite) auto-creates tables for a zero-config run.
@@ -1810,6 +1810,29 @@ def list_comments(
     return [c.to_dict() for c in rows]
 
 
+# ── Micron asset catalog (read-only lookup) ────────────────────────────────────
+# Backs the asset picker in the work package creator. This is a *lookup*, not a
+# resource this app owns: there is no POST, and nothing here ever writes to the
+# Micron database. It is deliberately not project-scoped by the app's own access
+# rules — the catalog is reference data, and any signed-in user who can build a
+# work package needs to be able to name the assets it covers. Authentication is
+# still required (the auth_gate middleware covers every /api/ path).
+@app.get("/api/assets")
+def list_assets(_user: models.User = Depends(auth.get_current_user)):
+    """The whole catalog, fetched once when the creator loads. Searching happens
+    in the browser — there is no per-keystroke endpoint by design."""
+    if not assets_db.configured():
+        # Not an error — the suite is designed to run without Micron wired up.
+        # The picker reads this and switches to manual entry.
+        return {"configured": False, "assets": [], "detail": assets_db.status()["detail"]}
+    try:
+        return {"configured": True, "assets": assets_db.load()}
+    except assets_db.AssetSourceError as exc:
+        # 503, not 500: the suite is healthy, its upstream lookup is not. The
+        # picker degrades to manual entry rather than blocking the package.
+        raise HTTPException(status_code=503, detail=str(exc))
+
+
 # ── Local dev convenience: serve the static site from this app ──────────────────
 # In production NGINX serves html/ and only proxies /api/ here, so this app never
 # receives "/" requests, and the api Docker image doesn't even include html/ — so

server/assets_db.py

@@ -0,0 +1,199 @@
+"""Read-only reader for the Micron asset catalog.
+
+The work package creator used to ask people to paste a controls.dev link for
+every asset. Assets actually live in the Micron database — a SQL Server instance
+that is NOT part of this repo and whose schema is not managed here. This module
+gives the API a *read-only* window onto it so the creator can offer a searchable
+picker instead of free-text links.
+
+How it works: the whole catalog is fetched in one query and handed to the browser
+when the creator loads. Searching then happens in the browser with no round trip
+at all. The catalog is a list of asset IDs — about 9k of them today and not
+expected past 100k — so it is small enough to send whole, and it is slow-moving
+reference data, so there is nothing to gain from querying it per keystroke and a
+lot of latency to lose. A short server-side cache keeps a room full of people
+opening the page from turning into a query each.
+
+Other deliberate constraints:
+
+  * **Read-only, always.** The only statement in this file is the SELECT below.
+    Point it at a login with `db_datareader` and nothing else.
+  * **No prime_db dependency.** A plain SQLAlchemy connection built from a
+    connection string, kept separate from the app's own engine in `db.py`, so a
+    Micron outage can never affect the suite's own database.
+
+Unconfigured is a first-class state: with no `MICRON_DB_URL` set, `configured()`
+returns False, the API says so, and the UI falls back to manual entry. The suite
+boots and runs fine without the Micron database being reachable.
+"""
+import os
+import time
+import logging
+import threading
+
+from sqlalchemy import create_engine, text
+from sqlalchemy.exc import SQLAlchemyError
+
+log = logging.getLogger(__name__)
+
+try:
+    from dotenv import load_dotenv
+    load_dotenv()
+except Exception:
+    pass
+
+
+# ── The query ─────────────────────────────────────────────────────────────────
+# The only place the Micron schema appears; everything else here is plumbing.
+# Returns one row per asset, aliased `tag`. No row cap: the catalog is small
+# enough to hand over whole, and a partial list would silently hide assets.
+#
+# Add a WHERE clause here if some rows should never be offered at all
+# (decommissioned assets, other sites, …). Filtering at the source keeps the
+# payload small, which matters more than anything else here.
+ASSET_QUERY = """
+    SELECT a.AssetID AS tag
+      FROM Asset.Asset AS a
+     ORDER BY a.AssetID
+"""
+
+# How long a fetched catalog is reused before the next page load re-queries.
+CACHE_SECONDS = int(os.getenv("MICRON_ASSETS_CACHE_SECONDS", "300"))    # 5 min
+CONNECT_TIMEOUT = 8
+
+
+class AssetSourceError(RuntimeError):
+    """The catalog is configured but could not be read."""
+
+
+# ── Engine (lazy, process-wide) ───────────────────────────────────────────────
+# A full SQLAlchemy URL, e.g.
+#   mssql+pymssql://user:pass@host:1433/MicronDB
+#   mssql+pyodbc://user:pass@host/MicronDB?driver=ODBC+Driver+18+for+SQL+Server
+# URL-encode any special characters in the password.
+_engine = None
+_engine_lock = threading.Lock()
+
+
+def _db_url() -> str:
+    return os.getenv("MICRON_DB_URL", "").strip()
+
+
+def configured() -> bool:
+    return bool(_db_url())
+
+
+def _validate_url(url: str) -> None:
+    """Catch the one URL mistake that produces a baffling error message.
+
+    A password containing an unencoded '@' makes the URL ambiguous: the parser
+    splits on the first '@', so part of the password ends up parsed as the host.
+    The driver then reports a connection failure against a nonsense hostname that
+    happens to contain a fragment of the password — confusing to read and unsafe
+    to display. Detect it here and say plainly what is wrong.
+
+    Nothing from the URL is included in the message; it never leaves this process.
+    """
+    authority = url.split("://", 1)[-1].split("/", 1)[0]
+    if authority.count("@") > 1:
+        raise AssetSourceError(
+            "MICRON_DB_URL is ambiguous: the username or password contains an "
+            "unencoded '@'. Percent-encode the special characters — @ = %40, "
+            ": = %3A, / = %2F, # = %23, ? = %3F, %  = %25."
+        )
+
+
+def _connect_args(url: str) -> dict:
+    """Per-driver connect timeouts, so an unreachable Micron host fails fast
+    instead of tying up a worker until the OS gives up."""
+    if url.startswith("mssql+pymssql"):
+        return {"login_timeout": CONNECT_TIMEOUT, "timeout": CONNECT_TIMEOUT}
+    if url.startswith("mssql+pyodbc"):
+        return {"timeout": CONNECT_TIMEOUT}
+    return {}
+
+
+def _get_engine():
+    global _engine
+    if _engine is not None:
+        return _engine
+    url = _db_url()
+    if not url:
+        raise AssetSourceError("The Micron DB is not configured.")
+    _validate_url(url)
+    with _engine_lock:
+        if _engine is None:
+            try:
+                _engine = create_engine(
+                    url,
+                    connect_args=_connect_args(url),
+                    pool_pre_ping=True,   # a recycled dead connection retries instead of erroring
+                    pool_recycle=1800,
+                    pool_size=1,          # one catalog query now and then, not a workload
+                    max_overflow=1,
+                    future=True,
+                )
+            except Exception as exc:      # bad URL, missing driver package, …
+                # See the note on load() — the exception text can echo the
+                # connection string, so it is logged and not propagated.
+                log.error("Micron asset catalog: could not open the connection: %s", exc)
+                raise AssetSourceError(
+                    "Could not open a connection to the Micron DB. "
+                    "Check MICRON_DB_URL and the API log for the driver error."
+                ) from exc
+    return _engine
+
+
+# ── Cache ─────────────────────────────────────────────────────────────────────
+# Every page load asks for the whole catalog, so without this a shift change
+# would be one full-table query per person. Held per worker process.
+_cache: list[dict] | None = None
+_cached_at = 0.0
+_cache_lock = threading.Lock()
+
+
+def load(force: bool = False) -> list[dict]:
+    """Return the whole catalog as [{'tag': …}, …]. Never writes."""
+    global _cache, _cached_at
+    with _cache_lock:
+        if _cache is not None and not force and (time.monotonic() - _cached_at) < CACHE_SECONDS:
+            return _cache
+
+    engine = _get_engine()
+    try:
+        with engine.connect() as conn:
+            result = conn.execute(text(ASSET_QUERY)).mappings().all()
+    except SQLAlchemyError as exc:
+        # The driver's message is NOT propagated. AssetSourceError text reaches the
+        # browser, and connection errors quote the host, the login, and — when the
+        # URL is malformed — fragments of the password. Operators get the detail
+        # from the API log, where it belongs; users get a message they can act on.
+        log.error("Micron asset catalog query failed: %s", exc)
+        raise AssetSourceError(
+            "The Micron DB could not be read. Check that the host is "
+            "reachable, that the login has SELECT on the asset table, and that "
+            "ASSET_QUERY matches the real schema — the API log has the driver error."
+        ) from exc
+
+    # Drop rows with no identifier — an asset with no tag is not selectable and
+    # would render as a blank line in the picker.
+    rows = [{"tag": str(r["tag"])} for r in result if r.get("tag") not in (None, "")]
+    with _cache_lock:
+        _cache, _cached_at = rows, time.monotonic()
+    return rows
+
+
+def status() -> dict:
+    """Describe the source for the UI, so it can explain itself rather than just
+    showing an empty dropdown."""
+    if not configured():
+        return {
+            "configured": False, "ok": False, "count": 0,
+            "detail": "The Micron DB is not configured — enter assets manually.",
+        }
+    try:
+        rows = load()
+    except AssetSourceError as exc:
+        return {"configured": True, "ok": False, "count": 0, "detail": str(exc)}
+    return {"configured": True, "ok": True, "count": len(rows),
+            "detail": f"{len(rows):,} asset IDs from the Micron DB."}

server/requirements.txt

@@ -9,6 +9,12 @@ gunicorn==26.0.0
 sqlalchemy==2.0.51
 alembic==1.18.5             # database migrations
 psycopg[binary]==3.3.4
+pymssql==2.3.13             # read-only lookups against the Micron asset DB (SQL Server).
+                            # Chosen over pyodbc because it ships self-contained wheels —
+                            # pyodbc would also need msodbcsql18 + unixODBC installed in
+                            # the image. To use pyodbc instead, add it here, install the
+                            # Microsoft ODBC driver in the Dockerfile, and switch
+                            # MICRON_DB_URL to mssql+pyodbc://…?driver=ODBC+Driver+18+for+SQL+Server
 pydantic==2.13.4
 python-dotenv==1.2.2
 bcrypt==5.0.0               # password hashing