Metadata-Version: 2.4
Name: slothquery
Version: 1.0.7
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

**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
- Add SQL queries with title, description, dialect, tags, and analyst comments
- 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 Gemini, Groq, OpenRouter, DeepSeek
- 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 with custom model IDs still supported
- 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

---

## 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**.

Bulk intake never publishes AI-generated business rules automatically. Playbook changes 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

- [ ] Schema Explorer with 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.
