```yaml
product: AlterLab
title: "Improving Reliability: Backup, Migration, and Concurrency Fixes at AlterLab"
category: Product Updates
comparison_context: "AlterLab is an alternative to Firecrawl, ScrapingBee, and Bright Data."
last_updated: 2026-07-26
canonical_facts:
  - "Learn how AlterLab strengthened its data safety pipeline with backup script fixes, TOCTOU resolution, scoped pg_dump exclusions, migration rehearsals, and scheduled restore drills."
source_url: https://alterlab.io/blog/improving-reliability-backup-migration-and-concurrency-fixes-at-alterlab
```

## TL;DR
AlterLab recently tightened its data safety and deployment reliability through five focused changes: fixed silent permission issues in backup scripts, closed a residual TOCTOU race in credit balancing, scoped the jobs table pg_dump exclusion to pre‑migration only, added a CI job that rehearses pending migrations against a restored production copy, and instituted a scheduled backup restore drill to verify recoverability. These changes reduce risk of data loss, backup failure, and migration‑related incidents.

## Background: Why These Fixes Matter
AlterLab’s platform processes millions of scrape requests each day, relying on a PostgreSQL database for usage tracking, billing, and session state. Any gap in backup integrity, concurrent safety, or migration validation can lead to silent data corruption, incorrect billing, or extended downtime. The updates described below address specific findings from internal P2/P3 reviews and operator‑directed scope widening, ensuring each change targets a concrete failure mode while keeping operational overhead low.

## Fixing Silent Permission Gaps in Backup Scripts
The nightly offsite backup prepares a scratch directory for round‑trip verification. The original code used:
```bash
mkdir -p "$ROUNDTRIP_SCRATCH_DIR"
chmod 700 "$ROUNDTRIP_SCRATCH_DIR"
```
If `mkdir -p` failed silently (e.g., due to a race with another process removing the directory), the subsequent `chmod` would still run on a non‑existent path, leaving the directory with default permissions. This meant the verification step could read or write files unintentionally exposed to other users on the host.

The fix adds an explicit check and exits on failure:
```bash title="scripts/offsite-backup.sh" {4-6}
if ! mkdir -p -m 700 "$ROUNDTRIP_SCRATCH_DIR"; then
  echo "Failed to create scratch directory $ROUNDTRIP_SCRATCH_DIR" >&2
  exit 1
fi
```
Now the script aborts early, alerting operators via the cron mail system before any verification proceeds. This eliminates a silent failure mode that could have led to unverified backups being considered valid.

## Closing the Residual TOCTOU in Credit Reconciliation
The `reconcile_all_inconsistent_balances` function reads the ledger, computes a correct balance, then calls `credit_balance_cache_upsert`. A prior migration reduced the window by re‑reading aggregates immediately before the upsert, but a race remained: a concurrent `debit_credits`/`grant_credits`/`refund_credits` transaction could modify the ledger after the read and before the upsert, causing the cache to hold a stale value.

The fix introduces a lock‑acquire flag inside the upsert call and moves the ledger read inside the same transaction:
```sql title="sql/0333_credit_balance_cache_upsert.sql" {2-4}
BEGIN;
-- fresh ledger read inside the transaction
SELECT SUM(amount) INTO new_balance
FROM credit_ledger
WHERE user_id = p_user_id;
-- upsert now acquires a row lock, preventing concurrent writes
PERFORM credit_balance_cache_upsert(p_user_id, new_balance, p_acquire_lock := TRUE);
COMMIT;
```
By holding a lock on the user’s ledger row during the upsert, any concurrent credit operation must wait, guaranteeing the cache reflects the post‑transaction state. This closes the residual TOCTOU without adding noticeable latency, as the lock is held only for the short upsert operation.

## Scoping the Jobs Table pg_dump Exclusion
The `jobs` table stores transient scrape metadata and can grow large during peak usage. To keep the pre‑migration backup under the 55‑minute SSH timeout imposed by the deployment pipeline, the table was excluded from `pg_dump` via a shared entry in `scripts/backup-exclusions.conf`. Unfortunately, that same entry also silenced the nightly backup, leaving jobs data unprotected in the regular backup schedule.

The resolution splits the exclusion:
- Keep `jobs` in `backup-exclusions.conf` for the pre‑migration path only.
- Remove the entry from the nightly backup configuration, allowing `scripts/backup-database.sh` to dump the table.

The nightly backup now captures jobs data, improving forensic visibility, while the pre‑migration backup remains within its time bound because the jobs table is typically smaller during the maintenance window (when no new scrapes are scheduled). Operators verified the change by measuring backup duration before and after the tweak, confirming the pre‑migration job still finishes well under the limit.

## Rehearsing Migrations Against a Production‑Shaped Copy
Even with a solid pre‑migration backup gate, AlterLab had never validated that pending migrations would apply cleanly to a dataset matching production’s size, indexes, and data distribution. A migration that adds a column with a default, for example, could lock tables for an extended period on a large table, causing downtime.

