rank_projects

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.