# Every contact has source_type, source_id, and acquired_at

- **Pattern:** `ab-000886` (`data-sourcing-provenance.provenance-tracking.required-provenance-fields`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000886
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000886)

## Why it matters

GDPR Art. 13 requires that data subjects be informed of the source from which their personal data was obtained. Art. 30 requires a Records of Processing Activities (RoPA) that includes data sources. Without `source_type`, `source_id`, and `acquired_at` as NOT NULL fields on every contact record, you cannot respond to a regulatory audit, a data subject access request, or a right-to-erasure request that asks you to delete all contacts from a specific list. SLSA Provenance L1 requires a minimum verifiable provenance record for supply-chain integrity. CWE-345 (Insufficient Verification of Data Authenticity) applies when records lack traceable origin.

## Severity rationale

Critical because the absence of any provenance field means the system cannot fulfill GDPR Art. 13 disclosure obligations or Art. 30 RoPA requirements, making every contact record a potential regulatory liability.

## Remediation

Add all three provenance fields as NOT NULL columns to the contacts table. Existing rows should be backfilled with a tombstone source identifier if the original source cannot be determined.

```sql
ALTER TABLE contacts
  ADD COLUMN source_type  TEXT        NOT NULL
    CHECK (source_type IN ('scraper', 'api', 'purchased', 'form', 'referral')),
  ADD COLUMN source_id    TEXT        NOT NULL,
  ADD COLUMN acquired_at  TIMESTAMPTZ NOT NULL DEFAULT NOW();
```

```prisma
// schema.prisma
model Contact {
  source_type  SourceType @map("source_type")
  source_id    String     @map("source_id")
  acquired_at  DateTime   @default(now()) @map("acquired_at")
}
```

Every ingestion path must supply all three values explicitly — the `DEFAULT NOW()` on `acquired_at` is a safety net, not a substitute for explicit population.

## Detection

- **ID:** `required-provenance-fields`
- **Severity:** `critical`
- **What to look for:** Count all provenance-related fields on the contact/lead table. Examine the database schema (Prisma schema, Drizzle schema, SQL migration files). Verify that every contact record has at least 3 non-nullable provenance fields: (1) a source type indicator (`source_type`, `source`, or equivalent), (2) a source identifier that points to the specific source instance (`source_id`, `list_id`, `campaign_id`, or equivalent), and (3) a timestamp recording when the contact was acquired (`acquired_at`, `ingested_at`, `created_at` if used exclusively for ingestion timing). All 3 must be NOT NULL — nullable provenance fields are treated as absent. Quote the actual column definitions found.
- **Pass criteria:** Contact records have at least 3 provenance fields, all defined as NOT NULL. Every insert into the contacts table must supply these values. Report the count of provenance fields even on pass.
- **Fail criteria:** Fewer than 3 provenance fields are present, or any provenance field is nullable or not present in the schema at all. `created_at` being the only timestamp does not count as pass if it can be modified.
- **Cross-reference:** Check `data-sourcing-provenance.provenance-tracking.provenance-immutable` — these fields must also be immutable after creation.
- **Skip (N/A) when:** Not applicable — all contact data systems should have provenance fields.
- **Detail on fail:** Example: `"contacts table has source_type but no source_id — cannot trace which specific source produced a contact"` or `"acquired_at field is nullable — provenance is optional and incomplete for some records"`.
- **Remediation:** Add all three fields as NOT NULL:

  ```sql
  ALTER TABLE contacts
    ADD COLUMN source_type  TEXT        NOT NULL
      CHECK (source_type IN ('scraper', 'api', 'purchased', 'form', 'referral')),
    ADD COLUMN source_id    TEXT        NOT NULL,
    ADD COLUMN acquired_at  TIMESTAMPTZ NOT NULL DEFAULT NOW();
  ```

  ```prisma
  model Contact {
    source_type  SourceType @map("source_type")
    source_id    String     @map("source_id")
    acquired_at  DateTime   @map("acquired_at")
  }
  ```

## External references

- gdpr Art. 13
- gdpr Art. 30
- ccpa §1798.100
- slsa Provenance L1
- cwe CWE-345

Taxons: data-integrity, supply-chain

HTML version: https://auditbuffet.com/patterns/ab-000886