Answer engineering-portfolio review tasks that query a shared read-only work-items API/SQL environment and must return one strict JSON object matching a supplied answer_template.json. Covers the recurring task families: portfolio-mix reviews (count-based category mix vs. a target, gaps, rebalance), SLA-aging audits (primary vs. duplicate work, overdue/aging buckets, breach rate, hotspots), and release-readiness assessments (milestone completion, gating items, blockers, dependency chains, ship decision). Use whenever a task references work items, mix targets, SLA policy, releases, milestones, blockers, or dependencies, an X-Env-Token query token, and stale "mirror" or "legacy" fields to ignore.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Answer engineering-portfolio review tasks that query a shared read-only work-items API/SQL environment and must return one strict JSON object matching a supplied answer_template.json. Covers the recurring task families: portfolio-mix reviews (count-based category mix vs. a target, gaps, rebalance), SLA-aging audits (primary vs. duplicate work, overdue/aging buckets, breach rate, hotspots), and release-readiness assessments (milestone completion, gating items, blockers, dependency chains, ship decision). Use whenever a task references work items, mix targets, SLA policy, releases, milestones, blockers, or dependencies, an X-Env-Token query token, and stale "mirror" or "legacy" fields to ignore.
Portfolio Environment Analysis
A single shared environment (a small REST API backed by a read-only SQLite DB) holds an
engineering portfolio: work items plus mix targets, SLA policy, releases, milestones,
dependencies, and blockers. Tasks ask you to compute one deterministic JSON answer
about that data and return nothing but the JSON.
The scope, quarter, teams, dates, and answer shape change per task, but the data model and
the analysis rules below are stable. Treat this file as the operating manual; read the
references/ files for the exhaustive field/enum tables and per-family recipes.
0. Golden rules (do these on every task)
Read the task's own answer_template.json (each task ships it at
input/payloads/answer_template.json). It is the contract: keys, enums, const
values, additionalProperties:false, ordering hints, and rounding all come from it.
When this manual and the template disagree, the template wins.
Get access from environment_access.md, not from memory. It gives the base URL
(prompts show it as <TASK_ENV_BASE_URL>), the endpoint list, and the query token.
Use the network as that file allows.
only
Trust authoritative fields; discard stale/legacy ones.status is truth;
mirror_status is a stale mirror — ignore it. legacy_category is a stale export
field — never categorize from it. stale-export / papertrail labels mark
export-noise records.
Separate primary work from noise before counting. Exclude duplicates and cancelled
records (and task-named "distractors") from the primary population; report them where
the template asks.
Compute with full precision; round only at output. Verify every count two ways
(SQL aggregate + a re-count in code). Percentages sum to ~100.
Emit exactly one JSON object, no prose, no markdown fences. Include precisely the
template's required keys (no extras — schemas use additionalProperties:false).
1. Environment access
Base URL: from environment_access.md. Read endpoints with GET. For anything needing
filtering/joins/aggregation, use POST /api/query with header
X-Env-Token: <token from environment_access.md> and body {"sql":"<read-only SELECT>"}.
The query endpoint runs SQL over these tables (names are the API resources):
work_items, mix_targets, sla_policy, releases, milestones, dependencies, blockers.
It returns {"columns":[...],"rows":[[...]],"row_count":N,"truncated":bool}.
SQL is convenient but the hygiene/classification rules below are not encoded in the DB —
you must apply them yourself. A pragmatic pattern: pull the candidate rows with SQL, then
classify/filter/aggregate in a script so the logic is auditable.
A ready helper is in scripts/query.py (reads base URL + token from an
environment_access.md you point it at). It is optional; plain curl/requests are fine.
2. Record hygiene — authoritative vs. stale/distractor
work_items fields and how to treat them (full table in references/data-model.md):
status — authoritative lifecycle state. Completed/closed = {Closed, Done, Verified, Deployed} (these carry a closed_at). Open/non-complete = {Backlog, In Progress, Review, Reopened} (closed_at is null). Excluded = {Duplicate, Cancelled}.
mirror_status — STALE mirror of status. Ignore entirely.
legacy_category — STALE legacy/export category. Ignore for classification.
duplicate_of — if non-null, the record duplicates / points at another item. A record
is a duplicate when status == "Duplicate" OR duplicate_of is non-null; its canonical
target is duplicate_of. Exclude from primary; report in duplicate clusters when asked.
status == "Cancelled" — excluded as cancelled.
labels — supporting signal; stale-export and papertrail flag export noise /
distractor records.
owner — may be null → render as UNASSIGNED (or list under missing-owner ids) per
the template.
id — WI-24024-<optional single UPPERCASE letter><3 digits>; plain, P…, and S…
variants all exist and are all valid ids. Never include/exclude by prefix — filter on
fields. (The P/S pools are where most duplicates/distractors live, but confirm by field.)
Primary population = records that are neither duplicates nor cancelled. Layer the
task-specific filters (scope, quarter, category, release, window) on top of that.
3. Portfolio category classification
Four categories, each item lands in exactly one: NewFeature, TechDebt, Reliability, Security. The authoritative signal is work_type:
work_type
category
Feature, Enhancement
NewFeature
Refactor, Chore, Dependency
TechDebt
Reliability, Incident, Bug
Reliability
Security, Compliance
Security
"Resolve conflicting type, label, and title signals" means: work_type wins.labels/title only corroborate; legacy_category is ignored. This map is total for the
observed work_type values.
Fallback only if work_type is missing/unmapped: pick the highest-priority category that has
a matching labels/title keyword, priority Security > Reliability > TechDebt >
NewFeature; if none match, default NewFeature. Keyword sets are in
references/classification.md.
4. Scope resolution
Quarter is derived from closed_at (e.g. 2025-Q4 = 2025-10-01…2025-12-31), unless the
template says otherwise.
Portfolio-mix in-scope item = team ∈ scope.teams AND product_area ∈ scope.product_areas AND closed in the target quarter AND primary AND completed.
Target mix = the mix_targets row whose scope_id equals the task's scope id. Fetch
it at runtime — never hardcode target percentages. Its *_pct values are fractions in
0–1; multiply by 100 for percentage points.
SLA-aging in-scope item = primary item whose team ∈ scope.teams and whose category
(per §3) ∈ scope.categories, evaluated at the given as_of date within the stated recent
closed window.
Release in-scope item = work item with release_id == <release> (and/or a milestone_id
belonging to that release).
5. Metric formulas
Percentages are percentage points rounded to 1 decimal; rates/scores rounded to 3
decimals. Compute with full precision, round last.
gap_pct(cat) = actual_pct - target_pct; under-invested = gap_pct < 0, ordered most
negative → least negative.
Follow-up / recommended action: if any negative gap → action REBALANCE_CAPACITY, primary
category = largest negative gap (rationale LARGEST_NEGATIVE_GAP); if no negative gaps →
MAINTAIN_CURRENT_MIX / NO_NEGATIVE_GAPS; genuine data conflict →
INVESTIGATE_DATA_QUALITY / DATA_CONFLICT. Use the exact enum values the template lists.
SLA: the due date is the authoritative due_at field (do not recompute it from
sla_policy; sla_policy gives days_to_due per severity for reference only). An overdue
primary item is a primary SLA item still unresolved as of the date with due_at before the
as-of date. breach_rate = overdue_primary_count / included_primary_count (3 decimals).
Aging days = as_of − due_at (bucket per the template's boundaries). Hotspot = the
(team, owner|UNASSIGNED) pair with the most overdue primary items.
Release readiness: for each milestone, primary_total = primary release items with that
milestone_id, complete_primary = those in a completed state, completion_pct (1 dec).
readiness_score = total complete primary / total primary denominator (3 dec).
gating_work_item_ids = non-complete primary release items. blocker_cause_counts = count
of unresolved (resolved_at is null) high-impact (severity ∈ {High, Critical})
blockers for the release, keyed by exact cause string. critical_dependency_chains =
ordered blocked_id → … → depends_on_id paths (from dependencies) ending at a non-complete
dependency. ship_decision ∈ {SHIP, SHIP_WITH_WATCH, NO_SHIP} from readiness + unresolved
high-impact blockers + gating items — honor any thresholds the task states; otherwise use the
default heuristic in references/task-recipes.md.
6. Ordering, precision, and output
Always obey the template's stated order. It varies: some fields are "sort alphabetically"
/ "lexicographically ascending", others fix an explicit order (e.g. a specific team ordering).
Common defaults: id lists lexicographic ascending; included mix ids by closed_at asc then
id asc; duplicate clusters sorted by primary_id with duplicate_ids lexicographic.
Respect every const/enum exactly (e.g. a flag like
ignored_mirror_status_and_legacy_category is const:true — set it true, having actually
ignored those fields).
Self-check before emitting: category percentages sum to ≈100; every included id is
unique and matches the id pattern; excluded ids don't appear in included lists; counts
reconcile between SQL and code; JSON has exactly the required keys and validates against the
template.
