09 — Canonical Data Model

From the Burrow™ specification set.

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

scan_error

Per-item or per-root failure recorded without aborting the scan (VLT-27).

Fields:

  • error_id
  • scan_id
  • scan_root_id
  • path
  • kind (permission-denied, unavailable-volume, inaccessible-placeholder, path-race, io)
  • message
  • scope (item or root)
  • recorded_at

Query coverage after a scan with Catalog.scanCoverage(scanId:). There is no HTTP scan-error route in 0.1.

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

cloud_state is the normalized value from 02 — Storage Model. For CLOUD_ONLY rows, inventory hash status is DEFERRED or UNAVAILABLE and is derived from that state plus item accessibility. Release 0.1 inventory does not persist a content digest on file_item and does not write file_hash rows. Migration 5 creates the file_hash table for HashService. Migration 6 creates duplicate_group and duplicate_member for exact groups. Migration 7 creates recommendation for informational reason codes. None of these migrations rebuild file_item.

Read-only Storage View analytics: Catalog.storageAnalytics() (optional scanId). It reads file_item (exists_last_scan = 1), volume capacity, provider type, and the latest scan timestamp. Folder totals are immediate-parent sums. Duplicate recovery is omitted as unavailable_until_0_2. There is no HTTP analytics route.

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

Migration 6 creates this table. DuplicateAnalyzer writes duplicate_type EXACT for full BLAKE3 matches. Reclaimable bytes are computed at read time and are not a column.

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

Migration 7 creates this table. Rows are informational. status is INFORMATIONAL. There is no apply transition.

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

This table is specified for later catalog persistence. 0.1 does not migrate it. ActivityService is the in-memory persist/query API (query(scanId:)). The hosted Activity view renders fixture events because HTTPS cannot call 127.0.0.1.

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 ── * scan_error
scan_root 1 ── * scan_error
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.