Skip to content
BranchesTags

Repository files navigation

Nexus Intelligence

Relational Database Intelligence Platform — Schema Analysis, Quality Scoring & AI Governance

Python Streamlit FastAPI React Gemini License

Drop in any relational database — SQLite, CSV bundle, or live enterprise DB. Get a complete schema map, quality audit, ER diagram, data dictionary, and AI governance brief in seconds.

The Problem

Understanding an unfamiliar relational database is slow and manual:

  • Analysts spend hours tracing foreign keys and building ER diagrams by hand
  • Data quality issues — nulls, duplicates, stale data — are discovered late and inconsistently
  • Documentation is written once and goes stale immediately
  • Governance artifacts (audit trails, data dictionaries) are an afterthought

There is no single tool that gives you a complete, explainable intelligence report on a database in one run — with relationship confidence, semantic enrichment, and an immutable audit trail baked in.

Core Capabilities

1. Multi-Mode Ingestion

  • SQLite — .db, .sqlite, .sqlite3 via file upload or CLI path
  • CSV bundle — upload multiple CSVs; system infers inter-table relationships automatically
  • Live enterprise DB — MySQL, PostgreSQL, SQL Server via SQLAlchemy connection URLs
    • Connect-first gating: connection is validated with actionable error diagnostics before any analysis runs
    • Error messages distinguish between auth failures, network errors, and missing drivers

2. Schema Intelligence Engine

  • Candidate primary key detection across all tables
  • Explicit FK confidence locked at 1.0 — parsed directly from PRAGMA foreign_key_list for SQLite
  • Inferred relationship scoring — value-overlap ratio between column pairs; scored 0.0–1.0 proportionally
  • Semantic column labeling — each column classified as one of: identifier, financial_metric, time_dimension, pii, categorical_attribute, geospatial_attribute, descriptive_text

3. Data Quality Scoring — Explainable Formula

Quality Scoring Formula

  • Base score: (completeness × 0.5) + (consistency × 0.5) per table
  • Temporal regularity adjustment (±10 pts):
    • Cadence regularity score = max(0, 1 - min(CoV, 2.0) / 2.0) where CoV is coefficient of variation on inter-row gaps
    • Cadence bonus = (regularity_score − 0.5) × 10.0 (up to +5 pts for perfectly stable data)
    • Stoppage penalty = min(8.0, max(0, lag_ratio − 1.75) × 1.5) (up to −8 pts for data that has stopped flowing)
    • Final adjustment clamped to [−10, +10]
  • User-selectable expected cadence (value + unit) in the Data Quality tab drives the stoppage check
  • All contributions are decomposed per-column in the UI — no black box scoring

4. Semantic Layer — Metadata-Driven Intelligence

Semantic Layer

  • Versioned semantic config persisted at semantic_layer.json — survives across analysis runs
  • Entity mapping — tables resolved to business entities (customer, order, product, payment, seller) via configurable alias lists
  • Role classification — columns classified as business_key, foreign_key, measure, event_time, status, pii, descriptor
  • Constraint engine — 5 constraint types enforced on every run:
Constraint Type What It Checks
role_presence Asserts that a required role (e.g. event_time) exists in the table
role_null_threshold Flags business_key columns exceeding N% null rate
column_regex Validates column values match a pattern (e.g. email format)
allowed_values Validates status columns use only known state values
column_range Enforces numeric bounds on measure columns
  • One-click auto-lock — promotes high-confidence suggestions directly into column_overrides
  • Run-to-run semantic drift tracking — confidence delta, violation delta, ambiguity delta compared across analysis runs; surfaced in the Semantic Layer tab

5. ER Diagram Rendering

  • Native HTML/SVG renderer embedded in Streamlit — zero Mermaid runtime dependency in the UI path
  • Orthogonal (rigid) edge routing with per-entity and per-pair lane offsets — eliminates overlapping edges without a physics engine
  • Domain-aware table ordering (sales → core → production) — groups related entities spatially
  • View modes: full (all columns) or keys (PKs and FKs only); layout directions: LR (left-right) or TB (top-bottom)
  • Mermaid .mmd retained as a separate export artifact for downstream tooling

