# Syncova Backups V1 — PostgreSQL Database Design

## 1. Database Rules

- PostgreSQL is the control-plane database.
- Backup payloads are never stored in PostgreSQL.
- UUIDs are preferred for public identifiers.
- All timestamps are UTC.
- Foreign keys are mandatory for relationships.
- Use soft deletion where audit/history requires it.
- Never store passwords or raw encryption keys.
- Every schema change uses a migration.

## 2. Core Tables

### users

```sql
id UUID PRIMARY KEY
username TEXT UNIQUE NOT NULL
email TEXT UNIQUE
password_hash TEXT
status TEXT NOT NULL
mfa_enabled BOOLEAN NOT NULL DEFAULT FALSE
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
last_login_at TIMESTAMPTZ
```

### user_mfa_methods

```sql
id UUID PRIMARY KEY
user_id UUID REFERENCES users(id)
type TEXT NOT NULL
secret_ciphertext BYTEA
enabled BOOLEAN NOT NULL
created_at TIMESTAMPTZ NOT NULL
```

### roles

```sql
id UUID PRIMARY KEY
name TEXT UNIQUE NOT NULL
description TEXT
```

### permissions

```sql
id UUID PRIMARY KEY
name TEXT UNIQUE NOT NULL
description TEXT
```

### role_permissions

```sql
role_id UUID REFERENCES roles(id)
permission_id UUID REFERENCES permissions(id)
PRIMARY KEY(role_id, permission_id)
```

### user_roles

```sql
user_id UUID REFERENCES users(id)
role_id UUID REFERENCES roles(id)
PRIMARY KEY(user_id, role_id)
```

## 3. Agents

### agents

```sql
id UUID PRIMARY KEY
name TEXT NOT NULL
hostname TEXT
platform TEXT NOT NULL
version TEXT
status TEXT NOT NULL
last_heartbeat_at TIMESTAMPTZ
registered_at TIMESTAMPTZ NOT NULL
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
```

### agent_certificates

```sql
id UUID PRIMARY KEY
agent_id UUID REFERENCES agents(id)
fingerprint TEXT UNIQUE NOT NULL
not_before TIMESTAMPTZ
not_after TIMESTAMPTZ
status TEXT NOT NULL
created_at TIMESTAMPTZ NOT NULL
```

## 4. Proxmox

### proxmox_clusters

```sql
id UUID PRIMARY KEY
name TEXT NOT NULL
api_endpoint TEXT NOT NULL
credential_ref TEXT NOT NULL
status TEXT NOT NULL
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
```

### proxmox_hosts

```sql
id UUID PRIMARY KEY
cluster_id UUID REFERENCES proxmox_clusters(id)
node_name TEXT NOT NULL
status TEXT NOT NULL
last_seen_at TIMESTAMPTZ
created_at TIMESTAMPTZ NOT NULL
```

### virtual_machines

```sql
id UUID PRIMARY KEY
cluster_id UUID REFERENCES proxmox_clusters(id)
host_id UUID REFERENCES proxmox_hosts(id)
provider_vm_id TEXT NOT NULL
name TEXT NOT NULL
status TEXT
cpu_count INTEGER
memory_bytes BIGINT
config_json JSONB
last_discovered_at TIMESTAMPTZ
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
UNIQUE(cluster_id, provider_vm_id)
```

## 5. Repositories

### repositories

```sql
id UUID PRIMARY KEY
name TEXT UNIQUE NOT NULL
type TEXT NOT NULL
endpoint TEXT NOT NULL
status TEXT NOT NULL
capacity_bytes BIGINT
used_bytes BIGINT
free_bytes BIGINT
immutable BOOLEAN NOT NULL DEFAULT FALSE
encryption_required BOOLEAN NOT NULL DEFAULT TRUE
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
```

### repository_health

```sql
id UUID PRIMARY KEY
repository_id UUID REFERENCES repositories(id)
health_status TEXT NOT NULL
latency_ms DOUBLE PRECISION
error_count BIGINT DEFAULT 0
checked_at TIMESTAMPTZ NOT NULL
details JSONB
```

## 6. Backup Jobs

### backup_jobs

