DEV Community

Rahul
Rahul

Posted on

Readme

#architecture #fastapi #llm #react

PLSQL Insight React

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.

Architecture 

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
Enter fullscreen mode Exit fullscreen mode

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.

  • Personal development may use the existing optional SQLite repository adapter.
  • Office deployment should use the dedicated Oracle AI metadata schema.
  • Switching repository, Oracle, Chroma, or model endpoints is configuration-only.

Product experience

  • Premium responsive application shell and command palette
  • PL/SQL-aware editor with line numbers, folding, highlighting, and bracket matching
  • Paste, upload, sample, and allowlisted Oracle input modes
  • Live stage-level agent workflow telemetry and cancellation
  • Evidence-rich result views for workflow, subprograms, data operations, dependencies, warnings, and raw metadata
  • Human approval/rejection workflow for generated knowledge
  • Table intelligence and Oracle object catalogue
  • Interactive React Flow dependency graph with automatic layout, minimap, zoom controls, relationship labels, and type-specific nodes
  • Runtime health, installed Ollama models, model routing, and processing-location visibility
  • Keyboard focus states, reduced-motion support, and responsive layouts

Analyst and Admin modes

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.

Repository layout 

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
Enter fullscreen mode Exit fullscreen mode

Requirements

  • Python 3.12 or newer
  • Node.js 22.13 or newer
  • Ollama locally or a reachable organisation-hosted Ollama endpoint
  • Oracle Client libraries only when your Oracle configuration requires thick mode

Local installation on Windows 

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
Enter fullscreen mode Exit fullscreen mode

The initializer never overwrites an existing .env.

Convenience equivalent:

.\scripts\dev.ps1 install
.\scripts\dev.ps1 init
Enter fullscreen mode Exit fullscreen mode

Ollama models 

ollama pull qwen2.5-coder:7b
ollama pull deepseek-r1:7b
ollama pull nomic-embed-text
ollama list
Enter fullscreen mode Exit fullscreen mode

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 locally

Start both services in the background:

.\scripts\start.ps1
Enter fullscreen mode Exit fullscreen mode

Then open:

  • React application: http://127.0.0.1:3000
  • API documentation: http://127.0.0.1:8000/docs

Stop the application:

.\scripts\stop.ps1
Enter fullscreen mode Exit fullscreen mode

Or run in separate terminals:

# Terminal 1
cd backend
..\.venv\Scripts\python.exe -m uvicorn app.main:app --host 127.0.0.1 --port 8000 --reload

# Terminal 2, from repository root
npm run dev
Enter fullscreen mode Exit fullscreen mode

Office-device setup

Copy this entire folder to the office device. No source-code changes are required.

  1. Install Python 3.12+ and Node.js 22.13+.
  2. Create the Python environment and run npm ci using the installation commands above.
  3. Copy .env.example to .env.
  4. Copy .env.local.example to .env.local.
  5. Configure the dedicated Oracle metadata schema: 
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
Enter fullscreen mode Exit fullscreen mode
  1. Set a strong application admin password: 
ADMIN_API_KEY=<load-a-long-random-password-from-the-approved-secret-store>
ADMIN_AUTH_HEADER=X-Admin-Key
Enter fullscreen mode Exit fullscreen mode

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.

  1. Point the backend at the organisation Ollama server: 
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
Enter fullscreen mode Exit fullscreen mode

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.

  1. Configure persistent Chroma. For a shared approved Chroma service: 
CHROMA_MODE=http
CHROMA_HOST=chroma-server.internal
CHROMA_PORT=8000
CHROMA_SSL=true
Enter fullscreen mode Exit fullscreen mode
  1. Configure the UI/API origin. If both run on the same office device: 
CORS_ALLOWED_ORIGINS=http://127.0.0.1:3000,http://localhost:3000
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

The frontend setting contains only the header name, not the password.

  1. Run the Oracle DDL only in the dedicated AI metadata schema: 
# Use your approved Oracle client:
# @scripts/create_oracle_tables.sql
Enter fullscreen mode Exit fullscreen mode
  1. Verify connections and start: 
.\.venv\Scripts\python.exe .\scripts\verify_connections.py
.\scripts\start.ps1
Enter fullscreen mode Exit fullscreen mode

The office configuration uses Oracle for structured metadata; SQLite is not required there.

Quality commands 

# React
npm run typecheck
npm run lint
npm test

# Backend
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
Enter fullscreen mode Exit fullscreen mode

Security controls

  • Submitted source is parsed as text and never executed or compiled.
  • Oracle owners are allowlisted and SQL uses bind variables.
  • Source owners remain read-only; only the AI metadata schema is writable.
  • Analyst analysis is transient and cannot write to the metadata schema or Chroma.
  • Admin write endpoints require the server-verified admin password.
  • Production startup is blocked by default.
  • File types, size, encoding, line count, names, and NUL bytes are validated.
  • Logs redact credential-like values and source logging is disabled by default.
  • Potential secrets are detected before model calls.
  • Current source and deterministic facts outrank retrieved or generated context.
  • AI-generated knowledge is not human-approved automatically.
  • Rejected and thumbs-down content is excluded from promoted retrieval.
  • Chroma is a rebuildable semantic index, not the structured source of truth.

Important limitations

  • A quick UI preview can run with the backend offline, but analysis requires FastAPI and all configured services.
  • Live Oracle validation requires an approved DEV/SIT/UAT environment.
  • Fully dynamic SQL targets may remain unresolved when the object name is assembled entirely at runtime.
  • The shared admin password is intentionally a small internal-deployment control. Replace it with organisation SSO and per-user authorization before broad multi-user rollout.
  • Use HTTPS/TLS, central secrets, malware scanning, log forwarding, retention, and capacity controls for an office deployment.

See Oracle deployment, security, agent workflow, and retrieval for deeper operational guidance.

Top comments (0)