video_editing_poc/docs/backup-and-restore-validation.md

Backup And Restore Validation

This document captures the operational readiness artifact for plan item Backup and restore validation.

Scope

The service stores durable state in two places:

• PostgreSQL metadata
• S3-compatible object storage

Both must be considered together during backup and restore because database records reference object-storage keys directly.

State that matters for recovery:

• video_assets
• upload_sessions
• clip_jobs
• clips
• job_events
• asset_idempotency
• job_idempotency
• queue_messages
• source objects under uploads/<uploadId>/source
• generated clips under clips/<jobId>/<clipIndex>.mp4

Ephemeral state that does not need backup:

• local worker input files
• local FFmpeg output files
• tmp/in-memory-storage
• tmp/stub-output

Recovery Goal

After restore, the system must be able to:

• read existing assets, jobs, clips, and events
• generate signed clip download URLs for restored clips
• continue processing recoverable queued jobs
• avoid data-loss surprises caused by missing source or clip objects

Backup Boundary

Treat PostgreSQL and object storage as a coordinated recovery set.

Minimum acceptable backup content:

• full PostgreSQL snapshot including all tables listed above
• object-storage snapshot or versioned copy of:
  • uploads/
  • clips/

Important consistency note:

• A database-only backup is insufficient because video_assets.source_object_key and clips.object_key point to object-storage objects.
• An object-storage-only backup is insufficient because job, asset, clip, upload-session, and idempotency metadata live in PostgreSQL.

Validation Preconditions

Perform validation in a non-production environment using:

• the same schema migrations as production
• a restored PostgreSQL backup
• a restored object-storage snapshot or replicated bucket contents
• application config set to:
  • video-clipping.repository=jpa
  • video-clipping.storage=s3
• queue mode chosen explicitly:
  • video-clipping.queue=db if queue recovery is part of the drill
  • otherwise disable worker processing during the first verification pass

Before restore testing:

• stop API and worker writes to the target environment, or restore into a clean isolated environment
• apply Flyway migrations before importing data if restoring into an empty database
• restore object-storage contents before testing download or processing flows

Restore Order

1. Restore object-storage contents for uploads/ and clips/.
2. Restore PostgreSQL metadata snapshot.
3. Apply any required schema migrations not already present in the restored database.
4. Start the API service only.
5. Validate read-only recovery checks.
6. Start workers only after queue-state checks are complete.

This order reduces the risk of API or worker code touching metadata that references objects not yet present.

Validation Checks

1. Schema And Row Presence

Confirm the restored database contains:

• expected tables from V1 through V6
• representative rows in:
  • video_assets
  • upload_sessions
  • clip_jobs
  • clips
  • job_events
• idempotency tables if the backup includes recent write activity

Minimum SQL checks:

select count(*) from video_assets;
select count(*) from upload_sessions;
select count(*) from clip_jobs;
select count(*) from clips;
select count(*) from job_events;
select count(*) from queue_messages;

2. Referential Consistency

Validate there are no obvious orphan relationships.

Suggested SQL:

select count(*)
from upload_sessions u
left join video_assets a on a.id = u.asset_id
where a.id is null;

select count(*)
from clip_jobs j
left join video_assets a on a.id = j.asset_id
where a.id is null;

select count(*)
from clips c
left join clip_jobs j on j.id = c.job_id
where j.id is null;

Expected result: 0 for each query.

3. Object-Key Coverage

Validate that metadata-backed object keys exist in object storage.

Must-check samples:

• a restored source object from video_assets.source_object_key
• multiple restored clip objects from clips.object_key

Expected result:

• every sampled key resolves successfully in object storage
• API-generated download URLs work for restored clips

4. API Read Validation

With API pods running and workers still disabled or scaled to zero:

• fetch a restored asset with GET /v1/video-assets/{assetId}
• fetch its upload session with GET /v1/video-assets/{assetId}/upload-session
• fetch related jobs with GET /v1/video-assets/{assetId}/clip-jobs
• fetch a restored job with GET /v1/clip-jobs/{jobId}
• fetch clips with GET /v1/clip-jobs/{jobId}/clips
• generate a signed download URL with POST /v1/clips/{clipId}/download-url

Expected result:

• endpoints return restored metadata successfully
• signed clip download URL points to an object that can be retrieved

5. Queue Recovery Validation

If video-clipping.queue=db is used in production, validate queue state deliberately.

Inspect restored queue message status distribution:

select status, count(*)
from queue_messages
group by status
order by status;

Interpretation:

• COMPLETED rows are historical and should remain inert.
• DLQ rows should not be replayed automatically.
• PENDING rows may be processed again once workers start.
• PROCESSING rows may represent interrupted work from the backup point.

Safety rule:

• Do not start workers blindly if many restored messages are in PROCESSING.

Because the code reclaims expired PROCESSING rows after video-clipping.database-queue.visibility-timeout-ms, starting workers can cause restored in-flight jobs to retry automatically.

Pre-worker SQL to inspect interrupted work:

select id, job_id, status, attempt_count, available_at, claimed_at
from queue_messages
where status in ('PENDING', 'PROCESSING', 'DLQ')
order by available_at nulls first, claimed_at nulls first
limit 50;

Recommended recovery decision:

• convert only intentionally recoverable work back to active processing
• keep DLQ rows quarantined
• review PROCESSING rows before allowing workers to reclaim them

6. Worker Resume Validation

After queue review:

• start one worker replica
• observe whether a restored PENDING job completes successfully
• verify resulting clip metadata and object uploads remain consistent

Expected result:

• no retry storm
• no duplicate clip-index conflicts
• no immediate DLQ growth

7. Cleanup Safety Validation

Confirm retention jobs do not accidentally delete freshly restored data because of old timestamps.

Check:

• video_assets.expires_at
• clips.expires_at
• cleanup configuration in the restored environment

Safety rule:

• if restored data is intentionally old but still under investigation, temporarily disable cleanup before starting scheduled jobs

Relevant config:

• video-clipping.cleanup.enabled
• video-clipping.cleanup.source-retention-hours
• video-clipping.cleanup.clip-retention-hours

Pass Criteria

Backup and restore validation is considered successful when:

• schema restores cleanly
• representative rows exist for assets, jobs, clips, uploads, and events
• sampled source_object_key and object_key values exist in object storage
• restored API reads succeed
• signed clip download URL generation succeeds
• reviewed queue state does not cause unsafe automatic replay
• at least one representative queued job can resume successfully when workers restart

Failure Conditions

Validation fails if any of the following occur:

• restored metadata references missing storage objects
• signed download URLs are generated for missing objects
• queue replay starts processing work that should have remained quarantined
• restored jobs immediately churn into DLQ due to unresolved dependency or data issues
• cleanup jobs delete restored investigation data before verification completes

Recommended Drill Cadence

• Run a non-production restore drill at least quarterly.
• Run an additional drill before major schema or storage-layout changes.
• Repeat validation after changing retention policy, queue semantics, or storage provider configuration.

Current Repo Limitation

This repository does not include production backup automation or an executable restore harness.

This artifact defines the validation standard and the checks operators should run in the target environment. Production completion of the milestone still requires an actual restore drill using real infrastructure snapshots.