PLSQL Insight is a private, evidence-grounded application for understanding Oracle PL/SQL. This repository is the React edition: a modern React 19 interface over the existing FastAPI, LangGraph, Ollama, Oracle, Chroma, parser, retrieval, and auditing implementation.
It accepts pasted source, uploaded .sql
/.pkb
/.pks
/.txt
files, approved lower-environment Oracle objects, optional supporting documents, and bulk Oracle ingestion. Submitted PL/SQL is never compiled or executed.
This project does not use Streamlit, Spring, Docker, or a frontend database.
flowchart LR
UI[React 19 + TypeScript] --> API[FastAPI]
API --> G[Typed LangGraph workflow]
G --> P[Lexical parser + deterministic facts]
G --> R[Exact + Chroma semantic retrieval]
G --> Q[Qwen analysis agents]
G --> D[DeepSeek audit + merge]
P --> M[(Oracle metadata schema or optional local SQLite)]
R --> M
R --> C[(Persistent Chroma)]
Q --> O[Ollama / internal model endpoint]
D --> O
The React application is stateless. Analyst submissions and results remain
transient. Durable knowledge, reviews, feedback, Oracle ingestion, and
dependency edges remain behind repository interfaces in the backend and are
available only in Admin mode.
The application has two deliberately simple access modes:
| Mode | Intended use | Persistent writes |
|---|---|---|
| Analyst | Paste or upload PL/SQL and receive its summary, workflow, dependencies, warnings, and other analysis details | None |
| Admin | Ingest approved Oracle objects, add supporting knowledge, publish generated context for review, approve or reject context, submit feedback, and run maintenance | Yes, to the configured metadata store and Chroma |
Everyone starts in Analyst mode. Choose Admin access in the React header and
enter the admin password only when an administrative action is required. The
browser keeps that password in memory for the current page session; it is not
stored in browser storage.
The environment variable is named ADMIN_API_KEY
, but its value is simply the
application's backend admin password. FastAPI compares it locally before
allowing a protected endpoint. This comparison does not call Ollama, add prompt
tokens, or change the analysis workflow, so it adds no meaningful LLM or
processing load.
ADMIN_API_KEY
and OLLAMA_API_KEY
are separate:
ADMIN_API_KEY
controls who may change application data.OLLAMA_API_KEY
lets FastAPI authenticate to an office-hosted Ollama gateway.
plsql-insight-react/
├── app/ React application, components, styles, and API client
├── backend/
│ ├── app/ FastAPI, agents, workflow, parser, retrieval, repositories
│ └── tests/ parser, metadata, storage, workflow, API, and security tests
├── scripts/ setup, start/stop, ingestion, Chroma, and Oracle DDL
├── sample_data/ synthetic PL/SQL including a 2,000+ line package
├── docs/ architecture, security, retrieval, and deployment guidance
├── .env.example backend configuration
├── .env.local.example React API endpoint configuration
├── package.json React build and quality commands
└── Makefile
cd "C:\path\to\plsql-insight-react"
python -m venv .venv
.\.venv\Scripts\python.exe -m pip install --upgrade pip
.\.venv\Scripts\python.exe -m pip install -e ".\backend[dev]"
npm ci
Copy-Item .env.example .env
Copy-Item .env.local.example .env.local
.\.venv\Scripts\python.exe .\scripts\init_local.py
The initializer never overwrites an existing .env
.
Convenience equivalent:
.\scripts\dev.ps1 install
.\scripts\dev.ps1 init
ollama pull qwen2.5-coder:7b
ollama pull deepseek-r1:7b
ollama pull nomic-embed-text
ollama list
Default model routing:
| Responsibility | Model |
|---|---|
| Metadata review | qwen2.5-coder:7b |
| Procedure context | qwen2.5-coder:7b |
| Procedure analysis | qwen2.5-coder:7b |
| Table context | qwen2.5-coder:7b |
| Package context | deepseek-r1:7b |
| Independent auditor | deepseek-r1:7b |
| Final merge | deepseek-r1:7b |
| Embeddings | nomic-embed-text |
Configured model names must exist on the target Ollama server. The backend does not silently substitute unrelated models.
Start both services in the background:
.\scripts\start.ps1
Then open:
http://127.0.0.1:3000
http://127.0.0.1:8000/docs
Stop the application:
.\scripts\stop.ps1
Or run in separate terminals:
cd backend
..\.venv\Scripts\python.exe -m uvicorn app.main:app --host 127.0.0.1 --port 8000 --reload
npm run dev
Copy this entire folder to the office device. No source-code changes are required.
npm ci
using the installation commands above..env.example
to .env
..env.local.example
to .env.local
.
APP_ENVIRONMENT=uat
APP_MODE=oracle
METADATA_DB_BACKEND=oracle
ORACLE_ENABLED=true
ORACLE_USER=plsql_ai
ORACLE_PASSWORD=<load-from-approved-secret-store>
ORACLE_DSN=approved-host:1521/APPDEV
ORACLE_AI_SCHEMA=PLSQL_AI
ORACLE_ALLOWED_OWNERS=APP_OWNER,REFERENCE_OWNER
ADMIN_API_KEY=<load-a-long-random-password-from-the-approved-secret-store>
ADMIN_AUTH_HEADER=X-Admin-Key
Do not reuse the Oracle or Ollama credential. Keep this value only in the
server-side .env
or approved secret store; never put it in .env.local
or
commit it. Staff enter the same value into the React Admin access dialog
when they need administrative functions. Serve the office application over
HTTPS so the credential is encrypted in transit.
OLLAMA_BASE_URL=https://ollama-server.internal
OLLAMA_API_KEY=<load-from-approved-secret-store>
OLLAMA_AUTH_HEADER=Authorization
OLLAMA_AUTH_SCHEME=Bearer
MODEL_METADATA_REVIEW=qwen2.5-coder:7b
MODEL_PROCEDURE_CONTEXT=qwen2.5-coder:7b
MODEL_PROCEDURE_ANALYSIS=qwen2.5-coder:7b
MODEL_TABLE_CONTEXT=qwen2.5-coder:7b
MODEL_PACKAGE_CONTEXT=deepseek-r1:7b
MODEL_AUDITOR=deepseek-r1:7b
MODEL_FINAL_MERGE=deepseek-r1:7b
EMBEDDING_MODEL=nomic-embed-text
The API key is read only by FastAPI and is never sent to the React browser. If
your office gateway expects X-API-Key: <key>
instead, set
OLLAMA_AUTH_HEADER=X-API-Key
and leave OLLAMA_AUTH_SCHEME=
empty. The same
credential is applied to model discovery, structured generation, and
embeddings.
CHROMA_MODE=http
CHROMA_HOST=chroma-server.internal
CHROMA_PORT=8000
CHROMA_SSL=true
CORS_ALLOWED_ORIGINS=http://127.0.0.1:3000,http://localhost:3000
For a network URL, add that exact frontend origin and set .env.local
:
VITE_API_URL=http://office-device-hostname:8000
VITE_ADMIN_AUTH_HEADER=X-Admin-Key
The frontend setting contains only the header name, not the password.
.\.venv\Scripts\python.exe .\scripts\verify_connections.py
.\scripts\start.ps1
The office configuration uses Oracle for structured metadata; SQLite is not required there.
npm run typecheck
npm run lint
npm test
cd backend
..\.venv\Scripts\python.exe -m ruff check app tests ..\scripts
..\.venv\Scripts\python.exe -m mypy app
..\.venv\Scripts\python.exe -m pytest
See Oracle deployment, security, agent workflow, and retrieval for deeper operational guidance.