Metadata-Version: 2.4
Name: slothquery
Version: 1.0.8
Summary: Local-first organizational intelligence platform
Author: Ayush Thakur
Author-email: ayush01thakur@gmail.com
Requires-Python: >=3.8
Description-Content-Type: text/markdown
Requires-Dist: fastapi
Requires-Dist: uvicorn
Requires-Dist: pydantic
Requires-Dist: sqlalchemy
Requires-Dist: cryptography
Requires-Dist: chromadb
Requires-Dist: litellm
Requires-Dist: sentence-transformers
Requires-Dist: sqlglot
Requires-Dist: python-multipart
Dynamic: author
Dynamic: author-email
Dynamic: description
Dynamic: description-content-type
Dynamic: requires-dist
Dynamic: requires-python
Dynamic: summary

<div align="center">

# SlothQuery v1.0.8

**Local-first organizational intelligence platform for SQL engineers and data analysts.**

SlothQuery transforms your raw SQL queries, business rules, table schemas, and analyst notes into a searchable, AI-powered knowledge base — so your team never has to rediscover the same logic twice.

[![Python](https://img.shields.io/badge/Python-3.10+-3776AB?style=flat&logo=python&logoColor=white)](https://python.org)
[![FastAPI](https://img.shields.io/badge/FastAPI-0.110+-009688?style=flat&logo=fastapi&logoColor=white)](https://fastapi.tiangolo.com)
[![React](https://img.shields.io/badge/React-18+-61DAFB?style=flat&logo=react&logoColor=black)](https://react.dev)
[![TypeScript](https://img.shields.io/badge/TypeScript-5+-3178C6?style=flat&logo=typescript&logoColor=white)](https://typescriptlang.org)
[![SQLite](https://img.shields.io/badge/SQLite-Local--First-003B57?style=flat&logo=sqlite&logoColor=white)](https://sqlite.org)

</div>

---

## What is SlothQuery?

SlothQuery is a **local-first** knowledge retrieval system for data teams. Instead of storing queries in a flat file or Notion doc, SlothQuery extracts structured intelligence from each query — business intent, schema entities, business rules, transformations — and makes all of it searchable and retrievable at chat time.

Think of it as **institutional memory for your SQL stack**, powered by an LLM that only ever answers from your own grounded knowledge — never from hallucinated context.

---

## Tech Stack

| Layer | Technologies |
|---|---|
| **Backend** | Python · FastAPI · SQLAlchemy · SQLite · ChromaDB · LiteLLM |
| **Frontend** | React · TypeScript · Vite · Tailwind CSS · Axios · Lucide Icons |
| **AI / Retrieval** | BGE-small-en-v1.5 (local embeddings) · LiteLLM (multi-provider LLM) · ChromaDB (vector search) |

---

## Features

### Knowledge Studio
- KPI extraction keeps only meaningful calculated metrics (ratios, rates, shares, deltas, and other derived logic) in the compact `name`, `formula`, `explanation` format; raw SUM/COUNT base measures remain metadata rather than standalone KPIs.
- Ambiguities are analyst clarifications only: AI starts the field empty and existing human entries survive regeneration.
- Bulk intake can target an existing Vault or create a new Vault inline before upload.
- Bulk review exposes editable intent, business rules, metrics/formulas, dimensions, grain, filters, transformations, schemas, and ambiguities, with raw JSON retained under Advanced view.
- Vault management supports clearing all knowledge and derived indexes while preserving the Vault row; chats and provider profiles are retained.
- Vault Metadata stores a human-editable purpose, scope, source storage, catalog, schema locations, and notes; chat also receives a rebuilt cross-vault catalog of tables, metrics, rules, schemas, and counts.
- Add SQL queries with title, description, dialect, tags, and analyst comments
- After bulk query approval, missing table-schema assets are materialized from observed SQL evidence. A second Analyst Notes review appears for explicit uploaded comments only; notes can be edited, removed, added, and approved in bulk, with `always include` disabled by default.
- Bulk SQL Intake — upload `.sql`, `.ddl`, or `.txt` files (or a ZIP), automatically split mixed prose and multi-query files into separate reviewable knowledge items, detect duplicates, generate drafts, review edits, and approve them together
- Human-in-the-loop AI extraction — review and approve extracted business intent, schema entities, business rules, metric transformations, and ambiguities before saving
- Organize knowledge into domain Vaults (e.g. Marketing, Finance, Product)
- Business Rules Playbooks — consolidated business logic documentation
- Table Schema documentation with join patterns and naming conventions
- Schema Explorer — discover tables and table-qualified columns from saved SQL, inspect join patterns and query references, and maintain authored schema documents used by grounded chat
- Analyst Notes — freeform knowledge about metrics, caveats, and patterns
- Delete queries (with ChromaDB sync) and delete entire vaults (full cascade)

### Chat Interface
- Grounded AI chat — every answer is retrieved from your knowledge base; the LLM never invents tables or metrics
- Multi-vault context — query across multiple vaults in a single conversation
- Dialect-aware SQL generation — Snowflake, BigQuery, PostgreSQL, Trino, Redshift
- Hashtag-aware retrieval — add tags directly to chat requests and allow tolerant matching for small typos
- Markdown-rendered responses with bold, italic, lists, and SQL code blocks with one-click copy
- Save useful generated SQL directly from a chat code block into a selected vault for later review and retrieval
- Smooth token streaming with grounded progress states and starter prompts for common analyst tasks
- Auto-named persistent chat history
- Onboarding guard — if your knowledge base is empty, SlothQuery guides you to add content first

### Chat tag search
- Add tags directly to a request, such as `#availability` or `#3p`, instead of scrolling through a checkbox list. Matching is case-insensitive and tolerant of small typos.

### Chat latency and context reuse
- Greetings and simple conversational acknowledgements use a deterministic fast path and do not call the provider or retrieval stack.
- Ordinary knowledge requests perform one direct semantic retrieval before generation. The extra decomposition LLM call is reserved for clearly compound requests.
- Related follow-ups and query edits reuse the current chat's persisted retrieval source IDs, avoiding another vector search and decomposition call. A new topic, explicit knowledge-base search, or unrelated request invalidates that reuse and performs fresh retrieval.
- The chat stream reports whether it is using a fast path, reusing context, retrieving, or generating so the UI reflects the actual work.

### LLM Provider Management
- Supports OpenAI, Anthropic, Google, Groq, OpenRouter, DeepSeek, Ollama, xAI, Z.ai, NVIDIA, Qwen, and Kimi
- API keys are encrypted with Fernet symmetric encryption before being written to SQLite — never logged or exposed to the frontend
- Test Connection before saving
- Searchable provider-aware model suggestions are refreshed from the allowlisted models.dev catalog, with custom model IDs still supported
- Existing profiles can be edited to change models while preserving the saved encrypted API key
- OpenRouter failures are classified into safe, actionable messages without exposing credentials
- Dynamic provider switching without restarting the app
- Last active provider automatically restored on restart

### Knowledge Portability
- Export your entire knowledge base or individual vaults as a `.slothkb` archive
- Import and automatically re-index embeddings
- Re-index — rebuild the entire ChromaDB vector store from SQLite at any time

Vault metadata and the derived catalog are documented in [docs/vault-catalog.md](docs/vault-catalog.md).

## Product Screenshots

SlothQuery is designed around a simple loop: capture organizational knowledge, review it with a human in the loop, and retrieve it through grounded chat.

<p align="center">
  <img src="docs/assets/product-chat.png" alt="SlothQuery grounded chat workspace" width="900" />
</p>

<p align="center"><em>Grounded chat with recent conversations, starter prompts, and the active SQL dialect visible in context.</em></p>

<p align="center">
  <img src="docs/assets/product-bulk-intake.png" alt="SlothQuery bulk SQL intake review" width="900" />
</p>

<p align="center"><em>Bulk SQL intake stages many files for parsing, enrichment, review, and explicit approval.</em></p>

<p align="center">
  <img src="docs/assets/product-settings.png" alt="SlothQuery settings and provider configuration" width="430" />
  <img src="docs/assets/product-help.png" alt="SlothQuery help center" width="700" />
</p>

<p align="center"><em>Local provider, vector-store, import/export controls alongside built-in guidance for the workflow.</em></p>

### Current product surface

- Vault Metadata records a vault's purpose, scope, source storage, catalogs, schema locations, and notes; the derived catalog lets chat answer vault- and storage-specific questions across vaults.
- Bulk SQL Intake separates mixed SQL and prose into reviewable items, exposes editable intelligence, and saves only the items a user explicitly approves.
- Chat retrieves queries, playbooks, schemas, analyst notes, and vault metadata before generation, with human-edited context prioritized over raw AI extraction.
- The sidebar keeps the latest 60 chats, loads history in pages of 20, and removes the oldest chats when the retention limit is exceeded.
- SQL responses use editor-style syntax highlighting with copy and save actions, while SQLite remains the source of truth and ChromaDB remains rebuildable derived state.

---

## Architecture

```
UI (React)
  ↓
API (FastAPI)
  ↓
Services (chat · extraction · retrieval · provider · vector_store)
  ↓
Repositories (SQLAlchemy)
  ↓
Storage (SQLite + ChromaDB)
```

- **Local-first** — all data lives in `~/Documents/SlothQuery/` on your machine
- **SQLite** is the source of truth for all structured data
- **ChromaDB** is derived state — always rebuildable from SQLite via Re-index
- **No cloud dependency** — works fully offline once the embedding model is cached

---

## Getting Started

### Option 1: Quick Install (Recommended)
You can install and run SlothQuery directly from PyPI as a global CLI tool. No cloning or Node.js compilation required:

```bash
# Install the package
pip install slothquery

# Start the application from any directory
slothquery
```
*This starts the local FastAPI server and automatically opens your default web browser to the dashboard at `http://127.0.0.1:8000`.*

---

### Option 2: Local Developer Setup (Clone & Run)
If you want to modify the source code, run integration tests, or contribute to the frontend layout:

#### 1. Clone the repository
```bash
git clone https://github.com/ayush01thakur/slothquery.git
cd slothquery
```

#### 2. Set up the Backend
```bash
cd backend

# Create and activate a virtual environment
python -m venv venv

# Windows
.\venv\Scripts\activate

# macOS / Linux
source venv/bin/activate

# Install dependencies
pip install -r requirements.txt

# Start the backend server
uvicorn app.main:app --reload --port 8000
```
> Note: First run automatically downloads the local BGE-small-en-v1.5 embedding model weights (~130MB).

API documentation is interactive at: `http://127.0.0.1:8000/docs`

#### 3. Set up the Frontend
```bash
cd ../frontend
npm install
npm run dev
```
App developer server available at: `http://localhost:3000`

---

## How to Use SlothQuery

### Step 1 — Configure an LLM Provider
1. Open `http://localhost:3000`
2. Click the **Settings** icon or click **Configure** on the amber banner
3. Select your provider, enter model name and API key
4. Click **Test & Save** — your key is encrypted and stored locally

### Step 2 — Build Your Knowledge Base

Go to **Knowledge Studio** in the left sidebar.

**Adding a Query:**
1. Click `+ Add Asset → Add Query`
2. Fill in title, vault, dialect, description, and tags
3. Paste your SQL and analyst comments
4. Click **Generate & Review Intelligence** — AI extracts context
5. Review the draft, edit anything incorrect, then **Approve & Save**

**Bulk SQL Intake:**
1. Open **Knowledge Studio → Add Asset → Bulk SQL Intake**.
2. Select a destination vault, choose a dialect or use auto-detection, then upload SQL/TXT files or a ZIP archive. TXT files can contain descriptions above queries and multiple queries in one file.
3. Keep rich comments or descriptions near each query so its purpose, grain, filters, and limitations enrich the knowledge package. SlothQuery automatically identifies separators, titles, descriptions, and SQL logic from the file.
4. Review the parser's separate items and duplicate warnings. When a provider is configured, a conservative parsing agent maps mixed prose and SQL; deterministic parsing remains the fallback.
5. Click **Generate AI drafts**. The backend progress bar shows separation, validation, and enrichment progress. Generated titles, descriptions, comments, and intelligence remain editable.
6. Select only the reviewed items you want to save and click **Approve selected**. Missing table-schema assets are created from observed SQL tables and columns without replacing authored schema documents.
7. Review the separate **Analyst Notes** stage. Save only notes you approve, add your own vault guidance, or skip it when the upload contains no reusable notes. Suggested notes are never inferred from low-confidence generated context.

The Analyst Notes stage also accepts a UTF-8 `.txt` file containing numbered sections (`1. NOTE TITLE`) followed by bullets or paragraphs. Each titled section becomes a separate editable note; the text is preserved exactly for review.

Bulk intake never silently publishes AI-generated analyst notes or business rules. Schema assets are deterministic observations from approved SQL; analyst notes remain a separate, reviewable action.

**Exploring schemas:**
1. Open **Schema Explorer** from the left sidebar.
2. Search the derived table map to inspect columns observed in saved SQL, join patterns, and the exact saved queries that reference each table.
3. Add or edit a schema document to record grain, ownership, column definitions, joins, and limitations. These documents are retrieved by chat when generating SQL.

**Adding Playbooks, Schemas, Notes:**
1. Click `+ Add Asset` and choose the asset type
2. Name it, select a vault, write the content, and save

### Step 3 — Chat

1. Go to the **Chat** view
2. Ask questions or request SQL — e.g.:
   - *"Show me the ARR calculation query"*
   - *"Write a Snowflake query for weekly active users"*
   - *"What business rules apply to revenue metrics?"*
3. SlothQuery retrieves matching context and generates a grounded response
4. SQL appears in a code block with **Copy** and **Save** actions
5. Save useful SQL to a vault when you have reviewed and want it to become reusable knowledge

> Tip: Use the **+** button in the chat input to choose active vaults. Add `#tags` inline when you want retrieval to prioritize saved knowledge with matching topic tags; for example, `Explain #availabiltiy for #3p`.

---

## Data Storage

All data is stored locally at:

```
~/Documents/SlothQuery/
├── slothquery.db        # SQLite — queries, chats, providers (encrypted keys)
├── chroma_db/           # ChromaDB — vector embeddings (rebuildable)
└── Exports/             # .slothkb export archives
```

> The SQLite database contains encrypted API keys. Never commit it to version control — it is excluded by `.gitignore`.

---

## Keyboard Shortcuts

| Action | Shortcut |
|---|---|
| Send chat message | `Enter` |
| New line in input | `Shift + Enter` |

---

## Roadmap

- [ ] Richer schema lineage and optional live table preview
- [ ] Query versioning and diff view
- [ ] Team shared knowledge base sync
- [ ] Slack / Teams bot integration
- [ ] Desktop wrapper (Tauri)
- [ ] MCP server support for AI coding agents

---

## Contributing

Branch naming:
```
feat/add-schema-versioning
fix/resolve-chromadb-sync
refactor/move-embedding-service
```

---

## License

MIT — use freely, build on top, keep it local-first.
