How OpenSEO Prevents Concurrent Rank Tracking Runs: Database Locks and Stale Run Guards

OpenSEO guarantees exclusive rank tracking execution by combining a partial unique database index with an active run-guard workflow that detects and clears stale runs before allowing new ones to start.

The OpenSEO repository eliminates race conditions in rank tracking operations through a database-centric concurrency control strategy. By enforcing uniqueness at the storage layer and implementing intelligent stale-run detection, the system ensures that only one active run exists per configuration at any given time.

Database-Level Exclusive Locking with Partial Unique Indexes

OpenSEO prevents concurrent rank tracking runs at the database layer using a partial unique index on the rank_check_runs table. This index enforces that only one row with status equal to 'pending' or 'running' can exist for a specific config_id.

According to the source code in src/server/features/rank-tracking/services/rankCheckRunGuards.ts, lines 11-16 describe the model definition that interacts with this constraint. The repository implementation in RankTrackingRepository.ts (lines 6-8 and 13-15) leverages this index when attempting to create new runs. The schema definition in src/server/db/schema.ts establishes this critical constraint, which serves as the foundation for all concurrency prevention logic.

When the tryCreateRun function attempts to insert a new run, the database either accepts the row or raises a unique constraint violation. This violation signals that another rank tracking run is already active for the configuration, triggering the conflict resolution workflow.

The Run Guard Workflow: Creating and Validating Runs

The beginRankCheckRun function in src/server/features/rank-tracking/services/rankCheckRunGuards.ts (lines 51-99) orchestrates the run creation process with built-in collision handling. This workflow implements a two-attempt loop that first tries to create a run, then handles conflicts by examining the blocking run.

If tryCreateRun fails due to the partial unique index conflict, the system calls getActiveRunForConfig to retrieve the blocking run details. The logic then evaluates whether the existing run is legitimate or stale. This approach ensures that transient failures or crashed workflows do not permanently block new rank tracking operations while maintaining strict protection against true concurrent execution.

Detecting and Cleaning Up Stale Runs

OpenSEO identifies stale runs through the getStaleRankCheckRunReason function (lines 91-114 in rankCheckRunGuards.ts). A run becomes stale when its associated workflow terminates, errors out, or completes without finalizing the run record. To prevent false positives for freshly initiated jobs, the system implements a grace window defined by the RANK_CHECK_STARTUP_GRACE_MS constant (line 56).

When the guard logic determines a run is stale, it invokes failRunIfActive (lines 20-38) to mark the run as failed. This operation updates the status column, effectively removing the entry from the partial unique index constraint and freeing the slot for the new run. The cleanup happens atomically within the workflow, ensuring that the transition from stale to failed state occurs before the second creation attempt.

Scheduled Run Coordination

The scheduled cron worker in src/server/features/rank-tracking/services/scheduledRankChecks.ts coordinates periodic rank checks through the claimDueConfig method. Located in RankTrackingRepository.ts (lines 74-99), this function selects configurations that are due for processing while respecting the same database constraints used in manual triggers.

Because both the claim operation and run creation respect the partial unique index, multiple concurrent cron invocations automatically serialize. The first worker successfully claims and starts the run, while subsequent workers encounter the database constraint and skip the configuration until the active run completes.

Implementation Code Examples

Attempting to Start a Rank Tracking Run

When initiating a rank check from a workflow or manual trigger, the system calls beginRankCheckRun with configuration details:

// In a workflow (e.g., manual trigger or scheduled cron)
const result = await beginRankCheckRun({
  workflow: env.RANK_CHECK_WORKFLOW,
  config,                       // RankTrackingConfig
  projectId,
  billingCustomer,
  keywordsTotal,
  trigger: "manual",           // or "scheduled"
  workflowStartErrorMessage: "Failed to start rank‑check workflow",
});

if (!result.ok) {
  // result.reason === "already_running"
  console.warn(`Run blocked by ${result.blockingRunId}`);
}

Source: src/server/features/rank-tracking/services/rankCheckRunGuards.ts – beginRankCheckRun function (lines 51-99).

Detecting and Failing Stale Runs

The following pattern demonstrates how the system identifies and removes blocking stale runs:

const blocker = await RankTrackingRepository.getActiveRunForConfig(config.id);
const staleReason = await getStaleRankCheckRunReason({
  run: blocker,
  runId: blocker.id,
  ageMs: Date.now() - new Date(blocker.startedAt).getTime(),
});

if (staleReason) {
  await failRunIfActive(blocker.id, staleReason, blocker);
  // Now the slot is free and a new run can proceed.
}

Source: rankCheckRunGuards.ts – stale-run logic (lines 90-118) and failRunIfActive (lines 20-38).

Repository Insert with Conflict Handling

The tryCreateRun function implements the database-level guard using the partial unique index:

async function tryCreateRun(data) {
  const inserted = await db
    .insert(rankCheckRuns)
    .values({ ...data, status: "pending" })
    .onConflictDoNothing()   // <-- respects the partial unique index
    .returning({ id: rankCheckRuns.id });
  return Boolean(inserted[0]);
}

Source: src/server/features/rank-tracking/repositories/RankTrackingRepository.ts – tryCreateRun (lines 13-26).

Summary

• Database-level locking: A partial unique index on rank_check_runs enforces single active run semantics per configuration at the storage layer.
• Conflict resolution: The beginRankCheckRun workflow in rankCheckRunGuards.ts handles creation conflicts by identifying and evaluating blocking runs.
• Stale run detection: The system uses getStaleRankCheckRunReason and RANK_CHECK_STARTUP_GRACE_MS to distinguish between active workflows and orphaned runs.
• Automatic cleanup: failRunIfActive terminates stale runs, releasing the database constraint slot for new executions.
• Scheduled coordination: The cron worker leverages claimDueConfig to serialize concurrent scheduled invocations without additional locking mechanisms.

Frequently Asked Questions

What happens when two rank tracking runs are triggered simultaneously for the same configuration?

The database partial unique index blocks the second insertion attempt. The beginRankCheckRun function catches this conflict, retrieves the blocking run via getActiveRunForConfig, and either waits for it to complete or fails it if determined to be stale. Only one run proceeds to execution while the other receives an "already_running" response.

How does OpenSEO determine if an existing rank tracking run is stale?

The getStaleRankCheckRunReason function (lines 91-114 in rankCheckRunGuards.ts) checks whether the workflow associated with the run is still active. Runs with terminated, errored, or completed workflows qualify as stale. The system also applies the RANK_CHECK_STARTUP_GRACE_MS grace period to avoid flagging runs that started milliseconds ago and have not yet fully initialized.

What prevents scheduled cron jobs from creating overlapping rank tracking runs?

The claimDueConfig method in RankTrackingRepository.ts (lines 74-99) uses the same database constraints as manual triggers. When multiple cron workers attempt to claim the same due configuration simultaneously, the partial unique index allows only one successful insertion into rank_check_runs. Subsequent workers encounter conflicts and skip to the next available configuration.

Which component is responsible for cleaning up stale runs that block new executions?

The failRunIfActive function in rankCheckRunGuards.ts (lines 20-38) handles cleanup by marking stale runs as failed. This status update removes the run from the partial unique index constraint, effectively releasing the exclusive lock and allowing the new run creation to proceed on the second attempt loop within beginRankCheckRun.

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