Skip to main content
Glama
WillHsiaoNYC

NYC Open Data Capital Projects MCP Server

by WillHsiaoNYC
OverviewSchemaSourceRelated ServersDiscussionsAdmin
Python
Local

run_sql

Execute read-only SELECT queries against local CPD DuckDB to analyze NYC capital project schedules, budgets, and lifecycle data; return inline rows or CSV/XLSX files.

Instructions

Run a read-only SELECT against the local CPD DuckDB.

output: 'inline' (default, rows capped) | 'csv' | 'xlsx' (writes a file, returns path).
PREFER the typed tables: latest_project_state (1 row/PID, borough+boroughs,
attributed_budget), schedule_history (PID x period), budget_history (snapshot rows
at the (fms_id, managing_agency) x period grain), original_budget (adopted first
budgets), lifetime_budget_variance (per-line lifetime, original_budget_source),
schedule_budget_link (PID<->FMS edges), pid_funding (per-PID link rollup),
cumulative_schedule_variance (per-PID lifetime days), fms_sponsor (fms_id -> owner),
fms_location (line-level borough/community board/name), agency_rollup_by_period,
category_dim, agency_dim, project_budget_fy, meta, data_build,
source_schedule_history (95tx-native observations, in_dashboard flag),
schedule_source_coverage (PID-level reconciliation and both cumulative bases).
RAW mirrors (raw_project_detail, raw_budget_fy, raw_budget_history,
raw_schedule_history) are all VARCHAR. Inline results echo latest_reporting_period
and may carry period_basis_note and truncation_note.

