Skip to content

Feature Implementation Plan: API Request Logging

Overview

Implement an asynchronous API request logging system to capture every request to /api/* endpoints. This data will later be used for: 1. Admin UI to visualize traffic patterns (Cloudflare-style analytics) 2. Cache warming by identifying "hot" endpoints

Related Plans: - api-request-admin-ui.md (depends on this plan) - api-cache-warming.md (depends on this plan)

Todo Checklist

  • [ ] Create ApiRequestPattern entity with migration
  • [ ] Create ApiRequestOccurrence entity with migration
  • [ ] Create ApiRequestPatternRepository with native upsert method
  • [ ] Create ApiRequestOccurrenceRepository
  • [ ] Create LogApiRequestMessage message class
  • [ ] Create LogApiRequestHandler message handler
  • [ ] Create ApiRequestLogListener kernel event listener
  • [ ] Configure Messenger transport for logging
  • [ ] Add data retention scheduled task
  • [ ] Write tests for the logging pipeline

Analysis & Investigation

Existing Infrastructure

Messenger Setup: - Multiple transports configured in config/packages/messenger.php - Uses TransportNamesStamp to route messages to specific transports - Handlers use #[AsMessageHandler] attribute pattern - Failed messages stored in Doctrine-backed failed transport

Cache Infrastructure: - CacheKeyGenerator normalizes query params (sorts keys, handles arrays) - Redis tag-aware cache pools with multiple TTL strategies - CacheResponseService sets Cache-Control and custom headers

Event Listener Pattern: - Uses #[AsEventListener] attribute (see ErrorLoggingListener) - Priority -100 runs after main processing

Entity Conventions: - UUID primary keys with doctrine.uuid_generator - DateTimeImmutable for timestamps - JSON columns for flexible data storage

Architecture Design

┌─────────────────────────────────────────────────────────────────┐
│  Request Lifecycle                                              │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  HTTP Request                                                   │
│       │                                                         │
│       ▼                                                         │
│  ┌─────────────┐                                                │
│  │  Controller │ → Process request → Return response            │
│  └─────────────┘                                                │
│       │                                                         │
│       ▼                                                         │
│  ┌──────────────────────┐                                       │
│  │ ApiRequestLogListener│ (KernelEvents::TERMINATE)             │
│  │   - Capture metrics  │                                       │
│  │   - Dispatch message │ → Response sent to client (immediate) │
│  └──────────────────────┘                                       │
│       │                                                         │
│       ▼ (async)                                                 │
│  ┌──────────────────────┐                                       │
│  │ LogApiRequestHandler │ (Messenger worker)                    │
│  │   - Generate hash    │                                       │
│  │   - Upsert pattern   │                                       │
│  │   - Insert occurrence│                                       │
│  └──────────────────────┘                                       │
│       │                                                         │
│       ▼                                                         │
│  ┌──────────────────────┐                                       │
│  │     PostgreSQL       │                                       │
│  │  - api_request_pattern                                       │
│  │  - api_request_occurrence                                    │
│  └──────────────────────┘                                       │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Entity Design

ApiRequestPattern - Unique request signatures (deduplicated) 

┌────────────────────────────────────────┐
│ api_request_pattern                    │
├────────────────────────────────────────┤
│ id            UUID PK                  │
│ method        VARCHAR(10)              │
│ path          VARCHAR(255)             │
│ query_hash    VARCHAR(64) UNIQUE       │ ← SHA256 of method:path:normalized_params
│ query_params  JSON                     │ ← Original params for display
│ hit_count     BIGINT DEFAULT 0         │ ← Denormalized for fast sorting
│ avg_response_time_ms  FLOAT            │ ← Running average
│ last_seen_at  TIMESTAMP                │
│ created_at    TIMESTAMP                │
├────────────────────────────────────────┤
│ INDEX idx_hit_count (hit_count)        │
│ INDEX idx_last_seen (last_seen_at)     │
│ UNIQUE idx_query_hash (query_hash)     │
└────────────────────────────────────────┘

ApiRequestOccurrence - Individual request instances 

┌────────────────────────────────────────┐
│ api_request_occurrence                 │
├────────────────────────────────────────┤
│ id              UUID PK                │
│ pattern_id      UUID FK                │ → api_request_pattern.id
│ ip_address      VARCHAR(45) NULL       │ ← IPv6 max length
│ user_agent      TEXT NULL              │
│ response_time_ms FLOAT                 │
│ cache_status    VARCHAR(20) NULL       │ ← HIT/MISS/STALE
│ status_code     SMALLINT               │
│ created_at      TIMESTAMP              │
├────────────────────────────────────────┤
│ INDEX idx_pattern (pattern_id)         │
│ INDEX idx_created (created_at)         │
│ INDEX idx_pattern_created              │
│       (pattern_id, created_at)         │
└────────────────────────────────────────┘

Query Hash Generation

Include HTTP method in the hash to differentiate GET vs POST to same endpoint:

$signature = sprintf(
    '%s:%s:%s',
    $method,                              // GET, POST, etc.
    $path,                                // /api/coffee-beans
    $this->normalizeQueryParams($params)  // Sorted, serialized params
);
$queryHash = hash('sha256', $signature);

Upsert Strategy

Use native PostgreSQL ON CONFLICT for atomic, race-condition-free updates:

INSERT INTO api_request_pattern
    (id, method, path, query_hash, query_params, hit_count, avg_response_time_ms, last_seen_at, created_at)
VALUES
    (gen_random_uuid(), :method, :path, :hash, :params, 1, :responseTime, NOW(), NOW())
ON CONFLICT (query_hash) DO UPDATE SET
    hit_count = api_request_pattern.hit_count + 1,
    avg_response_time_ms = (
        (api_request_pattern.avg_response_time_ms * api_request_pattern.hit_count) + :responseTime
    ) / (api_request_pattern.hit_count + 1),
    last_seen_at = NOW()
RETURNING id;

Implementation Plan

Prerequisites

  • Symfony Messenger configured (already done)
  • Redis for message transport (already configured)
  • PostgreSQL database (already in use)

Step 1: Create ApiRequestPattern Entity

File: src/Entity/ApiRequestPattern.php

<?php

namespace App\Entity;

use App\Repository\ApiRequestPatternRepository;
use DateTimeImmutable;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ApiRequestPatternRepository::class)]
#[ORM\Table(name: 'api_request_pattern')]
#[ORM\UniqueConstraint(name: 'uniq_query_hash', columns: ['query_hash'])]
#[ORM\Index(columns: ['hit_count'], name: 'idx_pattern_hit_count')]
#[ORM\Index(columns: ['last_seen_at'], name: 'idx_pattern_last_seen')]
final class ApiRequestPattern
{
    #[ORM\Id]
    #[ORM\Column(type: 'uuid', unique: true)]
    #[ORM\GeneratedValue(strategy: 'CUSTOM')]
    #[ORM\CustomIdGenerator(class: 'doctrine.uuid_generator')]
    private ?string $id = null;

    #[ORM\Column(length: 10)]
    private string $method;

    #[ORM\Column(length: 255)]
    private string $path;

    #[ORM\Column(length: 64, unique: true)]
    private string $queryHash;

    #[ORM\Column(type: Types::JSON)]
    private array $queryParams = [];

    #[ORM\Column(type: Types::BIGINT, options: ['default' => 0])]
    private int $hitCount = 0;

    #[ORM\Column(type: Types::FLOAT, nullable: true)]
    private ?float $avgResponseTimeMs = null;

    #[ORM\Column]
    private DateTimeImmutable $lastSeenAt;

    #[ORM\Column]
    private DateTimeImmutable $createdAt;

    public function __construct(
        string $method,
        string $path,
        string $queryHash,
        array $queryParams,
    ) {
        $this->method = $method;
        $this->path = $path;
        $this->queryHash = $queryHash;
        $this->queryParams = $queryParams;
        $this->createdAt = new DateTimeImmutable();
        $this->lastSeenAt = new DateTimeImmutable();
    }

    // Getters...
}

Step 2: Create ApiRequestOccurrence Entity

File: src/Entity/ApiRequestOccurrence.php

<?php

namespace App\Entity;

use App\Repository\ApiRequestOccurrenceRepository;
use DateTimeImmutable;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ApiRequestOccurrenceRepository::class)]
#[ORM\Table(name: 'api_request_occurrence')]
#[ORM\Index(columns: ['pattern_id'], name: 'idx_occurrence_pattern')]
#[ORM\Index(columns: ['created_at'], name: 'idx_occurrence_created')]
#[ORM\Index(columns: ['pattern_id', 'created_at'], name: 'idx_occurrence_pattern_created')]
final class ApiRequestOccurrence
{
    #[ORM\Id]
    #[ORM\Column(type: 'uuid', unique: true)]
    #[ORM\GeneratedValue(strategy: 'CUSTOM')]
    #[ORM\CustomIdGenerator(class: 'doctrine.uuid_generator')]
    private ?string $id = null;

    #[ORM\ManyToOne(targetEntity: ApiRequestPattern::class)]
    #[ORM\JoinColumn(nullable: false, onDelete: 'CASCADE')]
    private ApiRequestPattern $pattern;

    #[ORM\Column(length: 45, nullable: true)]
    private ?string $ipAddress = null;

    #[ORM\Column(type: Types::TEXT, nullable: true)]
    private ?string $userAgent = null;

    #[ORM\Column(type: Types::FLOAT)]
    private float $responseTimeMs;

    #[ORM\Column(length: 20, nullable: true)]
    private ?string $cacheStatus = null;

    #[ORM\Column(type: Types::SMALLINT)]
    private int $statusCode;

    #[ORM\Column]
    private DateTimeImmutable $createdAt;

    public function __construct(
        ApiRequestPattern $pattern,
        float $responseTimeMs,
        int $statusCode,
        ?string $ipAddress = null,
        ?string $userAgent = null,
        ?string $cacheStatus = null,
    ) {
        $this->pattern = $pattern;
        $this->responseTimeMs = $responseTimeMs;
        $this->statusCode = $statusCode;
        $this->ipAddress = $ipAddress;
        $this->userAgent = $userAgent;
        $this->cacheStatus = $cacheStatus;
        $this->createdAt = new DateTimeImmutable();
    }

    // Getters...
}

Step 3: Create ApiRequestPatternRepository with Native Upsert

File: src/Repository/ApiRequestPatternRepository.php

<?php

namespace App\Repository;

use App\Entity\ApiRequestPattern;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @extends ServiceEntityRepository<ApiRequestPattern>
 */
