shopdb/CLAUDE.md

ShopDB - Claude Code Instructions

Project Overview

ShopDB is a Classic ASP/VBScript web application for managing manufacturing shop floor infrastructure at GE Aerospace. It tracks machines, PCs, printers, and network devices.

Technology Stack

• Backend: Classic ASP (VBScript)
• Database: MySQL 5.6
• Frontend: Bootstrap 4.6, jQuery, DataTables
• Server: IIS on Windows VM (192.168.122.151:8080)
• Version Control: Gitea (localhost:3000)

Environment Access

Claude can access (dev environment):

• Dev MySQL database via Docker (localhost:3306)
• Dev IIS via Windows VM (192.168.122.151:8080)
• Local files and Gitea

Claude CANNOT access (production):

• Production IIS server
• Production IIS logs
• Production MySQL database
• Zabbix server (10.48.130.113)
• Employee database (wjf_employees) - used by displayprofile.asp (can update code, cannot test)

For production tasks, user must:

• Relay production data/logs to Claude
• Deploy files to production IIS
• Execute database changes on production

Key Architecture

Database Schema (Phase 2 - Current)

machines table (unified)
├── Equipment (machinetypeid 1-15 and 21-25, pctypeid IS NULL)
├── PCs (machinetypeid 33+, pctypeid IS NOT NULL)
└── Network Devices (machinetypeid 16-20)

printers table (separate)
communications table (all network interfaces)
machinerelationships table (PC↔equipment links)

Key Queries

-- All PCs
SELECT * FROM machines WHERE pctypeid IS NOT NULL;

-- All Equipment (excludes network devices and PCs)
SELECT * FROM machines WHERE pctypeid IS NULL
  AND machinetypeid NOT IN (16,17,18,19,20) AND machinetypeid < 33;

-- Network devices
SELECT * FROM machines WHERE machinetypeid IN (16,17,18,19,20);

File Structure

/home/camp/projects/windows/shopdb/
├── *.asp              # Main ASP pages
├── api.asp            # REST API for PowerShell
├── includes/          # Shared includes (header, footer, sql)
├── js/                # JavaScript files
├── css/               # Stylesheets
├── images/            # Images and icons
├── docs/              # Documentation
├── sql/               # SQL scripts
│   ├── migration_phase1/
│   ├── migration_phase2/
│   └── migration_phase3/
└── logs/              # Application logs

Log Locations

IIS Logs (Windows VM): /home/camp/projects/windows/logs/shopdb/

• Format: ex[YYMMDD].log (e.g., ex251201.log for Dec 1, 2025)
• These are synced from the Windows VM via Samba share
• Contains HTTP request logs with status codes and error messages

API Logs: Check logs/ folder in shopdb directory on IIS

Coding Standards

ASP/VBScript

1. Always use parameterized queries - Never concatenate user input into SQL
2. Use HTMLEncode for output - Prevent XSS attacks
3. Convert text fields to strings - Use & "" to avoid type mismatch errors
4. Use Option Explicit - Declare all variables

Example Safe Query

Dim cmd, rs
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = objConn
cmd.CommandText = "SELECT * FROM machines WHERE machineid = ?"
cmd.Parameters.Append cmd.CreateParameter("@id", 3, 1, , machineId)
Set rs = cmd.Execute()

Example Safe Output

' Always convert to string and encode
Dim hostname
hostname = ""
If NOT IsNull(rs("hostname")) Then hostname = rs("hostname") & ""
Response.Write(Server.HTMLEncode(hostname))

Common Tasks

Reading Files

• Main pages are in project root (*.asp)
• Includes are in /includes/
• Check docs/ for documentation

Making Changes

1. Read the file first
2. Use parameterized queries for any SQL
3. Test on dev server (192.168.122.151:8080)
4. Check IIS logs for errors

Database Access

docker exec -it dev-mysql mysql -u root -prootpassword shopdb

Important Notes

VBScript Limitations

• No IIf() function - use If-Then-Else instead
• No Try-Catch - use On Error Resume Next carefully
• Strings are 1-indexed, not 0-indexed

Column Name Gotchas

• address not ipaddress in communications table (but printers uses ipaddress)
• defaultgateway not gateway in communications table
• machinenotes not notes in machines table
• machineid not pcid for PCs in machines table
• commconfig and dncconfig tables (not pc_comm_config, pc_dnc_config)

PC Identification

PCs are in the machines table, identified by:

• pctypeid IS NOT NULL
• machinetypeid IN (33, 34, 35)

IIS 401 Error on New ASP Files

Problem: When creating new ASP files from scratch, they may return HTTP 200 on the first request but HTTP 401 Unauthorized on all subsequent requests.

Symptoms:

• First curl/browser request to new .asp file returns 200
• All following requests return 401 Unauthorized
• Existing files (like printerqrbatch.asp) work fine with consistent 200 responses
• Adding <%@ Language=VBScript %> directive alone does NOT fix it

Solution: Instead of creating new ASP files from scratch, copy an existing working ASP file to the new filename, then modify its contents:

# DON'T do this (creates file from scratch - will get 401 errors):
# Write tool to create newfile.asp

# DO this instead (copy existing working file):
cp printerqrbatch.asp newfile.asp
# Then use Edit tool to replace the content

Why this works: The exact root cause is unclear, but appears related to how IIS handles file metadata/permissions for newly created files vs. copied files. Copying preserves whatever attributes IIS needs to serve the file correctly.

Verified fix: Files created by copying printerqrbatch.asp consistently return 200 on multiple sequential requests.

API Endpoints

Base URL: http://192.168.122.151:8080/api.asp

Action	Method	Description
updateCompleteAsset	POST	PC data collection
getDashboardData	GET	Health check
updatePrinterMapping	POST	Printer assignments

External Integrations

Page	Purpose
printerlookup.asp?ip=x.x.x.x	Zabbix printer lookup by IP/FQDN

Zabbix API

• Config File: includes/zabbix.asp
• Used For: Printer supply levels, host monitoring
• Credentials: See secrets.md

Gitea (Version Control)

• URL: http://localhost:3000
• Repo: cproudlock/shopdb
• Credentials: See secrets.md

Testing

Run Form Tests

After making changes to ASP pages, run the test suite to verify all forms work:

./tests/test_forms.sh

Tests 41 endpoints: page loads, add forms, create operations (notifications, equipment, printers, subnets, applications, KB articles, vendors, models, network devices), and API endpoints.

Cleanup test data:

docker exec dev-mysql mysql -u root -prootpassword shopdb -e "DELETE FROM notifications WHERE notification LIKE 'AUTOTEST_%';"
# (similar for other tables - see tests/cleanup_test_data.sql)

Quick Reference

Start Dev Environment

~/start-dev-env.sh
~/status-dev-env.sh

Git Commands

cd /home/camp/projects/windows/shopdb
git status
git add .
git commit -m "message"
git push

Test a Page

curl -s http://192.168.122.151:8080/pagename.asp | head -50