Activity Logging

Activity Logging

Track user actions and system events for analytics and compliance

---

Overview

Comprehensive activity tracking:

• User actions - Login, API calls, purchases
• API usage - Requests, models used, tokens consumed
• System events - Errors, warnings, config changes
• Security events - Failed auth, permission changes
• Compliance - Audit trail for regulatory requirements

---

What Gets Logged

User Actions

• Account creation
• Login/logout
• API key creation/deletion
• Payment transactions
• Referral usage
• Coupon redemption

API Activity

• Chat completions
• Image generation
• Model queries
• Balance checks
• Each request with metadata

System Events

• Configuration changes
• Database migrations
• Deployment events
• Service restarts

Security Events

• Failed authentication
• Permission denied
• Rate limit exceeded
• Suspicious activity

---

Database Schema

activity_logs Table

CREATE TABLE activity_logs (
  id BIGINT PRIMARY KEY,
  user_id INTEGER,
  action_type TEXT NOT NULL,
  resource_type TEXT,
  resource_id TEXT,
  metadata JSONB,
  ip_address TEXT,
  user_agent TEXT,
  status TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_activity_user ON activity_logs(user_id, created_at);
CREATE INDEX idx_activity_type ON activity_logs(action_type, created_at);
CREATE INDEX idx_activity_created ON activity_logs(created_at);

---

Logging Actions

Manual Logging

from src.db.activity import log_activity

await log_activity(
    user_id=user.id,
    action_type="api_key_created",
    resource_type="api_key",
    resource_id=key_id,
    metadata={
        "key_name": "Production API",
        "environment": "live"
    },
    status="success"
)

Automatic Logging

Middleware logs all API requests:

@app.middleware("http")
async def activity_logging_middleware(request: Request, call_next):
    user_id = get_user_from_request(request)

    response = await call_next(request)

    await log_activity(
        user_id=user_id,
        action_type="api_request",
        resource_type="endpoint",
        resource_id=request.url.path,
        metadata={
            "method": request.method,
            "status_code": response.status_code
        }
    )

    return response

---

Action Types

User Management

• user_created
• user_updated
• user_deleted
• login_success
• login_failed

API Keys

• api_key_created
• api_key_updated
• api_key_deleted
• api_key_rotated

Payments

• payment_initiated
• payment_completed
• payment_failed
• refund_processed

Usage

• chat_completion
• image_generation
• model_query
• balance_check

Security

• auth_failed
• permission_denied
• rate_limit_exceeded
• suspicious_activity

---

Querying Logs

Recent Activity

SELECT * FROM activity_logs
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 100;

By Action Type

SELECT * FROM activity_logs
WHERE action_type = 'payment_completed'
  AND created_at > NOW() - INTERVAL '7 days';

Failed Actions

SELECT * FROM activity_logs
WHERE status = 'failed'
  AND created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC;

User Timeline

SELECT action_type, resource_type, status, created_at
FROM activity_logs
WHERE user_id = 123
ORDER BY created_at DESC;

---

API Endpoints

Get User Activity

GET /user/activity

Query params:

• limit - Max records (default: 50)
• offset - Pagination
• action_type - Filter by type
• from_date, to_date - Date range

Response:

{
  "total": 150,
  "data": [
    {
      "id": 1001,
      "action_type": "chat_completion",
      "resource_type": "model",
      "resource_id": "gpt-4",
      "status": "success",
      "created_at": "2024-12-15T10:30:00Z",
      "metadata": {
        "tokens": 1500,
        "cost": 0.015
      }
    }
  ]
}

Admin Activity Logs

GET /admin/activity

Access all user activity (admin only).

---

Analytics

Usage Patterns

-- Most used models
SELECT
  metadata->>'model' as model,
  COUNT(*) as requests
FROM activity_logs
WHERE action_type = 'chat_completion'
GROUP BY metadata->>'model'
ORDER BY requests DESC;

-- Peak hours
SELECT
  EXTRACT(HOUR FROM created_at) as hour,
  COUNT(*) as requests
FROM activity_logs
WHERE action_type IN ('chat_completion', 'image_generation')
GROUP BY hour
ORDER BY hour;

-- Active users
SELECT
  user_id,
  COUNT(DISTINCT DATE(created_at)) as active_days,
  COUNT(*) as total_actions
FROM activity_logs
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY user_id
ORDER BY total_actions DESC;

---

Retention Policy

Automatic Cleanup

-- Delete logs older than 90 days
DELETE FROM activity_logs
WHERE created_at < NOW() - INTERVAL '90 days';

Archive Old Logs

-- Move to archive table
INSERT INTO activity_logs_archive
SELECT * FROM activity_logs
WHERE created_at < NOW() - INTERVAL '90 days';

DELETE FROM activity_logs
WHERE created_at < NOW() - INTERVAL '90 days';

---

Privacy Compliance

GDPR Compliance

-- Export user data
SELECT * FROM activity_logs
WHERE user_id = 123
ORDER BY created_at;

-- Delete user data
DELETE FROM activity_logs
WHERE user_id = 123;

Anonymization

-- Anonymize old data
UPDATE activity_logs
SET user_id = NULL,
    ip_address = NULL,
    user_agent = NULL
WHERE created_at < NOW() - INTERVAL '1 year';

---

Monitoring

Alert on Suspicious Activity

-- Multiple failed logins
SELECT user_id, COUNT(*) as failed_attempts
FROM activity_logs
WHERE action_type = 'login_failed'
  AND created_at > NOW() - INTERVAL '1 hour'
GROUP BY user_id
HAVING COUNT(*) > 5;

-- Unusual API usage
SELECT user_id, COUNT(*) as requests
FROM activity_logs
WHERE action_type = 'chat_completion'
  AND created_at > NOW() - INTERVAL '1 hour'
GROUP BY user_id
HAVING COUNT(*) > 1000;

---

Best Practices

1. Log meaningful events: Not everything needs logging
2. Include context: Add relevant metadata
3. Protect PII: Don't log passwords, API keys
4. Set retention: Delete old logs regularly
5. Index wisely: user_id, created_at, action_type
6. Monitor storage: Logs can grow large
7. Async logging: Don't block requests

---

Related Documentation

• Audit System - Security auditing
• Analytics System - Usage analytics

---

Last Updated: December 2024 Status: Production Ready

---

Related

• Audit-System — Security-grade audit trail (tamper-proof, compliance)
• API-Key-Management — Key operations are logged
• Role-Based-Access-Control — Role-based access logged
• Delta Report — P2-1: Per-API-key usage breakdown missing