Database Schema & Entities

// Location: core/src/entities/sync_conduit.rs

pub struct Model {
    pub id: i32,
    pub uuid: Uuid,

    // Endpoints - both must be Entry records of type Directory
    pub source_entry_id: i32,
    pub target_entry_id: i32,

    // Configuration
    pub sync_mode: String,            // "mirror" | "bidirectional" | "selective"
    pub enabled: bool,
    pub schedule: String,             // "instant" | "interval:5m" | "manual"

    // Index settings
    pub use_index_rules: bool,        // Default: true
    pub index_mode_override: Option<String>,

    // Performance tuning
    pub parallel_transfers: i32,
    pub bandwidth_limit_mbps: Option<i32>,

    // State tracking
    pub last_sync_completed_at: Option<DateTimeUtc>,
    pub sync_generation: i64,
    pub last_sync_error: Option<String>,

    // Statistics
    pub total_syncs: i64,
    pub files_synced: i64,
    pub bytes_transferred: i64,

    pub created_at: DateTimeUtc,
    pub updated_at: DateTimeUtc,
}

// Location: core/src/entities/sync_generation.rs

pub struct Model {
    pub id: i32,
    pub conduit_id: i32,
    pub generation: i64,
    pub started_at: DateTimeUtc,
    pub completed_at: Option<DateTimeUtc>,

    // Operation summary
    pub files_copied: i32,
    pub files_deleted: i32,
    pub conflicts_resolved: i32,
    pub bytes_transferred: i64,
    pub errors_encountered: i32,

    // Verification tracking (Trust Watcher approach)
    pub verified_at: Option<DateTimeUtc>,
    pub verification_status: String, // "unverified" | "waiting_watcher" | "verified" | "failed:reason"
}

// Location: core/src/migrations/m20250315_000001_create_sync_tables.rs

async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
    // Create sync_conduit table with all columns
    // Create sync_generation table with all columns
    // Create indexes:
    //   - idx_sync_conduit_enabled (enabled)
    //   - idx_sync_generation_conduit (conduit_id, generation)
}

CREATE TABLE sync_conduit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid BLOB NOT NULL UNIQUE,
    source_entry_id INTEGER NOT NULL,
    target_entry_id INTEGER NOT NULL,
    sync_mode TEXT NOT NULL,
    enabled INTEGER NOT NULL DEFAULT 1,
    schedule TEXT NOT NULL DEFAULT 'manual',
    use_index_rules INTEGER NOT NULL DEFAULT 1,
    index_mode_override TEXT,
    parallel_transfers INTEGER NOT NULL DEFAULT 3,
    bandwidth_limit_mbps INTEGER,
    last_sync_completed_at TEXT,
    sync_generation INTEGER NOT NULL DEFAULT 0,
    last_sync_error TEXT,
    total_syncs INTEGER NOT NULL DEFAULT 0,
    files_synced INTEGER NOT NULL DEFAULT 0,
    bytes_transferred INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (source_entry_id) REFERENCES entry(id) ON DELETE CASCADE,
    FOREIGN KEY (target_entry_id) REFERENCES entry(id) ON DELETE CASCADE
);

CREATE INDEX idx_sync_conduit_enabled ON sync_conduit(enabled);

CREATE TABLE sync_generation (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    conduit_id INTEGER NOT NULL,
    generation INTEGER NOT NULL,
    started_at TEXT NOT NULL,
    completed_at TEXT,
    files_copied INTEGER NOT NULL DEFAULT 0,
    files_deleted INTEGER NOT NULL DEFAULT 0,
    conflicts_resolved INTEGER NOT NULL DEFAULT 0,
    bytes_transferred INTEGER NOT NULL DEFAULT 0,
    errors_encountered INTEGER NOT NULL DEFAULT 0,
    verified_at TEXT,
    verification_status TEXT NOT NULL DEFAULT 'unverified',
    FOREIGN KEY (conduit_id) REFERENCES sync_conduit(id) ON DELETE CASCADE
);

CREATE INDEX idx_sync_generation_conduit ON sync_generation(conduit_id, generation);

sync_conduit
  ├─> entry (source_entry_id)
  ├─> entry (target_entry_id)
  └─> sync_generation (one-to-many)