30 lines
1.1 KiB
SQL
30 lines
1.1 KiB
SQL
-- Create backup_jobs table for async backup job tracking
|
|
-- This should be run on all library databases (default and afterdark)
|
|
|
|
CREATE TABLE IF NOT EXISTS backup_jobs (
|
|
id UUID PRIMARY KEY,
|
|
library_id VARCHAR(255) NOT NULL,
|
|
type VARCHAR(50) NOT NULL CHECK (type IN ('DATABASE_ONLY', 'COMPLETE')),
|
|
status VARCHAR(50) NOT NULL CHECK (status IN ('PENDING', 'IN_PROGRESS', 'COMPLETED', 'FAILED', 'EXPIRED')),
|
|
file_path VARCHAR(1000),
|
|
file_size_bytes BIGINT,
|
|
progress_percent INTEGER,
|
|
error_message VARCHAR(1000),
|
|
created_at TIMESTAMP NOT NULL,
|
|
started_at TIMESTAMP,
|
|
completed_at TIMESTAMP,
|
|
expires_at TIMESTAMP
|
|
);
|
|
|
|
-- Create index on library_id for faster lookups
|
|
CREATE INDEX IF NOT EXISTS idx_backup_jobs_library_id ON backup_jobs(library_id);
|
|
|
|
-- Create index on status for cleanup queries
|
|
CREATE INDEX IF NOT EXISTS idx_backup_jobs_status ON backup_jobs(status);
|
|
|
|
-- Create index on expires_at for cleanup queries
|
|
CREATE INDEX IF NOT EXISTS idx_backup_jobs_expires_at ON backup_jobs(expires_at);
|
|
|
|
-- Create index on created_at for ordering
|
|
CREATE INDEX IF NOT EXISTS idx_backup_jobs_created_at ON backup_jobs(created_at DESC);
|