docs

metadata extraction.md

Metadata extraction

The parser first masks single-line comments, block comments, string literals, and quoted identifiers while preserving every character position and newline. Token positions therefore map back to exact original lines.

Subprogram discovery tracks signatures, parameter parentheses, IS/AS, nested local subprograms, outer BEGIN, and block stacks for BEGIN/END, IF/END IF, LOOP/END LOOP, and CASE/END CASE. Package specs, bodies, overload keys, parent subprograms, visibility, return types, and initialization blocks are recorded.

When a body cannot be balanced, bounded character chunks with line-safe boundaries and overlap preserve all source. These are marked FALLBACK_CHUNK, include warnings, and reduce confidence.

The extractor identifies parameter modes/defaults, function returns, SELECT/INSERT/UPDATE/DELETE/MERGE targets, aliases, database links, selected/filter/join/insert/update/group/order columns, calls, cursors, standard and named exceptions, raises, transactions, constants, status/process values, important conditions, dynamic SQL, sequences, and enterprise package use. Regex is used for focused patterns after lexical masking; it is not the package splitter.

Evidence contains current-source line numbers and short excerpts. Deterministic facts win any conflict with model output.

architecture.md

Architecture

PLSQL Insight separates authoritative extraction from probabilistic enrichment. React is a stateless product client. FastAPI owns validation, jobs, workflow execution, repositories, and administration. A typed LangGraph graph makes every stage and transition observable.

flowchart TD
    Intake[Source intake] --> Parser[Lexical state-machine parser]
    Parser --> Facts[Deterministic metadata extractor]
    Facts --> Exact[Exact structured retrieval]
    Facts --> Semantic[Chroma semantic retrieval]
    Exact --> Fusion[Priority fusion and context budget]
    Semantic --> Fusion
    Fusion --> Context[Procedure context generator]
    Context --> Analysis[Procedure analysis]
    Analysis --> Audit[Independent auditor]
    Audit -->|NEEDS_REVISION and below cap| Revision[Revision]
    Revision --> Audit
    Audit -->|PASS or cap reached| Package[Package context, table knowledge, merge]
    Package --> Persistence[Validated deterministic persistence]

The database interface bundle selects SQLite or Oracle once at startup. The UI,
workflow, domain schemas, retrieval service, and agents do not branch on the
backend. Analyst runs use bounded in-process transient storage and cannot write
the structured repository or Chroma. Admin runs and ingestion may persist
validated data. Chroma contains only rebuildable vectors and scalar metadata;
SQLite or Oracle contains durable versions, facts, contexts, reviews, feedback,
runs, ingestion state, and graph edges.

LLM agents cannot access repositories. They receive bounded prompt data and return Pydantic-validated structures. Python attaches authoritative parameters, table operations, calls, cursors, transactions, exceptions, evidence, and claim types.

Background work uses a bounded executor. Analysis runs store stage events, final status, errors, and cancellation state. Bulk ingestion uses hashes, versions, skips, resume cursors, partial failures, and per-object errors.

Admin authentication is intentionally outside the LLM workflow. FastAPI checks
the configured ADMIN_API_KEY before privileged endpoints; this adds no model
request, token use, retrieval work, or agent stage. OLLAMA_API_KEY is a
separate outbound credential for the model gateway.

oracle deployment.md

Oracle deployment

Use DEV, SIT, UAT, or another approved lower environment. Create a dedicated metadata schema, apply scripts/create_oracle_tables.sql, and grant it only the DML needed on its own AI_* tables. The source-reading account needs select access to approved dictionary views and owners. It does not need EXECUTE, compilation, source-object DML, schema DDL, or production access.

Set METADATA_DB_BACKEND=oracle, ORACLE_ENABLED=true, ORACLE_AI_SCHEMA, DSN, secret-managed credentials, and a narrow ORACLE_ALLOWED_OWNERS list. All dictionary queries bind owner, name, and type. Owner identifiers are validated against the allowlist; the metadata schema is validated as an unquoted identifier.

Configure ADMIN_API_KEY with a long random value from the approved secret
store and keep ADMIN_AUTH_HEADER=X-Admin-Key. The React user enters this value
through Admin access when durable actions are needed. Never put the value in
the frontend .env.local; VITE_ADMIN_AUTH_HEADER contains only the
matching header name. Use HTTPS between the browser and FastAPI.

Analysts can submit source and read the returned analysis, but their runs are
transient and cannot write Oracle metadata or Chroma. Admin mode is required
for Oracle ingestion, supporting knowledge, context publication and review,
feedback, and maintenance.