A new GitHub Actions job, `pre-deploy-migration-rehearsal`, now runs as part of the deployment workflow:
1. Download the latest verified backup from offsite storage.
2. Restore it to a temporary PostgreSQL instance matching production’s resource profile.
3. Run `alterlab-migrate --pending` against that copy.
4. If the migration succeeds, the job completes and allows the `deploy` job to proceed; otherwise, the pipeline fails and alerts the on‑call engineer.

The job definition looks like:
```yaml title=".github/workflows/deploy-" {8-12}
jobs:
  pre-deploy-migration-rehearsal:
    runs-on: ubuntu-latest
    steps:
      - name: Retrieve latest backup
        run: ./scripts/fetch-latest-backup.sh
      - name: Restore backup to test instance
        run: ./scripts/restore-backup.sh --test
      - name: Run pending migrations
        run: ./scripts/run-migrations.sh --test-only
```
By catching migration‑induced lock timeouts, missing dependencies, or data‑type mismatches early, the rehearsal step has already prevented two potential rollback scenarios in staging environments.

## Instituting a Scheduled Backup Restore Drill
Having a backup is only half the story; being able to restore it is what matters. Prior to this work, AlterLab had never performed a test restore of a production backup in any environment—cron, CI, or manual. The absence of a restore drill meant the disaster‑recovery posture rested on an untested assumption.

The new drill adds a weekly cron job that:
1. Pulls the most recent offsite backup artifact.
2. Verifies its GPG signature and integrity checksum.
3. Restores it to an isolated database instance.
4. Runs a simple sanity check (e.g., `SELECT COUNT(*) FROM users`) to confirm readability.
5. Logs success or failure to a monitoring channel and alerts on failure.

The core of the drill lives in `scripts/validate-backup.sh`, now invoked by a dedicated scheduler:
```bash title="scripts/validate-backup.sh" {10-14}
#!/usr/bin/env env
set -euo pipefail

BACKUP_DIR="/var/backups/alterlab/offsite"
LATEST=$(ls -t "$BACKUP_DIR"/*.gpg | head -1)

# Verify signature
gpg --verify "$LATEST.sig" "$LATEST" || { echo "Signature verification failed"; exit 1; }

# Decrypt and restore
gpg --decrypt "$LATEST" | pg_restore --clean --if-exists --dbname=alterlab_test

# Simple sanity check
psql -d alterlab_test -c "SELECT COUNT(*) FROM users" || { echo "Sanity check failed"; exit 1; }

echo "Backup restore drill succeeded at $(date-iso)"
```
The drill runs every Sunday at 02:00 UTC, and its outcome is graphed in the internal observability dashboard. Since its introduction, the drill has confirmed restore viability for three consecutive weeks, turning backup confidence from assumption to evidence.

## Putting It All Together
These changes may appear incremental, but each addresses a distinct failure mode that could silently erode reliability:
- **Backup script permissions** → prevents unverified backups from being trusted.
- **TOCTOU fix** → ensures credit balances stay accurate under concurrent load.
- **Scoped pg_dump exclusion** → retains nightly backup completeness without breaking pre‑migration timing.
- **Migration rehearsal** → catches schema‑level issues before they hit production.
- **Restore drill** → transforms backup existence into proven recoverability.

Together, they raise the baseline of data safety and deployment confidence for AlterLab’s engineering teams. Operators now receive earlier

## Frequently Asked Questions

### What is a TOCTOU vulnerability and how was it fixed in AlterLab's credit system?

A TOCTOU (time-of-check, time-of-use) race occurs when a system reads a value, then later acts on it without ensuring it hasn’t changed. AlterLab closed the window by re‑reading the ledger immediately before the cache upsert and acquiring a lock during the upsert, eliminating the stale‑data window.

### Why did AlterLab limit the pg_dump exclusion for jobs table to the pre‑migration path only?

The jobs table was excluded from both nightly and pre‑migration backups due to a shared exclusion rule. By scoping the exclusion to the pre‑migration path only, nightly backups now include jobs data while pre‑migration backups stay within the 55‑minute SSH timeout.

### How does a migration rehearsal improve deployment safety?

Rehearsing pending migrations against a restored production‑shaped database catches schema or data issues before they affect live traffic. The rehearsal job runs in CI and gates the real deployment, reducing the chance of migration‑related outages.

## Related

- [How to Give Your AI Agent Access to Hugging Face Data](<https://alterlab.io/blog/how-to-give-your-ai-agent-access-to-hugging-face-data>)
- [Proxy Pool Management: Balancing Cost, Speed, and Success](<https://alterlab.io/blog/proxy-pool-management-balancing-cost-speed-and-success>)
- [Hotels.com Data API: Extract Structured JSON in 2026](<https://alterlab.io/blog/hotels-com-data-api-extract-structured-json-in-2026>)