forensic-data-engineer

What this skill does

# Forensic Data Engineer Skill

I help you investigate data anomalies, detect fraud, analyze audit trails, and ensure data integrity and compliance.

## What I Do

**Forensic Analysis:**

- Anomaly detection and pattern recognition
- Fraud detection and prevention
- Breach investigation and root cause analysis
- Data integrity verification

**Audit & Compliance:**

- Audit trail analysis and reconstruction
- Chain of custody maintenance
- Regulatory compliance (GDPR, SOC2, HIPAA)
- Access control auditing

**Data Recovery:**

- Forensic recovery of deleted data
- Historical data reconstruction
- Change detection and unauthorized modifications
- Data lineage and provenance tracking

## Forensic Patterns

### Pattern 1: Audit Trail Implementation

**Use case:** Track all data changes for compliance and investigation

```typescript
// lib/forensics/audit-trail.ts

interface AuditEntry {
  id: string
  timestamp: Date
  userId: string
  action: 'CREATE' | 'UPDATE' | 'DELETE' | 'READ'
  tableName: string
  recordId: string
  oldValue?: any
  newValue?: any
  ipAddress: string
  userAgent: string
  sessionId: string
}

export async function createAuditLog(entry: Omit<AuditEntry, 'id' | 'timestamp'>) {
  return await db.auditLog.create({
    data: {
      ...entry,
      timestamp: new Date()
    }
  })
}

// Middleware for automatic audit logging
export function withAudit<T extends (...args: any[]) => Promise<any>>(
  operation: T,
  metadata: { tableName: string; action: AuditEntry['action'] }
): T {
  return (async (...args: any[]) => {
    const startTime = Date.now()
    const { tableName, action } = metadata

    try {
      // Capture before state for UPDATE/DELETE
      let oldValue
      if (action === 'UPDATE' || action === 'DELETE') {
        oldValue = await captureCurrentState(tableName, args[0])
      }

      // Execute operation
      const result = await operation(...args)

      // Capture after state
      const newValue = action !== 'DELETE' ? result : null

      // Log audit entry
      await createAuditLog({
        userId: getCurrentUser().id,
        action,
        tableName,
        recordId: args[0],
        oldValue,
        newValue,
        ipAddress: getClientIp(),
        userAgent: getClientUserAgent(),
        sessionId: getSessionId()
      })

      return result
    } catch (error) {
      // Log failed attempt
      await createAuditLog({
        userId: getCurrentUser().id,
        action,
        tableName,
        recordId: args[0],
        ipAddress: getClientIp(),
        userAgent: getClientUserAgent(),
        sessionId: getSessionId()
      })
      throw error
    }
  }) as T
}

// Usage
const updateUser = withAudit(
  async (userId: string, data: any) => {
    return await db.user.update({
      where: { id: userId },
      data
    })
  },
  { tableName: 'users', action: 'UPDATE' }
)
```

---

### Pattern 2: Anomaly Detection

**Use case:** Identify suspicious patterns in transaction data

