I Tried RAG on My Production Rails App — Then Replaced It with a $0.002 SQL Generator

The app: a medical services management platform

I maintain a Rails 8 application that manages cleaning operations for medical facilities. Staff check in and out of jobs via GPS, managers approve time-off requests, and the system tracks everything — tens of thousands of jobs, hundreds of users, and a substantial audit trail.

The super admin gets questions like:

Answering each one means SSHing into the server, opening a Rails console, writing a query, interpreting the results. It takes 10-30 minutes per question. I wanted to build an AI assistant that could answer these instantly.

What I built: the full RAG pipeline

I did what every AI tutorial tells you to do. I built RAG — Retrieval Augmented Generation:

Here's what a chunk looked like after indexing:

[Job Note] General Note
Job #4821 | Client A | North Scanning Centre
Date: 2024-08-15

Use the correct colour coding for waste bins.
Ensure sharps containers are sealed before disposal.
Check all hand sanitizer dispensers are refilled.

The infrastructure was impressive. MySQL 9 with native vector columns. Cosine similarity search in SQL. Deduplication via SHA256 digests. Real-time sync via after_commit callbacks. Nightly bulk re-sync via Solid Queue.

Total embedding cost: $0.55 for 77,000 chunks.

The moment RAG failed

I asked the first real question:

RAG found text chunks that mentioned check-ins — job notes about check-in procedures, site instructions about GPS requirements. It returned paragraphs of text. None of them answered the question.

The actual answer is a SQL query:

SELECT u.first_name, u.last_name, ju.checked_in_at, j.id
FROM job_users ju
JOIN users u ON ju.user_id = u.id
JOIN jobs j ON ju.job_id = j.id
WHERE ju.checked_in_at IS NOT NULL
  AND ju.checked_out_at IS NULL
  AND ju.deleted_at IS NULL

That's when it clicked. My data is structured. My questions have exact answers. RAG finds similar text — but I need exact rows.

Every question the admin asks — check-in status, holiday balances, GPS locations, audit trails — maps to a SELECT query with JOINs, WHERE clauses, and aggregations. Vector similarity search is the wrong tool entirely.

The replacement: Text-to-SQL in 200 lines

I ripped out the vector search and replaced it with something much simpler. The entire pipeline is two LLM calls:

The schema prompt is the key. I describe every table, column, type, and relationship the LLM needs to know:

SYSTEM_PROMPT = <<~PROMPT
  You are a SQL expert for a medical services platform.

  ## Schema
  - users: id, email, first_name, last_name, role(integer),
    archived(boolean), sign_in_count, last_sign_in_at
  - jobs: id, client_id, site_id, datetime, state(string),
    deleted_at(soft delete)
  - job_users: id, job_id, user_id, checked_in_at,
    checked_out_at, bypassed_location(boolean)
  - user_locations: id, user_id, job_id, name(address),
    out_of_range(boolean), parameters(json with distance,
    latitude, longitude)
  - time_off_requests: id, user_id, start_time, end_time,
    state, request_type, duration_in_days, notes
  - versions: id, item_type, item_id, event, whodunnit,
    object_changes(json), created_at

  ## Rules
  1. Only generate SELECT queries
  2. Always LIMIT to 50 rows
  3. Join to users/sites to show names, not IDs
  4. For soft deletes, check deleted_at IS NULL
PROMPT

That's it. No vector database. No embeddings. No chunking pipeline. No MySQL 9. The LLM reads the schema description and writes SQL that runs on plain MySQL 8.4.

The model: Claude Haiku (claude-haiku-4-5-20251001). The cheapest Claude model at $0.25/M input tokens. It generates correct SQL with JOINs, subqueries, aggregations, and date arithmetic consistently.

Real queries against production data

Here are actual results from the system running against production data. Names and identifiers are anonymized.

“Which staff have checked in but not checked out?”

The LLM generated a query joining job_users, users, jobs, clients, and sites. Result:

50 staff members checked in without checking out.

Most concerning:
1. Staff Member A — checked in March 2024 (over a year ago)
   Job state: "started" — never progressed
2. Staff Member B — checked in March 2024
   Job state: "read_note" — unusual state
3. Staff Member C — 7 separate incomplete check-ins
   Suggests repeated check-in issues

Recommendation: Investigate the oldest entries (March 2024)
as these are likely data anomalies that should be manually
resolved.

“Show out-of-range check-ins. Any anomalous patterns?”

The LLM queried the location tracking table, joined to users and jobs, filtered for out_of_range = 1, and analyzed the patterns:

Anomalous patterns detected:

