| run_sqlA | 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.
|
| dataset_infoA | Per-dataset freshness, current period, row counts, and the key caveats. Interpretation rules: [freshness] Reporting snapshots publish three times yearly (Jan/May/Sep, YYYYMM ending 01/05/09). Reporting periods, source revisions and ingestion timestamps are different clocks. dataset_info is local-only metadata, not an upstream freshness check. Use its available periods and complete snapshot information when choosing a comparison. [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.
|
| list_agenciesC | Agency dictionary with live CPD presence + schedule-executor flag. Interpretation rules: [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. [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.
|
| list_categoriesA | 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. Interpretation rules: [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. [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.
|
| describe_fieldA | 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. Interpretation rules: [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. [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.
|
| describe_tableA | Schema catalog for every queryable DuckDB table (typed analytics tables, dims,
raw mirrors): live columns + types plus curated grain and keying notes. No arg →
catalog of all tables; table= (case-insensitive) → full detail. Use this
instead of DESCRIBE/SHOW (blocked in run_sql). Complements describe_field (official
field semantics for the 4 raw datasets). Interpretation rules: [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. [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.
|
| resolve_project_referenceA | Resolve any project identifier (PID, FMS ID, name, partial) → schedule+budget
matches bucketed by entity. Call this first for any named-project question. Interpretation rules: [resolution] Call resolve_project_reference first for named-project questions. Matches identify candidate entities, not funding relationships: inspect linked_budgets/linked_schedules in the detail tools. Preserve multiple candidates rather than silently selecting one. Follow pagination next_offset when more matches are needed; disclose truncation when reporting an incomplete list. [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. [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.
|
| get_project_scheduleA | Schedule (PID): phase, lifecycle, signed variance, reason; lists linked budgets;
forecast_past_due flags a forecast already past as of the PID's own latest report
(never true for completed/cancelled). Interpretation rules: [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. [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. [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. [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. [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. [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. [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.
|
| get_project_budgetA | Budget (FMS line): total, spend, variance; lists linked schedules. NB budget has
no 'completed' state; spend%=100 ≠ done. Interpretation rules: [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. [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. [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. [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. [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.
|
| get_project_historyA | Period-by-period history for ONE project. Schedule lens (pid=…): each period's
phase, forecast, signed variance, delay reason + a current-state header carrying
agency_project_name, cumulative variance, and forecast_past_due (see
get_project_schedule). Budget lens (fms_id=…, case-insensitive): each period's
budget/spend/signed variance per (managing_agency, fms_id) line, the line-keyed
fms_project_name, + the adopted original budget when recorded (adoption-only lines
return header-only). Provide exactly one of pid/fms_id; managing_agency scopes a
multi-agency FMS id to one line — otherwise ALL lines are listed. Interpretation rules: [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. [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. [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. [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. [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. [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.
|
| schedule_breakdownA | 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. Interpretation rules: [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. [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. [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. [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. [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.
|
| schedule_changesC | 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. Interpretation rules: [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. [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. [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. [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. [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.
|
| delay_reason_statsB | Distribution of reason-for-delay (only populated when variance>0). Defaults to current
period; pass scope='all_history' for lifetime. Carries a coverage block (delayed_total /
with_reason / without_reason — the denominator for the distribution, counting delayed rows
by bare variance_day>0). agency_role ('auto'|'sponsor'|'managing') picks owner vs builder
lens. Interpretation rules: [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. [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. [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.
|
| budget_breakdownA | 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. Interpretation rules: [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. [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.
|
| budget_changeA | Δ 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). An FMS id held
by several managing agencies is several distinct budget lines: the result then lists
per-line deltas (never a cross-agency sum); pass managing_agency to scope to one line. Interpretation rules: [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. [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. [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. [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.
|
| rank_projectsA | 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; schedule rows carry forecast_past_due — a forecast
already past as of the observation period (never true for completed/cancelled).
population_scope='latest_known' uses each entity's own latest observation;
'current' uses values at the selected complete snapshot. Rows include their
reporting_period and current-snapshot presence. category_scope='current' uses
current funding links; 'all_history' also matches removed funding links. Interpretation rules: [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. [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. [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. [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. [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. [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. [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.
|
| project_duration_statsA | Duration distribution between two ACTUAL milestones (requires both dates).
Only forward actual_design_start to actual_construction_end is supported.
Negative intervals are retained in invalid_intervals and excluded from statistics;
missing-date and invalid-order counts reconcile to the full latest-known population.
Optional group_by returns per-group stats instead of the citywide block. Interpretation rules: [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. [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. [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.
|
| project_portfolioA | 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. Rows carry forecast_past_due — a forecast already past as of
the observation period (never true for completed/cancelled). population_scope
'latest_known' (default) uses each PID's own latest row; 'current' uses values at
the selected complete snapshot. Rows expose reporting_period and current-snapshot
presence. category_scope 'current' uses current links; 'all_history' includes
former category links. Interpretation rules: [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. [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. [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. [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. [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. [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. [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.
|
