AltimateAI / AltimateAI/altimate-code

[Feature] Improve PII detection with column-name heuristics

Abierto
#65 0 comentarios 0 reacciones 0 asignados Ver en GitHub
enhancement priority:medium sql-engine
Lenguaje dominante
TypeScript
Estrellas
811
Forks
134
Merge medio
3 d 2 h
PR fusionados (30 d)
50

Descripción

## Problem

The `altimate_core_query_pii` tool returns "CLEAN" for queries that clearly select PII columns (SSN, credit_card_number, email, phone, date_of_birth). This happens because the tool needs schema context that isn't provided, leading to false negatives.

For a security-critical feature, false assurance is worse than no detection.

## Desired Behavior

PII detection should work even without full schema context by using column-name heuristics as a first-pass analysis.

## Implementation Notes

### Approach

**Phase 1: Column-name regex matching (quick win)**

Add a fast first-pass detector that flags columns based on name patterns, inspired by piicatcher's `ColumnNameRegexDetector`:

```
Personal: first_name, last_name, full_name, ssn, social_security, passport, date_of_birth, dob
Contact: email, phone, phone_number, mobile, address, street, city, zip, postal
Financial: credit_card, card_number, cvv, bank_account, iban, salary, income
Auth: password, passwd, secret, token, api_key
Health: diagnosis, medical, patient, prescription
```

This doesn't need warehouse access or API credentials — just parse the SQL to extract column names/aliases.

**Phase 2: Schema-enhanced detection**

When schema cache is populated:
- Use column types + names for better classification (e.g., `VARCHAR(9)` named `ssn` → high confidence SSN)
- Cross-reference with table context (columns in a `users` table are more likely PII than in `metrics`)

**Phase 3: Belt-and-suspenders**

Always run column-name heuristics even when `altimate_core_query_pii` reports CLEAN:
- If heuristics find potential PII but the API says clean → flag with lower confidence: "Potential PII detected based on column names (ssn, email). Verify with schema context."
- If both agree → high confidence result

### Key Files
- PII detection tool implementation
- Python engine methods for PII analysis
- Schema cache for column metadata

### Industry Patterns
- **Microsoft Presidio**: 3-layer approach — regex → NLP NER → context-aware enhancer, 50+ recognizers
- **piicatcher**: Column-name regex (fast) + data sampling (accurate), extensible detector classes
- **Snowflake**: `EXTRACT_SEMANTIC_CATEGORIES` assigns semantic + privacy category tags
- **BigQuery**: DLP API integration with auto-tagging

## Acceptance Criteria

- [ ] Column-name heuristics detect obvious PII patterns (ssn, email, credit_card, etc.)
- [ ] Works without warehouse access or API credentials
- [ ] Results clearly indicate confidence level and detection method
- [ ] False negatives reduced for queries with obviously-named PII columns
- [ ] Heuristics run as complement to (not replacement for) existing PII detection

Guía de contribución

Abrir la guía de contribución

Evaluación

Este issue todavía no se ha evaluado.

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.