---
name: idstune
description: >-
  IDSTune is a reusable agent skill that provides integrated database tuning
  for specific workloads. Use this skill to collect runtime workload
  context, generate knob (configuration) recommendations, propose index and
  materialized-view changes, review and merge optimization plans, and run the
  framework iteratively. Designed for orchestration in dynamic agent
  frameworks (e.g., Copilot CLI-style tool use).
license: Apache-2.0
compatibility: >-
  Requires Python 3.10+ and access to the repository containing
  `configuration_recommendation/optimizer.py`. Needs network access for LLM
  API calls and optional web search. Requires database access 
  when running actions that exercise or apply plans.
metadata:
  author: IDSTune Project
  version: "0.1"
allowed-tools: Bash Python Read Web Search Safety Guardrails DB Execution
---

# IDSTune Skill

## Summary

This skill packages the IDSTune design into a single, discoverable agent skill.
It exposes a small, well-documented set of actions that orchestration agents
can call to perform database tuning tasks without needing to reimplement the
coordination logic or low-level plumbing.

Load this skill when the agent's goal includes automated database tuning,
configuration optimization, or workload-driven index/materialized-view
recommendations.

## Actions (what this skill provides)

- collect_context
  - Purpose: refresh and collect workload and database runtime features.
  - Implementation hint: calls `refresh_context()` or `workload_compression.get_features`.

- suggest_knobs
  - Purpose: produce knob (database configuration) tuning recommendations.
  - Implementation: `configuration_recommendation.optimizer.param_tuner(current_plan=None)`
  - Output: JSON object with `agent: "KnobTuner"`, `items: [ {name, value, details}, ... ]`, and `rationale`.

- suggest_indexes
  - Purpose: propose index recommendations.
  - Implementation: `configuration_recommendation.optimizer.index_recommender(current_plan=None)`
  - Output: JSON object with `agent: "IndexRecommender"`, `items: [ {name, table, columns, details}, ... ]`.

- suggest_matviews
  - Purpose: propose materialized view recommendations.
  - Implementation: `configuration_recommendation.optimizer.matview_recommender(current_plan=None)`
  - Output: JSON object with `agent: "MatViewRecommender"`, `items: [ {name, query, details}, ... ]`.

- review_plan
  - Purpose: evaluate a candidate plan and return an opinion or revision requests.
  - Implementation: `configuration_recommendation.optimizer.control_node(plan, current_plan=None, history=None)`
  - Output: `{ opinion: "Accept" | "Reject", revisions: [ {agent, comment}, ... ] }`.

- merge_plan
  - Purpose: merge a single agent's recommendation into the global plan.
  - Implementation: `configuration_recommendation.optimizer.merge_plan(plan, rec)`
  - Effect: updates passed-in `plan` object in place and logs the merged plan.

- run_framework
  - Purpose: run the full IDSTune iterative optimization loop.
  - Implementation: `configuration_recommendation.optimizer.run_framework(max_iters, previous_plan=None, history=None)`
  - Output: final `plan` JSON (may also write plan/result files and optionally run workload tests depending on `config.ini`).

## Tool execution policy

Use the following tools at the points below during an orchestration run:

- Web Search
  - Use automatically when the agent needs additional domain knowledge that is not present in the local repository or current plan history.
  - Typical triggers include missing tuning heuristics, unfamiliar PostgreSQL behavior, or ambiguous recommendations that need external corroboration.
  - Keep the search targeted and only fetch information that changes the tuning decision.

- Safety Guardrails
  - Use once a tuning report is finalized, before any recommendation is considered ready for application.
  - Validate recommendation quality, consistency, permission boundaries, and risk level.
  - Reject or flag plans that contain conflicting changes, unsafe resource usage, or actions that exceed the deployment policy.

- DB Execution
  - Use once Safety Guardrails approves the finalized tuning report.
  - Apply the recommended changes to the target database or test environment in the correct order.
  - Capture execution feedback, including success/failure, performance impact, and any runtime errors, then feed that feedback back into context collection for the next iteration.
  - Prefer transactional, reversible, or isolated execution paths whenever possible.

Orchestration order:

