Metadata-Version: 2.4
Name: colscan
Version: 0.2.0
Summary: Say what is inside each column of a table — semantic type and GDPR nature — with deterministic rules only. Runs offline.
Author: dbtool.it
License: Apache-2.0
Project-URL: Homepage, https://dbtool.it
Keywords: gdpr,data-discovery,pii,column-classification,csv,parquet
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: Legal Industry
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Quality Assurance
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: parquet
Requires-Dist: pyarrow>=15.0; extra == "parquet"
Provides-Extra: db
Requires-Dist: SQLAlchemy>=2.0; extra == "db"
Dynamic: license-file

# colscan

**Tell what is inside each column of a table — its semantic type and its GDPR
nature — with deterministic rules only. Offline, no model, no account, no data
leaving your machine.**

```console
$ pip install colscan
$ colscan clienti.csv
```

## Why

Whoever has to keep a register of processing activities (art. 30 GDPR) has to know
that the column named `f_12` holds national identification numbers. In databases
that have been alive for fifteen years nobody knows: the columns are called
`desc`, `campo3`, `note`, the documentation lies, and the person who designed the
schema left in 2014.

The tools that solve this — Collibra, BigID, Atlan, Cyera — sell it inside
governance platforms priced on request, and they want to connect to your database
**from their cloud**. Google Cloud DLP charges per unit inspected: ten million
cells cost thousands. At the low end there is nothing.

colscan is the low end. It reads a sample of each column, applies rules that a
person can read and an auditor can check, and prints what it found.

## What it does, and what it refuses to do

A rule either **proves** something or it does not. colscan keeps three answers
apart and never blurs them:

| answer | meaning |
|---|---|
| `personal` / `art.9` | a rule verified it — a mod-97 IBAN check, a codice fiscale check character, an exact match in an official list |
| `not personal` | same strength of evidence, and the type is not personal data |
| `not personal?` | a weak rule fired (any integer is a "sequential id"): an indication |
| `undetermined` | no rule can decide. Said plainly, with the reason |
| `empty` | the column is null or placeholder all the way down |

It does **not** guess to look useful. A column of surnames comes out
`undetermined`, because no regular expression can tell a surname from a city name
— many Italian surnames *are* place names. That answer is the honest one, and the
report says where the answer does exist (see the bottom of this page).

colscan is an analysis aid, **not a compliance certificate**. It reports evidence;
the legal qualification is the reader's.

## Example

```console
$ colscan examples/anagrafica.csv
```

```
colscan 0.2.0 · deterministic rules only · nothing leaves this machine

examples/anagrafica.csv · anagrafica.csv · csv, delimiter ';', encoding utf-8-sig
4 rows read, 4 sampled (seeded reservoir, reproducible)

COLUMN          TYPE            GDPR           NULL  UNREAD  CONF  SAMPLE
-------------------------------------------------------------------------
id              sequential_id   not personal?    0%      0%  0.30  1 | 2 | 3
codice_fiscale  fiscal_code_it  personal        25%     33%  1.00  RS…1U | RS…1R | VRDLGU85M15F205Z
iban            iban            personal        25%     33%  1.00  IT…56 | IT…56 | IT60XY542811101000000123456
email           email           personal        25%      0%  0.98  ma…om | l.…om | g.…om
note            free_text       personal        25%    100%  name  richiamare lunedì | sc…om | cliente storico
cognome         person_surname  undetermined     0%     75%  name  Rossi | Verdi | Esposito
importo_euro    amount          not personal?    0%      0%  0.50  1.234,50 | 89,00 | 12,00
sesso           gender_label    personal         0%    100% 0.90*  M | F | M
cap             postcode        not personal     0%      0% 0.90*  20121 | 10121 | 80100
diagnosi        icd_code        art.9           25%      0%  0.85  … | … | …
vuota           unknown         empty          100%      0%  1.00  
data            date            not personal?    0%      0%  0.85  12/05/2024 | 13/05/2024 | 14/05/2024

  ! note: email inside the text of 25% of the rows (1 cells, rule-verified)
  ? note: 100% of the non-null cells match no rule: no deterministic answer exists
  ? cognome: the column name is the only evidence
  ? cognome: 75% of the non-null cells match no rule: no deterministic answer exists
  > cognome: POST /v1/split on dbtool.it resolves given name vs surname, any script

12 columns · 6 hold personal data with rule-level certainty (1 of them a special category, art. 9) · 1 undetermined
Sample values that a rule recognised as personal are masked; use --reveal to print them.
```

