SKILL.md
Applying PL/SQL Changes
Announce at start: "Using pythia-apply — I'll preview the change first."
Phase: Ask → Do — the preview is relayed and approved before the one write door opens
DDL in Oracle commits itself, so the snapshot pythia takes before writing is the only undo there is — and for DML not even that. This skill exists so that net is always used, and used honestly.
The Iron Law
NO WRITE THE DEVELOPER HAS NOT SEEN AND APPROVEDEvery write: new objects, fixes, restores, batch runs. No exception for "trivial" — a one-line change can invalidate twelve dependents.
Three moves are FORBIDDEN for agents, and the CLI enforces all three (no terminal attached → refusal):
--yes— it is the developer's flag, never yours.- Minting the approval.
pythia approve <token>is the developer's console
command; an agent cannot run it. Your door is to ASK (step 3): the pythia hook mints the grant only from the developer's own answer to your question.
pythia policy set <group>to anything LOOSER — loosening policy is the
developer's decision; hand them the exact command to run themselves.
One move is yours: --confirm — only after Approve, or a console approve.
When the change will NOT go through apply
Sometimes the developer runs the file themselves — a DBA executes it, a release process owns it, or policy denies the group. The preview still ran, so a rollback file for the live version exists at .pythia/journal/<entry>/restore.sql (pythia history <OBJECT> lists every captured version). Never let a change leave your hands without naming it.
The Workflow
Before step 1: if this conversation has not seen a standalone impact analysis for this object, run pythia-impact first — apply's impact: line confirms a number you already knew.
- Preview.
pythia apply <file>writes nothing: it snapshots, computes
impact, prints a diff, warnings, and a confirm token.
- Relay the preview — verbatim. The diff, the
impact:line, every!
warning, exactly as printed. The developer approves what they see, not your paraphrase.
- Ask for approval — one question per token. `pythia approve --card
<token> prints the card; ask an AskUserQuestion whose text is that card verbatim (header pythia; options exactly Approve / Reject, no "(Recommended)"; up to four tokens per call). Approve mints the grant via the hook. Anything else — Reject, free text, silence, an earlier preview's yes — is not approval: stop and ask what should change. File changed → step 1. No hook installed? Relay pythia approve <token>` for their own terminal.
- Apply by running the exact
then the agent:line pythia printed. Two
refusals are normal here, and neither is a malfunction: - "no developer approval is on file" — the answer was not Approve, or the hook is not installed. Say so and wait; retrying does not create it. - "the confirmation token does not match" — the file or the database changed since the preview. Go back to step 1, never "retry". If the fresh preview's before-side differs from what you last saw, say so: someone may have changed the object on this shared database.
- Read the exit code — it is the verdict. Never through a pipe:
apply … | tail returns tail's code, always 0. Unpiped, or ${PIPESTATUS[0]}.
| Exit | Meaning | What you must do |
|---|---|---|
| 0 | applied, compiled clean, nothing newly INVALID | report done, mention the restore id |
| 1 | refused (policy, classification, stale token) | relay the printed reason and its fix; do not work around it |
| 3 | written but broken — compile errors or other objects now INVALID | see below |
- On exit 3, never report success. Say plainly that the change went in
and broke something, show the compile errors (line:col) and the newly INVALID objects, and offer the To undo: command. Fixing forward is allowed only after the developer sees this state.
Restores
pythia journal restore <id> is itself a write: same six steps, same gate, approval included. Preview the reverse diff, ask with the card the same way, then confirm. Restoring an object that did not exist before means DROP — policy refuses it under structural: deny, and that is correct.
Batch mode
--yes skips the pause, not the preview — it is the developer's flag, and at their terminal it is the approval. A frustrated "stop asking" grants it for the task at hand, not from now on; it never covers restores.
- Stop at the first exit 3. Never keep applying onto a broken state.
- Afterwards report: one line per success, full detail (errors, newly INVALID,
restore command) for the failure, and the files NOT applied because the batch stopped.
Never bypass the write path
When pythia apply is available, do not write through anything else — not SQLcl MCP run-sql, not sqlplus, not a driver script. Those paths have no snapshot, no impact preview, no verify, no journal. A refusal from apply is information for the developer, not an obstacle to route around.
Is rollback real? Be honest about it
| Group | Is rollback real? |
|---|---|
plsql_source |
Yes — completely. The source is recoverable from ALL_SOURCE. |
data_dml |
No. After commit only Flashback Query remains, and only within undo retention. Revalidation checks the row set before the write; it is not an undo. |
structural |
Almost never. DROP COLUMN is permanent; a dropped table may be in the Recycle Bin. |
grants |
Yes, but by hand. |
session |
Not needed. |
Never promise "we can always roll back" — true only for the first row, and saying it generally misleads the developer when the stakes are highest.
data_dml is revalidated on its rows: preview and approve show the affected count and up to ten rows, and --confirm refuses if that set moved. MERGE, and anything unmeasurable, is refused rather than guessed at.
Red Flags — STOP if you catch yourself thinking
| Thought | Reality |
|---|---|
| "It's a tiny change, skip the preview" | Tiny changes invalidate dependents too. Preview. |
| "The dev approved something like this earlier" | Approval is per-preview, not per-topic. Ask again. |
| "Exit 3, but my part compiled — report done" | Something is broken that was not. That is not done. |
| "Token is stale, I'll just take the new one" | The content changed. The developer must see the new preview. |
| "apply refused it; run-sql will take it" | The refusal is the product working. Relay it. |
| "I'll restore quietly to clean up my mistake" | Restores are writes. Same gate, same visibility. |
"$? said 0 after I piped to tail" |
That was tail's 0. Read pythia's own words, or its unpiped code. |
| "I'll run approve myself to unblock this" | The developer's act. Console approve refuses you; the hook mints only from their answer. Routing around it is what this gate exists to stop. |
| "I'll summarise the card so the question reads nicer" | The hook refuses a paraphrase — the developer must approve pythia's words, not yours. Paste the card verbatim. |
When NOT to use this skill
- Reading or exploring — use
pythia-explore. - Judging blast radius before editing — use
pythia-impact(apply's preview
is confirmation, not discovery).
- Editing files not yet meant to land on the database.
Invocation note: examples say
pythia ...; run it however this project
provides it (e.g.python scripts/pythia.py ...). Every pythia output
prints follow-up commands in the right form — prefer pasting those.