```sql
id UUID PRIMARY KEY
name TEXT UNIQUE NOT NULL
description TEXT
status TEXT NOT NULL
priority TEXT NOT NULL
schedule_type TEXT NOT NULL
schedule_config JSONB NOT NULL
retention_policy_id UUID
repository_id UUID REFERENCES repositories(id)
encryption_policy_id UUID
verification_policy_id UUID
notification_policy_id UUID
rpo_seconds BIGINT
rto_seconds BIGINT
bandwidth_limit_bps BIGINT
max_concurrency INTEGER
created_by UUID REFERENCES users(id)
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
```

### backup_job_sources

```sql
id UUID PRIMARY KEY
job_id UUID REFERENCES backup_jobs(id)
source_type TEXT NOT NULL
source_id UUID
include_patterns JSONB
exclude_patterns JSONB
created_at TIMESTAMPTZ NOT NULL
```

## 7. Backup Runs

### backup_job_runs

```sql
id UUID PRIMARY KEY
job_id UUID REFERENCES backup_jobs(id)
status TEXT NOT NULL
started_at TIMESTAMPTZ
completed_at TIMESTAMPTZ
bytes_processed BIGINT DEFAULT 0
bytes_written BIGINT DEFAULT 0
bytes_transferred BIGINT DEFAULT 0
throughput_bps BIGINT
error_code TEXT
error_message TEXT
correlation_id UUID NOT NULL
created_at TIMESTAMPTZ NOT NULL
```

## 8. Backups and Chains

### backup_chains

```sql
id UUID PRIMARY KEY
source_id UUID NOT NULL
status TEXT NOT NULL
created_at TIMESTAMPTZ NOT NULL
```

### backups

```sql
id UUID PRIMARY KEY
job_run_id UUID REFERENCES backup_job_runs(id)
chain_id UUID REFERENCES backup_chains(id)
repository_id UUID REFERENCES repositories(id)
parent_backup_id UUID REFERENCES backups(id)
backup_type TEXT NOT NULL
consistency_level TEXT
status TEXT NOT NULL
manifest_ref TEXT NOT NULL
logical_bytes BIGINT
unique_bytes BIGINT
compressed_bytes BIGINT
encrypted_bytes BIGINT
started_at TIMESTAMPTZ
completed_at TIMESTAMPTZ
integrity_status TEXT
verified_at TIMESTAMPTZ
immutable_until TIMESTAMPTZ
created_at TIMESTAMPTZ NOT NULL
```

## 9. Chunks

The database stores references/index information, not chunk payloads.

### chunks

```sql
id UUID PRIMARY KEY
repository_id UUID REFERENCES repositories(id)
content_hash TEXT NOT NULL
size_bytes BIGINT NOT NULL
compressed_size_bytes BIGINT
encryption_version TEXT
storage_ref TEXT NOT NULL
created_at TIMESTAMPTZ NOT NULL
UNIQUE(repository_id, content_hash)
```

### chunk_references

```sql
backup_id UUID REFERENCES backups(id)
chunk_id UUID REFERENCES chunks(id)
sequence_no BIGINT NOT NULL
logical_offset BIGINT
length_bytes BIGINT
PRIMARY KEY(backup_id, sequence_no)
```

## 10. Restore

### restore_jobs

```sql
id UUID PRIMARY KEY
backup_id UUID REFERENCES backups(id)
source_type TEXT NOT NULL
target_type TEXT NOT NULL
target_ref TEXT
status TEXT NOT NULL
started_at TIMESTAMPTZ
completed_at TIMESTAMPTZ
bytes_restored BIGINT
error_code TEXT
error_message TEXT
created_by UUID REFERENCES users(id)
created_at TIMESTAMPTZ NOT NULL
```

### restore_sessions

```sql
id UUID PRIMARY KEY
restore_job_id UUID REFERENCES restore_jobs(id)
state TEXT NOT NULL
checkpoint JSONB
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
```

## 11. Verification

### verification_jobs

```sql
id UUID PRIMARY KEY
backup_id UUID REFERENCES backups(id)
type TEXT NOT NULL
status TEXT NOT NULL
started_at TIMESTAMPTZ
completed_at TIMESTAMPTZ
created_at TIMESTAMPTZ NOT NULL
```

