Imported from washingtoneimae-dot/agent (skills-library/software-development/python-stdlib-web-app/SKILL.md). Install upstream with npx skills add washingtoneimae-dot/agent --skill python-stdlib-web-app. Copyright stays with the author.

Python Stdlib Web App

Build full-stack admin dashboards with zero dependencies. Uses ThreadingHTTPServer, sqlite3, and raw zipfile+xml.etree.ElementTree for Excel generation.

Triggers

• User asks to build a web dashboard/admin panel
• User asks to generate Excel files without openpyxl or xlsxwriter
• User asks for a Python web app that runs on both Linux and Windows without pip
• User asks for a CRUD app with SQLite backend
• User asks to email via CLI tool (Himalaya, sendmail, etc.)

Architecture

Browser ──► ThreadingHTTPServer (:PORT)
                │
        ┌───────┴────────┐
        │                │
    dashboard.html    do_POST()
    dashboard.js      (routes dict)
        │                │
        └───────┬────────┘
            sqlite3 DB

Key Patterns

Server Setup

from http.server import ThreadingHTTPServer, BaseHTTPRequestHandler
port = int(os.environ.get('APP_PORT', '9150'))
server = ThreadingHTTPServer(('127.0.0.1', port), Handler)
server.serve_forever()

Route Dispatching (do_POST)

routes = {
    '/add_member': lambda: self._add_member(body),
    '/edit_member': lambda: self._edit_member(body),
}
handler = routes.get(self.path)
if handler:
    self._json(handler())

Binary File Serving (do_GET)

def _binary(self, data, filename):
    self.send_response(200)
    self.send_header('Content-Type', 'application/zip')
    self.send_header('Content-Disposition', f'attachment; filename="{filename}"')
    self.send_header('Content-Length', str(len(data)))
    self.end_headers()
    self.wfile.write(data)

Auto-Schema Creation

Ship without a .db file — the database is created on first request. Useful for distribution where users might not copy the DB.

def _ensure_schema(conn):
    conn.executescript('''
        CREATE TABLE IF NOT EXISTS members (...);
        CREATE TABLE IF NOT EXISTS transactions (...);
    ''')
    conn.commit()

def _connect(self):
    conn = sqlite3.connect(DB, timeout=10)
    _ensure_schema(conn)
    return conn

Background Thread Pattern

For long-running operations (emailing 200 members), return immediately and process in a daemon thread to avoid HTTP timeout:

def _send_all(self):
    import threading
    def work():
        for m in members:
            # ... send email ...
            pass
    t = threading.Thread(target=work, daemon=True)
    t.start()
    return {'status': 'started', 'message': f'Sending to {len(members)} members'}

Never block the HTTP handler for batch operations that take >30s.

Server Shutdown

def _shutdown(self):
    import threading
    t = threading.Thread(target=lambda: (
        self._json({'status': 'ok', 'message': 'Shutting down'}),
        os._exit(0)
    ), daemon=True)
    t.start()

Use os._exit(0) (not sys.exit()) because sys.exit() only raises SystemExit which ThreadingHTTPServer catches and continues running.

Download ZIP Generation

Serve dynamically-generated ZIP files via GET:

def _download_all(self):
    import io, zipfile
    buf = io.BytesIO()
    with zipfile.ZipFile(buf, 'w', zipfile.ZIP_DEFLATED) as z:
        for m in members:
            z.write(xlsx_path, f'{safe_name}_statement.xlsx')
    self._binary(buf.getvalue(), 'statements.zip')

Email via CLI Tool (Himalaya)

Send emails by piping raw MIME to a CLI mailer. No SMTP libraries needed.

import subprocess
sname, semail = get_sender()
raw = f'From: {sname} <{semail}>\r\nTo: {to}\r\nSubject: {subj}\r\nMIME-Version: 1.0\r\nContent-Type: text/html; charset=UTF-8\r\n\r\n{html}'
r = subprocess.run(['himalaya', 'message', 'send'], input=raw, text=True, capture_output=True, timeout=30)
if r.returncode == 0:
    return {'status': 'ok', 'sent': True}
else:
    return {'status': 'error', 'message': r.stderr}

Always check r.returncode — subprocess.run() does NOT raise on non-zero exit by default.

