09 — Canonical Data Model

Principles

Normalized relational model.
SQLite initially.
Paths are attributes, not permanent identity.
Historical scans and actions are retained.
Provider-specific details may be stored in extension tables/structured metadata only when normalization would add no value.
Core business entities should not be reduced to opaque JSON blobs.

Core Entities

`volume`

Represents a mounted physical/logical volume.

Key fields:

volume_id UUID
stable_identifier
name
mount_path
filesystem_type
is_internal
is_removable
capacity_bytes
available_bytes
first_seen_at
last_seen_at

`storage_root`

A configured/discovered root.

Fields:

root_id
volume_id
provider_id
path
display_name
role
scan_enabled
created_at
updated_at

`provider`

Normalized provider.

Examples:

LOCAL
ICLOUD
ONEDRIVE

Fields:

provider_id
provider_type
account_label
display_name

`scan`

One inventory execution.

Fields:

scan_id
started_at
completed_at
status
files_seen
directories_seen
logical_bytes_seen
error_count

`scan_root`

Tracks coverage per root.

Fields:

scan_root_id
scan_id
root_id
status
started_at
completed_at
error_count

`file_item`

Current known file/directory identity.

Fields:

file_id
volume_id
filesystem_identity
current_path
parent_path
name
extension
content_type
is_directory
is_symlink
is_package
is_hidden
logical_size_bytes
allocated_size_bytes
created_at_fs
modified_at_fs
accessed_at_fs
provider_id
cloud_state
first_seen_at
last_seen_at
exists_last_scan

`file_observation`

Optional historical observation per scan for attributes that changed.

Fields:

observation_id
scan_id
file_id
path
logical_size_bytes
allocated_size_bytes
modified_at_fs
cloud_state
observed_at

`file_hash`

Fields:

file_hash_id
file_id
algorithm
hash_type (PARTIAL, FULL)
digest
calculated_at
content_version_evidence
status

Unique constraint should prevent duplicate equivalent hash records.

`duplicate_group`

Fields:

duplicate_group_id
duplicate_type
algorithm
digest
logical_size_bytes
created_at
last_evaluated_at

`duplicate_member`

Fields:

duplicate_group_id
file_id
confidence
is_exact

`collection`

Logical protection grouping.

Fields:

collection_id
name
description
freshness_threshold_days
created_at

`collection_root`

Many-to-many mapping between collections and roots.

`recommendation`

Fields:

recommendation_id
file_id or duplicate_group_id
recommendation_type
reason_code
reason_text
confidence
estimated_reclaimable_bytes
created_at
status

`activity`

Immutable-ish operational/audit log.

Fields:

activity_id
activity_type
started_at
completed_at
status
summary
error_text

Future Backup Entities

`backup_job`

backup_job_id
name
enabled
destination_root_id
verification_policy
freshness_threshold_days

`backup_job_source`

Maps job to collection/root.

`backup_run`

backup_run_id
backup_job_id
timestamps/status/counts/bytes

`backup_copy`

Maps source file/version to destination and verification.

Future Archive Entities

`archive_operation`

Tracks transaction.

`archive_item`

Records original path, archive path, hashes, verification, source cleanup state.

Relationships

volume 1 ── * storage_root
provider 1 ── * storage_root
volume 1 ── * file_item
provider 1 ── * file_item
scan 1 ── * scan_root
scan 1 ── * file_observation
file_item 1 ── * file_hash
duplicate_group * ── * file_item (via duplicate_member)
collection * ── * storage_root (via collection_root)

Migration Policy

Schema changes must use ordered migrations committed to source control. Never mutate a production catalog schema ad hoc.