Reading the report:

* `!` a rule verified something inside the column — including one row in a
  thousand. A mod-97 that passes is not noise, so there is no frequency threshold
  to tune: a single confirmed e-mail address inside a `note` column puts that
  column in the register.
* `?` something a person has to check, with the reason.
* `~` the rules also saw other types, with their share.
* `>` a column whose type is decidable, but not by a rule.
* `UNREAD` is the share of non-null cells that matched no rule. It is kept apart
  from `NULL` on purpose: a column of free text is not an empty column, and an
  empty column is not an unreadable one.

Sample values recognised as personal are masked (`RS…1U`) so the report can be
pasted into a ticket. `--reveal` prints them in full.

## Usage

```console
colscan clienti.csv                        # CSV/TSV: separator and encoding sniffed
colscan clienti.csv --delimiter ';'        # or told
colscan export.parquet                    # needs: pip install "colscan[parquet]"
colscan warehouse.sqlite --table clienti  # SQLite: no extra needed
colscan postgresql://user:pw@host/db --schema public   # needs: pip install "colscan[db]"
colscan clienti.csv --json                # machine-readable, same content
colscan clienti.csv --rows 100000         # sample size per column (default 10000)
colscan clienti.csv --fail-on-personal    # exit 1 when personal data is certain
```

`python -m colscan …` works too, for a machine where `pip install --user` puts the
console script somewhere that is not in `PATH`.

Sampling is a seeded reservoir over the whole table, not the first N rows: real
exports are sorted, and the head of a sorted file is not the file. Same `--seed`,
same report — a report nobody can reproduce is not evidence.

Two worked examples, with their reports committed next to them (and a test that
keeps them true): [`examples/`](examples/README.md).

### Python

```python
from colscan import scan_column

result = scan_column(["RSSMRA80A01H501U", "VRDLGU85M15F205Z"], name="cf")
result["type"]              # 'fiscal_code_it'
result["gdpr"]["personal"]  # True
result["gdpr"]["basis"]     # 'rule'
```

As a library the bundled reference lists load on demand — one call, then ICD-10
and the Italian registers decide there too:

```python
from colscan import reference, scan_column

reference.load()                                   # the bundled official lists
icd = scan_column(["C50", "J45", "E11"], name="diagnosi")
icd["type"]                          # 'icd_code'
icd["gdpr"]["special_category"]      # True — art. 9, exact match in ICD-10-CM
icd["gdpr"]["basis"]                 # 'rule'
```

## What it recognises

* **Check digits — certainty.** IBAN (mod-97), Italian codice fiscale (including
  omocodia variants), Italian VAT numbers. Payment cards are the one exception
  worth reading below, because one check digit is not the same evidence as a
  mod-97.
* **Unmistakable shapes.** E-mail, URL, UUID, IPv4/IPv6, MAC, hex digests,
  ISO 8601 date and datetime, ISO periods (`2026-W33`, `2026Q3`), opening hours,
  coordinates, international phone numbers, file paths, domains.
* **Closed vocabularies.** ISO 3166 country codes, ISO 4217 currencies,
  continents, booleans, sex labels, administrative regions.
* **Italian identifiers nobody else implements.** CIG and CUP (structure only,
  because neither publishes a verifiable check digit — the reasoning is in the
  source), and, against the official registers bundled in the package, Belfiore
  cadastral codes, ISTAT municipality codes, NUTS, school codes, IPA and
  e-invoicing office codes, ATECO.
* **Personal data buried in free text.** A `note` column with one e-mail address,
  IBAN, codice fiscale or phone number per thousand rows is reported as a column
  that *contains* personal data — while staying a column of notes.

