How OpenSEO Prevents Duplicate Rank Tracking Checks: Database Constraints and Application Guards

OpenSEO prevents duplicate rank tracking checks by combining a PostgreSQL partial unique index that blocks concurrent database inserts with application-level guard functions that handle race conditions and stale states.

The every-app/open-seo repository implements a robust concurrency control system to ensure that only one rank-tracking run can execute per configuration at any given time. This dual-layer approach leverages database constraints as the first line of defense, supplemented by TypeScript service logic that manages edge cases like server crashes and zombie processes.

Database-Level Protection with Partial Unique Indexes

The foundation of OpenSEO's duplicate prevention lies in a partial unique index defined directly in the database schema. This constraint makes it physically impossible to insert two active runs for the same configuration, eliminating race conditions at the storage layer.

Schema Definition in app.schema.ts

In src/db/app.schema.ts (lines 78-82), the rank_check_runs table declares a partial unique index on the config_id column, filtered to only apply when the status is either 'pending' or 'running':

// Partial unique index: only one active run per config
// Declared in src/db/app.schema.ts
export const rankCheckRuns = pgTable(
  'rank_check_runs',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    configId: uuid('config_id').notNull(),
    status: varchar('status', { enum: ['pending', 'running', 'completed', 'failed'] }).notNull(),
    // ... other columns
  },
  (table) => ({
    // Lines 78-82: Prevents duplicate active runs at the database level
    uniqueActiveRun: uniqueIndex('unique_active_run_per_config')
      .on(table.configId)
      .where(inArray(table.status, ['pending', 'running'])),
  })
);

Any insertion attempt that violates this constraint triggers a unique constraint violation error, which the application layer catches to determine that another run is already in progress.

Application-Level Coordination Logic

While the database constraint blocks invalid states, the application layer manages the workflow initiation through atomic operations and retry logic.

The tryCreateRun Repository Method

The RankTrackingRepository.ts file (lines 37-44) contains the tryCreateRun method, which attempts to insert a new run using ON CONFLICT DO NOTHING. This approach treats the database as the source of truth for concurrency:

// src/server/features/rank-tracking/repositories/RankTrackingRepository.ts
async tryCreateRun(configId: string, projectId: string) {
  const result = await db
    .insert(rankCheckRuns)
    .values({ configId, projectId, status: 'pending' })
    .onConflictDoNothing({ target: rankCheckRuns.configId }) // Uses the partial index
    .returning({ id: rankCheckRuns.id });

  return result.length > 0 ? result[0].id : null;
}

If tryCreateRun returns null, the application knows that an active run exists for that configuration and refuses to start a duplicate.

Guard Functions in rankCheckRunGuards.ts

The coordination orchestration happens in src/server/features/rank-tracking/services/rankCheckRunGuards.ts. The beginRankCheckRun function (lines 48-61) implements a retry loop that attempts to create a run, then handles the conflict case:

// src/server/features/rank-tracking/services/rankCheckRunGuards.ts (lines 48-61)
async function beginRankCheckRun(input: RankCheckInput) {
  for (let attempt = 0; attempt < MAX_RETRIES; attempt++) {
    const runId = await repository.tryCreateRun(input.configId, input.projectId);
    
    if (runId) {
      return { ok: true, runId };
    }
    
    // If insert failed, check if existing run is stale
    const activeRun = await repository.getActiveRunForConfig(input.configId);
    if (activeRun && isRunStale(activeRun)) {
      await failRunIfActive(activeRun.id);
      continue; // Retry after clearing the blocker
    }
    
    return { ok: false, reason: 'already_running' };
  }
}

When a conflict occurs, the code fetches the currently active run via getActiveRunForConfig and evaluates whether it has become stale (lines 96-118). If the existing run is stale, it is marked as failed to free the unique index slot, allowing the retry to succeed.

Handling Stale and Failed Runs

OpenSEO includes automatic cleanup mechanisms to prevent permanent deadlocks. If a workflow crashes, encounters an unhandled exception, or becomes unresponsive, the stale run detection logic identifies these orphaned records:

• getStaleRankCheckRunReason: Analyzes the run's start time and heartbeat to determine if it has exceeded the timeout threshold.
• failRunIfActive: Atomically updates the run status to failed, which removes it from the partial unique index constraint.

Once the status changes from running or pending to completed or failed, the unique index slot is released, allowing the next scheduled check to proceed.

Implementation Example

When triggering a manual check, the service returns an already_running response if the duplicate prevention mechanisms block the request:

// Trigger a manual rank-tracking check.
// The service will return `already_running` if another check is in progress.
import { RankTrackingService } from '@/server/features/rank-tracking/services/RankTrackingService';

async function runCheck(configId: string, projectId: string, billing) {
  try {
    const result = await RankTrackingService.triggerCheck({
      configId,
      projectId,
      billingCustomer: billing,
    });

    if (result.ok) {
      console.log('Rank check started, runId:', result.runId);
    } else {
      console.warn('Check not started – reason:', result.reason);
      // reason === 'already_running' means a duplicate was prevented
    }
  } catch (e) {
    console.error('Failed to trigger rank check', e);
  }
}

Summary

• Database constraint: A partial unique index on config_id (where status is pending or running) in app.schema.ts physically prevents duplicate active runs.
• Atomic insertion: The tryCreateRun method uses ON CONFLICT DO NOTHING to attempt creation and detect conflicts without race conditions.
• Stale run cleanup: The beginRankCheckRun guard detects stuck workflows via getStaleRankCheckRunReason and clears them using failRunIfActive to release the database slot.
• Graceful degradation: When duplicates are detected, the system returns already_running rather than queueing redundant work.

Frequently Asked Questions

How does OpenSEO handle race conditions when two users trigger a check simultaneously?

The partial unique index in app.schema.ts acts as the ultimate arbiter. Even if two tryCreateRun calls execute simultaneously from different server instances, PostgreSQL's atomic commit guarantees that only one insertion succeeds. The conflicting transaction receives a constraint violation, which the application interprets as an already_running response.

What happens if a rank tracking job crashes without updating the database?

The stale run detection logic in rankCheckRunGuards.ts monitors active runs for heartbeat timeouts. If getStaleRankCheckRunReason determines that a running job has exceeded the maximum execution time without updating its status, failRunIfActive marks it as failed. This status change removes the run from the unique index constraint, automatically freeing the slot for new checks.

Can the duplicate prevention be bypassed or disabled?

No. Because the constraint exists at the database schema level in app.schema.ts, it cannot be bypassed by application logic alone. The ON CONFLICT DO NOTHING clause in RankTrackingRepository.ts is the only insertion pattern used, ensuring that the database constraint remains the authoritative source of truth for run uniqueness.

How does the system differentiate between a stale run and a legitimate long-running check?

OpenSEO calculates staleness based on configurable timeouts and heartbeat timestamps. The getStaleRankCheckRunReason function compares the run's last heartbeat against the current time using thresholds defined in the configuration. Legitimate long-running checks periodically update their heartbeat, whereas crashed processes leave stale timestamps that trigger the cleanup logic.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}