Entity Relationship Diagram

📁 sources

            PK id INTEGER
               name TEXT
               type TEXT (local|ssh|database)
               config JSON
               created_at DATETIME
               updated_at DATETIME
          
⚙️ jobs

            PK id INTEGER
               name TEXT
               source_id INTEGER → sources
               strategy TEXT (full|incremental)
               destination_path TEXT
               exclude_patterns JSON
               enabled BOOLEAN
               created_at DATETIME
               updated_at DATETIME
          
📅 schedules

            PK id INTEGER
               job_id INTEGER → jobs
               cron_expression TEXT
               enabled BOOLEAN
               created_at DATETIME
          
▶️ job_executions

            PK id INTEGER
               job_id INTEGER → jobs
               status TEXT (pending|running|success|failed|cancelled)
               started_at DATETIME
               completed_at DATETIME
               bytes_processed INTEGER
               bytes_backed_up INTEGER
               error_message TEXT
               triggered_by TEXT (manual|schedule)
          
💾 backups

            PK id INTEGER
               execution_id INTEGER → job_executions
               storage_path TEXT
               size_bytes INTEGER
               checksum TEXT
               type TEXT (full|incremental)
               parent_backup_id INTEGER → backups (for incremental)
               created_at DATETIME
          
⚙️ settings

            PK key TEXT
               value TEXT
               updated_at DATETIME
          

Key Relationships

sources → jobs: One source can have many jobs (different backup strategies for the same data)
jobs → schedules: One job can have one schedule (1:1 for simplicity)
jobs → job_executions: One job has many executions over time
job_executions → backups: Each execution produces one or more backup archives
backups → backups: Self-referencing for incremental chain (parent_backup_id)

Design Decisions

Pros

Flexible source config via JSON (extensible for new source types)
Execution history tracking for audit and debugging
Incremental backup chain support
Simple key-value settings (easy to extend)

Data Model

Key Relationships

Design Decisions

Pros

Cons>/h4>

JSON config loses some type safety (mitigated by validation)

No built-in backup retention policy table (can be added later)

Data Model

Key Relationships

Design Decisions

Pros

Cons>/h4> JSON config loses some type safety (mitigated by validation) No built-in backup retention policy table (can be added later)

Cons>/h4>

JSON config loses some type safety (mitigated by validation)

No built-in backup retention policy table (can be added later)