copilot-history-briefing

Files (1)

• SKILL.md

SKILL.md (first lines)

# Copilot history briefing

Use this skill to turn Copilot conversation history into evidence-backed briefing material.

## Dependencies and setup

- Query the Copilot session store tables directly with SQL: `sessions`, `turns`, `checkpoints`, `session_files`, and `session_refs`.
- Use `search_index` only when it exists and you need faster keyword discovery.
- The local store is the SQLite database at `~/.copilot/session-store.db` on the current machine; per-session state files live under `~/.copilot/session-state/<session-id>/session.db`.
- Use local for this machine's SQLite history at `~/.copilot/session-store.db`; use cloud only for populated shared history through the agent runtime's cloud-backed path.

## Process

1. Start with a small test search.
   - Search the most recent and most relevant sessions first.
   - Use narrow filters by date, summary, repo, branch, session id, or request keywords.
   - Widen the search only if the first pass misses likely sessions.

2. Build the session set.
   - Identify the sessions that actually support the request.
   - For each candidate, capture the session id, summary, date, and the turns or checkpoints that matter.
   - If the user asked for prior history, include both direct matches and closely related sessions that reveal reusable themes.
   - If a candidate overlaps an existing story topic, expand the existing story instead of creating a duplicate.

3. Read the evidence.
   - Pull the turns, checkpoints, artifacts, and refactor notes that show what changed.
   - Capture concrete details: before/after state, blockers, corrections, benchmarks, tests, and user decisions.
   - Cite only what is needed to support each claim.
   - Before summarizing, build a detail-rich working record for each candidate story:
     - starting condition or pain point
     - Copilot-assisted intervention
     - concrete output or artifact created
     - authoritative source, system, dataset, or workflow involved
     - users or staff affected, if supported by the history
     - constraints, reversals, corrections, or user decisions that shaped the final result
     - follow-up work, reusable helper, package, report, or process that came out of the session
   - Keep distinct efforts separate in the working record. Do not merge related projects just because they share a domain, repository, package, or data source.

   Query strategy:
   - Start with `sessions` to find candidate stories. Narrow by `created_at`, `repository`, `cwd`, `branch`, or `summary` before you touch turn text.
   - If metadata is too thin, search `search_index` for repo names, skill names, issue numbers, or other distinctive phrases, then reuse the matching `session_id`s in the next table.
   - Read `checkpoints` for compact session summaries, `turns` for the actual conversation, and `session_files` / `session_refs` for files, commits, PRs, or issues tied to a session.
   - Use two steps: first identify the relevant `session_id`s, then query only those sessions for the evidence table you need.
   - Keep queries narrow: use a date window, select only needed columns, and add `LIMIT` early.
   - Treat the SQL below as a template and replace the placeholders with the repository, workspace, time window, and session ids you need.

   Example pattern:
   ```sql
 -- step 1: find candidate sessions
   SELECT id, created_at, repository, cwd, summary
   FROM sessions
   WHERE created_at > now() - INTERVAL 'REPLACE_ME_DAYS days'
    AND (
      repository ILIKE '%REPLACE_ME_REPOSITORY%'
      OR cwd ILIKE '%REPLACE_ME_WORKSPACE_FRAGMENT%'
    )
   ORDER BY created_at DESC
   LIMIT REPLACE_ME_LIMIT;

 -- step 2: inspect the conversation for the sessions you picked
   SELECT session_id, turn_index, user_message, assistant_response, timestamp
   FROM turns
   WHERE session_id IN (REPLACE_ME_SESSION_ID_LIST)
   ORDER BY session_id, turn_index
   LIMIT REPLACE_ME_LIMIT;

 -- optional: pull the compact evidence trail for those same sessions
   SELECT session_id, created_at, title, overview
   FROM checkpoints
   WHERE session_id IN (REPLACE_ME_SESSION_ID_LIST)
   ORDER BY session_id, created_at
   LIMIT REPLACE_ME_LIMIT;
   ```
   - `REPLACE_ME_REPOSITORY`: use a repository name or fragment that matches your local or cloud session metadata.
   - `REPLACE_ME_WORKSPACE_FRAGMENT`: use a unique substring from the workspace path or cwd for your environment.
   - `REPLACE_ME_DAYS`: use the date window you want to inspect, such as `7`, `30`, or `90`.
   - `REPLACE_ME_SESSION_ID_LIST`: use a comma-separated list of session IDs, or replace this block with the exact filter your SQL dialect expects.
   - `REPLACE_ME_LIMIT`: use the maximum row count appropriate for the query stage.

4. Extract success-story material.
   - First create a detail-rich working brief. It may be longer and more technical than the final answer, because its purpose is to preserve enough raw material for later distillation.
   - In the working brief, keep project-level specifics such as package names, report types, data sources, commands, files, issue numbers, and user decisions when they help explain what actually changed.
   - Only generalize in the final audience-facing text. Preserve enough specificity that the claim can still be traced back to one concrete effort rather than a generic theme.
   - When adapting for a broad audience, generalize jargon into the workflow category first, then keep one or two concrete details that prove the story. For example, "soil report routing based on authoritative ownership records" is better than only "improved reports."
   - Translate technical outcomes into executive language.
   - Lead with the business or operational outcome, not the implementation log.
   - Make the analyst or team the subject of the sentence; Copilot is the tool that helped.
   - Spell out acronyms on first use for executive-facing briefs. If the acronym is unclear, ask the user before using it.
   - Keep test counts, benchmark minutiae, failure details, troubleshooting notes, and "hot path" language out of the summary unless they are the point.
   - Look for time saved, work avoided, reliability improved, repeatability gained, manual steps removed, and risk reduced.
   - Include numbers whenever the history provides them. If it does not, say so plainly and do not invent them.
   - Prefer the strongest supported number or scale, then explain its business significance in plain language.
   - Prefer clean outcome metrics over work-log details or measurement mechanics.
   - Call out organization-, program-, or administration-level priorities only when the history supports the connection.

5. Synthesize by audience.
   - For non-technical executives, lead with business value, operational impact, and measurable change.
   - For technical readers, add implementation detail after the executive summary.
   - Prefer a short list of strong stories over a long list of weak ones.

## Output shape

When the user wants a briefing, use this structure:

```markdown
# Executive summary
[1-3 paragraphs on the overall themes, what Copilot accelerated, and why it matters]

## Best story candidates
| Story | Why it matters | Evidence | Metrics |
| --- | --- | --- | --- |
| ... | ... | session ids / turns | quantitative details if available |

## Priority alignment
- Organization priorities:
- Program priorities:

## Gaps
- Missing metrics
- Missing evidence

From AndrewBrown-USDA/aigb-skills-usda, MIT.

More from AndrewBrown-USDA/aigb-skills-usda