Portfolio Review Skill
Overview
This skill solves engineering portfolio review tasks that query a shared REST API
and produce structured JSON answers. Three task families are covered: portfolio
mix analysis, SLA aging audits, and release readiness assessments.
The API is accessed at a base URL supplied in the task prompt (typically as
<TASK_ENV_BASE_URL> or similar token). Endpoint and credential details are
provided in an environment_access.md file staged alongside the task. The API
returns JSON; all data extraction uses curl piped to jq.
Environment Setup
Before any query, read environment_access.md from the task working directory.
Extract:
BASE_URL: the root URL (e.g. http://task-env:9007)
TOKEN_HEADER: the header name and value for the read-only SQL endpoint
(e.g. X-Env-Token: portfolio-readonly)
Verify connectivity with curl -s "$BASE_URL/health". Set these as shell
variables for the session.
API Endpoints
| Method |
Path |
Purpose |
| GET |
/health |
Connectivity check |
| GET |
/api/work-items |
List work items (supports ?team=, ?product_area=, ?status=, ?quarter= filters) |
| GET |
/api/work-items/{item_id} |
Single work item detail |
| GET |
/api/mix-targets |
Target portfolio mix percentages by scope |
| GET |
/api/sla-policy |
SLA deadline rules by category and severity |
| GET |
/api/releases |
List releases |
| GET |
/api/releases/{release_id} |
Single release detail |
| GET |
/api/milestones |
List milestones (filter by ?release_id=) |
| GET |
/api/dependencies |
Dependency edges between work items |
| GET |
/api/blockers |
Blocker records on work items |
| POST |
/api/query |
Read-only SQL queries; include the token header from environment_access.md |
All GET endpoints return JSON arrays or objects. Use curl -s and pipe through
jq for filtering and transformation.
Work Item Data Model
Work items have these fields relevant across all task families:
id: string identifier (e.g. WI-24024-P001)
title: human-readable summary
type: work-item type string
status: authoritative current state (Closed, In Progress, Open, Cancelled, etc.)
category: the portfolio category assigned to this item
team: owning team
owner: assigned person (may be null)
product_area: product area label
quarter: planning quarter (e.g. 2025-Q4)
closed_at: ISO-8601 timestamp when closed, or null
sla_deadline: ISO-8601 deadline for SLA purposes, or null
severity: severity level (S1, S2, S3, S4), or null
duplicate_of: if this item is a duplicate, the id of the canonical primary item
release_id: associated release, or null
milestone_id: associated milestone, or null
Authoritative vs. Stale Fields
The API may include mirror or legacy fields (e.g. mirror_status,
legacy_category, export_status). These are stale and must not be used.
Always read the authoritative field directly:
- Use
status for item state, never mirror_status
- Use
category (after classification) for portfolio category, never legacy_category
- Use
closed_at for closure timestamp, never derived or mirrored timestamps
Primary vs. Duplicate Records
A work item with a non-null duplicate_of field points to another canonical
item. The canonical item is the primary; the pointing item is the
duplicate. Duplicates must be:
- Excluded from all counts, percentages, and statistics
- Reported separately in
duplicate_clusters or excluded_duplicate_ids
Build the primary population by filtering out any item where duplicate_of is
non-null. If an item is itself a primary (others point to it), it stays in the
primary population.
Cancelled Items
Work items with status equal to Cancelled are excluded from the closed
portfolio population. Report them in excluded_cancelled_ids.
Portfolio Category Classification
Every included work item must be classified into exactly one of these four
categories:
Security
Reliability
TechDebt
NewFeature
Resolve the category using this priority chain:
- Direct category field: If the work item has an explicit
category field
matching one of the four values, use it.
- Type-to-category mapping: Map the work item
type field:
- Types containing
security, vuln, cve, appsec → Security
- Types containing
reliability, sre, incident, resilience → Reliability
- Types containing
debt, refactor, cleanup, techdebt → TechDebt
- Types containing
feature, enhancement, story → NewFeature
- Label conventions: If labels are present, match against the same keyword
patterns.
- Title keyword matching: Scan the
title for the same keyword families as
a last resort.
When signals conflict (e.g. type says feature but title mentions security),
the higher-priority category wins: Security > Reliability > TechDebt > NewFeature.
A security signal from any source overrides a lower-category signal.
Task Family 1: Portfolio Mix Analysis
Workflow
Retrieve the target mix: GET /api/mix-targets, filter by
scope_id to find the target row. Extract target_pct for each of the four
categories.
Fetch in-scope work items: Query /api/work-items with filters matching
the scope (team, product_area, quarter, status=Closed).
Filter primary population: Remove duplicates (non-null duplicate_of),
cancelled items, and distractor records that appear in scope but do not
belong to the primary closed portfolio.
Classify each item: Apply the portfolio category classification rules
above.
Compute counts and percentages: Count items per category. Compute
actual_pct = (count / total_included) * 100. Round to 1 decimal place.
Build the gap table: For each category in fixed order (NewFeature,
TechDebt, Reliability, Security), compute gap_pct = actual_pct - target_pct.
Round to 1 decimal place. The target_pct values come from the target mix
row.
Identify under-invested categories: Categories with negative gap_pct,
sorted from most negative to least negative.
Determine follow-up action:
- If any category has negative gap:
action = "REBALANCE_CAPACITY",
primary_category = most negative gap category,
secondary_category = second most negative (or null if only one),
rationale_code = "LARGEST_NEGATIVE_GAP".
- If no negative gaps:
action = "MAINTAIN_CURRENT_MIX",
primary_category = null, secondary_category = null,
rationale_code = "NO_NEGATIVE_GAPS".
- If data quality issues are suspected:
action = "INVESTIGATE_DATA_QUALITY",
rationale_code = "DATA_CONFLICT".
Collect exclusion flags: Separate arrays for duplicate ids and cancelled
ids, sorted ascending. Set ignored_mirror_status_and_legacy_category to
true.
Assemble the answer: Follow the answer template schema exactly. The
scope block must match the task scope parameters. included_work_item_ids
sorted by closed_at ascending then id ascending. Team names
alphabetically.
Task Family 2: SLA Aging Audit
Workflow
Fetch SLA policy: GET /api/sla-policy to understand SLA deadline rules
by category and severity.
Fetch in-scope work items: Query /api/work-items with team and
category filters matching the task scope. Include open and in-progress items;
also include closed items whose closed_at falls within the recent-closed
window so recently-resolved items can be counted.
Build the primary SLA population: Remove duplicates (non-null
duplicate_of). The remaining items form included_primary_ids. Sort
lexicographically.
Determine overdue items: An item is overdue if:
- Its SLA deadline is before the as-of date, AND
- It is not closed, OR it was closed but within the recent-closed window
(i.e. it was overdue when it closed).
- Use
sla_deadline < as_of_date as the primary check.
Compute aging buckets: For each overdue item, compute days overdue as
as_of_date - sla_deadline in whole days. Bucket into:
0-3: 0 to 3 days
4-7: 4 to 7 days
8-14: 8 to 14 days
15-30: 15 to 30 days
31+: 31 or more days
Compute team overdue counts: Group overdue primary items by team. List
teams alphabetically with their overdue counts.
Identify the top hotspot: Group overdue primary items by (team, owner)
pairs. The pair with the highest count is the top hotspot. If owner is null,
use "UNASSIGNED". If there's a tie, pick the first alphabetically by team,
then by owner.
Build duplicate clusters: For each primary item that has duplicates
pointing to it, create a cluster {primary_id, duplicate_ids: [...]}.
Sort clusters by primary_id ascending. Sort duplicate_ids
lexicographically within each cluster.
Identify missing-owner items: Primary items where owner is null or
empty. List their ids sorted ascending.
Calculate breach rate: breach_rate = overdue_count / included_primary_count. Round to exactly 3 decimal places.
If the task asks for an escalation queue (severity-weighted ordering):
Sort overdue primary items by severity priority (S1 > S2 > S3 > S4), then
by days overdue descending within each severity band. Output the ordered id
list as escalation_queue_ids.
If the task asks for overdue counts by severity: Count overdue primary
items per severity level (S1, S2, S3, S4).
Task Family 3: Release Readiness Assessment
Workflow
Fetch the release: GET /api/releases/{release_id}.
Fetch milestones: GET /api/milestones?release_id={release_id}.
Fetch work items for the release: Query work items by release_id.
Build the primary population (exclude duplicates).
Fetch blockers: GET /api/blockers, filter to those on release work
items that are unresolved and high-impact.
Fetch dependencies: GET /api/dependencies, filter to edges where the
source is a release work item.
Compute milestone completion: For each milestone, count primary work
items associated with it. complete_primary = items with status Closed or
equivalent terminal state. primary_total = all primary items in the
milestone. completion_pct = (complete_primary / primary_total) * 100,
rounded to 1 decimal place. Sort milestones by milestone_id ascending.
Identify gating work items: Non-complete primary release work items that
block readiness. These are items whose incomplete state prevents shipping.
Sort ascending, no duplicates.
Count blocker causes: For unresolved high-impact blockers on release
work items, count by exact cause string. Only include blockers that are
not resolved/closed.
Trace critical dependency chains: For release work items that are blocked
by dependencies, follow depends_on edges to find the non-complete item at
the end of each chain. Build ordered paths [blocked_work_item, ..., non_complete_dependency]. Sort chains lexicographically by the full path
string representation.
Compute readiness score: completed_primary_work / total_primary_work,
rounded to 3 decimal places. Count across all milestones.
Determine ship decision:
SHIP: readiness_score = 1.0, zero gating items, zero unresolved
high-impact blockers.
SHIP_WITH_WATCH: readiness_score >= 0.85, manageable gating items,
no critical unresolved blockers.
NO_SHIP: readiness_score < 0.85, significant gating items, or
unresolved high-impact blockers.
Common Output Rules
Sorting
- Work item id lists: lexicographically ascending (string sort)
- Team names: alphabetically ascending
- Duplicate clusters: by
primary_id ascending; duplicate_ids lexicographically
- Milestones: by
milestone_id ascending
- Dependency chains: lexicographically by the full path representation
- Gap table rows: fixed category order: NewFeature, TechDebt, Reliability, Security
- Mix table rows: same fixed category order
- Under-invested categories: most negative gap first
Rounding
- Percentages (completion_pct, actual_pct, target_pct, gap_pct): 1 decimal place
- Rates and scores (breach_rate, readiness_score): 3 decimal places
- Use standard rounding (half-up) in all calculations
JSON Output
- Produce exactly one JSON object or array as specified by the answer template
- No prose, explanation, or markdown outside the JSON
- Follow the answer template's key names, types, and required fields exactly
- Use
jq to validate structure before final output
SQL Queries
When the REST endpoints do not provide sufficient filtering, use
POST /api/query:
curl -s -X POST "$BASE_URL/api/query" \
-H "Content-Type: application/json" \
-H "X-Env-Token: portfolio-readonly" \
-d '{"query": "SELECT ..."}'
The SQL schema mirrors the work item data model. Use parameterized filters in
the SQL string. Prefer REST endpoints when they support the needed filters;
fall back to SQL only when the REST API is insufficient.
Validation Checklist
Before submitting the answer, verify:
1---2name: portfolio-review3description: Solve engineering portfolio review tasks (mix analysis, SLA aging, release readiness) against a shared REST API with work items, milestones, blockers, dependencies, mix targets, and SLA policy data. Use when the task requires classifying work items into portfolio categories, computing mix gaps, auditing SLA compliance, or assessing release readiness.4metadata:5 source: https://skillmd.com/skills/prism-shadow/portfolio-review6---78# Portfolio Review Skill910## Overview1112This skill solves engineering portfolio review tasks that query a shared REST API13and produce structured JSON answers. Three task families are covered: portfolio14mix analysis, SLA aging audits, and release readiness assessments.1516The API is accessed at a base URL supplied in the task prompt (typically as17`<TASK_ENV_BASE_URL>` or similar token). Endpoint and credential details are18provided in an `environment_access.md` file staged alongside the task. The API19returns JSON; all data extraction uses `curl` piped to `jq`.2021## Environment Setup2223Before any query, read `environment_access.md` from the task working directory.24Extract:2526- `BASE_URL`: the root URL (e.g. `http://task-env:9007`)27- `TOKEN_HEADER`: the header name and value for the read-only SQL endpoint28 (e.g. `X-Env-Token: portfolio-readonly`)2930Verify connectivity with `curl -s "$BASE_URL/health"`. Set these as shell31variables for the session.3233## API Endpoints3435| Method | Path | Purpose |36|--------|------|---------|37| GET | `/health` | Connectivity check |38| GET | `/api/work-items` | List work items (supports `?team=`, `?product_area=`, `?status=`, `?quarter=` filters) |39| GET | `/api/work-items/{item_id}` | Single work item detail |40| GET | `/api/mix-targets` | Target portfolio mix percentages by scope |41| GET | `/api/sla-policy` | SLA deadline rules by category and severity |42| GET | `/api/releases` | List releases |43| GET | `/api/releases/{release_id}` | Single release detail |44| GET | `/api/milestones` | List milestones (filter by `?release_id=`) |45| GET | `/api/dependencies` | Dependency edges between work items |46| GET | `/api/blockers` | Blocker records on work items |47| POST | `/api/query` | Read-only SQL queries; include the token header from environment_access.md |4849All GET endpoints return JSON arrays or objects. Use `curl -s` and pipe through50`jq` for filtering and transformation.5152## Work Item Data Model5354Work items have these fields relevant across all task families:5556- `id`: string identifier (e.g. `WI-24024-P001`)57- `title`: human-readable summary58- `type`: work-item type string59- `status`: authoritative current state (`Closed`, `In Progress`, `Open`, `Cancelled`, etc.)60- `category`: the portfolio category assigned to this item61- `team`: owning team62- `owner`: assigned person (may be null)63- `product_area`: product area label64- `quarter`: planning quarter (e.g. `2025-Q4`)65- `closed_at`: ISO-8601 timestamp when closed, or null66- `sla_deadline`: ISO-8601 deadline for SLA purposes, or null67- `severity`: severity level (`S1`, `S2`, `S3`, `S4`), or null68- `duplicate_of`: if this item is a duplicate, the id of the canonical primary item69- `release_id`: associated release, or null70- `milestone_id`: associated milestone, or null7172### Authoritative vs. Stale Fields7374The API may include mirror or legacy fields (e.g. `mirror_status`,75`legacy_category`, `export_status`). These are stale and must not be used.76Always read the authoritative field directly:7778- Use `status` for item state, never `mirror_status`79- Use `category` (after classification) for portfolio category, never `legacy_category`80- Use `closed_at` for closure timestamp, never derived or mirrored timestamps8182### Primary vs. Duplicate Records8384A work item with a non-null `duplicate_of` field points to another canonical85item. The canonical item is the **primary**; the pointing item is the86**duplicate**. Duplicates must be:8788- Excluded from all counts, percentages, and statistics89- Reported separately in `duplicate_clusters` or `excluded_duplicate_ids`9091Build the primary population by filtering out any item where `duplicate_of` is92non-null. If an item is itself a primary (others point to it), it stays in the93primary population.9495### Cancelled Items9697Work items with `status` equal to `Cancelled` are excluded from the closed98portfolio population. Report them in `excluded_cancelled_ids`.99100## Portfolio Category Classification101102Every included work item must be classified into exactly one of these four103categories:1041051. `Security`1062. `Reliability`1073. `TechDebt`1084. `NewFeature`109110Resolve the category using this priority chain:1111121. **Direct category field**: If the work item has an explicit `category` field113 matching one of the four values, use it.1142. **Type-to-category mapping**: Map the work item `type` field:115 - Types containing `security`, `vuln`, `cve`, `appsec` → `Security`116 - Types containing `reliability`, `sre`, `incident`, `resilience` → `Reliability`117 - Types containing `debt`, `refactor`, `cleanup`, `techdebt` → `TechDebt`118 - Types containing `feature`, `enhancement`, `story` → `NewFeature`1193. **Label conventions**: If labels are present, match against the same keyword120 patterns.1214. **Title keyword matching**: Scan the `title` for the same keyword families as122 a last resort.123124When signals conflict (e.g. type says `feature` but title mentions `security`),125the higher-priority category wins: Security > Reliability > TechDebt > NewFeature.126A security signal from any source overrides a lower-category signal.127128## Task Family 1: Portfolio Mix Analysis129130### Workflow1311321. **Retrieve the target mix**: `GET /api/mix-targets`, filter by133 `scope_id` to find the target row. Extract `target_pct` for each of the four134 categories.1351362. **Fetch in-scope work items**: Query `/api/work-items` with filters matching137 the scope (team, product_area, quarter, status=Closed).1381393. **Filter primary population**: Remove duplicates (non-null `duplicate_of`),140 cancelled items, and distractor records that appear in scope but do not141 belong to the primary closed portfolio.1421434. **Classify each item**: Apply the portfolio category classification rules144 above.1451465. **Compute counts and percentages**: Count items per category. Compute147 `actual_pct = (count / total_included) * 100`. Round to 1 decimal place.1481496. **Build the gap table**: For each category in fixed order (NewFeature,150 TechDebt, Reliability, Security), compute `gap_pct = actual_pct - target_pct`.151 Round to 1 decimal place. The `target_pct` values come from the target mix152 row.1531547. **Identify under-invested categories**: Categories with negative `gap_pct`,155 sorted from most negative to least negative.1561578. **Determine follow-up action**:158 - If any category has negative gap: `action = "REBALANCE_CAPACITY"`,159 `primary_category` = most negative gap category,160 `secondary_category` = second most negative (or null if only one),161 `rationale_code = "LARGEST_NEGATIVE_GAP"`.162 - If no negative gaps: `action = "MAINTAIN_CURRENT_MIX"`,163 `primary_category` = null, `secondary_category` = null,164 `rationale_code = "NO_NEGATIVE_GAPS"`.165 - If data quality issues are suspected: `action = "INVESTIGATE_DATA_QUALITY"`,166 `rationale_code = "DATA_CONFLICT"`.1671689. **Collect exclusion flags**: Separate arrays for duplicate ids and cancelled169 ids, sorted ascending. Set `ignored_mirror_status_and_legacy_category` to170 `true`.17117210. **Assemble the answer**: Follow the answer template schema exactly. The173 `scope` block must match the task scope parameters. `included_work_item_ids`174 sorted by `closed_at` ascending then `id` ascending. Team names175 alphabetically.176177## Task Family 2: SLA Aging Audit178179### Workflow1801811. **Fetch SLA policy**: `GET /api/sla-policy` to understand SLA deadline rules182 by category and severity.1831842. **Fetch in-scope work items**: Query `/api/work-items` with team and185 category filters matching the task scope. Include open and in-progress items;186 also include closed items whose `closed_at` falls within the recent-closed187 window so recently-resolved items can be counted.1881893. **Build the primary SLA population**: Remove duplicates (non-null190 `duplicate_of`). The remaining items form `included_primary_ids`. Sort191 lexicographically.1921934. **Determine overdue items**: An item is overdue if:194 - Its SLA deadline is before the as-of date, AND195 - It is not closed, OR it was closed but within the recent-closed window196 (i.e. it was overdue when it closed).197 - Use `sla_deadline < as_of_date` as the primary check.1981995. **Compute aging buckets**: For each overdue item, compute days overdue as200 `as_of_date - sla_deadline` in whole days. Bucket into:201 - `0-3`: 0 to 3 days202 - `4-7`: 4 to 7 days203 - `8-14`: 8 to 14 days204 - `15-30`: 15 to 30 days205 - `31+`: 31 or more days2062076. **Compute team overdue counts**: Group overdue primary items by team. List208 teams alphabetically with their overdue counts.2092107. **Identify the top hotspot**: Group overdue primary items by `(team, owner)`211 pairs. The pair with the highest count is the top hotspot. If owner is null,212 use `"UNASSIGNED"`. If there's a tie, pick the first alphabetically by team,213 then by owner.2142158. **Build duplicate clusters**: For each primary item that has duplicates216 pointing to it, create a cluster `{primary_id, duplicate_ids: [...]}`.217 Sort clusters by `primary_id` ascending. Sort `duplicate_ids`218 lexicographically within each cluster.2192209. **Identify missing-owner items**: Primary items where `owner` is null or221 empty. List their ids sorted ascending.22222310. **Calculate breach rate**: `breach_rate = overdue_count /224 included_primary_count`. Round to exactly 3 decimal places.22522611. **If the task asks for an escalation queue** (severity-weighted ordering):227 Sort overdue primary items by severity priority (S1 > S2 > S3 > S4), then228 by days overdue descending within each severity band. Output the ordered id229 list as `escalation_queue_ids`.23023112. **If the task asks for overdue counts by severity**: Count overdue primary232 items per severity level (S1, S2, S3, S4).233234## Task Family 3: Release Readiness Assessment235236### Workflow2372381. **Fetch the release**: `GET /api/releases/{release_id}`.2392402. **Fetch milestones**: `GET /api/milestones?release_id={release_id}`.2412423. **Fetch work items for the release**: Query work items by `release_id`.243 Build the primary population (exclude duplicates).2442454. **Fetch blockers**: `GET /api/blockers`, filter to those on release work246 items that are unresolved and high-impact.2472485. **Fetch dependencies**: `GET /api/dependencies`, filter to edges where the249 source is a release work item.2502516. **Compute milestone completion**: For each milestone, count primary work252 items associated with it. `complete_primary` = items with status `Closed` or253 equivalent terminal state. `primary_total` = all primary items in the254 milestone. `completion_pct = (complete_primary / primary_total) * 100`,255 rounded to 1 decimal place. Sort milestones by `milestone_id` ascending.2562577. **Identify gating work items**: Non-complete primary release work items that258 block readiness. These are items whose incomplete state prevents shipping.259 Sort ascending, no duplicates.2602618. **Count blocker causes**: For unresolved high-impact blockers on release262 work items, count by exact `cause` string. Only include blockers that are263 not resolved/closed.2642659. **Trace critical dependency chains**: For release work items that are blocked266 by dependencies, follow `depends_on` edges to find the non-complete item at267 the end of each chain. Build ordered paths `[blocked_work_item, ...,268 non_complete_dependency]`. Sort chains lexicographically by the full path269 string representation.27027110. **Compute readiness score**: `completed_primary_work / total_primary_work`,272 rounded to 3 decimal places. Count across all milestones.27327411. **Determine ship decision**:275 - `SHIP`: readiness_score = 1.0, zero gating items, zero unresolved276 high-impact blockers.277 - `SHIP_WITH_WATCH`: readiness_score >= 0.85, manageable gating items,278 no critical unresolved blockers.279 - `NO_SHIP`: readiness_score < 0.85, significant gating items, or280 unresolved high-impact blockers.281282## Common Output Rules283284### Sorting285286- Work item id lists: lexicographically ascending (string sort)287- Team names: alphabetically ascending288- Duplicate clusters: by `primary_id` ascending; `duplicate_ids` lexicographically289- Milestones: by `milestone_id` ascending290- Dependency chains: lexicographically by the full path representation291- Gap table rows: fixed category order: NewFeature, TechDebt, Reliability, Security292- Mix table rows: same fixed category order293- Under-invested categories: most negative gap first294295### Rounding296297- Percentages (completion_pct, actual_pct, target_pct, gap_pct): 1 decimal place298- Rates and scores (breach_rate, readiness_score): 3 decimal places299- Use standard rounding (half-up) in all calculations300301### JSON Output302303- Produce exactly one JSON object or array as specified by the answer template304- No prose, explanation, or markdown outside the JSON305- Follow the answer template's key names, types, and required fields exactly306- Use `jq` to validate structure before final output307308### SQL Queries309310When the REST endpoints do not provide sufficient filtering, use311`POST /api/query`:312313```bash314curl -s -X POST "$BASE_URL/api/query" \315 -H "Content-Type: application/json" \316 -H "X-Env-Token: portfolio-readonly" \317 -d '{"query": "SELECT ..."}'318```319320The SQL schema mirrors the work item data model. Use parameterized filters in321the SQL string. Prefer REST endpoints when they support the needed filters;322fall back to SQL only when the REST API is insufficient.323324## Validation Checklist325326Before submitting the answer, verify:327328- [ ] `environment_access.md` was read and BASE_URL / TOKEN_HEADER extracted329- [ ] All REST calls use `curl -s` with appropriate query parameters330- [ ] Duplicate items (non-null `duplicate_of`) are excluded from primary counts331- [ ] Stale mirror/legacy fields are ignored; authoritative fields used332- [ ] Cancelled items excluded from closed portfolio populations333- [ ] Category classification follows the priority chain: Security > Reliability > TechDebt > NewFeature334- [ ] Sorting follows the conventions for each list type335- [ ] Rounding follows the 1-decimal (percentages) / 3-decimal (rates) rule336- [ ] The output is a single JSON value matching the answer template schema337- [ ] No prose, markdown, or commentary appears outside the JSON