UEX API rate limiting and concurrent sync prevention

[GitAddRemote]

User Story

As a backend engineer, I need the UEX API client to respect rate limits via configurable inter-request delays and response header inspection, and I need a PostgreSQL advisory lock to prevent concurrent ETL runs from racing against each other, so that we never trigger a UEX API ban and never corrupt the station_* tables with overlapping writes.

Definition of Done

• UexApiClient (or equivalent HTTP client wrapper) applies a configurable delay between requests (UEX_REQUEST_DELAY_MS, default 500)
• Client inspects X-RateLimit-Remaining response header; when ≤ 5, doubles the delay for the subsequent request
• Client inspects Retry-After response header on HTTP 429; sleeps for the specified seconds before retrying
• Client retries on HTTP 429 up to 3 times with exponential backoff before throwing
• Advisory lock logic extracted into a reusable AdvisoryLockService usable by any future long-running operation
• UEX_REQUEST_DELAY_MS documented in .env.example with default and description
• Unit tests cover: normal delay application, rate-limit header doubling, 429 retry, lock acquire success, lock acquire failure (409)
• pnpm test passes

Acceptance Criteria

• Mock a UEX client response with X-RateLimit-Remaining: 3 — the next request delay is 2× the configured base delay
• Mock a 429 response with Retry-After: 2 — client waits ~2 seconds then retries
• After 3 retries on 429 the client throws, which the ETL step catches and writes to station_etl_warnings
• AdvisoryLockService.withLock('catalog_etl', callback) executes callback and releases the lock in all code paths (success, throw)
• Calling withLock while the lock is held returns a rejected promise with ConflictException

Technical Elaboration

UexApiClient

Create or extend backend/src/modules/uex/uex-api.client.ts:

export class UexApiClient {
  private delayMs: number;

  constructor(private readonly config: UexApiConfig) {
    this.delayMs = config.requestDelayMs ?? 500;
  }

  async get<T>(path: string): Promise<T> {
    await sleep(this.delayMs);
    const response = await axios.get(`${this.config.baseUrl}${path}`);
    this.adjustDelay(response.headers);
    return response.data;
  }

  private adjustDelay(headers: Record<string, string>) {
    const remaining = parseInt(headers['x-ratelimit-remaining'] ?? '100', 10);
    if (remaining <= 5) {
      this.delayMs = this.delayMs * 2;
    }
  }
}

HTTP 429 handling via Axios interceptor:

axiosInstance.interceptors.response.use(null, async (error) => {
  if (error.response?.status === 429) {
    const retryAfter = parseInt(error.response.headers['retry-after'] ?? '5', 10);
    await sleep(retryAfter * 1000);
    // retry up to 3 times via retryCount metadata on the config
  }
  throw error;
});

AdvisoryLockService

@Injectable()
export class AdvisoryLockService {
  constructor(private readonly dataSource: DataSource) {}

  async withLock<T>(lockKey: string, fn: () => Promise<T>): Promise<T> {
    const key = `hashtext('${lockKey}')`;
    const [{ acquired }] = await this.dataSource.query(
      `SELECT pg_try_advisory_lock(${key}) AS acquired`
    );
    if (!acquired) throw new ConflictException(`Lock '${lockKey}' already held`);
    try {
      return await fn();
    } finally {
      await this.dataSource.query(`SELECT pg_advisory_unlock(${key})`);
    }
  }
}

Environment Variable

Add to .env.example:

# Milliseconds to wait between UEX API requests (default: 500)
# Increase if UEX rate-limits this application
UEX_REQUEST_DELAY_MS=500

Clustering Consideration

PostgreSQL advisory locks are session-scoped. When running multiple app replicas behind a load balancer, each replica uses a separate DB connection. The lock is held by exactly one connection at a time — if that process crashes, PostgreSQL automatically releases the lock when the TCP connection closes. This makes advisory locks the correct primitive for cluster-safe mutual exclusion without requiring Redis or a distributed lock service.

Testing

Use jest.spyOn on the DataSource query method to simulate lock acquisition and release. Use nock or axios-mock-adapter to simulate rate-limit response headers and 429 responses.

Design Elaboration

The UEX API is a third-party service with undocumented but observed rate limits. The 500ms default inter-request delay respects the UEX cache TTL (responses are cached server-side) and prevents hammering the API with redundant requests. The header-inspection approach is reactive — if UEX signals pressure via X-RateLimit-Remaining, we back off immediately rather than waiting for a 429.

Advisory locks are chosen over application-level mutex (in-memory Map) because in-memory state is per-process — it cannot prevent two separate Node.js processes or pods from running ETL concurrently. Advisory locks are chosen over Redis distributed locks because the database is already the bottleneck, Redis is an optional dependency (graceful fallback to memory cache), and the lock only needs to be held for the duration of a single ETL run (minutes, not milliseconds).

---

Depends on: #188