Editing Openai/69111a25-dab4-8000-8929-8d5e16a9da88 (section)

=== ## ===
* When new/updated card master record arrives, automatically run: - Schema validation (required fields, types) — reject if missing required fields. - Syntax checks: expiry format MM/YY or MM/YYYY, BIN 6-digit numeric, lastFour 4-digit. - Luhn check of full PAN (only in purpose-built secure environment or using tokenized PAN from HSM). - Mask check: cardNumberMasked must contain lastFourDigits. - BIN vs PAN: PAN prefix must equal cardBIN. - PCI flag and encryption present — if pciDssCompliant is false, route to security queue. - Tokenization provider valid (allowlist: e.g., stripe, vault, adyen, braintree). - Check encryptionKeyId is in allowed KMS inventory and not expired/rotated beyond policy.
* On failure of any hard rule, automatically set status = REJECTED and record validation_errors with code(s).

==== - Soft rules (warnings) include: unusual cardTier, suspicious creditLimit outliers, test card BIN ranges. ====
* Log soft-fail warnings but allow status = PENDING for manual review.

==== - Hard-fail actions (immediate): - REJECTED: do not issue or activate; send event to data steward and security. - ISOLATE: if PAN fails encryption or PCI flag false — mark unusable and trigger re-tokenization request. ====
* Soft-fail actions: - PENDING_REVIEW: send to Data Quality team queue with automatic enrichment attempt (e.g., call issuer BIN lookup). - MARK_TEST: if BIN or card number matches known test card patterns (common processor test BINs), set environment = TEST and exclude from production runs.
* Duplicate handling: - If duplicate cardToken or same PAN + issuingBank but different token, reconcile: prefer most recent token with valid token provider; flag for manual reconciliation if both active.

==== - Emit structured events to downstream systems via a message bus (Kafka/RabbitMQ): - card.master.created, card.master.updated, card.master.validation_failed ====
* Webhook for third-party tokenization providers to request re-tokenization.
* Create tickets (JIRA/ServiceNow) automatically for tickets requiring human action.

==== - Every validation attempt writes an immutable audit record: - record_id, validator_version, timestamp, outcome, errors, actor ====
* Retain audit logs per PCI-DSS requirements and local retention policy (e.g., 7 years for financial audits, but ensure PANs masked & stored per PCI).

==== - For expired cards: set status=expired, notify cardholder (or issuing bank integration) for re-issue flows. ====
* For PCI or encryption failures: rotate keys, re-encrypt token data, verify in sandbox, then re-ingest.
* For Luhn failures (production environment): require cardholder re-entry with SCA (3DS) + tokenization.

==== - Data acceptance rate (% passing all hard validations) — target 99.5% for production ingest. ====
* Mean time to remediate validation error — target < 24 hours for high-severity.
* Percent of master records with valid tokenization & encryption — target 100%.

==== - Validation service: lightweight microservice (Python/Go) using schema validation (JSON Schema or Pydantic), run as part of ingestion pipeline. ====
* Use an HSM/KMS-backed service to perform Luhn or PAN checks without exposing PAN in logs.
* Use background job queue for enrichment (BIN lookup) and synchronous hard-fail on critical faults.
* Store master data in secure DB with column-level encryption for PAN fields, and token-only values in application DB (no PAN storage unless strictly required and in vault).
* Expose a small administration UI for data stewards to view PENDING_REVIEW items and perform actions (re-issue, block, mark test).

==== - R1: cardToken must be present and unique. ====
* R2: cardNumber must pass Luhn OR cardToken must exist and be linked to token provider.
* R3: cardNumberMasked must include lastFourDigits.
* R4: expiryDate must parse and not be in the past.
* R5: pciDssCompliant must be true for production records.
* R6: encryptionStatus must be AES-/RSA and encryptionKeyId must exist in KMS inventory.
* R7: tokenizationProvider must be allowlisted.
* R8: creditLimit must be >=0 and within configured business limits.

==== Schema snippet: ====

<syntaxhighlight lang="sql">CREATE TABLE card_master (
  id UUID PRIMARY KEY,
  card_token TEXT UNIQUE,
  card_bin CHAR(6),
  last_four CHAR(4),
  card_number_masked TEXT,
  card_network TEXT,
  expiry_month SMALLINT,
  expiry_year SMALLINT,
  pci_dss_compliant BOOLEAN,
  encryption_key_id TEXT,
  tokenization_provider TEXT,
  credit_limit BIGINT,
  status TEXT,
  validation_errors JSONB,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

</syntaxhighlight>

Quick check query (expired as of specific date):

<syntaxhighlight lang="sql">SELECT card_token FROM card_master
WHERE (expiry_year < 2025) OR (expiry_year = 2025 AND expiry_month < 10);

</syntaxhighlight>