For attachments (multipart MIME with base64):

import base64
xlsx_b64 = base64.b64encode(open(xlsx_path, 'rb').read()).decode()
mime = f'''From: {sname} <{semail}>
To: {to}
Subject: {subj}
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=_boundary_"

--=_boundary_
Content-Type: text/html; charset=UTF-8

{html}
--=_boundary_
Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Content-Disposition: attachment; filename="statement.xlsx"
Content-Transfer-Encoding: base64

{xlsx_b64}
--=_boundary_--'''

Excel Generation (No Libraries)

Generate .xlsx files using zipfile + raw XML.

def generate_xlsx(member_name, member_number, rows, summary, output_path):
    with zipfile.ZipFile(buf, 'w', zipfile.ZIP_DEFLATED) as z:
        z.writestr('[Content_Types].xml', ...)
        z.writestr('xl/workbook.xml', ...)
        z.writestr('xl/worksheets/sheet1.xml', sheet_xml)
        z.writestr('xl/sharedStrings.xml', si_xml)
        z.writestr('xl/styles.xml', styles_xml)

Required files in xlsx:

• [Content_Types].xml — MIME types for each part
• _rels/.rels — relationship links
• xl/workbook.xml — workbook definition
• xl/_rels/workbook.xml.rels — workbook relationships
• xl/worksheets/sheet1.xml — actual data
• xl/sharedStrings.xml — shared string table
• xl/styles.xml — number formats, fonts, etc.

Number Formatting (Commas)

<numFmt numFmtId="164" formatCode="#,##0.00"/>

Apply with s="1" on the cell: <c r="I10" s="1"><v>348184.22</v></c>

Template Designer Workflow

Separate the HTML from Python so a designer can edit visuals without touching backend code:

1. Extract HTML from return f'''...''' into dashboard_template.html
2. Replace f-string variables with explicit .replace() calls
3. Serve the JS separately via /dashboard.js GET endpoint
4. Designer edits dashboard_template.html and dashboard.js — keeps {placeholders} intact

Placeholder Substitution

template = template.replace('{member_opts}', member_opts)
template = template.replace('{member_table}', member_table)

Critical: f.read() returns raw text — {placeholders} are NOT auto-substituted like an f-string. Every placeholder needs an explicit .replace().

Serving Static Files (JS)

Dont serve JS through _html() because that sets Content-Type: text/html and browsers may refuse to execute it.

# WRONG
self._html(f.read(), 200)

# RIGHT
js = f.read()
self.send_response(200)
self.send_header('Content-Type', 'application/javascript')
self.send_header('Content-Length', str(len(js.encode())))
self.end_headers()
self.wfile.write(js.encode())

Template Designer Workflow - DOM Conflicts

When a designer creates inline script blocks, they can silently break backend-generated event handlers.

Common conflict: a script that re-wraps table cells with new a tags, overwriting onclick handlers the Python code already generated:

// Designer script - DESTROYS backend onclick handlers
document.querySelectorAll('table tr').forEach(row => {
  const firstTd = row.querySelector('td:first-child');
  firstTd.innerHTML = '<a href=\"javascript:void(0)\">' + text + '</a>';
  // overwrites the onclick="viewTx(...)" the backend generated
});

How to prevent:

1. Keep all DOM manipulation for functional elements in dashboard.js, not inline script blocks
2. If the designer must add inline scripts, audit for innerHTML or outerHTML assignments that overwrite existing event handlers
3. Test every clickable element after integrating new template

Another common designer pattern to watch for: scroll-based visibility effects (3D card fade, parallax) that set opacity: 0 or scale: 0 on cards. When transaction data loads into a card below the viewport, it becomes invisible. Remove opacity/scale changes from scroll handlers or exclude the data card.

Input Validation & XSS Prevention

An admin dashboard that accepts user input (member names, settings values) needs defense against both broken data and malicious input.

Server-Side Validation

Validate numeric settings before saving — don't let "abc" crash downstream float() calls:

def _update_settings(self, body):
    if 'interest_rate' in body:
        try:
            rate = float(body['interest_rate'])
            if rate < 0 or rate > 100:
                return {'status': 'error', 'message': 'Must be 0-100'}
        except (ValueError, TypeError):
            return {'status': 'error', 'message': 'Must be a number'}

