Provenance queryable for compliance lookups

GDPR Art. 30 requires a Records of Processing Activities identifying each category of data subjects and the source of their data. Art. 17 (right to erasure) requires that you can efficiently locate and delete all records from a specific source when that source becomes non-compliant or a data subject invokes their rights. Without an index on source_id, bulk lookups require full table scans that become prohibitively slow at scale. CCPA §1798.100 adds a parallel right to know which business collected the personal information and from what source.

Add indexes on both source_id and source_type, then expose a compliance query function or admin endpoint that retrieves all contacts for a given source.

CREATE INDEX idx_contacts_source_id   ON contacts (source_id);
CREATE INDEX idx_contacts_source_type ON contacts (source_type);

// src/lib/compliance/lookup.ts
export async function getContactsBySource(sourceId: string) {
  return db.contacts.findMany({
    where:  { source_id: sourceId },
    select: { id: true, email: true, acquired_at: true, source_type: true },
    orderBy: { acquired_at: 'desc' },
  })
}

Surface this function in an admin panel so operations teams can run source-based erasure requests without direct database access.

• ID: data-sourcing-provenance.provenance-tracking.provenance-queryable

• Severity: high

• What to look for: Count all indexes on provenance-related fields (source_id, source_type) in the database schema. Also check whether the admin interface, API, or internal tooling surfaces a query like "show me all contacts from source X." This is critical for responding to data subject requests (GDPR Article 30) or removing all contacts from a specific source when a source becomes non-compliant.

• Pass criteria: At least 1 index exists on source_id (or equivalent) in the database, making bulk lookups by source efficient. An internal query, API endpoint, or admin tool exists that can retrieve all contacts associated with a given source.

• Fail criteria: No index exists on provenance fields. Retrieving all contacts from a specific source requires a full table scan. No tooling or endpoint exists to support compliance lookups by source.

• Skip (N/A) when: Not applicable — all systems with provenance fields should support provenance queries.

• Detail on fail: "No index on source_id — compliance lookups require full table scan" or "source_id indexed but no API or admin tool exposes source-filtered queries".

• Remediation: Add an index and a compliance query:

  CREATE INDEX idx_contacts_source_id ON contacts (source_id);
  CREATE INDEX idx_contacts_source_type ON contacts (source_type);
  

  // Compliance endpoint or admin utility
  async function getContactsBySource(sourceId: string) {
    return db.contacts.findMany({
      where: { source_id: sourceId },
      select: { id: true, email: true, acquired_at: true },
    })
  }