claudetools/projects/dataforth-dos/datasheet-pipeline/deploy_ui_feature.py

"""Deploy the api_uploaded_at + UI push feature to AD2 in the correct order:

  1. SFTP server_inventory.txt to AD2 (one-time, for back-population)
  2. SFTP migration SQL + back-populate script
  3. Run migration via psql
  4. Run back-populate
  5. Backup current production files (import.js already backed up earlier this
     session; backup routes/api.js + public/index.html + database/upload-to-api.js)
  6. SFTP updated upload-to-api.js, routes/api.js, public/index.html
  7. node --check on AD2
  8. Restart testdatadb service
  9. Verify
"""
import base64, paramiko, subprocess, time, yaml, os

pwd_raw = yaml.safe_load(subprocess.run(['sops','-d','D:/vault/clients/dataforth/ad2.sops.yaml'],
    capture_output=True, text=True, timeout=30, check=True).stdout)['credentials']['password']
PWD = pwd_raw  # vault now has clean password

LOCAL_IMPL = r'D:\claudetools\projects\dataforth-dos\datasheet-pipeline\implementation-upload'
REMOTE_DB  = 'C:/Shares/testdatadb/database'
REMOTE_API = 'C:/Shares/testdatadb/routes'
REMOTE_WEB = 'C:/Shares/testdatadb/public'
PROD_DIR   = 'C:/ProgramData/dataforth-uploader'
SERVER_INV_LOCAL = r'C:\Users\guru\AppData\Local\Temp\server_inventory.txt'

c = paramiko.SSHClient(); c.set_missing_host_key_policy(paramiko.AutoAddPolicy())
c.connect('192.168.0.6', username='sysadmin', password=PWD,
          timeout=30, banner_timeout=45, look_for_keys=False, allow_agent=False)

def ps(cmd, to=120):
    enc = base64.b64encode(cmd.encode('utf-16-le')).decode()
    _, o, e = c.exec_command(f'powershell -NoProfile -EncodedCommand {enc}', timeout=to)
    return o.read().decode('utf-8','replace'), e.read().decode('utf-8','replace')

print('[1] SFTP server_inventory.txt to AD2 (for back-population)')
sftp = c.open_sftp()
sftp.put(SERVER_INV_LOCAL, f'{PROD_DIR}/server_inventory.txt')
sftp.close()
out, _ = ps(f'$f = "{PROD_DIR.replace(chr(47),chr(92))}\\server_inventory.txt"; "bytes: $((Get-Item $f).Length)"; "lines: $((Get-Content $f).Count)"')
print(out.rstrip())

print('\n[2] SFTP migration SQL + back-populate script + new files')
sftp = c.open_sftp()
uploads = [
    (f'{LOCAL_IMPL}\\database\\migrate-add-api-uploaded.sql', f'{REMOTE_DB}/migrate-add-api-uploaded.sql'),
    (f'{LOCAL_IMPL}\\database\\back-populate-api-uploaded.js', f'{REMOTE_DB}/back-populate-api-uploaded.js'),
    (f'{LOCAL_IMPL}\\database\\upload-to-api.js',  f'{REMOTE_DB}/upload-to-api.js'),
    (f'{LOCAL_IMPL}\\routes\\api.js',              f'{REMOTE_API}/api.js'),
    (f'{LOCAL_IMPL}\\public\\index.html',          f'{REMOTE_WEB}/index.html'),
]
# Backups first
for _, remote_dst in uploads:
    if remote_dst.endswith('.sql') or remote_dst.endswith('back-populate-api-uploaded.js'):
        continue  # new file, no backup needed
    bak = remote_dst + f'.bak-{time.strftime("%Y%m%d-%H%M%S")}'
    try:
        with sftp.open(remote_dst, 'rb') as src, sftp.open(bak, 'wb') as dst:
            dst.write(src.read())
        print(f'  backed up {remote_dst} -> {bak}')
    except Exception as e:
        print(f'  backup skip {remote_dst}: {e}')
# Uploads
for local_src, remote_dst in uploads:
    sftp.put(local_src, remote_dst)
    print(f'  uploaded {local_src} -> {remote_dst}')
sftp.close()

print('\n[3] run migration via psql (env DATABASE_URL expected in service context; use psql -U testdatadb if set up)')
# check db.js to understand connection info
out, _ = ps(r'Get-Content "C:\Shares\testdatadb\database\db.js" | Select-String "host|user|database|port|connectionString" | Select -First 10 | Out-String')
print(out.rstrip())

print('\n[3b] run migration via psql using .env creds')
out, _ = ps(r'$env_file = "C:\Shares\testdatadb\.env"; if (Test-Path $env_file) { Get-Content $env_file } else { "no .env" }')
print(out.rstrip())

# Try discovering via the db.js defaults + running migration with Node (safer than psql here)
out, _ = ps(
    f'cd "{REMOTE_DB.replace("/","\\")}"; '
    r'& node -e "const db = require(''./db''); (async () => { '
    r'const sql = require(''fs'').readFileSync(''./migrate-add-api-uploaded.sql'', ''utf8''); '
    r'await db.execute(sql); console.log(''[MIG OK]''); '
    r'const c = await db.queryOne(\"SELECT COUNT(*) as c FROM information_schema.columns WHERE table_name=''test_records'' AND column_name=''api_uploaded_at''\"); '
    r'console.log(''column exists:'' + c.c); await db.close(); })().catch(e => { console.error(''[MIG FAIL]'', e.message); process.exit(1); });" 2>&1'
, to=60)
print(out.rstrip())

print('\n[4] run back-populate (batch 1000)')
out, _ = ps(
    f'cd "{REMOTE_DB.replace("/","\\")}"; '
    f'& node back-populate-api-uploaded.js --inventory "{PROD_DIR.replace(chr(47),chr(92))}\\server_inventory.txt" --batch 1000 2>&1'
, to=1200)
print(out.rstrip())

print('\n[5] node --check updated files')
out, _ = ps(
    f'cd "{REMOTE_DB.replace("/","\\")}"; & node --check upload-to-api.js; '
    f'cd "{REMOTE_API.replace("/","\\")}"; & node --check api.js; echo "[OK]"'
, to=60)
print(out.rstrip())

print('\n[6] restart testdatadb')
out, _ = ps('Restart-Service testdatadb; Start-Sleep 3; Get-Service testdatadb | Select Name,Status | Format-Table -AutoSize | Out-String', to=60)
print(out.rstrip())

print('\n[7] verify API')
out, _ = ps(
    r'try { $r = Invoke-WebRequest "http://localhost:3000/api/stats" -UseBasicParsing -TimeoutSec 15; "GET /api/stats HTTP $($r.StatusCode)" } catch { "GET /api/stats FAIL: $_" }; '
    r'try { $r = Invoke-WebRequest "http://localhost:3000/api/search?limit=1" -UseBasicParsing -TimeoutSec 15; "GET /api/search HTTP $($r.StatusCode)"; $j = $r.Content | ConvertFrom-Json; "first record keys: $($j.records[0].PSObject.Properties.Name -join '', '')" } catch { "GET /api/search FAIL: $_" }'
, to=30)
print(out.rstrip())

c.close()
print('\n[OK] deploy complete')