PII discovery and masking

Gluesync 2.3 classifies columns that look like personally identifiable information (PII), shows those labels across the Control Plane, and masks cell values in Query Studio results, copies, and exports. AI Studio and MCP tool results are always masked. Classification does not change replicated data on the wire.

What this feature does

PII discovery is a Core Hub service. It inspects column names (and, when you ask it to scan, a small sample of values), stores a verdict per column, and uses that verdict in two ways:

  • Labels. The Control Plane shows a PII tag next to the column wherever you pick or inspect fields — Query Studio, Fields Editor, entity mapping, UDFs, filters, custom keys, CDC field lists.

  • Masking. When Query Studio (or an MCP / AI path that executes SQL) returns rows, classified columns are replaced with a mask before the response leaves Core Hub. The browser never receives the original cell.

Replication, snapshots, CDC, and target writes are unchanged. Masking is a read-time overlay for Query Studio and AI tool results.

Disabling platform-wide masking (see [platform-toggle]) reveals classified values in Query Studio for every user. AI Studio, Query Studio AI helper tool traces, and MCP execute_sql results stay masked even when that toggle is off.

Recognized labels

Core Hub treats these labels as PII for badges and masking. Structural catalog hints such as identifier, uuid, mostly-null, or constant are not masked.

Label Typical column names Masked value shape

email

email, e_mail, mail_address

First character of the local part, then @ plus the original top-level domain (for example a@.com). The rest of the local part and domain are not kept.

phone

phone, mobile, telephone, tel

Bullet mask plus the last two characters.

iban

iban

Bullet mask plus the last four characters.

pan

credit_card, card_number, pan, cc_num

Digits only, then bullet mask plus the last four digits.

it.codice-fiscale

codice_fiscale, fiscal_code

Full bullet mask (••••).

ip

ip_address, client_ip, remote_addr

Full bullet mask.

national-id

ssn, national_id, nin, pesel, aadhaar, tckn, nric, 身份证

Full bullet mask. Manual Mark as PII uses this label when you do not pick another one.

date-of-birth

date_of_birth, birth_date, dob

Full bullet mask.

passport

passport, passport_no, pasaporte, 护照, 旅券

Full bullet mask.

name

first_name, apellido, cognome, nachname, 氏名, 성명

Full bullet mask.

address

address, indirizzo, dirección, adresse, 地址, 住所

Full bullet mask.

postal-code

postal_code, zip, cep, plz, 郵便番号

Full bullet mask.

tax-id

tax_id, vat_number, partita_iva, nif, rfc, cpf, gstin, tfn

Full bullet mask.

bank-account

bank_account, routing_number, sort_code, clabe, ifsc, 口座番号

Full bullet mask.

driver-license

driver_license, patente, cnh, permis_conduire, führerschein, 運転免許

Full bullet mask.

secret

password, api_key, access_token, client_secret, senha, 密码

Full bullet mask.

Empty and null cells stay empty. Masking never invents a value.

How classification works

Discovery is two-tiered. Both tiers write the same stored verdict (label, confidence, sourceRule, optional sampleSize / matches). A later user override wins over automation.

Name and type (always on)

The name classifier runs on ordinary schema discovery. It does not read cell values, so it is cheap enough to run whenever Core Hub lists columns.

It normalizes the column name (lowercase, Unicode letters and digits preserved, separators converted to _) and matches bounded tokens. Token boundaries prevent accidental matches such as name inside username or hostname. Confidence for a name match is typically 0.82 (0.95 for secrets).

The dictionary is intentionally global. It includes English plus common aliases in Spanish, Portuguese, Italian, French, German, Dutch, Polish, Turkish, Chinese, Japanese, and Korean. Examples include correo_electronico, téléphone, geburtsdatum, endereço, tc_kimlik, 身份证, 郵便番号, and 주민등록번호. Labels remain language-neutral (name, address, national-id); the regional detector is kept in sourceRule for audit.

A full-catalog scan (Gluesync Connect / agent catalog walk) reuses the same classifier and persists verdicts so pipeline screens can show tags without opening Query Studio.

Sample-based scan (on demand)

From Query Studio, Scan table for PII or Scan schema for PII (and the matching MCP tools) run classifiers that look at a small sample of values in addition to the name. The classify API response reports whether sampling actually ran (sampled: true|false). Schema scans are capped (maxTables, default 40) so a large catalog cannot stall the hub.

Use sampling when a column is named generically (contact, value_1) but the contents look like emails or card numbers. Use the name classifier alone when you only need a first pass.

Regional value checks

Sample scanning validates high-confidence formats rather than treating every long number as PII:

Region Detector Emitted label

Global