1. Collect context.
2. Generate candidate recommendations.
3. Invoke Web Search only if additional knowledge is required.
4. Review the report with Safety Guardrails.
5. Execute the approved changes with DB Execution.
6. Feed execution feedback back into context and continue iterating if needed.

## Inputs and Outputs

This skill prefers structured JSON inputs and returns structured JSON outputs.
Recommended common input schema:

- `target` (optional): { db: <connection>, workload_dir: <path>, benchmark: <string> }
- `constraints` (optional): { time_limit: <seconds>, max_iterations: <int>, dry_run: <bool> }
- `previous_plan` (optional): plan JSON produced by prior runs

Common output schema (plan):

- `plan`: {
  - `knobs`: { <name>: {value, details}, ... },
  - `indexes`: [ {name, table, columns, details}, ... ],
  - `matviews`: [ {name, query, details}, ... ],
  - `history`: [ ... ]
  }

Where applicable, recommendation actions return wrapper objects that include
an `agent` field (e.g., `KnobTuner`) and a free-text `rationale` when JSON
parsing fails.

## Examples

### Programmatic usage (Python, no side effects unless you run tests)

```python
from configuration_recommendation import optimizer

# Collect recommendations (safe: only calls LLM and local code)
plan = {"knobs": {}, "indexes": [], "matviews": [], "history": []}
for fn in (optimizer.param_tuner, optimizer.index_recommender, optimizer.matview_recommender):
    rec = fn(None)
    optimizer.merge_plan(plan, rec)

print(plan)
```

### CLI-style quick example (Copilot CLI-like agent exec)

To fetch recommendations only:

```bash
python -c "from configuration_recommendation.optimizer import param_tuner,index_recommender,matview_recommender,merge_plan; p={'knobs':{},'indexes':[],'matviews':[],'history':[]};
for f in (param_tuner,index_recommender,matview_recommender): merge_plan(p,f(None));
import json; print(json.dumps(p,ensure_ascii=False,indent=2))"
```

To run the full framework (may perform tests and have side effects):

```bash
python configuration_recommendation/optimizer.py
```

## Safety, validation and best practices

- Dry-run recommended: set `constraints.dry_run=true` or isolate DB in a test instance
  before applying changes (creating indexes, materialized views, or changing knobs).
- Validate plans before applying: perform `validate_plan(plan)` that checks for
  duplicates, conflicting index definitions, resource budget, and DB permissions.
- LLM failures: `control_node` contains fallback logic; orchestration layers
  should implement retry, backoff and human review for `Reject` outcomes.
- Audit & rollback: apply changes in transactional or revertible steps and keep
  an audit log of any schema or configuration changes.

## Error handling

- Recommendation functions return non-JSON `rationale` text when parsing fails.
  Orchestrators should treat such cases as candidate outputs requiring review.
- When external tests (benchmark runners) fail, the skill logs results to
  `optimization_result.json` and continues according to constraints.

## Files and integration points

Place this skill in the repository root as a sibling to the existing
`configuration_recommendation` folder. This skill references the following
modules inside the repository:

- `configuration_recommendation/optimizer.py` (core implementation)
- `workload_compression/*` (context extraction)
- `configuration_recommendation/DB_test.py` (benchmark/test runners)

Agents should register the following callable entrypoints provided by the
repository when activating this skill:

- `idstune.collect_context` -> `workload_compression.get_features` / `refresh_context`
- `idstune.suggest_knobs` -> `optimizer.param_tuner`
- `idstune.suggest_indexes` -> `optimizer.index_recommender`
- `idstune.suggest_matviews` -> `optimizer.matview_recommender`
- `idstune.review_plan` -> `optimizer.control_node`
- `idstune.merge_plan` -> `optimizer.merge_plan`
- `idstune.run_framework` -> `optimizer.run_framework`

## Progressive disclosure

- Agents should load the frontmatter and the action list when considering
  activating the skill (small). The instructions above provide enough detail
  for initial use. If deeper references are needed, load implementation files
  under `configuration_recommendation/` on demand.

## Reference & validation

Validate this skill locally using the agent skills reference tooling if
available (see repository `Specification.md` for validation commands).