Civic Service Delivery Equity Scorecard Script Generator

Role: Principal Civic Data Scientist specializing in urban analytics and social equity benchmarking.

Context

• Department Under Review: {{target_agency_name}}
• Census Boundary File: {{census_tract_shapefile}}
• Analysis Timespan: {{service_ticket_timespan}}
• Social Vulnerability Indicators: {{demographic_equity_metrics}}
• Operational SLA Standard: {{sla_target_hours}}

Task

Generate a complete R analytics script using tidyverse, sf, and modelsummary to join municipal 311 service records with demographic census data, compute resolution SLA compliance across demographic tiers, and generate an empirical equity index score.

Method

1. Ingest municipal 311 service tickets for {{target_agency_name}} over the period defined by {{service_ticket_timespan}}.
2. Read and validate spatial boundary geometries from {{census_tract_shapefile}} using the sf library.
3. Execute a spatial point-in-polygon join to associate each service request with its corresponding census tract.
4. Compute ticket resolution duration in hours and evaluate compliance against {{sla_target_hours}}.
5. Merge tract-level demographic indicators from {{demographic_equity_metrics}} into the processed service data.
6. Construct a generalized linear model to test for statistically significant response time disparities across socioeconomic tiers.
7. Compute a normalized Service Delivery Equity Index (0-100 scale) aggregating baseline performance across tracts.
8. Format findings into clean summary tibbles and export the final scorecard to disk.

Constraints

• MUST write idiomatic, tidyverse-aligned R code adhering strictly to modern R style conventions.
• MUST NOT hardcode absolute local file paths in the script logic.
• Spatial coordinate reference systems (CRS) MUST be explicitly validated and aligned before spatial joins.
• Script must handle missing resolution timestamps by categorizing tickets as unresolved rather than dropping rows.

Output format

Structure the generated output into three dedicated sections:

1. Package Dependencies and Setup: Library import commands and CRS alignment parameters.
2. Complete R Analytics Script: Fully annotated R script containing data ingestion, spatial joins, modeling, and index calculation.
3. Metric Definition Reference: Explanations of the Equity Index formulas and linear model coefficients.

Self-review

• Verify that spatial joins explicitly check for and resolve invalid geometries using sf functions.
• Ensure that all 5 variables are meaningfully utilized in the generated script.
• Confirm that statistical disparity tests properly control for request volume variations across tracts.