The GIS Division at our city needed a way for residents to report potholes, water leaks, graffiti, illegal dumping, code issues and similar concerns, and for staff to work those tickets without a third party 311 product. We built it on the ArcGIS Enterprise we already run, and we have now published the whole system on GitHub, generalized so any organization can stand it up.
https://community.esri.com/home/leaving?allowTrusted=1&target=https%3A%2F%2Fgithub.com%2Fbrianmcleer%2Freport-a-concern
This post is a technical walkthrough of how it is put together and why some of the choices were made. The repository has the full deployment runbook.
What is in the box
Two Experience Builder 1.21 widgets (public submit wizard and staff ticket manager), an enterprise geodatabase schema with attribute rules, a small Flask proxy that fronts the public feature service, seven Python scripts that run on Windows Task Scheduler, accessible HTML email templates, Task Scheduler job definitions, and docs. Nothing in the code is tied to our organization. Hostnames, email addresses, department names and item ids come from two git ignored config files and the widget settings panels.
The data model
Everything lives in one SQL Server enterprise geodatabase. The system of record is a point feature class, Tickets, with a GUID ticket id, a human readable ticket number, category as a subtype (13 codes), a subcategory text field whose coded value domain changes per subtype, status, assigned department, the boundary the point fell in, submitter contact fields, and two flags the scripts poll: notification_sent and survey_sent. Attachments are enabled on Tickets for photos.
Around it sit related tables joined by ticket id with composite relationship classes so a ticket delete cascades: Ticket_Comments (with an is_public flag and an email_sent flag), Ticket_Photos_Meta and Survey_Responses. Three lookup tables drive routing: Service_Boundaries (polygons with an is_active flag), Category_Boundary_Lookup (which categories are valid inside which boundary, plus a redirect message for the ones that are not) and Ticket_Routing (category, optional subcategory, boundary id, default department, department email). Notification_Log is an append only audit table that every script and the proxy write to.
Tickets, comments and survey responses are versioned and archived. The lookup tables and Notification_Log are not, on purpose, which matters below.
Submission flow, end to end
- The resident opens the public Experience Builder app and drops a pin. The submit widget queries Service_Boundaries client side to find which active polygons contain the point, then queries Category_Boundary_Lookup so the category list only shows what is valid there. A water ticket cannot be filed inside a neighboring water district, and the resident sees that district's contact info instead of a dead end.
- The widget posts an applyEdits to a same origin URL under the app, not to the feature service directly. IIS URL Rewrite (site level, so an Experience Builder republish cannot wipe it) forwards that path to a Flask proxy on localhost through Application Request Routing. A second site level rule returns 403 to any direct POST against the FeatureServer from outside.
- The proxy rate limits by client IP, rejects more than one feature per request, checks the geometry against a bounding box, validates category, description length, email and phone shape, and only then forwards to the FeatureServer on localhost. For photos it reads the first bytes and only accepts real JPEG, PNG, WebP and HEIC content regardless of the declared type, with a size cap. Every accepted and rejected request is written to Notification_Log with the client IP, so we have an audit trail without any file logging.
- Five Arcade attribute rules run on insert in the geodatabase: a geofence constraint (blocks out of bounds or invalid category submissions with a message from the lookup), a routing calculation that finds the boundary, tries a category plus subcategory plus boundary match in Ticket_Routing and falls back to the category catch all row to set the assigned department, a ticket number calculation pulling from a SQL sequence starting at 10000, and two validation constraints for required fields and business rules. Routing is data, not code: adding a department or changing who gets water tickets in one district is a row edit.
- Every five minutes the new ticket notifier script finds tickets with notification_sent = 0, emails the resident a confirmation with a status link and emails the routed department a work notice with a deep link into the manager app.
Staff side
The manager widget runs in an internal Experience Builder app behind Portal sign in. It reads the Tickets layer and the related tables from the web map, so no extra tokens or service URLs are configured. Staff filter by status, category and department badges, open a ticket, change status (a status change requires a comment, and backdating a resolved date writes an internal audit note), add public or internal comments, view photos in a lightbox and see the survey response if one came back. Public comments trigger an email to the resident through the comments mailer script. There is an Excel export with a summary sheet and the related records. A help guide is built into the widget following the pattern we use on all our widgets.
The scripts and two things we got wrong the first time
Seven scripts share one common module for config, logging, failure alerts, email, template rendering and audit writes, so each script is only its own logic: new ticket notifier, reassignment notifier (detects assigned_to changing and emails the new department), public comments mailer, survey invitation mailer (fires when a ticket reaches Resolved or Closed), Survey123 response pull (writes into Survey_Responses and emails the resolver when the resident asked for a follow up), a weekday directors report of overdue tickets by category, and a monthly all department report. Every failure in a run is collected and sent as one alert email at exit, and the process exits 1 so Task Scheduler shows it.
Two lessons are baked into the code. First, duplicate emails. The original notifier sent emails inside one batch wide edit session and committed the flag at the end. If that commit failed (a staff member had the ticket open in the manager, so a version conflict), every email had already gone out and every flag rolled back, so the next run sent them all again. Now the flag is committed per ticket in its own short edit operation before any email is sent. If the commit fails, no email, safe retry next run. If the commit succeeds and the send fails, it is logged and visible but never re sent.
Second, the audit table. Notification_Log started out versioned, and scripts writing it through cursors were both slow and dependent on Compress. It is now unregistered from versioning and every writer inserts with direct SQL and an explicit OBJECTID of MAX plus one with a short retry, so no write depends on the geodatabase row id counter and no two scripts collide. Related to this: the survey mailer once read the base table and did not see a resolution until the next Compress, so surveys went out a full cycle late. Every script now reads through the versioned feature class.
Survey loop
When a ticket is resolved the resident gets a link to a Survey123 form with the ticket id in the URL. The pull script reads new responses from ArcGIS Online, writes them into Survey_Responses in the geodatabase, and if the resident asked for a call back, emails the staff member who resolved the ticket (resolved from editor tracking, with a department fallback). The manager widget shows the rating and comments on the ticket.
Security and privacy
Anonymous users have read on the public view and can create through the proxy only. Site level IIS headers set HSTS, nosniff and frame options. The feature service restricts upload file types and size. The proxy never trusts the declared content type. Retention is handled by summary tables that keep counts by month, category and department with no ticket ids or free text, so old tickets with personal information can be purged on a schedule while the statistics survive.
Standing it up
The runbook in docs/deployment.md is the ordered list: build the schema with the arcpy script (dry run first), publish three services (public write, public read, staff), add the attribute rules, drop the two widgets into your-extensions and build the apps, put the IIS rules at the site level, stand up the proxy in a cloned ArcGIS Pro Python environment, fill in config.py and rac_secrets.py, import the Task Scheduler jobs. Every script ships with testing mode on, so nothing emails a real resident until you flip it. The troubleshooting doc is the symptom to cause to fix table we built while migrating the system between servers: the IIS HTML 500 that means the proxy is not listening, the 404 that means ARR is not installed, the CORS failure that means a widget URL is not same origin with the page, and the Windows Server 2025 Task Scheduler error that means the job is pointing at the base ArcGIS Pro Python instead of a clone.
Widget zips are attached to each GitHub release. Issues and pull requests are welcome on the repository.
