311 triage: route every request to the right system

Every 15 minutes

• Every 15 minutes (workspace time zone)

Then: Changed 311 requests

Changed 311 requests

• Reads Service requests from Sentra 311
• Only records changed since the last good run
• At most 1,000 records per run

Then: Clean and check

Clean and check

• description: collapse
• department: copy dept_code
• dept_code: translate with the value map Department codes
• address: required (else “No address”)
• lat: is number (else “No map location”)
• Records that fail a check leave by the Invalid output

Then: Already in the database?; Count by department; Invalid → Data quality alert

Count by department

• Counts records by dept_code

Then: Email operations

Already in the database?

• Looks for a row in service_requests where request_id matches {{id}}
• Saves it on the record as existing

Then: New request?

Data quality alert

• Hands the records to the Data quality alert (sub-flow) sub-flow

Email operations

• Emails [email protected]: “311 activity by department”
• One summary email

New request?

• Yes when existing is empty

Then: Yes → Crew work?; No → Name like the columns

Crew work?

• Yes when category is one of Pothole, Streetlight out, Tree down

Then: Yes → Shape as a crew visit; No → Code enforcement?

Name like the columns

• Shapes each record into 11 fields: request_id, number, category, department, status, address, description, created_at, updated_at, lat…

Then: Update the database copy

Shape as a crew visit

• Shapes each record into 6 fields: description, site_address, crew_id, priority, external_ref, scheduled_for

Then: Create Scheduler visit

Code enforcement?

• Yes when department is Code Enforcement

Then: Yes → Map attributes; No → Everything else to SFTP

Update the database copy

• Upserts rows in service_requests keyed on request_id
• Builds a map point in geom (SRID 4326)

Create Scheduler visit

• Creates Visits in Sentra Scheduler

Map attributes

• Shapes each record into 6 fields: ref, status, address, category, lat, lng

Then: Make map point

Everything else to SFTP

• Writes CSV file other-311-{{ $today }}.csv to outbox
• One file with every record

Make map point

• Builds an ArcGIS map point from lat and lng
• Drops records without coordinates

Then: Add to the code-case map

Add to the code-case map

• Creates Features in Esri ArcGIS feature layer