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

> OpenSEO prevents concurrent rank tracking runs with database locks and stale run guards. Ensure exclusive execution and clear old processes automatically.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: internals
- Published: 2026-08-30

---

**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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/services/scheduledRankChecks.ts) coordinates periodic rank checks through the `claimDueConfig` method. Located in [`RankTrackingRepository.ts`](https://github.com/every-app/open-seo/blob/main/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:

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/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:

```typescript
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`](https://github.com/every-app/open-seo/blob/main/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:

```typescript
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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`.