Search docs.molo17.com

    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.

    How-to guide

    For a step-by-step walkthrough tested on a running Gluesync, see the how-to guide Query pipeline data with Query Studio.

    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. Select the Settings gear icon in the upper-right corner → 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.

    Query Forge (JDBC clients)

    Query Forge reads through Query Studio, so its results are masked the same way for the JDBC user. There is no PII visible toggle on this path: while platform-wide masking is on, JDBC results stay masked for every role. Query Forge also keeps the source database from computing with a masked column: it pushes a masked column to the source only as a plain read, a comparison with a literal of the same type, or a count, and does everything else with it over the masked values.

    Two points matter when you rely on masking with Query Forge:

    • A column that Query Studio masks only because its value sampler recognised the values in a result is not known to Query Forge in advance. A value computed from it on the source, such as MAX(notes) or notes AS n, can come back unmasked. Run Scan table for PII or Mark as PII so that a verdict is stored for such columns.

    • A result with no unmasked key is paged in an order that includes its masked columns, so the order of the rows can reveal the rank order of masked values.

    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 is not an access boundary. Masking hides values in results, but a user who may query a table can still infer masked values with filters (WHERE email LIKE 'a%'). To keep data away from a user, deny the table or schema with a data-access policy.

    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.