final class ApiRequestPatternRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, ApiRequestPattern::class);
    }

    /**
     * Upserts a request pattern and returns the pattern ID.
     * Uses native PostgreSQL ON CONFLICT for atomic operation.
     */
    public function upsertPattern(
        string $method,
        string $path,
        string $queryHash,
        array $queryParams,
        float $responseTimeMs,
    ): string {
        $conn = $this->getEntityManager()->getConnection();

        $sql = <<<'SQL'
            INSERT INTO api_request_pattern
                (id, method, path, query_hash, query_params, hit_count, avg_response_time_ms, last_seen_at, created_at)
            VALUES
                (gen_random_uuid(), :method, :path, :hash, :params, 1, :responseTime, NOW(), NOW())
            ON CONFLICT (query_hash) DO UPDATE SET
                hit_count = api_request_pattern.hit_count + 1,
                avg_response_time_ms = (
                    (api_request_pattern.avg_response_time_ms * api_request_pattern.hit_count) + :responseTime
                ) / (api_request_pattern.hit_count + 1),
                last_seen_at = NOW()
            RETURNING id
            SQL;

        $result = $conn->executeQuery($sql, [
            'method' => $method,
            'path' => $path,
            'hash' => $queryHash,
            'params' => json_encode($queryParams),
            'responseTime' => $responseTimeMs,
        ]);

        return $result->fetchOne();
    }

    /**
     * Get top patterns by hit count for cache warming.
     *
     * @return ApiRequestPattern[]
     */
    public function findTopPatterns(int $limit = 20): array
    {
        return $this->createQueryBuilder('p')
            ->orderBy('p.hitCount', 'DESC')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }
}

