Audit Logging Implementation

# Audit Logging for Google Antigravity

Implement comprehensive audit logging for compliance, security, and debugging.

## Database Schema

```sql
CREATE TABLE public.audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    action TEXT NOT NULL,
    entity_type TEXT NOT NULL,
    entity_id TEXT,
    old_values JSONB,
    new_values JSONB,
    metadata JSONB DEFAULT '{}'::jsonb,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_audit_user ON public.audit_logs(user_id, created_at DESC);
CREATE INDEX idx_audit_entity ON public.audit_logs(entity_type, entity_id);
CREATE INDEX idx_audit_action ON public.audit_logs(action, created_at DESC);
CREATE INDEX idx_audit_time ON public.audit_logs(created_at DESC);

-- Partition by month for large-scale deployments
-- CREATE TABLE audit_logs_2024_01 PARTITION OF audit_logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
```

## Audit Logger Service

```typescript
// lib/audit.ts
import { createClient } from "@/lib/supabase/server";

export type AuditAction = "create" | "update" | "delete" | "view" | "login" | "logout" | "export" | "import" | "share" | "permission_change";

interface AuditLogParams {
    userId?: string;
    action: AuditAction;
    entityType: string;
    entityId?: string;
    oldValues?: Record<string, unknown>;
    newValues?: Record<string, unknown>;
    metadata?: Record<string, unknown>;
    ipAddress?: string;
    userAgent?: string;
}

export async function createAuditLog(params: AuditLogParams): Promise<void> {
    const supabase = createClient();
    await supabase.from("audit_logs").insert({
        user_id: params.userId,
        action: params.action,
        entity_type: params.entityType,
        entity_id: params.entityId,
        old_values: params.oldValues,
        new_values: params.newValues,
        metadata: params.metadata,
        ip_address: params.ipAddress,
        user_agent: params.userAgent,
    });
}

export function diffObjects(oldObj: Record<string, unknown>, newObj: Record<string, unknown>): { old: Record<string, unknown>; new: Record<string, unknown> } {
    const changes = { old: {} as Record<string, unknown>, new: {} as Record<string, unknown> };
    const allKeys = new Set([...Object.keys(oldObj), ...Object.keys(newObj)]);
    
    for (const key of allKeys) {
        if (JSON.stringify(oldObj[key]) !== JSON.stringify(newObj[key])) {
            changes.old[key] = oldObj[key];
            changes.new[key] = newObj[key];
        }
    }
    return changes;
}
```

## Audit Middleware

```typescript
// lib/audit-middleware.ts
import { NextRequest } from "next/server";
import { createAuditLog, AuditAction } from "./audit";

export function withAudit(action: AuditAction, entityType: string) {
    return async function auditMiddleware(request: NextRequest, handler: () => Promise<Response>, entityId?: string) {
        const userId = request.headers.get("x-user-id") || undefined;
        const ipAddress = request.ip;
        const userAgent = request.headers.get("user-agent") || undefined;

        const response = await handler();

        if (response.ok) {
            await createAuditLog({ userId, action, entityType, entityId, ipAddress, userAgent });
        }

        return response;
    };
}
```

## Database Trigger for Automatic Logging

```sql
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_logs (user_id, action, entity_type, entity_id, new_values)
        VALUES (auth.uid(), 'create', TG_TABLE_NAME, NEW.id::text, to_jsonb(NEW));
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_logs (user_id, action, entity_type, entity_id, old_values, new_values)
        VALUES (auth.uid(), 'update', TG_TABLE_NAME, NEW.id::text, to_jsonb(OLD), to_jsonb(NEW));
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_logs (user_id, action, entity_type, entity_id, old_values)
        VALUES (auth.uid(), 'delete', TG_TABLE_NAME, OLD.id::text, to_jsonb(OLD));
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to tables
CREATE TRIGGER audit_posts AFTER INSERT OR UPDATE OR DELETE ON posts FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
```

## Audit Log Viewer

```typescript
// app/admin/audit/page.tsx
import { createClient } from "@/lib/supabase/server";

export default async function AuditLogPage({ searchParams }: { searchParams: { user?: string; action?: string; entity?: string } }) {
    const supabase = createClient();
    let query = supabase.from("audit_logs").select("*, user:profiles(full_name, email)").order("created_at", { ascending: false }).limit(100);

    if (searchParams.user) query = query.eq("user_id", searchParams.user);
    if (searchParams.action) query = query.eq("action", searchParams.action);
    if (searchParams.entity) query = query.eq("entity_type", searchParams.entity);

    const { data: logs } = await query;

    return (
        <div className="audit-logs">
            <h1>Audit Logs</h1>
            <table>
                <thead>
                    <tr><th>Time</th><th>User</th><th>Action</th><th>Entity</th><th>Details</th></tr>
                </thead>
                <tbody>
                    {logs?.map((log) => (
                        <tr key={log.id}>
                            <td>{new Date(log.created_at).toLocaleString()}</td>
                            <td>{log.user?.full_name || "System"}</td>
                            <td><span className={`badge ${log.action}`}>{log.action}</span></td>
                            <td>{log.entity_type} {log.entity_id}</td>
                            <td><code>{JSON.stringify(log.new_values || log.old_values, null, 2)}</code></td>
                        </tr>
                    ))}
                </tbody>
            </table>
        </div>
    );
}
```

## Export Audit Logs

```typescript
// app/api/admin/audit/export/route.ts
import { NextRequest, NextResponse } from "next/server";
import { createClient } from "@/lib/supabase/server";
import { createAuditLog } from "@/lib/audit";

export async function GET(request: NextRequest) {
    const supabase = createClient();
    const { data: { user } } = await supabase.auth.getUser();
    
    const startDate = request.nextUrl.searchParams.get("start");
    const endDate = request.nextUrl.searchParams.get("end");

    let query = supabase.from("audit_logs").select("*");
    if (startDate) query = query.gte("created_at", startDate);
    if (endDate) query = query.lte("created_at", endDate);

    const { data: logs } = await query.order("created_at", { ascending: false });

    // Log the export action
    await createAuditLog({ userId: user?.id, action: "export", entityType: "audit_logs", metadata: { startDate, endDate, count: logs?.length } });

    const csv = convertToCSV(logs || []);
    return new NextResponse(csv, { headers: { "Content-Type": "text/csv", "Content-Disposition": "attachment; filename=audit-logs.csv" } });
}

function convertToCSV(data: any[]): string {
    if (!data.length) return "";
    const headers = Object.keys(data[0]).join(",");
    const rows = data.map((row) => Object.values(row).map((v) => JSON.stringify(v)).join(","));
    return [headers, ...rows].join("\n");
}
```

## Best Practices

1. **Immutability**: Never update or delete audit logs
2. **Retention**: Set appropriate retention policies
3. **Sensitive Data**: Redact sensitive information
4. **Indexing**: Index frequently queried columns
5. **Partitioning**: Partition large tables by date