Bulk ingestion discovers allowed objects, fetches ALL_SOURCE in line order, hashes source, skips unchanged objects, versions changes, parses/extracts facts, analyzes procedures, generates package/table contexts, refreshes vectors, and records progress/errors. Retry uses saved state; begin with one object. Bulk ingestion is an Admin-only action.

Point Ollama/model variables to organisation-hosted approved names. If the embedding model changes, run scripts/migrate_embedding_model.py <model> so incompatible vector dimensions never coexist.

The application admin password and Ollama gateway key are unrelated.
ADMIN_API_KEY is checked locally by FastAPI and causes no LLM call;
OLLAMA_API_KEY is used by FastAPI only when it connects to the organisation
model gateway.

Chroma can remain path-backed with CHROMA_MODE=persistent, or connect to an approved server with CHROMA_MODE=http and the CHROMA_HOST, CHROMA_PORT, CHROMA_SSL, CHROMA_TENANT, and CHROMA_DATABASE settings. No application code changes are required.

The production safety gate intentionally rejects APP_ENVIRONMENT=production while BLOCK_PRODUCTION=true unless the explicit approved override is provided. Production is outside the default threat and deployment model.

localsetup.md

Local setup

Use Python 3.12+, Ollama, and enough disk for models and Chroma. Run the PowerShell commands in the README or scripts/dev.ps1 install, scripts/dev.ps1 init, then pull the three models.

Start FastAPI and the React frontend in separate terminals, or use scripts/start.ps1. Confirm /api/health, /api/system/status, and the React System Status page. Analyze sample_data/small_procedure.sql, then the 2,248-line sample. Seed deterministic facts without invoking models using scripts/seed_demo_data.py.

SQLite is stored at data/plsql_ai.db; Chroma is stored under data/chroma. Both paths are configurable. Delete neither while services are running. Use the rebuild script to recreate every vector collection from structured storage. For an approved Chroma server, set CHROMA_MODE=http plus CHROMA_HOST, CHROMA_PORT, CHROMA_SSL, tenant, and database settings.

If Ollama is offline, API and UI startup still work and show the outage. Live analysis fails clearly at the first configured model check. Tests remain runnable because their LLM dependency is explicit and mocked.

Local Ollama is optional. To use an office-hosted endpoint instead, configure
OLLAMA_BASE_URL and load OLLAMA_API_KEY from the approved secret store. The
default sends it as a bearer token; OLLAMA_AUTH_HEADER and
OLLAMA_AUTH_SCHEME support gateways that require a different API-key header.

For local UI testing, set a strong ADMIN_API_KEY in the ignored .env file
and keep ADMIN_AUTH_HEADER=X-Admin-Key. Open the React application in Analyst
mode for normal analysis. Use Admin access only to test data ingestion,
knowledge review, persistence, feedback, or maintenance. The admin password
check is a local backend comparison: it does not call Ollama or consume model
tokens.

Do not place the password in .env.local. That file contains only the public
API URL and the non-secret header name:

VITE_API_URL=http://127.0.0.1:8000
VITE_ADMIN_AUTH_HEADER=X-Admin-Key

Analyst submissions are transient and do not write to SQLite, Oracle, or
Chroma. Admin mode is the only mode that enables durable application writes.

agent workflow.md

Agent workflow

The compiled LangGraph contains these named nodes: source intake; parser and chunking; deterministic metadata extractor; metadata reviewer; exact retrieval; semantic retrieval; retrieval fusion and ranking; procedure context generator; procedure analysis; auditor; revision; package context generator; table knowledge; package merge; and persistence.

State is a typed dictionary containing immutable source, parsed chunks, authoritative metadata, version identifiers, retrieval items, generated contexts, procedure analyses, audit results, revision count, table knowledge, and final package output.

The metadata reviewer may only suggest omissions or ambiguity. Suggestions are stored in workflow state and never overwrite facts. Each procedure receives its exact source range, facts, parent package metadata, and ranked support. The auditor independently compares output with those inputs. MAX_AUDIT_REVISIONS bounds the loop. Results that still need revision at the cap retain warnings and reduced confidence.

Package generation occurs after all procedure audits. The final merge synthesizes entry points, end-to-end flow, relationships, shared state, consolidated table use, risks, and unknowns. It is not a concatenation.

Workflow tests inject an interface-compatible deterministic LLM double. Runtime construction always uses the real Ollama client.