NotAShelf
f34c78b238
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: Id99798df6f7e4470caae8a193c2654aa6a6a6964
103 lines
3.4 KiB
SQL
103 lines
3.4 KiB
SQL
-- V16: Cross-Device Sync System
|
|
-- Adds device registration, change tracking, and chunked upload support
|
|
|
|
-- Sync devices table
|
|
CREATE TABLE sync_devices (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
device_type TEXT NOT NULL,
|
|
client_version TEXT NOT NULL,
|
|
os_info TEXT,
|
|
device_token_hash TEXT NOT NULL UNIQUE,
|
|
last_sync_at TIMESTAMPTZ,
|
|
last_seen_at TIMESTAMPTZ NOT NULL,
|
|
sync_cursor BIGINT DEFAULT 0,
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_sync_devices_user ON sync_devices(user_id);
|
|
CREATE INDEX idx_sync_devices_token ON sync_devices(device_token_hash);
|
|
|
|
-- Sync log table - tracks all changes for sync
|
|
CREATE TABLE sync_log (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
sequence BIGSERIAL UNIQUE NOT NULL,
|
|
change_type TEXT NOT NULL,
|
|
media_id TEXT REFERENCES media_items(id) ON DELETE SET NULL,
|
|
path TEXT NOT NULL,
|
|
content_hash TEXT,
|
|
file_size BIGINT,
|
|
metadata_json TEXT,
|
|
changed_by_device TEXT REFERENCES sync_devices(id) ON DELETE SET NULL,
|
|
timestamp TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_sync_log_sequence ON sync_log(sequence);
|
|
CREATE INDEX idx_sync_log_path ON sync_log(path);
|
|
CREATE INDEX idx_sync_log_timestamp ON sync_log(timestamp);
|
|
|
|
-- Device sync state - tracks sync status per device per file
|
|
CREATE TABLE device_sync_state (
|
|
device_id TEXT NOT NULL REFERENCES sync_devices(id) ON DELETE CASCADE,
|
|
path TEXT NOT NULL,
|
|
local_hash TEXT,
|
|
server_hash TEXT,
|
|
local_mtime BIGINT,
|
|
server_mtime BIGINT,
|
|
sync_status TEXT NOT NULL,
|
|
last_synced_at TIMESTAMPTZ,
|
|
conflict_info_json TEXT,
|
|
PRIMARY KEY (device_id, path)
|
|
);
|
|
|
|
CREATE INDEX idx_device_sync_status ON device_sync_state(device_id, sync_status);
|
|
|
|
-- Upload sessions for chunked uploads
|
|
CREATE TABLE upload_sessions (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
device_id TEXT NOT NULL REFERENCES sync_devices(id) ON DELETE CASCADE,
|
|
target_path TEXT NOT NULL,
|
|
expected_hash TEXT NOT NULL,
|
|
expected_size BIGINT NOT NULL,
|
|
chunk_size BIGINT NOT NULL,
|
|
chunk_count BIGINT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
last_activity TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_upload_sessions_device ON upload_sessions(device_id);
|
|
CREATE INDEX idx_upload_sessions_status ON upload_sessions(status);
|
|
CREATE INDEX idx_upload_sessions_expires ON upload_sessions(expires_at);
|
|
|
|
-- Upload chunks - tracks received chunks
|
|
CREATE TABLE upload_chunks (
|
|
upload_id TEXT NOT NULL REFERENCES upload_sessions(id) ON DELETE CASCADE,
|
|
chunk_index BIGINT NOT NULL,
|
|
offset BIGINT NOT NULL,
|
|
size BIGINT NOT NULL,
|
|
hash TEXT NOT NULL,
|
|
received_at TIMESTAMPTZ NOT NULL,
|
|
PRIMARY KEY (upload_id, chunk_index)
|
|
);
|
|
|
|
-- Sync conflicts
|
|
CREATE TABLE sync_conflicts (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
device_id TEXT NOT NULL REFERENCES sync_devices(id) ON DELETE CASCADE,
|
|
path TEXT NOT NULL,
|
|
local_hash TEXT NOT NULL,
|
|
local_mtime BIGINT NOT NULL,
|
|
server_hash TEXT NOT NULL,
|
|
server_mtime BIGINT NOT NULL,
|
|
detected_at TIMESTAMPTZ NOT NULL,
|
|
resolved_at TIMESTAMPTZ,
|
|
resolution TEXT
|
|
);
|
|
|
|
CREATE INDEX idx_sync_conflicts_device ON sync_conflicts(device_id);
|
|
CREATE INDEX idx_sync_conflicts_unresolved ON sync_conflicts(device_id) WHERE resolved_at IS NULL;
|