6. Data Dictionary with Inline Editing

  • Column-level semantic labels, data types, null rates, sample values, generated descriptions, quality notes
  • Live edits via st.data_editor — changes saved explicitly with a user-provided change note
  • Change history persisted to schema_change_history.jsonl — append-only, each entry timestamped
  • MySQL DDL provisioning from the edited dictionary — the Exports tab generates and runs CREATE TABLE DDL directly against a target MySQL instance

7. AI Copilot — Ollama or Gemini

  • Provider selection at runtime: Ollama (local, free) or Gemini API (cloud)
  • Connection test uses the models-list endpoint — avoids burning generation quota on misconfigured keys
  • Automatic model fallback chain for Gemini: selected_model → gemini-1.5-flash → gemini-1.5-flash-8b → gemini-1.5-pro → gemini-2.0-flash — graceful 404 recovery
  • Explicit guidance surfaced for 429 rate-limit responses vs 404 model-not-found errors
  • Exponential backoff retry on transient failures
  • Output: plain-language executive brief summarising schema structure, quality risk concentration, and recommended actions

8. Governance Exports & Immutable Audit Ledger

  • JSON analysis export (full pipeline output)
  • CSV relationship export (table, related_table, confidence, inference_type)
  • Native SVG ER export (primary artifact); PNG optional
  • Mermaid .mmd export (for external tooling)
  • dbi_audit_ledger.json — SHA-256 content hash committed on every analysis run; append-only, tamper-evident; each entry records timestamp, source fingerprint, table count, and quality summary

9. CLI Interface — Full Parity with UI

  • analyze — CSV bundle, SQLite file, or live DB connection; optional --ai-brief and --audit-commit flags
  • models — Ollama model discovery
  • Fully scriptable; outputs structured JSON artifacts to --out-dir

Architecture

graph TD
    A[Data Sources] --> B[nexus/ Analysis Engine]

    A1[SQLite .db] --> A
    A2[CSV Bundle] --> A
    A3[Live DB\nMySQL / PostgreSQL / SQL Server] --> A

    B --> C1[schema.py\nPK/FK inference + confidence scoring]
    B --> C2[profiling.py\nQuality scoring: completeness + consistency + temporal]
    B --> C3[semantic.py\nEntity mapping, constraint engine, drift tracking]
    B --> C4[visualization.py\nSVG/HTML ER renderer, Mermaid exporter]
    B --> C5[ai.py\nOllama + Gemini providers, retry/fallback chain]
    B --> C6[audit.py\nSHA-256 append-only ledger commits]

    C1 & C2 & C3 & C4 & C5 & C6 --> D[Serving Layer]

    D --> D1[app.py\nStreamlit UI]
    D --> D2[api.py\nFastAPI REST backend]
    D --> D3[nexus_cli.py\nTerminal CLI]

    D2 --> E[nexus-ui/\nReact + Vite frontend]
Loading

API Reference

Endpoint Method Description
/api/analyze POST Run full analysis on uploaded file(s) — returns schema, quality, relationships, dictionary
/api/semantic/config GET / PUT Read or update the versioned semantic layer config
/api/semantic/validate POST Validate semantic config against the currently loaded schema
/api/semantic/suggest GET Confidence-ranked semantic mapping suggestions
/api/semantic/ambiguities GET List unresolved semantic conflicts across tables
/api/semantic/overrides/apply POST Auto-lock high-confidence suggestions into column_overrides
/api/semantic/drift GET Run-to-run semantic drift report (confidence Δ, violations Δ, ambiguities Δ)

Project Structure