HTML Escaping (XSS)

User-entered names, emails, and settings values rendered in HTML must be escaped. Python stdlib has html.escape():

import html

def h(val):
    return html.escape(str(val)).replace("'", "'")

# Use when building HTML strings
member_opts += f'<option value="{m["id"]}">{h(m["name"])}</option>'

This prevents <script>alert(1)</script> from executing in the browser — it renders as <script>alert(1)</script> instead.

Client-Side Validation

Use HTML input types to prevent bad data before it hits the server:

<input type="number" step="0.1" min="0" max="100">

Safety Fallbacks in Compute Logic

When reading user-configurable settings in backend computation, protect against corrupt data:

try:
    rate = float(db_value) / 100.0
except (ValueError, TypeError):
    rate = 0.01  # safe default
if rate <= 0 or rate > 1:
    rate = 0.01  # clamp to sane range

Placeholder Substitution Note

When using .replace() for template placeholders, also escape the replacement values:

template = template.replace('{sender_name}',
    html.escape(settings.get('sender_name', 'Default')))

This ensures that even if a setting value contains HTML, it won't break the page.

Cross-Platform Notes

PyWebView / Native Desktop Packaging

When wrapping a stdlib web app in a native window with pywebview:

import webview
webview.create_window(title='My App', url='http://127.0.0.1:PORT/dashboard')
webview.start()

CRITICAL: pywebview (Edge WebView2) does not have the same rendering performance as a full Chrome browser. Glassmorphism CSS (backdrop-filter: blur()) over animated backgrounds causes severe input lag inside WebView2.

See references/pywebview-webview2-performance.md for the full fix — the short version:

1. Remove JS-driven parallax from background layers (keep CSS-only keyframes)
2. Add isolation: isolate + transform: translateZ(0) to all blurred cards
3. Debounce MutationObservers with requestAnimationFrame to prevent keystroke cascades

PyInstaller packaging: When freezing with PyInstaller, the template/JS files must be declared as datas in the .spec file and served from sys._MEIPASS at runtime:

if getattr(sys, 'frozen', False):
    BASE_DIR = sys._MEIPASS
else:
    BASE_DIR = os.path.dirname(os.path.abspath(__file__))
os.environ['SACCO_BASE_DIR'] = BASE_DIR
os.environ['SACCO_DATA_DIR'] = os.path.join(os.environ.get('APPDATA', '~'), 'MyApp')

User data (DB, evidence) goes to %APPDATA% so it survives app updates.

Evidence Upload / File Attachment

For transactions that need supporting documents (loan disbursement evidence, receipts, etc.):

Database

Add an evidence_path TEXT column to your transactions table.

Upload Endpoint

Accept base64-encoded file data via POST, save to an evidence/ subdirectory:

def _upload_evidence(self, body):
    tx_id = body.get('tx_id', '')
    b64_data = body.get('file_data', '')
    ev_dir = os.path.join(os.path.dirname(__file__) or '.', 'evidence')
    os.makedirs(ev_dir, exist_ok=True)
    filepath = os.path.join(ev_dir, f'{tx_id}.pdf')
    with open(filepath, 'wb') as f:
        f.write(base64.b64decode(b64_data))
    # Store RELATIVE path so it survives folder moves
    rel_path = f'evidence/{tx_id}.pdf'
    conn.execute('UPDATE transactions SET evidence_path=? WHERE id=?', (rel_path, tx_id))
    return {'status': 'ok', 'path': filepath}

Download Endpoint

Resolve relative paths at download time:

def _download_evidence(self):
    tx_id = parse_qs(urlparse(self.path).query)['tx_id'][0]
    row = conn.execute('SELECT evidence_path FROM transactions WHERE id=?', (tx_id,)).fetchone()
    ev_path = row['evidence_path']
    if not os.path.isabs(ev_path):
        ev_path = os.path.join(os.path.dirname(__file__) or '.', ev_path)
    with open(ev_path, 'rb') as f:
        data = f.read()
    self.send_response(200)
    self.send_header('Content-Type', 'application/pdf')
    self.send_header('Content-Disposition', f'attachment; filename="evidence_{tx_id}.pdf"')
    self.send_header('Content-Length', str(len(data)))
    self.end_headers()
    self.wfile.write(data)

