fix server in wp-suite.conf so proxy works.

With 2 workers, both ran Base.metadata.create_all() at import on an empty DB,
racing on CREATE TABLE (duplicate pg_type for "projects"). --preload imports
the app once in the master before forking, so tables are created a single time.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

DEPLOYMENT.md

@@ -51,16 +51,23 @@ Create a file named `.env` in the **project root** (same folder as
 POSTGRES_DB=wpsuite
 POSTGRES_USER=wpsuite
 POSTGRES_PASSWORD=<strong-random-password>
-
-# Must match the POSTGRES_* values above. Host is the compose service name "db".
-DATABASE_URL=postgresql+psycopg://wpsuite:<strong-random-password>@db:5432/wpsuite
 ```
 
+That's it — the API now builds its own connection string from these three
+values and **encodes the password automatically**, so a password with special
+characters (`@ ! # : /` …) works without any manual escaping. `DATABASE_URL`
+is **optional** and only needed if you want to point the API at some other
+database; if you do set it, you must URL-encode the password yourself, and it's
+ignored whenever the three `POSTGRES_*` values are present.
+
 Generate a strong password with `openssl rand -base64 32`.
 
-These are the only credentials in the system: the `db` container initialises
-Postgres from `POSTGRES_*`, and the `api` container connects with the matching
-`DATABASE_URL`. Neither value appears in the compose file or in git.
+> **Portainer note:** for a Git-based stack these go in the stack's
+> **Environment variables** section (Portainer doesn't read a local `.env`).
+> Set `POSTGRES_DB` / `POSTGRES_USER` / `POSTGRES_PASSWORD` there.
+
+These are the only credentials in the system, and they never appear in the
+compose file or in git.
 
 ## 3. Point your reverse proxy at the nginx container
 
@@ -106,6 +113,49 @@ create a project**. Create one, complete an SOP, and confirm a row appears:
 docker compose exec db psql -U wpsuite -d wpsuite -c "select id, name from projects;"
 ```
 
+### Automated smoke test
+
+`server/smoketest.py` exercises the whole stack end-to-end (health → project →
+SOP → Work Package → the AWP issue gate → status → metrics → comments → cascade
+cleanup). Stdlib only — no pip/jq.
+
+```bash
+# Through the proxy (use --insecure for a self-signed internal cert):
+python3 server/smoketest.py https://wp-suite.company.local --insecure
+
+# Or from inside the api container (hits FastAPI directly):
+docker compose exec api python /app/server/smoketest.py http://localhost:8000
+
+# Add --keep to leave a demo project in the DB so you can open it in the UI.
+```
+
+Exit code 0 and "ALL PASS" means the API, the Python logic, and SQL are all
+working. It cleans up after itself (the test project and its SOP/WPs are
+deleted via cascade); a single tagged test comment remains (there's no comment
+delete endpoint).
+
+### Loadable demo project
+
+`server/seed_demo.py` populates a realistic **DEMO** project (a complete SOP plus
+a spread of Work Packages: issued, gated, a multi-discipline master with split
+instances, an overdue one, an over-threshold draft) so there's data to look at.
+
+```bash
+python3 server/seed_demo.py https://wp-suite.company.local --insecure
+python3 server/seed_demo.py https://wp-suite.company.local --clean   # remove it later
+```
+
+> **What shows where:** the DEMO **project** is API/SQL-backed, so it appears in
+> the home-page project picker right away (this is the visible proof that the
+> projects → SQL path works end-to-end). The DEMO **SOP and Work Packages** are
+> written to SQL too, but the current front end still reads SOPs/WPs from the
+> browser, so they won't render in the Creator/Dashboard until the Phase 2
+> wiring. Inspect them at the SQL layer with `smoketest.py` or:
+> ```bash
+> docker compose exec db psql -U wpsuite -d wpsuite \
+>   -c "select number, subject, status from work_packages order by number;"
+> ```
+
 ---
 
 ## What is stored in SQL today

Dockerfile

@@ -4,5 +4,7 @@ COPY server/requirements.txt ./server/
 RUN pip install --no-cache-dir -r server/requirements.txt
 COPY server/ ./server/
 EXPOSE 8000
-CMD ["gunicorn", "-k", "uvicorn.workers.UvicornWorker", \
+# --preload imports the app once in the master (so create_all runs a single time)
+# before forking workers, preventing a table-creation race on first startup.
+CMD ["gunicorn", "-k", "uvicorn.workers.UvicornWorker", "--preload", \
      "-b", "0.0.0.0:8000", "--workers", "2", "server.app:app"]

docker-compose.yml

@@ -19,7 +19,14 @@ services:
     build: .
     container_name: wp_api
     environment:
-      DATABASE_URL: ${DATABASE_URL}
+      # Preferred: the API builds its own connection string from these and
+      # encodes the password automatically (no manual URL-encoding needed).
+      POSTGRES_USER: ${POSTGRES_USER}
+      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
+      POSTGRES_DB: ${POSTGRES_DB}
+      POSTGRES_HOST: db
+      # Optional full-URL override (must be URL-encoded if used).
+      DATABASE_URL: ${DATABASE_URL:-}
     restart: unless-stopped
     depends_on:
       db:

html/admin.html

@@ -0,0 +1,107 @@
+<!DOCTYPE html>
+<html lang="en">
+<head>
+<meta charset="UTF-8">
+<meta name="viewport" content="width=device-width, initial-scale=1.0">
+<title>Admin Console — Work Package Suite</title>
+<link rel="icon" href="favicon.ico" sizes="any">
+<style>
+  :root{ --bg:#f4f5f7; --surface:#fff; --border:#e3e6ec; --border-strong:#d0d5de; --text:#1a2230;
+    --muted:#5a6675; --dim:#9aa3b2; --accent:#2563d6; --green:#15924f; --green-bg:#e4f6ec;
+    --red:#cf3b3b; --red-bg:#fbeaea; --amber:#b87100; --amber-bg:#fdf2e0; --mono:'Cascadia Mono',Consolas,monospace; }
+  *{ box-sizing:border-box; }
+  body{ margin:0; font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif; background:var(--bg); color:var(--text); }
+  .wrap{ max-width:860px; margin:0 auto; padding:28px 20px 80px; }
+  h1{ font-size:20px; margin:0 0 2px; }
+  .sub{ color:var(--muted); font-size:13px; margin-bottom:18px; }
+  .card{ background:var(--surface); border:1px solid var(--border); border-radius:10px; padding:18px 20px; margin-bottom:16px; }
+  .card h2{ font-size:14px; margin:0 0 12px; text-transform:uppercase; letter-spacing:.03em; color:var(--accent); }
+  button{ font:inherit; font-size:13px; font-weight:600; border-radius:6px; padding:8px 14px; cursor:pointer;
+    border:1px solid var(--border-strong); background:#fff; color:var(--text); }
+  button:hover{ border-color:var(--accent); color:var(--accent); }
+  button.primary{ background:var(--accent); border-color:var(--accent); color:#fff; }
+  button.primary:hover{ background:#1e54bb; color:#fff; }
+  button.danger{ border-color:var(--red); color:var(--red); }
+  button.danger:hover{ background:var(--red-bg); }
+  .row{ display:flex; gap:10px; flex-wrap:wrap; align-items:center; }
+  .banner{ padding:10px 14px; border-radius:8px; font-size:13px; font-weight:600; margin-top:10px; border:1px solid var(--border); background:var(--surface); }
+  .banner.ok{ background:var(--green-bg); color:var(--green); border-color:var(--green); }
+  .banner.bad{ background:var(--red-bg); color:var(--red); border-color:var(--red); }
+  pre.out{ background:#0f1525; color:#d7e0f5; border-radius:8px; padding:12px 14px; font-family:var(--mono);
+    font-size:12px; line-height:1.55; white-space:pre-wrap; max-height:340px; overflow:auto; margin:12px 0 0; }
+  pre.out .p{ color:#56d364; font-weight:700; } pre.out .f{ color:#ff7b72; font-weight:700; }
+  table.kv{ border-collapse:collapse; font-size:13px; margin-top:8px; }
+  table.kv th{ text-align:left; padding:5px 18px 5px 0; color:var(--muted); font-weight:600; }
+  table.kv td{ padding:5px 0; font-variant-numeric:tabular-nums; font-weight:700; }
+  .note{ font-size:12px; color:var(--dim); margin-top:10px; }
+  .gate-overlay{ position:fixed; inset:0; background:var(--bg); display:flex; align-items:center; justify-content:center; padding:20px; }
+  .gate-box{ background:var(--surface); border:1px solid var(--border); border-radius:12px; padding:28px; max-width:380px; width:100%; box-shadow:0 8px 30px rgba(20,30,50,.12); }
+  .gate-box h2{ margin:0 0 4px; font-size:17px; }
+  .gate-box p{ color:var(--muted); font-size:13px; margin:0 0 16px; }
+  .gate-box input{ width:100%; padding:10px 12px; font-size:14px; border:1px solid var(--border-strong); border-radius:6px; margin-bottom:12px; }
+  .gate-msg{ color:var(--red); font-size:12px; min-height:16px; margin-bottom:8px; }
+  .secwarn{ background:var(--amber-bg); color:var(--amber); border:1px solid var(--amber); border-radius:8px; padding:9px 13px; font-size:12px; margin-bottom:16px; }
+  a.home{ color:var(--accent); font-size:13px; text-decoration:none; }
+</style>
+</head>
+<body>
+
+<!-- GATE -->
+<div class="gate-overlay" id="admin-gate">
+  <div class="gate-box">
+    <h2>🔒 Admin Console</h2>
+    <p>Enter the admin passphrase to continue.</p>
+    <input type="password" id="gate-input" placeholder="Passphrase" autocomplete="off"
+           onkeydown="if(event.key==='Enter') tryUnlock()">
+    <div class="gate-msg" id="gate-msg"></div>
+    <button class="primary" style="width:100%" onclick="tryUnlock()">Unlock</button>
+  </div>
+</div>
+
+<!-- CONSOLE -->
+<div class="wrap" id="admin-main" style="display:none">
+  <div class="row" style="justify-content:space-between">
+    <div><h1>Work Package Suite — Admin Console</h1><div class="sub">Stack diagnostics &amp; tests · talks to <code>/api</code> on this host</div></div>
+    <div class="row"><a class="home" href="index.html">← Site</a> <button onclick="lock()">Lock</button></div>
+  </div>
+
+  <div class="secwarn">⚠ This page is gated client-side only — that stops casual access, not a determined user. For real protection, restrict this host/route at the network or reverse-proxy layer.</div>
+
+  <!-- CONNECTIVITY -->
+  <div class="card">
+    <h2>API connectivity</h2>
+    <div class="row"><button class="primary" onclick="checkHealth()">Check /api/health</button></div>
+    <div class="banner" id="health-banner">—</div>
+  </div>
+
+  <!-- DB SNAPSHOT -->
+  <div class="card">
+    <h2>Database snapshot</h2>
+    <div class="row"><button onclick="snapshot()">Refresh counts</button></div>
+    <div id="snapshot-out" class="note">Click refresh to read row counts from SQL via the API.</div>
+  </div>
+
+  <!-- SMOKE TEST -->
+  <div class="card">
+    <h2>End-to-end smoke test</h2>
+    <div class="sub" style="margin-bottom:8px">Creates a throwaway project, exercises the issue gate / status / metrics / comments, then deletes it (cascade). Mirrors <code>server/smoketest.py</code>.</div>
+    <div class="row"><button class="primary" onclick="runSmokeTest()">Run smoke test</button></div>
+    <pre class="out" id="smoke-out">Ready.</pre>
+  </div>
+
+  <!-- DEMO DATA -->
+  <div class="card">
+    <h2>Demo data</h2>
+    <div class="sub" style="margin-bottom:8px">Seed a realistic <code>DEMO</code> project (SOP + a spread of Work Packages) into SQL, or remove all <code>DEMO-</code>/<code>SMOKE-</code> projects.</div>
+    <div class="row">
+      <button class="primary" onclick="seedDemo()">Seed demo project</button>
+      <button class="danger" onclick="cleanDemo()">Clean DEMO / SMOKE projects</button>
+    </div>
+    <pre class="out" id="demo-out">Ready.</pre>
+    <div class="note">Note: the seeded <strong>project</strong> appears in the home picker; its SOP/WPs live in SQL but won't render in the Creator/Dashboard until the front end is wired to the API (Phase 2).</div>
+  </div>
+</div>
+
+<script src="admin.js"></script>
+</body>
+</html>

html/admin.js

@@ -0,0 +1,170 @@
+/* Admin console for the Work Package Suite.
+   Browser-side diagnostics + tests that call the same /api on this host.
+
+   PASSPHRASE GATE (lightweight / obfuscation only):
+   The gate compares a SHA-256 hash so the passphrase isn't in the source, but a
+   determined user can still bypass client-side JS. For real protection, restrict
+   this host/route at the network or reverse-proxy layer.
+
+   Default passphrase: "prime-admin"
+   To change it: compute a new hash and replace ADMIN_PASSPHRASE_SHA256 below —
+     python3 -c "import hashlib,sys;print(hashlib.sha256(sys.argv[1].encode()).hexdigest())" "your-new-passphrase"
+   or in a browser console:
+     crypto.subtle.digest('SHA-256', new TextEncoder().encode('your-new-passphrase'))
+       .then(b=>console.log([...new Uint8Array(b)].map(x=>x.toString(16).padStart(2,'0')).join('')));
+*/
+const ADMIN_PASSPHRASE_SHA256 = 'ae1fb92c43fccbad26f05434a194f574ec98a2197e0ff4080f84e6e26a8dd00f';
+
+// ── gate ──────────────────────────────────────────────────────────────────────
+async function sha256hex(s){
+  const buf = await crypto.subtle.digest('SHA-256', new TextEncoder().encode(s));
+  return [...new Uint8Array(buf)].map(b=>b.toString(16).padStart(2,'0')).join('');
+}
+async function tryUnlock(){
+  const v = document.getElementById('gate-input').value || '';
+  const msg = document.getElementById('gate-msg');
+  if(!v){ msg.textContent='Enter the passphrase.'; return; }
+  let h;
+  try { h = await sha256hex(v); }
+  catch(e){ msg.textContent='This page must be served over HTTPS (or localhost) to unlock.'; return; }
+  if(h === ADMIN_PASSPHRASE_SHA256){ sessionStorage.setItem('wp_admin_ok','1'); reveal(); }
+  else { msg.textContent='Incorrect passphrase.'; }
+}
+function reveal(){
+  document.getElementById('admin-gate').style.display='none';
+  document.getElementById('admin-main').style.display='';
+  checkHealth();
+}
+function lock(){ sessionStorage.removeItem('wp_admin_ok'); location.reload(); }
+
+// ── api helper ──────────────────────────────────────────────────────────────
+async function api(method, path, body){
+  const opt = { method, headers:{ 'Accept':'application/json' } };
+  if(body !== undefined){ opt.headers['Content-Type']='application/json'; opt.body=JSON.stringify(body); }
+  try {
+    const r = await fetch(path, opt);
+    const t = await r.text();
+    let json; try { json = t ? JSON.parse(t) : null; } catch(_){ json = t; }
+    return { status:r.status, json };
+  } catch(e){ return { status:0, json:String(e) }; }
+}
+
+// ── connectivity ──────────────────────────────────────────────────────────────
+async function checkHealth(){
+  const b = document.getElementById('health-banner');
+  b.className='banner'; b.textContent='Checking…';
+  const { status, json } = await api('GET','/api/health');
+  if(status===200 && json && json.ok){
+    b.className='banner ok'; b.textContent='✅ API reachable — /api/health returned ok.';
+  } else if(status===404){
+    b.className='banner bad'; b.textContent='❌ /api/ returns 404 — the reverse proxy is not routing /api/ to the API. The site loads but the API is unreachable from the browser.';
+  } else if(status===0){
+    b.className='banner bad'; b.textContent='❌ Could not reach the server: '+json;
+  } else {
+    b.className='banner bad'; b.textContent='❌ Unexpected response: HTTP '+status;
+  }
+}
+
+// ── db snapshot ───────────────────────────────────────────────────────────────
+async function snapshot(){
+  const out = document.getElementById('snapshot-out'); out.textContent='Loading…';
+  const [p,s,w,c] = await Promise.all([
+    api('GET','/api/projects'), api('GET','/api/sops'),
+    api('GET','/api/wps'), api('GET','/api/comments')]);
+  if(p.status!==200){
+    out.innerHTML = `<div class="banner bad">API not reachable (HTTP ${p.status}). Fix /api/ routing first.</div>`; return;
+  }
+  const n = r => Array.isArray(r.json) ? r.json.length : ('err '+r.status);
+  out.innerHTML = `<table class="kv">
+    <tr><th>Projects</th><td>${n(p)}</td></tr>
+    <tr><th>SOPs</th><td>${n(s)}</td></tr>
+    <tr><th>Work Packages</th><td>${n(w)}</td></tr>
+    <tr><th>Comments</th><td>${n(c)}</td></tr></table>`;
+}
+
+// ── smoke test ────────────────────────────────────────────────────────────────
+function smLog(html){ const o=document.getElementById('smoke-out'); o.innerHTML += html + '\n'; o.scrollTop=o.scrollHeight; }
+async function runSmokeTest(){
+  const o=document.getElementById('smoke-out'); o.innerHTML=''; let pass=0, fail=0, pid=null;
+  const chk=(name,cond,detail)=>{ if(cond){ pass++; smLog('<span class="p">PASS</span>  '+name); }
+    else { fail++; smLog('<span class="f">FAIL</span>  '+name+(detail?'  ('+detail+')':'')); } return cond; };
+  try {
+    let r = await api('GET','/api/health');
+    if(!chk('health endpoint ok', r.status===200 && r.json && r.json.ok, 'status '+r.status)){
+      smLog('\nAborting — API unreachable (fix /api/ routing).'); return finishSmoke(pass,fail);
+    }
+    r = await api('POST','/api/projects',{name:'ZZ Smoke Test Project',number:'SMOKE-001',client:'Internal QA',created_by:'admin-console'});
+    pid = r.json && r.json.id; chk('create project', r.status===200 && !!pid, 'status '+r.status);
+    r = await api('GET','/api/projects/'+pid); chk('fetch project by id', r.status===200 && r.json.number==='SMOKE-001');
+    r = await api('GET','/api/projects'); chk('project in list', r.status===200 && r.json.some(p=>p.id===pid));
+    r = await api('POST','/api/sops',{project_id:pid,name:'ZZ Smoke SOP',number:'SMOKE-001',complete:true,data:{governance:{disciplines:['Mechanical','Electrical','Tech']}}});
+    const sid = r.json && r.json.id; chk('create SOP linked to project', r.status===200 && !!sid && r.json.project_id===pid);
+    r = await api('GET','/api/sops/latest?project_id='+pid); chk('latest SOP resolves', r.status===200 && r.json.id===sid);
+    r = await api('POST','/api/wps',{project_id:pid,sop_id:sid,number:'WP01-SMOKE',subject:'Smoke test package',type:'Conduit Install',status:'Scheduled',data:{disciplines:['Electrical'],hours:'40',constraints:[{name:'Materials',status:'open',comment:'awaiting delivery'},{name:'Safety',status:'cleared',comment:''}]}});
+    const wid = r.json && r.json.id; chk('create work package', r.status===200 && !!wid);
+    r = await api('POST','/api/wps/'+wid+'/issue'); chk('issue blocked while a constraint is open (409)', r.status===409, 'status '+r.status);
+    await api('POST','/api/wps',{id:wid,project_id:pid,sop_id:sid,number:'WP01-SMOKE',subject:'Smoke test package',type:'Conduit Install',status:'Scheduled',data:{disciplines:['Electrical'],hours:'40',constraints:[{name:'Materials',status:'cleared',comment:''},{name:'Safety',status:'cleared',comment:''}]}});
+    r = await api('POST','/api/wps/'+wid+'/issue'); chk('issue succeeds once cleared', r.status===200 && r.json.status==='Issued', 'status '+r.status);
+    chk('issued_at timestamp set', !!(r.json && r.json.issued_at));
+    r = await api('POST','/api/wps/'+wid+'/status',{status:'In Progress'}); chk('status transition', r.status===200 && r.json.status==='In Progress');
+    r = await api('GET','/api/wps/metrics?project_id='+pid); chk('metrics aggregate', r.status===200 && r.json && r.json.total>=1, JSON.stringify(r.json));
+    r = await api('POST','/api/feedback',{type:'wp_review_comment',name:'admin-console',wp_id:wid,text:'SMOKE TEST comment — safe to delete'}); chk('post comment', r.status===200 && !!(r.json && r.json.id));
+    r = await api('GET','/api/wps?project_id='+pid); chk('list WPs by project', r.status===200 && r.json.some(w=>w.id===wid));
+  } catch(e){ chk('unexpected error', false, String(e)); }
+  finally {
+    if(pid){ const r=await api('DELETE','/api/projects/'+pid); chk('cleanup — delete project (cascades SOP+WPs)', r.status===200, 'status '+r.status); }
+    finishSmoke(pass,fail);
+  }
+}
+function finishSmoke(pass,fail){
+  const total=pass+fail;
+  smLog('\n'+pass+'/'+total+' checks passed.');
+  smLog(fail ? '<span class="f">RESULT: FAIL ('+fail+')</span>' : '<span class="p">RESULT: ALL PASS — API, Python logic, and SQL are working.</span>');
+}
+
+// ── demo data ─────────────────────────────────────────────────────────────────
+function demoLog(s){ const o=document.getElementById('demo-out'); o.innerHTML += s + '\n'; o.scrollTop=o.scrollHeight; }
+function stdConstraints(open){ return ['Safety & Permitting','Quality Control / Inspection','IFC Drawings & Specs','Schedule','Materials (on site, bagged & tagged)']
+  .map(n=>({name:n, status:(open&&open.includes(n))?'open':'cleared', comment:''})); }
+async function seedDemo(){
+  const o=document.getElementById('demo-out'); o.innerHTML='';
+  let r = await api('GET','/api/health');
+  if(!(r.status===200 && r.json && r.json.ok)){ demoLog('❌ API unreachable — fix /api/ routing first.'); return; }
+  r = await api('POST','/api/projects',{name:'DEMO — Micron INC (test data)',number:'DEMO-001',client:'Micron Technology, Inc.',division:'Semiconductor',site:'Boise, ID — Fab',created_by:'admin-console'});
+  if(r.status!==200){ demoLog('❌ create project failed (HTTP '+r.status+')'); return; }
+  const pid=r.json.id; demoLog('Project created: '+r.json.name);
+  r = await api('POST','/api/sops',{project_id:pid,name:'DEMO SOP',number:'DEMO-001',complete:true,data:{governance:{woFormat:'WP##-[Sector]-[TYPE]',disciplines:['Mechanical','Electrical','Tech'],discMode:'choice',instanceSuffix:'letter',woSize:'Standard — 3–5 days (≈40–80 hrs)',sizeHoursMax:'80'}}});
+  const sid=r.json && r.json.id; demoLog('SOP created (complete).');
+  const mk=async(num,subj,typ,status,data,parent)=>{ const body={project_id:pid,sop_id:sid,number:num,subject:subj,type:typ,status,created_by:'admin-console',data}; if(parent)body.parent_id=parent; const rr=await api('POST','/api/wps',body); demoLog('  WP '+num+'  ['+status+']'); return rr.json; };
+  await mk('WP01-1P-CONDUIT','1P horn/strobe conduit','Conduit Install','Issued',{disciplines:['Electrical'],hours:'40',constraints:stdConstraints(),due:'2026-06-30'});
+  await mk('WP02-1P-WIRE','1P wire pull','Wire Pull','Scheduled',{disciplines:['Electrical'],hours:'60',constraints:stdConstraints(['Materials (on site, bagged & tagged)']),due:'2026-07-04'});
+  const masterId='wp_demo_master_chiller';
+  const kids=[['WP03-CHILLER_Mech','Mechanical','A','Mechanical Install','In Progress'],['WP03-CHILLER_Elec','Electrical','B','Wire Pull','Scheduled'],['WP03-CHILLER_Tech','Tech','C','Terminations','Draft']];
+  const kidIds=[];
+  for(const [num,disc,label,typ,status] of kids){ const id='wp_demo_'+label.toLowerCase(); kidIds.push(id);
+    await api('POST','/api/wps',{id,project_id:pid,sop_id:sid,parent_id:masterId,number:num,subject:'Chiller skid — '+disc,type:typ,status,created_by:'admin-console',data:{disciplines:[disc],instanceOf:masterId,instanceLabel:label,parentNumber:'WP03-CHILLER',hours:'50',constraints:stdConstraints(),due:'2026-07-10'}});
+    demoLog('  WP '+num+'  ['+status+']  (instance '+label+')'); }
+  await api('POST','/api/wps',{id:masterId,project_id:pid,sop_id:sid,number:'WP03-CHILLER',subject:'Chiller skid (multi-discipline master)',type:'Mechanical Install',status:'Scheduled',created_by:'admin-console',data:{disciplines:['Mechanical','Electrical','Tech'],split:true,children:kidIds,hours:'150',constraints:stdConstraints(),due:'2026-07-10'}});
+  demoLog('  WP WP03-CHILLER  [master, split into A/B/C]');
+  await mk('WP04-2P-TERM','2P terminations','Terminations','In Progress',{disciplines:['Tech'],hours:'30',actualHrs:'20',constraints:stdConstraints(),due:'2026-06-10'});
+  await mk('WP05-3P-PANEL','3P panel install','Panel Install','Draft',{disciplines:['Electrical'],hours:'120',constraints:stdConstraints(['Schedule']),due:'2026-07-20'});
+  r = await api('GET','/api/wps/metrics?project_id='+pid);
+  demoLog('\nMetrics (masters excluded): '+JSON.stringify(r.json));
+  demoLog('\n✅ Done — "DEMO — Micron INC (test data)" now appears in the home picker.');
+  snapshot();
+}
+async function cleanDemo(){
+  if(!confirm('Delete ALL projects whose number starts with DEMO- or SMOKE- (and their SOPs/WPs via cascade)?')) return;
+  const o=document.getElementById('demo-out'); o.innerHTML='';
+  const r = await api('GET','/api/projects');
+  if(r.status!==200){ demoLog('❌ API unreachable (HTTP '+r.status+').'); return; }
+  const targets=(r.json||[]).filter(p=>/^(DEMO-|SMOKE-)/.test(String(p.number||'')));
+  if(!targets.length){ demoLog('Nothing to remove.'); return; }
+  for(const p of targets){ await api('DELETE','/api/projects/'+p.id); demoLog('Deleted: '+p.name+'  ('+p.number+')'); }
+  demoLog('\n✅ Removed '+targets.length+' project(s).');
+  snapshot();
+}
+
+// reveal immediately if already unlocked this session
+if(sessionStorage.getItem('wp_admin_ok')==='1'){ reveal(); }
+else { const i=document.getElementById('gate-input'); if(i) i.focus(); }

html/index.html

@@ -471,7 +471,7 @@
 
   <!-- FOOTER -->
   <footer class="footer">
-    <p>Work Package Suite v1.0 | Prime Controls | All files work offline with local browser storage</p>
+    <p>Work Package Suite v1.0 | Prime Controls - Business Technology Group | Pilot Use Only</p>
   </footer>
 
   <script src="feedback-config.js"></script>
@@ -664,9 +664,7 @@
       if (window.postFeedback) window.postFeedback({ type: 'home_feedback', ...comment });
 
       document.getElementById('comment-text').value = '';
-      document.getElementById('commenter-name').value = '';
       loadComments();
-      alert('Thank you! Feedback submitted.');
     }
 
     function exportFeedback() {

html/work-package-suite-app.js

@@ -477,18 +477,45 @@ function removeRole(i){
   renderOptionalRoles();
 }
 
+// Seed the standard 10 once; after that, render reflects state.constraints
+// (checkbox = whether each standard one is active) and never clobbers customs.
+let _constraintsSeeded = false;
 function renderStandardConstraints(){
   const container = document.getElementById('standard-constraints');
+  if(!_constraintsSeeded){
+    if(!state.constraints || !state.constraints.length){
+      state.constraints = STANDARD_10_CONSTRAINTS.map(c=>({...c}));
+    }
+    _constraintsSeeded = true;
+  }
+  const active = name => state.constraints.some(c=>c.name===name);
   container.innerHTML = STANDARD_10_CONSTRAINTS.map(c=>`
     <div style="display:flex; align-items:start; gap:0.75rem; padding:0.75rem; background:var(--bg); border:1px solid var(--border); border-radius:6px; margin-bottom:0.5rem;">
-      <input type="checkbox" id="const_${c.name}" checked onchange="toggleConstraint('${c.name}')" style="width:18px; height:18px; cursor:pointer; margin-top:0.2rem;">
+      <input type="checkbox" id="const_${c.name}" ${active(c.name)?'checked':''} onchange="toggleConstraint('${c.name}')" style="width:18px; height:18px; cursor:pointer; margin-top:0.2rem;">
       <div style="flex:1;">
         <label for="const_${c.name}" style="margin:0; font-weight:600; display:block; cursor:pointer;">${c.name}</label>
         <div style="font-size:12px; color:var(--text-dim); margin-top:0.25rem;">${c.description}</div>
       </div>
     </div>
   `).join('');
-  state.constraints = STANDARD_10_CONSTRAINTS.map(c=>({...c}));
+  renderCustomConstraints();
+}
+
+// Render the custom (non-standard) constraints into their own list with remove buttons.
+function renderCustomConstraints(){
+  const el = document.getElementById('custom-constraints-list'); if(!el) return;
+  const stdNames = STANDARD_10_CONSTRAINTS.map(c=>c.name);
+  const customs = state.constraints.filter(c=>!stdNames.includes(c.name));
+  el.innerHTML = customs.length ? customs.map(c=>`
+    <div style="display:flex; align-items:center; justify-content:space-between; gap:0.75rem; padding:0.6rem 0.75rem; background:var(--bg); border:1px solid var(--border); border-radius:6px; margin-bottom:0.5rem;">
+      <strong>${escAttr(c.name)}</strong>
+      <button onclick="removeCustomConstraint('${c.name.replace(/'/g,"\\'")}')" title="Remove" style="background:var(--danger); color:#fff; border:none; border-radius:4px; width:28px; height:28px; cursor:pointer; font-weight:600;">✕</button>
+    </div>`).join('') : `<div style="font-size:12px; color:var(--text-dim);">No custom constraints added yet.</div>`;
+}
+
+function removeCustomConstraint(name){
+  state.constraints = state.constraints.filter(c=>c.name!==name);
+  renderCustomConstraints();
 }
 
 function toggleConstraint(name){
@@ -514,13 +541,24 @@ function closeConstraintModal(){
 }
 
 function addCustomConstraint(name){
-  if(!state.constraints.find(c=>c.name===name)){
+  if(name && !state.constraints.find(c=>c.name===name)){
     state.constraints.push({name,description:''});
   }
   closeConstraintModal();
   renderStandardConstraints();
 }
 
+// Free-text custom constraint from the modal's input.
+function addCustomConstraintText(){
+  const inp = document.getElementById('custom-constraint-input');
+  const name = (inp && inp.value || '').trim();
+  if(!name){ if(inp) inp.focus(); return; }
+  if(state.constraints.find(c=>c.name===name)){ alert('That constraint is already in the list.'); return; }
+  state.constraints.push({name, description:''});
+  if(inp) inp.value='';
+  renderStandardConstraints();
+}
+
 const DEFAULT_SEQUENCE = ['Layout','Conduit Install','Tray Install','Wire Pull','Device Install','Termination','QC Inspection','Commissioning'];
 
 let seqDragIndex = null;
@@ -600,20 +638,30 @@ const DEFAULT_SOURCES = [
 function escAttr(v){ return String(v==null?'':v).replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;').replace(/"/g,'&quot;'); }
 function renderSources(){
   const container = document.getElementById('sources-list');
-  if(!state.sources.length) state.sources = DEFAULT_SOURCES.map(s=>({label:s.label, system:'', notes:'', link:'', ph:s.ph}));
-  container.innerHTML = state.sources.map((s,i)=>`
-    <div style="display:grid; grid-template-columns:150px 150px 250px 150px 30px; gap:1rem; align-items:center; padding:1rem; background:var(--bg); border-radius:6px; margin-bottom:0.5rem; border:1px solid var(--border);">
-      <input type="text" value="${escAttr(s.label)}" placeholder="Label" onchange="state.sources[${i}].label=this.value" style="padding:0.5rem; font-size:12px; border:1px solid var(--border); border-radius:4px;">
-      <input type="text" value="${escAttr(s.system)}" placeholder="${escAttr(s.ph||'System of record')}" onchange="state.sources[${i}].system=this.value" style="padding:0.5rem; font-size:12px; border:1px solid var(--border); border-radius:4px;">
-      <input type="text" value="${escAttr(s.link)}" placeholder="Paste SharePoint 'Copy Link' URL" onchange="state.sources[${i}].link=this.value" style="padding:0.5rem; font-size:12px; border:1px solid var(--border); border-radius:4px;">
-      <input type="text" value="${escAttr(s.notes)}" placeholder="Notes" onchange="state.sources[${i}].notes=this.value" style="padding:0.5rem; font-size:12px; border:1px solid var(--border); border-radius:4px;">
-      <button style="background:var(--danger); color:white; padding:0.25rem; width:30px; height:30px; border:none; border-radius:4px; cursor:pointer; font-weight:600;" onclick="state.sources.splice(${i},1); renderSources()">✕</button>
-    </div>
-  `).join('');
+  if(!state.sources.length) state.sources = DEFAULT_SOURCES.map(s=>({label:s.label, system:'', notes:'', link:'', ph:s.ph, preset:true}));
+  const grid = "display:grid; grid-template-columns:170px 170px 1fr 160px 30px; gap:1rem; align-items:center;";
+  const inStyle = "padding:0.5rem; font-size:12px; border:1px solid var(--border); border-radius:4px;";
+  const header = `<div style="${grid} padding:0 1rem 0.4rem; font-size:11px; font-weight:700; text-transform:uppercase; letter-spacing:.03em; color:var(--text-dim);">
+      <div>Data Type</div><div>Location / Platform</div><div>URL</div><div>Notes</div><div></div>
+    </div>`;
+  container.innerHTML = header + state.sources.map((s,i)=>{
+    // Preset data types are fixed labels; custom rows (Add Source) get an editable name.
+    const dataType = s.preset
+      ? `<div style="font-weight:600; font-size:13px;">${escAttr(s.label)}</div>`
+      : `<input type="text" value="${escAttr(s.label)}" placeholder="Custom data type" onchange="state.sources[${i}].label=this.value" style="${inStyle} font-weight:600;">`;
+    return `<div style="${grid} padding:1rem; background:var(--bg); border-radius:6px; margin-bottom:0.5rem; border:1px solid var(--border);">
+      ${dataType}
+      <input type="text" value="${escAttr(s.system)}" placeholder="${escAttr(s.ph||'Procore / Bluebeam / SharePoint…')}" onchange="state.sources[${i}].system=this.value" style="${inStyle}">
+      <input type="text" value="${escAttr(s.link)}" placeholder="Paste the 'Copy Link' URL" onchange="state.sources[${i}].link=this.value" style="${inStyle}">
+      <input type="text" value="${escAttr(s.notes)}" placeholder="Notes" onchange="state.sources[${i}].notes=this.value" style="${inStyle}">
+      <button style="background:var(--danger); color:white; padding:0.25rem; width:30px; height:30px; border:none; border-radius:4px; cursor:pointer; font-weight:600;" onclick="state.sources.splice(${i},1); renderSources()" title="Remove">✕</button>
+    </div>`;
+  }).join('');
 }
 
 function addSource(){
-  state.sources.push({label:'',system:'',notes:'',link:''});
+  // Added rows are custom — the user types their own data type here.
+  state.sources.push({label:'', system:'', notes:'', link:'', preset:false});
   renderSources();
 }
 
@@ -823,9 +871,7 @@ function submitComment(){
   if(window.postFeedback) window.postFeedback({type:'sop_step_comment', ...comment});
 
   document.getElementById('comment-text').value = '';
-  document.getElementById('commenter-name').value = '';
   loadStepComments();
-  alert('✓ Comment submitted!');
 }
 
 function exportComments(){

html/work-package-suite.html

@@ -172,14 +172,23 @@
                 <small>Use ## for counter, [Sector] [TYPE] as variables</small>
               </div>
               <div class="field">
-                <label>Issuance Strategy</label>
-                <select id="gov_issuance" multiple size="3">
+                <label>Issuance Strategy<span class="help-tip" data-tip="How Work Packages are grouped and released on this project. Pick one or more — most projects combine 'By Sector / Area' with 'By Phase / Sequence'.">i</span></label>
+                <select id="gov_issuance" multiple size="4">
                   <option selected>By Sector / Area</option>
                   <option>By Discipline</option>
                   <option>By Phase / Sequence</option>
                   <option>By Resource Availability</option>
                 </select>
-                <small>Hold Ctrl to select multiple</small>
+                <small>Hold Ctrl (Cmd on Mac) to select multiple.</small>
+                <div class="notice" style="margin-top:0.6rem; font-size:12px;">
+                  <strong>Examples:</strong>
+                  <ul style="margin:0.35rem 0 0; padding-left:1.1rem;">
+                    <li><strong>By Sector / Area</strong> — one package per physical area, e.g. <em>all work in Sector 1P, Level 2 chase</em>.</li>
+                    <li><strong>By Discipline</strong> — separate packages per trade, e.g. <em>Electrical wire-pull</em> vs <em>Mechanical install</em>.</li>
+                    <li><strong>By Phase / Sequence</strong> — follow the build order, e.g. <em>rough-in → wire pull → terminations</em>.</li>
+                    <li><strong>By Resource Availability</strong> — size to a crew/equipment window, e.g. <em>one boom-lift crew's week</em>.</li>
+                  </ul>
+                </div>
               </div>
             </div>
 
@@ -374,7 +383,12 @@
         <h3>Add Custom Constraint</h3>
         <button class="modal-close" onclick="closeConstraintModal()">✕</button>
       </div>
-      <div id="constraint-library" style="max-height: 400px; overflow-y: auto; margin: 1rem 0;"></div>
+      <div style="display:flex; gap:0.5rem; margin:1rem 0 0.5rem;">
+        <input type="text" id="custom-constraint-input" placeholder="Type a custom constraint name…" style="flex:1; padding:0.55rem 0.65rem; border:1px solid var(--border); border-radius:4px;" onkeydown="if(event.key==='Enter'){addCustomConstraintText();event.preventDefault();}">
+        <button class="add-btn" onclick="addCustomConstraintText()">Add</button>
+      </div>
+      <div style="font-size:12px; color:var(--text-dim); margin-bottom:0.5rem;">…or pick from the library:</div>
+      <div id="constraint-library" style="max-height: 320px; overflow-y: auto; margin: 0 0 1rem;"></div>
       <button class="nav-btn" onclick="closeConstraintModal()">Done</button>
     </div>
   </div>

html/wp-creation-app.js

@@ -529,6 +529,20 @@ function setConstraint(i,val){
   if(val==='open' && STATUS_ORDER.indexOf(getRadio('status'))>=ISSUED_IDX){
     prevStatus=getRadio('status'); holdContext={index:i, before};
     openHoldModal(pkgConstraints[i].name, true);
+    return;
+  }
+  // Clearing the LAST open constraint makes the package release-ready — offer to
+  // issue it and scroll up to the status control so the change is visible.
+  if(before==='open' && val!=='open' && readiness().open===0){
+    const st=getRadio('status');
+    if(STATUS_ORDER.indexOf(st) < ISSUED_IDX){
+      if(confirm('All constraints are cleared — this Work Package is release-ready.\n\nMark it as Issued now?')){
+        setRadio('status','Issued'); prevStatus='Issued'; updateReleaseBanner();
+        track('status_change',{status:'Issued',via:'constraint_clear'});
+      }
+      const sg=document.getElementById('status-group');
+      if(sg) sg.scrollIntoView({behavior:'smooth', block:'center'});
+    }
   }
 }
 function readiness(){ const open=pkgConstraints.filter(c=>c.status==='open').length; return {open, total:pkgConstraints.length, cleared:pkgConstraints.filter(c=>c.status==='cleared').length, ready:open===0}; }
@@ -771,7 +785,24 @@ function setFormChrome(on){
   if(nav) nav.style.display = on ? '' : 'none';
   if(save) save.style.display = on ? 'flex' : 'none';
   document.body.classList.toggle('has-sticky-save', !!on);
-  if(on){ buildSectionNav(); updateStickyStatus(); }
+  if(on){ buildSectionNav(); updateStickyStatus(); makeCollapsible(); }
+}
+// Make each form card collapsible by clicking its heading (idempotent).
+function makeCollapsible(){
+  document.querySelectorAll('.main > .card').forEach(card=>{
+    if(card.id==='saved-card') return;
+    const head=card.querySelector('.section-header, .sub-heading');
+    if(!head || head.dataset.collapsible) return;
+    head.dataset.collapsible='1';
+    head.style.cursor='pointer';
+    const chev=document.createElement('span'); chev.className='collapse-chev'; chev.textContent='▾';
+    head.insertBefore(chev, head.firstChild);
+    head.addEventListener('click', e=>{
+      if(['INPUT','SELECT','TEXTAREA','BUTTON','A'].includes(e.target.tagName) || e.target.classList.contains('help-tip')) return;
+      const collapsed=card.classList.toggle('collapsed');
+      chev.textContent = collapsed ? '▸' : '▾';
+    });
+  });
 }
 function buildSectionNav(){
   const nav=document.getElementById('section-nav'); if(!nav) return;

html/wp-creation-styles.css

@@ -564,6 +564,11 @@
   .so-date { font-size:13px; font-variant-numeric:tabular-nums; }
   .so-ovr { margin-left:8px; font-size:11px; }
 
+  /* Collapsible form sections */
+  .collapse-chev { display:inline-block; width:1em; margin-right:7px; color:var(--text-muted); font-size:11px; user-select:none; }
+  .card.collapsed > :not(.section-header):not(.sub-heading) { display:none !important; }
+  .card.collapsed .section-desc { display:none; }
+
   /* Section nav (jump chips) */
   .section-nav-bar{ position:sticky; top:0; z-index:30; display:flex; flex-wrap:wrap; gap:6px;
     padding:8px 12px; background:rgba(255,255,255,.92); backdrop-filter:blur(4px);

nginx/conf.d/wp-suite.conf

@@ -4,7 +4,7 @@
 
 server {
     listen 80;
-    server_name _;
+    server_name wp.controls.dev;
 
     root /usr/share/nginx/html;
     index index.html;

server/db.py

@@ -1,28 +1,49 @@
 """Database engine and session setup.
 
-The connection string comes from the DATABASE_URL environment variable, e.g.
-    postgresql+psycopg://wpsuite:secret@db-host:5432/wpsuite
+Connection precedence:
+  1. POSTGRES_USER + POSTGRES_PASSWORD + POSTGRES_DB (preferred) — the URL is
+     built with SQLAlchemy's URL.create(), which encodes the password for you,
+     so passwords with special characters (@ ! # : / …) need NO manual escaping.
+     Host/port default to POSTGRES_HOST=db / POSTGRES_PORT=5432.
+  2. DATABASE_URL — a full SQLAlchemy URL, if you'd rather supply one directly
+     (you must URL-encode any special characters in the password yourself).
+  3. Neither set → a local SQLite file, so the API runs anywhere without Postgres.
 
-If unset, it falls back to a local SQLite file so the API can be run and tested
-on any machine without Postgres. The schema is identical either way (SQLAlchemy
-handles the dialect differences).
+The schema is identical either way (SQLAlchemy handles dialect differences).
 """
 import os
-from sqlalchemy import create_engine
+from sqlalchemy import create_engine, URL
 from sqlalchemy.orm import sessionmaker, DeclarativeBase
 
-# Load a local .env if present (dev convenience). In production the DATABASE_URL
-# normally comes from the systemd unit's Environment / EnvironmentFile instead.
+# Load a local .env if present (dev convenience).
 try:
     from dotenv import load_dotenv
     load_dotenv()
 except Exception:
     pass
 
-DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///./wpsuite.db")
 
-# SQLite needs this flag to be used from FastAPI's threadpool; Postgres ignores it.
-connect_args = {"check_same_thread": False} if DATABASE_URL.startswith("sqlite") else {}
+def _resolve_url():
+    user = os.getenv("POSTGRES_USER")
+    pw = os.getenv("POSTGRES_PASSWORD")
+    dbname = os.getenv("POSTGRES_DB")
+    if user and pw and dbname:
+        # Build from components — password is encoded automatically.
+        return URL.create(
+            "postgresql+psycopg",
+            username=user, password=pw,
+            host=os.getenv("POSTGRES_HOST", "db"),
+            port=int(os.getenv("POSTGRES_PORT", "5432")),
+            database=dbname,
+        )
+    return os.getenv("DATABASE_URL") or "sqlite:///./wpsuite.db"
+
+
+DATABASE_URL = _resolve_url()
+
+# SQLite needs this flag from FastAPI's threadpool; Postgres ignores it.
+_is_sqlite = isinstance(DATABASE_URL, str) and DATABASE_URL.startswith("sqlite")
+connect_args = {"check_same_thread": False} if _is_sqlite else {}
 
 engine = create_engine(DATABASE_URL, connect_args=connect_args, pool_pre_ping=True, future=True)
 SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False, future=True)

server/seed_demo.py

@@ -0,0 +1,175 @@
+#!/usr/bin/env python3
+"""Seed a realistic DEMO project into the Work Package Suite database via the API.
+
+Creates one project, a complete SOP, and a spread of Work Packages that exercise
+the features and dashboard: an issued package, a gated (open-constraint) package,
+a multi-discipline master with its split instances (A/B/C), an overdue package,
+and an over-threshold draft. Use it to prove the SQL + Python layer end-to-end
+and to have data to inspect.
+
+USAGE
+  python3 server/seed_demo.py https://wp-suite.company.local --insecure
+  docker compose exec api python /app/server/seed_demo.py http://localhost:8000
+  python3 server/seed_demo.py https://wp-suite.company.local --clean   # remove DEMO-* projects
+
+IMPORTANT — what shows where:
+  * The DEMO **project** is API/SQL-backed, so it appears in the home-page
+    project picker immediately (proves the projects → SQL path in the UI).
+  * The DEMO **SOP and Work Packages** are written to SQL too, but the current
+    front end still reads SOPs/WPs from the browser (localStorage), so they will
+    NOT render in the WP Creator / Dashboard yet — that's the pending Phase 2
+    wiring. Verify them at the SQL/API layer instead:
+        python3 server/smoketest.py <url>            # automated end-to-end check
+        docker compose exec db psql -U wpsuite -d wpsuite \
+          -c "select number,subject,status from work_packages order by number;"
+"""
+import argparse
+import json
+import ssl
+import sys
+import urllib.error
+import urllib.request
+
+BASE = ""
+CTX = None
+DEMO_NUMBER = "DEMO-001"   # project number prefix used to find/clean demo data
+
+
+def call(method, path, body=None):
+    url = BASE + path
+    data = json.dumps(body).encode() if body is not None else None
+    req = urllib.request.Request(url, data=data, method=method,
+                                 headers={"Content-Type": "application/json", "Accept": "application/json"})
+    try:
+        with urllib.request.urlopen(req, context=CTX, timeout=20) as r:
+            raw = r.read().decode(); status = r.status
+    except urllib.error.HTTPError as e:
+        raw = e.read().decode(); status = e.code
+    try:
+        parsed = json.loads(raw) if raw else None
+    except ValueError:
+        parsed = raw
+    return status, parsed
+
+
+def constraints(open_names=()):
+    base = ["Safety & Permitting", "Quality Control / Inspection", "IFC Drawings & Specs",
+            "Schedule", "Materials (on site, bagged & tagged)", "Work Access & Laydown"]
+    return [{"name": n, "status": ("open" if n in open_names else "cleared"),
+             "comment": ("awaiting delivery" if n in open_names else "")} for n in base]
+
+
+def main():
+    global BASE, CTX
+    ap = argparse.ArgumentParser(description="Seed a demo project into the Work Package Suite")
+    ap.add_argument("base_url", nargs="?", default="http://localhost:8000",
+                    help="Site root, no /api (default: http://localhost:8000)")
+    ap.add_argument("--insecure", action="store_true", help="skip TLS verification")
+    ap.add_argument("--clean", action="store_true", help="delete existing DEMO-* projects and exit")
+    args = ap.parse_args()
+    BASE = args.base_url.rstrip("/")
+    if args.insecure:
+        CTX = ssl.create_default_context(); CTX.check_hostname = False; CTX.verify_mode = ssl.CERT_NONE
+
+    # health gate
+    try:
+        st, _ = call("GET", "/api/health")
+    except urllib.error.URLError as e:
+        print(f"ABORT: cannot reach {BASE}/api/health — {e}"); return 1
+    if st != 200:
+        print(f"ABORT: /api/health returned {st}"); return 1
+
+    # --clean: remove any prior demo projects (cascade removes their SOP + WPs)
+    st, projects = call("GET", "/api/projects")
+    demos = [p for p in (projects or []) if str(p.get("number", "")).startswith("DEMO-")]
+    if args.clean:
+        for p in demos:
+            call("DELETE", f"/api/projects/{p['id']}")
+        print(f"Removed {len(demos)} DEMO project(s).")
+        return 0
+    if demos:
+        print(f"Note: {len(demos)} DEMO project(s) already exist. Run with --clean first to avoid duplicates.\n")
+
+    # 1) Project
+    st, proj = call("POST", "/api/projects", {
+        "name": "DEMO — Micron INC (test data)", "number": DEMO_NUMBER,
+        "client": "Micron Technology, Inc.", "division": "Semiconductor",
+        "site": "Boise, ID — Fab", "created_by": "seed_demo"})
+    pid = proj["id"]
+    print(f"Project: {proj['name']}  ({pid})")
+
+    # 2) SOP (complete)
+    st, sop = call("POST", "/api/sops", {
+        "project_id": pid, "name": "DEMO SOP", "number": DEMO_NUMBER, "complete": True,
+        "created_by": "seed_demo",
+        "data": {"governance": {"woFormat": "WP##-[Sector]-[TYPE]",
+                                "disciplines": ["Mechanical", "Electrical", "Tech"],
+                                "discMode": "choice", "instanceSuffix": "letter",
+                                "woSize": "Standard — 3–5 days (≈40–80 hrs)", "sizeHoursMax": "80"}}})
+    sid = sop["id"]
+    print(f"SOP: complete ({sid})")
+
+    # 3) Work packages
+    def wp(number, subject, typ, status, data, parent_id=None):
+        body = {"project_id": pid, "sop_id": sid, "number": number, "subject": subject,
+                "type": typ, "status": status, "created_by": "seed_demo", "data": data}
+        if parent_id:
+            body["parent_id"] = parent_id
+        st, w = call("POST", "/api/wps", body)
+        print(f"  WP {number:<16} {status:<12} {subject}")
+        return w
+
+    # a) issued, all clear
+    wp("WP01-1P-CONDUIT", "1P horn/strobe conduit", "Conduit Install", "Issued",
+       {"disciplines": ["Electrical"], "hours": "40", "actualHrs": "",
+        "constraints": constraints(), "due": "2026-06-30"})
+    # b) gated — one open constraint, still Scheduled
+    wp("WP02-1P-WIRE", "1P wire pull", "Wire Pull", "Scheduled",
+       {"disciplines": ["Electrical"], "hours": "60", "actualHrs": "",
+        "constraints": constraints(open_names=["Materials (on site, bagged & tagged)"]), "due": "2026-07-04"})
+    # c) multi-discipline master + split instances (master excluded from metrics)
+    master_id = "wp_demo_master_chiller"
+    instances = [("WP03-CHILLER_Mech", "Mechanical", "A", "Mechanical Install", "In Progress"),
+                 ("WP03-CHILLER_Elec", "Electrical", "B", "Wire Pull", "Scheduled"),
+                 ("WP03-CHILLER_Tech", "Tech", "C", "Terminations", "Draft")]
+    child_ids = []
+    for num, disc, label, typ, status in instances:
+        cid = f"wp_demo_{label.lower()}"
+        child_ids.append(cid)
+        body = {"project_id": pid, "sop_id": sid, "parent_id": master_id, "id": cid,
+                "number": num, "subject": "Chiller skid — " + disc, "type": typ, "status": status,
+                "created_by": "seed_demo",
+                "data": {"disciplines": [disc], "instanceOf": master_id, "instanceLabel": label,
+                         "parentNumber": "WP03-CHILLER", "hours": "50", "actualHrs": "",
+                         "constraints": constraints(), "due": "2026-07-10"}}
+        call("POST", "/api/wps", body)
+        print(f"  WP {num:<16} {status:<12} (instance {label})")
+    wp("WP03-CHILLER", "Chiller skid (multi-discipline master)", "Mechanical Install", "Scheduled",
+       {"disciplines": ["Mechanical", "Electrical", "Tech"], "split": True, "children": child_ids,
+        "hours": "150", "constraints": constraints(), "due": "2026-07-10"})
+    call("POST", "/api/wps", {"project_id": pid, "sop_id": sid, "id": master_id,
+                              "number": "WP03-CHILLER", "subject": "Chiller skid (multi-discipline master)",
+                              "type": "Mechanical Install", "status": "Scheduled", "created_by": "seed_demo",
+                              "data": {"disciplines": ["Mechanical", "Electrical", "Tech"], "split": True,
+                                       "children": child_ids, "hours": "150", "constraints": constraints(),
+                                       "due": "2026-07-10"}})
+    # d) overdue, in progress
+    wp("WP04-2P-TERM", "2P terminations", "Terminations", "In Progress",
+       {"disciplines": ["Tech"], "hours": "30", "actualHrs": "20",
+        "constraints": constraints(), "due": "2026-06-10"})   # past today (2026-06-16) → overdue
+    # e) over-threshold draft (hours > 80)
+    wp("WP05-3P-PANEL", "3P panel install", "Panel Install", "Draft",
+       {"disciplines": ["Electrical"], "hours": "120", "actualHrs": "",
+        "constraints": constraints(open_names=["Schedule"]), "due": "2026-07-20"})
+
+    # metrics readback
+    st, m = call("GET", f"/api/wps/metrics?project_id={pid}")
+    print(f"\nMetrics (masters excluded): {m}")
+    print(f"\nDone. The DEMO project '{proj['name']}' now appears in the home-page picker.")
+    print("SOP/WPs are in SQL (see header note) — verify with smoketest.py or psql.")
+    print("Remove later with:  python3 server/seed_demo.py <url> --clean")
+    return 0
+
+
+if __name__ == "__main__":
+    sys.exit(main())

server/smoketest.py

@@ -0,0 +1,196 @@
+#!/usr/bin/env python3
+"""End-to-end smoke test for the Work Package Suite API + PostgreSQL.
+
+Exercises the real HTTP endpoints the way the front end does, proving that
+NGINX → FastAPI → PostgreSQL all work and that the Python logic (the AWP
+release gate, metrics, cascade delete) behaves. Stdlib only — no pip, no jq.
+
+USAGE
+  # Against the deployed site (through the NGINX proxy):
+  python3 server/smoketest.py https://wp-suite.company.local
+
+  # Self-signed / internal TLS cert? skip verification:
+  python3 server/smoketest.py https://wp-suite.company.local --insecure
+
+  # From inside the api container (hits FastAPI directly):
+  docker compose exec api python /app/server/smoketest.py http://localhost:8000
+
+  # Leave the demo project in the database so you can open it in the UI:
+  python3 server/smoketest.py https://wp-suite.company.local --keep
+
+The base URL is the SITE root (no /api). Default: http://localhost:8000
+Exit code 0 = all checks passed, 1 = one or more failed.
+"""
+import argparse
+import json
+import ssl
+import sys
+import urllib.error
+import urllib.request
+
+# ── tiny colored reporter ─────────────────────────────────────────────────────
+_PASS, _FAIL = [], []
+def _c(s, code):  # color if a TTY
+    return f"\033[{code}m{s}\033[0m" if sys.stdout.isatty() else s
+def ok(msg):   _PASS.append(msg); print("  " + _c("PASS", "32") + "  " + msg)
+def bad(msg):  _FAIL.append(msg); print("  " + _c("FAIL", "31") + "  " + msg)
+def check(name, cond, detail=""):
+    (ok if cond else bad)(name + (f"  ({detail})" if detail and not cond else ""))
+    return cond
+
+BASE = ""
+CTX = None
+
+def call(method, path, body=None):
+    """Returns (status_code, parsed_body). Never raises on HTTP status."""
+    url = BASE + path
+    data = json.dumps(body).encode() if body is not None else None
+    req = urllib.request.Request(
+        url, data=data, method=method,
+        headers={"Content-Type": "application/json", "Accept": "application/json"},
+    )
+    try:
+        with urllib.request.urlopen(req, context=CTX, timeout=20) as r:
+            raw = r.read().decode(); status = r.status
+    except urllib.error.HTTPError as e:
+        raw = e.read().decode(); status = e.code
+    try:
+        parsed = json.loads(raw) if raw else None
+    except ValueError:
+        parsed = raw
+    return status, parsed
+
+
+def main():
+    global BASE, CTX
+    ap = argparse.ArgumentParser(description="Work Package Suite API smoke test")
+    ap.add_argument("base_url", nargs="?", default="http://localhost:8000",
+                    help="Site root, no /api (default: http://localhost:8000)")
+    ap.add_argument("--insecure", action="store_true", help="skip TLS verification")
+    ap.add_argument("--keep", action="store_true", help="keep the demo project (don't delete)")
+    args = ap.parse_args()
+    BASE = args.base_url.rstrip("/")
+    if args.insecure:
+        CTX = ssl.create_default_context(); CTX.check_hostname = False; CTX.verify_mode = ssl.CERT_NONE
+
+    print(f"\nWork Package Suite — API smoke test\nTarget: {BASE}\n")
+
+    project_id = None
+    try:
+        # 1) Health — API is up and reachable through the proxy.
+        try:
+            st, body = call("GET", "/api/health")
+        except urllib.error.URLError as e:
+            print(_c("\nABORT", "31") + f"  cannot reach {BASE}/api/health — {e}\n"
+                  "  Is the stack up (docker compose ps) and the URL correct?\n")
+            return 1
+        check("health endpoint returns ok", st == 200 and isinstance(body, dict) and body.get("ok") is True,
+              f"status={st} body={body}")
+
+        # 2) Create a project (writes to the projects table).
+        st, proj = call("POST", "/api/projects", {
+            "name": "ZZ Smoke Test Project", "number": "SMOKE-001",
+            "client": "Internal QA", "division": "Controls", "site": "Test Host",
+            "created_by": "smoketest",
+        })
+        project_id = proj.get("id") if isinstance(proj, dict) else None
+        check("create project", st == 200 and bool(project_id), f"status={st}")
+
+        # 3) Read it back + confirm it's in the list (SQL round-trip).
+        st, got = call("GET", f"/api/projects/{project_id}")
+        check("fetch project by id", st == 200 and got.get("number") == "SMOKE-001", f"status={st}")
+        st, lst = call("GET", "/api/projects")
+        check("project appears in list", st == 200 and any(p.get("id") == project_id for p in lst),
+              f"status={st} count={len(lst) if isinstance(lst, list) else '?'}")
+
+        # 4) Create a SOP linked to the project.
+        st, sop = call("POST", "/api/sops", {
+            "project_id": project_id, "name": "ZZ Smoke SOP", "number": "SMOKE-001",
+            "complete": True, "created_by": "smoketest",
+            "data": {"governance": {"woFormat": "WP##-[Sector]-[TYPE]",
+                                    "disciplines": ["Mechanical", "Electrical", "Tech"]}},
+        })
+        sop_id = sop.get("id") if isinstance(sop, dict) else None
+        check("create SOP linked to project", st == 200 and bool(sop_id) and sop.get("project_id") == project_id,
+              f"status={st}")
+        st, latest = call("GET", f"/api/sops/latest?project_id={project_id}")
+        check("latest SOP for project resolves", st == 200 and latest.get("id") == sop_id, f"status={st}")
+
+        # 5) Create a Work Package with one OPEN constraint (not release-ready).
+        st, wp = call("POST", "/api/wps", {
+            "project_id": project_id, "sop_id": sop_id,
+            "number": "WP01-SMOKE", "subject": "Smoke test package", "type": "Conduit Install",
+            "status": "Scheduled", "created_by": "smoketest",
+            "data": {"disciplines": ["Electrical"], "hours": "40", "actualHrs": "",
+                     "constraints": [{"name": "Materials", "status": "open", "comment": "awaiting delivery"},
+                                     {"name": "Safety & Permitting", "status": "cleared", "comment": ""}]},
+        })
+        wp_id = wp.get("id") if isinstance(wp, dict) else None
+        check("create work package", st == 200 and bool(wp_id), f"status={st}")
+
+        # 6) The AWP release gate: issuing with an open constraint must be REFUSED (409).
+        st, refused = call("POST", f"/api/wps/{wp_id}/issue")
+        check("issue is blocked while a constraint is open (409)", st == 409, f"status={st} body={refused}")
+
+        # 7) Clear the constraint (upsert), then issue must SUCCEED (200, status Issued).
+        call("POST", "/api/wps", {
+            "id": wp_id, "project_id": project_id, "sop_id": sop_id,
+            "number": "WP01-SMOKE", "subject": "Smoke test package", "type": "Conduit Install",
+            "status": "Scheduled",
+            "data": {"disciplines": ["Electrical"], "hours": "40", "actualHrs": "",
+                     "constraints": [{"name": "Materials", "status": "cleared", "comment": ""},
+                                     {"name": "Safety & Permitting", "status": "cleared", "comment": ""}]},
+        })
+        st, issued = call("POST", f"/api/wps/{wp_id}/issue")
+        check("issue succeeds once constraints clear", st == 200 and issued.get("status") == "Issued",
+              f"status={st}")
+        check("issued_at timestamp is set", isinstance(issued, dict) and bool(issued.get("issued_at")))
+
+        # 8) Status transition endpoint.
+        st, prog = call("POST", f"/api/wps/{wp_id}/status", {"status": "In Progress"})
+        check("status transition endpoint", st == 200 and prog.get("status") == "In Progress", f"status={st}")
+
+        # 9) Metrics aggregate for the project (Python aggregation over SQL rows).
+        st, m = call("GET", f"/api/wps/metrics?project_id={project_id}")
+        check("metrics endpoint aggregates", st == 200 and isinstance(m, dict) and m.get("total", 0) >= 1,
+              f"status={st} metrics={m}")
+
+        # 10) Comment / feedback write + read.
+        st, c = call("POST", "/api/feedback", {
+            "type": "wp_review_comment", "name": "smoketest", "wp_id": wp_id,
+            "text": "SMOKE TEST comment — safe to delete", "page": "/smoketest"})
+        check("post comment/feedback", st == 200 and isinstance(c, dict) and bool(c.get("id")), f"status={st}")
+        st, comments = call("GET", f"/api/comments?wp_id={wp_id}")
+        check("comment is queryable", st == 200 and any("SMOKE TEST" in (x.get("text") or "") for x in comments),
+              f"status={st}")
+
+        # 11) WPs filter by project.
+        st, wps = call("GET", f"/api/wps?project_id={project_id}")
+        check("list WPs by project", st == 200 and any(w.get("id") == wp_id for w in wps), f"status={st}")
+
+    finally:
+        # 12) Cleanup — deleting the project cascades to its SOPs and WPs (FK ON DELETE CASCADE).
+        if project_id and not args.keep:
+            st, _ = call("DELETE", f"/api/projects/{project_id}")
+            check("delete project (cascades SOP + WPs)", st == 200, f"status={st}")
+            st, after = call("GET", f"/api/wps?project_id={project_id}")
+            check("WPs removed by cascade", st == 200 and isinstance(after, list) and len(after) == 0,
+                  f"status={st} remaining={after}")
+        elif project_id and args.keep:
+            print(f"\n  --keep: left demo project {project_id} ('ZZ Smoke Test Project') in the database.")
+
+    # ── summary ────────────────────────────────────────────────────────────────
+    total = len(_PASS) + len(_FAIL)
+    print(f"\n{'-'*52}\n{len(_PASS)}/{total} checks passed.")
+    if _FAIL:
+        print(_c(f"FAILED ({len(_FAIL)}):", "31"))
+        for f in _FAIL:
+            print("  - " + f)
+        print("\nResult: " + _c("FAIL", "31") + "\n")
+        return 1
+    print("\nResult: " + _c("ALL PASS — API, Python logic, and SQL are working.", "32") + "\n")
+    return 0
+
+
+if __name__ == "__main__":
+    sys.exit(main())