Email, E.164 phone, IPv4 and IPv6, IBAN mod-97, payment-card Luhn

email, phone, ip, iban, pan

Italy

Codice fiscale structure and control character

it.codice-fiscale

United States / Canada

US SSN allocation rules; Canadian SIN Luhn checksum

national-id

Brazil

CPF check digits

national-id

India

Aadhaar Verhoeff checksum

national-id

China

18-character resident identity checksum

national-id

South Africa

13-digit identity number Luhn checksum

national-id

Türkiye

TCKN check digits

national-id

Spain

NIF / NIE control letter

tax-id

France

NIR key (including Corsica department codes)

national-id

Australia

TFN weighted checksum

tax-id

The badge stays generic across countries; for example a valid Brazilian CPF emits label national-id with sourceRule=checksum/br.cpf. This avoids a different UI taxonomy for every jurisdiction while retaining the evidence used to classify it.

Manual override

On a column in the Query Studio schema tree:

  • Mark as PII stores a user verdict (default label national-id unless you send another recognized label).

  • Not PII dismisses automated verdicts for that column so it is no longer tagged or masked.

Overrides live in Core Hub’s SQLite store with the other Query Studio state. They survive hub restarts. They do not rewrite the source database.

Where labels appear

Once a column has a PII verdict, the Control Plane shows a compact tag next to the field name:

  • Query Studio schema tree (columns).

  • Query Studio result grid headers and the record-view modal.

  • Fields Editor column list.

  • Entity column mapping.

  • UDF column picker.

  • Filter fields, custom key fields, CDC field multi-selects, and Custom Field Functions column lists.

The tag is informational. It does not block mapping, filtering, or replication. It exists so you can see sensitive columns before you copy a result set or send a query to an LLM.

Query Studio masking

Default behavior

When you run a statement in Query Studio:

  1. Core Hub executes SQL as usual.

  2. It joins result columns to stored PII verdicts (by agent, schema, table, and column name when the driver reports them).

  3. Classified columns are masked in the row payload and flagged masked: true on the column metadata.

  4. The grid, record view, copy actions, and CSV / JSON / SQL / Markdown / HTML / XML / XLSX exports all see the masked values (or omit masked columns from client-side copies that rebuild the file in the browser).

  5. Masked cells cannot be edited in place. Double-click and cell actions are disabled.

Headers still show the PII tag so you know why the cell is masked.

Reveal values (privileged users)

SUPER_ADMIN and MANAGER can turn on PII visible in the Query Studio toolbar. That sends unmaskPii: true on the execute request. Core Hub unmasks only when:

  • platform-wide masking is still enabled, and

  • the caller’s role has canUnmaskPii (SUPER_ADMIN or MANAGER).

VIEWER (and other roles without that permission) never see the toggle. If a client forges unmaskPii: true, Core Hub still masks.

Turning the toolbar toggle off (or leaving it off) keeps masking on for that session.

What stays masked anyway

These paths force masking regardless of the toolbar toggle and regardless of [platform-toggle]:

  • Query Studio AI helper schema context and any SQL the helper executes on your behalf.

  • AI Studio / MCP execute_sql and related tool traces.

  • Suggested prompt Scan this schema for hidden PII, which calls classify_table / classify_schema (labels only, never raw cells).

The model is instructed, and the server enforces, that classified cell values never enter the LLM prompt.

Platform-wide setting

A SUPER_ADMIN can turn masking off for the whole instance from Settings → Global settings.

  1. Open the user menu → Settings → Global settings.

  2. Find PII masking.

  3. Use Mask PII platform-wide.

  4. The control is Enabled by default. That is also the value when the key has never been stored.

When you disable it:

  • Query Studio returns unmasked cells to every role (the per-session PII visible toggle is hidden, because there is nothing left to unmask).

  • A warning in Global settings reminds you that classified values are visible to every Query Studio user.

  • AI and MCP results remain masked.

  • PII tags still appear. Classification and overrides keep working; only the user-facing Query Studio mask is skipped.

The setting is stored as global configuration key PII_MASKING_ENABLED ({ "enabled": true } by default). Only SUPER_ADMIN can read or write GET / PUT /global-config/pii-masking.

Turn this off only on isolated lab instances, or during a supervised incident review. It is not a per-user exception. Every Query Studio user on the hub sees the same unmasked grid.

Permissions

Capability SUPER_ADMIN MANAGER VIEWER

See PII tags

Yes

Yes

Yes

Run classify / mark / dismiss (Query Studio read)

Yes

Yes

Yes (read-only Query Studio)

Masked Query Studio results (default)

Yes

Yes

Yes

Toolbar PII visible

Yes

Yes

No