Frontend: Conditional File Input

Show/hide a file input based on the selected transaction type:

<div id="evidence-upload" style="display:none;">
  <label>Evidence PDF (required for loan)</label>
  <input id="evidence-file" type="file" accept=".pdf">
</div>

// Show file input when loan_disbursement is selected
document.addEventListener('change', function(e){
  if(e.target && e.target.name === 'type' && e.target.value === 'loan_disbursement'){
    document.getElementById('evidence-upload').style.display = 'block';
  } else if(e.target && e.target.name === 'type'){
    document.getElementById('evidence-upload').style.display = 'none';
  }
});

// Read file as base64 and upload after transaction is created
function doSubmit(action, data){
  fetch('/'+action, {method:'POST', ...})
  .then(function(j){
    if(j.status === 'ok' && data.type === 'loan_disbursement' && j.tx_id){
      var fileInput = document.getElementById('evidence-file');
      if(fileInput && fileInput.files && fileInput.files[0]){
        var reader = new FileReader();
        reader.onload = function(e){
          var base64Data = e.target.result.split(',')[1];
          fetch('/upload_evidence', {method:'POST', body:JSON.stringify({tx_id:j.tx_id, file_data:base64Data})});
        };
        reader.readAsDataURL(fileInput.files[0]);
      }
    }
  });
}

Dynamic Filtering (Filtered Queries)

For transaction lists with filter-by-type, amount range, date range — see references/searchable-dropdown.md for the searchable member selector pattern, and references/date-handling.md for date input format handling and calendar year navigation.

For transaction lists with filter-by-type, amount range, date range:

def _member_tx(self, body):
    mid = body['member_id']
    query = 'SELECT * FROM transactions WHERE member_id=?'
    params = [mid]
    
    if body.get('filter_type'):
        query += ' AND type=?'
        params.append(body['filter_type'])
    if body.get('filter_amt_min'):
        query += ' AND amount>=?'
        params.append(float(body['filter_amt_min']))
    if body.get('filter_date_from'):
        query += ' AND date>=?'
        params.append(body['filter_date_from'])
    if body.get('filter_date_to'):
        query += ' AND date<=?'
        params.append(body['filter_date_to'])
    
    query += ' ORDER BY date DESC'
    txs = [dict(r) for r in conn.execute(query, params)]
    return {'html': rows_html, 'count': len(txs)}

Frontend: build filter data from form fields and POST as part of the body. Include a Clear Filters button that resets all fields and re-fetches.

Pre-Submission Validation (Client-Side)

Date Validation

Warn when dates are in the future:

const today = new Date().toISOString().split('T')[0];
if(action === 'add_tx' && data.date > today){
  if(!confirm('Transaction date is in the future. Continue anyway?')) return false;
}

Duplicate Check

Check for existing records before submitting (e.g., registration fee already paid):

if(action === 'add_tx' && data.type === 'registration'){
  fetch('/check_registration', {method:'POST', body:JSON.stringify({member_id:data.member_id})})
  .then(function(j){
    if(j.has_registration && !confirm('Member already has registration. Add another?')) return;
    doSubmit(action, data);
  });
  return false;
}

Server-side endpoint:

def _check_registration(self, body):
    count = conn.execute("SELECT COUNT(*) FROM transactions WHERE member_id=? AND type='registration'", (mid,)).fetchone()[0]
    return {'has_registration': count > 0}

Activity Log / Status Endpoint

Return aggregated DB stats as JSON for a collapsible dashboard panel:

def _get_activity(self):
    return {
        'last_transaction': conn.execute('SELECT MAX(created_at) FROM transactions').fetchone()[0] or 'Never',
        'total_members': conn.execute('SELECT COUNT(*) FROM members').fetchone()[0],
        'total_transactions': conn.execute('SELECT COUNT(*) FROM transactions').fetchone()[0],
        'total_statements_sent': conn.execute('SELECT COUNT(*) FROM statements_sent').fetchone()[0],
    }

Interest Lock Toggle

For financial systems where interest rates should only apply prospectively:

Database: computed_interest table (member_id, month, interest_rate, interest_amount, loan_balance). Setting: lock_past_months (yes/no).

