# 🚀 Enhanced SQL Safety Analysis for AI Assistant

[AHP15]

🎯 Proposal Overview

I'd like to propose a significant enhancement to Supabase's AI Assistant SQL safety analysis capabilities. After analyzing the codebase, I've identified critical areas where we can improve SQL query classification, security analysis, and remove deprecated code.

🔍 What I Found

In /apps/studio/components/ui/AIAssistantPanel/AIAssistant.utils.ts, there are several functions marked as deprecated with comments indicating they "aren't scalable":

/** @deprecated [Joshen] Ideally we move away from this as this isn't a scalable way to deduce */
export const containsUnknownFunction = (query: string) => {

/** @deprecated
 * [Joshen] This isn't really a scalable way to reduce this behaviour, we now have support
 * for a readonly connection string which we can use this to run queries, and is a much
 * clearer way to deduce if the query is read only or not
 */
export const isReadOnlySelect = (query: string): boolean => {

💡 The Solution: @supabase/sql-analyzer

I propose creating a new package that provides:

✨ Key Features

• Advanced SQL Parsing: Proper AST-based analysis instead of string matching
• Smart Query Classification: DDL, DML, DCL operations with risk assessment
• Security Analysis: Permission requirements, side effect detection
• AI Integration: Enhanced validation for AI-generated SQL
• Performance Optimized: Caching and lazy loading
• Highly Configurable: Customizable security rules and analysis depth

🏗️ Package Structure

packages/sql-analyzer/
├── src/
│   ├── parser/
│   │   ├── SqlParser.ts           # Core SQL parsing logic
│   │   ├── QueryClassifier.ts     # Classify query types
│   │   └── SecurityAnalyzer.ts    # Security & safety analysis
│   ├── types/
│   │   ├── QueryTypes.ts          # Type definitions
│   │   └── SecurityReport.ts      # Security analysis types
│   └── utils/
│       ├── PostgresKeywords.ts    # Postgres-specific keywords
│       └── SafetyRules.ts         # Configurable safety rules

🔧 API Design

// Main API
export interface SqlAnalysisResult {
  query: ParsedQuery
  riskLevel: QueryRiskLevel
  category: QueryCategory
  permissions: RequiredPermission[]
  warnings: SecurityWarning[]
  performance: PerformanceEstimate
}

export async function analyzeSqlQuery(
  sql: string, 
  options?: AnalysisOptions
): Promise<SqlAnalysisResult>

// Integration helpers for AI Assistant
export function isQuerySafeForAi(result: SqlAnalysisResult): boolean
export function getAiPromptType(result: SqlAnalysisResult): AiPromptType

🎯 Benefits

For Developers:

• More accurate AI-generated SQL
• Better understanding of query risks and permissions
• Enhanced security warnings and suggestions

For Supabase:

• Removes technical debt (deprecated functions)
• Scalable architecture for future AI enhancements
• Improved security and user trust
• Foundation for advanced AI features

📊 Success Metrics

• Accuracy: >95% correct query classification
• Performance: <50ms analysis time for typical queries
• Security: Zero false negatives for dangerous queries
• Compatibility: 100% backward compatibility

🤝 Community Input Needed

I'd love to get feedback from the community on:

1. Feature Priorities: Which analysis features would be most valuable?
2. API Design: Does the proposed API meet your needs?
3. Security Requirements: What security checks are most important?
4. Integration Points: How should this integrate with existing tools?
5. Testing Strategy: What SQL edge cases should we prioritize?

🎬 Next Steps

If there's community interest, I'm ready to:

1. Create a detailed RFC with technical specifications
2. Start development with the core parsing engine
3. Set up collaboration with Supabase team members
4. Provide regular progress updates and demos

💭 Discussion Points

• Would this package be useful for other parts of Supabase beyond AI Assistant?
• Should we support other SQL dialects (MySQL, SQLite) or focus on PostgreSQL?
• What's the best approach for handling complex queries with CTEs and subqueries?
• How can we make this accessible for community contributions?

---

This represents a significant enhancement to Supabase's AI capabilities while addressing technical debt - exactly the type of foundational work that can unlock new features and improve developer experience.

What do you think? Would you find this useful? Any suggestions or concerns? 🚀