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), agency_rollup_by_period,
category_dim, agency_dim, project_budget_fy, meta.
GRAIN RULES: budget comparisons key on (managing_agency, fms_id) — never fms_id
alone; sponsor-scoped budget sums use the semi-join
fms_id IN (SELECT fms_id FROM fms_sponsor WHERE sponsor_agency = ...) — a
value-bearing JOIN fans out across a line's agency rows and double-counts.
PERIOD BASIS: fms_location, fms_sponsor, lifetime_budget_variance are ALL-HISTORY
dimensions (latest row per line/owner, NO reporting_period column) — JOIN them to
enrich or for lifetime figures; do NOT COUNT them as a single period's inventory.
For a period count, aggregate raw_project_detail / schedule_history / budget_history
filtered by reporting_period. Every result echoes latest_reporting_period (and warns
via period_basis_note when a query counts an all-history dim) — state the basis.
RAW mirrors (raw_project_detail, raw_budget_fy, raw_budget_history,
raw_schedule_history) are all VARCHAR — cast as needed.

Per-dataset freshness, current period, row counts, and the key caveats.

Agency dictionary with live CPD presence + schedule-executor flag.

Program/facility categories (Library, Parks & Recreation, Sewer & Water, …) with budget-line counts and total budget. Use a category name as the category filter on rank_projects. Categories are derived from ten_year_plan_category + sponsor_agency + fms-id prefix — NOT managing_agency or project name.

Official field definitions (the NYC Open Data data dictionary): description, allowed values, primary/foreign key, limitations, notes. Filter by field (column name or display name) and/or dataset (RAW table name or socrata_id); omit both for the full dictionary.

Resolve any project identifier (PID, FMS ID, name, partial) → schedule+budget matches bucketed by entity. Call this first for any named-project question.

Schedule (PID): phase, lifecycle, signed variance, reason; lists linked budgets.

Budget (FMS line): total, spend, variance; lists linked schedules. NB budget has no 'completed' state; spend%=100 ≠ done.

Counts/averages of schedule metrics by agency/sponsor/borough/phase/category. agency scopes to one agency; agency_role ('auto'|'sponsor'|'managing') picks owner vs builder lens (auto: sponsor, except DDC/DCAS/EDC -> managing). Category grouping counts a PID once in EACH of its categories (non-additive). Report neutral, signed variance.

Newly completed (DR1) or newly delayed projects between two periods. agency scopes to one agency; agency_role ('auto'|'sponsor'|'managing') picks owner vs builder lens.

Distribution of reason-for-delay (only populated when variance>0). Defaults to current period; pass scope='all_history' for lifetime. agency_role ('auto'|'sponsor'|'managing') picks owner vs builder lens.

Total budget / spend by managing_agency or category, deduped on (fms_id, managing_agency). Category is line-grain (additive). Optional agency scopes to one agency; agency_role ('auto'|'sponsor'|'managing') picks owner vs builder lens. For richer cuts use run_sql.

Δ budget/spend for an agency ('agency:DEP') or FMS line ('fms:ABC') between two periods. For an agency target, agency_role ('auto'|'sponsor'|'managing') picks the lens; sponsor scope uses the latest-period owner set (as-of caveat in the result label).

Rank schedules (entity='schedule', rows=PIDs) or budgets (entity='budget', rows=FMS lines). rank_by must be NATIVE to entity; the other domain is filter-only. Echoes ranked_entity. Budget rank_by: total_budget | spend_to_date | spend_pct | budget_variance (last-period delta) | cumulative_budget_change (latest - original budget). Optional category (see list_categories) filters to one program type, e.g. 'Library'. Optional agency scopes to one agency; agency_role ('auto'|'sponsor'|'managing') picks the lens — 'auto' uses the owner (sponsor) view, except DDC/DCAS/EDC default to builder (managing). Echoes agency_scope.

Duration distribution between two ACTUAL milestones (requires both dates). Optional group_by ('managing_agency'|'borough'|'lifecycle_status') returns per-group stats instead of the citywide block.

Cross-section listing of projects (PIDs): filter by category (see list_categories), borough, community_board, lifecycle_status ('in_progress'|'completed'|'cancelled'), and/or agency (+agency_role lens); rows ordered by nearest completion date (NULLs last). Each row carries schedule state + attributed_budget; summary covers the FULL filtered set and reports BOTH budget bases (per-PID attributed vs deduped line_budget_total). Borough matches the PID's boroughs LIST, so multi-borough projects are found by any of their boroughs.