In compute logic:

if lock_past_months:
    locked = conn.execute("SELECT interest_rate FROM computed_interest WHERE member_id=? AND month=?", (mid, month)).fetchone()
    effective_rate = locked[0] if locked else current_rate
else:
    effective_rate = current_rate

# Calculate interest with effective_rate
# If lock is ON and interest > 0: store it
if lock_past_months and interest > 0:
    conn.execute("INSERT OR REPLACE INTO computed_interest ...")

This allows changing the rate in settings while preserving past calculated values.

Critical Pitfalls

• f-string vs file read: When HTML moves from an f-string to a file, {variable} placeholders stop being auto-substituted. Add explicit .replace() calls.
• CSS braces in f-strings: Inside an f-string, CSS { must be {{ and } must be }}. In a separate template file, single braces work normally.
• JS in f-strings: Use a separate .js file served via GET. Avoids f-string escaping nightmares with { } in JavaScript.
• Escape drift in patch tool: When patching Python f-strings that contain HTML with escaped quotes, the patch tool's fuzzy matching can fail. Use write_file for large replacements.
• Windows encoding: Always open template files with encoding='utf-8' on Windows. The default cp1252 can't handle Unicode characters like ✕, ✓, ⚠.
• Windows batch CRLF: .bat and .ps1 files MUST have CRLF (0d 0a) line endings. Files written from WSL/Linux get LF (0a) which cmd.exe silently fails on. Safe fix (strips existing CR first to avoid double-CR): tr -d '\r' < file.bat | sed 's/$/\r/' > tmp && mv tmp file.bat. Prevent with .gitattributes: *.bat text eol=crlf / *.ps1 text eol=crlf. Check with xxd file.bat | head -1. Plain sed 's/$/\r/' without the tr -d '\r' step produces \r\r\n (double CR) on already-CRLF files, which cmd.exe also chokes on.
• Port checks over process checks: In Windows batch wrappers, use netstat -an | findstr ":%PORT% " to verify server startup, not tasklist. tasklist /fi "STATUS eq running" fails on non-English Windows (localized status values). netstat output is identical on every locale. See sacco-statement-automation skill → references/windows-batch-troubleshooting.md for the full debugging checklist.
• Port conflicts: Use environment variable for port so WSL and Windows versions can run simultaneously on different ports.
• Background vs sync sends: Long-running operations (200 emails) need threading to avoid HTTP timeout. Return {"status": "started"} immediately.
• subprocess returncode: Always check r.returncode — subprocess.run() does not raise on non-zero exit unless you pass check=True.
• Dead code after template migration: When migrating from inline f-string HTML to a template file, the old return f'''...''' block becomes dead code after return template. Python still parses it and raises SyntaxError. Delete the old block entirely.
• Wrong Content-Type for JS: Servers built with _html() helper set text/html on every response. JS served this way gets ignored by browsers. Serve JS with explicit send_header('Content-Type', 'application/javascript') before end_headers().
• Designer inline scripts masking backend handlers: Always audit designer-added scripts for DOM overwrites (innerHTML, outerHTML) that could erase backend-generated event handlers like onclick.
• Scroll-based card opacity: 3D scroll effects that set opacity on cards will hide dynamically-loaded data cards that sit below the viewport. Remove opacity changes from scroll handlers for data cards.
• Argument count drift in server-generated JS calls: When the server-side _dashboard_html() (or equivalent) builds HTML containing JavaScript function calls with inline arguments, the argument count must match the JS function signature exactly. If a field is added to the DB schema and the JS function signature is updated (e.g. adding a dob parameter), the server-side f-string generating the onclick must also pass that argument — otherwise it silently passes undefined. There is no compiler or linter that catches this mismatch because the JS call is embedded in a Python f-string. Pattern to prevent: (a) Define the argument list in ONE place (a helper function or template) so both the HTML generation and the JS function signature share the same data. (b) When adding a new field to the edit/create flow, check ALL call sites: the DB schema, the API _edit_member(), the JS editMember() function, AND the server-side onclick generation. (c) Or better: have the JS function fetch member data from the API endpoint instead of receiving inline arguments, eliminating the count-sync problem entirely.