AI Agent with Database Access for Mobile App

Implementing AI Agent with Database Access in a Mobile Application

Text-to-SQL is old task, but with LLM it became reliable enough for production. User asks "show my expenses last month by category," agent translates to SQL query, executes, formats result. Without separate analytics screen, without fixed filters.

Why Text-to-SQL on Mobile is Separate Task

Direct mobile app access to production DB—bad idea. Even read-only. Correct architecture: mobile client → backend API with agent → DB. Backend validates generated SQL, limits accessible tables, controls user permissions.

On client use either local DB (SQLite via Room on Android, Core Data / GRDB on iOS) for app offline data, or agent runs on server and client gets ready data.

Teaching Model Your DB Schema

Model doesn't know your schema. Must pass it in system prompt or via get_schema tool. Don't dump full DDL of 200 tables—take only relevant. For personal finance app, 5–8 tables enough.

-- Example schema for prompt (simplified)
CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    amount DECIMAL(10,2) NOT NULL,  -- negative = expense
    category VARCHAR(50),            -- 'food', 'transport', 'entertainment'
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

In system prompt add: "You generate SQL queries ONLY for SELECT. Never use INSERT, UPDATE, DELETE, DROP. All queries must contain WHERE user_id = :user_id."

Prompt limit—first protection layer. Second layer—server validation: parse generated SQL AST (library sql-parser or pg_query for PostgreSQL), check query type and table list.

Room and Agent: Local DB on Android

If agent works with local app data via Room:

// Tool interface for agent
class DatabaseTool(private val db: AppDatabase) {
    suspend fun executeQuery(sql: String): String {
        return try {
            // Only SELECT via SupportSQLiteDatabase
            val cursor = db.openHelper.readableDatabase.query(sql)
            cursor.toJsonArray().toString()
        } catch (e: Exception) {
            """{"error": "${e.message}"}"""
        }
    }
}

SupportSQLiteDatabase.query() takes raw SQL—convenient for agent. Room DAO not suitable: requires fixed queries at compile-time.

Important: Room by default doesn't allow raw queries on main thread. Everything must be in suspend fun or withContext(Dispatchers.IO).

Result Formatting

Agent got rows from DB—need return to user in readable form, not JSON array. Pass query result back to model with instruction to format:

Tool result: [{"category":"food","total":"-15420"},{"category":"transport","total":"-8300"}]
→ Model formats: "Last month you spent 154.20 BYN on food and 83.00 BYN on transport"

For numeric data, query model to create Markdown table—easy render on mobile via any Markdown parser (Markwon on Android, AttributedString + custom render on iOS, flutter_markdown on Flutter).

Security: What's Mandatory

• Parameterized subqueries where possible (even for SELECT)
• Whitelist tables and columns accessible
• Result limit mandatory: LIMIT 1000 in validator
• Query execution timeout (PostgreSQL: SET statement_timeout = '5s')
• Log all generated queries for audit

Stages and Timeline

Analyze DB schema, define accessible tables → develop system prompt with schema description → implement SQL validator on backend → integrate agent cycle → format results → test on diverse user queries → monitor generation quality.

For local SQLite/Room with 3–5 tables—2–3 weeks. Server agent with PostgreSQL, validator, complex schema—4–6 weeks.