Add audit logging for enterprise compliance (SOC2, ISO 27001)

[mjunaidca]

Overview

Implement database-based audit logging to track security-critical events for enterprise B2B compliance requirements (SOC2, ISO 27001, GDPR, HIPAA).

Status: Deferred until enterprise customers request it or compliance certification begins.

Why It's Needed

Different from Sentry:

• Sentry = Error monitoring (exceptions, crashes, performance)
• Audit Logs = Compliance (who did what when, investigations, forensics)

Use cases:

• Security investigations ("Who accessed this user's data?")
• Compliance audits (SOC2, ISO 27001 requirements)
• Forensics after security incidents
• Customer requests during enterprise procurement

Implementation Plan

1. Database Schema (5 minutes)

// auth-schema.ts - Add audit log table
export const auditLog = pgTable("audit_log", {
  id: text("id").primaryKey(),
  event: text("event").notNull(),      // "user.created", "session.created", etc.
  userId: text("user_id"),
  actorId: text("actor_id"),           // Who performed the action
  metadata: jsonb("metadata"),         // Additional context
  ipAddress: text("ip_address"),
  userAgent: text("user_agent"),
  timestamp: timestamp("timestamp").notNull().defaultNow(),
});

// Indexes for efficient queries
.addIndex("audit_log_user_id_idx", ["user_id"])
.addIndex("audit_log_event_idx", ["event"])
.addIndex("audit_log_timestamp_idx", ["timestamp"]);

2. Database Hooks (1 hour)

Update src/lib/auth.ts to add audit logging in databaseHooks:

databaseHooks: {
  user: {
    create: {
      after: async (user) => {
        if (process.env.ENABLE_AUDIT_LOGGING === "true") {
          await db.insert(auditLog).values({
            id: crypto.randomUUID(),
            event: "user.created",
            userId: user.id,
            metadata: { email: user.email },
            timestamp: new Date(),
          });
        }
        // ... existing auto-join logic
      }
    }
  },
  session: {
    create: {
      after: async (session) => {
        if (process.env.ENABLE_AUDIT_LOGGING === "true") {
          await db.insert(auditLog).values({
            id: crypto.randomUUID(),
            event: "session.created",
            userId: session.userId,
            actorId: session.userId,
            metadata: { ip: session.ipAddress },
            timestamp: new Date(),
          });
        }
      }
    }
  },
  // Add hooks for: role changes, org membership, etc.
}

3. Query API (30 minutes)

Create admin endpoint to query audit logs:

// src/app/api/admin/audit-logs/route.ts
export async function GET(request: NextRequest) {
  // Check admin auth
  const session = await auth.api.getSession({ headers: await headers() });
  if (session?.user?.role !== "admin") {
    return NextResponse.json({ error: "Forbidden" }, { status: 403 });
  }

  const { searchParams } = new URL(request.url);
  const userId = searchParams.get("userId");
  const event = searchParams.get("event");
  const startDate = searchParams.get("startDate");

  const logs = await db
    .select()
    .from(auditLog)
    .where(
      and(
        userId ? eq(auditLog.userId, userId) : undefined,
        event ? eq(auditLog.event, event) : undefined,
        startDate ? gte(auditLog.timestamp, new Date(startDate)) : undefined
      )
    )
    .orderBy(desc(auditLog.timestamp))
    .limit(100);

  return NextResponse.json({ logs });
}

4. Retention Policy (30 minutes)

Create cleanup script to delete old logs:

// scripts/cleanup-audit-logs.ts
const retentionDays = parseInt(process.env.AUDIT_LOG_RETENTION_DAYS || "730"); // 2 years default

await db
  .delete(auditLog)
  .where(lt(auditLog.timestamp, new Date(Date.now() - retentionDays * 24 * 60 * 60 * 1000)));

console.log(`Deleted audit logs older than ${retentionDays} days`);

Schedule as monthly cron job.

Database Load Estimates

User Scale	Daily Inserts	Storage/Month
100 users	~50-100/day	~1.5MB
1,000 users	~500-700/day	~15MB
10,000 users	~5,000-7,000/day	~150MB
20,000 users	~10,000-15,000/day	~450MB

Events logged (5 critical actions only):

• User registration (user.created)
• Login events (session.created)
• Role changes (role.updated)
• Organization membership (member.added, member.removed)
• Failed login attempts (via rate limiting hooks)

Performance impact: ~0.17 inserts/second for 20k users (negligible)

Environment Configuration

Already documented in .env.example:

# Enable audit logging (set to "true" to enable)
ENABLE_AUDIT_LOGGING=false

# Retention period in days (default: 730 = 2 years)
AUDIT_LOG_RETENTION_DAYS=730

When To Implement

Implement when:

• ✅ Enterprise customers request audit logs during procurement
• ✅ Starting SOC2 or ISO 27001 certification
• ✅ Security incident requires forensics capability
• ✅ Compliance requirements mandate audit trails

Defer if:

• ❌ Early-stage consumer product
• ❌ No enterprise B2B customers yet
• ❌ No compliance certification planned in next 6 months

Testing Checklist

After implementation:

• Verify user registration creates audit log entry
• Verify login creates audit log entry
• Verify role changes create audit log entry
• Verify audit logs queryable via admin API
• Verify retention policy deletes old logs
• Test with ENABLE_AUDIT_LOGGING=false (should be no-op)
• Verify performance impact is negligible

Estimated Effort

Total: 2-3 hours

• Schema + migration: 15 minutes
• Database hooks: 1 hour
• Query API: 30 minutes
• Retention script: 30 minutes
• Testing: 30-45 minutes

References

• Discussion: Session on 2025-12-02
• Commit with env config: 58b2734
• Better Auth databaseHooks: https://www.better-auth.com/docs/concepts/database-hooks