135 lines
5.7 KiB
JavaScript
135 lines
5.7 KiB
JavaScript
#!/usr/bin/env node
|
|
// Reads every CSV in data/seed/ and writes a scrubbed copy to data/seed-scrubbed/.
|
|
// Goal: preserve structure (column names, row counts, value patterns, relational
|
|
// integrity via deterministic mapping) while stripping personal data so the
|
|
// scrubbed output is safe to share with an LLM.
|
|
//
|
|
// What gets scrubbed:
|
|
// - Columns whose header matches name/email/phone/address/iban/etc.
|
|
// - Cells matching email / phone / IBAN regex regardless of column.
|
|
// - Free-text "notes/comments/bemerkung" columns are blanked unless --keep-notes.
|
|
// Same input value maps to the same fake (deterministic via hash) so foreign
|
|
// keys, repeated organisations, repeated emails etc. still line up.
|
|
//
|
|
// Usage:
|
|
// node scripts/scrub-csvs.mjs
|
|
// node scripts/scrub-csvs.mjs --keep-notes # keep notes column verbatim
|
|
// node scripts/scrub-csvs.mjs --dry-run # report only
|
|
|
|
import { readdirSync, readFileSync, writeFileSync, mkdirSync } from 'node:fs'
|
|
import { createHash } from 'node:crypto'
|
|
import { join, basename } from 'node:path'
|
|
|
|
const SRC = 'data/seed'
|
|
const DST = 'data/seed-scrubbed'
|
|
const args = new Set(process.argv.slice(2))
|
|
const KEEP_NOTES = args.has('--keep-notes')
|
|
const DRY = args.has('--dry-run')
|
|
|
|
mkdirSync(DST, { recursive: true })
|
|
|
|
const PII_HEADER = /^(name|vorname|nachname|first[_ ]?name|last[_ ]?name|full[_ ]?name|email|e[-_ ]?mail|mail|phone|telefon|tel|mobile|handy|fax|address|adresse|street|strasse|straße|zip|plz|city|ort|stadt|country|land|iban|bic|kontoinhaber|account|signatur|password|passwort)$/i
|
|
const NOTES_HEADER = /^(note|notes|notiz|notizen|comment|comments|kommentar|bemerkung|bemerkungen|remark|remarks|message|nachricht|description|beschreibung|free[_ ]?text)$/i
|
|
const EMAIL_RE = /[\w.+-]+@[\w-]+\.[\w.-]+/g
|
|
const PHONE_RE = /(?:\+\d{1,3}[\s-]?)?(?:\(\d+\)[\s-]?)?\d[\d\s\-/]{6,}\d/g
|
|
const IBAN_RE = /\b[A-Z]{2}\d{2}[A-Z0-9]{10,30}\b/g
|
|
|
|
function fakeFrom(prefix, value) {
|
|
if (value == null || value === '') return ''
|
|
const h = createHash('sha256').update(prefix + '|' + value).digest('hex').slice(0, 8)
|
|
return `${prefix}_${h}`
|
|
}
|
|
function fakeEmail(v) { return fakeFrom('user', v) + '@example.test' }
|
|
function fakeName(v) { return 'Person_' + createHash('sha256').update(v).digest('hex').slice(0, 6) }
|
|
function fakePhone(v) { const h = createHash('sha256').update(v).digest('hex'); return '+49' + parseInt(h.slice(0, 9), 16).toString().padStart(10, '0').slice(0, 10) }
|
|
function fakeIban(v) { return 'DE' + createHash('sha256').update(v).digest('hex').slice(0, 20).toUpperCase() }
|
|
function fakeGeneric(prefix, v) { return fakeFrom(prefix, String(v)) }
|
|
|
|
// Minimal CSV parser — handles quoted fields, embedded commas, doubled quotes, CRLF.
|
|
function parseCSV(text) {
|
|
const rows = []
|
|
let row = [], field = '', inQ = false, i = 0
|
|
while (i < text.length) {
|
|
const c = text[i]
|
|
if (inQ) {
|
|
if (c === '"') {
|
|
if (text[i + 1] === '"') { field += '"'; i += 2; continue }
|
|
inQ = false; i++; continue
|
|
}
|
|
field += c; i++; continue
|
|
}
|
|
if (c === '"') { inQ = true; i++; continue }
|
|
if (c === ',') { row.push(field); field = ''; i++; continue }
|
|
if (c === '\r') { i++; continue }
|
|
if (c === '\n') { row.push(field); rows.push(row); row = []; field = ''; i++; continue }
|
|
field += c; i++
|
|
}
|
|
if (field !== '' || row.length) { row.push(field); rows.push(row) }
|
|
return rows
|
|
}
|
|
function emitCSV(rows) {
|
|
return rows.map(r => r.map(v => {
|
|
const s = v == null ? '' : String(v)
|
|
return /[",\n\r]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s
|
|
}).join(',')).join('\n') + '\n'
|
|
}
|
|
|
|
function scrubCell(value, headerName) {
|
|
if (value == null || value === '') return value
|
|
let v = String(value)
|
|
|
|
// Header-driven replacement
|
|
const h = headerName.toLowerCase().trim()
|
|
if (PII_HEADER.test(h)) {
|
|
if (/email|mail/.test(h)) return fakeEmail(v)
|
|
if (/phone|telefon|tel|mobile|handy|fax/.test(h)) return fakePhone(v)
|
|
if (/iban|bic|account|kontoinhaber/.test(h)) return fakeIban(v)
|
|
if (/name|vorname|nachname/.test(h)) return fakeName(v)
|
|
if (/address|adresse|street|strasse|straße/.test(h)) return fakeGeneric('addr', v)
|
|
if (/zip|plz/.test(h)) return '00000'
|
|
if (/city|ort|stadt/.test(h)) return fakeGeneric('city', v)
|
|
if (/country|land/.test(h)) return v // country is fine
|
|
return fakeGeneric('val', v)
|
|
}
|
|
|
|
if (NOTES_HEADER.test(h) && !KEEP_NOTES) return '[notes-redacted]'
|
|
|
|
// Pattern-driven replacement on free-form columns
|
|
v = v.replace(EMAIL_RE, m => fakeEmail(m))
|
|
v = v.replace(IBAN_RE, m => fakeIban(m))
|
|
// phone last — broad regex, may eat dates; only apply if cell is mostly digits/separators
|
|
if (/^[\d\s\-+()/]{8,}$/.test(v)) v = v.replace(PHONE_RE, m => fakePhone(m))
|
|
|
|
return v
|
|
}
|
|
|
|
const files = readdirSync(SRC).filter(f => f.toLowerCase().endsWith('.csv'))
|
|
if (files.length === 0) {
|
|
console.log(`No .csv files found in ${SRC}/. Drop CSVs there and re-run.`)
|
|
process.exit(0)
|
|
}
|
|
|
|
console.log(`Found ${files.length} file(s). KEEP_NOTES=${KEEP_NOTES} DRY=${DRY}\n`)
|
|
|
|
for (const f of files) {
|
|
const raw = readFileSync(join(SRC, f), 'utf8')
|
|
const rows = parseCSV(raw)
|
|
if (rows.length === 0) { console.log(` ${f}: empty`); continue }
|
|
const headers = rows[0]
|
|
const scrubbed = [headers.slice()]
|
|
for (let r = 1; r < rows.length; r++) {
|
|
const row = rows[r]
|
|
if (row.length === 1 && row[0] === '') continue
|
|
const out = []
|
|
for (let c = 0; c < headers.length; c++) {
|
|
out.push(scrubCell(row[c] ?? '', headers[c] ?? `col${c}`))
|
|
}
|
|
scrubbed.push(out)
|
|
}
|
|
const outPath = join(DST, basename(f))
|
|
if (!DRY) writeFileSync(outPath, emitCSV(scrubbed))
|
|
console.log(` ${f}: ${rows.length - 1} rows, ${headers.length} cols → ${outPath}`)
|
|
}
|
|
|
|
console.log('\nDone. Review data/seed-scrubbed/ before sharing.')
|