Staff Member D:
- Significantly more out-of-range flags than any other user
- Multiple identical coordinates in quick succession
  (genuine movement shows continuous variation)
- Some recorded distances physically implausible

Staff Member E:
- 100% of location pings were out-of-range across
  multiple jobs

Recommendation: Flag for review by operations manager.
Cross-reference with job completion records.

“Summarize sick leave usage this year”

Sick leave summary (approved requests only):

- Total employees with sick leave: 25
- Range: 1 to 9 days per person
- Top user: 2 requests but longest total duration
- Most frequent: 6 separate requests, shorter each time

Pattern note: one employee has the most frequent
requests despite fewer total days — could indicate
a recurring condition or a pattern worth reviewing
with HR.

“What are the busiest sites?”

Top 5 sites by job volume:
1. Site A — over 1,500 jobs (40% completed)
2. Site B — over 1,300 jobs (37% completed)
3. Site C — over 1,000 jobs (34% completed)
4. Site D — over 1,000 jobs (44% completed)
5. Site E — over 600 jobs (13% completed)

One client dominates with 4 of the top 5 sites.
Site E has the lowest completion rate (13%) — the AI
flagged this and suggested investigating whether it
indicates operational issues or a different job type.

Security: how to safely let AI write SQL

Letting AI generate SQL against production sounds dangerous. It is — if you rely on application-level validation alone. Here's the layered approach:

Layer 1: Database-level (the real guardrail)

-- Create a read-only user for the AI assistant
CREATE USER 'ai_readonly'@'%' IDENTIFIED BY '...';
GRANT SELECT ON your_database.* TO 'ai_readonly'@'%';

-- Optionally restrict to specific tables
REVOKE SELECT ON your_database.sensitive_table FROM 'ai_readonly'@'%';

Layer 2: Application-level (defense in depth)

Layer 3: Infrastructure (production best practice)

The application-level validation code:

def validate_and_execute(sql)
  normalized = sql.strip.gsub(/\s+/, " ").upcase
  return { error: "Only SELECT" } unless normalized.start_with?("SELECT")

  # Word-boundary check — "deleted_at" passes, "DELETE" is blocked
  sql_words = normalized.split(/[\s;,()]+/)
  dangerous = %w[INSERT UPDATE DELETE DROP ALTER TRUNCATE GRANT REVOKE]
  if dangerous.any? { |kw| sql_words.include?(kw) }
    return { error: "Forbidden keyword" }
  end

  # Block sensitive columns
  blocked = %w[ENCRYPTED_PASSWORD RESET_PASSWORD_TOKEN CONFIRMATION_TOKEN]
  if blocked.any? { |col| normalized.include?(col) }
    return { error: "Sensitive column blocked" }
  end

  # Block system tables
  return { error: "System table blocked" } if normalized.include?("INFORMATION_SCHEMA")

  sql = "#{sql.chomp(';')} LIMIT 50" unless normalized.include?("LIMIT")

  # Execute with timeout
  ActiveRecord::Base.connection.execute("SET SESSION MAX_EXECUTION_TIME=5000")
  data = connection.select_all(sql).to_a
  { data: data, error: nil }
end

Cost breakdown: $0.04 for 15 queries

I tested 15 real questions against production data. Total cost:

For comparison, the RAG approach would have cost:

The Sonnet comparison was interesting — it produced slightly more polished prose but generated the same SQL. Haiku at 7.5x cheaper with the same data accuracy is the clear winner for this use case.

When RAG is actually the right choice

RAG isn't bad. I used it wrong. Here's the decision framework:

The mistake I made: treating structured data like unstructured data. My 77,000 “chunks” were just database rows converted to text paragraphs. They were structured all along — I was throwing away the structure by embedding them.

If your app has a relational database and your users ask operational questions, you probably don't need RAG either. A 200-line SQL generator with the cheapest LLM will outperform a vector search pipeline every time.

But I already had a free solution

Here's the part I should have realized sooner. Before building any of this, I had already built a Claude Code skill file that answers the exact same questions — for free, using my existing Claude Code subscription.

The skill generates ActiveRecord queries, runs them on the production server via kamal console, and interprets the results. Same questions, same data, same answers. No API key, no extra cost.

So when do you need each approach?

The skill file is better for developers. It has full codebase knowledge, can run any Ruby code, understands your models and scopes, and costs nothing. If you're the one answering data questions, build a skill file and stop there.

The SQL assistant is for when someone else needs answers. If your operations manager, client support team, or non-technical admin needs to investigate data without asking a developer — that's when the browser-based SQL assistant earns its $0.002 per query. It's a self-service tool. The skill file is a power tool for the person who built the system.

FAQ