### verification_results

```sql
id UUID PRIMARY KEY
verification_job_id UUID REFERENCES verification_jobs(id)
check_name TEXT NOT NULL
status TEXT NOT NULL
details JSONB
created_at TIMESTAMPTZ NOT NULL
```

## 12. Alerts and Events

### alert_rules

```sql
id UUID PRIMARY KEY
name TEXT UNIQUE NOT NULL
enabled BOOLEAN NOT NULL DEFAULT TRUE
severity TEXT NOT NULL
condition JSONB NOT NULL
actions JSONB NOT NULL
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
```

### alerts

```sql
id UUID PRIMARY KEY
rule_id UUID REFERENCES alert_rules(id)
severity TEXT NOT NULL
status TEXT NOT NULL
title TEXT NOT NULL
message TEXT NOT NULL
entity_type TEXT
entity_id UUID
created_at TIMESTAMPTZ NOT NULL
acknowledged_at TIMESTAMPTZ
resolved_at TIMESTAMPTZ
```

### events

```sql
id UUID PRIMARY KEY
event_type TEXT NOT NULL
severity TEXT NOT NULL
entity_type TEXT
entity_id UUID
message TEXT NOT NULL
details JSONB
correlation_id UUID
created_at TIMESTAMPTZ NOT NULL
```

## 13. Audit

### audit_events

```sql
id UUID PRIMARY KEY
user_id UUID REFERENCES users(id)
action TEXT NOT NULL
entity_type TEXT
entity_id UUID
result TEXT NOT NULL
ip_address INET
user_agent TEXT
details JSONB
correlation_id UUID
created_at TIMESTAMPTZ NOT NULL
```

Audit data should be append-oriented.

## 14. Metrics

### metrics

```sql
id BIGSERIAL PRIMARY KEY
metric_name TEXT NOT NULL
entity_type TEXT
entity_id UUID
value DOUBLE PRECISION NOT NULL
unit TEXT
timestamp TIMESTAMPTZ NOT NULL
labels JSONB
```

Use indexes on:

```text
(metric_name, timestamp)
(entity_type, entity_id, timestamp)
```

For large installations, introduce time partitioning and rollups.

## 15. Policies

### retention_policies

```sql
id UUID PRIMARY KEY
name TEXT UNIQUE NOT NULL
rules JSONB NOT NULL
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
```

### encryption_policies

```sql
id UUID PRIMARY KEY
name TEXT UNIQUE NOT NULL
algorithm TEXT NOT NULL
key_ref TEXT NOT NULL
rotation_policy JSONB
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
```

### verification_policies

```sql
id UUID PRIMARY KEY
name TEXT UNIQUE NOT NULL
rules JSONB NOT NULL
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
```

### notification_policies

```sql
id UUID PRIMARY KEY
name TEXT UNIQUE NOT NULL
channels JSONB NOT NULL
rules JSONB NOT NULL
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL
```

## 16. Certificates and Configuration

### certificates

```sql
id UUID PRIMARY KEY
name TEXT UNIQUE NOT NULL
type TEXT NOT NULL
fingerprint TEXT UNIQUE NOT NULL
not_before TIMESTAMPTZ
not_after TIMESTAMPTZ
status TEXT NOT NULL
secret_ref TEXT
created_at TIMESTAMPTZ NOT NULL
```

### system_settings

```sql
key TEXT PRIMARY KEY
value_json JSONB NOT NULL
updated_at TIMESTAMPTZ NOT NULL
updated_by UUID REFERENCES users(id)
```

## 17. Recommended Indexes

Create indexes for:

- backup_jobs(status)
- backup_job_runs(job_id, started_at DESC)
- backups(repository_id, completed_at DESC)
- backups(chain_id, completed_at DESC)
- backups(parent_backup_id)
- virtual_machines(cluster_id)
- agents(status)
- alerts(status, severity, created_at DESC)
- events(created_at DESC)
- audit_events(created_at DESC)
- metrics(metric_name, timestamp DESC)

## 18. Migration Strategy

Use a migration tool such as:

- golang-migrate
- Atlas

Every schema change must be versioned.

Production startup must never silently mutate the schema.