Feature: PII detection and redaction before data export (NER + column heuristics)

[NKeleher]

Problem

DataSure processes survey data that routinely contains PII (names, phone numbers, GPS coordinates, addresses), but nothing in the app detects or removes PII before data is exported. Today there are three paths where data leaves the app, and all of them can carry PII:

1. Replication package (replication/package_builder.py) — writes the full raw dataset as CSV (3_data/1_raw/<survey>_raw.csv), a codebook containing up to 5 real sample values per column (replication/codebook.py), and the correction log with actual data values (current_value/new_value columns).
2. GPS check downloads (checks/gpschecks.py) — two st.download_button CSVs (outliers and comparison) containing coordinates, survey keys, and enumerator IDs.
3. The only current safeguard is an all-or-nothing "I understand this contains PII" checkbox in replication_view.py.

Users should be able to (a) detect likely-PII columns automatically, (b) redact or remove them as part of data preparation, and (c) have exports enforce those decisions so flagged PII never leaves the app unreviewed.

IPA has already built the detection approach in PovertyAction/PII_detection (MIT). This issue proposes porting its core ideas into DataSure's Streamlit app.

Proposed feature

Four components, designed as defense in depth: users can redact early in prep, and the export layer enforces flags regardless.

A. PII detection service (new module, e.g. processing/pii.py)

• Pass 1 — heuristics (always available, no model download). Ported from PII_detection:
  • Strict/fuzzy column-name and variable-label matching against multilingual restricted-word lists (restricted_words.py: SurveyCTO metadata like deviceid/subscriberid, name/address/email/phone/GPS/age terms; English, Spanish, Swahili — extend with French).
  • Format regexes on values (phone numbers, dates).
  • Sparsity heuristic (high unique/total ratio on string columns → likely open-ended/identifying).
  • Explicitly not porting the external-API checks (GeoNames, Forebears, Google-via-Selenium).
• Pass 2 — spaCy NER (requires an installed model). Run NER over a sample of values from string columns; flag columns by entity density and record the dominant entity type per column (PERSON, ORG, GPE, ...), which drives the mask label in redaction. Load models via st.cache_resource. (Note: PII_detection disabled NER in its shipped app for performance — sampling + model caching is how we keep it usable here.)
• Persistence. Store per-column flags in a logs.duckdb table (e.g. pii_flags_{alias}: column name, flag source, entity type, user decision), following the existing missing_codes_* pattern in utils/duckdb_utils.py / ConfigurationService pattern in utils/config_utils.py.

B. Redaction as a prep action

New "redact column(s)" prep action that masks values in place — replacing each value with the column's dominant NER entity label (e.g. [PERSON], [ORG], [GPE]), falling back to ***** when the column was flagged by heuristics only. Column drop remains available via the existing remove-column action.

Follows the established prep-action pattern, which gives logging, replay, undo, and replication-script coverage for free:

• PrepActions member in models/enums.py
• RedactColumnsOperation(PrepOperation) in processing/prep.py (modeled on RemoveColumnsOperation), registered in PrepProcessor.operation_handlers
• Description + confirmation formatter in utils/prep_utils.py and prep._generate_action_description
• UI handler in PrepStepHandler (views/prep_view.py, modeled on remove_column_handler) with the column multiselect pre-populated from the PII flags table
• Stata emitter in replication/prep_script_generator.py _EMITTERS (emit the equivalent replace, or a comment noting redaction was applied in DataSure where NER-derived labels aren't reproducible in Stata)

C. Export-time PII gate

• In build_replication_package(): apply a redact_for_export(df, flags) helper before writing the raw CSV, generating the codebook (its sample_values must be covered), and writing the correction log (current_value/new_value must be covered).
• Replace the current all-or-nothing checkbox in replication_view.py with a review UI listing flagged columns and the action that will be taken for each.
• Apply the same helper to the two GPS CSV downloads in checks/gpschecks.py — gate the download, not the in-app map/table (coordinates are legitimately needed for the check display).
• Unredacted export remains possible, but only via an explicit per-export override (a strengthened version of today's confirmation).

D. spaCy model management in the Streamlit UI

• Add spacy as a dependency; language models are not bundled.
• A settings section lets the user choose and download a model from within the app, limited to small models in three languages: en_core_web_sm (English), es_core_news_sm (Spanish), fr_core_news_sm (French) — with progress feedback and graceful handling of offline/restricted-network failures.
• With no model installed, Pass 1 heuristics still work; the UI shows an install hint for enabling NER.

User workflow

1. Import data as usual.
2. Open the PII review section (on the Prepare Data page): heuristics run instantly; NER runs if a model is installed.
3. Review flagged columns (with flag source and detected entity type); choose per column: mask / drop / keep.
4. Decisions are saved to the flags table and applied as prep actions (logged, replayable, removable).
5. At export time, the replication builder and GPS downloads enforce the flags and surface anything flagged-but-kept before data leaves the app.

Out of scope (possible follow-ups)

• External-API location/name population checks (GeoNames, Forebears)
• Partial within-cell free-text redaction (v1 masks whole values only)
• Hash-encoding of identifiers with an exportable mapping (PII_detection's "Encode" option)
• Languages beyond English/Spanish/French

Open questions

• NER sampling size per column: accuracy vs. speed tradeoff (and whether to let the user re-run on the full column).
• Should raw.duckdb ever be redacted in place? (Proposal: no — raw stays raw inside the local cache; redaction applies at prep and export.)
• Threshold tuning for fuzzy-match and sparsity heuristics to control false positives.
• Model download in restricted-network environments (offer a "install from local wheel" fallback?).

Implementation phases

• 1. Detection engine (heuristics) + flags storage + review UI on the prep page
• 2. Redact prep action (mask with entity label / *****)
• 3. Export gate (replication builder + codebook sample values + correction log + GPS CSVs)
• 4. spaCy model management UI + NER pass
• 5. Tests (tests/processing/, tests/utils/, tests/views/ per existing conftest patterns) and user-guide docs

References

• PovertyAction/PII_detection (MIT) — port targets: restricted_words.py (multilingual word lists), the word_match strict/fuzzy matching, format regexes, and sparsity logic in PII_data_processor.py; NER approach in find_piis_in_unstructured_text.py.
• Existing export surfaces: replication/package_builder.py, replication/codebook.py, checks/gpschecks.py, views/replication_view.py.