burplefolk/
├── app.py                        # Streamlit UI entrypoint (8 tabs)
├── api.py                        # FastAPI REST backend
├── nexus_cli.py                  # CLI: analyze / models / audit
├── semantic_layer.json           # Versioned semantic metadata contract
├── dbi_audit_ledger.json         # SHA-256 append-only audit snapshots
├── scripts/                      # Utility scripts (benchmark, test data gen, setup)
├── requirements.txt
├── assets/
│   ├── quality_scoring.png       # Quality formula diagram
│   └── semantic_layer.png        # Semantic layer concept diagram
├── nexus/
│   ├── schema.py                 # PK/FK inference, overlap scoring, semantic labeling
│   ├── profiling.py              # Quality scoring (completeness, consistency, temporal cadence)
│   ├── semantic.py               # Entity mapping, constraint validation, drift tracking
│   ├── visualization.py          # SVG/HTML ER renderer, orthogonal routing, Mermaid export
│   ├── ai.py                     # Ollama + Gemini providers, model fallback chain, retry
│   ├── analysis.py               # Pipeline orchestration
│   ├── ingestion.py              # Source loading (SQLite, CSV, DB)
│   ├── provisioning.py           # MySQL DDL provisioning from data dictionary
│   └── audit.py                  # SHA-256 audit ledger management
└── nexus-ui/                     # React + Vite frontend

Tech Stack

Layer Technology
Primary UI Streamlit ≥1.32
Backend API FastAPI, Uvicorn
Frontend React (Vite)
Data Engine Python 3.10+, Pandas, NumPy
ER Rendering Native HTML/SVG (custom orthogonal router); Mermaid (export only)
AI — Local Ollama (llama3.1 validated)
AI — Cloud Google Gemini API (gemini-2.0-flash default; auto-fallback chain)
Enterprise DB SQLAlchemy (MySQL / PostgreSQL / SQL Server)
Auth Firebase Email/Password
Governance SHA-256 audit ledger, JSONL change history

Getting Started

Install

python -m venv .venv
.venv\Scripts\activate
pip install -r requirements.txt

Run — Streamlit (recommended)

streamlit run app.py
# → http://localhost:8501

Run — Full Stack (FastAPI + React + Streamlit)

# Terminal 1: API backend
python api.py             # → http://localhost:8000

# Terminal 2: React UI
cd nexus-ui
npm run dev               # → http://localhost:5173

# Terminal 3: Streamlit
streamlit run app.py      # → http://localhost:8501

CLI Usage

# CSV bundle — with AI brief
python nexus_cli.py analyze \
  --source csv \
  --csv orders.csv customers.csv payments.csv \
  --out-dir outputs/run1 \
  --ai-brief --audit-commit

# SQLite
python nexus_cli.py analyze \
  --source sqlite --sqlite enterprise_demo.db \
  --out-dir outputs/sqlite_run

# Live MySQL
python nexus_cli.py analyze \
  --source db --db-type mysql \
  --host localhost --port 3306 \
  --database mydb --username root --password secret \
  --out-dir outputs/mysql_run

# List available Ollama models
python nexus_cli.py models

Generate Synthetic Test Datasets

# Generates: clean bundle, quality-issues bundle, schema-issues bundle, SQLite demo
python scripts/generate_test_datasets.py --out outputs/test_scenarios --rows 60000

Environment Variables

# AI Copilot
OLLAMA_ENDPOINT=http://localhost:11434
GEMINI_API_KEY=...

# Firebase Auth (optional)
FIREBASE_API_KEY=...
FIREBASE_AUTH_DOMAIN=...
FIREBASE_PROJECT_ID=...
FIREBASE_STORAGE_BUCKET=...

# React frontend (nexus-ui/.env.local)
VITE_API_BASE=http://localhost:8000/api

Demo Flow

  1. Upload the SQLite demo DB or CSV bundle from outputs/test_scenarios/
  2. Overview tab — see table count, row count, avg quality score, risk concentration
  3. Schema tab — inspect PK/FK inferences and their confidence scores
  4. ER Graph tab — interactive SVG diagram; switch between full and keys view
  5. Data Quality tab — drill into per-table scores; adjust expected cadence; see temporal adjustment
  6. Semantic Layer tab — review entity mappings, constraint violations, drift report
  7. Data Dictionary tab — edit descriptions inline; save with change note
  8. AI Brief tab — generate executive summary via Gemini or Ollama
  9. Exports tab — download analysis JSON, relationship CSV, ER SVG, Mermaid .mmd; commit audit snapshot

Built for the DB Intelligence hackathon track.

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

3 stars

Watchers

1 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors

Languages