### Payment cards, and why they get treated differently

A card number carries **one** check digit (Luhn), so about one card-shaped digit
sequence in ten satisfies it by luck — against roughly one in thousands for an
IBAN mod-97. Two consequences, both deliberate:

* A number is only called a card if it also has an **issued prefix** (IIN) with a
  length that issuer actually uses, per ISO/IEC 7812. Measured on 5.000 random
  16-digit sequences: 9,6% pass Luhn, 2,6% pass Luhn *and* the prefix. The price is
  declared — closed-loop cards (loyalty, meal vouchers) and Maestro numbers outside
  the published IIN list are no longer recognised.
* A **minority** of such cells inside a column of numeric codes is reported, but
  **not** as certainty, and the report says why: «3 of the 60 cells that have the
  shape and prefix of a card also pass its single check digit, which chance alone
  does 10% of the time». A column of order numbers is not a column of payment
  cards, and an earlier version of this tool said it was.

A column that is *made of* cards, or a card found inside a sentence in a free-text
column, is still reported as certainty — that is the finding this tool exists for.

### Reference lists: bundled, official, replaceable

Health data and Italian register-backed identifiers are decided against four
official lists, **bundled in the package** — a fresh install reports a `diagnosi`
column as `icd_code`, art. 9, with no flag and nothing to download:

| list | contents | source | licence |
|---|---|---|---|
| `icd10.txt` | 74,721 ICD-10-CM FY2026 codes | CDC / NCHS | public domain (US Gov) |
| `it_territori.json` | 7,896 municipalities: Belfiore cadastral, ISTAT, NUTS | ISTAT | CC BY 4.0 |
| `it_ipa.json` | 23,744 IPA + 122,775 e-invoicing office codes | AGID / IndicePA | CC BY 4.0 |
| `it_ateco.txt` | 4,229 ATECO codes (2025 + 2022) | ISTAT | CC BY 4.0 |

Provenance, retrieval date and SHA-256 of every list: `colscan/reference_data/SOURCES.md`
(regenerate with `scripts/fetch_it_codes.py` in the dbtool-platform repo). A test
pins each file to its recorded hash: a list a rule decides with is not documentation.

No model weights and no training data ship, ever — the lists are the only data in
the package. If you keep the registers updated yourself, your copies win:

```console
colscan clienti.csv --reference-dir ~/reference   # or COLSCAN_REFERENCE_DIR
```

Expected file names, all optional: `icd10.txt`, `it_territori.json`,
`it_ipa.json`, `it_ateco.txt`. A missing file puts that rule back to abstaining —
see `colscan/reference.py` for the JSON keys.

## Databases: read-only, and checked

The tool issues nothing but `SELECT`. On top of that it asks the server to make the
session read-only and **verifies the answer** before reading any table — if the
server does not confirm it, colscan stops instead of scanning. SQLite files are
opened `mode=ro`, which also stops a mistyped path from creating an empty database.
Each table says which of the two happened in its header line, and a dialect with no
known read-only switch is scanned with that stated plainly rather than implied.

Point it at an account with `SELECT` privileges only anyway: two locks are better
than one, and the second one is the only one your DBA can see.

## Privacy

colscan makes no network connection. Cell values are never written anywhere: they
are read, counted and dropped. The report contains types, fractions and masked
samples. There is no telemetry and there is no account.

## Requirements

Python 3.10+. No dependencies. `pyarrow` only for Parquet, `SQLAlchemy` only for
databases other than SQLite.

---

A rule proves what has a verifiable form. It cannot decide whether a name is a
given name or a surname, what gender a first name suggests, or which institution
an affiliation string refers to — no regular expression can. **dbtool.it** serves
exactly those:

| endpoint | what it resolves |
|---|---|
| `POST /v1/gender` | gender (and likely nationality) from a first name |
| `POST /v1/split` | given name vs surname, any script |
| `POST /v1/affiliation` | affiliation -> country, plus ROR institution linking |

Free plan, no data stored: <https://dbtool.it>
