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

> Discover how OpenSEO prevents duplicate rank tracking checks using PostgreSQL partial unique indexes and application guards to ensure data integrity and system efficiency.

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

---

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

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

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

```typescript
// 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:

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/app.schema.ts), it cannot be bypassed by application logic alone. The `ON CONFLICT DO NOTHING` clause in [`RankTrackingRepository.ts`](https://github.com/every-app/open-seo/blob/main/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.