gdpr-compliance

CREATE TABLE consents (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  purpose VARCHAR(100) NOT NULL,  -- 'marketing', 'analytics', 'personalization'
  granted BOOLEAN NOT NULL,
  granted_at TIMESTAMPTZ,
  withdrawn_at TIMESTAMPTZ,
  ip_address INET,
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for quick consent checks
CREATE INDEX idx_consents_user_purpose ON consents(user_id, purpose);

async function hasConsent(userId: string, purpose: string): Promise<boolean> {
  const consent = await db.consents.findFirst({
    where: { 
      user_id: userId, 
      purpose,
      granted: true,
      withdrawn_at: null,
    },
    orderBy: { created_at: 'desc' },
  });
  
  return !!consent;
}

// Usage
if (await hasConsent(userId, 'marketing')) {
  await sendMarketingEmail(userId);
}

// Required elements:
// 1. Clear explanation of each purpose
// 2. Granular opt-in (not bundled)
// 3. Easy withdraw mechanism
// 4. No pre-checked boxes

interface ConsentPreferences {
  necessary: true;  // Can't be disabled
  analytics: boolean;
  marketing: boolean;
  personalization: boolean;
}

async function exportUserData(userId: string): Promise<UserDataExport> {
  const [user, orders, consents, activityLog] = await Promise.all([
    db.users.findUnique({ where: { id: userId } }),
    db.orders.findMany({ where: { user_id: userId } }),
    db.consents.findMany({ where: { user_id: userId } }),
    db.activity_logs.findMany({ where: { user_id: userId } }),
  ]);
  
  return {
    personal_info: {
      email: user.email,
      name: user.name,
      created_at: user.created_at,
    },
    orders: orders.map(o => ({
      id: o.id,
      total: o.total,
      created_at: o.created_at,
    })),
    consent_history: consents,
    activity_log: activityLog,
    exported_at: new Date().toISOString(),
  };
}

// API endpoint
app.get('/api/me/data-export', async (req, res) => {
  const data = await exportUserData(req.user.id);
  res.json(data);
});

async function deleteUserData(userId: string): Promise<void> {
  // 1. Delete from all tables
  await db.$transaction([
    db.activity_logs.deleteMany({ where: { user_id: userId } }),
    db.consents.deleteMany({ where: { user_id: userId } }),
    db.sessions.deleteMany({ where: { user_id: userId } }),
    // Keep orders for legal reasons, but anonymize
    db.orders.updateMany({
      where: { user_id: userId },
      data: { 
        user_id: null,
        customer_email: 'deleted@anonymized.com',
        customer_name: 'Deleted User',
      },
    }),
    db.users.delete({ where: { id: userId } }),
  ]);
  
  // 2. Delete from external services
  await Promise.all([
    analytics.deleteUser(userId),
    emailService.deleteContact(userId),
    storage.deleteUserFiles(userId),
  ]);
  
  // 3. Log deletion for compliance
  await auditLog.create({
    action: 'USER_DATA_DELETED',
    subject_id: userId,
    timestamp: new Date(),
  });
}

app.patch('/api/me', async (req, res) => {
  const { name, email } = req.body;
  
  await db.users.update({
    where: { id: req.user.id },
    data: { name, email },
  });
  
  await auditLog.create({
    action: 'USER_DATA_UPDATED',
    user_id: req.user.id,
    changes: { name, email },
  });
  
  res.json({ success: true });
});

// BAD: Collecting unnecessary data
interface UserRegistration {
  email: string;
  password: string;
  phone: string;      // Not needed for email-based product
  birthdate: string;  // Not needed
  address: string;    // Not needed
}

// GOOD: Minimum viable data
interface UserRegistration {
  email: string;
  password: string;
}

-- Auto-delete old activity logs
DELETE FROM activity_logs WHERE created_at < NOW() - INTERVAL '2 years';

-- Anonymize old orders (keep for accounting, anonymize PII)
UPDATE orders 
SET customer_email = 'anonymized@deleted.com',
    customer_name = 'Anonymized'
WHERE created_at < NOW() - INTERVAL '7 years';

// Don't use email as ID across systems
// Use opaque user ID instead

interface AnalyticsEvent {
  user_id: string;  // UUID, not email
  event: string;
  properties: Record<string, unknown>;
}

// Analytics service never sees email
analytics.track({
  user_id: user.id,  // Not user.email
  event: 'purchase_completed',
  properties: { amount: 99 },
});

// Encrypt sensitive fields before storage
import { encrypt, decrypt } from './crypto';

await db.users.create({
  data: {
    email: user.email,  // Plain for login lookup
    phone_encrypted: encrypt(user.phone),  // Encrypted
    ssn_encrypted: encrypt(user.ssn),       // Encrypted
  },
});

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY,
  timestamp TIMESTAMPTZ DEFAULT NOW(),
  actor_id UUID,                    -- Who performed action
  action VARCHAR(100) NOT NULL,     -- 'USER_CREATED', 'DATA_EXPORTED', etc.
  resource_type VARCHAR(50),        -- 'user', 'order', etc.
  resource_id UUID,
  changes JSONB,                    -- Before/after values
  ip_address INET,
  user_agent TEXT
);

async function auditLog(params: {
  actor_id: string;
  action: string;
  resource_type?: string;
  resource_id?: string;
  changes?: Record<string, unknown>;
  req?: Request;
}) {
  await db.audit_logs.create({
    data: {
      ...params,
      ip_address: params.req?.ip,
      user_agent: params.req?.headers['user-agent'],
    },
  });
}

// Usage
await auditLog({
  actor_id: adminUser.id,
  action: 'USER_DATA_ACCESSED',
  resource_type: 'user',
  resource_id: targetUser.id,
  req,
});