Interpretation rules:

  • [sql_usage] Prefer purpose-built tools for supported questions. For custom SQL, use describe_table for grain/keying and describe_field for field definitions, then prefer typed tables. Raw mirrors are VARCHAR and need casts. run_sql enforces read-only access, not correct analytical grain: the caller must prevent join fan-out and choose the period/population. Disclose truncated results; use CSV/XLSX for the full result when needed.

  • [relationships] "Project" is ambiguous: PID identifies a SCHEDULE; FMS ID identifies a BUDGET. They are MANY-TO-MANY: a budget can fund several PIDs and a PID can have several budgets. LIST ALL linked counterparts; never silently select one. One linked counterpart does not establish a 1:1 relationship in the reverse direction. A budget without a PID is normal before Design and for lines that do not require schedules; absence alone does not establish missing data. Source-only schedules can also lack dashboard funding links.

  • [grain] Schedule questions use PID; budget questions use the BUDGET LINE key (managing_agency, fms_id). The same FMS ID under different holders is distinct lines. Schedule history is PID x reporting_period; budget history is budget line x reporting_period; fiscal-year budgets also key on fiscal_year. The schedule source has no fms_id; budget sources have no pid. The combined source repeats rows across PID-budget links and location splits: deduplicate at the requested entity grain before counting or summing. Never compare budgets using fms_id alone.

  • [period_basis] Reporting-period basis: state the returned period for every count, total or ranking and both periods for comparisons. Period aggregates default to the latest complete snapshot, which may precede a partially published newer period. fms_location, fms_sponsor and lifetime_budget_variance are all-history dimensions without reporting_period: use them for enrichment or lifetime figures, never as a single period's inventory.

  • [population] Listings/rankings default to population_scope='latest_known' (each entity's own latest observation). Use population_scope='current' for the selected complete snapshot. State the population_scope and observation period; respect present_in_current_snapshot. Detail tools use latest-known state and each anchor's latest available link period; these links are not proof of presence in the current complete snapshot.

  • [agency_roles] Agency attribution is role-aware: an agency's projects mean its sponsor (owner) view, except DDC/DCAS/EDC default to managing (builder). State the returned agency_scope. managing_agency is the executor on schedules and the budget-holder on budgets; a budget-only holder is not a schedule executor. list_agencies exposes is_schedule_executor. For sponsor-scoped budget totals, use a semi-join to fms_sponsor; a value-bearing join can multiply lines. Co-owner totals can overlap and must not be added together.

  • [categories] Classify with the curated category_dim, not project-name searches: specific ten-year labels/FMS prefixes precede sponsor routing, then generic facility keywords and Other. Categories key on (managing_agency, fms_id); institution owner rules can use all-history ownership. Schedule category_scope='current' uses each PID's current funding links; 'all_history' includes former links. A PID can count in multiple categories, so schedule category counts are non-additive; each budget line has one category.

  • [funding_totals] attributed_budget counts a funding line fully on every PID it funds; it is not an allocated share or an independent project cost. Summing it across PIDs can double-count shared funding. For the portfolio's distinct funding total use summary.line_budget_total, which counts each (managing_agency, fms_id) once. State which budget basis is reported.

  • [budget_baseline] budget_variance is change from the previous reporting period; cumulative_budget_change is latest minus original. State which basis is used. original_budget prefers the adopted first budget, with first_snapshot as fallback: disclose original_budget_source. Adoption months are calendar months from a separate first-budget system, not reporting snapshots.

  • [schedule_coverage] Schedule totals and cumulative variance use dashboard-aligned schedule_history. source_schedule_history retains native observations absent from that population; schedule_source_coverage reconciles them. State the schedule universe and cumulative basis. Parenthesized phases are no-schedule reasons. Respect excluded variance artifacts and missing/invalid-duration counts; forward duration statistics exclude reversed dates.

  • [signed_values] Report neutral, SIGNED changes: "moved 45 days later" or "budget decreased $2M". Do not echo loaded terms in the answer. "slippage" means positive schedule change and "overrun" means positive budget growth; neither includes the decreasing side. Preserve the returned direction and metric basis.

  • [lifecycle] Lifecycle: Pre-Design -> Design -> Construction Procurement -> Construction -> Close-out. Schedule progression is reported from Design through Construction. Forecasts and most actual milestones can be suppressed outside those phases; actual_construction_end is the exception and means substantial completion. NULL milestones need not mean missing data. Budget spend%=100 does not prove completion. forecast_past_due is evaluated as of the observation period, not today's date.

  • [location] Location belongs to the BUDGET LINE, represented by fms_location. A PID inherits its funding-line boroughs: one specific borough takes precedence over Citywide, multiple specific boroughs yield 'Multiple', and only Citywide lines yield 'Citywide'. Preserve the boroughs list when several boroughs apply.

  • [presence] Presence in a selected reporting snapshot means reportable at that period. There is no separate active flag. Historical presence or a latest-known row does not prove presence in the current complete snapshot. A completed project can retain an open budget line for years; presence does not mean construction is in progress.

Input Schema

TableJSON Schema
NameRequiredDescriptionDefault
queryYes
outputNoinline

Output Schema

TableJSON Schema
NameRequiredDescriptionDefault
fileNo
rowsNo
truncatedNo
provenanceYes
interpretation_rulesYes

Schema Changelog

Changes observed during successful MCP inspections.

  1. Changed2 schema fields changedv0.1.1
    • addedInput schema / properties / output / enum
      Added value: +[
+  "inline",
+  "csv",
+  "xlsx"
+]
    • changedOutput schema / (root)
      Previous value: -nullNew value: +{
+  "$defs": {
+    "InterpretationRule": {
+      "properties": {
+        "id": {
+          "description": "Stable identifier of the domain rule.",
+          "title": "Id",
+          "type": "string"
+        },
+        "text": {
+          "description": "Guidance for interpreting and reporting this tool's result.",
+          "title": "Text",
+          "type": "string"
+        }
+      },
+      "required": [
+        "id",
+        "text"
+      ],
+      "title": "InterpretationRule",
+      "type": "object"
+    }
+  },
+  "additionalProperties": true,
+  "properties": {
+    "file": {
+      "anyOf": [
+        {
+          "type": "string"
+        },
+        {
+          "type": "null"
+        }
+      ],
+      "default": null,
+      "title": "File"
+    },
+    "interpretation_rules": {
+      "items": {
+        "$ref": "#/$defs/InterpretationRule"
+      },
+      "title": "Interpretation Rules",
+      "type": "array"
+    },
+    "provenance": {
+      "additionalProperties": true,
+      "title": "Provenance",
+      "type": "object"
+    },
+    "rows": {
+      "anyOf": [
+        {
+          "items": {
+            "additionalProperties": true,
+            "type": "object"
+          },
+          "type": "array"
+        },
+        {
+          "type": "null"
+        }
+      ],
+      "default": null,
+      "title": "Rows"
+    },
+    "truncated": {
+      "anyOf": [
+        {
+          "type": "boolean"
+        },
+        {
+          "type": "null"
+        }
+      ],
+      "default": null,
+      "title": "Truncated"
+    }
+  },
+  "required": [
+    "provenance",
+    "interpretation_rules"
+  ],
+  "title": "SQLResult",
+  "type": "object"
+}
  2. First observedv0.1.0

TDQS

A4.5/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations (readOnlyHint=false, idempotent=false, destructive=false) are consistent with the disclosed side effect that 'csv'/'xlsx' 'writes a file, returns path'; the description reconciles the 'read-only SELECT' claim with that file write rather than hiding it. It also adds behavior annotations do not carry: rows are capped inline, truncation_note is echoed, latest_reporting_period/period_basis_note may be returned, and results can be truncated so CSV/XLSX should be used for full output.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness3/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Front-loaded correctly (purpose, then output modes, then preferred tables, then rules), but the body is a very large wall of text. Long stretches on lifecycle phases, location inheritance, presence semantics and category rules are domain knowledge that applies to many sibling tools, not to invoking run_sql, and dilute the actionable SQL-authoring guidance.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

An output schema exists, so return-value documentation is not needed, and the description instead concentrates on the things an agent cannot infer: table grains, many-to-many PID/FMS linkage, period and population bases, funding double-counting, and the requirement to disclose truncation, population_scope and period basis. For a raw-SQL escape hatch this is about as complete as it can be.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 0%, so the description must carry both params. It fully explains the `output` enum (inline default with capped rows, csv, xlsx writes a file and returns a path), which is the one param with non-obvious behavior. The `query` param is a raw SQL string, so there is little semantic surface left, but the description compensates with grain/cast guidance ('Raw mirrors are all VARCHAR').

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

Opens with a specific verb+resource+constraint: 'Run a read-only SELECT against the local CPD DuckDB.' It also distinguishes itself from the many siblings by stating 'Prefer purpose-built tools for supported questions' and naming describe_table/describe_field as the SQL-authoring helpers, so an agent can tell it apart from get_project_budget, budget_breakdown, etc.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines5/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

Gives explicit routing rules: prefer purpose-built tools for supported questions, fall back to run_sql for 'custom SQL', consult describe_table for grain/keying and describe_field for definitions, and prefer typed tables over RAW VARCHAR mirrors. It even states the boundary of its own guarantee ('enforces read-only access, not correct analytical grain').

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Deploy Server

Other Tools