Step 4: Create LogApiRequestMessage

File: src/Message/LogApiRequestMessage.php

<?php

namespace App\Message;

final readonly class LogApiRequestMessage
{
    public function __construct(
        public string $method,
        public string $path,
        public array $queryParams,
        public float $responseTimeMs,
        public int $statusCode,
        public ?string $ipAddress = null,
        public ?string $userAgent = null,
        public ?string $cacheStatus = null,
    ) {}
}

Step 5: Create LogApiRequestHandler

File: src/MessageHandler/LogApiRequestHandler.php

<?php

namespace App\MessageHandler;

use App\Entity\ApiRequestOccurrence;
use App\Message\LogApiRequestMessage;
use App\Repository\ApiRequestPatternRepository;
use Doctrine\ORM\EntityManagerInterface;
use Psr\Log\LoggerInterface;
use Symfony\Component\Messenger\Attribute\AsMessageHandler;

#[AsMessageHandler]
final readonly class LogApiRequestHandler
{
    public function __construct(
        private ApiRequestPatternRepository $patternRepository,
        private EntityManagerInterface $entityManager,
        private LoggerInterface $logger,
    ) {}

    public function __invoke(LogApiRequestMessage $message): void
    {
        try {
            $queryHash = $this->generateQueryHash(
                $message->method,
                $message->path,
                $message->queryParams,
            );

            // Atomic upsert - returns pattern ID
            $patternId = $this->patternRepository->upsertPattern(
                $message->method,
                $message->path,
                $queryHash,
                $message->queryParams,
                $message->responseTimeMs,
            );

            // Fetch the pattern entity for the relationship
            $pattern = $this->patternRepository->find($patternId);

            if ($pattern === null) {
                $this->logger->error('Pattern not found after upsert', [
                    'patternId' => $patternId,
                ]);
                return;
            }

            // Create occurrence record
            $occurrence = new ApiRequestOccurrence(
                pattern: $pattern,
                responseTimeMs: $message->responseTimeMs,
                statusCode: $message->statusCode,
                ipAddress: $message->ipAddress,
                userAgent: $message->userAgent,
                cacheStatus: $message->cacheStatus,
            );

            $this->entityManager->persist($occurrence);
            $this->entityManager->flush();
        } catch (\Throwable $e) {
            $this->logger->error('Failed to log API request', [
                'path' => $message->path,
                'error' => $e->getMessage(),
            ]);
            throw $e;
        }
    }

    private function generateQueryHash(string $method, string $path, array $params): string
    {
        // Sort params for consistent hashing
        ksort($params);

        // Normalize array values
        array_walk($params, function (&$value) {
            if (is_array($value)) {
                sort($value);
                $value = implode(',', $value);
            }
        });

        $signature = sprintf(
            '%s:%s:%s',
            $method,
            $path,
            http_build_query($params),
        );

        return hash('sha256', $signature);
    }
}

