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

Prevent table-creation race: gunicorn --preload
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>
2026-06-17 13:07:49 -05:00 · 2026-06-16 16:03:38 -07:00 · 2026-06-16 15:24:40 -07:00 · 2026-06-16 15:24:17 -07:00 · 2026-06-16 15:09:17 -07:00 · 2026-06-16 15:06:04 -07:00
8 changed files with 335 additions and 21 deletions
--- a/DEPLOYMENT.md
+++ b/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

--- a/4
+++ b/4
@@ -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"]
--- a/docker-compose.yml
+++ b/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:
--- a/html/admin.html
+++ b/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>
--- a/html/admin.js
+++ b/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(); }
--- a/html/index.html
+++ b/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>
--- a/nginx/conf.d/wp-suite.conf
+++ b/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;
--- a/server/db.py
+++ b/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)
Author	SHA1	Message	Date
Tim Shepardson	deaf13c724	fix server in wp-suite.conf so proxy works.	2026-06-17 13:07:49 -05:00
n.siegfried	a02f7ec511	Prevent table-creation race: gunicorn --preload 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>	2026-06-16 16:03:38 -07:00
n.siegfried	c010bc22a0	Update home footer to BTG / Pilot Use Only Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>	2026-06-16 15:24:40 -07:00
n.siegfried	66da5b708a	Fix API DB connection: build URL from POSTGRES_* (auto-encode password) The api crash-looped because DATABASE_URL had an un-encoded special-char password (@/!), so SQLAlchemy parsed part of the password as the host ("...@db" → name resolution failure). db.py now prefers building the connection from POSTGRES_USER/PASSWORD/DB via SQLAlchemy URL.create(), which encodes the password automatically — any password works with no manual escaping. DATABASE_URL remains an optional override (still must be hand-encoded if used). docker-compose now passes the POSTGRES_* vars to the api container; DEPLOYMENT.md updated (incl. a Portainer env-vars note). Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>	2026-06-16 15:24:17 -07:00
n.siegfried	1e31aa535e	Merge feat/admin-console into main Adds /admin.html — a passphrase-gated, in-site admin console for diagnostics and testing: API connectivity check, DB snapshot, browser smoke test, and demo-data seed/clean. Gate is SHA-256 obfuscation only (default passphrase "prime-admin"); restrict at the network/proxy for real protection. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>	2026-06-16 15:09:17 -07:00
n.siegfried	ca8c36a889	Add in-site Admin Console (gated diagnostics + tests) html/admin.html + admin.js — a passphrase-gated console served at /admin.html: - API connectivity check (clearly flags the /api/ 404 if the proxy isn't routing). - Database snapshot (project/SOP/WP/comment counts via the API). - End-to-end smoke test in the browser (mirrors smoketest.py: issue gate, status, metrics, comments) with self-cleanup. - Demo data: seed a DEMO project + clean DEMO-/SMOKE- projects. Gate is SHA-256-based (default passphrase "prime-admin"; documented how to change) — obfuscation only, not real auth; restrict at the network/proxy for real protection. Not linked from the main nav. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>	2026-06-16 15:06:04 -07:00
n.siegfried	39230adf07	Merge fix/round1-feedback into main Round-1 test feedback + verification tooling: - Constraints: fix custom constraints never appearing; free-text add + remove. - Sources: column headers; preset (hard-coded) data types, Add Source for extras. - Issuance strategy: tooltip + worked examples. - Remove "comment submitted" popups; keep commenter name. - WP: offer to issue when the last constraint clears; collapsible sections. - server/smoketest.py (end-to-end API/SQL check) and server/seed_demo.py (loadable demo project); DEPLOYMENT.md documents both. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>	2026-06-16 12:50:04 -07:00