PII Detection & Continuous Learning Flow¶
🎯 The Complete Picture¶
PII detection in l0l1 serves dual purposes for a comprehensive continuous learning system:
┌─────────────────────────────────────────────────────────────────┐
│ User Submits Query │
│ SELECT name, email FROM users WHERE ssn = '123-45-6789' │
└─────────────────┬───────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ PII Detection Engine │
│ • Detects: email addresses, SSN, phone numbers, names, etc. │
│ • Uses: Presidio + custom SQL patterns │
│ • Result: [EMAIL: email, SSN: 123-45-6789, PERSON: name] │
└─────────────────┬───────────────────────────────────────────────┘
│
┌───────┴───────┐
│ │
▼ ▼
┌─────────────────┐ ┌──────────────────────┐
│ User Safety │ │ Learning Pipeline │
│ (Warning) │ │ (Sanitization) │
└─────────────────┘ └──────────────────────┘
│ │
▼ ▼
┌─────────────────┐ ┌──────────────────────┐
│ Show Warning: │ │ Sanitize Query: │
│ "PII Detected!" │ │ SELECT name, email │
│ │ │ FROM users WHERE │
│ Option to │ │ ssn = 'XXX-XX-XXXX' │
│ anonymize │ │ │
└─────────────────┘ └──────────────────────┘
│ │
│ ▼
│ ┌──────────────────────┐
│ │ Vector Embedding │
│ │ Store in Learning │
│ │ Database │
│ └──────────────────────┘
│ │
│ ▼
│ ┌──────────────────────┐
│ │ Future Similarity │
│ │ Search & Suggestions│
│ └──────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ Execute Original Query │
│ (User might legitimately need the PII data for their work) │
└─────────────────────────────────────────────────────────────────┘
🔄 Continuous Learning Benefits¶
1. Safe Pattern Recognition¶
-- Original (with PII):
SELECT name, email FROM users WHERE ssn = '123-45-6789'
-- Stored for learning (sanitized):
SELECT name, email FROM users WHERE ssn = 'XXX-XX-XXXX'
-- Future suggestions based on pattern:
SELECT name, email FROM users WHERE ssn = ?
SELECT name, email FROM users WHERE user_id = ?
2. Query Optimization Learning¶
-- User's query (after PII sanitization):
SELECT u.name, u.email, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > 'YYYY-MM-DD'
GROUP BY u.id
-- AI learns this pattern works well and suggests:
-- - Add LIMIT clause for performance
-- - Use indexes on created_at
-- - Consider user_id in WHERE for better performance
3. Smart Autocompletion¶
-- User types: "SELECT name, email FROM users WHERE"
-- AI suggests (from sanitized learning data):
-- ✅ created_at > '2024-01-01' (safe)
-- ✅ status = 'active' (safe)
-- ✅ id IN (SELECT ...) (safe)
-- ❌ ssn = '123-45-6789' (PII - not suggested)
💡 Implementation Deep Dive¶
Learning Service Flow¶
async def record_successful_query(self, workspace_id, query, execution_time, result_count):
# Step 1: Check for PII
if not self.pii_detector.is_safe_for_learning(query):
# Step 2: Sanitize before learning
sanitized_query = self.pii_detector.sanitize_for_learning(query)
query = sanitized_query
# Step 3: Generate vector embedding of CLEAN query
embedding = await self.model.generate_embedding(query)
# Step 4: Store sanitized query + embedding + performance metadata
learning_record = {
"query": query, # This is the SANITIZED version
"embedding": embedding,
"execution_time": execution_time,
"result_count": result_count,
"success_count": 1
}
# Step 5: Future similarity searches happen on clean data
self.learning_data[query_hash] = learning_record
PII Detection Methods¶
class PIIDetector:
def is_safe_for_learning(self, query: str) -> bool:
"""Check if query contains PII - if yes, don't learn as-is"""
pii_findings = self.detect_pii(query)
return len(pii_findings) == 0
def sanitize_for_learning(self, query: str) -> str:
"""Replace PII with generic placeholders for learning"""
anonymized, _ = self.anonymize_sql(query)
return anonymized
def anonymize_sql(self, query: str) -> Tuple[str, List[Dict]]:
"""Replace actual PII values with safe placeholders"""
# '123-45-6789' → 'XXX-XX-XXXX'
# 'john@email.com' → 'user@example.com'
# '555-123-4567' → '555-0123'
🛡️ Privacy Benefits¶
For Users:¶
- Real-time warnings: "You're about to query PII data"
- Anonymization options: "Want to see a safe version?"
- Audit compliance: All PII access is logged and warned
For Organizations:¶
- Learning without exposure: AI improves without storing sensitive data
- Compliance: GDPR/CCPA safe learning pipeline
- Governance: Clear audit trail of PII access
For AI Performance:¶
- Pattern recognition: Learns SQL structures, not data values
- Better suggestions: Recommends safe, optimized patterns
- Continuous improvement: Gets smarter without privacy risks
🔧 Configuration Options¶
# Fine-tune PII detection
L0L1_PII_ENTITIES=["PERSON", "EMAIL_ADDRESS", "PHONE_NUMBER", "SSN", "CREDIT_CARD"]
L0L1_LEARNING_THRESHOLD=0.8 # Confidence threshold for learning
# Learning behavior
L0L1_ENABLE_LEARNING=true
L0L1_ENABLE_PII_DETECTION=true
L0L1_ANONYMIZE_BEFORE_LEARNING=true # Default: true
📈 Analytics Workbench Integration¶
In your SQL workbench, this creates a seamless experience:
// User types query with PII
const query = "SELECT * FROM users WHERE email = 'john@company.com'"
// Real-time analysis shows:
const analysis = await analyzeSQL(query)
// {
// pii_detected: [{ entity_type: "EMAIL_ADDRESS", text: "john@company.com" }],
// suggestions: [
// "SELECT * FROM users WHERE user_id = ?", // From learning data
// "SELECT * FROM users WHERE status = 'active'" // Safe patterns
// ]
// }
// User gets:
// 1. Warning about PII
// 2. Better query suggestions (learned from sanitized data)
// 3. Option to anonymize
// 4. Query still executes (they might need the data)
This approach ensures AI gets smarter while keeping data private - the best of both worlds! 🎯