```typescript
// lib/forensics/anomaly-detection.ts

interface Transaction {
  id: string
  userId: string
  amount: number
  timestamp: Date
  location: string
  deviceId: string
}

export async function detectTransactionAnomalies(transaction: Transaction) {
  const anomalies: string[] = []

  // Check 1: Unusual amount (statistical outlier)
  const userStats = await getUserTransactionStats(transaction.userId)
  const zScore = (transaction.amount - userStats.mean) / userStats.stdDev

  if (Math.abs(zScore) > 3) {
    anomalies.push(`Unusual amount: ${transaction.amount} (z-score: ${zScore.toFixed(2)})`)
  }

  // Check 2: Rapid succession (velocity check)
  const recentTransactions = await db.transactions.findMany({
    where: {
      userId: transaction.userId,
      timestamp: {
        gte: new Date(Date.now() - 5 * 60 * 1000) // Last 5 minutes
      }
    }
  })

  if (recentTransactions.length > 5) {
    anomalies.push(`High velocity: ${recentTransactions.length} transactions in 5 minutes`)
  }

  // Check 3: Impossible travel (location mismatch)
  const lastTransaction = await db.transactions.findFirst({
    where: { userId: transaction.userId },
    orderBy: { timestamp: 'desc' }
  })

  if (lastTransaction) {
    const timeDiff = transaction.timestamp.getTime() - lastTransaction.timestamp.getTime()
    const distance = calculateDistance(lastTransaction.location, transaction.location)
    const maxPossibleSpeed = 900 // km/h (commercial flight)
    const requiredSpeed = distance / (timeDiff / 3600000) // km/h

    if (requiredSpeed > maxPossibleSpeed) {
      anomalies.push(
        `Impossible travel: ${distance}km in ${timeDiff / 60000} minutes (${requiredSpeed.toFixed(0)} km/h required)`
      )
    }
  }

  // Check 4: New device from new location
  const deviceHistory = await db.deviceHistory.findFirst({
    where: {
      userId: transaction.userId,
      deviceId: transaction.deviceId
    }
  })

  if (!deviceHistory) {
    anomalies.push(`New device: ${transaction.deviceId}`)
  }

  // Check 5: Time-of-day anomaly
  const hour = transaction.timestamp.getHours()
  const userActivity = await getUserActivityPattern(transaction.userId)

  if (userActivity.typicalHours.indexOf(hour) === -1) {
    anomalies.push(`Unusual time: ${hour}:00 (typical: ${userActivity.typicalHours.join(', ')})`)
  }

  return {
    isAnomalous: anomalies.length > 0,
    anomalies,
    riskScore: calculateRiskScore(anomalies)
  }
}

async function getUserTransactionStats(userId: string) {
  const result = await db.$queryRaw<[{ mean: number; stddev: number }]>`
    SELECT
      AVG(amount)::float as mean,
      STDDEV(amount)::float as stddev
    FROM transactions
    WHERE user_id = ${userId}
    AND timestamp > NOW() - INTERVAL '90 days'
  `

  return {
    mean: result[0]?.mean || 0,
    stdDev: result[0]?.stddev || 1
  }
}

function calculateRiskScore(anomalies: string[]): number {
  // Weight different anomaly types
  const weights = {
    'Unusual amount': 2,
    'High velocity': 3,
    'Impossible travel': 5,
    'New device': 2,
    'Unusual time': 1
  }

  return anomalies.reduce((score, anomaly) => {
    const type = anomaly.split(':')[0]
    return score + (weights[type] || 1)
  }, 0)
}
```

---

### Pattern 3: Data Lineage Tracking

**Use case:** Track data provenance and transformation history

```typescript
// lib/forensics/lineage.ts

interface LineageNode {
  id: string
  datasetName: string
  recordId: string
  operation: string
  timestamp: Date
  sourceNodes: string[]
  metadata: Record<string, any>
}

export class DataLineageTracker {
  async trackTransformation(config: {
    output: { dataset: string; recordId: string }
    inputs: Array<{ dataset: string; recordId: string }>
    operation: string
    metadata?: Record<string, any>
  }) {
    const node: LineageNode = {
      id: generateId(),
      datasetName: config.output.dataset,
      recordId: config.output.recordId,
      operation: config.operation,
      timestamp: new Date(),
      sourceNodes: config.inputs.map(i => `${i.dataset}:${i.recordId}`),
      metadata: config.metadata || {}
    }

    await db.dataLineage.create({ data: node })
    return node
  }

  async getLineage(dataset: string, recordId: string): Promise<LineageNode[]> {
    const visited = new Set<string>()
    const lineage: LineageNode[] = []

    async function traverse(ds: string, rid: string) {
      const key = `${ds}:${rid}`
      if (visited.has(key)) return

      visited.add(key)

      const node = await db.dataLineage.findFirst({
        where: { datasetName: ds, recordId: rid }
      })

      if (!node) return

      lineage.push(node)

      // Recursively traverse source nodes
      for (const sourceKey of node.sourceNodes) {
        const [sourceDs, sourceRid] = sourceKey.split(':')
        await traverse(sourceDs, sourceRid)
      }
    }

    await traverse(dataset, recordId)
    return lineage
  }

  async visualizeLineage(dataset: string, recordId: string): Promise