Description:
The importers.ein column stores a plaintext Employer Identification Number, which is PII subject to data minimization requirements. Many queries only need to verify that a given EIN matches an importer (e.g., deduplication checks, lookup by EIN) — they don't need the raw value. Adding an ein_hash column that stores the SHA-256 hex digest of the EIN allows equality lookups without exposing the plaintext, enabling the raw ein column to be encrypted or removed in a future PII hardening pass.
Acceptance Criteria:
Relevant Files:
apps/api/src/db.ts — add ein_hash column to importers DDL and backfill migrationapps/api/src/routes/importers.ts — update registration and lookup queries
Description:
The
importers.eincolumn stores a plaintext Employer Identification Number, which is PII subject to data minimization requirements. Many queries only need to verify that a given EIN matches an importer (e.g., deduplication checks, lookup by EIN) — they don't need the raw value. Adding anein_hashcolumn that stores the SHA-256 hex digest of the EIN allows equality lookups without exposing the plaintext, enabling the raweincolumn to be encrypted or removed in a future PII hardening pass.Acceptance Criteria:
ALTER TABLE importers ADD COLUMN IF NOT EXISTS ein_hash TEXT;CREATE TABLE IF NOT EXISTS importersDDL indb.tsto includeein_hash TEXTfor fresh deploymentsCREATE UNIQUE INDEX IF NOT EXISTS idx_importers_ein_hash ON importers(ein_hash) WHERE ein_hash IS NOT NULL;to enforce uniqueness and enable O(1) lookupencode(sha256(ein::bytea), 'hex')for all existing rows whereein IS NOT NULLand setsein_hashimporters.tsto compute and storeein_hashusingcrypto.createHash('sha256').update(ein).digest('hex')before insertingWHERE ein = $1queries inimporters.tswithWHERE ein_hash = $1(where the caller passes a pre-hashed value) to avoid plaintext EIN in query paramsdb.tsthateinis scheduled for encryption (referencing the AES-GCM security issue) whileein_hashhandles lookupRelevant Files:
apps/api/src/db.ts— addein_hashcolumn toimportersDDL and backfill migrationapps/api/src/routes/importers.ts— update registration and lookup queries