Step 6: Create ApiRequestLogListener

File: src/EventListener/ApiRequestLogListener.php

<?php

namespace App\EventListener;

use App\Message\LogApiRequestMessage;
use Symfony\Component\EventDispatcher\Attribute\AsEventListener;
use Symfony\Component\HttpKernel\Event\TerminateEvent;
use Symfony\Component\HttpKernel\KernelEvents;
use Symfony\Component\Messenger\MessageBusInterface;
use Symfony\Component\Stopwatch\Stopwatch;

#[AsEventListener(event: KernelEvents::TERMINATE)]
final readonly class ApiRequestLogListener
{
    public function __construct(
        private MessageBusInterface $messageBus,
        private Stopwatch $stopwatch,
    ) {}

    public function onKernelTerminate(TerminateEvent $event): void
    {
        if (!$event->isMainRequest()) {
            return;
        }

        $request = $event->getRequest();
        $response = $event->getResponse();

        // Only log API routes
        if (!str_starts_with($request->getPathInfo(), '/api/')) {
            return;
        }

        // Skip non-GET methods for now (adjust as needed)
        if ($request->getMethod() !== 'GET') {
            return;
        }

        // Calculate response time
        $responseTimeMs = $this->calculateResponseTime();

        $message = new LogApiRequestMessage(
            method: $request->getMethod(),
            path: $request->getPathInfo(),
            queryParams: $request->query->all(),
            responseTimeMs: $responseTimeMs,
            statusCode: $response->getStatusCode(),
            ipAddress: $request->getClientIp(),
            userAgent: $request->headers->get('User-Agent'),
            cacheStatus: $response->headers->get('X-Cache-Status'),
        );

        $this->messageBus->dispatch($message);
    }

    private function calculateResponseTime(): float
    {
        if ($this->stopwatch->isStarted('request')) {
            $event = $this->stopwatch->stop('request');
            return $event->getDuration();
        }

        // Fallback: calculate from REQUEST_TIME_FLOAT
        $startTime = $_SERVER['REQUEST_TIME_FLOAT'] ?? microtime(true);
        return (microtime(true) - $startTime) * 1000;
    }
}

