Talk to your subscription data. Ask questions in plain English, get clear answers backed by real numbers — no dashboards, no SQL, no friction.
Live Demo → https://subscrub.onrender.com
⚠️ Live Demo Note: The demo uses a free-tier Gemini API key with strict rate limits. Please test 4–5 queries max per session. If results stop appearing, the key has been rate-limited — wait a minute and retry. Also, the demo data covers Jan 2024 – Dec 2025, so time-relative queries like "this month" will return empty results. Use absolute queries like "show my subscriptions" or "which payments went up in 2025" for best results.
People forget subscriptions. OTT platforms, gym memberships, cloud storage, music apps — small recurring charges that quietly drain your balance every month. Most users have no easy way to see all their subscriptions in one place, track price increases, or understand how much they're actually spending on autopilot payments.
There's no single tool that lets you simply ask: "How much am I spending on subscriptions?" or "Which payments went up this year?" — and get a clear, honest answer.
SubScrub is a "talk to my subscriptions" tool — a self-service subscription intelligence platform where users ask natural language questions about their recurring payments and instantly receive trustworthy, data-backed answers. No dashboards, no SQL, no friction.
The system detects recurring patterns in card/UPI/direct debit transactions, groups them by service, shows cost trends over time, and lets users simulate cancellations ("What if I cancel Netflix and Spotify — how much do I save monthly?").
The project was built for the NatWest Code for Purpose — India Hackathon under the "Talk to Data — Seamless Self-Service Intelligence" use case, focusing on three pillars: Clarity (plain-language answers for non-experts), Trust (versioned metrics, SQL transparency, and query lineage), and Speed (local embeddings with near-instant intent classification).
- Natural Language Q&A — Users ask questions in plain English; the system classifies intent, queries the database, and returns a human-friendly explanation with supporting data.
- 13 Supported Intents — List subscriptions, spend summary, monthly trend, category breakdown, most expensive, price changes, upcoming debits, anomaly detection, cancelled subscriptions, recent transactions, cancel simulation, user profile, and greeting/help.
- YAML-Based Semantic Layer — Every metric is defined in a versioned YAML file with a named-parameter SQL template, description, and data window — ensuring consistent, auditable query definitions.
- Local ONNX Embeddings — Intent classification runs entirely on-device using all-MiniLM-L6-v2 (~22 MB, CPU-only). No API key needed for the classification step — only Gemini is called for SQL fallback and natural language explanations.
- Gemini 2.5 Flash Fallback — When the semantic layer confidence is below 0.70, the system falls back to Gemini to generate SQL dynamically from the full DB schema + few-shot examples.
- SQL Safety Guard — Every query (both template and Gemini-generated) passes through a strict whitelist: SELECT-only, forbidden keyword detection, and allowed-table enforcement before reaching the database.
- CSV Upload with Auto-Detection — Users can upload bank transaction CSVs; the system automatically detects recurring patterns, creates subscription records, computes next due dates, and flags price anomalies.
- Cancel Simulation Engine — Pure Java what-if calculator: "What if I cancel Netflix and Spotify?" → shows projected monthly/yearly savings instantly, no AI needed.
- Query Lineage Tracking — Every query is logged with intent, SQL used, row count, runtime, and metric version — providing a full audit trail for trust and debugging.
- Dark-Themed Chat UI — Single-page app with a chat interface, quick suggestion buttons, CSV drag-and-drop upload, intent/confidence badges, toggleable SQL source view, and data tables.
- Docker Support — Multi-stage Maven build with a production-ready Dockerfile and docker-compose configuration.
- Dual Database Profile — H2 file-mode for development, PostgreSQL for production — switch via a single environment variable.
Every user question flows through a deterministic 8-step pipeline:
┌──────────────────────────────────────────────────────────────────────────────────┐
│ USER QUERY (plain English) │
└──────────────────────────┬───────────────────────────────────────────────────────┘
│
┌──────▼──────┐
│ Step 1: │
│ Validate │
│ Input │
└──────┬──────┘
│
┌────────────▼────────────┐
│ Step 2: Intent │
│ Classification │
│ │
│ 2A: ONNX Bi-Encoder │ ← all-MiniLM-L6-v2 (local, no API)
│ ↓ top-5 candidates │
│ 2B: Cosine Similarity │ ← Reranker picks best intent
│ ↓ intent + score │
│ 2C: Regex Slot Extract │ ← year, days, service names
└──────────┬──────────────┘
│
┌──────────▼──────────┐
│ Step 3: Confidence │
│ Gate (≥ 0.70?) │
└─────┬─────────┬─────┘
│ │
┌─── YES ─┘ └─ NO ───┐
│ │
┌──────▼──────┐ ┌───────▼────────┐
│ Step 4: │ │ Gemini 2.5 │
│ Semantic │ │ Flash SQL │ ← Schema + few-shot prompt
│ Layer │ │ Generator │
│ (YAML) │ └───────┬────────┘
└──────┬──────┘ │
│ │
┌──────▼──────┐ │
│ Step 5: │ │
│ SQL │ │
│ Builder │ │
└──────┬──────┘ │
│ │
└────────────┬───────────────┘
│
┌──────▼──────┐
│ Step 6: │
│ SQL Guard │ ← SELECT-only, whitelist tables, no mutations
└──────┬──────┘
│
┌──────▼──────┐
│ Step 7: │
│ Query │
│ Executor │ ← NamedParameterJdbcTemplate
└──────┬──────┘
│
┌──────▼──────┐
│ Step 8: │
│ Explanation│ ← Gemini 2.5 Flash composes NL answer
│ Composer │
└──────┬──────┘
│
┌────────▼────────┐
│ JSON Response │
│ • answer │ (plain English explanation)
│ • intent │ (detected intent name)
│ • confidence │ (0.0 – 1.0)
│ • sqlUsed │ (for transparency)
│ • data │ (table rows)
│ • dataWindow │ (time period covered)
└─────────────────┘
| Intent | Handler | Description |
|---|---|---|
GREETING |
Static response | Returns help text with example queries |
CANCEL_SIMULATION |
SimulationEngine |
Pure Java arithmetic on subscription data |
| Pillar | How SubScrub Delivers |
|---|---|
| Clarity | Gemini composes 2–3 sentence explanations in plain language with specific numbers and time windows. No jargon, no SQL exposed by default. |
| Trust | Versioned YAML metrics ensure consistent definitions. SQL is shown on-demand for transparency. Query lineage logs every request with intent, SQL, row count, and runtime. |
| Speed | Intent classification runs locally via ONNX embeddings (no API roundtrip). Template SQL executes instantly against indexed tables. Gemini is called only for low-confidence fallback and final explanation. |
| Hackathon Use Case | SubScrub Intents |
|---|---|
| 1. Understand What Changed | PRICE_CHANGES — which subscriptions increased/decreased in price; ANOMALY_LIST — flags unusual charges with delta amounts and percentages |
| 2. Compare | MONTHLY_TREND — month-over-month spending comparison; CATEGORY_BREAKDOWN — compare spend across categories |
| 3. Breakdown (Decomposition) | CATEGORY_BREAKDOWN — decompose total spend by category; MOST_EXPENSIVE — rank top-5 costliest subscriptions |
| 4. Summarize | SPEND_SUMMARY — current month's total spend; RECENT_TRANSACTIONS — last 20 transactions; Gemini fallback handles "give me a weekly summary" style queries |
| Layer | Technology | Purpose |
|---|---|---|
| Language | Java 21 | Core runtime |
| Framework | Spring Boot 3.4.1 | Web server, DI, JPA, configuration |
| AI Framework | Spring AI 1.1.4 | Gemini chat client, embedding model, vector store abstraction |
| LLM | Google Gemini 2.5 Flash (free tier) | SQL generation fallback + natural language explanations |
| Embeddings | all-MiniLM-L6-v2 (ONNX, 22 MB) | Local intent classification — no API key, CPU-only |
| Vector Store | Spring AI SimpleVectorStore | In-memory ANN search over ~190 intent anchor documents |
| Database (Dev) | H2 (file mode) | Embedded SQL database, zero setup, persists across restarts |
| Database (Prod) | PostgreSQL | Production-grade relational database |
| ORM | Spring Data JPA + Hibernate | Entity management, repository pattern |
| SQL Execution | NamedParameterJdbcTemplate | Safe parameterized query execution |
| CSV Parsing | OpenCSV 5.9 | Bank statement CSV ingestion |
| Metric Definitions | SnakeYAML | YAML-based semantic layer loading |
| Build | Maven 3.9 (wrapper included) | Dependency management, build lifecycle |
| Containerization | Docker (multi-stage build) | Reproducible builds, deployment |
| Frontend | Vanilla HTML/CSS/JS | Single-page chat UI served by Spring Boot |
- Java 21+ — Download from Adoptium
- Gemini API Key — Free tier, used for SQL fallback + natural language explanations (see steps below)
- Maven 3.9+ — Or use the included
mvnwwrapper (no install needed) - Docker (optional) — For containerized deployment
- Go to Google AI Studio.
- Sign in with your Google account.
- Click "Get API Key" in the left sidebar.
- Click "Create API key" and select a Google Cloud project (or create a new one).
- Copy the generated key — it starts with
AIza.... - Paste it into your
.envfile asGEMINI_API_KEY=your_key_here.
The free tier allows ~15 requests/minute and ~1,500 requests/day, which is sufficient for development and demo use.
# 1. Clone the repository
git clone https://github.com/Hemantshankhwar/SubScrub.git
cd SubScrub
# 2. Configure environment
cp .env.example .env
# Edit .env and add your Gemini API key:
# GEMINI_API_KEY=your_key_here
# 3. Run with Maven wrapper (no Maven install needed)
GEMINI_API_KEY=$(grep GEMINI_API_KEY .env | cut -d= -f2) ./mvnw spring-boot:run
# 4. Open in browser
open http://localhost:8080Note: On first startup, the ONNX embedding model (~22 MB) is downloaded automatically from Hugging Face and cached locally. This requires an internet connection the first time only.
The application ships with pre-seeded demo data (a "demo" user with 10 subscriptions, 193 transactions spanning Jan 2024 – Dec 2025, 20 known services, and pre-computed monthly summaries). You can start querying immediately.
# 1. Configure environment
cp .env.example .env
# Edit .env and add your Gemini API key
# 2. Build and run
docker compose up --build
# 3. Open in browser
open http://localhost:8080| Variable | Required | Default | Description |
|---|---|---|---|
GEMINI_API_KEY |
Yes | — | Google AI Studio API key (free tier) |
SPRING_PROFILES_ACTIVE |
No | dev |
Set to prod for PostgreSQL |
DATABASE_URL |
Only for prod | — | PostgreSQL JDBC URL |
PORT |
No | 8080 |
Server port |
Once the app is running, type questions in the chat input (default user: demo):
| Example Query | Intent Triggered | What You Get |
|---|---|---|
| "Show all my subscriptions" | LIST_SUBSCRIPTIONS |
Table of all active subscriptions with amounts and frequency |
| "How much do I spend per month?" | SPEND_SUMMARY |
Current month's total subscription cost with breakdown |
| "Which payments went up this year?" | PRICE_CHANGES |
List of subscriptions with price increases, showing old vs new amounts |
| "What's due in the next 10 days?" | UPCOMING_DEBITS |
Upcoming auto-debit schedule with days remaining |
| "Show my monthly spending trend" | MONTHLY_TREND |
Month-over-month comparison of total subscription spend |
| "Which category costs the most?" | CATEGORY_BREAKDOWN |
Spend decomposed by category (Entertainment, Music, Fitness, etc.) |
| "Show unusual charges" | ANOMALY_LIST |
Flagged price changes with delta amounts and percentages |
| "What if I cancel Netflix and Spotify?" | CANCEL_SIMULATION |
Projected monthly/yearly savings and remaining subscription cost |
| "Compare this month with last month" | Gemini fallback | Dynamic SQL generated and explained in plain language |
Drag and drop a CSV file into the sidebar upload area, or click to browse. Expected columns:
date,amount,merchant,description,channel
2025-01-15,649.00,NETFLIX.COM/BILL,Netflix Monthly,CARD
2025-01-06,119.00,SPOTIFY PREMIUM,Spotify Subscription,UPI
After upload, the system automatically:
- Parses and stores transactions
- Detects recurring patterns (monthly, yearly, weekly, quarterly)
- Creates subscription records with next due dates
- Flags price anomalies
| Method | Endpoint | Body | Description |
|---|---|---|---|
POST |
/api/ask |
{ "query": "...", "userId": "demo" } |
Ask a natural language question |
POST |
/api/upload |
Multipart: file (CSV) + userId |
Upload bank transactions |
POST |
/api/simulate |
{ "userId": "demo", "cancelTargets": ["Netflix"] } |
Simulate cancellation savings |
Example API call:
curl -X POST http://localhost:8080/api/ask \
-H "Content-Type: application/json" \
-d '{"query": "show all my subscriptions", "userId": "demo"}'Example response:
{
"answer": "You have 9 active subscriptions costing a total of ₹20,827 per month...",
"intent": "LIST_SUBSCRIPTIONS",
"confidence": 0.92,
"sqlUsed": "SELECT name, current_amount, frequency, status ... FROM subscriptions WHERE ...",
"data": [ ... ],
"metricVersion": "1.0",
"dataWindow": "Current snapshot"
}SubScrub/
├── src/main/java/com/subscrub/
│ ├── SubScrubApplication.java # Spring Boot entry point
│ ├── config/
│ │ ├── VectorStoreConfig.java # ONNX embeddings + intent anchor docs (~190 examples)
│ │ └── WebConfig.java # CORS configuration
│ ├── controller/
│ │ ├── ChatController.java # POST /api/ask — main NLQ endpoint
│ │ ├── UploadController.java # POST /api/upload — CSV ingestion
│ │ └── SimulationController.java # POST /api/simulate — cancel what-if
│ ├── dto/
│ │ ├── AskRequest.java # { query, userId }
│ │ ├── AskResponse.java # { answer, intent, confidence, sqlUsed, data, ... }
│ │ ├── IntentResult.java # Intent classification output
│ │ ├── EntityParams.java # Extracted slot parameters
│ │ ├── SimRequest.java # { userId, cancelTargets }
│ │ └── SimResult.java # { currentTotal, projectedTotal, savings, ... }
│ ├── model/ # JPA entities
│ │ ├── AppUser.java # User profile
│ │ ├── Subscription.java # Detected recurring payments
│ │ ├── Transaction.java # Raw bank transactions
│ │ ├── MonthlySummary.java # Pre-aggregated monthly spend
│ │ ├── SubscriptionEvent.java # Individual charges with anomaly flags
│ │ ├── KnownService.java # Reference catalog of known subscription services
│ │ └── QueryLineage.java # Audit log of every query
│ ├── repository/ # Spring Data JPA repositories (7 interfaces)
│ └── service/
│ ├── NLQOrchestrator.java # Master pipeline — wires all 8 steps
│ ├── IntentRouter.java # Step 2: ONNX embeddings + cosine reranker + regex slots
│ ├── CrossEncoderService.java # Step 2B: Picks best intent from top-K candidates
│ ├── SemanticLayer.java # Step 4: Loads YAML metric definitions
│ ├── SQLBuilder.java # Step 5: Fills SQL templates with parameters
│ ├── SQLGuard.java # Step 6: SELECT-only whitelist enforcement
│ ├── QueryExecutor.java # Step 7: NamedParameterJdbcTemplate execution
│ ├── ExplanationComposer.java # Step 8: Gemini NL explanation generation
│ ├── GeminiSqlGenerator.java # Low-confidence fallback: Gemini generates SQL
│ ├── SimulationEngine.java # Cancel what-if calculator (pure Java)
│ ├── CsvIngestionService.java # CSV parsing + transaction import
│ └── RecurrenceDetector.java # Auto-detects subscription patterns from transactions
├── src/main/resources/
│ ├── application.properties # Base config (profile selection, port)
│ ├── application-dev.properties # H2 file-mode, Gemini config, ONNX embeddings
│ ├── application-prod.properties # PostgreSQL, Gemini config
│ ├── data.sql # Seed data (193 transactions, 10 subs, 20 services)
│ ├── metrics/ # Semantic layer — 13 YAML metric definitions
│ │ ├── LIST_SUBSCRIPTIONS.yaml
│ │ ├── SPEND_SUMMARY.yaml
│ │ ├── MONTHLY_TREND.yaml
│ │ ├── CATEGORY_BREAKDOWN.yaml
│ │ ├── MOST_EXPENSIVE.yaml
│ │ ├── PRICE_CHANGES.yaml
│ │ ├── UPCOMING_DEBITS.yaml
│ │ ├── ANOMALY_LIST.yaml
│ │ ├── CANCELLED_SUBS.yaml
│ │ ├── RECENT_TRANSACTIONS.yaml
│ │ ├── CANCEL_SIMULATION.yaml
│ │ ├── USER_INFO.yaml
│ │ └── GREETING.yaml
│ └── static/
│ └── index.html # Single-page chat UI (dark theme)
├── data/seed/ # CSV seed files (7 tables)
├── docker-compose.yml # Container orchestration
├── Dockerfile # Multi-stage Maven build
├── pom.xml # Maven dependencies
├── .env.example # Environment variable template
└── mvnw / mvnw.cmd # Maven wrapper (no Maven install needed)
Most text-to-SQL systems send every query to an LLM, which is slow, expensive, and inconsistent — the same question can produce different SQL each time. SubScrub's semantic layer solves this:
- Consistent definitions: "spend summary" always runs the same SQL template, ensuring "revenue" and "total cost" always mean the same thing.
- Versioned metrics: Each YAML file carries a version tag. When definitions change, the version updates — enabling reproducibility and auditing.
- Speed: Template SQL executes instantly. Gemini is only called for the ~30% of queries that don't match a known intent.
Using all-MiniLM-L6-v2 (ONNX) for intent classification means:
- Zero cost — No embedding API calls for intent classification.
- Low latency — ~5ms per embedding on CPU vs ~200ms for a cloud API call.
- Offline capable — After the initial download, classification works without internet.
- Privacy — User queries never leave the server for the classification step.
Gemini 2.5 Flash is used as a fallback, not the primary path, because:
- Cost: Free-tier Gemini has rate limits; local embeddings handle most queries without any API call.
- Consistency: Template SQL is deterministic; LLM-generated SQL can vary between runs.
- Safety: Template SQL is pre-validated. Gemini SQL still passes through the SQLGuard, but templates are inherently safer.
- Speed: Template path avoids the ~1–2 second LLM roundtrip entirely.
Every SQL query (template or Gemini-generated) passes through SQLGuard before execution:
- SELECT-only check — Rejects any statement not starting with
SELECT. - Forbidden keyword detection — Word-boundary regex blocks
INSERT,UPDATE,DELETE,DROP,ALTER,CREATE,TRUNCATE,EXEC, and comment/semicolon injection. - Table whitelist — SQL must reference at least one of the 6 allowed tables:
transactions,subscriptions,subscription_events,monthly_summaries,known_services,users.
This is a hackathon prototype. The following limitations exist and are acknowledged honestly:
- Embedded H2 Database — The application uses H2 in file mode rather than a dedicated database server. H2 is excellent for prototyping and demos, but is not suitable for concurrent multi-user production workloads. There is no separate database server; the DB runs embedded within the Java process.
- Semantic Layer Confidence Issues — The semantic layer does not work reliably for all queries. For some questions, the confidence score falls below the 0.70 threshold and the Gemini SQL fallback is triggered. In certain cases, Gemini may also fail to generate valid SQL, resulting in a "Failed to generate query" error. This is a known limitation of the current intent anchor coverage and the free-tier LLM's SQL accuracy.
- Demo Data Only — The application ships with a single pre-seeded demo user. There is no user authentication or multi-user support.
- No Real Bank Integration — Transaction data comes from CSV uploads or seed data. There is no integration with real banking APIs (e.g., Plaid, CRED, or open banking).
- ONNX Model Download — The all-MiniLM-L6-v2 model (~22 MB) must be downloaded from Hugging Face on first startup, requiring internet access.
- Stale Demo Data — The pre-seeded demo data covers January 2024 – December 2025. Queries referencing relative time periods like "this month", "upcoming debits", or "what's due next week" will return empty results because the current date is beyond the data range. To see meaningful results, use absolute queries like "show my subscriptions", "which payments went up in 2025", or "show monthly spending trend".
- Gemini Free-Tier Rate Limiting — The deployed demo at subscrub.onrender.com uses a free-tier Gemini API key with strict rate limits. You can realistically test 4–5 queries before hitting the rate limit. If a query returns "Failed to generate query" or the explanation is missing, the API key has likely been rate-limited — please wait a minute and try again. This is a free-tier constraint, not an application bug.
- Explanation Timeout — The
ExplanationComposerhas a 15-second timeout for Gemini responses. Slow network or Gemini rate-limiting can cause explanation generation to fail (data is still returned without the NL summary). - Single-Page UI — The frontend is a single HTML file with inline CSS/JS. It is functional but not a production-grade SPA framework.
With more time, the following enhancements would strengthen the application:
- User Authentication — OAuth2 or SSO integration for multi-user support with proper data isolation.
- Real Bank Statement Parsing — Support for PDF, OFX, and QIF bank statement formats beyond CSV.
- Dedicated Database — Migration to a managed PostgreSQL instance for production reliability and concurrent access.
- Improved Semantic Layer Coverage — Expand intent anchor examples and add more YAML metric definitions to reduce Gemini fallback frequency.
- Streaming Responses — Server-Sent Events (SSE) for real-time streaming of Gemini explanations instead of waiting for the full response.
- Charts and Visualizations — Add interactive charts (spending trends, category pie charts) alongside text explanations.
- Query Caching — Cache frequently asked questions to avoid repeated Gemini calls and database queries.
- Multi-Language Support — Extend intent classification to support Hindi and other Indian languages.
- All data stays on the server. The frontend communicates only with the SubScrub backend.
- User queries are sent to Google Gemini only for SQL generation (fallback) and natural language explanation — never for intent classification.
- No data is stored by Gemini (Google AI Studio free tier does not retain API inputs).
- The
.envfile containing the API key is git-ignored and never committed.
This project is licensed under the Apache License 2.0.