AltimateAI / AltimateAI/altimate-code

[Feature] Improve PII detection with column-name heuristics

Aberta
#65 0 comentários 0 reações 0 responsáveis Ver no GitHub
enhancement priority:medium sql-engine
Linguagem predominante
TypeScript
Estrelas
811
Forks
134
Merge médio
3d 2h
PRs com merge (30d)
50

Descrição

## 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

Guia de contribuição

Abrir o guia de contribuição

Avaliação

Esta issue ainda não foi avaliada.

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.