Step 7: Configure Messenger Transport

File: config/packages/messenger.php (add to existing config)

// Add new transport for API logging
'api_request_log' => [
    'dsn' => '%env(MESSENGER_TRANSPORT_DSN)%',
    'options' => [
        'queue_name' => 'api_request_log',
    ],
],

// Add routing for the message
'routing' => [
    // ... existing routes ...
    LogApiRequestMessage::class => 'api_request_log',
],

Step 8: Create Data Retention Scheduler

File: src/Scheduler/Message/PurgeApiRequestOccurrencesMessage.php

<?php

namespace App\Scheduler\Message;

use Symfony\Component\Scheduler\Attribute\AsSchedule;

#[AsSchedule('purge_api_logs')]
final readonly class PurgeApiRequestOccurrencesMessage
{
    public function __construct(
        public int $retentionDays = 90,
    ) {}
}

File: src/Scheduler/Handler/PurgeApiRequestOccurrencesHandler.php

<?php

namespace App\Scheduler\Handler;

use App\Repository\ApiRequestOccurrenceRepository;
use App\Scheduler\Message\PurgeApiRequestOccurrencesMessage;
use Psr\Log\LoggerInterface;
use Symfony\Component\Messenger\Attribute\AsMessageHandler;

#[AsMessageHandler]
final readonly class PurgeApiRequestOccurrencesHandler
{
    public function __construct(
        private ApiRequestOccurrenceRepository $repository,
        private LoggerInterface $logger,
    ) {}

    public function __invoke(PurgeApiRequestOccurrencesMessage $message): void
    {
        $deletedCount = $this->repository->deleteOlderThan($message->retentionDays);

        $this->logger->info('Purged old API request occurrences', [
            'deleted_count' => $deletedCount,
            'retention_days' => $message->retentionDays,
        ]);
    }
}

Step 9: Generate Migration

make sf c="doctrine:migrations:diff"

Review and adjust the generated migration, ensuring indexes are created correctly.

Testing Strategy

Unit Tests

  1. LogApiRequestHandler - Test hash generation is deterministic
  2. ApiRequestPatternRepository - Test upsert increments hit_count correctly

Integration Tests

  1. Full pipeline test:
  2. Make API request
  3. Verify message is dispatched
  4. Process message with worker

  5. Verify pattern and occurrence records created

  6. Deduplication test:

  7. Make same request twice
  8. Verify only one pattern exists with hit_count = 2
  9. Verify two occurrences exist

Manual Testing

# Start worker for api_request_log transport
make sf c="messenger:consume api_request_log -vv"

# Make test requests
make api path="/api/coffee-beans"
make api path="/api/coffee-beans?roaster=some-uuid"

# Verify records
make sf c="dbal:run-sql 'SELECT * FROM api_request_pattern'"
make sf c="dbal:run-sql 'SELECT COUNT(*) FROM api_request_occurrence'"

Success Criteria

  • [ ] API requests to /api/* are logged without impacting response time
  • [ ] Identical requests share a single pattern with incrementing hit_count
  • [ ] Running average response time is calculated correctly
  • [ ] Data retention job removes old occurrences
  • [ ] Worker processes messages reliably
  • [ ] No duplicate patterns created under concurrent load (atomic upsert)

Security Considerations

PII in Logs

  • IP addresses: Stored for debugging, consider anonymization (zero last octet) if needed
  • Query params: May contain sensitive data - be aware when building admin UI
  • User agents: Generally safe, useful for bot detection

Data Retention

The 90-day default retention can be adjusted via the scheduler message constructor. Consider: - Shorter retention for high-traffic deployments - Longer retention for debugging historical issues

Future Enhancements (Out of Scope)

These will be addressed in follow-up plans:

  1. Admin UI (api-request-admin-ui.md)
  2. Cloudflare-style log viewer
  3. UUID resolution for human-readable display

  4. Filtering and time range selection

  5. Cache Warming (api-cache-warming.md)

  6. Event-driven warming on cache invalidation
  7. Query top patterns from this logging data
  8. Re-fetch endpoints to populate cache