pythia
Oracle's MCP gives your agent a connection. pythia gives it the judgment to use it.
English · Tiếng Việt · The Complete Guide → · Website →
An Agent Skills + CLI kit for developing PL/SQL on Oracle Database with AI
coding agents (Claude Code, Codex, Cursor — any of the 70+ agents npx skills
supports).
pythia is a harness: a book of working rules an agent studies and follows when it sits next to a developer. Not a chatbot, not an autopilot — a disciplined assistant with three properties a good one has: knowledge (it asks the live schema, never its memory of one), judgment (it measures before it proposes), and obedience to rules it can recite (gates it will quote back to you rather than quietly skip).
Why ask the database instead of reading dumps
A real mid-size system, repo export vs live database, audited in 2026:
Code that "reads fine" against the dump references types and packages the dump never heard of. Every pythia command asks the live data dictionary instead — and every truncated output says so, so an agent never mistakes a partial answer for a full one.
The operating model: Learn → Ask → Do
Give the agent a problem — "add a column and every procedure that maintains it", "why does this report double rows", "port this fix" — and the kit walks it through the same three movements a careful senior developer makes.
1 · Learn — understand before proposing
The agent studies four things, in order, with tools instead of guesses:
Nothing in this phase writes. Reading is free, so the bar is: no proposal
before the blast radius is known (pythia-impact's iron law) and no line
written before the neighbours have been read (pythia-write's).
2 · Ask — the questions are part of the method, not an interruption
The kit makes the agent stop at exactly the moments where a human's judgment is the missing input, and forbids it to guess past them:
- Before any write: the full preview — diff, dependents, warnings — is relayed verbatim, and the agent waits for a real yes. A compliment is not a yes. Silence is not a yes. And the wait is no longer a matter of obedience: the write cannot complete until the developer mints the approval themselves.
- When the blast radius is large: ten or more dependents, or anything cross-schema, goes to the developer before code is written, not after.
- When sources of truth disagree: a standards document says one thing, the schema does another — that gap is a question ("rule nobody follows, new-code-only, or drift?"), never a silent pick.
- When something breaks: exit 3 means written but broken. The agent reports it exactly so, with the ready rollback — reporting success here is the one sin the whole kit is built to prevent.
- When policy refuses: the refusal is relayed as information, not routed around.
3 · Do — act inside a pipeline that cannot lie
Only after Learn and Ask does anything touch the database, and then only
through one door: snapshot → impact → preview → token → approve → apply →
verify → report. DDL self-commits in Oracle, so the snapshot is the only real
undo — it runs first and no flag disables it. A content-bound token guarantees
what lands is byte-for-byte what was previewed, and a developer-minted grant
guarantees a person approved it at all — through a door that is always the
developer's, tried in order until one works. On Claude Code first: an
AskUserQuestion whose text is the card (pythia approve --card <token>) —
Claude Code's own dialog, shown in every client (terminal, desktop, web,
phone) and never answered by a hook or an allow rule; its Approve, chosen
in the dialog and traced through the session transcript to make sure the
agent did not write it, mints the one-time grant. Next, and first on Codex:
the pythia_approve tool (MCP), pythia's own card as a select prompt. Last, at
a console: pythia approve <token>. A door that fails says so and names the
next, and the agent moves on without asking.
Without that grant apply --confirm refuses. A headless agent cannot --yes
its own writes, cannot mint approval, and cannot loosen policy — each of those
takes a human, answering or typing.
When the developer wants the agent to just work, they delegate once: an
autopilot lease — one connection, the statement groups they name, a few
hours and a write budget — approved through the same doors. Until it ends,
apply and deploy land covered statements without asking; every write is
still snapshotted, journaled and verified. A lease never covers a protected
connection, destructive statements (unless the card named them), or a file
with lint errors, and it pauses itself the moment other objects break.
pythia autopilot report lists every write with its undo.
The same discipline holds when the developer does the work: src and
impact silently snapshot what they read, so even a change made by hand in
SQL Developer has a rollback file waiting (history lists them, drift is
reported when source moved with no apply behind it).
Học – Hỏi – Làm — Learn, Ask, Do. If the agent cannot show which phase it is in, it is doing none of them.
Install
npx pythia-plsql # everything: pip install + skills picker + config scaffold + hooks
Or the same thing piecewise:
pip install pythia-plsql # the CLI (thin driver — no Oracle Instant Client needed)
python -m pythia install # skills + .pythia/ scaffold + Claude Code CLAUDE.md/hooks + Codex AGENTS.md/MCP approver
pythia check # fill in connections.json first, then verify
The pip package is the whole kit: with Node.js present, pythia install
runs npx skills add (70+ agents, symlinked updates; --source <git-url>
for internal mirrors) — without Node it copies the bundled pack itself.
Prefer global skills: pythia install -g once per machine serves every
project, and per-project installs then skip the skills step automatically —
one copy anywhere means no skill ever shows up twice in the agent's menu. Skills alone: npx skills add thaildhe172591/pythia, or /plugin marketplace add thaildhe172591/pythia.
pip install is once per machine; pythia install is once per
project — run it in each repo's root to drop the skills and a fresh
.pythia/connections.json there. The CLI always reads the config of the
project you are standing in (searched upward from the current directory,
no global fallback), so one global CLI never mixes up databases.
Updating follows the same split:
pip install --upgrade pythia-plsql # new CLI version (once per machine)
pythia install # refresh a project's skills; config never touched
(npx skills update also works for skills installed via npx.)
Running from a clone works too — python scripts/pythia.py <command>; every
printed follow-up command matches how you invoked it. Windows, macOS, Linux
and WSL are all CI-tested.
Setting up Claude Code and Codex
pythia install configures whichever of these you use — in one run, from the
project root. Here is exactly what it wires for each, and the one or two steps
each agent needs you to finish by hand.
Claude Code
-
Run
python -m pythia install(ornpx pythia-plsql) in the project root. It registers the write-free MCP approver in.mcp.jsonand merges into.claude/settings.json, keeping every key you already have there:- a SessionStart hook: the Learn → Ask → Do guide plus the project's live state (connections, autopilot lease, conventions, approval door);
- a PreToolUse guard on Bash and SQLcl
run-sql: pythia's read-only commands pass without a prompt that carries no decision, already-approved writes pass, and writes that skip the gate (sqlplus/SQLcl DDL) are denied; - PostToolUse hooks: on
AskUserQuestionit mints the approval grant; on Bash it puts the verdict rules (exit 3 is broken) next to the result; - a
denyrule so the agent cannot forge a grant, and the MCP server enabled.
It scaffolds
.pythia/connections.json(mode 600) and adds it to the project's.gitignore— commit that line — and warns if git already tracks the file: its passwords are then in the history, so untrack it and change them.It also writes the harness into
CLAUDE.md— the file Claude Code itself loads every session — in a marker-delimited block, leaving your own text untouched. The rules hold even with--no-hooks, before a restart, or while the hooks are untrusted; when the block is there, the SessionStart hook adds only the live state instead of repeating the guide. A block from an older pythia (upgraded, not yet re-installed) gets the current guide instead, with aNOTEline saying it wins untilpythia installrefreshes the block.The interpreter is chosen by what really starts pythia. A hook that cannot start fails open — Claude Code treats its error as non-blocking — so a bare
python -m pythiagoes into the shared files only when it imports pythia outside a virtual environment. Installed in a venv, with pipx, or wherepythonis another interpreter, the hooks go to.claude/settings.local.json(this machine's file, kept out of git) with the absolute interpreter, and the MCP approver to Claude Code's local scope (claude mcp add --scope local); your teammates never get a path from your machine. Every command pythia prints uses the spelling that starts it here, the session preamble'sRUNline names it for the agent (CLAUDE.md, being shared, can only saypythia), and the guard lets reads through in that spelling — this interpreter's own path, never another. starts every launcher it finds and fails the ones that cannot import pythia — except where this machine's own launcher already serves the same hooks: then the team's shared copy erroring here is a warning (keep it for teammates or remove it). Codex's session hook is a warning too: it carries only the session guide, and AGENTS.md the rules.
Approving a change: the agent previews with pythia apply, asks an
AskUserQuestion carrying the card, you click Approve, and the agent runs
apply --confirm itself. If that dialog's answer does not count (it closed
while you were away, or the hook could not trace it), the agent goes on
unasked to the pythia_approve tool, then to pythia approve <token> for your
terminal.
Working autonomously: say so. The agent requests a lease
(pythia autopilot request), you approve its card once, and it builds —
tables, comments, packages, releases — without stopping for each write.
acceptEdits mode plus a lease is the fully hands-off setup.
Codex
-
Run
python -m pythia installin the project root. It:- writes the harness into
AGENTS.md(Codex loads it every session); - registers a write-free MCP approver in
.codex/config.toml; - adds a SessionStart guide hook in
.codex/hooks.json.
Skills land in
.agents/skills(or~/.agents/skillswith-g) — the directory Codex reads. - writes the harness into
-
In Codex, trust the pieces once:
/mcp(thepythiaserver) and/hooks(the project.codexlayer). -
Restart the Codex session so the new
AGENTS.mdloads. -
Check it:
/skillslists the pythia skills,/mcpshowspythia: connected.
Needs Codex ≥ v0.120 for the in-chat approval (MCP elicitation); an older
or headless Codex — or any host that declines the prompt without showing it,
which pythia tells apart by time — falls back to the console pythia approve <token>, and the agent says so without being asked. Do
not run Codex under danger-full-access — that sandbox auto-approves
elicitations and bypasses your Approve.
Approving a change: the agent previews with pythia apply, calls the
pythia_approve tool, you answer a select Approve / Reject prompt right in
Codex, and on Approve the agent runs apply --confirm itself.
Commands
Every command takes --json (machine output), --conn (pick a connection), and
caps output with explicit truncation markers so context windows stay intact.
The safety net covers hand edits too: src and impact snapshot the
object silently into the journal, each with a runnable rollback file, so a
change made later in SQL Developer still has something to go back to —
pythia history <OBJECT> lists the versions. Source that moved with no
apply behind it is reported as drift.
Your house style is config, not folklore: put naming patterns in
.pythia/conventions.json and apply previews warn when a new object's name
drifts; put the prose rules in .pythia/conventions.md and the skills make
every agent read them first (pythia conventions shows both).
Security & write policy
The account is the real security layer — the policy file is an application-side
fence. Give the agent its own revocable credential with proxy authentication
(agent_user[schema_owner], no ANY privileges, no owner password shared):
pythia agent-user --save # SQL for the DBA + matching credential saved as <conn>_agent
pythia check # after the DBA ran it: proxy session, warning gone
One run does both — the password is regenerated each run, so the SQL and the
saved config must come from the same run. Optional convenience: doing it by
hand with
examples/agent-user-setup.example.sql
works just as well.
pythia check warns when the session runs with more power than the task needs.
The developer approval that apply requires is minted per agent — Claude
Code's AskUserQuestion hook first, then the pythia_approve MCP tool (where
Codex starts), then the console pythia approve <token> anywhere. How each is wired is in
Setting up Claude Code and Codex above.
Per-group write policy, .pythia/policy.json (defaults shown):
The groups that cannot be snapshotted default to deny — and the refusal says that,
instead of "policy forbids it". Anonymous PL/SQL blocks are refused outright.
Unrecognized statements are refused, never guessed into a group.
Threat model and what a snapshot does not restore: SECURITY.md.
Reads may flow through Oracle's official SQLcl MCP server (sql -mcp, keep -R 4);
it audits every interaction in DBTOOLS$MCP_LOG. Writes never do — only
pythia apply has the snapshot, preview, verify and journal.
Skills
The skills teach the agent the workflow — gates, not suggestions:
- The method:
using-pythia(the router) ·pythia-spec(open decisions are asked, not assumed) ·pythia-explore·pythia-impact(before any change) ·pythia-verify(evidence before "done"). - Building on Oracle:
pythia-table(tables, columns, constraints, indexes, every column commented) ·pythia-package(packages, procedures, functions, triggers to industry-standard design) ·pythia-write(copy the codebase's conventions) ·pythia-comment·pythia-tune(measure, change one thing, measure again) ·pythia-review(antipatterns). - Landing it:
pythia-apply(the gate) ·pythia-deploy(release scripts, in order, one approval) ·pythia-autopilot(one delegated lease instead of every approval). - The team's own way:
pythia-conventions(the LEAD's naming and lint rules, verified against real names) ·pythia-skill-author(capture a workflow as a skill;pythia skill new/skill lint) ·pythia-setup(connections, agent user,pythia doctor, a robust dev environment).
Compatibility
Who is using pythia
Nobody is listed yet. Using pythia on a real codebase? Open a pull request that adds a line here: your team or project, and what you use pythia for.
Star History
Contributing
Tests need no database — the fakes prove the safety properties (snapshot before write, deny touches nothing, stale tokens refused). See CONTRIBUTING.md.
MIT — see LICENSE.