-- V2 placements multi-device : où existent les octets d'une ressource. -- `storage_locations` modélise chaque device (le serveur reste implicite via -- `resources.has_content`, pas de ligne). `resource_placements` matérialise -- les reports clients (`report_bytes` via l'outbox) : hint de présence, -- jamais une preuve — le serveur ne vérifie pas les octets du device. CREATE TABLE storage_locations ( id TEXT PRIMARY KEY CHECK (id ~ '^[0-9a-f]{32}$'), user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, device_id TEXT NOT NULL UNIQUE CHECK (device_id ~ '^[0-9a-f]{32}$'), type TEXT NOT NULL DEFAULT 'mobile' CHECK (type IN ('mobile', 'desktop', 'server')), last_seen_at TIMESTAMPTZ DEFAULT NOW(), created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_storage_locations_user ON storage_locations(user_id); CREATE TABLE resource_placements ( resource_id TEXT NOT NULL REFERENCES resources(resource_id) ON DELETE CASCADE, location_id TEXT NOT NULL REFERENCES storage_locations(id) ON DELETE CASCADE, has_bytes BOOLEAN NOT NULL, updated_at TIMESTAMPTZ DEFAULT NOW(), PRIMARY KEY (resource_id, location_id) ); CREATE INDEX idx_placements_location ON resource_placements(location_id);