Disable platform-wide masking

Yes

No

No

See Users and roles for the rest of the Query Studio matrix (writable SQL, shared saved queries, audit log).

Worked example (Query Studio)

  1. Open Query Studio, pick a pipeline and a SQL-capable agent.

  2. In the schema tree, expand a table that has an email column. A PII tag should already appear from the name classifier.

  3. Right-click the table → Scan table for PII if you want a sample-based pass on generically named columns.

  4. Run SELECT * FROM customers LIMIT 20.

  5. The email header shows the tag. Cells look like a@.com, not alice@example.com. Copy as CSV / JSON and Export CSV omit or mask those columns the same way.

  6. If you are a manager or super admin and you must see the original values, enable PII visible and run the query again. Disable it when you are done.

  7. Right-click a false positive → Not PII. Right-click an unclassified national-id column → Mark as PII.

REST API

All Query Studio classify routes require a Bearer token and Query Studio read permission. They live under /query-studio/pipelines/{pipelineId}/agents/{agentId}.

Classify one table

POST /query-studio/pipelines/{pipelineId}/agents/{agentId}/classify
Content-Type: application/json

{
  "schema": "public",
  "table": "customers"
}

Response (shape):

{
  "schema": "public",
  "table": "customers",
  "sampled": true,
  "columns": [
    {
      "name": "email",
      "dataType": "VARCHAR",
      "verdicts": [
        {
          "label": "email",
          "confidence": 0.82,
          "sourceRule": "name/email"
        }
      ]
    }
  ]
}

The body contains labels only. It does not include sampled cell values.

Classify a schema

POST /query-studio/pipelines/{pipelineId}/agents/{agentId}/classify-schema
Content-Type: application/json

{
  "schema": "public",
  "maxTables": 40
}

Response includes tables[] (each the same shape as a table classify) and truncated when the table cap was hit.

Override sensitivity

PUT /query-studio/pipelines/{pipelineId}/agents/{agentId}/columns/sensitivity
Content-Type: application/json

{
  "schema": "public",
  "table": "customers",
  "column": "notes",
  "sensitivity": "pii",
  "label": "national-id"
}

sensitivity is pii (mark) or none (dismiss). Success is HTTP 204 No Content.

Execute with or without unmask

POST …​/execute accepts options.unmaskPii (boolean, default false). Query columns in the response include verdicts and masked. Forcing unmaskPii has no effect for roles without canUnmaskPii, and no effect on AI / MCP execute paths.

Platform setting

GET  /global-config/pii-masking
PUT  /global-config/pii-masking
Content-Type: application/json

{ "enabled": true }

SUPER_ADMIN only. HTTP 202 Accepted on update.

MCP tools

The Core Hub MCP catalog adds two read tools. Prefer them over execute_sql when an agent is looking for hidden PII — they return labels and confidence, never raw cells.

Tool Required arguments Notes

classify_table

pipeline_id, agent_id, schema, table

Same as POST …​/classify.

classify_schema

pipeline_id, agent_id, schema

Optional max_tables (default 40). Same as POST …​/classify-schema.

execute_sql still redacts classified columns in its result payload and tool trace. Query Studio AI helper includes a suggested prompt: Scan this schema for hidden PII.

See Core Hub MCP server.

What this is not

  • It is not column-level encryption or tokenization in the source or target database.

  • It does not stop replication of PII. Use mapping, filters, and UDFs if a column must not leave the source.

  • It does not replace a dedicated DLP product. Name matching can miss oddly named columns until you scan or mark them.

  • It does not apply to Query Forge JDBC clients. Federated SQL is a different path; treat those credentials as production-grade.

Troubleshooting

A column looks like PII but has no tag

  • The name may not match a known token (contact_1, c1). Run Scan table for PII or Mark as PII.

  • Overrides of none hide automation. Clear the dismiss if you still want a tag.

  • Result-grid tags need the driver to report tableName / schemaName. Computed columns (SELECT email \|\| domain) may not join to a stored verdict.

Cells are still masked after I enabled PII visible

  • Confirm your role is MANAGER or SUPER_ADMIN.

  • Re-run the query after toggling. The previous result set stays as it was fetched.

  • If you came from an AI helper or MCP execute, that path cannot unmask.

Everyone can read emails in Query Studio

  • Check Settings → Global settings → PII masking. If Mask PII platform-wide is disabled, turn it back on.

  • Only a super admin can change that setting.

AI chat mentioned a real email

  • That should not happen for classified columns on execute/tool-trace paths. File a support ticket with the conversation id and do not paste the raw value into the ticket if you can avoid it. Use Scan table for PII / Mark as PII so the column is classified before the next prompt.

Related pages